--两表如果一致时: insert into tableA as select * from tableB;
begin for i in 1..500000 loop insert into test values(i); end loop; end;
insert into table1 select sysdate+rownum from dual connect by rownum<10000
我没搞过oracle 不知道写个存储过程的语法,,请大家能给个完整的存储过程。。谢谢
LZ的意思是总共10万条记录通过存储过程代码LOOP的方式循环插入吧?问题1:如果正合LZ所意请提供源表和目标表结构、数据,以及相关的存储过程代码/业务逻辑。问题2:可否通过如下SQL方式而不用存储过程: INSERT INTO SOURCE_TABLE(FIELD1,FIELD2,..) SELECT FIELD1,FIELD2,.. FROM TARGET_TABLE;
declare pi constant NUMBER(9, 7) :=3.1415927; RADIUS integer(5); area NUMBER(14, 2); begin radius := 1; loop area := pi*power(radius, 2); insert into AREAS values (radius, area); radius := radius+1; exit when radius >= 100000; end loop; end; /
create or replace procedure proc is begin for i in 1..500000 loop insert into test values(i); end loop; end proc;
今天闲得无聊 别人都不好意思些,我来写一种loop的SQL> select * from t1; IDX ---------------------------------------SQL> SQL> begin 2 for i in 1..100000 3 loop 4 insert into t1 values(i); 5 if i mod 1000=0 then 6 commit; 7 end if; 8 end loop; 9 end; 10 /PL/SQL procedure successfully completedSQL> select count(1) from t1; COUNT(1) ---------- 100000SQL>
SQL> create table test111(num number);--建个测试表
Table created --创建过程 SQL> create or replace procedure proc 2 as 3 begin 4 for i in 1..50000 loop 5 insert into test111 values(i); 6 end loop; 7 end proc; 8 /
Procedure created --调用过程 SQL> begin 2 proc; 3 end; 4 /
PL/SQL procedure successfully completed --查看结果 SQL> select * from test111;
declare pi constant NUMBER(9, 7) := 3.1415927; radius INTEGER(5); area NUMBER(14, 2); begin for radius in 1..7 loop area := pi*power(radius, 2); insert into AREAS values (radius, area); end loop; end; /
declare pi constant NUMBER(9, 7) := 3.1415927; radius INTEGER(5); area NUMBER(14, 2); begin radius := 3; while radius <= 100004 loop area := pi*power(radius, 2); insert into AREAS values (radius, area); radius := radius+1; end loop; end; /
--两表如果一致时:
insert into tableA as select * from tableB;
for i in 1..500000 loop
insert into test values(i);
end loop;
end;
select sysdate+rownum from dual
connect by rownum<10000
INSERT INTO SOURCE_TABLE(FIELD1,FIELD2,..) SELECT FIELD1,FIELD2,.. FROM TARGET_TABLE;
pi constant NUMBER(9, 7) :=3.1415927;
RADIUS integer(5);
area NUMBER(14, 2);
begin
radius := 1;
loop
area := pi*power(radius, 2);
insert into AREAS values (radius, area);
radius := radius+1;
exit when radius >= 100000;
end loop;
end;
/
is
begin
for i in 1..500000 loop
insert into test values(i);
end loop;
end proc;
别人都不好意思些,我来写一种loop的SQL> select * from t1; IDX
---------------------------------------SQL>
SQL> begin
2 for i in 1..100000
3 loop
4 insert into t1 values(i);
5 if i mod 1000=0 then
6 commit;
7 end if;
8 end loop;
9 end;
10 /PL/SQL procedure successfully completedSQL> select count(1) from t1; COUNT(1)
----------
100000SQL>
Table created
--创建过程
SQL> create or replace procedure proc
2 as
3 begin
4 for i in 1..50000 loop
5 insert into test111 values(i);
6 end loop;
7 end proc;
8 /
Procedure created
--调用过程
SQL> begin
2 proc;
3 end;
4 /
PL/SQL procedure successfully completed
--查看结果
SQL> select * from test111;
pi constant NUMBER(9, 7) := 3.1415927;
radius INTEGER(5);
area NUMBER(14, 2);
begin
for radius in 1..7 loop
area := pi*power(radius, 2);
insert into AREAS values (radius, area);
end loop;
end;
/
pi constant NUMBER(9, 7) := 3.1415927;
radius INTEGER(5);
area NUMBER(14, 2);
begin
radius := 3;
while radius <= 100004
loop
area := pi*power(radius, 2);
insert into AREAS values (radius, area);
radius := radius+1;
end loop;
end;
/