存储过程编写规范.docx

存储过程编写规范.docx

《存储过程编写规范.docx》由会员分享，可在线阅读，更多相关《存储过程编写规范.docx（11页珍藏版）》请在冰豆网上搜索。

存储过程编写规范

存储过程编写规范

XX有限公司

文档控制

文档名称

文档代码

文档描述

版本信息

版本号

修订日

修订者

审核者

描述

2014-10-01

2014-12-16

备注：

1、文档代码是标识文档类型的分类代码，主要用于文档的分类，查询和归档。

其编码规则请参阅相关文档。

2、文档描述主要用于文档的快速的分类和查询，使用关键字的方式体现文档的主要内容，需要体现主要功能，系统所属的业务部门，使用部门等关键信息。

3、版本信息中项目过程文档版本以前不需要每次审核发布，可以统一批次审批发布。

4、版本信息中项目文档版本正式发布后，每个版本发布需要审核者签字确认。

1.概述

1.1目的

本规范用于指导项目编写Oracle数据库存储过程（StoredProcedure）代码，以保证需求按时、安质实现。

1.2适用范围

本过程定义文档适用于上海通用汽车有限公司所有软件开发项目编码集成活动。

1.3术语解释

2.存储过程使用原则

应用系统的业务逻辑实现，必须严格按照系统架构师的设计方案进行。

应用系统的设计、开发必须保持公司应用系统架构的一致性、系统的可移植性、系统的可维护性、系统版本的可控制性。

原则上，自开发系统不建议使用存储过程。

如果有特殊情况需要使用存储过程，需要在架构师团队中立案说明，得到讨论认可后，方可进行存储过程的设计开发。

并且使用存储过程，须符合以下场景。

业务逻辑的处理涉及到多个数据表及大量数据记录，并且逻辑处理结果仅是一个比较小的结果集（一个结果、几条数据记录）。

业务逻辑处理在系统逻辑层实现比较困难，而采用SQL语句实现其功能时，需要大量SQL语句，并且涉及到多个数据表的数据更新的一致性问题，则可使用存储过程来实现。

应用系统的功能实现对系统性能有较大影响，并且经过证明，在存储过程中实现业务逻辑比在应用系统的业务逻辑层实现性能得到较大的提升。

应用系统的使用场景对网络带宽、流量有明显的限制。

3.开发规范

3.1存储体结构

--------------------------------------------------------------------------

CREATEORREPLACEPROCEDUREexample（parameters）--过程声明区

IS

--------------------------------------------------------------------------

/*描述存储过程名称，编写人，编写日期，修改人，修改日期，版本号等信息*/

--------------------------------------------------------------------------

/*声明部分*/

--------------------------------------------------------------------------

BEGIN

/*代码部分*/

EXCEPTION

/*异常处理部分*/

END;

--------------------------------------------------------------------------

3.2书写规范

3.2.1程序头书写规范

程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。

示例如下：

-----------------------------------------------------------------------------

/*

存储过程名称：

存储过程功能说明:

版本号:

（版本号标示：

新建小的修改变为大的修改重构创建日期：

YYYY-MM-DD创建人员：

***

修改日期：

YYYY-MM-DD修改人员：

***

修改原因：

修改内容：

修改日期：

YYYY-MM-DD修改人员：

***

修改原因：

修改内容：

涉及的表或视图：

表或视图名称用途

XXXX1YYYY1

XXXX2YYYY2

存储过程调用：

存储过程名称用途

XXXX1YYYY1

（描述此存储过程调用存储过程列表，如果没有调用任何存储过程，则填写“NONE”，不允许为空白。

）

*/

-----------------------------------------------

传入参数：

@X1

数据类型：

描述：

@X2

数据类型：

描述：

传出参数：

@X3

数据类型：

描述：

返回值：

@X4

数据类型：

描述：

------------------------------------------------------------------------------

3.2.2代码书写规范

语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、PL/SQL保留字、关键字大写，用户声明的标识符小写。

连接符OR、IN、AND、以及＝、<=、>=等前后加上一个空格。

WHERE子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。

