DB2存储过程编写规范

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

2、t 的使用12 六全表删除12 七.MERGE( UPSERT 的使用 12 第六章.附录A 13 第一章.前言 一. 编写目的 为了提高开发效率和程序的可读性，降低程序编写过程的出错率 和重复劳动性，保持程序编写风格的一致性和连贯性，特定此规范。 二. 编写背景 目前数据库工具有很多种，考虑到数据仓库开发的实用性，数据 仓库开发工具选择了 DB2 三适用范围 本规范适用于招商银行信息技术部开发人员以及运行管理人员， 从事DB2存储过程开发的相关技术人必须按照此规范编写存储过程。 第二章.程序结构 一.整体结构 创建DB2存储过程必须按如下标准格式书写： DROP PROCEDURE式名 .过

3、程名 CREATE PROCEDURE式名.过程名 （ IN|OUT 输入|输出变量名输入|输出变量类型 ，. ） SPECIFIC模式名.过程名 LANGUAGE SQL /*程序说明*/ BEGIN 程序体 END 其中： 1）模式名是用来指定该存储过程属于哪个模式下的，默认为编 译该过程的登录用户名，但为了过程的统一管理以及各系统间的相互 区分，必须要指定一个模式名，模式名由过程所属项目设计中统一制 2）存储过程可以参数不带参数，但如果带参数必须按照命名规 则写出参数名，明确指定参数类型，并显示定义参数的输入输出性质。 多个参数之间用换行和逗号分隔。 3）程序体一般包括变量定义、异常错误

4、处理、程序正文等。 4）整个过程结束的标志符为。 二.程序说明 程序说明是一种注释，是对存储过程作用、定义等的一种描述, 在程序正文开始前必须有必要的说明，其具体内容包括： 脚本名称：该存储过程名 功 能:存储过程的作用与所要完成的目的 参 数：对输入输出参数进行必要的说明 调用：如何调用该过程，调用条件等，并写出一个调用 的例子 返回值：将所有可能的返回值列出 创建时间：首次建立该过程的时间 作者：建立该过程的作者姓名 修改记录：依次记录每次修改的修改人、修改日期、修改原 因等信息 三变量定义 变量定义规则如下： DECLARE 变量名1变量类型DEFAULT默认值; DECLARE 变量名

5、2变量类型DEFAULT默认值; 原则上每个变量的定义都要单独放置一行，并以分号结尾。 变量的定义应该出现在存储过程的程序体开始。 四.异常错误处理 异常错误处理是正文中必不可少的一部分，有着特定的作用 当过程执行出现警告或错误时需要通过异常处理来判断是否继续 执行该过程。它通常在程序变量定义与程序正文之间进行定义。 一个规范的异常错误处理定义如下： DECLARE s_rtcdINTEGER DEFAULT 0; DECLARE s_rtstCHAR(5) DEFAULT 00000： DECLARE s_text VARCHAR(200); DECLARE s_prdname VARCHA

6、R(30) DEFAULT 存储过程名； DECLARE s_sysname VARCHAR(18) DEFAULT 运行系统名； DECLARE s_errlvl char(4) DEFAULT 提示； 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 erro

7、r_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; in sert into DWCTRL.CT_LOG_CT values(I_Date, s_sys name , s_prd name, CURRENT TIMESTAMP, s_errlvl, s_text, error_me

8、ssage); 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; rollback work; set s_text = s_text-sqlcode: | cast(s_rtcd as char(5)-sqlstate: |s_rtst; set s_errlvl=错误; s_text, error_message); in

9、 sert into DWCTRL.CT_LOG_CT values(l_Date, s_sys name , s_prd name, CURRENyiMESTAMP,s_errlvl, commit work; set O_APPMSG = s_text ; set O_SYSMSG = error_message set O_RTCD = -1 ; END; 程序正文是用来实现程序功能的一条或多条 SQL语句，每个存 储过程的程序正文必须存在至少一条 SQL语句。 非报表性质的过程在开始和结束时都必须插入系统日志信息， 报表性质的过程不需要插入日志。 数据仓库统一的日志信息表为：DWCTR

10、L.CT_LOG_CT 第三章.命名规范 程序中的命名要遵循“知名闻意”的总体规则，让他人能很快 很容易的明白其意思。 一.存储过程命名 存储过程模式名：每个项目中的过程都必须有统一的模式名， 模式名一般为大写，由项目设计前期统一制定。一些临时性质的过 程可使用自己的用户名作为模式名 过程名：按存储过程的功能进行分类，每类用一个统一的前缀 标识，存储过程的命名方法为前缀加能反映存储过程本身功能含义 的一串英文单词，前缀的定义有每个项目设计前期统一制定。 存储过程名一般为大写。 .参数命名 存储过程的参数分为输入参数（IN），输出参数（OUT和输入 输出参数（INTOUT,根据参数性质规定统一的

11、前缀标识，参数命 名的方法为前缀加有意义的英文名，前缀定义规则如下： 输入参数：I_ 输出参数： 0_ 输入输出参数：IO_ 英文名要遵循“知名闻意”的原则，一般一串有有意义的英文 名的第一个字母为大写，举例如下： l_Date表示输入日期参数 O_SysMsg表示输出系统的错误信息 IO_Clid 表示客户ID的输入输出 三变量命名 变量名一般可分为三种：系统变量，程序变量和游标变量，变 量命名方法为前缀加有意义的英文名，前缀标识其规则如下: 系统变量：s_ 程序变量：v_ 游标变量：c_ 变量名是不区分大小写的，但一般代表一个意思的名字的第一 个字母为大写，其余的为小写，女口： v_Dat

12、e表示日期变量；v_CDate 表示字符型的日期变量 四临时表命名 在程序中建立临时表时，其名称应以tmp_开头再加上有意义 的表名命名。 第四章.书写格式 一.表达范式 为便于阅读，请遵循下列建议： 1）书写SQL语句时，多个字段名之间应该用逗号和空格分开。 2）函数调用的参数之间使用个逗号隔开。 3）简单赋值语句左右最好保留空格，如： set v_i = 1 ; 4）每行最多只能写一条SQL语句。 5）当一条SQL语句过长时，需要分行书写。 二.段落缩进 在编写程序时要注意段落的缩进对齐，以突出嵌套的层次结 构，提高可读性。原则上规定以4个空格字符为一个缩进单位。不 能使用tab键做缩进字

13、符，否则在不同的编辑器中会出现意想不到 的结果。 三. 段落间隔 在完成一段比较明确的作用，开始另一目的的编写时，之间要 以空行隔开以便于阅读。例如，完成一条 in sert语句时，接着写 下条语句之间要健入一行空行。 四. 程序注释 存储过程的注释是一个必不可少的环节，注释可分为行注释和 段落注释。 行注释标识：- 段落注释标识：注释开始标识/* 注释结束标识*/ 注意：段落注释是不能嵌套使用的。 在以下的地方应该用到注释 存储过程说明 参数名 变量名 在编写者认为有必要的地方。 第五章.注意事项 一 固定的输出参数 除了报表过程外，其他过程有三个固定的输出参数，如下： OUT O_RTCD

14、 INT,- 输出错误信息（代码） OUT O_APPMSG varchar（300）,-输出错误信息（自定义文本） OUT O_SYSMSG varchar（300）-输出错误信息（系统） 参数O_RTCD是判断程序运行是否正确的参数具体输出值如 下： 值=o 则运行正确 值 o 则程序有错误 值 0 则程序出现警告，但不影响程序的运行 二临时表的使用 存储过程在处理多条记录时最好使用临时表，而不要使用游 标。 在建立临时表时先确认临时表空间的名字 在创建临时表中，只有在调试过程时才能使用ON COMMIT PRESERVE ROWS生，在正式上线时必须将该属性屏蔽。 三.数据的插入 在进行

15、insert操作时，应写全目标表的字段，以防止发生插 入错行等错误。 四. where条件 在编写where条件时如果有常量条件的，尽量将其放在前面。 如果有partitioning key字段条件时，不论是否是多余条件都 应该写上。 五. count 的使用 在使用的count(*)时可尽量用count (索引列)来替代，或 用count(1)也可。 六. 全表删除 在进行全表删除时，如果不需要记录事务日志，可以调用如下过 程以提高执行效率： etl_ctrlo ad_empty(i ntable name varchar(80), out rtcd int, out appmsg varc

16、har(254), out sysmsg varchar(1024) 注意：对含有历史数据的表进行删除要慎用。 七. MERGRUPSERT的使用 对表进行DELETE寸并不会立即释放表空间，因此需要对同一张 表进行UPDATE + INSERT!作时请尽量使用 MERGE代替。其在效率 方面也会比UPDATE + INSERT强许多 第六章附录A 一个规范的DB2存储过程示例: DROP PROCEDURE PRO_CORE.MODEL CREATE PROCEDURE PRO_CORE.MODEL ( IN l_Date DATE,- OUT O_RTCD INT,- OUT O_APPM

17、SG varchar(300),- OUT O_SYSMSG varchar(300)- 统计日期 输出错误信息（代码） 输出错误信息（自定义文本） 输出错误信息（系统） ) SPECIFIC PRO_CORE.MODEL LANGUAGE SQL /* -脚本名称：PRO_CORE.MODEL -目 的：编写该存储过程的目的 -例 程：call PRO_CORE.MODEL(current date ,?,?,?) -作 者：张三 -日 期：2007.2.1 -修改版本: -modi by -modi by 李四2007.3.1修改统计类型 王二2007.3.2 修改将表aa修改成bb */

18、 BEGIN /* = 程序变量=*/ DECLARE v_C nt int ;-计数器 DECLARE v_Debug 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_prd name varchar(30) DEFAULT PRO_CORE.MODEL; DECLARE s

19、_sysname varchar(18) DEFAULT 测试用例； DECLARE s_errlvl char(4) DEFAULT 提示； DECLARE putli neout 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_

20、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; in sert into DWCTRL.CT_LOG_CT values( I_Date, s_sys name, s_prd name, CURRENT TIMESTAMP, s_errlvl, s_text, error_message

21、); 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; rollback work; set s_text = s_text|卜-sqlcode: | cast(s_rtcd as char(5)|-sqlstate: |s_rtst; set s_errlvl= 错误; in sert into DWCTRL.CT_LOG_

22、CT values( I_Date, s_sys name, s_prd name, 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; 以下为程序正文= /* =程序错误提示处理结束 */ set O_APPMSG = ” set O_RTCD = 0; set s_text =过程开始 | s_prdn ame ; if v_Debug = 0 the n

23、values DWCTRL.put_li ne(s_text|s_prd name) into putl in eout ; end if ; set s_errlvl= 提示; in sert into DWCTRL.CT_LOG_CT values( I_Date, s_sys name, s_prd name, CURRENT TIMESTAMP, s_errlvl, s_text, error_message); commit work ; 程序主体beg set s_text =初始化变量； set v_C nt = 0 ; set s_text = 定义临时表； declare g

24、lobal temporary table tmp_test( clt_num int ) not logged with replace in usrtmp_tbs ; - 循环开始 while v_Cnt 10 do set s_text =将数据插入临时表中； in sert into sessi on .tmp_test(clt_ num) values (v_C nt); set v_C nt = v_Cnt + 1 ; end while ; - 循环结束 程序主体end commit work ; set s_text =过程开始 | s_prdn ame ; set s_err

25、lvl= 提示; if v_Debug = 0 the n values DWCTRL.put_li ne(s_text|s_prd name) into putl in eout ; 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); commit work; END Whe n you are old and grey and full of sleep, And no ddi ng by the fire, take dow n this book, And slowly read, and dream of the soft look Your eyes had once, and of their shadows deep; How many loved your mome nts of glad grace, And loved your beauty with love false or true, But one man loved the pilgr