DB2存储过程编写规范

1、DB2存储过程编写规范 版本号：1.0 修订记录: 修订日期 修订版本 修订人 修订内容 2007-03-01 1.0 潘冬梅 制定DB2存储过程编写规范 目录 第一章 .前言 3 一 .编写目的 3 二 .编写背景 4 三 .适用范围 4 第二章 .程序结构 5 一 .整体结构 5 二 .程序说明 6 三 .变量定义 7 四 .异常错误处理 7 五 .程序正文 9 第三章 .命名规范 10 一 .存储过程命名 10 二 .参数命名 10 三 .变量命名 11 四 .临时表命名 11 第四章 .书写格式 11 一 .表达范式 11 二 .段落缩进 12 三 .段落间隔 12 四 .程序注释 1

2、2 第五章 .注意事项 13 一 .固定的输出参数 13 二 .临时表的使用 14 三 .数据的插入 14 四 .where 条件 14 五 .count 的使用 14 六 .全表删除 14 七.MERGE ( UPSERT) 的使用 15 第六章 .附录 A 15 第一章 .前言 一. 编写目的 为了提高开发效率和程序的可读性， 降低程序编写过程的出错率 和重复劳动性，保持程序编写风格的一致性和连贯性，特定此规范 .编写背景 数据 目前数据库工具有很多种， 考虑到数据仓库开发的实用性， 仓库开发工具选择了 DB2 。 三. 适用范围 本规范适用于招商银行信息技术部开发人员以及运行管理人员，

3、从事 DB2 存储过程开发的相关技术人必须按照此规范编写存储过 程。 第二章 .程序结构 一. 整体结构 创建 DB2 存储过程必须按如下标准格式书写： DROP PROCEDURE 模式名 .过程名 CREATE PROCEDURE 模式名 .过程名 （ IN|OUT 输入|输出变量名输入 |输出变量类型 , . ） SPECIFIC 模式名 .过程名 LANGUAGE SQL /* 程序说明 */ BEGIN END 其中： 1）模式名是用来指定该存储过程属于哪个模式下的， 默认为编 译该过程的登录用户名， 但为了过程的统一管理以及各系统间的相互 区分，必须要指定一个模式名， 模式名由过程

4、所属项目设计中统一制 2) 存储过程可以参数不带参数， 但如果带参数必须按照命名规 则写出参数名，明确指定参数类型， 并显示定义参数的输入输出性质。 多个参数之间用换行和逗号分隔。 3 ) 程序体一般包括变量定义、异常错误处理、程序正文等。 4) 整个过程结束的标志符为 。 二. 程序说明 程序说明是一种注释， 是对存储过程作用、 定义等的一种描述， 在程序正文开始前必须有必要的说明，其具体内容包括： 脚本名称：该存储过程名 功 能：存储过程的作用与所要完成的目的 参数 : 对输入输出参数进行必要的说明 调 用：如何调用该过程，调用条件等，并写出一个调用 的例子 返 回 值：将所有可能的返回值

5、列出 创建时间：首次建立该过程的时间 作 者：建立该过程的作者姓名 修改记录：依次记录每次修改的修改人、修改日期、修改原 因等信息 三. 变量定义 变量定义规则如下： DECLARE 变量名 1 变量类型 DEFAULT 默认值 ; DECLARE 变量名 2 变量类型 DEFAULT 默认值 ; 原则上每个变量的定义都要单独放置一行，并以分号结尾。 变量的定义应该出现在存储过程的程序体开始。 四. 异常错误处理 异常错误处理是正文中必不可少的一部分，有着特定的作用 当过程执行出现警告或错误时需要通过异常处理来判断是否继续 执行该过程。它通常在程序变量定义与程序正文之间进行定义。 一个规范的异

6、常错误处理定义如下： DECLARE s_rtcdINTEGER DEFAULT 0; DECLARE s_rtstCHAR(5) DEFAULT 00000; DECLARE s_textVARCHAR(200); DECLARE s_prdname VARCHAR(30) DEFAULT 存储过程名 ; DECLARE s_sysname VARCHAR(18) DEFAULT 运行系统名 ; DECLARE s_errlvl char(4) DEFAULT 提示 ; DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE SQLSTATE CHAR(5) D

