对DB2 sql 存储过程基础的详细解析

downup

存储过程就是stored 简称为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隔离吗？）


开发特性：

明白了这些基本概念后再来看看开发的特性。根据以上得知开发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)



stored procedure feathures:   parameter modes:

3种类型的参数：

1)IN :传入数据到stored procedure

2)OUT: stored procedure 返回数据

3)INOUT: 传入的那部分数据，在执行过程中被返回数据覆盖

result sets:

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


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


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

downup

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


开发：

最后终于来到了真正的开发了，刚才讲到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 useriduserid = userid;   set today = current date;  

//同时给多个变量赋值



set pay =10000,bonus = 1500;   set (pay,bonus) = (10000,1500);   set (pay,bonus) = select (pay,bonus) from employee where empno = 5;   >>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运算符，如：



if (salary between 10000 and 90000) then...   if (deptno in ('a00','b01')) then..   if (exist (select * from employee)) then...   if (select count(*) from employee)>0) then..   >>while   label:   while condition do   ...sql pl ..   end while lable;   
label可选


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


格式：



label:   for row_label as select satement do   ..sql pl..  

end for label;//label可选


例子：



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

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



check_bonus:   for emp as select * from employee do   if(emp.bonus>10000) then   set total_bonustotal_bonus = total_bonus +emp.bonus;   else   iterate check_bonus;   end if;   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语句影响的记录数。


get diagnostics variable = row_count;