深入高性能的Oracle动态SQL开发

数据库 Oracle
Oracle动态SQL开发你是否了解,这里就向大家简单介绍一下,欢迎大家能和我一起学习。文中包含了静态SQL与动态SQL的区别,动态SQL语句开发技巧等。

Oracle动态SQL开发你是否了解,这里就向大家简单介绍一下,欢迎大家能和我一起学习。

1.静态SQL与动态SQL

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

2.Oracle动态SQL开发

理解了动态SQL编译的原理,也就掌握了其基本的开发思想。动态SQL既然是一种”不确定”的SQL,那其执行就有其相应的特点。Oracle中提供了Execute immediate语句来执行动态SQL,语法如下:
Excute immediate 动态SQL语句 using 绑定参数列表 returning into 输出参数列表;
对这一语句作如下说明:
1) 动态SQL是指DDL和不确定的DML(即带参数的DML)
2) 绑定参数列表为输入参数列表,即其类型为in类型,在运行时刻与动态SQL语句中的参数(实际上占位符,可以理解为函数里面的形式参数)进行绑定。
3) 输出参数列表为动态SQL语句执行后返回的参数列表。
4) 由于动态SQL是在运行时刻进行确定的,所以相对于静态而言,其更多的会损失一些系统性能来换取其灵活性。
为了更好的说明其开发的过程,下面列举一个实例:

设数据库的emp表,其数据为如下:

ID NAME SALARY
100 Jacky  5600
101 Rose  3000
102  John  4500

要求:
1.创建该表并输入相应的数据。
2.根据特定ID可以查询到其姓名和薪水的信息。
3.根据大于特定的薪水的查询相应的员工信息。
根据前面的要求,可以分别创建三个过程(均使用动态SQL)来实现:

过程一:

  1. create or replace procedure create_table as  
  2. begin  
  3. execute immediate '  
  4. create table emp(id number,  
  5. name varchar2(10)  
  6. salary number; )'; --动态SQL为DDL语句  
  7. insert into emp  
  8. values (100,'jacky',5600);  
  9. insert into emp  
  10. values (101,'rose',3000);  
  11. insert into emp  
  12. values (102,'john',4500);  
  13. end create_table;   
  14.  

过程二:

  1. create or replace procedure find_info(p_id number) as  
  2. v_name varchar2(10);  
  3. v_salary number;  
  4. begin  
  5. execute immediate '  
  6. select name,salary from emp  
  7. where id=:1'  
  8. using p_id  
  9. returning into v_name,v_salary; --动态SQL为查询语句  
  10. dbms_output.put_line(v_name ||'的收入为:'||to_char(v_salary));  
  11. exception  
  12. when others then   
  13. dbms_output.put_line('找不到相应数据');  
  14. end find_info;    
  15.  
  16. 过程三:  
  17.  
  18. create or replace procedure find_emp(p_salary number) as  
  19. r_emp emp%rowtype;  
  20. type c_type is ref cursor;  
  21. c1 c_type;  
  22. begin  
  23. open c1 for '  
  24. select * from emp  
  25. where salary >:1'  
  26. using p_salary;  
  27. loop  
  28. fetch c1 into r_emp;  
  29. exit when c1%notfound;  
  30. dbms_output.put_line('薪水大于‘||to_char(p_salary)||’的员工为:‘);  
  31. dbms_output.put_line('ID为'to_char(r_emp)||' 其姓名为:'||r_emp.name);  
  32. end loop;  
  33. close c1;   
  34. end create_table;   
  35.  

注意:在过程二中的动态SQL语句使用了占位符“:1“,其实它相当于函数的形式参数,使用”:“作为前缀,然后使用using语句将p_id在运行时刻将:1给替换掉,这里p_id相当于函数里的实参。另外过程三中打开的游标为动态游标,它也属于动态SQL的范畴,其整个编译和开发的过程与execute immediate执行的过程很类似,这里就不在赘述了。#p#

3.动态SQL语句开发技巧

前面分析到了,动态SQL的执行是以损失系统性能来换取其灵活性的,所以对它进行一定程度的优化也是必要的,笔者根据实际开发经验给出一些开发的技巧,需要指出的是,这里很多经验不仅局限于动态SQL,有些也适用于静态SQL,在描述中会给予标注。
技巧一:尽量使用类似的SQL语句,这样Oracle本身通过SGA中的共享池来直接对该SQL语句进行缓存,那么在下一次执行类似语句时就直接调用缓存中已解析过的语句,以此来提高执行效率。

技巧二:当涉及到集合单元的时候,尽量使用批联编。比如需要对id为100和101的员工的薪水加薪10%,一般情况下应该为如下形式:

  1. declare   
  2. type num_list is varray(20) of number;  
  3. v_id num_list :=num_list(100,101);  
  4. begin  
  5. ...  
  6. for i in v_id.first .. v_id.last loop  
  7. ...  
  8. execute immediate 'update emp   
  9. set =salary*1.2   
  10. where id=:1 '  
  11. using v_id(i);  
  12. end loop;  
  13. end;   
  14. 对于上面的处理,当数据量大的时候就会显得比较慢,那么如果采用批联编的话,则整个集合首先一次性的传入到SQL引擎中进行处理,这样比单独处理效率要高的多,进行批联编处理的代码如下:  
  15. declare   
  16. type num_list is varray(20) of number;  
  17. v_id num_list :=num_list(100,101);  
  18. begin  
  19. ...  
  20. forall i in v_id.first .. v_id.last loop  
  21. ...  
  22. execute immediate 'update emp   
  23. set =salary*1.2   
  24. where id=:1 '  
  25. using v_id(i);  
  26. end loop;  
  27. end;   
  28.  

这里是使用forall来进行批联编,这里将批联编处理的情形作一个小结:
1) 如果一个循环内执行了insert,delete,update等语句引用了集合元素,那么可以将其移 动到一个forall语句中。
2) 如果select into,fetch into 或returning into 子句引用了一个集合,应该使用bulk collect 子句进行合并。
3) 如有可能,应该使用主机数组来实现在程序和数据库服务器之间传递参数。
技巧三:使用NOCOPY编译器来提高PL/SQL性能。缺省情况下,out类型和in out类型的参数是由值传递的方式进行的。但是对于大的对象类型或者集合类型的参数传递而言,其希望损耗将是很大的,为了减少损耗,可以采用引用传递的方式,即在进行参数声明的时候引用NOCOPY关键字来说明即可到达这样的效果。比如创建一个过程:

  1. create or replace procedure test(p_object in nocopy square)  
  2. ...  
  3. end;   
  4.  

