Oracle中动态SQL详解
1.静态SQLSQL与动态SQL
Oracle编译PL/SQL程序块分为两个种:其一为前期联编(early binding),即SQL语句在程序编译期间就已经确定,大多数的编译情况属于这种类型;另外一种是后期联编(late binding),即SQL语句只有在运行阶段才能建立,例如当查询条件为用户输入时,那么Oracle的SQL引擎就无法在编译期对该程序语句进行确定,只能在用户输入一定的查询条件后才能提交给SQL引擎进行处理。通常,静态SQL采用前一种编译方式,而动态SQL采用后一种编译方式。
本文主要就动态SQL的开发进行讨论,并在最后给出一些实际开发的技巧。
2.动态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)来实现:
过程一:
create or replace procedure create_table as begin execute immediate ' create table emp(id number, name varchar2(10), salary number ) ' ; -- 动态SQL为DDL语句 insert into emp values ( 100 , ' jacky ' , 5600 ); insert into emp values ( 101 , ' rose ' , 3000 ); insert into emp values ( 102 , ' john ' , 4500 ); end create_table;
过程二:
create or replace procedure find_info(p_id number ) as v_name varchar2 ( 10 ); v_salary number ; begin execute immediate ' select name,salary from emp where id=:1 ' using p_id returning into v_name,v_salary; -- 动态SQL为查询语句 dbms_output.put_line(v_name || ' 的收入为: ' || to_char(v_salary)); exception when others then dbms_output.put_line( ' 找不到相应数据 ' ); end find_info;
create or replace procedure find_emp(p_salary number ) as r_emp emp % rowtype; type c_type is ref cursor ; c1 c_type; begin open c1 for ' select * from emp where salary >:1 ' using p_salary; loop fetch c1 into r_emp; exit when c1 % notfound; dbms_output.put_line( ' 薪水大于‘||to_char(p_salary)||’的员工为:‘); dbms_output.put_line( ' ID为 ' to_char(r_emp)|| ' 其姓名为: ' ||r_emp.name); end loop; close c1; end create_table;
3. 动态SQL语句开发技巧
前面分析到了,动态SQL的执行是以损失系统性能来换取其灵活性的,所以对它进行一定程度的优化也是必要的,笔者根据实际开发经验给出一些开发的技巧,需要指出的是,这里很多经验不仅局限于动态SQL,有些也适用于静态SQL,在描述中会给予标注。
技巧一:尽量使用类似的SQL语句,这样Oracle本身通过SGA中的共享池来直接对该SQL语句进行缓存,那么在下一次执行类似语句时就直接调用缓存中已解析过的语句,以此来提高执行效率。
技巧二:当涉及到集合单元的时候,尽量使用批联编。比如需要对id为100和101的员工的薪水加薪10%,一般情况下应该为如下形式:
declare type num_list is varray( 20 ) of number ; v_id num_list : = num_list( 100 , 101 ); begin ... for i in v_id.first .. v_id.last loop ... execute immediate ' update emp set =salary*1.2 where id=:1 ' using v_id(i); end loop; end ;
declare type num_list is varray( 20 ) of number ; v_id num_list : = num_list( 100 , 101 ); begin ... forall i in v_id.first .. v_id.last loop ... execute immediate ' update emp set =salary*1.2 where id=:1 ' using v_id(i); end loop; end ;
1) 如果一个循 环 内 执 行了 insert , delete , update 等 语 句引用了集合元素,那 么 可以将其移 动 到一个 forall 语 句中。
2) 如果 select into , fetch into 或 returning into 子句引用了一个集合, 应该 使用 bulk collect 子句 进 行合并。
3) 如有可能, 应该 使用主机数 组 来 实现 在程序和数据 库 服 务 器之 间传递 参数。
技巧三:使用 NOCOPY 编译 器来提高 PL/SQL 性能。缺省情况下, out 类 型和 in out 类 型的参数是由 值传递 的方式 进 行的。但是 对 于大的 对 象 类 型或者集合 类 型的参数 传递 而言,其希望 损 耗将是很大的, 为 了减少 损 耗,可以采用引用 传递 的方式,即在 进 行参数声明的 时 候引用 NOCOPY 关键 字来 说 明即可到达 这样 的效果。 比如 创 建一个 过 程:
create or replace procedure test(p_object in nocopy square ) ... end ;
4. 小结
本文 对动态 SQL 的 编译 原理、 开发过 程以及 开发 技巧的 讨论 ,通 过 本文的介 绍 后,相信 读 者 对动态 SQL 程序 开发 有了一个 总 体的 认识 , 为 今后深入的工作打下一个良好的基 础 。