动态sql 和静态sql 下篇

简介: 1、批量绑定让oracle 在对一组值进行sql操作时绑定一个变量,这一组数据可以是任何的plsql的聚合类型(联合数组,内嵌表,可变数组),集合元素必须是sql的数据类型,比如char date mumber。

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(调用者权限)

相关文章
|
25天前
|
SQL Java 关系型数据库
MyBatis的动态SQL之OGNL(Object-Graph Navigation Language)表达式以及各种标签的用法
MyBatis的动态SQL之OGNL(Object-Graph Navigation Language)表达式以及各种标签的用法
15 0
|
25天前
|
SQL Java 关系型数据库
MyBatis中的9种常用动态sql标签精妙用法
MyBatis中的9种常用动态sql标签精妙用法
35 0
|
5月前
|
SQL XML Java
Mybatis之动态SQL(带你了解动态SQL的魅力)
Mybatis之动态SQL(带你了解动态SQL的魅力)
304 0
|
1月前
|
SQL druid Java
【MyBatis】2、MyBatis 的动态 SQL 和增删改操作
【MyBatis】2、MyBatis 的动态 SQL 和增删改操作
27 0
|
2月前
|
SQL XML Java
【JavaEE进阶】 MyBatis之动态SQL
【JavaEE进阶】 MyBatis之动态SQL
|
3月前
|
SQL 缓存 Java
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
62 0
JAVAEE框架技术之8-myBatis ORM框架技术参数和动态SQL语句
|
3月前
|
SQL XML Java
【MyBatis】动态SQL
【MyBatis】动态SQL
|
3月前
|
SQL XML Java
MyBatis 实现动态 SQL
 MyBatis 中的动态 SQL 就是SQL语句可以根据不同的情况情况来拼接不同的sql。 本文会介绍 xml 和 注解 两种方式的动态SQL实现方式。
71 1
|
3月前
|
SQL Java 数据库连接
Mybatis之动态SQL
【1月更文挑战第4天】Mybatis框架的动态SQL技术是一种根据特定条件动态拼装SQL语句的功能,它存在的意义是为了解决拼接SQL语句字符串时的痛点问题
39 2
|
3月前
|
SQL Java 数据库连接
一文彻底搞懂Mybatis系列(九)之Mybatis动态SQL标签总结
一文彻底搞懂Mybatis系列(九)之Mybatis动态SQL标签总结