day18——sql优化——绑定变量
SQL> create table t(id int);
SQL> set timing on
SQL> variable x number; (声明x变量)
SQL> exec :x :=8
SQL> insert into t values(:x);
SQL> select * from t;
SQL> commit;
SQL> declare
2 i number;
3 sqlstr varchar(2000);
4 begin
5 for i in 1..1000 loop
6 sqlstr :='insert into t values('||to_char(i)||')';
7 execute immediate sqlstr;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.05
(这段代码同样是执行了1000条insert语句，但是每一条语句都是不同的，因此ORACLE会把每条语句硬解析一次，其效率就比前面那段就低得多了。)
SQL> declare
2 i number;
3 sqlstr varchar(2000);
4 begin
5 for i in 1..1000 loop
6 sqlstr :='insert into t values(:i)';
7 execute immediate sqlstr using i;
8 end loop;
9 end;
10 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.19
(使用了绑定变量执行1000条时 只有第一条硬解析)
