存儲過程快速入門

1、1.1SQL過程的結(jié)構(gòu)命名規(guī)則：1、清洗過程名稱命名：PROC_業(yè)務(wù)主題_目標(biāo)表（PROC_JY_KJYRLJB交易主題的卡交易日類聚表）2、函數(shù)名稱命名：PROC_業(yè)務(wù)主題_函數(shù)名（PROC_JY_GETYWZL交易主題取得卡業(yè)務(wù)種類函數(shù)）3、變量命名：VAR_變量描述（VAR_YWZL業(yè)務(wù)種類變量）4、游標(biāo)命名：CUR_游標(biāo)描述（CUR_KJYB對卡交易表進(jìn)行游標(biāo)處理）語法：CREATEPROCEDURE過程名稱（參數(shù)列表）DYNAMICRESULTSETS結(jié)果集數(shù)量是否允許SQLLANGUAGESQLBEGINSQL過程體END范例“資產(chǎn)負(fù)債.sql”中第1行：CreateProcedu

2、readmin.BalanceSheetDayly定義了過程名稱參數(shù)列表為OutProcStatevarchar(100)其定義SQL過程從客戶應(yīng)用獲取，或返回客戶應(yīng)用的0個或多個參數(shù)，參數(shù)列表使用逗號侵害各個參數(shù)參數(shù)類型有三種：lIN從客戶應(yīng)用檢索值。其不能夠在SQL過程體中修改lOUT向客戶應(yīng)用返回值lINOUT從客戶應(yīng)用檢索值，并返回值省略了結(jié)果集數(shù)量的定義，省略了是否允許SQL的說明何：lNOSQLlCOTAINSSQL句lREADSSQLDATAlMODIFIESSQLDATA句以外。第37行，為注釋，標(biāo)明此為default為0。即表示不返回結(jié)果集。o其值指出了存儲過程是否會使用不能

3、夠執(zhí)行任何SQL語句可以執(zhí)行不會讀取SQL數(shù)據(jù),可以包含不會修改SQL數(shù)據(jù)的可以執(zhí)行任何SQL過程，編寫、SQL語句，如果使用，其類型如也不會修改SQL數(shù)據(jù)的SQL語SQL語句SQL語句，除了不能夠在存儲過程中支持的語最后修改時間。注釋為“-”開始的行第8行和最后一行199共同標(biāo)識出SQL過程體條件控制、流控制語句、以及通過SQL過程體存儲過程的邏輯內(nèi)容，包括變量聲明、語句處理數(shù)據(jù)的過程另例:CREATEPROCEDUREbbgs_to_testinfo(INvar0INTEGER,outretINTEGER,outret_strvarchar(5),OUTerrorLabelCHAR(32)

4、SPECIFICbbgs_to_testinfoRESULTSETS1LANGUAGESQL此定義為創(chuàng)建名為bbgs_to_testinfo的存儲過程。它有4個參數(shù)：第一個IN參數(shù)是INTEGER類型，第一個OUT參數(shù)是INTEGER類型，第二個OUT參數(shù)是VARCHAR(5)類型，第三個OUT參數(shù)是CHAR(32)類型。指定的別名為bbgs_to_testinfo,將返回一個結(jié)果集。1.2SQL過程體1.2.1聲明、設(shè)置變量第961行。必須在SQL過程體的第一部分中聲明變量。必須指定惟一的標(biāo)識符，聲明SQL數(shù)據(jù)類型、并且可以先把指定變量的初始值。變量聲明的語法如下：DECLARE標(biāo)識符SQL

5、數(shù)據(jù)類型DEFAULT默認(rèn)值1、SQLCODE、SQLSTATE變量1314行。用于在SQL過程中處理錯誤和排錯問題。它們的值代表了SQL過程體中最后使用的SQL過程體中最后使用的SQL語句的返回值。2、游標(biāo)申明3749行。declarecurAssetcursorwithholdfor3、條件處理器5061行。當(dāng)SQL語句返回超過00000的SQLSTATE值時，會產(chǎn)生一個條件，表示出現(xiàn)了錯誤、數(shù)據(jù)沒有找到或者警告。條件處理器可以決定SQL過程將如何響應(yīng)一個或多個已定義的條件或預(yù)定義條件組。其語法如下：DECALRE數(shù)據(jù)類型HANDLERFOR條件,其有三種處理類型：lCONTINUE處理器

6、操作完成后，繼續(xù)執(zhí)行產(chǎn)生這個條件的語句之后的下一條語句。lEXIT處理器操作完成后，SQL過程將終止，并將控制返回給調(diào)用者。lUNDO處理器操作執(zhí)行之前，DB2將回滾SQL過程中執(zhí)行的SQL操作。完成后，SQL過程將終止，并將控制返回給調(diào)用者。其預(yù)定義了3個類的條件：lNOTFOUND標(biāo)識導(dǎo)致SQLCODE值為+100或SQLSTATE值為02000的條件。一般在使用SELECT語句時出現(xiàn)。lSQLEXCEPTION標(biāo)識導(dǎo)致SQLCODE值為負(fù)的條件lSQLWARNING警告條件或?qū)е耂QLCODE>100的條件1.2.2控制結(jié)構(gòu)流常用的結(jié)構(gòu)：1、SET為輸出參數(shù)或者SQL變量賦值。例如

