DB2存储过程快速入门.docx

DB2存储过程快速入门.docx

《DB2存储过程快速入门.docx》由会员分享，可在线阅读，更多相关《DB2存储过程快速入门.docx（17页珍藏版）》请在冰豆网上搜索。

DB2存储过程快速入门

1.1SQL过程的结构

命名规则：

1、清洗过程名称命名：

PROC_业务主题_目标表（PROC_JY_KJYRLJB交易主题的卡交易日类聚表）

2、函数名称命名：

PROC_业务主题_函数名（PROC_JY_GETYWZL交易主题取得卡业务种类函数）

3、变量命名：

VAR_变量描述（VAR_YWZL业务种类变量）

4、游标命名：

CUR_游标描述（CUR_KJYB对卡交易表进行游标处理）

语法：

CREATEPROCEDURE过程名称

（参数列表

DYNAMICRESULTSETS结果集数量

是否允许SQL

LANGUAGESQL

BEGIN

SQL过程体

END

范例“资产负债.sql”中

第1行：

CreateProcedureadmin.BalanceSheetDayly定义了过程名称

参数列表为OutProcStatevarchar（100

其定义SQL过程从客户应用获取，或返回客户应用的0个或多个参数，参数列表使用逗号侵害各个参数

参数类型有三种：

lIN从客户应用检索值。

其不能够在SQL过程体中修改

lOUT向客户应用返回值

lINOUT从客户应用检索值，并返回值

省略了结果集数量的定义，default为0。

即表示不返回结果集。

省略了是否允许SQL的说明。

其值指出了存储过程是否会使用SQL语句，如果使用，其类型如何：

lNOSQL不能够执行任何SQL语句

lCOTAINSSQL可以执行不会读取SQL数据，也不会修改SQL数据的SQL语句

lREADSSQLDATA可以包含不会修改SQL数据的SQL语句

lMODIFIESSQLDATA可以执行任何SQL语句，除了不能够在存储过程中支持的语句以外。

第3~7行，为注释，标明此为SQL过程，编写、最后修改时间。

注释为“--”开始的行。

第8行和最后一行199共同标识出SQL过程体

过程体存储过程的逻辑内容，包括变量声明、条件控制、流控制语句、以及通过SQL

语句处理数据的过程。

另例：

CREATEPROCEDUREbbgs_to_testinfo（INvar0INTEGER,outretINTEGER,outret_strvarchar（5,OUTerrorLabelCHAR（32

SPECIFICbbgs_to_testinfo

RESULTSETS1

LANGUAGESQL

此定义为创建名为bbgs_to_testinfo的存储过程。

它有4个参数：

第一个IN参数是INTEGER类型，第一个OUT参数是INTEGER类型，第二个OUT参数是VARCHAR（5类型，第三个OUT参数是CHAR（32类型。

指定的别名为bbgs_to_testinfo，将返回一个结果集。

1.2SQL过程体

1.2.1声明、设置变量

第9~61行。

必须在SQL过程体的第一部分中声明变量。

必须指定惟一的标识符，声明SQL数据类型、并且可以先把指定变量的初始值。

变量声明的语法如下：

DECLARE标识符SQL数据类型[DEFAULT默认值]

1、SQLCODE、SQLSTATE变量

13~14行。

用于在SQL过程中处理错误和排错问题。

它们的值代表了SQL过程体中最后使用的SQL过程体中最后使用的SQL语句的返回值。

2、游标申明

37~49行。

declarecurAssetcursorwithholdfor

3、条件处理器

50~61行。

当SQL语句返回超过00000的SQLSTATE值时，会产生一个条件，表示出现了错误、数据没有找到或者警告。

条件处理器可以决定SQL过程将如何响应一个或多个已定义的条件或预定义条件组。

其语法如下：

DECALRE数据类型HANDLERFOR条件[，…]

其有三种处理类型：

lCONTINUE处理器操作完成后，继续执行产生这个条件的语句之后的下一条语句。

lEXIT处理器操作完成后，SQL过程将终止，并将控制返回给调用者。

lUNDO处理器操作执行之前，DB2将回滚SQL过程中执行的SQL操作。

完成后，SQL过程将终止，并将控制返回给调用者。

其预定义了3个类的条件：

lNOTFOUND标识导致SQLCODE值为+100或SQLSTATE值为02000的条件。

一般在使用SELECT语句时出现。

lSQLEXCEPTION标识导致SQLCODE值为负的条件

lSQLWARNING警告条件或导致SQLCODE>100的条件

1.2.2控制结构流

常用的结构：

1、SET

为输出参数或者SQL变量赋值。

例如：

setat_end=0;（66行

setvProcState=char（0||'00000Success';（164行

setvBal302=vBal302+vBal;（89行

2、IF

IF条d

…

ELSEIF条件2THEN

…

ELSE

…

ENDIF;

3、LOOP

多次执行一个代码块，直到LEAVE（跳出循环、ITERATE（跳至标签循环的开始、GOTO（跳至指定标签块。

