• 软件测试技术
  • 软件测试博客
  • 软件测试视频
  • 开源软件测试技术
  • 软件测试论坛
  • 软件测试沙龙
  • 软件测试资料下载
  • 软件测试杂志
  • 软件测试人才招聘
    暂时没有公告

字号: | 推荐给好友 上一篇 | 下一篇

软件测试数据库中基于Oracle的高性能动态SQL程序开发

发布: 2010-6-25 08:15 | 作者: 不详 | 来源: 领测软件测试网采编 | 查看: 21次 | 进入软件测试论坛讨论

领测软件测试网

过程三:

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;

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

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;

对于上面的处理,当数据量大的时候就会显得比较慢,那么如果采用批联编的话,则整个集合首先一次性的传入到SQL引擎中进行处理,这样比单独处理效率要高的多,进行批联编处理的代码如下:

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;

这里是使用forall来进行批联编,这里将批联编处理的情形作一个小结:

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;

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

4. 小结

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

前面代码部分已经在下列环境中调试成功:

服务器端:UNIX+ORACLE9.2

客户端:WINDOWS2000 PRO+TOAD

文章来源于领测软件测试网 https://www.ltesting.net/

22/2<12

关于领测软件测试网 | 领测软件测试网合作伙伴 | 广告服务 | 投稿指南 | 联系我们 | 网站地图 | 友情链接
版权所有(C) 2003-2010 TestAge(领测软件测试网)|领测国际科技(北京)有限公司|软件测试工程师培训网 All Rights Reserved
北京市海淀区中关村南大街9号北京理工科技大厦1402室 京ICP备2023014753号-2
技术支持和业务联系:info@testage.com.cn 电话:010-51297073

软件测试 | 领测国际ISTQBISTQB官网TMMiTMMi认证国际软件测试工程师认证领测软件测试网