---
title: "利用存储过程循环插入数据"
author: "hanjava"
date: "2012-09-29T10:29:20Z"
tags: []
categories: ["数据库"]
slug: "untitled"
draft: false
lastmod: "2012-09-29T11:00:54Z"
id: 1688411
url: "https://hanjava.iteye.com/blog/1688411"
---

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