PLSQL编码开发规范

PLSQL开发规范Andrew.zhao日期作者版本变更说明2013-6-7Andrew.zhaoV1.0原始版1.PL/SQL编码规范1.1.使用工具Toad或PL/SQLDeveloper1.2.使用基表(视图)别名在SQL语句中为表或视图指定别名。其命名规格以简短为原则，一般为基表或视图名称每一节单词的第一个字母，如mtl_system_items的别名一般指定为msi。1.3.例外处理尽可能多的使用EXCEPTION捕捉各种例外，并返回用户可读懂的信息，如无法确定是否有其它例外，可在每个PL/SQL程序块的例外中加上WHENOTHERS语句。例外信息统一风格，定义统一的例外处理Package统一返回在哪个Package、Procedure中发生的例外对日志中的一些调试信息，通过参数设定是否需要输出语法如下：EXCEPTIONWHENOTHERSTHENFND_FILE.PUT_LINE(FND_FILE.OUTPUT,SQLCODE);FND_FILE.PUT_LINE(FND_FILE.OUTPUT,SQLERRM);END;1.4.注释PL/SQL有两种表示注释的方法，分别为’--’和’/*…*/’,对于多行或整段的注释一般采用后者，对于单行或行尾的注释一般采用前者。以下从几个主要地方注释做个规定，力求统一。注释风格：注释单独成行、放在语句前面。应对不易理解的分支条件表达式加注释；对重要的计算应说明其功能；过长的函数实现，应将其语句按实现的功能分段加以概括性说明；常量及变量注释时，应注释被保存值的含义(必须)，合法取值的范围；可采用单行/多行注释。（--或/**/方式）。包、过程、函数注释(代码头信息注释规范)/*===============================================================*Copyright(C)CNCAllrightsreserved*===============================================================*ProgramName:[PackageName]*Author:$OSUSER*Date:$DATE$TIME*Purpose:[Purpose]*Parameters:*InX--参数X的简要说明*OutY--参数Y的简要说明*Called:*CalledBy:*Description:**UpdateHistory*VersionDateNameDescription*-----------------------------------------------------*V1.0$DATE$OSUSERCreation*===============================================================*/如增加包中的过程或函数，需在包的注释中的UpdateHistory中增加相应的信息。如仅是修改过程或函数，则在过程或函数的注释中的UpdateHistory中增加相应的信息。注释的基本原则过程中定义的变量需要注释其含义，过程或函数中关键性语句也要注释其作用，对于较复杂的程序，则对该段程序的流程进行描述，在程序中的关键位置应加入相应的注释，对于非首次作者修改原有的程序，要对所做的修改部分加以标示，并作相应的注释。注释的格式无特殊要求，一般单行以’--’注释，多行以/*…*/注释。增加程序selectcount(*)fromtable_listwherewhere_clause1andwhere_clause2--addedbyxxx2006-05-28andwhere_clause4--endaddedbyxxx2006-05-28andwhere_clause3蓝色部分为新增加的程序修改/删除程序selectcount(*)fromtable_listwherewhere_clause1andwhere_clause2--changedbyxxx2006-05-28--andwhere_clause4andwhere_clause5--endchangedbyxxx2006-05-28andwhere_clause3蓝色部分为修改的程序，修改where_clause4为where_clause51.5.代码语法规范语法规范主要包括变量规范、包规范、游标规范、事务处理规范等等1.5.1.变量规范1)变量名全部采用小写，局部变量名使用“v_”开头，输入参数以“i_开头，输出参数以“o_”开头，输入输出参数用io_开头。所有输入参数必须显示声明。2)游标的命名，游标统一用“c_”命名。3)使用命名的常量以避免硬编码，使用常量包,常量统一用cn_的前缀命名。4)当变量代表列时，使用%TYPE属性，当变量实际上表示数据库表的某列数据时，为避免数据库结构修改对变量的影响，应统一使用%TYPE属性对变量命名。5)使用%TYPE以标准化非数据库变量的声明1.5.2.包规范包内的存储过程及函数的命名必须遵循简写规范，常用的操作简称如下：增加add修改upd删除del查询qry检查chk1.5.3.游标规范1)外部查询的多行数据返回使用游标进行处理，通过传递游标变量的形式返回数据到外部接口,由外部程序自行FETCH数据。2)打开游标前，必须显式检查游标的%ISOPEN属性。3)使用FETCH语句后，要立即检查%NOTFOUND属性，以便正常终止游标FETCH循环。4)无论PL/SQL程序是正常终止还是出错退出，都要关闭所有已打开的游标。在出错退出时，应该在其异常处理部分管理所有游标，这可以释放一部分的系统资源。5)尽可能使用显示游标，避免使用隐式游标。1.5.4.数据封装和访问规范1)按照业务逻辑实现功能模块的封装，将业务规则逻辑集中在更少量的、良好设计的、易于维护的函数或者过程中，不必在每条SQL语句或者每块PL/SQL程序中重复这些逻辑。2)后台数据按照逻辑划分成多个SCHEMA，不同SCHEMA的数据不可互相访问。3)需要相互访问的表均存放在INTERFACE的SCHEMA中，通过访问INTERFACE中的接口表实现跨SCHEMA的数据访问。1.5.5.日志书写规范1)采用公共的API包完成后台日志数据记录。（API完成输出错误信息提示、记录错误信息内容到数据库表、系统级的错误代码及错误信息等）2)后台日志的信息记录级别包括DEBUG、INFO、WARN、ERROR、FATAL，其定义以及不同级别日志的采集标准如下：DEBUG－调试信息，供开发人员调试使用，由开发人员自行确定。INFO－提示信息，记录重要的系统事件或者业务事件，如开始结算等。WARN－警告信息，可能导致严重错误的警告信息ERROR－错误信息，导致系统运行错误的信息。FATAL－致命信息，导致系统无法继续运行的致命错误信息。3)所有表操作的错误处理部分均应记录日志信息。1.5.6.错误处理规范1)后台中所有出现错误或者异常的地方，都必须调用公共的日志模块记录日志信息。2)凡是涉及到表操作(insert,update,select,delete)的sql语句,都必须进行错误捕捉,不能将错误带到后面的语句。3)从表中select数据的语句,应严格区分NO_DATA_FOUND和TOO_MANY_ROWS的错误,并将相应错误信息填入错误信息。NO_DATA_FOUND多数为查询条件问题导致无数据返回（用户级错误）。TOO_MANY_ROWS应该是数据表内数据异常导致（系统级错误）。4)所有存储过程(函数)的统一出口一律在存储过程的结束部分,不允许中间返回。5)在每一个异常处理部分，都要定义WHENOTHER子程序，以便捕获所有没有显示处理或其它类型的异常。6)所有程序中捕获到的错误,均转换成对应的errcode,errmsg,通过输出参数返回给调用者。7)在每个存储过程(函数)的入口处统一先将返回错误代码(errCode)设置为-999,功能处理成功结束后再将错误代码(errCode)设置为0(成功),避免程序过程中因错误未能正确捕获,导致功能未能完成,而程序却成功返回的情况出现。8)所有的模块都有错误编码区间,原则上错误编码全局唯一。9)错误信息描述应准确，业务相关的错误应将输入数据拼接到错误信息中。如会员密码错误等等。10)错误代码根据存储过程（函数）的功能号来确定，具体规则为：错误代码＝功能号后7位＋XX，（XX为00－99）。即分配给每个功能点的错误代码为100个，由开发者自己划分错误代码，自行描述错误信息，不做具体规定。1.5.7.书写规范1)PL/SQL语句的所有表名、字段名遵照数据字典的定义，系统保留字、内置函数名、PL/SQL保留字、关键字大写，用户声明的标识符小写。2)对于子程序、触发器、包等带名的程序块，使用结束标识。3)连接符OR、IN、AND、以及＝、=、=等前后加上一个空格。4)对较为复杂的SQL语句加上注释，说明算法、功能。5)SQL语句的缩进风格：一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进；WHERE子句书写时，每个条件占一行，语句另起一行时，以保留字或者连接符开始，连接符右对齐。例如：wheref1=1andf2=2orf3=36)INSERT语句，必须书写字段，字段可5个或6个一组。中间用TAB分开。7)多表连接时，使用表的别名来引用列。8)供别的文件或函数调用的函数，绝不应使用全局变量交换数据。9)TAB统一定义为4个空格。10)书写优化性能建议：避免嵌套连接。例如：A=BANDB=CANDC=DWHERE条件中尽量减少使用常量比较，改用主机变量系统可能选择基于规则的优化器，所以将结果集返回数据量小的表作为驱动表（FROM后边最后一个表）。大量的排序操作影响系统性能，所以尽量减少ORDERBY和GROUPBY排序操作。如必须使用排序操作，请遵循如下规则排序尽量建立在有索引的列上。如结果集不需唯一，使用UNIONALL代替UNION。索引的使用尽量避免对索引列进行计算。尽量注意比较值与索引列数据类型的一致性。对于复合索引，SQL语句必须使用主索引列索引中，尽量避免使用NULL。对于索引的比较，尽量避免使用!=查询列和排序列与索引列次序保持一致。尽量避免相同语句由于书写格式的不同，而导致多次语法分析。尽量使用共享的SQL语句。查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面。任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。IN、OR子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。11)其它经验性规则：尽量少用嵌套查询。如必须，请用notexist代替notin子句。错误SELECT......FROMempWHEREdept_noNOTIN(SELECTdept_noFROMdeptWHEREdept_cat='A');正确SELECT......FROMempeWHERENOTEXISTS(SELECT'X'FROMdeptWHEREdept_no=e.dept_noANDdept_cat='A');用多表连接代替EXISTS子句。错误SELECT......FROMempWHEREEXISTS(SELECT'X'FROMdeptWHEREdept_no=e.dept_noANDdept_cat='A');正确SELECT......FROMempe,deptdWHEREe.dept_no=d.dept_noANDdept_cat='A';少用DISTINCT，用EXISTS代替如例（4）错SELECTDISTINCTd.dept_code,d.dept_nameFROMdeptd,empeWHEREe.dept_code=