DB2存儲過程快速入門

1、1.1 SQL過程的結(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)處理）語法：CREATE PROCEDURE 過程名稱(參數(shù)列表DYNAMIC RESULT SETS 結(jié)果集數(shù)量是否允許SQLLANGUAGE SQLBEGINSQL 過程體END范例“資產(chǎn)負(fù)債.sql ”中參數(shù)列表為

2、Out ProcState varchar(100其定義SQL 過程從客戶應(yīng)用獲取，或返回客戶應(yīng)用的0個(gè)或多個(gè)參數(shù)，參數(shù)列表使用逗號侵害各個(gè)參數(shù)參數(shù)類型有三種：l IN 從客戶應(yīng)用檢索值。其不能夠在SQL 過程體中修改l OUT 向客戶應(yīng)用返回值l INOUT 從客戶應(yīng)用檢索值，并返回值省略了結(jié)果集數(shù)量的定義，default 為0。即表示不返回結(jié)果集。省略了是否允許SQL 的說明。其值指出了存儲過程是否會使用SQL 語句，如果使用，其類型如何：l NO SQL 不能夠執(zhí)行任何SQL 語句l COTAINS SQL 可以執(zhí)行不會讀取SQL 數(shù)據(jù)，也不會修改SQL 數(shù)據(jù)的SQL 語句l READS

3、 SQL DATA 可以包含不會修改SQL 數(shù)據(jù)的SQL 語句l MODIFIES SQL DATA 可以執(zhí)行任何SQL 語句，除了不能夠在存儲過程中支持的語句以外。第37行，為注釋，標(biāo)明此為SQL 過程，編寫、最后修改時(shí)間。注釋為“-”開始的行。 第8行和最后一行199共同標(biāo)識出SQL 過程體過程體存儲過程的邏輯內(nèi)容，包括變量聲明、條件控制、流控制語句、以及通過SQL語句處理數(shù)據(jù)的過程。另例：CREATE PROCEDURE bbgs_to_testinfo (IN var0 INTEGER , out ret INTEGER , out ret_str varchar(5, OUT err

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

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

6、下：DECALRE 數(shù)據(jù)類型 HANDLER FOR 條件，其有三種處理類型：l CONTINUE 處理器操作完成后，繼續(xù)執(zhí)行產(chǎn)生這個(gè)條件的語句之后的下一條語句。 l EXIT 處理器操作完成后，SQL 過程將終止，并將控制返回給調(diào)用者。l UNDO 處理器操作執(zhí)行之前，DB2將回滾SQL 過程中執(zhí)行的SQL 操作。完成后，SQL 過程將終止，并將控制返回給調(diào)用者。其預(yù)定義了3個(gè)類的條件：l NOT FOUND 標(biāo)識導(dǎo)致SQLCODE 值為+100或SQLSTATE 值為02000的條件。一般在使用SELECT 語句時(shí)出現(xiàn)。l SQLEXCEPTION 標(biāo)識導(dǎo)致SQLCODE 值為負(fù)的條件l

7、SQLWARNING 警告條件或?qū)е耂QLCODE>100的條件1.2.2 控制結(jié)構(gòu)流常用的結(jié)構(gòu)：1、 SET為輸出參數(shù)或者SQL 變量賦值。例如：set at_end=0;(66行set vProcState=char(0|'00000 Success' (164行set vBal302=vBal302+vBal;(89行2、 IFIF 條dELSEIF 條件2 THENELSEEND IF;3、 LOOP多次執(zhí)行一個(gè)代碼塊，直到LEAVE(跳出循環(huán) 、ITERATE(跳至標(biāo)簽循環(huán)的開始 、GOTO(跳至指定標(biāo)簽塊 。例如：76161行，請注意其中的76、77、152、

8、153、161行，它們與5155行的條件處理器一同控制著循環(huán)流程。4、 WHILEWHILE 條件 DOEND WHILE;5、 CASE基于一個(gè)或多個(gè)條件的評估選擇執(zhí)行路徑，WHEN 子句將直接值與CASE 表達(dá)式中規(guī)定的變量進(jìn)行比較。例如：87128行1.2.3 在SQL 過程體中使用SQL 語句1、 直接使用過程體中可以直接使用SQL 語句。例如：第62行：select date(days(admin.SystemState.dtTransDate+1 into today from admin.SystemState;查詢得到的結(jié)果將通過into 的方式賦給SQL 過程變量today

9、。第90行：insert into admin.fSubjectBalanceSheet values ( vDateCode ,vNodeCode, vCurrencyCode, '01170', vBal ;向表插入一條記錄。2、 使用結(jié)果集具體步驟如下：1 聲明游標(biāo)語法：DECLARE 游標(biāo)名 CURSOR WITH HOLD FORSELECT 語句；WITH HOLD表示打開游標(biāo)保留打開狀態(tài)，且這個(gè)游標(biāo)定位在結(jié)果表的下一邏輯行的前面； 還可以用WITH RETURN指定此游標(biāo)用作存儲過程中返回的結(jié)果集合。例如：3749行，declare curAsset cursor

10、 with hold for；定義了curAsset 游標(biāo)。2 為結(jié)果集打開游標(biāo)打開游標(biāo)，以便于它可以用于從其結(jié)果表中提取行。語法：OPEN 游標(biāo)名；例如：69行，open curAsset;3 從結(jié)果集中取得查詢數(shù)據(jù)使用FETCH 語句，它將游標(biāo)定位在其結(jié)果表中的下一行上，并給主機(jī)變量分配這個(gè)行的值。 語法：FETCH 游標(biāo)名 INTO 主機(jī)變量例如：7072行FETCH curAsset INTO vNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;if at_end<>0 then goto Exit1;end if;此例應(yīng)與5155行

11、的條件處理器一起理解。當(dāng)正常取到數(shù)據(jù)時(shí)，將游標(biāo)的結(jié)果表中的數(shù)據(jù)賦給INTO 后的主機(jī)變量列表中的對應(yīng)變量。 如果游標(biāo)定位于結(jié)果表的未端(結(jié)果表為空時(shí)也是 時(shí)，將產(chǎn)生一個(gè)NOT FOUND條件，根據(jù)5155行的聲明，將在處理器操作中改變vProcState 和at_end的值。處理器操作完成后，繼續(xù)處理下面的語句71行。4 關(guān)閉結(jié)果集當(dāng)游標(biāo)使用完后，需要將其關(guān)閉。語法：CLOSE 游標(biāo)名例如：162行，close curAsset;如果CREATE PROCEDURE語句中的定義的返回結(jié)果集個(gè)數(shù)不為0，且此結(jié)果集需要被返回給調(diào)用者時(shí)，則不能夠被關(guān)閉。3、 使用動態(tài)SQL 語句具體步驟如下：1 通

12、過DECLARE 語句聲明VARCHAR 類型變量作為SQL 語句字符串例如：第9行，declare stmt varchar(1000;declare stmt1 varchar(1000;2 給SQL 語句字符串 賦值。用戶不能夠在語句字符串中直接包含變量，相反，必須使用？作為在語句中所使用的變量的參數(shù)標(biāo)記符。例如：第63行，set stmt='delete from admin.fSubjectBalanceSheet'set stmt1 = 'insert into test_info values( ? ,? '3 使用PREPARE 語句從語句字符串

13、中生成經(jīng)過準(zhǔn)備的語句。例如：第64行，prepare DelStmt from stmt;prepare ps from stmt;4 使用EXECUTE 語句執(zhí)行經(jīng)準(zhǔn)備的語句。如SQL 語句字符串 包含了參數(shù)標(biāo)記符，可以利用USING 子句將變量值替代參數(shù)標(biāo)記符。例如：第65行，execute DelStmt;execute ps using v_a1,v_a2;1 SQL存儲過程說明請參見參考程序“資產(chǎn)負(fù)債.sql ”(1 Create Procedure admin.BalanceSheetDayly ( Out ProcState varchar(100(2 Language SQL(

14、3 -(4 - SQL Procudure(5 - Jun 12,2002(6 - Last Modification in Oct,2002(7 -(8 P1:Begin(9 declare stmt varchar(1000;(10 declare vProcState varchar(100;(11 declare today date;(12 declare vTable char(40;(13 declare sqlcode int default 0;(14 declare sqlstate char(5 default '00000'(15 declare vAc

15、tion varchar(100;(16 declare vDateCode int;(17 declare vNodeCode char(4;(18 declare vCurrencyCode char(2;(19 declare vBalSubjItemCode char(6;(20 declare vBal decimal(16,2;(21 declare vLastNodeCode char(4;(22 declare vLastCurrencyCode char(2;(23 -期收期付款項(xiàng)(24 declare vBal301 decimal(16,2 default 0;(25 -

16、系統(tǒng)內(nèi)款項(xiàng)(26 declare vBal302 decimal(16,2 default 0;(27 -存放海外分支機(jī)構(gòu)款項(xiàng)(28 declare vBal303 decimal(16,2 default 0;(29 -海外分支機(jī)構(gòu)(30 declare vBal304 decimal(16,2 default 0;(31 -撥入營運(yùn)資金(32 declare vBal305 decimal(16,2 default 0;(33 declare at_end int default 0;(34 declare not_found condition for sqlstate '020

17、00'(35 -得到資產(chǎn)負(fù)債表統(tǒng)計(jì)口徑的科目余額(36 -科目余額日表+資產(chǎn)負(fù)債科目表(37 declare curAsset cursor with hold for(38 select(39 admin.fSubjectBalanceDayly.siNodeCode,(40 admin.fSubjectBalanceDayly.siCurrencyCode,(41 admin.dBalanceSubject.siBalSubjItemCode,(42sum(admin.fSubjectBalanceDayly.dDebitBal-admin.fSubjectBalanceDayly

18、.dLoanBal as dBalance(44 where(45(46 group by(47 admin.fSubjectBalanceDayly.siNodeCode,(48 admin.fSubjectBalanceDayly.siCurrencyCode,(49 admin.dBalanceSubject.siBalSubjItemCode;(50 declare continue handler for sqlstate '42704' setvProcState=char(sqlcode|sqlstate|' Warning'(51 declare

19、 continue handler for sqlstate '02000'(52 begin(53 set vProcState=char(sqlcode|sqlstate|' Warning'(54 set at_end=1;(55 end;(56 declare continue handler for sqlstate '23502' setvProcState=char(sqlcode|sqlstate|' Warning'(57 declare exit handler for sqlexception(58 begi

20、n(59 set vProcState=char(sqlcode|sqlstate|' Failed'(60 insert into admin.ProcState values('BalanceSheetDayly',GetToday(,current timestamp,vTable,substr(vProcState,18,10,int(substr(vProcState,1,11,substr(vProcState,12,5,vAction;(61 end;(62 select date(days(admin.SystemState.dtTransDat

21、e+1 into today fromadmin.SystemState;(63 set stmt='delete from admin.fSubjectBalanceSheet'(64 prepare DelStmt from stmt;(65 execute DelStmt;(66 set at_end=0;(67 set vTable='fSubjectBalanceSheet'(68 set vAction='重新生成資產(chǎn)負(fù)債科目余額表'(69 open curAsset;(70 fetch curAsset into vNodeCode

22、,vCurrencyCode,vBalSubjItemCode,vBal;(71 if at_end<>0 then goto Exit1;(72 end if;(73 set vDateCode=days(today-1;(74 set vLastNodeCode=vNodeCode;(75 set vLastCurrencyCode=vCurrencyCode;(76 l1:(77 loop(78 -軋差項(xiàng)以“借-貸”判斷余額方向(79 -3155以“貸-借”入表(80 -其他3字頭科目以“借-貸”判斷余額方向(81 -9670以“貸-借”入表(82 -6999以“貸-借”入表

23、(83 -其他1字頭科目、6字頭科目以“借-貸”入表(84 -其他科目以“貸-借”入表(85 while at_end=0 and vNodeCode=vLastNodeCode and vCurrencyCode=vLastCurrencyCode do(86 begin(87 case w hen vBalSubjItemCode in ('1620','2710' then set vBal301=vBal301+vBal;(88 when vBalSubjItemCode='1170'(89 then begin set vBal302=

24、vBal302+vBal;(90 insert into admin.fSubjectBalanceSheet values( vDateCode, vNodeCode, vCurrencyCode, '01170',vBal;(91 end;(92 when vBalSubjItemCode='1175'(93 then begin set vBal302=vBal302+vBal;(94 insert into admin.fSubjectBalanceSheet values( vDateCode, vNodeCode, vCurrencyCode, &#

25、39;01175',vBal;(95 end;(96 when vBalSubjItemCode='1185' then set vBal302=vBal302+vBal;(97 when vBalSubjItemCode='2170'(98 then begin set vBal302=vBal302+vBal;(99 insert into admin.fSubjectBalanceSheet values( vDateCode, vNodeCode, vCurrencyCode, '02170',-vBal;(100 end;(10

26、1 when vBalSubjItemCode='2175'(102 then begin set vBal302=vBal302+vBal;values(vDateCode, vNodeCode, vCurrencyCode, '02175',-vBal;(104 end;(105 when vBalSubjItemCode in('2185','2195','3110','3115','3130','3150','1625','2715&#

27、39;(106 then set vBal302=vBal302+vBal;(107 when vBalSubjItemCode in ('1180','2180' then setvBal303=vBal303+vBal;(108 when vBalSubjItemCode in ('1190','2190' then setvBal304=vBal304+vBal;(109 when vBalSubjItemCode in ('1199','2199' then setvBal305=vBal3

28、05+vBal;(111 then insert into admin.fSubjectBalanceSheet values( vDateCode, vNodeCode, vCurrencyCode, '83155',-vBal;(112 when vBalSubjItemCode like '3%'(113 then if vBal>0values(vDateCode, vNodeCode, vCurrencyCode, '4'|vBalSubjItemCode,-vBal; (116 end if;(117 when vBalSubj

29、ItemCode='9670'(118 then begin(119 insert into admin.fSubjectBalanceSheet values(vDateCode, vNodeCode, vCurrencyCode,'59670',-vBal;(120 insert into admin.fSubjectBalanceSheet values(vDateCode, vNodeCode, vCurrencyCode,'69670',-vBal;(121 end;(123 then insert into admin.fSubjec

30、tBalanceSheet values(vDateCode, vNodeCode, vCurrencyCode,'76999',-vBal;(124 else if vBalSubjItemCode like '1%' or vBalSubjItemCode like '6%'(125 then insert into admin.fSubjectBalanceSheet values (vDateCode, vNodeCode, vCurrencyCode, '1'|vBalSubjItemCode,vBal;(126 els

31、e insert into admin.fSubjectBalanceSheet values (vDateCode, vNodeCode, vCurrencyCode,'2'|vBalSubjItemCode,-vBal;(127 end if;(128 end case;(129 fetch curAsset into vNodeCode,vCurrencyCode,vBalSubjItemCode,vBal; (130 end;(131 end while;(132 if vBal301>0(133 then insert into admin.fSubjectBa

32、lanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '301',vBal301;(134 else insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '401',-vBal301;(135 end if;(136 if vBal302>0(137 then insert into admin.fSubjectBalanceSheet values(vDa

33、teCode, vLastNodeCode, vLastCurrencyCode,'302',vBal302;(138 else insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '402',-vBal302;(139 end if;(140 if vBal303>0(141 then insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode,

34、 vLastCurrencyCode, '303',vBal303;(142 else insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '403',-vBal303;(143 end if;(144 if vBal304>0(145 then insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, &

35、#39;304', vBal304;(146 else insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '404',-vBal304;(147 end if;(148 if vBal305>0(149 then insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '305', vBal30

36、5;(150 else insert into admin.fSubjectBalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '405', -vBal305;(151 end if;(152 if at_end<>0 then goto Exit1;(153 end if;(154 set vBal301=0;(155 set vBal302=0;(156 set vBal303=0;(157 set vBal304=0;(158 set vBal305=0;(159 set vLast

37、NodeCode=vNodeCode;(160 set vLastCurrencyCode=vCurrencyCode;(161 end loop l1;(162 close curAsset;(163 Exit1:(164 set vProcState=char(0|'00000 Success'(165 insert into admin.ProcState values('BalanceSheetDayly',GetToday(,current timestamp, 'fSubjectBalanceSheet', substr(vProcState,18,10,int(substr(vProcState,1,11, substr(vProcState,12,5, '重新生成資產(chǎn)負(fù)債科目余額表' (166 delete from admin.fNodeBalanceSheet where date(siDateCode=GetToday(; (167 set vProcState=char(0|'00000 Succe