[训练]DB2存储过程编写规范

[训练]DB2存储过程编写规范
DB2存储过程编写规范
版本号:1.0
修订记录:
修订日期修订版本修订人修订内容 2007-03-01 1.0 潘冬梅
制定DB2存储过程编写规范
目录
第一章.前言 (5)
一.编写目的 (5)
二.编写背景 (5)
三.适用范围 (5)
程序结构 (6)
第二章.
一.整体结构......................................................... 6 二.程序说明......................................................... 7 三.变量定义 (7)
四.异常错误处理 (8)
五.程序正文 (10)
第三章.命名规范 .......................................................
10 一.存储过程命名 (10)
二.参数命名 (11)
三.变量命名 (11)
四.临时表命名 (12)
第四章.书写格式 .......................................................
12 一.表达范式 .......................................................
12 二.段落缩进 .......................................................
12
.段落间隔 (13)
三
四.程序注释 .......................................................
13 第五章.注意事项 (14)
一.固定的输出参数 (14)
二.临时表的使用 (14)
三.数据的插入 (15)
四.where 条件 (15)
五.count 的使用 (15)
六.全表删除 (15)
七.MERGE,UPSERT, 的使用 (15)
第六章.附录A............................................................
16
第一章.前言
一.编写目的
为了提高开发效率和程序的可读性～降低程序编写过程的出错率和重复劳动性～保持程序编写风格的一致性和连贯性～特定此规范。

二.编写背景
目前数据库工具有很多种～考虑到数据仓库开发的实用性～数据仓库开发工具选择了DB2。

三.适用范围
本规范适用于招商银行信息技术部开发人员以及运行管理人员～从事DB2存储过程开发的相关技术人必须按照此规范编写存储过程。

第二章.程序结构
一.整体结构
创建DB2存储过程必须按如下标准格式书写:
DROP PROCEDURE 模式名.过程名@
CREATE PROCEDURE 模式名.过程名
(
IN|OUT 输入|输出变量名输入|输出变量类型
[ , ... ]
)
SPECIFIC模式名.过程名
LANGUAGE SQL
/* 程序说明 */
BEGIN
<程序体>
END@
其中:
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 则程序有错误
值> 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.MODEL@ CREATE 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.MODEL
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_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_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 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_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 ;
set s_text = '过程开始 , ' || s_prdname ;
set s_errlvl='提示';
if v_Debug = '0' then
values DWCTRL.put_line(s_text||s_prdname) into putlineout ; end if ;
insert into DWCTRL.CT_LOG_CT
values( I_Date, s_sysname, s_prdname,
CURRENT TIMESTAMP, s_errlvl, s_text, error_message);
commit work;
END@。