DB2存储过程基础详解.docx
《DB2存储过程基础详解.docx》由会员分享，可在线阅读，更多相关《DB2存储过程基础详解.docx（20页珍藏版）》请在冰豆网上搜索。
DB2存储过程基础详解
DB2存储过程-基础详解
2010-12-20来源：
网络
简介
DB2SQLProceduralLanguage（SQLPL）是SQLPersistentStoredModule语言标准的一个子集。
该标准结合了SQL访问数据的方便性和编程语言的流控制。
通过SQLPL当前的语句集合和语言特性，可以用SQL开发综合的、高级的程序，例如函数、存储过程和触发器。
这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。
SQLPL支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。
这些话题将在本教程中讨论。
变量声明
SQL过程允许使用本地变量赋予和获取SQL值，以支持所有SQL逻辑。
在SQL过程中，在代码中使用本地变量之前要先进行声明。
清单1中的图演示了变量声明的语法：
清单1.变量声明的语法
.-,-----------------.
V|
|--DECLARE----SQL-variable-name-+------------------------------->
.-DEFAULTNULL------.
>--+-data-type--+-------------------+-+-------------------------|
|'-DEFAULT--constant-'|
SQL-variable-name定义本地变量的名称。
该名称不能与其他变量或参数名称相同，也不能与列名相同。
图1显示了受支持的DB2数据类型：
DEFAULT 值–如果没有指定，在声明时将赋值为NULL。
下面是变量声明的一些例子：
∙DECLAREv_salaryDEC（9,2）DEFAULT0.0;
∙DECLAREv_statuschar（3）DEFAULT‘YES’;
∙DECLAREv_descritionVARCHAR（80）;
∙DECLAREv1,v2INTDEFAULT0;
请注意，从DB2version9.5开始才支持在一个DECLARE语句中声明多个相同数据类型的变量。
数组数据类型
SQL过程从9.5版开始支持数组类型的变量和参数。
要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。
数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。
DB2支持以下创建数组数据类型的语法：
清单2.创建数组数据类型的语法
Sql代码
1.>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
2.
3. .-2147483647-------.
4.>--+------------------+--]-------------------------------------><
5. '-integer-constant-'
>>-CREATETYPE—array-type-name--AS--|data-type|--ARRAY--[---------->
.-2147483647-------.
>--+------------------+--]-------------------------------------><
'-integer-constant-'
数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。
LONGVARCHAR、LONGVARGRPAHIC、XML和用户定义类型不能作为数组元素的数据类型。
下面是数组类型的例子：
Sql代码
1.CREATE TYPE numbers as INTEGER ARRAY[100];
2.CREATE TYPE names as VARCHAR（30） ARRAY[];
3.CREATE TYPE MYSCHEMA.totalcomp as DECIMAL（12,2） ARRAY[];
CREATETYPEnumbersasINTEGERARRAY[100];
CREATETYPEnamesasVARCHAR（30）ARRAY[];
CREATETYPEMYSCHEMA.totalcompasDECIMAL（12,2）ARRAY[];
请注意，整数“constant”指定数组的最大基数，它是可选的。
数组元素可以通过ARRAY-VARIABLE（subindex）来引用，其中subindex必须介于1到数组的基数之间。
现在可以在SQL过程中使用这个数据类型：
清单3.在过程中使用数组数据类型
Sql代码
1.CREATE PROCEDURE PROC_VARRAY_test （out mynames names）
2. BEGIN
3.DECLARE v_pnumb numbers;
4.SET v_pnumb = ARRAY[1,2,3,5,7,11];
5.SET mynames
（1） =’MARINA’;
6.
7.…
8.END
CREATEPROCEDUREPROC_VARRAY_test（outmynamesnames）
BEGIN
DECLAREv_pnumbnumbers;
SETv_pnumb=ARRAY[1,2,3,5,7,11];
SETmynames
（1）=’MARINA’;
…
END
DB2支持一些操作数组的方法。
例如，函数CARDINALITY（myarray）返回一个数组中元素的个数。
赋值
SQLPL提供了SET语句来为变量和数组元素赋值。
下面是一个SET语句的简化的语法：
SETvariable_name=value/expression/NULL;
这个变量名可以是一个本地变量、全局变量或数组元素的名称。
下面是一些例子：
清单4.SET语句的例子
SETvar1=10;
SETtotal=（selectsum（c1）fromT1）;
SETvar2=POSSTR（‘MYTEST’,’TEST’）;
SETv_numb（10）=20;--assignvalueof20tothe10th
element
ofthearrayv_numb
SETv_numb=ARRAY[1,2,3,4];--filluparraywithvalues
为变量赋值的其他方法有：
VALUESINTO
SELECT（orFETCH）INTO
下面的例子演示了这些方法的使用：
清单5.VALUEINTO和SELECTINTO的例子
VALUES2INTOv1;
VALUES‘TEST’INTOvar2;
SELECTSUM（c1）INTOvar1FROMT1;
SELECTPOSSTR（‘MYTEST’,’TEST’）INTOv1FROMSYSIBM.SYSDUMMY1;
专用寄存器
专用寄存器（specialregister） 是DBA定义的一个存储块，供一个应用程序过程使用。
寄存器中的值可以在SQL语句或SQLPL语句中访问和引用。
在IBMDB2databaseforLinux,UNIX,andWindowsInformationCenter可以找到所有的专用寄存器。
最常用的专用寄存器有：
∙CURRENTDATE
∙CURRENTTIME
∙CURRENTTIMESTAMP
∙CURRENTUSER
∙CURRENTPATH
所有这些寄存器都可以通过在名称中加下划线来引用。
例如，CURRENT_DATE。
下面的过程返回当前日期和时间：
清单6.返回当前日期和时间的过程
CREATEPROCEDUREget_datetime（outcdatedate,outctimetime）
P1:
BEGIN
VALUESCURRENTDATEINTOcdate;
VALUESCURRENTTIMEINTOctime;
ENDP1
执行后，该过程返回：
NameInputOutput
cdate2008-08-28
ctime13:
47:
41
有些专用寄存器的值可以通过SET语句来更新。
例如，为了更新正在访问的模式，需要像下面这样更改专用寄存器CURRENTSCHEMA。
SETCURRENT_SCHEMA=MYSCHEMA
若要更改默认函数路径，则需要更新专用寄存器CURRENTPATH。
游标
声明
SQLPL提供DECLAREcursor语句来定义一个游标，并提供其他语句来支持返回其他结果集和游标处理。
下面是游标声明的语法：
清单7.游标声明的语法
>>-DECLARE--cursor-name
--CURSOR---------->
>--FOR--+-select-statement
-+-------------><
.-WITHOUTHOLD-.
|--+--------------+---------------------------------------------|
'-WITHHOLD----'
.-WITHOUTRETURN-------------.
|--+----------------------------+-------------------------------|
|.-TOCALLER-.|
'-WITHRETURN--+-----------+-'
'-TOCLIENT-'
Select-statement 是一条有效的SQLSELECT语句。
可以指定FORUPDATE子句，以便将游标用于定位更新或删除。
WITHOUTHOLD/WITHHOLD 选项定义COMMIT操作之后的游标状态（open/close）。
默认情况下为WITHOUTHOLD。
如果使用了WITHHOLD选项定义一个游标，那么在COMMIT操作之后，该游标保持OPEN状态。
在ROLLBACK操作之后，所有游标都将被关闭。
下面是一个显式声明游标的例子，它可以用于过程中后面的迭代处理：
清单8.游标声明的例子
DECLAREmycur1CURSOR
FORSELECTe.empno,e.lastname,e.job
FROMemployeee,departmentd
WHEREe.workdept=d.deptno
ANDdeptname=’PLANNING’;
虽然SQL语句不能包含参数占位符，但是它可以引用在游标之前声明的本地变量。
例如：
清单9.使用本地变量的游标声明
DECLAREv_deptCHAR（3）DEAFULT‘‘;
DECLAREmyres_setCURSOR
FORSELECTempno,lastname,job,salary,comm.
FROMemployee
WHEREworkdept=v_dept;
游标和结果集
在SQL过程中，除了迭代结果集中的行以外，游标还可以做更多的事情。
游标还可用于将结果集返回给调用程序或其他过程。
∙WITHOUTRETURN/WITHreturn 选项指定游标的结果表是否用于作为从一个过程中返回的结果集。
∙WITHRETURNTOCALLER 选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。
这是默认选项。
∙WITHRETURNTOCLIENT 选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。
若要从一个过程中返回结果集，需要：
1.创建一个过程，创建时指定DYNAMICRESULTSETS子句。
2.声明游标，声明时指定WITHRETURN子句。
3.打开该游标，并使之保持open状态。
如果关闭该游标，则结果集将不能返回给调用者应用程序。
清单10演示了一个游标的声明，该游标从一个过程中返回一个结果集：
清单10.返回一个结果集的游标的声明
CREATEPROCEDUREemp_from_dept（）
DYNAMICRESULTSETS1
P1:
BEGIN
DECLAREc_emp_deptCURSORWITHRETURN
FORSELECTempno,lastname,job,salary,comm.
FROMemployee
WHEREworkdept=‘E21’;
OPENc_emp_dept;
ENDP1
游标处理
为了在一个过程中处理一个游标的结果，需要做以下事情：
1.在存储过程块的开头部分DECLARE游标。
2.打开该游标。
3.将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的FOR语句中将对此加以解释）。
4.关闭该游标。
（注意：
如果现在不关闭游标，当过程终止时将隐式地关闭游标）。
条件语句
SQLPL中支持两种类型的条件语句—IF语句和CASE语句。
IF语句
通过IF语句可以根据一个条件的状态来实现逻辑的分支。
IF语句支持使用可选的 ELSEIF 子句和默认的 ELSE 子句。
ENDIF 子句是必需的，它用于表明IF语句的结束。
清单11展示了一个示例IF语句。
清单11.IF语句示例
IFyears_of_serv>30THEN
SETgl_sal_increase=15000;
ELSEIFyears_of_serv>20THEN
SETgl_sal_increase=12000;
ELSE
SETgl_sal_increase=10000;
ENDIF;
CASE语句
SQLPL支持两种类型的CASE语句，以根据一个条件的状态实现逻辑的分支：
∙simple CASE语句用于根据一个字面值进入某个逻辑。
∙searched CASE语句用于根据一个表达式的值进入某个逻辑。
清单12显示了使用searchedCASE语句的一个存储过程的例子。
清单12.使用searchedCASE语句的存储过程
CREATEPROCEDUREsal_increase_lim1（empidCHAR（6））
BEGIN
DECLAREyears_of_servINTDEFAULT0;
DECLAREv_incr_rateDEC（9,2）DEFAULT0.0;
SELECTYEAR（CURRENTDATE）-YEAR（hiredate）
INTOyears_of_serv
FROMempl1
WHEREempno=empid;
CASE
WHENyears_of_serv>30THEN
SETv_incr_rate=0.08;
WHENyears_of_serv>20THEN
SETv_incr_rate=0.07;
WHENyears_of_serv>10THEN
SETv_incr_rate=0.05;
ELSE
SETv_incr_rate=0.04;
ENDCASE;
UPDATEempl1
SETsalary=salary+salary*v_incr_rate
WHEREempno=empid;
END
迭代语句
SQLPL支持一些重复执行某个逻辑的方法，包括简单的LOOP、WHILE循环、REPEAT循环和FOR循环：
∙LOOP循环 --简单的循环
oL1:
LOOP
o SQLstatements;
o LEAVEL1;
oENDLOOPL1;
∙WHILE循环 --进入前检查条件
oWHILE condition
oDO
o SQLstatements
oENDWHILE;
∙REPEAT循环 --退出前检查条件
oREPEAT
o SQLstatements;
o UNTIL condition
oENDREPEAT;
∙FOR循环 --结果集上的隐式循环
oFOR loop_name AS
o SELECT…FROM
oDO
o SQLstatements;
oENDFOR;
请注意，FOR语句不同于其他的迭代语句，因为它用于迭代一个定义好的结果集中的行。
为了演示这些循环技巧的使用，我们来编写一个过程，该过程从一个EMPLOYEE表中获取每个雇员的姓氏、工作年限和年龄，并将其插入到新表REPORT_INFO_DEPT中，这些信息分别被声明为lnamevarchar（15）、hiredatedate和birthdatedate。
请注意，使用一个简单的SQL语句也可以做同样的事情，但是在这个例子中我们使用3种不同的循环语句。
清单13.简单的循环例子
CREATEPROCEDURELEAVE_LOOP（DEPTINchar（3）,OUTp_counterINTEGER）
Ll:
BEGIN
DECLAREv_at_end,v_counterINTEGERDEFAULT0;
DECLAREv_lastnameVARCHAR（15）;
DECLAREv_birthd,v_hiredDATE;
DECLAREc1CURSOR
FORSELECTlastname,hiredate,birthdateFROMemployee
WHEREWORKDEPT=deptin;
DECLARECONTINUEHANDLERFORNOTFOUNDSETv_at_end=1;
OPENc1;
FETCH_LOOP:
LOOP
FETCHc1INTOv_lastname,v_hired,v_birthd;
IFv_at_end<>0THEN--loopuntillastrowofthecursor
LEAVEFETCH_LOOP;
ENDIF;
SETv_counter=v_counter+1;
INSERTINTOREPORT_INFO_DEPT
values（v_lastname,v_hired,v_birthd）;
ENDLOOPFETCH_LOOP;
SETp_counter=v_counter;
ENDLl
现在，我们使用WHILE循环语句来做同样的事情。
清单14.WHILE循环的例子
CREATEPROCEDUREDEPT_REPT（DEPTINchar（3）,OUTp_counterINTEGER）
Pl:
BEGIN
DECLAREv_at_end,v_counterINTEGERDEFAULT0;
DECLAREv_lastnameVARCHAR（15）;
DECLAREv_birthd,v_hiredDATE;
DECLAREc1CURSOR
FORSELECTlastname,hiredate,birthdateFROMemployee
WHEREWORKDEPT=deptin;
DECLARECONTINUEHANDLERFORNOTFOUNDSETv_at_end=1;
OPENc1;
FETCHc1INTOv_lastname,v_hired,v_birthd;
WHILE（v_at_end=0）
DO
INSERTINTOREPORT_INFO_DEPT
values（v_lastname,v_hired,v_birthd）;
SETv_counter=v_counter+1;
FETCHc1INTOv_lastname,v_hired,v_birthd;
ENDWHILE;
SETp_counter=v_counter;
ENDP1
REPEAT循环非常类似于WHILE循环，只不过条件是在最后检查的（因此，它实际上是一个UNTIL循环）。
现在，我们使用包含FOR循环语句的一个过程来填充REPORT_INFO_DEPT表。
清单15.FOR循环的例子
CREATEPROCEDUREDEPT_REPT1（DEPTINchar（3）,OUTp_counterINT）
P1:
BEGIN
DECLAREv_counterINTDEFAULT0;
FORdept_loopAS
SELECTlastname,hiredate,birthdateFROMemployee
WHEREWORKDEPT=deptin
DO
INSERTINTOREPORT_INFO_DEPTvalues
（dept_loop.lastname,dept_loop.hiredate,dept_loop.birthdate）;
SETv_counter=v_counter+1;
ENDFOR;
SETp_counter=v_counter;
ENDP1
请注意，最后一个过程没有打开游标、从
升级会员 