7、EFAULT 00000; rollback work; DECLARE continue HANDLER FOR SQLWARNING BEGIN GET DIAGNOSTICS EXCEPTION 1 error_message = MESSAGE_TEXT; set s_rtcd = SQLCODE; set s_rtst = SQLSTATE; set s_errlvl= 警告 if s_rtst 01003 then set s_text = s_text|-sqlcode: | cast(s_rtcd as char(5)|-sqlstate: |s_rtst; insert in

8、to DWCTRL.CT_LOG_CT values(I_Date, s_sysname , s_prdname, CURRENT TIMESTAMP , s_errlvl, s_text, error_message); end if ; END; DECLARE exit HANDLER FOR SQLEXCEPTION BEGIN GET DIAGNOSTICS EXCEPTION 1 error_message = MESSAGE_TEXT; set s_rtcd = SQLCODE; set s_rtst = SQLSTATE; set s_text = s_text|-sqlcod

9、e: | cast(s_rtcd as char(5)|-sqlstate: |s_rtst; set s_errlvl= 错误 ; insert into DWCTRL.CT_LOG_CT values(I_Date, s_sysname , s_prdname, CURRENT TIMESTAMP , s_errlvl, s_text, error_message); commit work; set O_APPMSG = s_text ; set O_SYSMSG = error_message ; set O_RTCD = -1 ; END; 五. 程序正文 程序正文是用来实现程序功能

10、的一条或多条 SQL 语句，每个 存储过程的程序正文必须存在至少一条 SQL 语句。 非报表性质的过程在开始和结束时都必须插入系统日志信息， 报表性质的过程不需要插入日志。 数据仓库统一的日志信息表为： DWCTRL.CT_LOG_CT 第三章 .命名规范 程序中的命名要遵循“知名闻意”的总体规则，让他人能很快 很容易的明白其意思。 一. 存储过程命名 存储过程模式名：每个项目中的过程都必须有统一的模式名， 模式名一般为大写， 由项目设计前期统一制定。 一些临时性质的过 程可使用自己的用户名作为模式名。 过程名： 按存储过程的功能进行分类， 每类用一个统一的前缀 标识，存储过程的命名方法为前缀

11、加能反映存储过程本身功能含义 的一串英文单词，前缀的定义有每个项目设计前期统一制定。 存储过程名一般为大写。 二. 参数命名 存储过程的参数分为输入参数（ IN ），输出参数（ OUT ）和输 入输出参数（ INTOUT ），根据参数性质规定统一的前缀标识，参 数命名的方法为前缀加有意义的英文名，前缀定义规则如下： 输入参数： I_ 输出参数： O_ 输入输出参数： IO_ 英文名要遵循“知名闻意”的原则，一般一串有有意义的英文 名的第一个字母为大写，举例如下： I_Date 表示输入日期参数 O_SysMsg 表示输出系统的错误信息 IO_Clid 表示客户 ID 的输入输出 三. 变量命名

12、 变量名一般可分为三种：系统变量，程序变量和游标变量，变 量命名方法为前缀加有意义的英文名，前缀标识其规则如下： 系统变量： s_ 程序变量： v_ 游标变量： c_ 变量名是不区分大小写的， 但一般代表一个意思的名字的第一 个字母为大写，其余的为小写，如： v_Date 表示日期变量； v_CDate 表示字符型的日期变量 四. 临时表命名 在程序中建立临时表时，其名称应以 tmp_ 开头再加上有意 义的表名命名。 第四章 .书写格式 一. 表达范式 为便于阅读，请遵循下列建议： 1) 书写 SQL 语句时，多个字段名之间应该用逗号和空格分开 2）函数调用的参数之间使用个逗号隔开。 3）简单

13、赋值语句左右最好保留空格，如： set v_i = 1 ; 4）每行最多只能写一条 SQL 语句。 5）当一条 SQL 语句过长时，需要分行书写。 二. 段落缩进 在编写程序时要注意段落的缩进对齐，以突出嵌套的层次结 构，提高可读性。 原则上规定以 4 个空格字符为一个缩进单位。 不 能使用 tab 键做缩进字符，否则在不同的编辑器中会出现意想不到 的结果。 三. 段落间隔 在完成一段比较明确的作用， 开始另一目的的编写时， 之间要 以空行隔开以便于阅读。例如，完成一条 insert 语句时，接着写 下条语句之间要健入一行空行。 四. 程序注释 存储过程的注释是一个必不可少的环节， 注释可分为

14、行注释和 段落注释。 行注释标识： - 段落注释标识： 注释开始标识 /* 注释结束标识 */ 注意：段落注释是不能嵌套使用的 在以下的地方应该用到注释 存储过程说明 参数名 变量名 在编写者认为有必要的地方。 第五章 .注意事项 一.固定的输出参数 除了报表过程外，其他过程有三个固定的输出参数，如下： OUT O_RTCD INT, - 输出错误信息 （代码 ） OUT O_APPMSG varchar（300）, - 输出错误信息 （ 自定义文本 ） OUT O_SYSMSG varchar（300） - 输出错误信息 （系统 ） 参数 O_RTCD 是判断程序运行是否正确的参数具体输出值

15、 如下： 值=0 则运行正确 值 0 则程序有错误 值 0则程序出现警告，但不影响程序的运行 二. 临时表的使用 存储过程在处理多条记录时最好使用临时表，而不要使用游 标。 在建立临时表时先确认临时表空间的名字 在创建临时表中，只有在调试过程时才能使用 ON COMMIT PRESERVE ROWS 属性，在正式上线时必须将该属性屏蔽。 三. 数据的插入 在进行 insert 操作时，应写全目标表的字段， 以防止发生插入 错行等错误。 四. where条件 在编写 where 条件时如果有常量条件的，尽量将其放在前面。 如果有 partitioning key 字段条件时， 不论是否是多余条件

16、都应 该写上。 五. count 的使用 在使用的 count(*) 时可尽量用 count (索引列) 来替代，或 用 count(1) 也可。 六. 全表删除 在进行全表删除时， 如果不需要记录事务日志， 可以调用如下过 程以提高执行效率： etl_ctrl.load_empty(in tablename varchar(80), out rtcd int, out appmsg varchar(254), out sysmsg varchar(1024) ) 注意：对含有历史数据的表进行删除要慎用 。 七. MERGE ( UPSERT)的使用 对表进行DELETE时并不会立即释放表空间

17、，因此需要对同一张 表进行 UPDATE + INSERT 操作时请尽量使用 MERGE 代替。其在 效率方面也会比 UPDATE + INSERT 强许多。 第六章 .附录 A 一个规范的 DB2 存储过程示例： DROP PROCEDURE PRO_CORE.MODEL CREATE PROCEDURE PRO_CORE.MODEL IN I_Date DATE, OUT O_RTCDINT, OUT O_APPMSG varchar(300), OUT O_SYSMSG varchar(300) ) SPECIFIC PRO_CORE.MODEL - 统计日期 - 输出错误信息 (代码

18、) - 输出错误信息 (自定义文本 ) - 输出错误信息 (系统 ) LANGUAGE SQL /* 脚本名称 : PRO_CORE.MODEL 目 的： 编写该存储过程的目的 例 程： call PRO_CORE.MODEL(current date ,?,?,?) 作 者： 张三 日 期： 2007.2.1 - 修改版本 : - modi by 李四 2007.3.1 修改统计类型 - modi by 王二 2007.3.2 修改将表 aa 修改成 bb */ BEGIN /* = = 程序变量 = = */ DECLARE v_Cnt int ; - 计数器 DECLARE v_Debu