7、：setat_end=0;（66行）setvProcState=char（0）|'00000Success'（164行）setvBal302=vBal302+vBal;（89行）2、IFIF條dELSEIF條件2THENELSEENDIF;3、LOOP多次執(zhí)行一個代碼塊，直到LEAVE（跳出循環(huán)）、ITERATE（跳至標(biāo)簽循環(huán)的開始）、GOTO（跳至指定標(biāo)簽塊）。例如：76161行，請注意其中的76、77、152、153、161行，它們與5155行的條件處理器一同控制著循環(huán)流程。4、WHILEWHILE條件DOENDWHILE;5、CASE基于一個或多個條件的評估選擇執(zhí)行路徑，

8、WHEN子句將直接值與CASE表達(dá)式中規(guī)定的變量進(jìn)行比較。例如：87128行1.2.3在SQL過程體中使用SQL語句1、直接使用過程體中可以直接使用SQL語句。例如：第62行：selectdate(days(admin.SystemState.dtTransDate)+1)intotodayfromadmin.SystemState;查詢得到的結(jié)果將通過into的方式賦給SQL過程變量today。第90行：insertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'01170',vB

9、al);向表插入一條記錄。使用結(jié)果集具體步驟如下:聲明游標(biāo)語法：DECLARE游標(biāo)名CURSORWITHHOLDFORSELECT語句；WITHHOLD表示打開游標(biāo)保留打開狀態(tài)，且這個游標(biāo)定位在結(jié)果表的下一邏輯行的前面;還可以用WITHRETURN指定此游標(biāo)用作存儲過程中返回的結(jié)果集合。例如：3749行，declarecurAssetcursorwithholdfor；定義了curAsset游標(biāo)。為結(jié)果集打開游標(biāo)打開游標(biāo)，以便于它可以用于從其結(jié)果表中提取行。語法：OPEN游標(biāo)名；例如：69行，opencurAsset;從結(jié)果集中取得查詢數(shù)據(jù)使用FETCH語句，它將游標(biāo)定位在其結(jié)果表中的下一行上

10、，并給主機變量分配這個行的值語法：FETCH游標(biāo)名INTO主機變量例如：7072行FETCHcurAssetINTOvNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;ifat_end<>0thengotoExit1;endif;此例應(yīng)與5155行的條件處理器一起理解。當(dāng)正常取到數(shù)據(jù)時，將游標(biāo)的結(jié)果表中的數(shù)據(jù)賦給INTO后的主機變量列表中的對應(yīng)變量。如果游標(biāo)定位于結(jié)果表的未端(結(jié)果表為空時也是)時，將產(chǎn)生一個NOTFOUND條件，根據(jù)5155行的聲明，將在處理器操作中改變vProcState和at_end的值。處理器操作完成后，繼續(xù)處理下面的

11、語句71行。關(guān)閉結(jié)果集當(dāng)游標(biāo)使用完后，需要將其關(guān)閉。語法：CLOSE游標(biāo)名例如：162行，closecurAsset;如果CREATEPROCEDURE語句中的定義的返回結(jié)果集個數(shù)不為0,且此結(jié)果集需要被返回給調(diào)用者時，則不能夠被關(guān)閉。3、使用動態(tài)SQL語句具體步驟如下：1) 通過DECLARE語句聲明VARCHAR類型變量作為SQL語句字符串例如：第9行，declarestmtvarchar(1000);declarestmtlvarchar(1000);給SQL語句字符串賦值。用戶不能夠在語句字符串中直接包含變量，相反，必須使用？作為在語句中所使用的變量的參數(shù)標(biāo)記符。例如：2) 第63行，

