存储过程编写规范V1.doc

版本：X.X修改日期：XXXX-XX-XX存储过程编写规范XX有限公司14文档控制文档名称文档代码文档描述版本信息版本号修订日修订者审核者描述V0.12014-10-01V1.02014-12-16备注：1、 文档代码是标识文档类型的分类代码，主要用于文档的分类，查询和归档。其编码规则请参阅相关文档。2、 文档描述主要用于文档的快速的分类和查询，使用关键字的方式体现文档的主要内容，需要体现主要功能，系统所属的业务部门，使用部门等关键信息。3、 版本信息中项目过程文档1.0版本以前不需要每次审核发布，可以统一批次审批发布。4、 版本信息中项目文档1.0版本正式发布后，每个版本发布需要审核者签字确认。目 录1.概述41.1目的41.2适用范围41.3术语解释42.存储过程使用原则53.开发规范63.1存储体结构63.2书写规范63.2.1程序头书写规范63.2.2代码书写规范73.2.3注释书写规范83.3命名规范93.3.1存储过程名称93.3.2存储过程的输入输出参数93.3.3程序主体的注释93.3.4变量的命名93.3.1数据字段103.3.2空格及换行103.3.3返回值113.3.4包的使用113.3.5事务控制113.3.6执行成功113.3.7异常处理123.3.8排序操作123.3.9操作日志133.3.10错误处理133.4其它规范143.4.1游标143.4.2其它141. 概述1.1 目的本规范用于指导项目编写Oracle数据库存储过程（Stored Procedure）代码，以保证需求按时、安质实现。1.2 适用范围本过程定义文档适用于上海通用汽车有限公司所有软件开发项目编码集成活动。1.3 术语解释2. 存储过程使用原则应用系统的业务逻辑实现，必须严格按照系统架构师的设计方案进行。应用系统的设计、开发必须保持公司应用系统架构的一致性、系统的可移植性、系统的可维护性、系统版本的可控制性。原则上，自开发系统不建议使用存储过程。如果有特殊情况需要使用存储过程，需要在架构师团队中立案说明 ，得到讨论认可后，方可进行存储过程的设计开发。并且使用存储过程，须符合以下场景。l 业务逻辑的处理涉及到多个数据表及大量数据记录，并且逻辑处理结果仅是一个比较小的结果集（一个结果、几条数据记录）。l 业务逻辑处理在系统逻辑层实现比较困难，而采用SQL语句实现其功能时，需要大量SQL语句，并且涉及到多个数据表的数据更新的一致性问题，则可使用存储过程来实现。l 应用系统的功能实现对系统性能有较大影响，并且经过证明，在存储过程中实现业务逻辑比在应用系统的业务逻辑层实现性能得到较大的提升。l 应用系统的使用场景对网络带宽、流量有明显的限制。3. 开发规范3.1 存储体结构-CREATE OR REPLACE PROCEDURE example（parameters） -过程声明区IS-/* 描述存储过程名称，编写人，编写日期，修改人，修改日期，版本号等信息 */-/* 声明部分 */-BEGIN/* 代码部分 */EXCEPTION/* 异常处理部分 */END;-3.2 书写规范3.2.1 程序头书写规范程序头开始部分应说明程序整体的功能，存储过程名称，编写人，编写日期，修改人，修改日期，版本号以及过程涉及的表和视图。示例如下：-/*存储过程名称：存储过程功能说明: 版本号: ( 版本号标示：新建 V1.0.0 小的修改变为V1.0.1 大的修改V1.1.0 重构V2.0.0)创建日期：YYYY-MM-DD 创建人员：*修改日期：YYYY-MM-DD 修改人员：*修改原因：修改内容：修改日期：YYYY-MM-DD 修改人员：*修改原因：修改内容：涉及的表或视图：表或视图名称 用途XXXX1 YYYY1XXXX2 YYYY2存储过程调用：存储过程名称 用途XXXX1 YYYY1（描述此存储过程调用存储过程列表，如果没有调用任何存储过程，则填写“NONE”， 不允许为空白。）*/-传入参数： X1 数据类型： 描述： X2 数据类型： 描述：传出参数： X3 数据类型： 描述：返回值： X4 数据类型： 描述：-3.2.2 代码书写规范l 语句中出现的所有表名、字段名全部小写，系统保留字、内置函数名、PL/SQL保留字、关键字大写，用户声明的标识符小写。l 连接符OR、IN、AND、以及、=等前后加上一个空格。l WHERE子句书写时，每个条件占一行，语句令起一行时，以保留字或者连接符开始，连接符右对齐。 l 查询的WHERE过滤，原则应使过滤记录数最多的条件放在最前面。 l 多表连接时，使用表的别名来引用列。l 查找数据库表或视图时，只能取出确实需要的那些字段，不允许使用*来代替所有列名。l 功能相似的过程和函数，尽量写到同一个包中，加强管理。l SQL语句采用缩进风格书写，保证代码清晰易读，风格一致。缩进格数统一为2个空格，但需要关键字右对齐或者参数/字段向上对齐的时候例外。一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进 。WHERE子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐。例如：Where f1 = 1and f2 = 2and f3 = 3l 不允许把多个短语句写在一行中，即一行只写一条语句。l 相对独立的程序块之间、变量说明之后须加空行。l SQL语句中存在不同优先级的关系运算符时，使用括号进行隔离。l INSERT语句，必须书写字段，字段可5个或6个一组。 l 供别的文件或函数调用的函数，不允许使用全局变量交换数据； 3.2.3 注释书写规范为了提高可读性，应该使用一定数量的注释。注释大约占总行数的10%。l 注释风格：注释单独成行、放在语句前面。 l 分支条件表达式必须加注释；l 对重要的计算应加注释说明其功能；l 过长的函数实现，应将其语句按实现的功能分段加以概括性说明；l 每条SQL语句均应加注释说明l 对于程序的整体功能，应在程序开始部分说明，可采用单行/多行注释。（- 或 /* */ 方式）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.1 数据字段脚本中不允许出现“*”的用法（SELECT * FROM），必须用实际的字段名代替。在INSERT 语句中，必须指定要插入的字段名。3.3.2 空格及换行不允许把多个短语句写在一行中，即一行只写一条语句。两个程序块在逻辑上相对独立，应用空行加以分隔，同时增加注释。建议对超过 120字符的语句要分行书写，长表达式应在低优先级操作符处换行，操作符或关键字放在新行之首。划分出的新行应适当地缩进，使排版整齐、语句可读。是否分行应根据实际情况而定，原则是保证代码整齐、语句可读。调用函数或过程时，如果参数列表超过 120 字符，应根据逻辑内容进行换行，或者每个参数占用一行。双目运算符、操作符前后应以空格分隔，间隔符之后应以空格分隔。当不同优先级的操作符混合使用时，建议使用括号进行隔离，使代码的优先级更加清晰，而且可以避免犯错。当SQL语句中存在不同优先级的关系运算符时，进行隔离。如果SQL 语句的条件复杂，很容易出现问题时，建议增加括号可以提高代码的可阅读性。3.3.3 返回值函数的返回值定义应遵从下列规范：成功出口返回0，失败出口返回-1。过程中定义的用于返回错误码的OUTPUT 参数，其定义应遵从下列规范：成功出口返回0，失败出口返回-1。3.3.4 包的使用ORACLE 的包类似C+中的 Class，有包头和包体两部分组成；包能够把相关的功能封装性在一个包中，包里面里可以有函数和过程。PACKAGE 与PROCEDURE 和FUNCTION 的优点是封装性比较好； 在开发过程中， 建议把功能点紧密相关的PROCEDURE、FUNCTION 封装在一个包里面。在对包体进行编译时，调用该包的存储过程不会失效。3.3.5 事务控制 在事务处理过程中，存储过程中的所有处理逻辑，要么被成功执行，要么什么都没做。在编写存储过程时，可以显式使用COMMIT和ROLLBACK进行事务的控制。 存储过程的出口一律在存储过程的结束部分，不允许在存储过程中间返回。规则：在任何出口之前，只要存在事务未结束，必须提交或者回滚，除非有特殊设计考虑。说明：存储过程在每个出口前，如果启动了事务就必须结束所有事务，以提交（COMMIT）或回滚（ROLLBACK）来结束事务，否则可能会导致表锁等严重问题；如果在存储过程中，没有启动事务，就不必执行COMMIT 或ROLLBACK，否则多余的ROLLBACK 或COMMIT操作将增加数据库的额外开销。规则：ORACLE 的事务是串行的，嵌套调用中内层存储过程的提交会导致外层的存储过程事务被一并提交；诸如生成流水号这类存储过程应使用独立事务，否则在产生流水号时就将之前的业务逻辑操作提交，而后续的操作不能保证在一个事务中。例如现有系统中一个工单循环派给多个部门时，循环体内产生流水号，如最后一个派单失败时回滚操作根本就不能回撤所有操作，导致事务一致性被破坏。建议：尽量分解大事务，事务的大小应视系统的性能和应用的具体情况而定，过多过小的事务造成重做日志同步的等待，比如要往一个表插入100 万条记录，如果每条记录提交一次，则事务太小，可以做一个计数器，设置1 万或事务5 万条作一次提交。3.3.6 执行成功 存储过程必须返回成功标志，在存储过程返回参数中表达 “执行成功”。涉及到数据表更新的，必须使用commit，退出程序。3.3.7 异常处理 1) 规则：函数/过程中应该有异常处理的代码，除非需要将任何可能的异常都向上抛出。说明：不论代码逻辑是否简单，只要有可能会抛出异常，函数/过程块就应该包括异常处理代码。2) 规则：如果需要的话，可以在异常处理部分将异常继续向上抛出给调用者。说明：如果有些异常是预料可能产生，而且脚本需要根据是否有异常来做不同的逻辑处理，这种情况可以在异常处理部分将该异常进行处理，但可能有些意料之外的异常，需要继续向上抛出，便于调用者了解脚本执行是否成功，以便于调用者作下一步的处理如写错误日志等操作。3) 规则：如果需要自定义异常，必须在异常处理块中对其进行处理。说明：如果自定义了异常，却没有相应的代码进行处理，那么就应该去掉该异常的定义。4) 规则：在对容错性要求比较高的情况下，对异常块中的代码还需要判断是否可能触发异常，必要的时候应使用嵌套的异常。说明：在异常处理部分中的写日志之前，如果有事务，一定要先ROLLBACK；异常块中写日志、向表中插入一条初始化记录等语句也可能失败，对这些代码必要时也应该增加异常保护，即使用嵌套的异常处理。在每一个异常处理部分，都要定义WHEN OTHER 子程序，以便捕获所有没有显示处理或其他类型的异常。所有程序中捕获到的错误均转换成对应的Errcode、Errmsg，记录在操作日志中，并通过输出参数返回给调用者。所有存储过程（函数）的最后要写EXCEPTION来处理异常，并且必须在存储过程返回参数中表达 “执行失败” ，涉及到数据表更新的，必须使用ROLLBACK回滚事务, 退出程序。 3.3.8 排序操作在存储过程中，大量的排序操作会影响系统性能，所以尽量减少ORDER BY和GROUP BY排序操作。如果必须使用排序操作，则尽量将排序建立在有索引的字段上。3.3.9 操作日志存储过程在处理（更新数据表）后，必须根据执行状态填写日志。日志的信息记录级别包括INFO、WARN、ERROR，其定义标准如下：类型描述说明INFO提示信息供开发人员调试使用，由开发人员自行确定，主要是调试信息，程序运行中普通信息提示。WARN警告信息可能导致严重错误的警告信息ERROR错误信息导致系统运行错误的信息所有表操作的错误处理部分均应记录日志信息3.3.10 错误处理凡是涉及到表操作(INSERT、UPDATE、SELECT、DELETE)的SQL语句，都必须进行错误捕捉，不能将错误带到后面的语句。执行SELECT INTO语句时，应严格区分NO_DATA_FOUND和 TOO_MANY_ROWS的错误，并将相应错误信息记入操作日志。在每一个异常处理部分，都要定义WHEN OTHERS 子程序，以便捕获所有没有显示处理或其他类型的异常错误信息描述应准确，业务相关的错误应将输入数据拼接到错误信息中。所有程序中捕获到的错误,均转换成对应的errCode,errmsg,通过输出参数返回给调用者,所有存储过程（函数）结束前应统一捕获系统异常在每个存储过程(函数)的入口处统一先将返回错误代码(errCode)设置为4