ITeye博客

利用存储过程循环插入数据

hanjava · 2012-09-29 10:29 · 数据库应用 · 数据库 · Markdown

//第一种方法 create or replace procedure p_batchCreaeStaff ( i_staffid in varchar2, i_pwd in varchar2, i_name in varchar2, i_state in varchar2, i_org_id in varchar2, i_roleId in varchar2,

i_staffNum in number, o_status out number, o_errMsg OUT VARCHAR2 ) is i number :=0; v_num number(5):=100; v_staffNum number(10); v_staffId varchar2(100); v_name varchar2(100); v_post number(10) := 12; ls_flag varchar2(10); ln_start number(10);

begin ls_flag := 'F'; v_staffNum :=i_staffNum; WHILE ls_flag = 'F' LOOP --每次循环转换100条记录 ln_start := 0 ; for i in 1..v_staffNum loop v_staffId := i_staffid + i; v_name := i_name + i; --插入人员信息 insert into t_sys_staffbasicinfo( staff_id, staff_password, STAFF_NAME, STAFF_STATE, post, ORGA_ID) values ( v_staffId, i_pwd, v_name, i_state, v_post, i_org_id
);

     --插入人员角色
      insert into t_sys_staffrole
      (
        role_id,
        staff_id
        
        
       )
       values
      ( 
        i_roleId,
        v_staffId
      );
     
 ln_start := ln_start + 1 ;  
 END LOOP;

commit;

IF ln_start = 1000 THEN ls_flag := 'F'; ELSE ls_flag := 'T'; END IF;

END LOOP;

exception

when others then o_errMsg := SQLERRM; rollback;

end p_batchCreaeStaff; /


//第二种方法 create or replace procedure p_batchCreaeStaffInfo ( i_staffid in varchar2, i_pwd in varchar2, i_staffName in varchar2, i_state in varchar2, i_post in varchar2, i_org_id in varchar2, i_roleId in varchar2,

i_insertNum in number, i_CommitNum in number, o_status out number, o_errMsg OUT VARCHAR2 ) is i number :=0; v_staffId varchar2(100); v_staffName varchar2(100); ln_start number(10); begin o_status := 0;

--每次循环转换N条记录 ln_start := 0 ; for i in 1..i_insertNum loop v_staffId := i_staffid + i; v_staffName := i_staffName + i; --插入人员信息 insert into t_sys_staffbasicinfo( staff_id, staff_password, STAFF_NAME, STAFF_STATE, post, ORGA_ID) values ( v_staffId, i_pwd, v_staffName, i_state, i_post, i_org_id
);

     --插入人员角色
      insert into t_sys_staffrole
      (
        role_id,
        staff_id
       )
       values
      ( 
        i_roleId,
        v_staffId
      );
     
 ln_start := ln_start + 1 ;
 IF ln_start = i_CommitNum THEN
   ln_start := 0;
   commit;
 END IF; 

END LOOP;

exception

when others then o_errMsg := SQLERRM; o_status := -1; rollback;

end p_batchCreaeStaffInfo; /

--参数值 1000

DA4FB5C6E93E74D3DF8527599FA62642

1000

1

572

1184

100

评论(0)

暂无评论。

评论功能维护中,当前仅展示历史评论。