12、setstmt='deletefromadmin.fSubjectBalanceSheet'setstmt1='insertintotest_infovalues(?,?)'使用PREPARE語句從語句字符串中生成經(jīng)過準(zhǔn)備的語句。例如：第64行，prepareDelStmtfromstmt;preparepsfromstmt;使用EXECUTE語句執(zhí)行經(jīng)準(zhǔn)備的語句。如SQL語句字符串包含了參數(shù)標(biāo)記符，可以利用USING子句將變量值替代參數(shù)標(biāo)記符。例如：(8) 第65行，executeDelStmt;executepsusingv_a1,v_a2;1SQL存儲過程

13、說明請參見參考程序“資產(chǎn)負(fù)債.sql”CreateProcedureadmin.BalanceSheetDayly(OutProcStatevarchar(100)LanguageSQL(3)-SQLProcudure-Jun12,2002-LastModificationinOct,2002P1:Begindeclarestmtvarchar(1000);declarevProcStatevarchar(100);declaretodaydate;declarevTablechar(40);declaresqlcodeintdefault0;declaresqlstatechar(5)def

14、ault'00000'declarevActionvarchar(100);declarevDateCodeint;declarevNodeCodechar(4);declarevCurrencyCodechar(2);(9) declarevBalSubjItemCodechar(6);declarevBaldecimal(16,2);declarevLastNodeCodechar(4);declarevLastCurrencyCodechar(2);-期收期付款項declarevBal301decimal(16,2)default0;-系統(tǒng)內(nèi)款項declarevBal30

15、2decimal(16,2)default0;-存放海外分支機構(gòu)款項declarevBal303decimal(16,2)default0;-海外分支機構(gòu)declarevBal304decimal(16,2)default0;-撥入營運資金declarevBal305decimal(16,2)default0;(34) declarenot_foundconditionforsqlstate'02000'-得到資產(chǎn)負(fù)債表統(tǒng)計口徑的科目余額-科目余額日表+資產(chǎn)負(fù)債科目表declarecurAssetcursorwithholdforselectadmin.fSubjectBala

16、nceDayly.siNodeCode,admin.fSubjectBalanceDayly.siCurrencyCode,admin.dBalanceSubject.siBalSubjItemCode,sum(admin.fSubjectBalanceDayly.dDebitBal-admin.fSubjectBalanceDayly.dLoanBal)asdBalancefromadmin.fSubjectBalanceDayly,admin.dBalanceSubjectwhereadmin.fSubjectBalanceDayly.siSubjectCode=admin.dBalanc

17、eSubject.siSubjectCodegroupbyadmin.fSubjectBalanceDayly.siNodeCode,admin.fSubjectBalanceDayly.siCurrencyCode,admin.dBalanceSubject.siBalSubjItemCode;declarecontinuehandlerforsqlstate'42704'setvProcState=char(sqlcode)|sqlstate|'Warning'declarecontinuehandlerforsqlstate'02000'b

18、eginsetvProcState=char(sqlcode)|sqlstate|'Warning'setat_end=1;end;declarecontinuehandlerforsqlstate'23502'setvProcState=char(sqlcode)|sqlstate|'Warning'declareexithandlerforsqlexceptionbeginsetvProcState=char(sqlcode)|sqlstate|'Failed'insertintoadmin.ProcStatevalues(&

19、#39;BalanceSheetDayly',GetToday(),currenttimestamp,vTable,substr(vProcState,18,10),int(substr(vProcState,1,11),substr(vProcState,12,5),vAction);end;(35) selectdate(days(admin.SystemState.dtTransDate)+1)intotodayfromadmin.SystemState;setstmt='deletefromadmin.fSubjectBalanceSheet'prepareDe

20、lStmtfromstmt;executeDelStmt;setat_end=0;setvTable='fSubjectBalanceSheet'setvAction='重新生成資產(chǎn)負(fù)債科目余額表opencurAsset;fetchcurAssetintovNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;ifat_end<>0thengotoExit1;endif;setvDateCode=days(today)-1;setvLastNodeCode=vNodeCode;setvLastCurrencyCode=vC

21、urrencyCode;(105) -軋差項以“借-貸”判斷余額方向-3155以“貸-借”入表-其他3字頭科目以“借-貸”判斷余額方向-9670以“貸-借”入表-6999以“貸-借”入表-其他1字頭科目、6字頭科目以“借-貸”入表-其他科目以“貸-借”入表whileat_end=0andvNodeCode=vLastNodeCodeandvCurrencyCode=vLastCurrencyCodedobegincasewhenvBalSubjItemCodein('1620','2710')thensetvBal301=vBal301+vBal;whenvBa

22、lSubjItemCode='1170'thenbeginsetvBal302=vBal302+vBal;insertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'01170',vBal);whenvBalSubjItemCode='1175thenbeginsetvBal302=vBal302+vBal;insertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,

23、'01175',vBal);end;whenvBalSubjItemCode='1185'thensetvBal302=vBal302+vBal;whenvBalSubjItemCode='2170'thenbeginsetvBal302=vBal302+vBal;insertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'02170',-vBal);end;whenvBalSubjItemCode='2175'then

