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

时间: 2024-11-10 07:58:00

动态sql 和静态sql 下篇的相关文章

动态sql 和静态sql 上篇

本文讲解基于Oracle高性能动态SQL程序开发. 1.静态SQL与动态SQL: Oracle编译PL/SQL程序块分为两个种: 其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型: 另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理. 通常,静态SQL

动态SQL和PL/SQL的EXECUTE IMMEDIATE选项

动态       EXECUTE IMMEDIATE代替了以前Oracle8i中DBMS_SQL package包.它解析并马上执行动态的SQL语句或非运行时创建的PL/SQL块.动态创建和执行SQL语句性能超前,EXECUTE IMMEDIATE的目标在于减小企业费用并获得较高的性能,较之以前它相当容易编码.尽管DBMS_SQL仍然可用,但是推荐使用EXECUTE IMMEDIATE,因为它获的收益在包之上.    使用技巧   1. EXECUTE IMMEDIATE将不会提交一个DML事务

SQL Server静态页面导出技术1

server|静态|页面 本段文章节选自铁道出版社新出的<用BackOffice建立Intranet/Extranet应用>一书(现已在海淀图书城有售).本书详尽地讲述了如何使用微软BackOffice系列产品来组建Intranet/Extranet应用.通过它您将掌握NT的安装和设置.使用IIS建立Web站点.通过ILS建立网络会议系统.用Exchange建立企业的邮件和协作系统.用SQL Server建立Web数据库应用.用Proxy Server建立同Internet安全可靠的连接.用M

MS SQL统计信息浅析下篇

   MS SQL统计信息浅析上篇对SQL SERVER 数据库统计信息做了一个整体的介绍,随着我对数据库统计信息的不断认识.理解,于是有了MS SQL统计信息浅析下篇. 下面是我对SQL Server统计信息的一些探讨或认识,如有不对的地方,希望大家能够指正. 触发统计信息更新条件疑问     关于这个触发统计信息更新的条件.因为我在很多资料上看到过,例如Microsoft  SQL Server 企业级平台管理实践. 我自己上篇也是这样解释的.    1:普通表上,触发数据库自动更新统计信息

sql 查询语句-在pb里面如何动态的执行一个sql语句

问题描述 在pb里面如何动态的执行一个sql语句 其效果是这样的: 在pb的界面上有省.市.区.街道四个字段 所以省需要管理到市,即在省的窗口里面 定义一个变量 在sql里面查询市时需要做到先把省查询出来,即为动态的实现查询市的结果 解决方案 PB动态SQL语句执行应用动态执行sql语句动态执行SQL语句

Oracle Listener 动态注册 与 静态注册_oracle

一.什么是注册 注册就是将数据库作为一个服务注册到监听程序.客户端不需要知道数据库名和实例名,只需要知道该数据库对外提供的服务名就可以申请连接到数据库.这个服务名可能与实例名一样,也有可能不一样. 在数据库服务器启动过程中,数据库服务器会向监听程序注册相应的服务(无论何时启动一个数据库,默认地都有两条信息注册到监听器中:数据库服务器对应的实例和服务.) 相当于是这样:在数据库服务器和客户端之间有一监听程序(Listener),在监听程序中,会记录相应数据库对应的服务名(一个数据库可能对应有多个服

SQL点滴16—SQL分页语句总结

原文:SQL点滴16-SQL分页语句总结 今天对分页语句做一个简单的总结,他们大同小异的,只要理解其中一个其他的就很好理解了. 使用top选项 selecttop10*from Orders a where a.orderid notin(selecttop10 orderid from Orders orderby orderid) orderby a.orderid 使用max函数 这种方法的前提是有唯一值的一个列. selecttop10*from Orders a  where a.or

【SQL】Oracle SQL monitor

[SQL]Oracle SQL monitor 第一章 被埋没的SQL优化利器--Oracle SQL monitor DBAplus社群 | 2015-11-26 07:00 转载声明:本文为DBA+社群原创文章,转载必须连同本订阅号二维码全文转载,并注明作者名字及来源:DBA+社群(dbaplus). 据说,在Oracle企业版数据库中有一个免费的工具,乃SQL优化之利器,那就是Oracle SQL monitor.下面,由DBA+社群原创专家周俊,给大家科普一下这一被埋没的神器. 周俊 D

Asp利用XMLHTTP将动态网页生成静态网页

xml|动态|静态|网页 新闻系统.blog系统等都可能用到将动态页面生成静态页面的技巧来提高页面的访问速度.下面是利用XMLHTTP将动态网页生成静态网页的一段简单代码.   url = "http://www.csdn.net/"  set http=Server.createobject("Msxml2.XMLHTTP")   Http.open "GET",url,false   Http.send()   set objStream =