DB2存储过程快速入门.

1、1.1 SQL过程的结构命名规则：1、清洗过程名称命名：PROC_#务主题_目标表（PROC_JY_KJYRLJB交易主题的卡交易日类聚表）2、函数名称命名：PROC_lk务主题_函数名（PROC_JY_GETYWZL交易主题取得卡业务种类函3、变量命名：VAR变量描述（VAR_YWZL业务种类变量）4、游标命名：CUR游标描述（CUR_KJYB对卡交易表进行游标处理）语法：CREATE PROCEDURE 过程名称（参数列表DYNAMIC RESULT SETS 结果集数量是否允许SQLLANGUAGE SQLBEGINSQL过程体END范例资产负债.sql中第 1 行：Create Pro

2、cedure admin.BalanceSheetDayl定义了过程名称参数歹U表为 Out ProcState varchar(100其定义SQL过程从客户应用获取，或返回客户应用的 0个或多个参数，参数 列表使用逗号侵害各个参数参数类型有三种：l IN从客户应用检索值。其不能够在 SQL过程体中修改l OUT向客户应用返回值l INOUT从客户应用检索值，并返回值省略了结果集数量的定义，default为00即表示不返回结果集。省略了是否允许SQL的说明。其值指出了存储过程是否会使用SQL语句，如果使用，具类型如何：l NO SQL不能够执行任何SQL语句l COTAINS SQL可以执行不

3、会读取SQL数据，也不会修改SQL数据的SQL 语句l READS SQL DATA可以包含不会修改 SQL数据的SQL语句l MODIFIES SQL DATA 可以执行任何SQL语句，除了不能够在存储过程中 支持的语句以外第37行，为注释，标明此为SQL过程，编写、最后修改时间。注释为 -'-"开 始的行。第8行和最后一行199共同标识出SQL过程体过程体存储过程的逻辑内容，包括变量声明、条件控制、流控制语句、以及通 过SQL语句处理数据的过程。另例：CREATE PROCEDURE bbgs_to_testinfo (IN var0 INTEGER , out ret

4、INTEGER , out ret_str varchar(5, OUT errorLabel CHAR(32SPECIFIC bbgs_to_testinfoRESULT SETS 1LANGUAGE SQL此定义为创建名为bbgs_to_testinfo的存储过程。它有4个参数：第一个IN参 数是INTEGER类型，第一个OUT参数是INTEGER类型，第二个OUT参数是 VARCHAR(5类型，第三个OUT参数是CHAR(32类型。指定的别名为 bbgs_to_testinfo,将返回一个结果集。1.2 SQL过程体1.2.1 声明、设置变量第961行。必须在SQL过程体的第一部分中声明

5、变量。必须指定惟一的标识符，声明 SQL数据类型、并且可以先把指定变量的初始值。变量声明的语法如下：DECLARE标识符SQL数据类型DEFAULT默认值1、SQLCODE、SQLSTATE 变量1314 行。用于在SQL过程中处理错误和排错问题。它们的值代表了SQL过程体中最后使用的SQL过程体中最后使用的SQL语句的返回值。2、游标中明3749 行。declare curAsset cursor with hold for3、条件处理器5061 行。当SQL语句返回超过00000的SQLSTATE值时，会产生一个条件，表示出现 了错误、数据没有找到或者警告。条件处理器可以决定SQL过程将如

6、何响应一个或多个已定义的条件或预定义 条件组。其语法如下：DECALRE数据类型HANDLER FOR条件,其有三种处理类型：l CONTINUE处理器操作完成后，继续执行产生这个条件的语句之后的下一条 语句。l EXIT处理器操作完成后，SQL过程将终止，并将控制返回给调用者。l UNDO处理器操作执行之前，DB2将回滚SQL过程中执行的SQL操作。完 成后，SQL过程将终止，并将控制返回给调用者。其预定义了 3个类的条件:l NOT FOUND标识导致SQLCODE值为+100或SQLSTATE值为02000的条件。一般在使用SELECT语句时出现。l SQLEXCEPTION标识导致SQ

7、LCODE值为负的条件l SQLWARNING 警告条件或导致 SQLCODE>100的条件1.2.2 控制结构流常用的结构：1、SET为输出参数或者SQL变量赋值。例如：set at_end=0;(66亍set vProcState=char(0|'00000 Success' (164set vBal302=vBal302+vBal;(89 行2、IFIF条dELSEIF 条件 2 THENELSEEND IF;3、LOOP多次执行一个代码块，直到LEAVE（跳出循环、ITERATE（跳至标签循环的开 始、GOTO（跳至指定标签块。例如：76161行，请注意其中的76

8、、77、152、153、161行，它们与5155行的条 件处理器一同控制着循环流程。4、WHILEWHILE 条件 DOEND WHILE;5、CASE基于一个或多个条件的评估选择执行路径，WHEN子句将直接值与CASE表 达式中规定的变量进行比较。例如：87128行1.2.3 在SQL过程体中使用 SQL语句1、直接使用过程体中可以直接使用SQL语句。例如:第 62 行：select date(days(admin.SystemState.dtTransDate+1 into today from admin.SystemState;查询得到的结果将通过into的方式赋给SQL过程变量tod

9、ay。第 90 行：insert into admin.fSubjectBalanceSheet values ( vDateCode ,vNodeCode, vCurrencyCode, '01170', vBal ;向表插入一条记录。2、使用结果集具体步骤如下：1声明游标语法：DECLARE 游标名 CURSOR WITH HOLD FORSELECT 语句;WITH HOLD表示打开游标保留打开状态，且这个游标定位在结果表的下一逻 辑行的前面；还可以用WITH RETURN指定此游标用作存储过程中返回的结果集 合。例如：3749行，declare curAsset cur

10、sor with hold for ； 定义了 curAsset游标。2为结果集打开游标打开游标，以便于它可以用于从其结果表中提取行。语法:OPEN游标名；例如：69行，open curAsset;3从结果集中取得查询数据使用FETCH语句，它将游标定位在其结果表中的下一行上，并给主机变量分 配这个行的值。语法：FETCH游标名INTO主机变量例如：7072 行FETCH curAsset INTO vNodeCode,vCurrencyCode,vBalSubjItemCode,vBal;if at_end<>0 then goto Exit1;end if;此例应与5155行的

11、条件处理器一起理解。当正常取到数据时，将游标的结果表中的数据赋给INTO后的主机变量列表中的对应变量。如果游标定位于结果表的未端（结果表为空时也是时，将产生一个 NOT FOUND条件，根据5155行的声明，将在处理器操作中改变 vProcState和 at_end的值。处理器操作完成后，继续处理下面的语句71行。4关闭结果集当游标使用完后，需要将其关闭。语法:CLOSE游标名例如：162 行，close curAsset;如果CREATE PROCEDURE语句中的定义的返回结果集个数不为 0,且此结 果集需要被返回给调用者时，则不能够被关闭。3、使用动态SQL语句具体步骤如下：1通过DEC

12、LARE语句声明VARCHAR类型变量作为SQL语句字符串例如：第 9 行，declare stmt varchar(1000;declare stmt1 varchar(1000;2给SQL语句字符串 赋值。用户不能够在语句字符串中直接包含变量，相 反，必须使用？作为在语句中所使用的变量的参数标记符。例如：第 63 行，set stmt='delete from admin.fSubjectBalanceSheet'set stmt1 = 'insert into test_info values( ? ,?'3使用PREPARE语句从语句字符串中生成经过准备

13、的语句。例如：第 64 行，prepare DelStmt from stmt;prepare ps from stmt;4使用EXECUTE语句执行经准备的语句。如 SQL语句字符串 包含了参数标 记符，可以利用USING子句将变量值替代参数标记符。例如：第 65 行，execute DelStmt;execute ps using v_a1,v_a2;1 SQL存储过程说明请参见参考程序资产负债.sql ”(1 Create Procedure admin.BalanceSheetDayly ( Out ProcState varchar(100(2 Language SQL(3(4 -

14、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 vAction varch

15、ar(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 -期收期付款项(24 declare vBal301 decimal(16,2 default 0;(25 -系统内款项(26 d

16、eclare vBal302 decimal(16,2 default 0;(27 -存放海外分支机构款项(28 declare vBal303 decimal(16,2 default 0;(29 -海外分支机构(30 declare vBal304 decimal(16,2 default 0;(31 -拨入营运资金(32 declare vBal305 decimal(16,2 default 0;(33 declare at_end int default 0;(34 declare not_found condition for sqlstate '02000'(35

17、 -得到资产负债表统计口径的科目余额(36 -科目余额日表+资产负债科目表(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.dLoanBal

18、as dBalance(43 from admin.fSubjectBalanceDayly,admin.dBalanceSubject(44 where(45admin.fSubjectBalanceDayly.siSubjectCode=admin.dBalanceSubject.siSubjectCode(46 group by(47 admin.fSubjectBalanceDayly.siNodeCode,(48 admin.fSubjectBalanceDayly.siCurrencyCode,(49 admin.dBalanceSubject.siBalSubjItemCode;

19、(50 declare continue handler for sqlstate '42704' setvProcState=char(sqlcode|sqlstate|' Warning'(51 declare 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 sqlst

20、ate '23502' setvProcState=char(sqlcode|sqlstate|' Warning'(57 declare exit handler for sqlexception(58 begin(59 set vProcState=char(sqlcode|sqlstate|' Failed'(60 insert into admin.ProcState values('BalanceSheetDayly',GetToday(,current timestamp,vTable,substr(vProcStat

21、e,18,10,int(substr(vProcState,1,11,substr(vProcState,1 2,5,vAction;(61 end;(62 select date(days(admin.SystemState.dtTransDate+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 vTa

22、ble='fSubjectBalanceSheet'(68 set vAction='重新生成资产负债科目余额表'(69 open curAsset;(70 fetch curAsset into vNodeCode,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=

23、vCurrencyCode;(76 l1:(77 loop(78 -轧差项以 惜-贷”判断余额方向(79 -3155以 贷-借”入表(80 -其他3字头科目以 惜-贷”判断余额方向(81 -9670以 贷-借”入表(82 -6999以 贷-借”入表(83 -其他1字头科目、6字头科目以 借-贷”入表(84 -其他科目以 贷-借”入表(85 while at_end=0 and vNodeCode=vLastNodeCode and vCurrencyCode=vLastCurrencyCode do(86 begin(87 case w hen vBalSubjItemCode in (

24、9;1620','2710' then set vBal301=vBal301+vBal;(88 when vBalSubjItemCode='1170'(89 then begin set vBal302=vBal302+vBal;(90 insert into admin.fSubjectBalanceSheet values( vDateCode, vNodeCode, vCurrencyCode, '01170',vBal;(91 end;(92 when vBalSubjItemCode='1175'(93 th

25、en begin set vBal302=vBal302+vBal;(94 insert into admin.fSubjectBalanceSheet values( vDateCode, vNodeCode, vCurrencyCode, '01175',vBal;(95 end;(96 when vBalSubjItemCode='1185' then set vBal302=vBal302+vBal;(98 then begin set vBal302=vBal302+vBal;(99 insert into admin.fSubjectBalanceS

26、heet values( vDateCode, vNodeCode, vCurrencyCode, '02170',-vBal;(100 end;(101 when vBalSubjItemCode='2175'(102 then begin set vBal302=vBal302+vBal;(103 insert into admin.fSubjectBalanceSheetvalues(vDateCode, vNodeCode, vCurrencyCode, '02175',-vBal;(104 end;(105 when vBalSubjI

27、temCode in('2185','2195','3110','3115','3130','3150','1625','2715'(106 then set vBal302=vBal302+vBal;(107 when vBalSubjItemCode in ('1180','2180' then setvBal303=vBal303+vBal;(108 when vBalSubjItemCode in ('1190'

28、,'2190' then setvBal304=vBal304+vBal;(109 when vBalSubjItemCode in ('1199','2199' then setvBal305=vBal305+vBal;(111 then insert into admin.fSubjectBalanceSheet values( vDateCode, vNodeCode, vCurrencyCode, '83155',-vBal;(112 when vBalSubjItemCode like '3%'(113

29、then if vBal>0(114 then insert into admin.fSubjectBalanceSheetvalues(vDateCode, vNodeCode, vCurrencyCode, '3'|vBalSubjItemCode,vBal; (115 else insert into admin.fSubjectBalanceSheetvalues(vDateCode, vNodeCode, vCurrencyCode, '4'|vBalSubjItemCode,-vBal; (116 end if;(117 when vBalSu

30、bjItemCode='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.fSubj

31、ectBalanceSheet 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 e

32、lse 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.fSubject

33、BalanceSheet values(vDateCode, vLastNodeCode, vLastCurrencyCode, '30T,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(vDateC

34、ode, 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, vL

35、astCurrencyCode, '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, '

36、;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', vBal305;(

37、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 vLastNod

38、eCode=vNodeCode;(160 set vLastCurrencyCode=vCurrencyCode;(161 end loop 11;(162 close curAsset;(163 Exit1:(164 set vProcState=char(0|'00000 Success'(165 insert into admin.ProcState values('BalanceSheetDayly',GetToday(,current timestamp, 'fSubjectBalanceSheet', substr(vProcStat

39、e,18,10,int(substr(vProcState,1,11, substr(vProcState,12,5重新生成资产负债科目余额表'(166 delete from admin.fNodeBalanceSheet where date(siDateCode=GetToday(; (167 set vProcState=char(0|'00000 Success'(168 set vTable='fNodeBalanceSheet'(169 set vAction='网点资产负债表每日增量数据;(170 -网点资产负债表 fNodeBalanceSheet(171 insert into admin.fNodeBalanceSheet(173 admin.fSubjectBalanceSheet.siDateCode as siDateCode,(17