oracle10g 批量插入存储过程

Question

我的表（tbl_cus）字段是id，cusname，phone，state。谁能帮我写个insert的存储过程？每500条提交一次。谢谢大家了！

Answer 1

CREATE OR REPLACE TYPE tbl_cus_record_type IS OBJECT(
       ID VARCHAR2(20),
       cusname VARCHAR2(100),
       phone VARCHAR2(20),
       state VARCHAR2(2)
);
/CREATE OR REPLACE TYPE tbl_cus_table_type IS TABLE OF tbl_cus_record_type;
/PROCEDURE insert_tbl_cus_proc(info_list    IN  tbl_cus_table_type
                              out_message  OUT VARCHAR2) IS
BEGIN
   FOR i IN info_list.count LOOP
      INSERT INTO tbl_cus(ID,cusname,phone,state)
      VALUES (info_list(i).id,info_list(i).cusname,info_list(i).phone,info_list(i).state);
       
      IF MOD(i,500) = 0 THEN
         COMMIT;
      END IF;
   END LOOP;
   
   COMMIT;
   
EXCEPTION
   WHEN OTHERS THEN
      out_message := 'ERROR!';
      ROLLBACK;
END;
/
试一下这样行不行，没测试过哦。

Answer 2

create or replace procedure bulk_insert(in_bulk_count In integer)
is
cursor cur is select * from tbl_cus;
type tbl_b is table of tbl_cus%rowtype index by pls_integer;
v_tbl_b tbl_b;
begin
 open cur;
 loop
 <<bulk_insert>>
  exit when cur%notfound;
  fetch cur bulk collect into v_tbl_b limit in_bulk_count;
  forall i in indices of v_tbl_b save exceptions
  insert /*+nologging*/ into a values v_tbl_b(i);
  commit;
  exception
  when others then  
  null;
 end loop bulk_insert;
close cur;
end;执行的时候exec bulk_insert(500);

Answer 3

insert into tbl_cus(id，cusname，phone，state) select id,cusname,phone,state from (  select id,cusname,phone,state,rank() over(order by field1) rn from tbl_data_source)
where rn between i*500+1 and (i+1)*500
 
如果有数据源批量的，可以这么做吧

Answer 4

mutou88我想问一下你的存储过程在c#中如何调用呢？

Answer 5

那我就不知道啦，完全不懂C#。java里面是把表列的值都放到一个object里面，然后比如说要插入五百行的话，生成一个Array，Array里面放的就是object，然后把这个Array作为一个参数调用存储过程。至于C#是怎样，求高人解答~~

Answer 6

mutou88还有我复制你的代码执行提示sql语句错误，然后我就在PROCEDURE前面加上CREATE OR REPLACE就好了。但是存储过程创建完之后，左上角是个红叉。recompile后提示insert_tbl_cus_proc compiled with errors。

Answer 7

比好意思，tbl_cus_table_type后面漏了逗号，for那也漏了1..应该是这样：CREATE OR REPLACE PROCEDURE insert_tbl_cus_proc(info_list    IN  tbl_cus_table_type,
                              out_message  OUT VARCHAR2) IS
BEGIN
   FOR i IN 1.. info_list.count LOOP
      INSERT INTO tbl_cus(ID,cusname,phone,state)
      VALUES (info_list(i).id,info_list(i).cusname,info_list(i).phone,info_list(i).state);      IF MOD(i,500) = 0 THEN
         COMMIT;
      END IF;
   END LOOP;   COMMIT;EXCEPTION
   WHEN OTHERS THEN
      out_message := 'ERROR!';
      ROLLBACK;
END;平时存储过程都是写在包里，忘了加create or replace了；

Answer 8

不行还是报那个错误。我把代码贴出来你看看。表换成了test，字段也换了一下
CREATE OR REPLACE TYPE test_record_type IS OBJECT(
       testid VARCHAR2(40),
       testname VARCHAR2(10),
       testphone VARCHAR2(11),
);CREATE OR REPLACE TYPE test_table_type IS TABLE OF test_record_type;CREATE OR REPLACE PROCEDURE insert_test_proc(info_list  IN  test_table_type,
                              out_message  OUT VARCHAR2) IS
BEGIN
   FOR i IN 1.. info_list.count LOOP
      INSERT INTO test(testID,testname,testphone)
      VALUES (info_list(i).testid,info_list(i).testname,info_list(i).testphone);
       
      IF MOD(i,500) = 0 THEN
         COMMIT;
      END IF;
   END LOOP;
   
   COMMIT;
   
EXCEPTION
   WHEN OTHERS THEN
      out_message := 'ERROR!';
      ROLLBACK;
END;

Answer 9

你可以在PL/SQL developer里面找到你那个存储过程，右键，编辑，然后看看报什么错的。

Answer 10

Error: PLS-00905: 对象 USR_CC.TEST_TABLE_TYPE 无效
Line: 1
Text: CREATE OR REPLACE PROCEDURE insert_test_proc(info_list  IN  test_table_type,Error: PL/SQL: Compilation unit analysis terminated
Line: 0
Text: CREATE OR REPLACE PROCEDURE insert_test_proc(info_list  IN  test_table_type,就是这样USR_CC是数据库的登录名

Answer 11

我想问一下执行这个存储过程，insert的时候数据是从哪来的？能具体说一下这个object类型怎么使用吗？谢谢！！！

Answer 12

CREATE OR REPLACE TYPE test_record_type IS OBJECT(
  testid VARCHAR2(40),
  testname VARCHAR2(10),
  testphone VARCHAR2(11),  ----- 这里多了个逗号
);

Answer 13

mutou88去掉逗号后好使了。谢谢

Answer 14

insert里面的数据是从页面得来的。谁能告诉我在asp.net（c#）中如何调用这个存储过程？CREATE OR REPLACE TYPE test_record_type IS OBJECT(
       testid VARCHAR2(40),
       testname VARCHAR2(10),
       testphone VARCHAR2(11)
);CREATE OR REPLACE TYPE test_table_type IS TABLE OF test_record_type;CREATE OR REPLACE PROCEDURE insert_test_proc(info_list  IN  test_table_type,
                              out_message  OUT VARCHAR2) IS
BEGIN
   FOR i IN 1.. info_list.count LOOP
      INSERT INTO test(testID,testname,testphone)
      VALUES (info_list(i).testid,info_list(i).testname,info_list(i).testphone);
       
      IF MOD(i,500) = 0 THEN
         COMMIT;
      END IF;
   END LOOP;
   
   COMMIT;
   
EXCEPTION
   WHEN OTHERS THEN
      out_message := 'ERROR!';
      ROLLBACK;
END;

Answer 15

请问调用这个存储过程insert_test_proc时，传递的参数info_list数据格式是什么样的？