19、g char(1) default 0 ;- 调试开关 /* = = 程 序 错 误 提示相关的变量与处理 */ DECLARE s_rtcd INTEGER DEFAULT 0; - 错误的 SQL 代码 DECLARE s_rtst CHAR(5) DEFAULT 00000; - 错误的 SQL 状态 DECLARE s_text varchar(500); - 注释文本 DECLARE s_prdname varchar(30) DEFAULT PRO_CORE.MODEL; DECLARE s_sysname varchar(18) DEFAULT 测试用例 ; DECLARE s_

20、errlvlchar(4) DEFAULT 提示 ; DECLARE putlineout char(5); DECLARE SQLCODE INTEGER DEFAULT 0; DECLARE SQLSTATE CHAR(5) DEFAULT 00000; DECLARE error_message varchar(1024) DEFAULT ; DECLARE continue HANDLER FOR SQLWARNING BEGIN GET DIAGNOSTICS EXCEPTION 1 error_message = MESSAGE_TEXT; set s_rtcd = SQLCODE

21、; set s_rtst = SQLSTATE; set s_errlvl= 警告 ; if s_rtst 01003 then set s_text = s_text|-sqlcode: | cast(s_rtcd as char(5)|-sqlstate: |s_rtst; insert intoDWCTRL.CT_LOG_CT values( I_Date, s_sysname, s_prdname, CURRENT TIMESTAMP , s_errlvl, s_text, error_message); end if ; END; DECLARE exit HANDLER FOR S

22、QLEXCEPTION BEGIN GET DIAGNOSTICS EXCEPTION 1 error_message = MESSAGE_TEXT; set s_rtcd = SQLCODE; set s_rtst = SQLSTATE; rollback work; set s_text = s_text|-sqlcode: | cast(s_rtcd as char(5)|-sqlstate: |s_rtst; set s_errlvl= 错误 ; insert into DWCTRL.CT_LOG_CT values( I_Date, s_sysname, s_prdname, CUR

23、RENT TIMESTAMP , s_errlvl, s_text, error_message); commit work; set O_APPMSG = s_text ; set O_SYSMSG = error_message set O_RTCD = -1 ; END; /* 程序错误 提示处理结束 以下为程序正 文 */ set O_APPMSG = ; set O_SYSMSG = ; set O_RTCD = 0; set s_text = 过程开始 | s_prdname ; if v_Debug = 0 then values DWCTRL.put_line(s_text|s

24、_prdname) into putlineout ; end if ; set s_errlvl= 提示 ; insert into DWCTRL.CT_LOG_CT values( I_Date, s_sysname, s_prdname, CURRENT TIMESTAMP , s_errlvl, s_text, error_message); commit work ; 程序主体 beg set s_text = 初始化变量 ; set v_Cnt = 0 ; set s_text = 定义临时表 declare global temporary table tmp_test( clt

25、_num int ) not logged with replace in usrtmp_tbs ; - 循环开始 while v_Cnt 10 do set s_text = 将数据插入临时表中 ; insert into session.tmp_test(clt_num) values (v_Cnt) ; set v_Cnt = v_Cnt + 1 ; end while ; - 循环结束 程序主体 end commit work ; | s_prdname ; set s_text = 过程开始 set s_errlvl= 提示 ; if v_Debug = 0 then values DWCTRL.put_line(s_text|s_prdname) into putlineout ; end if ; in sert into DWCTRL.CT LOG CT values( l_Date, s_sys name, s_prd name, CURRENT TIMESTAMP , s_errlvl, s_text, error_message); com