Oracle中动态SQL详解(EXECUTE IMMEDIATE)

前端之家收集整理的这篇文章主要介绍了Oracle中动态SQL详解(EXECUTE IMMEDIATE)前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

Oracle中动态sql详解

编者:什么是动态sql

要么是ddl 要么是dml(带参数的)


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 (即 参数的 2) 定参数列表 为输 入参数列表,即其 in 型,在运行 刻与 句中的参数( 实际 上占位符,可以理解 函数里面的形式参数) 定。 3) 出参数列表 为动态 行后返 回的参数列表。 4) 由于 是在运行 行确定的,所以相 于静 而言,其更多的会 失一些系 性能 取其灵活性。

了更好的 明其 开发 程,下面列 一个 例: 数据 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 beginexecute immediate ' create table emp(id number,name varchar2(10),salary number )'; --动态sql为DDL语句 insert emp values (100,jacky5600); 101rose3000102john4500end create_table;

过程二:

find_info(p_id number) v_name varchar2(10); v_salary ; select name,salary from emp where id=:1 using p_id returning v_name,v_salary; 动态sql查询语句dbms_output.put_line(v_name ||的收入为:'||to_char(v_salary)); exception when others then dbms_output.put_line(找不到相应数据); find_info;

程三: find_emp(p_salary r_emp emp%rowtype; type c_type is ref cursor; c1 c_type; open c1 for select * from emp where salary >:1 using p_salary; loop fetch r_emp; exit c1notfound; 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;
注意:在 程二中的 句使用了占位符 “:1“ ,其 它相当于函数的形式参数,使用 ,然后使用 using 句将 p_id 在运行 刻将 :1 掉, 相当于函数里的 参。另外 程三中打 的游 标为动态 ,它也属于 的范畴,其整个 编译 程与 execute immediate 行的 程很 似, 里就不在 述了

3.动态sql语句开发技巧

  前面分析到了,动态sql的执行是以损失系统性能来换取其灵活性的,所以对它进行一定程度的优化也是必要的,笔者根据实际开发经验给出一些开发的技巧,需要指出的是,这里很多经验不仅局限于动态sql,有些也适用于静态sql,在描述中会给予标注。

  技巧一:尽量使用类似的sql语句,这样Oracle本身通过SGA中的共享池来直接对该sql语句进行缓存,那么在下一次执行类似语句时就直接调用缓存中已解析过的语句,以此来提高执行效率。

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

declare type num_list varray(20of; v_id num_list :=num_list( ... i in v_id.first .. v_id.last loop ... update emp set =salary*1.2 where id=:1 using v_id(i); loop; ;

于上面的 理,当数据量大的 候就会 得比 慢,那 如果采用批 联编 整个集合首先一次性的 入到 引擎中 理, 这样 理效率要高的多, 行批 联编处 理的代 ... forall i 里是使用 forall 里将批 理的情形作一个小 如果一个循 行了 insert delete update 句引用了集合元素,那 可以将其移 到一个 句中。 如果 select into fetch into returning into 子句引用了一个集合, 应该 使用 bulk collect 子句 行合并。 如有可能, 使用主机数 实现 在程序和数据 器之 间传递 参数。   技巧三:使用 NOCOPY 器来提高 PL/sql 性能。缺省情况下, out 型和 in out 型的参数是由 值传递 的方式 行的。但是 于大的 型或者集合 型的参数 传递 而言,其希望 耗将是很大的, 了减少 耗,可以采用引用 的方式,即在 行参数声明的 候引用 关键 字来 明即可到达 效果 比如 建一个 程: test(p_object nocopy square) ... 其中 square 一个大的 型。 只是 一个地址,而不是 整个 象了。 理也是提高了效率。 4.小结   本文 对动态 原理、 开发过 程以及 技巧的 讨论 ,通 本文的介 后,相信 程序 有了一个 体的 认识 今后深入的工作打下一个良好的基 原文链接:https://www.f2er.com/oracle/210362.html

猜你在找的Oracle相关文章