查询的WHERE过滤，原则应使过滤记录数最多的条件放在最前面。

多表连接时，使用表的别名来引用列。

查找数据库表或视图时，只能取出确实需要的那些字段，不允许使用*来代替所有列名。

功能相似的过程和函数，尽量写到同一个包中，加强管理。

SQL语句采用缩进风格书写，保证代码清晰易读，风格一致。

缩进格数统一为2个空格，但需要关键字右对齐或者参数/字段向上对齐的时候例外。

一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进。

WHERE子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐。

例如：

Wheref1=1

andf2=2

andf3=3

不允许把多个短语句写在一行中，即一行只写一条语句。

相对独立的程序块之间、变量说明之后须加空行。

SQL语句中存在不同优先级的关系运算符时，使用括号进行隔离。

INSERT语句，必须书写字段，字段可5个或6个一组。

供别的文件或函数调用的函数，不允许使用全局变量交换数据；

3.2.3注释书写规范

为了提高可读性，应该使用一定数量的注释。

注释大约占总行数的10%。

注释风格：

注释单独成行、放在语句前面。

分支条件表达式必须加注释；

对重要的计算应加注释说明其功能；

过长的函数实现，应将其语句按实现的功能分段加以概括性说明；

每条SQL语句均应加注释说明

对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。

（--或/**/方式）

3.3命名规范

3.3.1存储过程名称

格式：

p_xx_yyyy

采用“p_2位模块名缩写_功能简单描述”的格式命名。

3.3.2存储过程的输入输出参数

格式：

i_xxx

o_yyy

io_zzz

参数命名规范为:

参数“方向缩写”加“描述”。

方向缩写：

i：

输入

o：

输出

io：

输入/出

存储过程根据设计需求决定参数的数目、类型和长度。

存储过程至少要有一个输入参数和一个输出参数，字符参数长度不超过80，类型为varchar2（80）。

一个输出参数返回存储过程的执行结果状态（成功、失败）。

3.3.3程序主体的注释

注释的原则是有助于对程序的阅读理解，注释不宜太多也不能太少，太少不利于代码理解，太多则会对阅读产生干扰，因此只在必要的地方才加注释，而且注释要准确、易懂、尽可能简洁，防止注释二义性。

注释应与其描述的代码相近，对代码的注释应放在其上方或右方（对单条语句的注释）相邻位置，不可放在下面。

避免在注释中使用缩写，特别是非常用缩写。

注释的编写应该是边写代码边注释，修改代码同时修改相应的注释，以保证注释与代码的一致性。

不再有用的注释要删除。

对于所有的变量、数据结构声明，如果其命名不是充分自注释的，在声明时都必须加以注释，说明其含义。

对重要代码段的功能、意图进行注释，提供有用的、额外的信息。

并在该代码段的结束处加一行注释表示该段代码结束。

维护代码时，必须更新相应的注释，删除不再有用的注释。

保持代码、注释的一致性，避免产生误解

3.3.4变量的命名

所有的变量采用格式为：

变量前缀_变量描述。

一般变量的前缀使用类型的缩写：

命名对象

规则

存储过程、包、方法

1业务相关以模块代码开头

2如果区分全量和增量，在最后加标识

3全局使用，以global开头

变量

以v开头，v_变量名

常量

以con开头，con_常量名

游标

以cur开头，cur_游标名

内存表

以m开头

临时表

以t开头

用户定义的类型

type_名称

规则：

代码中声明与表的字段相对应的变量时，应保证变量名和字段名相同，并且变量名有自读性。

说明：

这样要求的目的是增强可读性。

规则：

代码中声明与表的字段相对应的变量时，对类型的定义需要使用%TYPE方式。

说明：

这样表结构的变动不会影响存储过程，避免表结构变更后出现变量长度或类型不一致的问题。

3.3.5数据字段

脚本中不允许出现“*”的用法（SELECT*FROM），必须用实际的字段名代替。

在INSERT语句中，必须指定要插入的字段名。

3.3.6空格及换行

不允许把多个短语句写在一行中，即一行只写一条语句。

