ODS-ETL开发规范V0.1

文档编号：ODS-11-T-2001

中信银行ODS系统

DB2 SQL及存储过程编写规范

当前版本：V1.0

版本日期：2008年6月20日

文件信息

此文件需如下审核

此文档将分发至如下个人或机构

1. 前言

本标准是针对IBM DB2数据库SQL编写设计而起草的开发规范；同时，也作为中信银行ODS项目ETL组评审ETL任务开发质量的标准之一。

本标准将作为中信银行ODS项目规范开发系列之一，并且需要在项目实施工作中不断改进和完善，保障规范能够真正的提高质量和效率。

2. 引言

2.1. 总则

1．整个ODS系统中ETL开发的编码，包括基于封装的程序包中的代码，在对象的命名和使用、程序的注释和排版，都应当注重规范

2．在编码的过程中，应时刻牢记优化的重要性，对重要程序块或程序包需要注明其逻辑结构。在必要的时候，还应进行代码评审

2.2. 应用

1．本规范适用各IBM DB2数据库和中信银行ODS项目ETL开发的程序包 2．应用于ODS系统基于数据库的ETL模块开发并对应用集市相关开发提供参考与指导

2.3. 参考文档

《T-SQL Coding Standards》——Brian Walker

3. 术语和定义

3.1. 描述术语

本文档采用以下的术语描述：

规范：编程时强制必须遵守的原则标准：量化的规范。 说明：对此规则或建议进行必要的说明和解释。 示例：对此规则或建议给出适当的例子。

3.2. 编码术语

编码(Coding) 关键字(Keyword) 函数(Function) 存储过程(Procedure) 变量(Variable) 游标(Cursor)

ETL(Extraction、Translation、Loading)

4. 编码规范

4.1. 命名规范

1．命名只能使用26个英文字母、下划线或阿拉伯数字，不能使用汉字。 2．所有关键字和函数大写；变量、游标、表名、列名可以混合大小写，也可以小写，但不得大写；数据类型小写。 3．对象命名不能超过8个英文字母，前缀和单词之间用下划线分隔，尽量不采用汉语拼音，使用英文单词或公认单词缩写，单词缩写可通过去掉“元音”形成。 4．名称具有复数意义时，使用名词的正确的复数形式

5．当一个SQL 语句中涉及到多个表时，始终使用表名别名来限定字段名。这使其他人阅读起来更清楚，避免了含义模糊的引用。

6．一般情况下，列名称不应包含表名或者表名的任何形式，列名不允许使用统一的前缀。

7．所有存储过程的前缀统一使用”proc_” 8．用户Schema不允许使用系统Schema。

4.2. 注释规范

1. 开始注释。

示例：

2. 可采用单行/多行注释。（-- 或 /* */ 方式）

示例：

login_id VARCHAR2(32) NOT NULL, -- 会员标识

3. 注释单独成行、放在语句前面.注释的内容要清楚、明了，含义准确，无二

义性。 4. 对变量的定义和分支语句（条件分支、循环分支等）必须编写注释。对较为

复杂的SQL语句加上注释，说明算法、功能. 5. 通过对函数、变量、结构等正确的命名以及合理地组织代码的结构，使代码

成为自注释的。

4.3. 排版规范

1. 缩进

1.1. 低级别语句在高级别语句后的，一般缩进4个空格, 建议使用Ultraedit作

为SQL书写工具： 示例：

DECLARE

v_MemberId VARCHAR2(32); SET STARTM = CHAR(CURRENT TIME); BEGIN

SELECT admin_member_id INTO v_MemberId FROM company WHERE id = 10;

Drop Table Session.temp_company; END;

1.2. 同一语句不同部分的缩进，如果为sub statement，则通常为2个空格，如

果与上一句某部分有密切联系的，则缩至与其对齐：

示例： BEGIN

FOR v_TmpRec IN

(SELECT login_id,

gmt_created, -- here indented as column above satus

FROM member -- sub statement WHERE site = 'china'

AND country='cn' )

For

NULL; End For;

END; 2. 断行

2.1. 一行最长不能超过80字符 2.2. 同一语句不同字句之间 2.3. 逗号以后空格

2.4. 连接符OR、IN、AND、以及＝、<=、>=等前后加上一个空格。 2.5. 其他分割符前空格

示例：

SELECT offer_name||',' ||offer_count as offer_category,

id

FROM category

WHERE super_category_id_1 = 0;

3. 当相关数字出现在连续的代码行中时（例如一系列 SUBSTRING 函数调用），

建议将它们排成列。这样容易浏览数字列表。

4.4. 数据库对象定义和使用规范

1. 始终指定字符数据类型的长度，并确保允许用户可能需要的最大字符数，因

为超出最大长度的字符会丢失

2. 避免使用“未声明的”功能，例如系统表中未声明的列、SQL 语句中未声明