24、beginsetvBal302=vBal302+vBal;insertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'02175',-vBal);whenvBalSubjItemCodein('2185','2195','3110','3115','3130','3150','1625','2715')thensetvBal302=vBal302+vBal;

25、whenvBalSubjItemCodein('1180','2180')thensetvBal303=vBal303+vBal;whenvBalSubjItemCodein('1190','2190')thensetvBal304=vBal304+vBal;whenvBalSubjItemCodein('1199','2199')thensetvBal305=vBal305+vBal;whenvBalSubjItemCode='3155'theninsertintoadmin.fS

26、ubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'83155',-vBal);whenvBalSubjItemCodelike'3%'thenifvBal>0theninsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'3'|vBalSubjItemCode,vBal);elseinsertintoadmin.fSubjectBalanceSheetvalues(vDat

27、eCode,vNodeCode,vCurrencyCode,'4'|vBalSubjItemCode,-vBal);(141) whenvBalSubjItemCode='9670'thenbegininsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'59670',-vBal);insertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'69

28、670',-vBal);end;whenvBalSubjItemCode='6999'theninsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'76999',-vBal);elseifvBalSubjItemCodelike'1%'orvBalSubjItemCodelike'6%theninsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurren

29、cyCode,'1'|vBalSubjItemCode,vBal);elseinsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vNodeCode,vCurrencyCode,'2'|vBalSubjItemCode,-vBal);endif;fetchcurAssetintovNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;end;endwhile;ifvBal301>0theninsertintoadmin.fSubjectBalanceSheetvalue

30、s(vDateCode,vLastNodeCode,vLastCurrencyCode,'301',vBal301);elseinsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'401',-vBal301);endif;ifvBal302>0theninsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'302

31、9;,vBal302);elseinsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'402',-vBal302);endif;theninsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'303',vBal303);elseinsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,

32、vLastNodeCode,vLastCurrencyCode,'403',-vBal303);endif;ifvBal304>0theninsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'304',vBal304);elseinsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'404',-vBal304)

33、;endif;ifvBal305>0theninsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'305',vBal305);elseinsertintoadmin.fSubjectBalanceSheetvalues(vDateCode,vLastNodeCode,vLastCurrencyCode,'405',-vBal305);(152) ifat_end<>0thengotoExitl;endif;setvBal301=

34、0;setvBal302=0;setvBal303=0;setvBal304=0;setvBal305=0;setvLastNodeCode=vNodeCode;setvLastCurrencyCode=vCurrencyCode;endloopl1;closecurAsset;Exit1:(153) setvProcState=char(0)|'00000Success'insertintoadmin.ProcStatevalues('BalanceSheetDayly',GetToday(),currenttimestamp,'fSubjectBal

35、anceSheet',substr(vProcState,18,10),重新生成資產(chǎn)負(fù)債科目余額表');int(substr(vProcState,1,11),substr(vProcState,12,5),deletefromadmin.fNodeBalanceSheetwheredate(siDateCode)=GetToday();setvProcState=char(0)|'00000Success'setvTable='fNodeBalanceSheet'setvAction='網(wǎng)點資產(chǎn)負(fù)債表每日增量數(shù)據(jù)-網(wǎng)點資產(chǎn)負(fù)債表fNodeBalanceSheetinsertintoa