其中square为一个大的对象类型。这样只是传递一个地址,而不是传递整个对象了。显然这样的处理也是提高了效率。

4 小结

本文对Oracle动态SQL的编译原理、开发过程以及开发技巧的讨论,通过本文的介绍后,相信读者对Oracle动态SQL程序开发有了一个总体的认识,为今后深入的工作打下一个良好的基础。

前面代码部分已经在下列环境中调试成功:
服务器端:UNIX+ORACLE9.2
客户端:WINDOWS2000 PRO+TOAD

  1. CREATE OR REPLACE package body pkg_test as   
  2. --函数体   
  3.     function get(intID number) return myrctype is   
  4.         rc myrctype; --定义ref cursor变量   
  5.         sqlstr varchar2(500);   
  6.     begin   
  7.         if intID=0 then   
  8.             --静态测试,直接用select语句直接返回结果   
  9.             open rc for select id,name,sex,address,postcode,birthday from student;   
  10.         else   
  11.             --动态sql赋值,用:w_id来申明该变量从外部获得   
  12.             sqlstr :'select id,name,sex,address,postcode,birthday from student where id=:w_id';   
  13.             --动态测试,用sqlstr字符串返回结果,用using关键词传递参数   
  14.             open rc for sqlstr using intid;   
  15.         end if;   
  16.         return rc;   
  17.     end get;   
  18. end pkg_test;   
  19. /   
  20.  

【编辑推荐】

  1. 如何用Oracle 9i全索引扫描完成任务
  2. Oracle服务器如何进一步的获取权限 
  3. 对Oracle数据库设计中字段的正确使用方案
  4. 访问 Oracle 数据库的实例描述
  5. Oracle数据库的密集型实际应用程序的开发
责任编辑:佚名
相关推荐

2013-09-10 16:16:19

移动网站性能优化移动web

2011-06-14 09:27:43

高性能WEB开发

2011-02-13 10:12:24

SQL语句

2014-11-21 13:13:10

2010-04-12 14:22:13

Oracle性能sql语句

2016-12-15 09:58:26

优化SQL高性能

2017-07-12 13:04:23

数据库SQL查询执行计划

2009-02-27 14:46:09

AjaxGWT开发

2011-04-18 10:16:30

WEB高性能

2010-11-08 09:59:22

SQL Server动

2011-04-27 10:57:29

高性能web开发

2010-11-12 12:01:08

Oracle存储过程

2015-07-13 11:39:25

SphinxSQL

2011-08-29 16:05:07

高性能SQL语句SQL Server

2021-03-10 07:20:45

网络IO同步

2011-10-24 09:43:18

高性能计算HPC云计算

2020-12-04 11:40:53

Linux

2010-04-13 14:43:37

Oracle性能检测

2010-04-13 14:36:17

Oracle性能检测

2011-04-22 09:54:37

CSSjavascript
点赞
收藏

51CTO技术栈公众号