DB2存储过程

DB2存储过程-基础详解DB2SQL应用服务器Linux编程学习DB29.5SQLProceduralLanguage，包括变量、条件、处理程序声明、控制流和迭代语句以及错误处理机制。您将：学习SQLPL的基本要素。理解如何声明变量、条件和处理程序。学习控制流语句。学习游标处理和如何返回结果集。理解错误处理机制。简介DB2SQLProceduralLanguage（SQLPL）是SQLPersistentStoredModule语言标准的一个子集。该标准结合了SQL访问数据的方便性和编程语言的流控制。通过SQLPL当前的语句集合和语言特性，可以用SQL开发综合的、高级的程序，例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。SQLPL支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。这些话题将在本教程中讨论。变量声明SQL过程允许使用本地变量赋予和获取SQL值，以支持所有SQL逻辑。在SQL过程中，在代码中使用本地变量之前要先进行声明。清单1中的图演示了变量声明的语法：清单1.变量声明的语法Sql代码收藏代码.-,-----------------.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代码收藏代码-CREATETYPE—array-type-name--AS--|data-type|--ARRAY--[----------.-2147483647-------.--+------------------+--]-------------------------------------'-integer-constant-'数组类型的名称应该用模式加以限定，并且在当前服务器上应该是惟一的。LONGVARCHAR、LONGVARGRPAHIC、XML和用户定义类型不能作为数组元素的数据类型。下面是数组类型的例子：Sql代码收藏代码CREATETYPEnumbersasINTEGERARRAY[100];CREATETYPEnamesasVARCHAR(30)ARRAY[];CREATETYPEMYSCHEMA.totalcompasDECIMAL(12,2)ARRAY[];请注意，整数“constant”指定数组的最大基数，它是可选的。数组元素可以通过ARRAY-VARIABLE(subindex)来引用，其中subindex必须介于1到数组的基数之间。现在可以在SQL过程中使用这个数据类型：清单3.在过程中使用数组数据类型Sql代码收藏代码CREATEPROCEDUREPROC_VARRAY_test(outmynamesnames)BEGINDECLAREv_pnumbnumbers;SETv_pnumb=ARRAY[1,2,3,5,7,11];SETmynames(1)=’MARINA’;…ENDDB2支持一些操作数组的方法。例如，函数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;--assignvalueof20tothe10thelementofthearrayv_numbSETv_numb=ARRAY[1,2,3,4];--filluparraywithvalues为变量赋值的其他方法有：VALUESINTOSELECT(orFETCH)INTO下面的例子演示了这些方法的使用：清单5.VALUEINTO和SELECTINTO的例子VALUES2INTOv1;VALUES‘TEST’INTOvar2;SELECTSUM(c1)INTOvar1FROMT1;SELECTPOSSTR(‘MYTEST’,’TEST’)INTOv1FROMSYSIBM.SYSDUMMY1;专用寄存器专用寄存器（specialregister）是DBA定义的一个存储块，供一个应用程序过程使用。寄存器中的值可以在SQL语句或SQLPL语句中访问和引用。在IBMDB2databaseforLinux,UNIX,andWindowsInformationCenter可以找到所有的专用寄存器（参见参考资料）。最常用的专用寄存器有：CURRENTDATECURRENTTIMECURRENTTIMESTAMPCURRENTUSERCURRENTPATH所有这些寄存器都可以通过在名称中加下划线来引用。例如，CURRENT_DATE。下面的过程返回当前日期和时间：清单6.返回当前日期和时间的过程CREATEPROCEDUREget_datetime(outcdatedate,outctimetime)P1:BEGINVALUESCURRENTDATEINTOcdate;VALUESCURRENTTIMEINTOctime;ENDP1执行后，该过程返回：NameInputOutputcdate2008-08-28ctime13: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.游标声明的例子DECLAREmycur1CURSORFORSELECTe.empno,e.lastname,e.jobFROMemployeee,departmentdWHEREe.workdept=d.deptnoANDdeptname=’PLANNING’;虽然SQL语句不能包含参数占位符，但是它可以引用在游标之前声明的本地变量。例如：清单9.使用本地变量的游标声明DECLAREv_deptCHAR(3)DEAFULT‘‘;DECLAREmyres_setCURSORFORSELECTempno,lastname,job,salary,comm.FROMemployeeWHEREworkdept=v_dept;游标和结果集在SQL过程中，除了迭代结果集中的行以外，游标还可以做更多的事情。游标还可用于将结果集返回给调用程序或其他过程。WITHOUTRETURN/WITHreturn选项指定游标的结果表是否用于作为从一个过程中返回的结果集。WITHRETURNTOCALLER选项指定将来自游标的结果集返回给调用者，后者可以是另一个过程或一个客户机应用程序。这是默认选项。WITHRETURNTOCLIENT选项指定将来自游标的结果集返回给客户机应用程序，绕过任何中间的嵌套过程。若要从一个过程中返回结果集，需要：创建一个过程，创建时指定DYNAMICRESULTSETS子句。声明游标，声明时指定WITHRETURN子句。打开该游标，并使之保持open状态。如果关闭该游标，则结果集将不能返回给调用者应用程序。清单10演示了一个游标的声明，该游标从一个过程中返回一个结果集：清单10.返回一个结果集的游标的声明CREATEPROCEDUREemp_from_dept()DYNAMICRESULTSETS1P1:BEGINDECLAREc_emp_deptCURSORWITHRETURNFORSELECTempno,lastname,job,salary,comm.FROMemployeeWHEREworkdept=‘E21’;OPENc_emp_dept;ENDP1游标处理为了在一个过程中处理一个游标的结果，需要做以下事情：在存储过程块的开头部分DECLARE游标。打开该游标。将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的FOR语句中将对此加以解释）。关闭该游标。（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。条件语句SQLPL中支持两种类型的条件语句—IF语句和CASE语句。IF语句通过IF语句可以根据一个条件的状态来实现逻辑的分支。IF语句支持使用可选的ELSEIF子句和默认的ELSE子句。ENDIF子句是必需的，它用于表明IF语句的结束。清单11展示了一个示例IF语句。清单11.IF语句示例IFyears_of_serv30THENSETgl_sal_increase=15000;ELSEIFyears_of_serv20THENSETgl_sal_increase=12000;ELSESETgl_sal_increase=10000;ENDIF;CASE语句SQLPL支持两种类型的CASE语句，以根据一个条件的状态实