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

//第一种方法
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