动态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中的return bulk collect  into 和 using子句：

declare

  type namelist is table of varchar2(15);

  enames  namelist;

  bonus_amt   number:=50;

  sql_stmt  varchar(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中用fofall 和 using子句:

declaere

 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 empl;oyees 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 tbale 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。

endl;

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

declare 

type cursor_rof 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;

6、null值，database link ，invoker rights（调用者权限）