例如：

76~161行，请注意其中的76、77、152、153、161行，它们与51~55行的条件处理器一同控制着循环流程。

4、WHILE

WHILE条件DO

…

ENDWHILE;

5、CASE

基于一个或多个条件的评估选择执行路径，WHEN子句将直接值与CASE表达式中规定的变量进行比较。

例如：

87~128行

1.2.3在SQL过程体中使用SQL语句

1、直接使用

过程体中可以直接使用SQL语句。

例如：

第62行：

selectdate（days（admin.SystemState.dtTransDate+1intotodayfromadmin.SystemState;

查询得到的结果将通过into的方式赋给SQL过程变量today。

第90行：

insertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'01170',vBal;

向表插入一条记录。

2、使用结果集

具体步骤如下：

1声明游标

语法：

DECLARE游标名CURSORWITHHOLDFOR

SELECT语句；

WITHHOLD表示打开游标保留打开状态，且这个游标定位在结果表的下一逻辑行的前面；还可以用WITHRETURN指定此游标用作存储过程中返回的结果集合。

例如：

37~49行，declarecurAssetcursorwithholdfor…；定义了curAsset游标。

2为结果集打开游标

打开游标，以便于它可以用于从其结果表中提取行。

语法：

OPEN游标名；

例如：

69行，opencurAsset;

3从结果集中取得查询数据

使用FETCH语句，它将游标定位在其结果表中的下一行上，并给主机变量分配这个行的值。

语法：

FETCH游标名INTO主机变量

例如：

70~72行

FETCHcurAssetINTOvNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;

ifat_end<>0thengotoExit1;

endif;

此例应与51~55行的条件处理器一起理解。

当正常取到数据时，将游标的结果表中的数据赋给INTO后的主机变量列表中的对应变量。

如果游标定位于结果表的未端（结果表为空时也是时，将产生一个NOTFOUND条件，根据51~55行的声明，将在处理器操作中改变vProcState和at_end的值。

处理器操作完成后，继续处理下面的语句71行。

4关闭结果集

当游标使用完后，需要将其关闭。

语法：

CLOSE游标名

例如：

162行，closecurAsset;

如果CREATEPROCEDURE语句中的定义的返回结果集个数不为0，且此结果集需要被返回给调用者时，则不能够被关闭。

3、使用动态SQL语句

具体步骤如下：

1通过DECLARE语句声明VARCHAR类型变量作为SQL语句字符串

例如：

第9行，declarestmtvarchar（1000;

declarestmt1varchar（1000;

2给SQL语句字符串赋值。

用户不能够在语句字符串中直接包含变量，相反，必须使用？

作为在语句中所使用的变量的参数标记符。

例如：

第63行，setstmt='deletefromadmin.fSubjectBalanceSheet';

setstmt1='insertintotest_infovalues（?

?

';

3使用PREPARE语句从语句字符串中生成经过准备的语句。

例如：

第64行，prepareDelStmtfromstmt;

preparepsfromstmt;

4使用EXECUTE语句执行经准备的语句。

如SQL语句字符串包含了参数标记符，可以利用USING子句将变量值替代参数标记符。

例如：

第65行，executeDelStmt;

executepsusingv_a1,v_a2;

1SQL存储过程说明

请参见参考程序“资产负债.sql”

（1CreateProcedureadmin.BalanceSheetDayly（OutProcStatevarchar（100

（2LanguageSQL

（3------------------------------------------------------------------------

（4--SQLProcudure

（5--Jun12,2002

（6--LastModificationinOct,2002

（7------------------------------------------------------------------------

（8P1:

Begin

（9declarestmtvarchar（1000;

（10declarevProcStatevarchar（100;

（11declaretodaydate;

（12declarevTablechar（40;

（13declaresqlcodeintdefault0;

（14declaresqlstatechar（5default'00000';

（15declarevActionvarchar（100;

（16declarevDateCodeint;

（17declarevNodeCodechar（4;

（18declarevCurrencyCodechar（2;

（19declarevBalSubjItemCodechar（6;

（20declarevBaldecimal（16,2;

（21declarevLastNodeCodechar（4;

（22declarevLastCurrencyCodechar（2;

（23--期收期付款项

（24declarevBal301decimal（16,2default0;

（25--系统内款项

（26declarevBal302decimal（16,2default0;

（27--存放海外分支机构款项

（28declarevBal303decimal（16,2default0;

（29--海外分支机构

（30declarevBal304decimal（16,2default0;

（31--拨入营运资金

（32declarevBal305decimal（16,2default0;

（33declareat_endintdefault0;

（34declarenot_foundconditionforsqlstate'02000';

（35--得到资产负债表统计口径的科目余额

（36--科目余额日表+资产负债科目表

（37declarecurAssetcursorwithholdfor

（38select

（39admin.fSubjectBalanceDayly.siNodeCode,

（40admin.fSubjectBalanceDayly.siCurrencyCode,

（41admin.dBalanceSubject.siBalSubjItemCode,

