存储过程快速入门.docx

存储过程快速入门.docx

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

存储过程快速入门

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个或多个参数，参数列表使用逗号侵害各个参数

参数类型有三种：

l        IN                从客户应用检索值。

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

l        OUT                向客户应用返回值

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

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

即表示不返回结果集。

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

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

l        NOSQL                不能够执行任何SQL语句

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

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

l        MODIFIESSQLDATA        可以执行任何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.2        SQL过程体

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条件[，…]

其有三种处理类型：

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

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

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

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

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

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

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

l        SQLEXCEPTION        标识导致SQLCODE值为负的条件

l        SQLWARNING        警告条件或导致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）+1）intotodayfromadmin.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”

（1）CreateProcedureadmin.BalanceSheetDayly（OutProcStatevarchar（100））

（2）LanguageSQL

（3）------------------------------------------------------------------------

（4）--SQLProcudure

（5）--Jun12,2002

（6）--LastModificationinOct,2002

（7）------------------------------------------------------------------------

（8）P1:

Begin

（9）declarestmtvarchar（1000）;

（10）declarevProcStatevarchar（100）;

（11）declaretodaydate;

（12）declarevTablechar（40）;

（13）declaresqlcodeintdefault0;

（14）declaresqlstatechar（5）default'00000';

（15）declarevActionvarchar（100）;

（16）declarevDateCodeint;

（17）declarevNodeCodechar（4）;

（18）declarevCurrencyCodechar

（2）;

（19）declarevBalSubjItemCodechar（6）;

（20）declarevBaldecimal（16,2）;

（21）declarevLastNodeCodechar（4）;

（22）declarevLastCurrencyCodechar

（2）;

（23）--期收期付款项

（24）declarevBal301decimal（16,2）default0;

（25）--系统内款项

（26）declarevBal302decimal（16,2）default0;

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

（28）declarevBal303decimal（16,2）default0;

（29）--海外分支机构

（30）declarevBal304decimal（16,2）default0;

（31）--拨入营运资金

（32）declarevBal305decimal（16,2）default0;

（33）declareat_endintdefault0;

（34）declarenot_foundconditionforsqlstate'02000';

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

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

（37）declarecurAssetcursorwithholdfor

（38）select

（39）admin.fSubjectBalanceDayly.siNodeCode,

（40）admin.fSubjectBalanceDayly.siCurrencyCode,

（41）admin.dBalanceSubject.siBalSubjItemCode,

（42）sum（admin.fSubjectBalanceDayly.dDebitBal-admin.fSubjectBalanceDayly.dLoanBal）asdBalance

（43）fromadmin.fSubjectBalanceDayly,admin.dBalanceSubject

（44）where

（45）admin.fSubjectBalanceDayly.siSubjectCode=admin.dBalanceSubject.siSubjectCode

（46）groupby

（47）admin.fSubjectBalanceDayly.siNodeCode,

（48）admin.fSubjectBalanceDayly.siCurrencyCode,

（49）admin.dBalanceSubject.siBalSubjItemCode;

（50）declarecontinuehandlerforsqlstate'42704'setvProcState=char（sqlcode）||sqlstate||'Warning';

（51）declarecontinuehandlerforsqlstate'02000'

（52）begin

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

（54）setat_end=1;

（55）end;

（56）declarecontinuehandlerforsqlstate'23502'setvProcState=char（sqlcode）||sqlstate||'Warning';

（57）declareexithandlerforsqlexception

（58）begin

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

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

（61）end;

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

（63）setstmt='deletefromadmin.fSubjectBalanceSheet';

（64）prepareDelStmtfromstmt;

（65）executeDelStmt;

（66）setat_end=0;

（67）setvTable='fSubjectBalanceSheet';

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

（69）opencurAsset;

（70）fetchcurAssetintovNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;

（71）ifat_end<>0thengotoExit1;

（72）endif;

（73）setvDateCode=days（today）-1;

（74）setvLastNodeCode=vNodeCode;

（75）setvLastCurrencyCode=vCurrencyCode;

（76）l1:

（77）loop

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

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

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

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

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

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

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

（85）whileat_end=0andvNodeCode=vLastNodeCodeandvCurrencyCode=vLastCurrencyCodedo

（86）begin

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

（88）whenvBalSubjItemCode='1170'

（89）thenbeginsetvBal302=vBal302+vBal;

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

（91）end;

（92）whenvBalSubjItemCode='1175'

（93）thenbeginsetvBal302=vBal302+vBal;

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

（95）end;

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

（97）whenvBalSubjItemCode='2170'

（98）thenbeginsetvBal302=vBal302+vBal;

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

（100）end;

（101）whenvBalSubjItemCode='2175'

（102）thenbeginsetvBal302=vBal302+vBal;

（103）insertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'02175',-vBal）;

（104）end;

（105）whenvBalSubjItemCodein（'2185','2195','3110','3115','3130','3150','1625','2715'）

（106）thensetvBal302=vBal302+vBal;

（107）whenvBalSubjItemCodein（'1180','2180'）thensetvBal303=vBal303+vBal;

（108）whenvBalSubjItemCodein（'1190','2190'）thensetvBal304=vBal304+vBal;

（109）whenvBalSubjItemCodein（'1199','2199'）thensetvBal305=vBal305+vBal;

（110）whenvBalSubjItemCode='3155'

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

（112）whenvBalSubjItemCodelike'3%'

（113）thenifvBal>0

（114）theninsertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'3'||vBalSubjItemCode,vBal）;

（115）elseinsertintoadmin.fSubjectBalanceSheetvalues（vDateCode,vNodeCode,vCurrencyCode,'4'||vBalSubjItemCode,-vBal）;

（116）endif;

（117）whenvBalSubjItemCode='9670'

（118）thenbegin

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

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

（121）end