使用绑定变量,测试实例。
--创建M测试表
create table m (x int);
Table created.
--创建proc1过程
create or replace procedure proc1
as
begin
for i in 1..10000
loop
execute immediate
'insert into m values(:x)' using i;--使用变量
end loop;
end;
/
Procedure created.
--创建proc2过程
create or replace procedure proc2
as
begin
for i in 1..10000
loop
execute immediate
'insert into m values('||i||')';--未使用变量
end loop;
end;
/
Procedure created.
--打开时间显示
set timing on
select count(*) from m;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
--执行没有绑定变量的过程
exec proc2
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.48--执行时间7.48秒
select count(*) from m;
COUNT(*)
----------
10000
Elapsed: 00:00:00.00
--清空M表
truncate table m;
Table truncated.
Elapsed: 00:00:00.33
select count(*) from m;
COUNT(*)
----------
0
Elapsed: 00:00:00.00
--执行有绑定变量的过程
exec proc1
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.81--执行时间0.81秒
select count(*) from m;
COUNT(*)
----------
10000
Elapsed: 00:00:00.00
过程1与过程2的时间明显区别很大
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7755871/viewspace-738217/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7755871/viewspace-738217/