1.1 SQL过程的结构 命名规则: 1、清洗过程名称命名: PROC_业务主题_目标表(PROC_JY_KJYRLJB 交易主题的卡交易日类聚表) 2、函数名称命名: PROC_业务主题_函数名(PROC_JY_GETYWZL 交易主题取得卡业务种类函数)
3、变量命名: VAR_变量描述(VAR_YWZL 业务种类变量) 4、游标命名: CUR_游标描述(CUR_KJYB 对卡交易表进行游标处理) 语法: CREATE PROCEDURE 过程名称 (参数列表 DYNAMIC RESULT SETS 结果集数量 是否允许SQL LANGUAGE SQL BEGIN SQL 过程体 END 范例“资产负债.sql ”中 第1行:Create Procedure admin.BalanceSheetDayly定义了过程名称 参数列表为Out ProcState varchar(100 其定义SQL 过程从客户应用获取,或返回客户应用的0个或多个参数,参数列表使用逗号侵害各个参数
参数类型有三种: l IN 从客户应用检索值。其不能够在SQL 过程体中修改 l OUT 向客户应用返回值 l INOUT 从客户应用检索值,并返回值 省略了结果集数量的定义,default 为0。即表示不返回结果集。 省略了是否允许SQL 的说明。其值指出了存储过程是否会使用SQL 语句,如果使用,其类型如何:
l NO SQL 不能够执行任何SQL 语句 l COTAINS SQL 可以执行不会读取SQL 数据,也不会修改SQL 数据的SQL 语句
l READS SQL DATA 可以包含不会修改SQL 数据的SQL 语句 l MODIFIES SQL DATA 可以执行任何SQL 语句,除了不能够在存储过程中支持的语句以外。 第3~7行,为注释,标明此为SQL 过程,编写、最后修改时间。注释为“--”开始的行。 第8行和最后一行199共同标识出SQL 过程体
过程体存储过程的逻辑内容,包括变量声明、条件控制、流控制语句、以及通过SQL
语句处理数据的过程。 另例: CREATE PROCEDURE bbgs_to_testinfo (IN var0 INTEGER , out ret INTEGER , out ret_str varchar(5, OUT errorLabel CHAR(32
SPECIFIC bbgs_to_testinfo RESULT SETS 1 LANGUAGE SQL 此定义为创建名为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行。 declare curAsset cursor with hold for 3、条件处理器 50~61行。 当SQL 语句返回超过00000的SQLSTATE 值时,会产生一个条件,表示出现了错误、数据没有找到或者警告。
条件处理器可以决定SQL 过程将如何响应一个或多个已定义的条件或预定义条件组。其语法如下:
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 标识导致SQLCODE 值为负的条件 l SQLWARNING 警告条件或导致SQLCODE>100的条件 1.2.2 控制结构流 常用的结构: 1、 SET 为输出参数或者SQL 变量赋值。 例如: set at_end=0;(66行 set vProcState=char(0||'00000 Success'; (164行 set vBal302=vBal302+vBal;(89行 2、 IF IF 条d … ELSEIF 条件2 THEN … ELSE … END IF; 3、 LOOP 多次执行一个代码块,直到LEAVE(跳出循环 、ITERATE(跳至标签循环的开始 、GOTO(跳至指定标签块 。
例如: 76~161行,请注意其中的76、77、152、153、161行,它们与51~55行的条件处理器一同控制着循环流程。
4、 WHILE WHILE 条件 DO … END WHILE; 5、 CASE 基于一个或多个条件的评估选择执行路径,WHEN 子句将直接值与CASE 表达式中规定的变量进行比较。
例如: 87~128行 1.2.3 在SQL 过程体中使用SQL 语句 1、 直接使用 过程体中可以直接使用SQL 语句。 例如: 第62行:select date(days(admin.SystemState.dtTransDate+1 into today from admin.SystemState;
查询得到的结果将通过into 的方式赋给SQL 过程变量today 。 第90行:insert into admin.fSubjectBalanceSheet values ( vDateCode ,vNodeCode, vCurrencyCode, '01170', vBal ;
向表插入一条记录。 2、 使用结果集 具体步骤如下: 1 声明游标 语法: DECLARE 游标名 CURSOR WITH HOLD FOR SELECT 语句; WITH HOLD表示打开游标保留打开状态,且这个游标定位在结果表的下一逻辑行的前面; 还可以用WITH RETURN指定此游标用作存储过程中返回的结果集合。
例如: 37~49行,declare curAsset cursor with hold for…;定义了curAsset 游标。 2 为结果集打开游标 打开游标,以便于它可以用于从其结果表中提取行。 语法: OPEN 游标名; 例如: 69行,open curAsset; 3 从结果集中取得查询数据 使用FETCH 语句,它将游标定位在其结果表中的下一行上,并给主机变量分配这个行的值。 语法:
FETCH 游标名 INTO 主机变量 例如: 70~72行 FETCH curAsset INTO vNodeCode,vCurrencyCode,vBalSubjItemCode,vBal; if at_end<>0 then goto Exit1; end if; 此例应与51~55行的条件处理器一起理解。 当正常取到数据时,将游标的结果表中的数据赋给INTO 后的主机变量列表中的对应变量。 如果游标定位于结果表的未端(结果表为空时也是 时,将产生一个NOT FOUND条件,根据51~55行的声明,将在处理器操作中改变vProcState 和at_end的值。处理器操作完成后,继续处理下面的语句71行。
4 关闭结果集 当游标使用完后,需要将其关闭。 语法: CLOSE 游标名 例如: 162行,close curAsset; 如果CREATE PROCEDURE语句中的定义的返回结果集个数不为0,且此结果集需要被返回给调用者时,则不能够被关闭。
3、 使用动态SQL 语句 具体步骤如下: 1 通过DECLARE 语句声明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 语句从语句字符串中生成经过准备的语句。 例如: 第64行,prepare DelStmt from stmt; prepare ps from stmt;