的功能或者未声明的系统存储过程或扩展的存储过程。

3. 不要依赖任何隐式的数据类型转换。例如，不能为数字变量赋予字符值，而

假定 SQL 会进行必要的转换。相反，在为变量赋值或比较值之前，应使用适当的 CONVERT 函数使数据类型相匹配。另一个示例：虽然 SQL 会在进行比较之前对字符表达式进行隐式且自动的 RTRIM，但不能依赖此行为，因为兼容性级别设置非字符表达式会使情况复杂化。

4. 不要将空的变量值直接与比较运算符（符号）比较。如果变量可能为空，应

使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。

5. 通常，应将 ORDER BY 子句与 SELECT 语句一起使用。可预知的顺序（即使

不是最方便的）比不可预知的顺序强，尤其是在开发或调试过程中。（部署到生产环境中之前，可能需要删除 ORDER BY 子句。）在返回行的顺序无关紧要的情况下，可以忽略 ORDER BY 的开销。

6. 不要在 SQL 代码中使用双引号。应为字符常量使用单引号。如果没有必要

限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。 7. 避免在WHERE字句中对列施以函数

示例： SELECT *

FROM service_promotion

WHERE TO_CHAR(gmt_modified,’yyyy-mm-dd’) = ‘20001-09-01’;

而应使用：

SELECT *

FROM service_promotion

WHERE gmt_modified >= TO_DATE(‘2001-9-01’,’yyyy-mm-dd’)

AND gmt_modified < TO_DATE(‘2001-9-02’,’yyyy-mm-dd’);

8. 避免使用数据库的类型自动转换功能

示例： SELECT *

FROM category

WHERE id = ‘123’; -- id’s type is number

9. 要认识到临时表并不是不可使用，适当地使用它们可以使某些例程更有效，

例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。

10. 只要在例程中使用多个数据库修改语句，包括在一个循环中多次执行一个语

句，就应考虑声明显式事务。

11. 为了提高性能，应优先使用连接，然后使用子查询或嵌套查询。多表连接时，

使用表的别名来引用列。

4.5. 变量规范

1. 变量名全部采用小写，局部变量名使用“v_”开头，输入参数以“i_开头，输出参数以“o_”开头，输入输出参数用io_开头。 所有输入参数必须显示声明

2. 游标的命名：游标统一用后缀 “_cur” 命名

使用命名的常量以避免硬编码，使用常量包,常量统一用 cn_ 的前缀命名

4.6. 游标规范

1. 外部查询的多行数据返回使用游标进行处理，通过传递游标变量的形式返回

数据到外部接口,由外部程序自行FETCH数据。 2. 打开游标前，必须显式检查游标的%ISOPEN属性。

3. 使用FETCH语句后，要立即检查%NOTFOUND属性，以便正常终止游标

FETCH循环。

4. 无论PROCSQL程序是正常终止还是出错退出，都要关闭所有已打开的游标。

在出错退出时，应该在其异常处理部分管理所有游标，这可以释放一部分的系统资源

4.7. 事务处理规范

1. 在需要分割事务以使主事务的提交或者回滚独立于子事务的提交及回滚时，应使用自治事务

2. 所有的存储过程均统一在结束处统一COMMIT或者ROLLBACK

4.8. 数据封装规范

1. 按照业务逻辑实现功能模块的封装，将业务规则逻辑集中在更少量的、良好设计的、易于维护的函数或者过程中，不必在每条SQL语句或者每天PL/SQL程序中重复这些逻辑

2. 基于单一数据表的增、删、改、查采用标准SP进行封装，不允许相同逻辑的处理出现在多个SP中

4.9. 数据访问规范

1. 后台数据按照逻辑划分成多个SCHEMA，不同SCHEMA的数据不可互相访问

2. 需要相互访问的表均存放在 ***的SCHEMA中，通过访问***中的接口表实现跨SCHEMA的数据访问

4.10. 日志书写规范

1. 采用公共的API包完成后台日志数据记录。（API完成输出错误信息提示、记录错误信息内容到数据库表、系统级的错误代码及错误信息等）

2. 后台日志的信息记录级别包括DEBUG、INFO、WARN、ERROR、FATAL，其定义以及不同级别日志的采集标准如下：

1) DEBUG－调试信息，供开发人员调试使用，由开发人员自行确定。 2) INFO－提示信息，记录重要的系统事件或者业务事件，如开始结算等。 3) WARN－警告信息，可能导致严重错误的警告信息 4) ERROR－错误信息，导致系统运行错误的信息。

5) FATAL－致命信息，导致系统无法继续运行的致命错误信息。 3. 所有表操作的错误处理部分均应记录日志信息

4.11. 日志处理规范

