利用存储过程循环插入数据
//第一种方法 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