软件开发培训班 >> 编程开发 >> DB2


DB2存储过程快速入门的“窍门”

　　本文标签：DB2存储过程

　　以下的文章主要描述的是DB2存储过程快速入门的简便方案，DB2数据库存储过程的基本概念即是stored procedure,一般都会被简称procedure  。如果你要学这个先得弄明白另外一个概念：routine，这个一般翻译成“例程”  。

　　>>routine:存在server端，按应用程序逻辑编写的，可以通过client或者其他 routine调用的数据库对象.

　　>3种类型：stored procedures,UDFs(自定义function),methods.

　　stored procedures:作为客户端的扩展但是运行在服务端；UDFs:扩展并且自定义SQL；methods:提供结构化类型的行为

　　>2种形式：

　　1）sql routines:完全用sql编写，通过create statement来注册routine.

　　2）external routines:用C,C++,Java,OLE编写，stored procedure还可用cobol编写  。任何语言编写的都可以包含sql  。

　　不同形式的routines可以互相调用，不管是什么语言编写的  。

　　再来看看stored procedure.

　　>>stored procedures:可以通过call statement被client或者其他routine调用；stored procedures 和它的调用程序通过create procedure statement中的参数交换数据；stored procedures还能给它的调用者返回result sets.

　　stored procedures的优点：

　　1) 多个sql statement被调用者一次调用就能全部执行，这能减少client和server间的数据传输  。

　　2)将数据库逻辑与应用程序逻辑隔离开

　　3)能返回多个result sets

　　4)如果被应用程序调用，运行起来stored procedure就像应用程序的一部分

　　缺点：

　　1)不能被sql statement调用，除了用call

　　2)返回的结果集不能直接被sql statement使用

　　3)多次调用之间不能保存调用的状态，即调用之间是独立的，无法传递信息  。

　　一般的应用之处：

　　1)提供一个interface给一组sql statements  。比如同时对多个表的insert操作

　　2)标准化应用程序逻辑（不理解，就是把db logic与app logic隔离吗？）

　　DB2存储过程之开发特性：

　　明白了这些基本概念后再来看看开发的特性  。根据以上得知开发routine的语言有很多，这篇只讲sql procedure（即sql/sql pl写的procedure）  。

　　>>各种语言的特性

　　sql:

　　1)效率高于java routine,基本上与c/c++ routine相当

　　2)完全用sql编写，能很快就能执行(making them quick to implement)

　　3)DB2认为sql routine是safe的因为全是sql，正因如此sql routine能直接在db engine上运行，并且有很好的运行效率和应用范围(good performance and scalability)

　　

  1. >>stored procedure feathures:  
  2. parameter modes:  

　　

　　3种类型的参数：1)IN :传入数据到stored procedure 2)OUT: stored procedure 返回数据 3)INOUT: 传入的那部分数据，在执行过程中被返回数据覆盖

　　

  1. result sets: 

　　stored procedure通过cursor来传递结果集给调用者  。DB2存储过程必须为每一个需要返回的结果集保留一个游标  。

　　>使用with return to caller/client来指定结果集返回的对象  。指定为client将使得中间调用的routine不能获得结果集，只有client才能获得  。

　　>使用dynamic result sets 语句来指定返回结果集的数目，这个数目保存在syscat.routines视图的result_sets字段  。如果实际返回的结果集数目大于声明的这个数目，将发出一个warning(sqlcode +464,sqlstate 0100E)

　　sql stored procedure返回结果集的操作步骤：

　　1)declare cursor:

　　如：declare clientcur cursor with return to caller for select * from staff;

　　2)open the cursor:如 open clientcur;

　　3)不关闭游标退出stored procedure

　　DB2存储过程之开发：

　　最后终于来到了真正的开发了，刚才讲到sql procedure是由sql,sql pl写的，sql就没什么好说的了  。关键说说sql pl (procedural language)

　　>>功能:控制逻辑流向，声明和设置变量，处理警告和异常  。可用于例程(routine),触发器，动态复合语句（单个调用中的sql语句块）

　　>>控制语句：declare,set,for,get diagnostics,if,iterate,leave,return,signal,while

　　>>sql pl不能执行的sql：table,index,view的create和drop

　　>>begin atomic 开头，end 结尾

　　>>declare :定义变量 和 定义出错处理

　　declare sql-var-name data-type default default-values

　　declare condition-name condition for sqlstate value... //这里的condition一般做“异常”解释

　　>>set:声明变量 和 给触发器定义中的表中的列赋值

　　set pay = select salary from employee where empno = 5;//仅返回一个值

　　set pay = null;//空值

　　set pay = default;//变量定义的默认值

　　//专用寄存器的内容

　　set userid = userid;

　　set today = current date;

　　//同时给多个变量赋值

　　

  1. set pay =10000,bonus = 1500;  
  2. set (pay,bonus) = (10000,1500);  
  3. set (pay,bonus) = select (pay,bonus) from employee where empno = 5;  
  4. >>if/then/else  

　　

　　三种形式：

　　1) if then/end if 语句块

　　2) if then/else/end if

　　3) if then/elseif /else/end if

　　可以在if/then/else 语句中使用sql运算符，如：

　　

  1. if (salary between 10000 and 90000) then...  
  2. if (deptno in (a00,b01)) then..  
  3. if (exist (select * from employee)) then...  
  4. if (select count(*) from employee)>0) then..  
  5. >>while  
  6. label:  
  7. while condition do  
  8. ...sql pl ..  
  9. end while lable;   

　　label可选

　　>>for:用于循环select返回结果集的行

　　格式：

　　

  1. label:  
  2. for row_label as select satement do  
  3. ..sql pl..  
  4. end for label;  

　　label可选

　　例子：

　　

  1. for emp as select * from employee where bonus >1000 do  
  2. set total_bonustotal_bonus = total_bonus +emp.bonus;  
  3. end for;  

　　

　　>>iterate:用来回到for或者while循环的开始重新执行

　　

  1. check_bonus:   
  2. for emp as select * from employee do  
  3. if(emp.bonus>10000) then  
  4. set total_bonustotal_bonus = total_bonus +emp.bonus;  
  5. else  
  6. iterate check_bonus;  
  7. end if;  
  8. end for check_bonus;  

　　

　　>>leave:相当于java中的break，需要一个label

　　>>signal:对出现异常的应用程序报警

　　signal sqlstate value set message_text = ...;//自定义一个sqlstate,7、8、9和I'Z开头的sqlstate

　　signal condition set message_text = ...;//自定义异常condition

　　>>get diagnostics:用在sql pl触发器或语句块(不是函数)内，返回update,insert,delete语句影响的记录数  。

　　

  1. get diagnostics variable = row_count; 

　　如果你还不很熟悉DB2存储过程，那么读完此文章你将收获很大的  。