Oracle编程规范及过程说明.doc

Oracle编程规范及过程说明文件编号：SIDW022-02 生效日期：2002/4/18版次：Ver 1.0编制：审核：批准：东软软件股份有限公司 社保事业部（版权所有）目录1.引言52.命名规范52.1基本命名规则52.2定制对象命名规则62.3变量命名规则63.程序书写规范83.1大小写风格：83.2注释风格：83.2.1源代码文件的注释83.2.2包、过程、函数的注释83.2.3语句的注释93.2.4常量和变量的注释103.2.5注释的书写规范103.3缩进规则：103.3.1SQL语句的缩进风格103.3.2控制结构的缩进113.3.3缩进的限制113.4其他113.5例子113.5.1例一.114.SQL优化规则144.1索引的使用原则154.1.1尽量避免对索引列进行计算。154.1.2尽量注意比较值与索引列数据类型的一致性。154.1.3尽量避免使用NULL154.1.4尽量避免使用NOT=（!=）154.1.5对于复合索引，SQL语句必须使用主索引列164.1.6ORDER BY子句164.1.7查询列与索引列次序（WHERE）的一致性164.2查询的WHERE过滤原则164.3尽量使用共享的SQL语句。164.4使用优化线索机制进行访问路径控制。174.5显示光标优于隐式光标174.6安全与优化设计184.73其他184.7.1语句书写要规范184.7.2尽量少用嵌套查询。184.7.3使用表的别名184.7.4用NOT EXISTS代替NOT IN184.7.5用多表连接代替EXISTS子句194.7.6少用DISTINCT，用EXISTS代替194.7.7使用UNION ALL、MINUS、INTERSECT提高性能204.7.8使用ROWID提高检索速度204.7.9查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面。204.7.10尽量使用共享的SQL语句。204.7.11使用优化线索机制进行访问路径控制。214.7.12显示光标优于隐式光标215.* SQL常用函数：235.1数值函数：235.1.1* ABS235.1.2* CEIL235.1.3* MOD235.1.4* ROUND235.1.5* TRUNC245.2字符函数：245.2.1* CONCAT245.2.2* LOWER255.2.3* LPAD255.2.4* LTRIM255.2.5* REPLACE265.2.6* RPAD265.2.7* RTRIM275.2.8* SUBSTR275.2.9* TRANSLATE285.2.10* UPPER295.2.11* ASCII295.2.12* INSTR305.2.13* LENGTH305.3日期函数315.3.1* ADD_MONTHS315.3.2* LAST_DAY315.3.3* MONTHS_BETWEEN325.3.4* NEXT_DAY335.3.5* SYSDATE345.4转换函数：345.4.1* CHARTOROWID345.4.2* TO_CHAR, date conversion345.4.3* TO_CHAR, number conversion355.4.4* TO_DATE355.4.5* TO_NUMBER365.4.6* NVL365.4.7* UID375.4.8* USER375.5组函数：385.5.1* AVG385.5.2* COUNT395.5.3* MAX405.5.4* MIN405.5.5* SUM415.6数据库系统函数415.6.1* DBMS_ALERT415.6.2* DBMS_OUTPUT415.6.3* DBMS_PIPE425.6.4* DBMS_SQL425.6.5* DBMS_STANDARD425.6.6* FUNCTION（主要用于触发器）425.6.7* RAISE_APPLICATION_ERROR425.6.8* UTL_FILE435.6.9* SQLCODE、SQLERRM435.6.10* SAVEPOINT、ROLLBACK445.6.11* EXCEPTION_INIT455.6.12* DBMS_JOB455.6.13* SUBMIT（） 提交作业455.6.14* RUN465.6.15* REMOVE465.6.16* CHANGE465.6.17* DBMS_UTILITY465.6.18* COMPILE_SCHEMA（用户名）465.6.19* DBMS_TRANSACTION465.6.20* READ_ONLY()465.6.21* READ_WRITE()465.6.22* USE_ROLLBACK_SEGMENT( rb_name varchar2)465.6.23* SAVEPOINT(savept varchar2)465.6.24* rollback_savepoint(svpt varchar2)461. 引言本规范用于说明在利用PL/SQL开发Oracle数据库后台应用程序时，应遵守的某些原则。本规范的制定主要出于以下几方面的考虑： 效率 可读性 可维护性 规范性规范中未做声明的内容，以满足开发总则为准。2. 命名规范2.1 基本命名规则本规范中采用的命名规则，基本模式为：前缀_描述其中前缀根据定制对象，变量类型的不同而变化。描述的命名规则为：信息结构通则命名规则与字母标识符命名两者相结合（1）与表中字段相对应的变量命名采取代码方式，以与字段命名相对应（2）其余用有意义的字母标识符（如：拼音各个字汉语拼音首字母联结成的字符串，对只有两个字符的，采用名称全拼）2.2 定制对象命名规则如表2-1所示前缀定制对象举例说明pkg程序包个人帐户 pkg_grzhprc过程生成个人帐户 prc_scgrzhfun函数确定缴费比例 fun_qdjfblind索引ind_grzhtri触发器tri_grcbzlvi视图vi_seq序列发生器seq_pk主键fk外键syn同义词dlkDB LINKsnp快照表2-1 定制对象命名规则2.3 变量命名规则变量命名规则中前缀由两部分组成：变量类型前缀+数据类型前缀变量类型前缀规则如下表2-2：前 缀变量类型g全局变量def常量表2-2变量类型命名规则数据类型前缀命名规则如下表2-3：前 缀数据类型binBinary_IntegerbBooleancChardDatelLonglobLOBnNumberdecDecimaldblDoubleiIntegerfFloatrRealplsPls_IntegerRec%ROWTYPErawRAWrowROWIDstrVarchar2curCURSORrecRecordtabTablerefcurREF CURSORu用户自定义数据类型typTYPE自定义类型类型e异常（EXCEPTION）表2-3数据类型前缀命名规则例：声明一全局类数值型：gn_grsxh 声明一局部类：str_name说明：对于未在上述数据类型中列出的其它数据类型（包括系统提供的数据类型），其数据类型前缀的命名必须不与表3-4中的前缀重名，它们的前缀命名规则是未规定的。3. 程序书写规范3.1 大小写风格：类型约定举例保留字大写BEGIN、DECLARE、ELSIF内置函数大写SUBSTR、COUNT、TO_NUMBER预定义类型大写NUMBER(7,2)、BOOLEANSQL关键字大写SELECT、INTO、WHERE数据库对象小写abc007、ac021变量名小写gn_dwhrbl表3-1 大小写书写规范3.2 注释风格：注释总是加在程序的需要一个概括性说明或不易理解或易理解错的地方。注释应语言简炼、易懂而又准确。3.2.1 源代码文件的注释（1） 在文件的头部必须标明程序名称，它所完成的主要功能。（2） 文件的作者，及完成时间。（3） 文件的状态：测试/未测试。（4） 主要修改活动的修改人、时间、简单原因说明列表、版本号。（5） 维护过程中需要修改程序时，应在被修改语句前面注明修改时间和原因说明。例：REM 文件名：REM 功能描述：REM 状态：REM 作者：REM 完成时间：REM 修改：时间 版本号 修改人 修改原因3.2.2 包、过程、函数的注释(1) 头部必须进行功能和参数说明；(2) 主体部分，如算法复杂时，应以注释的方式对其算法结构作出说明；3.2.3 语句的注释注释单独成行、放在语句前面。(1) 应对不易理解的分支条件表达式加注释；(2) 不易理解的循环，应说明出口条件(有GOTO的程序还应说明入口条件)；(3) 对重要的计算应说明其功能；(4) 过长的函数实现，应将其语句按实现的功能分段加以概括性说明；(5) 供别的文件或函数调用的函数，绝不应使用全局变量交换数据；(6) 每条SQL语句均应有注释说明（表名、字段名）。例：SELECT abc007,abc008,abc009, -上年计入金额，本年计入金额，本年缴费月数 abc010,abc011, -年度、本年缴费基数、 abc012,abc013 -本年帐户支付累计金额、本年统筹支付金额 INTO ln_abc007,ln_abc008,ln_abc009, ln_abc010,ln_abc011, ln_abc012,ln_abc013 FROM ab003 -个人帐户 WHERE abc001 = ivc_shbzh - AND abd004 = .; -UPDATE ab003 - SET abc004 = ln_abc004, - abc005 = ln_abc005, - . WHERE abc001 = ivc_shbzh - AND . INSERT INTO table_name - ( col1,col2,col3, - col4,col5,.) -VALUES ( v1,v2,v3, v4,v5,.)3.2.4 常量和变量的注释注释说明放在常量和变量定义语句的后面，注释说明的要点是：(1) 被保存值的含义(必须)(2) 合法取值的范围(可选)(3) 全局量需要对以上逐点做充分的说明。3.2.5 注释的书写规范可采用单行/多行注释。（- 或 /* */ 方式）3.3 缩进规则：3.3.1 SQL语句的缩进风格（1）查询列表的书写风格(与注释综合考虑)一行有多列，超过80个字符时，基于列对齐原则，采用下行缩进SELECT col1,col2,. colm,coln,. INTO v_col1,v_col2,. v_colm,.（2）WHERE 子句的书写规范 每个条件占一行 嵌套查询条件书写规范 WHERE con1 AND con2 AND col3 NOT IN ( SELECT col3 FROM t2 WHERE .);（3）SET 子句的书写规范每个表达式占一行。SET col1 = v1, col2 = v2, .3.3.2 控制结构的缩进程序应以缩进形式展现程序的块结构和控制结构。下列保留字的下一行缩进三格BEGIN、THEN、ELSE、ELSIF、LOOP 下列保留字所在行前移三格 END、ELSE、ELSIF、END IF、END LOOP3.3.3 缩进的限制（1）每次缩进标准为 3 个空格，不准使用 TAB 键。（2）任何一个程序最大行宽不得超过80列，第一行续行语句缩进三格，后续续行语句与第一行续行语句对齐。长语句的下一语句以长语句为对齐基准参照上面规定执行。3.4 其他（1） 后台过程禁止使用COMMIT。在出错处理中可用ROLLBACK。（2） 过程的输入/输出参数声明必须指定为IN或OUT，要与参数命名一致。特别，不能指定为IN OUT。3.5 例子3.5.1 例一.REM 文件名：jlgrzh.sqlREM 功能描述：该模块主要用于人员新参保时帐户的处理REM 状态：未测试REM 作者：丁蓉REM 完成时间：1999/11/3REM 修改：1999/11/5，版本号，王建，(所修改信息描述)CREATE OR REPLACE PACKAGE pkg_jlgrzh AS -对于新增，统筹范围外转入人员，新建个人帐户信息 PROCEDURE pro_xjgrzh ( ic_shbzh IN CHAR， -个人社会保障号 in_dwsxh IN NUMBER, -单位顺序号id_cbrq IN DATE, -参保日期 on_fhz OUT NUMBER -返回值 ); -对统筹范围外转入的参保人员，如实记载职工个人帐户。 PROCEDURE pro_sjgrzh ( ic_shbzh IN CHAR, -个人社会保障号 in_snjrje IN NUMBER, -上年计入金额on_fhz OUT NUMBER -返回值 ); -功能说明 FUNCTION fun_函数名 (参数1 IN 类型, -参数说明参数2 IN 类型,) -参数说明RETURN 类型;END pkg_jlgrzh;CREATE OR REPLACE PACKAGE BODY pkg_jlgrzh AS -功能说明 PROCEDURE 过程名 (参数1 IN 类型, -参数说明参数2 IN 类型, -参数说明参数1 OUT 类型 -参数说明 ) IS 变量名 数据类型; -变量说明 BEGIN -语句说明 语句; 长语句第一行 续行1 续行2; 语句; IF 判断条件组合 THEN -分支处理说明 语句; ELSIF THEN 语句; ELSE 语句; END IF; FOR LOOP 循环体; END LOOP; EXCEPTION WHEN THEN 例外处理语句; END 过程名; -对于新增，统筹范围外转入人员，新建个人帐户信息 PROCEDURE pro_xjgrzh ( ic_shbzh IN CHAR, -个人社会保障号 in_dwsxh IN NUMBER, -单位顺序号 id_cbrq IN DATE, -参保日期 on_fhz OUT NUMBER -返回值 ) IS ln_bz NUMBER; -标志 ln_abc007 NUMBER; -上年计入金额 ln_abc008 NUMBER; -本年计入金额 ln_abc009 NUMBER; -本年缴费月数 BEGIN SELECT abc007,abc008, -上年计入金额,本年计入金额 abc009 -本年缴费月数 INTO ln_abc007,ln_abc008, ln_abc009 FROM ab003 -个人帐户 WHERE abc001 = ic_shbzh; -生成个人帐户记录 INSERT INTO ab004 ( abc007,abc008, - abc009,.) - VALUES ( ln_abc007,ln_abc008, - ln_abc009,.); - . EXCEPTION WHEN OTHERS THEN on_fhz := -10110.9; END pro_xjgrzh; .END pkg_jlgrzh;4. SQL优化规则4.1 索引的使用原则4.1.1 尽量避免对索引列进行计算。例：X WHERE sal*1.1950O WHERE sal950/1.1X WHERE SUBSTR(name,1,7)=CAPITALO WHERE name LIKE CAPITAL%4.1.2 尽量注意比较值与索引列数据类型的一致性。例：emp_no： NUMBER型O WHERE emp_no=123（好） WHERE emp_no=123（也可）emp_type：CHAR型X WHERE emp_type=123 （此时，查询时，不利用索引列）O WHERE emp_type=1234.1.3 尽量避免使用NULL例：X WHERE comm IS NOT NULLX WHERE comm IS NULLO WHERE comm=04.1.4 尽量避免使用NOT=（!=）例：X WHERE deptno!=0O WHERE deptno04.1.5 对于复合索引，SQL语句必须使用主索引列例：复合索引(deptno,job)O WHERE deptno=20 AND job=MANAGERO WHERE deptno=20O WHERE job=MANAGER AND deptno=20X WHERE job=MANAGER4.1.6 ORDER BY子句O 子句中，列的顺序与索引列的顺序一致。O 子句中，列应为非空列。4.1.7 查询列与索引列次序（WHERE）的一致性O SELECT empno,job FROM emp WHERE empno100 AND job=MANAGER;4.2 查询的WHERE过滤原则应使过滤记录数最多的条件放在最前面。例：SELECT infoFROM taba a,tabb b,tabc cWHERE a.acol between :alow and :ahighAND b.bcol between :blow and :bhighAND c.ccol between :clow and :chighAND a.key1 = b.key1AND a.key2 = c.key2;其中，A表的acol列可以最多减少查询的记录数目，其次为B表的bcol列，依次类推。4.3 尽量使用共享的SQL语句。如经常使用select * from dept where deptno=值如果每一个值都是常量，则每一次都会重新解释，不能共享内存中的SQL语句优化结果。应把值设置为一个变量，所有的共同语句都可以优化一次，高度共享语句解释优化的结果。例：select * from dept where deptno=:d;4.4 使用优化线索机制进行访问路径控制。Select e.enameFrom emp eWhere e.job|=CLERK;不如下面的语句好：SELECT /*+FULL(EMP)*/ E.ENAMEFrom emp eWhere e.job=CLERK;4.5 显示光标优于隐式光标如：update targetset t_field = (select s_information from source where source.key = target.key)where exists (select from source where source.key = target.key)不如下面的显示光标语句好：declare cursor src is select * from source;begin for row in src loop update target set t_field = row.s_information where key = row.key; end loop;end;4.6 安全与优化设计 对于性能的提高，主要采用表分区(Partition)、物化视图等进行，而可以采用透明二阶段提交的分布式和数据库复制技术，实现数据的复制功能。 对于系统的设计，如安全性和便于分布是要优先设计的。系统的灵活性主要体现在系统的设计思想上，也就是，应用程序可以对存储在数据库中的一些运行参数进行设置，就可以基本上满足一些业务需求的变化，而不必每一次都要改动应用程序。例如，银行利息的变化、是否允许跨区缴费等，都可以通过应用系统存储在数据库系统的开关变量来完成。4.7 3其他4.7.1 语句书写要规范尽量避免相同语句由于书写格式的不同，而导致多次语法分析。4.7.2 尽量少用嵌套查询。4.7.3 使用表的别名多表连接时，使用表的别名来引用列。例：X SELECT abc002,abd003 FROM ab001 ,ab020 WHERE ab001.col2=ab020.col3.O SELECT t1.abc002,t2.abd003 FROM ab001 t1,ab020 t2 WHERE t1.col2=t2.col3.4.7.4 用NOT EXISTS代替NOT IN例：X SELECT . FROM emp WHERE dept_no NOT IN ( SELECT dept_no FROM dept WHERE dept_cat=A);O SELECT . FROM emp e WHERE NOT EXISTS ( SELECT X FROM dept WHERE dept_no=e.dept_no AND dept_cat=A);4.7.5 用多表连接代替EXISTS子句例：X SELECT . FROM emp WHERE EXISTS ( SELECT X FROM dept WHERE dept_no=e.dept_no AND dept_cat=A);O SELECT . FROM emp e,dept dWHERE e.dept_no=d.dept_no AND dept_cat=A;4.7.6 少用DISTINCT，用EXISTS代替X SELECT DISTINCT d.dept_code,d.dept_name FROM dept d ,emp eWHERE e.dept_code=d.dept_code;O SELECT dept_code,dept_name FROM dept dWHERE EXISTS ( SELECT X FROM emp e WHERE e.dept_code=d.dept_code);4.7.7 使用UNION ALL、MINUS、INTERSECT提高性能4.7.8 使用ROWID提高检索速度对SELECT得到的单行记录，需进行DELETE、UPDATE操作时，使用ROWID将会使效率大大提高。例：SELECT rowid INTO v_rowid FROM t1 WHERE con1 FOR UPDATE OF col2; UPDATE t1 SET col2=. WHERE rowid=v_rowid;4.7.9 查询的WHERE过滤原则，应使过滤记录数最多的条件放在最前面。例：SELECT infoFROM taba a,tabb b,tabc cWHERE a.acol between :alow and :ahighAND b.bcol between :blow and :bhighAND c.ccol between :clow and :chighAND a.key1 = b.key1AND a.key2 = c.key2;其中，A表的acol列可以最多减少查询的记录数目，其次为B表的bcol列，依次类推。4.7.10 尽量使用共享的SQL语句。如经常使用select * from dept where deptno=值如果每一个值都是常量，则每一次都会重新解释，不能共享内存中的SQL语句优化结果。应把值设置为一个变量，所有的共同语句都可以优化一次，高度共享语句解释优化的结果。例：select * from dept where deptno=:d;4.7.11 使用优化线索机制进行访问路径控制。Select e.enameFrom emp eWhere e.job|=CLERK;不如下面的语句好：SELECT /*+FULL(EMP)*/ E.ENAMEFrom emp eWhere e.job=CLERK;4.7.12 显示光标优于隐式光标如：update targetset t_field = (select s_information from source where source.key = target.key)where exists (select from source where source.key = target.key)不如下面的显示光标语句好：declare cursor src is select * from source;begin for row in src loop update target set t_field = row.s_information where key = row.key; end loop;end;5. * SQL常用函数：Oracle常用函数/过程说明，主要介绍Oracle的系统函数、过程和包。数值函数：* ABSPurpose 返回绝对值 Returns the absolute value of n. Example SELECT ABS(-15) Absolute FROM DUAL; Absolute- 15* CEILPurpose 取最小整数 Returns smallest integer greater than or equal to n. Example SELECT CEIL(15.7) Ceiling FROM DUAL; Ceiling- 165.1.1 * MODSyntax MOD(m,n)Purpose 取余 Returns remainder of m divided by n. Returns m if n is 0. Example SELECT MOD(11,4) Modulus FROM DUAL; Modulus- 35.1.2 * ROUNDSyntax ROUND(n,m)Purpose 取四舍五入信息 Returns n rounded to m places right of the decimal point; if m is omitted, to 0 places. m can be negative to round off digits left of the decimal point. m must be an integer. Example 1 SELECT ROUND(15.193,1) Round FROM DUAL; Round- 15.2Example 2 SELECT ROUND(15.193,-1) Round FROM DUAL; Round- 20 5.1.3 * TRUNCPurpose 取截取后的信息 Returns n truncated to m decimal places; if m is omitted, to 0 places. m can be negative to truncate (make zero) m digits left of the decimal point. Examples SELECT TRUNC(15.79,1) Truncate FROM DUAL; Truncate- 15.7SELECT TRUNC(15.79,-1) Truncate FROM DUAL; Truncate- 105.2 字符函数：5.2.1 * CONCATSyntax CONCAT(char1, char2)Purpose 合并字符串，相当于“|” Returns char1 concatenated with char2. This function is equivalent to the concatenation operator (|). For information on this operator, see Concatenation Operator. Example This example uses nesting to concatenate three character strings: SELECT CONCAT( CONCAT(ename, is a ), job) JobFROM empWHERE empno = 7900;Job-JAMES is a CLERK5.2.2 * LOWERPurpose 变为小写 Returns char, with all letters lowercase. The return value has the same datatype as the argument char (CHAR or VARCHAR2). Example SELECT LOWER(MR. SCOTT MCMILLAN) Lowercase FROM DUAL;Lowercase-mr. scott mcmillan 5.2.3 * LPADPurpose 左填充 Returns char1, left-padded to length n with the sequence of characters in char2; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n. The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string. Example SELECT LPAD(Page 1,15,*.) LPAD example FROM DUAL;LPAD example-*.*.*.*.*Page 15.2.4 * LTRIMSyntax LTRIM(char ,set)Purpose 左截取 Removes characters from the left of char, with all the leftmost characters that appear in set removed; set defaults to a single blank. Oracle begins scanning char from its first character and removes all characters that appear in set until reaching a character not in set and then returns the result. Example SELECT LTRIM(xyxXxyLAST WORD,xyXLA) LTRIM example FROM DUAL;LTRIM exampl-ST WORD5.2.5 * REPLACESyntax REPLACE(char,search_string,replacement_string)Purpose 替换 Returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, all occurrences of search_string are removed. If search_string is null, char is returned. This function provides a superset of the functionality provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE allows you to substitute one string for another as well as to remove character strings. Example SELECT REPLACE(JACK and JUE,J,BL) Changes FROM DUAL;Changes-BLACK and BLUE5.2.6 * RPADSyntax RPAD(char1, n ,char2)Purpose 右填充 Returns char1, right-padded to length n with char2, replicated as many times as necessary; char2 defaults to a single blank. If char1 is longer than n, this function returns the portion of char1 that fits in n. The argument n is the total length of the return value as it is displayed on your terminal screen. In most character sets, this is also the number of characters in the return value. However, in some multibyte character sets, the display length of a character string can differ from the number of characters in the string. Example SELECT RPAD(MORRISON,12,ab) RPAD example FROM DUAL;RPAD exa