DB2存储过程编写规范.doc

数据库技术标准系列DB2存储过程编写规范版本号：1.0修订记录：修订日期修订版本修订人修订内容2007-03-011.0潘冬梅制定DB2存储过程编写规范目录第一章.前言3一.编写目的3二.编写背景3三.适用范围3第二章.程序结构4一.整体结构4二.程序说明5三.变量定义5四.异常错误处理6五.程序正文7第三章.命名规范7一.存储过程命名7二.参数命名8三.变量命名8四.临时表命名9第四章.书写格式9一.表达范式9二.段落缩进9三.段落间隔10四.程序注释10第五章.注意事项11一.固定的输出参数11二.临时表的使用11三.数据的插入11四.where 条件12五.count 的使用12六.全表删除12七.MERGE（UPSERT） 的使用12第六章.附录A12第一章.前言一.编写目的为了提高开发效率和程序的可读性，降低程序编写过程的出错率和重复劳动性，保持程序编写风格的一致性和连贯性，特定此规范。二.编写背景目前数据库工具有很多种，考虑到数据仓库开发的实用性，数据仓库开发工具选择了DB2。三.适用范围本规范适用于招商银行信息技术部开发人员以及运行管理人员，从事DB2存储过程开发的相关技术人必须按照此规范编写存储过程。第二章.程序结构一.整体结构创建DB2存储过程必须按如下标准格式书写：DROP PROCEDURE 模式名.过程名CREATE PROCEDURE 模式名.过程名( IN|OUT输入|输出变量名 输入|输出变量类型 , . )SPECIFIC模式名.过程名LANGUAGE SQL/* 程序说明 */BEGINEND其中：1） 模式名是用来指定该存储过程属于哪个模式下的，默认为编译该过程的登录用户名，但为了过程的统一管理以及各系统间的相互区分，必须要指定一个模式名，模式名由过程所属项目设计中统一制定。2） 存储过程可以参数不带参数，但如果带参数必须按照命名规则写出参数名，明确指定参数类型，并显示定义参数的输入输出性质。多个参数之间用换行和逗号分隔。3） 程序体一般包括变量定义、异常错误处理、程序正文等。4） 整个过程结束的标志符为 。二.程序说明程序说明是一种注释，是对存储过程作用、定义等的一种描述，在程序正文开始前必须有必要的说明，其具体内容包括：脚本名称：该存储过程名功 能：存储过程的作用与所要完成的目的参 数: 对输入输出参数进行必要的说明调 用：如何调用该过程，调用条件等，并写出一个调用的例子返 回 值：将所有可能的返回值列出创建时间：首次建立该过程的时间作 者：建立该过程的作者姓名修改记录：依次记录每次修改的修改人、修改日期、修改原因等信息三.变量定义变量定义规则如下： DECLARE 变量名1 变量类型 DEFAULT 默认值; DECLARE 变量名2 变量类型 DEFAULT 默认值; 原则上每个变量的定义都要单独放置一行，并以分号结尾。变量的定义应该出现在存储过程的程序体开始。四.异常错误处理异常错误处理是正文中必不可少的一部分，有着特定的作用。当过程执行出现警告或错误时需要通过异常处理来判断是否继续执行该过程。它通常在程序变量定义与程序正文之间进行定义。一个规范的异常错误处理定义如下：DECLARE s_rtcd INTEGER DEFAULT 0; DECLARE s_rtst CHAR(5) DEFAULT 00000; DECLARE s_text VARCHAR(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) 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;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 into 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; 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, 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;五.程序正文程序正文是用来实现程序功能的一条或多条SQL语句，每个存储过程的程序正文必须存在至少一条SQL语句。非报表性质的过程在开始和结束时都必须插入系统日志信息，报表性质的过程不需要插入日志。数据仓库统一的日志信息表为： DWCTRL.CT_LOG_CT第三章.命名规范程序中的命名要遵循“知名闻意”的总体规则，让他人能很快很容易的明白其意思。一.存储过程命名存储过程模式名：每个项目中的过程都必须有统一的模式名，模式名一般为大写，由项目设计前期统一制定。一些临时性质的过程可使用自己的用户名作为模式名。过程名：按存储过程的功能进行分类，每类用一个统一的前缀标识，存储过程的命名方法为前缀加能反映存储过程本身功能含义的一串英文单词，前缀的定义有每个项目设计前期统一制定。存储过程名一般为大写。二.参数命名存储过程的参数分为输入参数（IN），输出参数（OUT）和输入输出参数（INTOUT），根据参数性质规定统一的前缀标识，参数命名的方法为前缀加有意义的英文名，前缀定义规则如下：输入参数：I_ 输出参数：O_ 输入输出参数：IO_ 英文名要遵循“知名闻意”的原则，一般一串有有意义的英文名的第一个字母为大写，举例如下：I_Date 表示输入日期参数O_SysMsg 表示输出系统的错误信息IO_Clid 表示客户ID的输入输出三.变量命名变量名一般可分为三种：系统变量，程序变量和游标变量，变量命名方法为前缀加有意义的英文名，前缀标识其规则如下：系统变量：s_程序变量：v_游标变量：c_变量名是不区分大小写的，但一般代表一个意思的名字的第一个字母为大写，其余的为小写，如： v_Date 表示日期变量；v_CDate 表示字符型的日期变量四.临时表命名在程序中建立临时表时，其名称应以 tmp_ 开头再加上有意义的表名命名。第四章.书写格式一.表达范式为便于阅读，请遵循下列建议：1)书写SQL语句时，多个字段名之间应该用逗号和空格分开。2)函数调用的参数之间使用个逗号隔开。3)简单赋值语句左右最好保留空格，如：set v_i = 1 ;4)每行最多只能写一条SQL语句。5)当一条SQL语句过长时，需要分行书写。二.段落缩进在编写程序时要注意段落的缩进对齐，以突出嵌套的层次结构，提高可读性。原则上规定以4个空格字符为一个缩进单位。不能使用tab键做缩进字符，否则在不同的编辑器中会出现意想不到的结果。三.段落间隔在完成一段比较明确的作用，开始另一目的的编写时，之间要以空行隔开以便于阅读。例如，完成一条insert 语句时，接着写下条语句之间要健入一行空行。四.程序注释存储过程的注释是一个必不可少的环节，注释可分为行注释和段落注释。行注释标识： - 段落注释标识： 注释开始标识 /*注释结束标识 */ 注意：段落注释是不能嵌套使用的。在以下的地方应该用到注释存储过程说明参数名变量名在编写者认为有必要的地方。第五章.注意事项一.固定的输出参数除了报表过程外，其他过程有三个固定的输出参数，如下：OUT O_RTCD INT, - 输出错误信息(代码)OUT O_APPMSG varchar(300), - 输出错误信息(自定义文本)OUT O_SYSMSG varchar(300) - 输出错误信息(系统)参数 O_RTCD 是判断程序运行是否正确的参数具体输出值如下：值0 则运行正确值 0 则程序出现警告，但不影响程序的运行 二.临时表的使用存储过程在处理多条记录时最好使用临时表，而不要使用游标。在建立临时表时先确认临时表空间的名字在创建临时表中，只有在调试过程时才能使用 ON COMMIT PRESERVE ROWS 属性，在正式上线时必须将该属性屏蔽。三.数据的插入在进行insert操作时，应写全目标表的字段，以防止发生插入错行等错误。四.where 条件在编写where条件时如果有常量条件的，尽量将其放在前面。如果有partitioning key字段条件时，不论是否是多余条件都应该写上。五.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时并不会立即释放表空间，因此需要对同一张表进行UPDATE + INSERT操作时请尽量使用 MERGE 代替。其在效率方面也会比 UPDATE + INSERT 强许多。第六章.附录A一个规范的DB2存储过程示例：DROP PROCEDURE PRO_CORE.MODELCREATE PROCEDURE PRO_CORE.MODEL( IN I_Date DATE, - 统计日期 OUT O_RTCD INT, - 输出错误信息(代码) OUT O_APPMSG varchar(300), - 输出错误信息(自定义文本) OUT O_SYSMSG varchar(300) - 输出错误信息(系统) )SPECIFIC PRO_CORE.MODELLANGUAGE 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_Debugchar(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_errlvl char(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; 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 into 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; 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, 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_SYSMSG = ; set O_RTCD = 0; set s_text = 过程开始 | s_prdname ; if v_Debug = 0 then values DWCTRL.put_line(s_text|s_prdname) into putlineout ; end if ; set s_errlvl=提示; insert into DWCTR