两个程序块在逻辑上相对独立，应用空行加以分隔，同时增加注释。

建议对超过120字符的语句要分行书写，长表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。

划分出的新行应适当地缩进，使排版整齐、语句可读。

是否分行应根据实际情况而定，原则是保证代码整齐、语句可读。

调用函数或过程时，如果参数列表超过120字符，应根据逻辑内容进行换行，或者每个参数占用一行。

双目运算符、操作符前后应以空格分隔，间隔符之后应以空格分隔。

当不同优先级的操作符混合使用时，建议使用括号进行隔离，使代码的优先级更加清晰，而且可以避免犯错。

当SQL语句中存在不同优先级的关系运算符时，进行隔离。

如果SQL语句的条件复杂，很容易出现问题时，建议增加括号可以提高代码的可阅读性。

3.3.7返回值

函数的返回值定义应遵从下列规范：

成功出口返回0，失败出口返回-1。

过程中定义的用于返回错误码的OUTPUT参数，其定义应遵从下列规范：

成功出口返回0，失败出口返回-1。

3.3.8包的使用

ORACLE的包类似C++中的Class，有包头和包体两部分组成；包能够把相关的功能封装性在一个包中，包里面里可以有函数和过程。

PACKAGE与PROCEDURE和FUNCTION的优点是封装性比较好；在开发过程中，建议把功能点紧密相关的PROCEDURE、FUNCTION封装在一个包里面。

在对包体进行编译时，调用该包的存储过程不会失效。

3.3.9事务控制

在事务处理过程中，存储过程中的所有处理逻辑，要么被成功执行，要么什么都没做。

在编写存储过程时，可以显式使用COMMIT和ROLLBACK进行事务的控制。

存储过程的出口一律在存储过程的结束部分，不允许在存储过程中间返回。

规则：

在任何出口之前，只要存在事务未结束，必须提交或者回滚，除非有特殊设计考虑。

说明：

存储过程在每个出口前，如果启动了事务就必须结束所有事务，以提交（COMMIT）或回滚（ROLLBACK）来结束事务，否则可能会导致表锁等严重问题；如果在存储过程中，没有启动事务，就不必执行COMMIT或ROLLBACK，否则多余的ROLLBACK或COMMIT操作将增加数据库的额外开销。

规则：

ORACLE的事务是串行的，嵌套调用中内层存储过程的提交会导致外层的存储过程事务被一并提交；诸如生成流水号这类存储过程应使用独立事务，否则在产生流水号时就将之前的业务逻辑操作提交，而后续的操作不能保证在一个事务中。

例如现有系统中一个工单循环派给多个部门时，循环体内产生流水号，如最后一个派单失败时回滚操作根本就不能回撤所有操作，导致事务一致性被破坏。

建议：

尽量分解大事务，事务的大小应视系统的性能和应用的具体情况而定，过多过小的事务造成重做日志同步的等待，比如要往一个表插入100万条记录，如果每条记录提交一次，则事务太小，可以做一个计数器，设置1万或事务5万条作一次提交。

3.3.10执行成功

存储过程必须返回成功标志，在存储过程返回参数中表达“执行成功”。

涉及到数据表更新的，必须使用commit，退出程序。

3.3.11异常处理

1）规则：

函数/过程中应该有异常处理的代码，除非需要将任何可能的异常都向上抛出。

说明：

不论代码逻辑是否简单，只要有可能会抛出异常，函数/过程块就应该包括异常处理代码。

2）规则：

如果需要的话，可以在异常处理部分将异常继续向上抛出给调用者。

说明：

如果有些异常是预料可能产生，而且脚本需要根据是否有异常来做不同的逻辑处理，这种情况可以在异常处理部分将该异常进行处理，但可能有些意料之外的异常，需要继续向上抛出，便于调用者了解脚本执行是否成功，以便于调用者作下一步的处理如写错误日志等操作。

3）规则：

如果需要自定义异常，必须在异常处理块中对其进行处理。

说明：

如果自定义了异常，却没有相应的代码进行处理，那么就应该去掉该异常的定义。