（42

sum（admin.fSubjectBalanceDayly.dDebitBal-admin.fSubjectBalanceDayly.dLoanBalasdBalance

（43fromadmin.fSubjectBalanceDayly,admin.dBalanceSubject

（44where

（45

admin.fSubjectBalanceDayly.siSubjectCode=admin.dBalanceSubject.siSubjectCode

（46groupby

（47admin.fSubjectBalanceDayly.siNodeCode,

（48admin.fSubjectBalanceDayly.siCurrencyCode,

（49admin.dBalanceSubject.siBalSubjItemCode;

（50declarecontinuehandlerforsqlstate'42704'set

vProcState=char（sqlcode||sqlstate||'Warning';

（51declarecontinuehandlerforsqlstate'02000'

（52begin

（53setvProcState=char（sqlcode||sqlstate||'Warning';

（54setat_end=1;

（55end;

（56declarecontinuehandlerforsqlstate'23502'set

vProcState=char（sqlcode||sqlstate||'Warning';

（57declareexithandlerforsqlexception

（58begin

（59setvProcState=char（sqlcode||sqlstate||'Failed';

（60insertintoadmin.ProcStatevalues（'BalanceSheetDayly',GetToday（,currenttimestamp,vTable,substr（vProcState,18,10,int（substr（vProcState,1,11,substr（vProcState,12,5,vAction;

（61end;

（62selectdate（days（admin.SystemState.dtTransDate+1intotodayfrom

admin.SystemState;

（63setstmt='deletefromadmin.fSubjectBalanceSheet';

（64prepareDelStmtfromstmt;

（65executeDelStmt;

（66setat_end=0;

（67setvTable='fSubjectBalanceSheet';

（68setvAction='重新生成资产负债科目余额表';

（69opencurAsset;

（70fetchcurAssetintovNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;

（71ifat_end<>0thengotoExit1;

（72endif;

（73setvDateCode=days（today-1;

（74setvLastNodeCode=vNodeCode;

（75setvLastCurrencyCode=vCurrencyCode;

（76l1:

（77loop

（78--轧差项以“借-贷”判断余额方向

（79--3155以“贷-借”入表

（80--其他3字头科目以“借-贷”判断余额方向

（81--9670以“贷-借”入表

（82--6999以“贷-借”入表

（83--其他1字头科目、6字头科目以“借-贷”入表

（84--其他科目以“贷-借”入表

（85whileat_end=0andvNodeCode=vLastNodeCodeandvCurrencyCode=vLastCurrencyCodedo

（86begin

（87casewhenvBalSubjItemCodein（'1620','2710'thensetvBal301=vBal301+vBal;

（88whenvBalSubjItemCode='1170'

（89thenbeginsetvBal302=vBal302+vBal;

（90insertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'01170',vBal;

（91end;

（92whenvBalSubjItemCode='1175'

（93thenbeginsetvBal302=vBal302+vBal;

（94insertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'01175',vBal;

（95end;

（96whenvBalSubjItemCode='1185'thensetvBal302=vBal302+vBal;

（97whenvBalSubjItemCode='2170'

（98thenbeginsetvBal302=vBal302+vBal;

（99insertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'02170',-vBal;

（100end;

（101whenvBalSubjItemCode='2175'

（102thenbeginsetvBal302=vBal302+vBal;

（103insertintoadmin.fSubjectBalanceSheet

values（vDateCode,vNodeCode,vCurrencyCode,'02175',-vBal;

（104end;

（105whenvBalSubjItemCodein

（'2185','2195','3110','3115','3130','3150','1625','2715'

（106thensetvBal302=vBal302+vBal;

（107whenvBalSubjItemCodein（'1180','2180'thenset

vBal303=vBal303+vBal;

（108whenvBalSubjItemCodein（'1190','2190'thenset

vBal304=vBal304+vBal;

（109whenvBalSubjItemCodein（'1199','2199'thenset

vBal305=vBal305+vBal;

（111theninsertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'83155',-vBal;

（112whenvBalSubjItemCodelike'3%'

（113thenifvBal>0

（114theninsertintoadmin.fSubjectBalanceSheet

values（vDateCode,vNodeCode,vCurrencyCode,'3'||vBalSubjItemCode,vBal;（115elseinsertintoadmin.fSubjectBalanceSheet

values（vDateCode,vNodeCode,vCurrencyCode,'4'||vBalSubjItemCode,-vBal;（116endif;

（117whenvBalSubjItemCode='9670'

（118thenbegin

（119insertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'59670',-vBal;

（120insertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'69670',-vBal;

（121end;

（123theninsertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'76999',-vBal;

（124elseifvBalSubjItemCodelike'1%'orvBalSubjItemCodelike'6%'

（125theninsertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'1'||vBalSubjItemCode,vBal;

（126elseinsertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'2'||vBalSubjItemCode,-vBal;

（127endif;

（128endcase;

（129fetchcurAssetintovNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;（130end;

（131endwhile;

（132i