存储过程快速入门.docx
- 文档编号:5819297
- 上传时间:2023-01-01
- 格式:DOCX
- 页数:14
- 大小:20.86KB
存储过程快速入门.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 存储 过程 快速 入门
![提示](https://static.bdocx.com/images/bang_tan.gif)