4）规则：

在对容错性要求比较高的情况下，对异常块中的代码还需要判断是否可能触发异常，必要的时候应使用嵌套的异常。

说明：

在异常处理部分中的写日志之前，如果有事务，一定要先ROLLBACK；异常块中写日志、向表中插入一条初始化记录等语句也可能失败，对这些代码必要时也应该增加异常保护，即使用嵌套的异常处理。

在每一个异常处理部分，都要定义WHENOTHER子程序，以便捕获所有没有显示处理或其他类型的异常。

所有程序中捕获到的错误均转换成对应的Errcode、Errmsg，记录在操作日志中，并通过输出参数返回给调用者。

所有存储过程（函数）的最后要写EXCEPTION来处理异常，并且必须在存储过程返回参数中表达“执行失败”，涉及到数据表更新的，必须使用ROLLBACK回滚事务,退出程序。

3.3.12排序操作

在存储过程中，大量的排序操作会影响系统性能，所以尽量减少ORDERBY和GROUPBY排序操作。

如果必须使用排序操作，则尽量将排序建立在有索引的字段上。

3.3.13操作日志

存储过程在处理（更新数据表）后，必须根据执行状态填写日志。

日志的信息记录级别包括INFO、WARN、ERROR，其定义标准如下：

类型

描述

说明

INFO

提示信息

供开发人员调试使用，由开发人员自行确定，主要是调试信息，程序运行中普通信息提示。

WARN

警告信息

可能导致严重错误的警告信息

ERROR

错误信息

导致系统运行错误的信息

所有表操作的错误处理部分均应记录日志信息

3.3.14错误处理

凡是涉及到表操作（INSERT、UPDATE、SELECT、DELETE）的SQL语句，都必须进行错误捕捉，不能将错误带到后面的语句。

执行SELECTINTO语句时，应严格区分NO_DATA_FOUND和TOO_MANY_ROWS的错误，并将相应错误信息记入操作日志。

在每一个异常处理部分，都要定义WHENOTHERS子程序，以便捕获所有没有显示处理或其他类型的异常

错误信息描述应准确，业务相关的错误应将输入数据拼接到错误信息中。

所有程序中捕获到的错误,均转换成对应的errCode,errmsg,通过输出参数返回给调用者,所有存储过程（函数）结束前应统一捕获系统异常

在每个存储过程（函数）的入口处统一先将返回错误代码（errCode）设置为42,功能处理成功结束后再将错误代码（errCode）设置为0（成功）,避免程序过程中因错误未能正确捕获,导致功能未能完成,而程序却成功返回的情况出现

所有的模块都有错误编码区间,原则上错误编码全局唯一

3.4其它规范

3.4.1游标

考虑到系统性能，原则上在存储过程中不允许使用游标，如果由于没有其它更加有效的技术能够实现业务逻辑，则实施方案经过系统架构师审核批准后可以使用游标技术。

原则上避免使用游标，尤其是动态游标。

如果必须使用游标，则推荐使用CURSORFORLOOP语句，可以隐式打开关闭游标；否则，显式打开游标，就必须在任何出口之前显式关闭游标。

3.4.2其它

避免写超长的PL/SQL代码，每个存储过程和函数的最多不能超过2000行。

不要在循环中定义变量。

如果变量是用于存储某个字段的值，请保持变量类型（包括精度）和字段类型的一致。

一次性清空表，使用Truncate替代Delete来删除记录，但Truncate数据不记录日志，无法进行回滚。

Distinct和Having子句都是耗时操作，应该尽可能少使用。

用直接的表关联来代替Exist。

用Exist或NotExists来代理In。

使用In进行子查询效率很差。

尽量分解大事务，事务的大小应视系统的性能和应用的具体情况而定。

对于在存储过程中处理复杂业务逻辑，在能够保证业务数据一致性的前提下，建议使用分多次提交业务数据（分多次COMMIT），以减少长事务对系统性能影响很大。

注意，过多过小的事务造成日志同步的等待。

不建议使用函数索引。

删除已定义但没有使用的变量。