DB2存储过程.docx
- 文档编号:3733000
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:23
- 大小:56.53KB
DB2存储过程.docx
《DB2存储过程.docx》由会员分享,可在线阅读,更多相关《DB2存储过程.docx(23页珍藏版)》请在冰豆网上搜索。
DB2存储过程
学习DB29.5SQLProceduralLanguage,包括变量、条件、处理程序声明、控制流和迭代语句以及错误处理机制。
您将:
∙学习SQLPL的基本要素。
∙理解如何声明变量、条件和处理程序。
∙学习控制流语句。
∙学习游标处理和如何返回结果集。
∙理解错误处理机制。
Sql代码
1..-,-----------------.
2.V|
3.|--DECLARE----SQL-variable-name-+------------------------------->
4.
5..-DEFAULTNULL------.
6.>--+-data-type--+-------------------+-+-------------------------|
7.|'-DEFAULT--constant-'|
8.
Sql代码
1..-,-----------------.
2.V|
3.|--DECLARE----SQL-variable-name-+------------------------------->
4.
5..-DEFAULTNULL------.
6.>--+-data-type--+-------------------+-+-------------------------|
7.|'-DEFAULT--constant-'|
8.
.-,-----------------.
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.>>-CREATETYPE—array-type-name--AS--|data-type|--ARRAY--[---------->
2.
3..-2147483647-------.
4.>--+------------------+--]-------------------------------------><
5.'-integer-constant-'
Sql代码
1.>>-CREATETYPE—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.CREATETYPEnumbersasINTEGERARRAY[100];
2.CREATETYPEnamesasVARCHAR(30)ARRAY[];
3.CREATETYPEMYSCHEMA.totalcompasDECIMAL(12,2)ARRAY[];
Sql代码
1.CREATETYPEnumbersasINTEGERARRAY[100];
2.CREATETYPEnamesasVARCHAR(30)ARRAY[];
3.CREATETYPEMYSCHEMA.totalcompasDECIMAL(12,2)ARRAY[];
CREATETYPEnumbersasINTEGERARRAY[100];
CREATETYPEnamesasVARCHAR(30)ARRAY[];
CREATETYPEMYSCHEMA.totalcompasDECIMAL(12,2)ARRAY[];
请注意,整数“constant”指定数组的最大基数,它是可选的。
数组元素可以通过ARRAY-VARIABLE(subindex)来引用,其中subindex必须介于1到数组的基数之间。
现在可以在SQL过程中使用这个数据类型:
清单3.在过程中使用数组数据类型
Sql代码
1.CREATEPROCEDUREPROC_VARRAY_test(outmynamesnames)
2.BEGIN
3.DECLAREv_pnumbnumbers;
4.SETv_pnumb=ARRAY[1,2,3,5,7,11];
5.SETmynames
(1)=’MARINA’;
6.
7.…
8.END
Sql代码
1.CREATEPROCEDUREPROC_VARRAY_test(outmynamesnames)
2.BEGIN
3.DECLAREv_pnumbnumbers;
4.SETv_pnumb=ARRAY[1,2,3,5,7,11];
5.SETmynames
(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中支持两种类型的条件语句—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语句,以根据一个条件的状态实现逻辑的分支:
∙simpleCASE语句用于根据一个字面值进入某个逻辑。
∙searchedCASE语句用于根据一个表达式的值进入某个逻辑。
清单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
oSQLstatements;
oLEAVEL1;
oENDLOOPL1;
∙WHILE循环--进入前检查条件
oWHILEcondition
oDO
oSQLstatements
oENDWHILE;
∙REPEAT循环--退出前检查条件
oREPEAT
oSQLstatements;
oUNTILcondition
oENDREPEAT;
∙FOR循环--结果集上的隐式循环
oFORloop_nameAS
oSELECT…FROM
oDO
oSQLstatements;
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
请注意,最后一个过程没有打开游标、从游标中取数据或关闭游标—所有这些都是由FOR循环语句隐式进行的。
而且,可以引用循环中隐式地获取的值,使用循环名称限定列(例如dept_loop.lastname)—而不必使用本地变量来存储这些值。
在本教程中,您学习了用于编写过程、用户定义函数和触发器的SQLProceduralLanguage。
您学习了SQLProcedureLanguage的所有基本要素,包括变量声明和赋值、语法和使用以及用于控制过程逻辑的流程的条件语句和迭代语句。
您还学习了如何使用错误处理和结果集。
这使您能够构建可集成到数据库应用程序中的定制的、复杂的业务逻辑。
异常处理机制
DECLARE有名称的条件
SQLPL允许为给定的SQLSTATE声明用户命名的条件,以用于之后的错误处理。
条件名称在整个复合语句中必须是惟一的,并且只能在声明它的复合语句中引用它。
清单16显示了声明一个有名称的条件的语法。
清单16.声明一个有名称的条件的语法
|--DECLARE--condition-name
--CONDITION--FOR---------------------->
.-VALUE-.
.-SQLSTATE--+-------+-.
>--+---------------------+--string-constant
---------------------|
下面是条件声明的例子:
DECLAREFOREIGN_KEY_VIOLATIONCONDITIONFORSQLSTATE‘23503’;
DECLAREoverflowCONDITIONFORSQLSTATE'22003';
DECLARE条件处理程序
如果发生一个错误,存储过程的行为是根据条件处理程序来决定的。
在一个存储过程中,可以为一个普通的或有名称的条件和特定的SQLSTATE声明一个或多个条件处理程序。
当一个SQL语句产生一个SQLEXCEPTION或SQLWARNING(SQLCODE<>0)时,控制被转移到为一个声明的处理程序中,以获取普通的异常或特定的SQLSTATE值。
清单17显示了受支持的处理程序声明的语法。
清单17.处理程序声明的语法
|--DECLARE--+-CONTINUE-+--HANDLER--FOR-------------------------->
+-EXIT-----+
'-UNDO-----'
>--+-specific-condition-value
-+--|SQL-procedure-statement|----|
'-general-condition-value
--'
WHEREspecific-condition-value
.-,----------------------------------------.
V.-VALUE-.|
|----+-SQLSTATE--+-------+--string-constant
-+-+-----------------|
'-condition-name
-----------------------'
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB2 存储 过程