采用公共的Package包和包体（Package_body）中的存储过程PLOG跟踪后台日志数据处理记录。（Package包和包体Package_body完成输出错误信息提示、记录错误信息内容到数据库表、系统级的错误代码及错误信息等） 后台日志的信息记录级别包括INFO、ERROR，其定义以及不同级别日志的采集标准如下：

INFO－提示信息，记录重要的系统事件或者业务事件，供开发人员调试使用，由开发人员自行确定

ERROR－错误信息，导致系统运行错误的信息；可能导致严重错误的警告信息；导致系统无法继续运行的致命错误信息。 所有表操作的错误处理部分均应记录日志信息 日志处理应用 运行时间处理日志

为了方便跟踪、定位程序的实时运行时间，为方便跟踪定位局部程序运行时间，需要增加时间处理日志。为程序的优化的估量提供参考性的参数标准。 Eg.

SELECT current timestamp INTO end_time FROM sysibm.dummy;

per_sec := (end_time - start_time) * 86400;

(v_prg_name,Date(SYSDATE, 'hh:mi:ss')||':结束向M区债

项表录入数据,完成记录数：'||rcount||',执行耗时:'||TRUNC (per_sec, 2)||'sec,执行速率:

'||TRUNC ((rcount / NVL(per_sec,1)), 2)|| '/sec') 区分错误日志

由于生产环境的复杂性和可变性，以及程序的复杂性，需要增加区分错误日志的功能，方便定位出错程序的具体位置，提高处理出错程序的效率，保证生产环境得以正常运转。

合理的利用[error(prg_name varchar, err_code varchar, err_msg varchar)]、[info(prg_name varchar, info_msg varchar)]过程，增加区分错误日志的功能。

4.12. 错误处理规范

1. 凡是涉及到表操作(insert,update,select,delete)的sql语句,都必须进行错误捕捉,

不能将错误带到后面的语句

2. 从表中select数据的语句,应严格区分NO_FOUND 和 TOO_MANY_ROWS

的错误,并将相应错误信息填入错误信息

3. 所有程序中捕获到的错误,均转换成对应的errcode,errmsg,通过输出参数返回

给调用者,所有存储过程（函数）结束前应统一捕获系统异常

4. 在每个存储过程(函数)的入口处统一先将返回错误代码(errCode)设置为-999,

功能处理成功结束后再将错误代码(errCode)设置为0(成功),避免程序过程中因错误未能正确捕获,导致功能未能完成,而程序却成功返回的情况出现 5. 所有的模块都有错误编码区间,原则上错误编码全局唯一

6. 错误信息描述应准确，业务相关的错误应将输入数据拼接到错误信息中。 7. 错误代码根据存储过程（函数）的功能号来确定，具体规则为

a) 错误代码＝功能号后7位＋XX，（XX 为 00 －99）

8. 即分配给每个功能点的错误代码为100个，由开发者自己划分错误代码，自

行描述错误信息，不做具体规定

9. 能截获DB2错误码，填入SQLCODE值 。

4.13. 与ETL plus 接口相匹配的日志处理

1. 在ETL PLUS 调度的过程中，会有下面这个日志表。对于各个应用来说，ETL的日志信息都应该插入到该表。

2. 由于同时使用该日志信息表的人多，为了避免出现表的死锁等问题，同时输入的各个日志信息又要符合ETL PLUS中日志管理的要求，各个ETL程序必须要按照如下要求插入日志信息。

建议：由于该表创建在ETL PLUS所在服务器上，各个应用系统需要在本数据库暂时创建此表。以后会统一配置后，做到将数据直接库数据库插入日志信息。 日志信息表 PROG_LOG CREATE TABLE PROG_LOG (

MACHINE_TM TIMESTAMP, --机器时间 DATA_DT DATE, --数据日期 PROG_TP CHAR, --程序类型 PROG_NM VARCHAR(255), --程序名 INFO_TP CHAR, --日志信息类型 ERROR_CD CHAR(8), --错误码 INFO_CONTEXT VARCHAR(1024) --日志信息内容

);

程序类型：0-DS Job ；1-存储过程 ；2-Perl程序 ；3-可执行文件 日志信息类型：I-正常 ；W-警告 ；E-错误

错误码:日志信息类型为警告、错误时，能截获DB2错误码，填入SQLCODE 值。 日志信息内容: 建议填入信息内容 + SQL ;

最后在异常处理的块中，按各个字段的要求做数据插入： 如下：

INSERT INTO PROG_LOG(

MACHINE_TM TIMESTAMP, --机器时间 DATA_DT DATE, --数据日期 PROG_TP CHAR, --程序类型 PROG_NM VARCHAR(255), --程序名 INFO_TP CHAR, --日志信息类型 ERROR_CD CHAR(8), --错误码 INFO_CONTEXT VARCHAR(1024) --日志信息内容 ) VALUES(

CURRENT TIMESTAMP, --机器时间 DATE(i_WorkDate), --运行日期 prog_typ, --程序类型 ProcName, --程序名 SqlLevel, --日志信息类型 SqlCode ,--错误码 Action --日志信息内容 ) ;

