Question

Oracle 批量插入数据怎么做

Answer 1

Oracle 批量插入数据的方法：
1 创建表
create table emp (id number(4),name varchar2(10));

2 创建序列
create sequence my_seq increment by 1 start with 1 maxvalue 1000;

3 创建获取序列的函数
create or replace function get_seq return number
as
seq number;
begin
select my_seq.nextval into seq from dual;
return seq;
end get_seq;
/

4 创建匿名块
DECLARE
i INT;
testid number(4);
BEGIN
i := 0;
WHILE(i < 1000)
LOOP
i := i + 1;
testid := get_seq;
INSERT INTO EMP(ID,NAME) VALUES(testid, 'Zheng');
END LOOP;
COMMIT;
END;
/

注：第4步也可以使用存储过程
create or replace procedure my_proc as
BEGIN
DECLARE
i INT;
testid number(4);
BEGIN
i := 0;
WHILE(i < 1000)
LOOP
i := i + 1;
testid := get_seq;
INSERT INTO EMP(ID,NAME) VALUES(testid, 'Zheng');
END LOOP;
COMMIT;
END;
END;
/

exec my_proc;
5 检验结果：
select count(*) from emp;

COUNT(*)
----------
1000