动态sql 和静态sql 下篇

动态sql 和静态sql 下篇

1、批量绑定让oracle 在对一组值进行sql操作时绑定一个变量，这一组数据可以是任何的plsql的聚合类型（联合数组，内嵌表，可变数组），
集合元素必须是sql的数据类型，比如char date mumber。有三个句法支持动态批量绑定，EXECUTE IMMEDIATE , FETCH , FORALL;

EXECUTE IMMEDIATE: 你可以用BULK COLLECT INTO子句和EXECUTE IMMEDIATE声明去存储 一个查询集合的每一列到一个分散的集合中去。
你可以用RETURNING BULK COLLECT INTO子句和EXECUTE IMMEDIATE去存储insert ，update或者delete的结果集到一个集合中。

FETCH: 你可以用BULK COLLECT INTO和FETCH子句去存储游标的每一列值到一个集合中。

FORALL: 你可以用EXECUTE IMMEDIATE声明把RETURNING BULK COLLECT INTO放在一个FORALL里面.你可以去存储insert ，update或者delete的结果集到一个集合中。

2、动态绑定的例子：

动态sql中的bulk collect into 子句：

declare
type empcurtyp is ref cursor;
type numlist is table of number;
type namelist is table of varchar2(25);
emp_cv  empcurtyp;
empids  numlist;
empnames namelist;
sals  numlist;
begin
open emp_cv for ' select employee_id,last_name from employees';
fetch emp_cv BULK COLLECT INTO empids,enames;  --批量存入内嵌表empids和enames。
close emp_cv;    --关闭游标
EXECUTE IMMEDIATE ' select salary from employees' BULK COLOLECT INTO sals;  --批量存入sals表。
end;

动态sql中的returning bulk collect into 和 using子句：

declare
type namelist is table of varchar2(15);
enames  namelist;
bonus_amt number:=50;
sql_stmt varchar2(200);
begin
sql_stmt:='update employees set salary =salary :1 returning last_name into :2';
execude  immediate sql_stmt
using bonus_amt RETURNING BULK COLLECT INTO enames; --sql_stmt中用了returning。。。。
end;

动态sql中用forall 和 using子句:

declare
type numberlist is table of number;
type namelist is table of varchar2(15);
empids numlist;
enames namelist;
begin
empids:=numlist(101,102,103,104,105);
FORALL i in 1..5
execute immediate
'update employees set salary=salaruy *1.04 where employee_id=:1 RETURNING last_name into :2'      --注意这里没有分号结束，这个例子与上面的例子是不同的，这是一句整体的话。
using empids(i) RETURNING BULK COLLECT INTO enames;
  end;

3、提高带有绑定变量的动态sql的性能

当你在plsql语句中用到 insert ，update，delete，select语句时，plsql把变量自动的转变成绑定的变量，这样就能使语句执行的更加迅速，
但是在动态sql中，你需要自己去绑定变量，以达到更好地性能。

下面的例子中，oracle为每个单独的emp_id打开了一个单独的cursor，这样带来的资源竞争和性能低下是可想而知的：

create procedure fire_employee (emp_id  number) as
begin
execute immediate 'delete from employee where employee_id=' || to_char(emp_id);
end;

当然我们可以用下面的绑定变量的方法，为不同的emp_id重用一个cursor:

create procedure fire_employee(emp_id) as
begin
execute immediate 'delete from employee where employee_id= :id' using emp_id;  --这句话就表示绑定了变量。
end;

看下面一个传递对象名作为参数的例子：

create procudure drop_table(table_name in varchar2) as
begin
execute immediate 'drop table' || table_name;
end;

如果你要调用一个不知道名字的过程（当运行时才知道名字）：

create procedure run_proc(proc_name in varchar2, table_name in varchar2) as
begin
execute immediate 'call "  ' || proc_name| | ' "( :proc_name) ' using table_name;
end;

下面来调用上面的过程：

create table employees_temp as select last_name from employees;

begin
  run_proc('drop_table' , 'employees_temp');  --传入两个实参，一个是过程参数drop_table过程，另一个是表employees_temp。
end;     


4、重复占位符的使用：

在动态sql中，占位符和变量的绑定是由位置决定的，而不是由名字决定的，有一个例子很好的说明一下：

sql_stmt :='insert into payroll values(:x,:x,:y,:x)';
在这里x是没有实际意义的，它只是占据了一个位置，在using子句中你可以绑定四个不同的值：
execute immediate sql_semt using a,a,b,a;

如果一个动态的声明代表了一个plsql块，那这个规则就不一样了：每一个单独的占位符在using子句中对应一个单独的字符。如果相同的占位符出现多次，在using子句中和绑定的变量一致的参照的名字相对应：

create procedure calc_stats(w number , x number , z number ) is
begin
dbms_output.put_line(w x y z);
end;
/

declare                                           
a  number:=4;
b  number:=7;
plsql_block varchar2(100); --声明一个plsql块的字符串。
begin
plsql_block:='begin calc_stats(:x , :x, :y, :x, ) ;  end ;';        --这是在一个plsql块中的占位符，注意与上面的区分好。
execute immediate plsql_block using a,b;  --a代表先出现的x，b代表后出现的y。
end;


5、在动态sql中用cursor的属性（%found, %isopen, %notfount, %rowcount）：

declare
type cursor_ref is ref cursor;
c1 cursor_ref;
type emp_tab is table of employees%rowtype;
rec_tab emp_tab;
rows_fetched  number;
begin
open c1 for 'select * from employees';
fetch c1 BULK COLLECT INTO rec_tab;
rows_fetched:=c1%rowcount;
dbms_output.put_line('number of employees fetched:' || to_char(rows_fetched));
end;


转自：http://blog.csdn.net/cymm_liu/article/details/7449459