3. 按照异常和日志的总体要求，每个ETL程序都必须结合自身ETL的流程处理，添加相关的异常处理。在对日志表数据做清理的时候，请只删除自己写入的日志信息。

5. 性能建议和经验性规则

5.1. 书写优化性能建议

1. 避免嵌套连接。例如：A = B AND B = C AND C = D 2. WHERE条件中尽量减少使用常量比较，改用主机变量

3. 系统可能选择基于规则的优化器，所以将结果集返回数据量小的表作为驱动

表（FROM后边最后一个表）。

4. 大量的排序操作影响系统性能，所以尽量减少ORDER BY和GROUP BY排

序操作。 如必须使用排序操作，请遵循如下规则 a) 排序尽量建立在有索引的列上。

b) 如结果集不需唯一，使用UNION ALL代替UNION。 5. 索引的使用

a) 尽量避免对索引列进行计算。

b) 尽量注意比较值与索引列数据类型的一致性。 c) 对于复合索引，SQL语句必须使用主索引列 d) 索引中，尽量避免使用NULL。

e) 对于索引的比较，尽量避免使用!= 查询列和排序列与索引列次序保持一

致

6. 尽量避免相同语句由于书写格式的不同，而导致多次语法分析。 7. 尽量使用共享的SQL语句。

8. 查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面。 9. 任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查

询时要尽可能将操作移至等号右边。

10. IN、OR子句常会使用工作表，使索引失效；如果不产生大量重复值，可以

考虑把子句拆开；拆开的子句中应该包含索引。

5.2. 其他经验性规则

4. 使用UNION ALL、MINUS、INTERSECT提高性能

5. 使用ROWID提高检索速度。对SELECT得到的单行记录，需进行DELETE、

UPDATE操作时，使用ROWID将会使效率大大提高。

6. 使用优化线索机制进行访问路径控制。 7. 使用cursor时，显示光标优于隐式光标 。

6. 存储过程编写说明

6.1. SQL存储过程说明

请参见参考程序“XX.db2”

6.2. 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

第1行：Create Procedure admin.BalanceSheetDayly定义了过程名称 参数列表为Out ProcState varchar(100)

其定义SQL过程从客户应用获取，或返回客户应用的0个或多个参数，参数列表使用逗号分割各个参数 参数类型有三种： IN OUT

从客户应用检索值。其不能够在SQL过程体中修改 向客户应用返回值

范例见“参考程序”章节：

INOUT 从客户应用检索值，并返回值

省略了结果集数量的定义，default为0。即表示不返回结果集。

第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，将返回一个结果集。

6.3. SQL过程体 6.4. 声明、设置变量

第9~61行。

必须在SQL过程体的第一部分中声明变量。必须指定惟一的标识符，声明SQL数据类型、并且可以先把指定变量的初始值。变量声明的语法如下： DECLARE 标识符 SQL数据类型 [DEFAULT 默认值] 1、SQLCODE、SQLSTATE变量 13~14行。

用于在SQL过程中处理错误和排错问题。它们的值代表了SQL过程体中最后使用的SQL过程体中最后使用的SQL语句的返回值。 2、游标申明 37~49行。

3、条件处理器 50~61行。

当SQL语句返回超过00000的SQLSTATE值时，会产生一个条件，表示出现了错误、数据没有找到或者警告。

条件处理器可以决定SQL过程将如何响应一个或多个已定义的条件或预定义条件组。其语法如下：

DECALRE 数据类型 HANDLER FOR 条件[， ] 其有三种处理类型：

CONTINUE 处理器操作完成后，继续执行产生这个条件的语句之后的下一条

语句。 EXIT

处理器操作完成后，SQL过程将终止，并将控制返回给调用者。

UNDO 处理器操作执行之前，DB2将回滚SQL过程中执行的SQL操作。完成

后，SQL过程将终止，并将控制返回给调用者。 其预定义了3个类的条件：

NOT FOUND 标识导致SQLCODE值为+100或SQLSTATE值为02000的条件。

一般在使用SELECT语句时出现。 SQLEXCEPTION 标识导致SQLCODE值为负的条件 SQLWARNING 警告条件或导致SQLCODE>100的条件

6.5. 控制结构流

常用的结构： 1、 SET

为输出参数或者SQL变量赋值。 例如：

set at_end=0;(66行)

set vProcState=char(0)||'00000 Success'; (164行) set vBal302=vBal302+vBal;(89行) 2、 IF

IF 条件1 THEN

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行

6.6. 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

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，且此结果集需要被返回给调用者时，则不能够被关闭。

INTO

本文来源：https://www.bwwdw.com/article/v171.html