Oracle 高效SQL编程指南 V1.1.docVIP

Confidential (秘密) 协同办公组数据库高效编程指南 2010.10 Written By Talkweb Talkweb 湖南拓维信息系统有限公司 1996,2009 All Rights Reserved 文档标识： TW_PROC_DS_303 当前版本： V1.1 当前状态： 草稿 ( 发布日期： 发布 修改历史 日期 版本 作者 修改内容 评审号 变更控制号 2010/10/11 1.0 熊坚 新建 2011/08/23 1.1 陈虹君 修改增加完善 写在前面的话 规范SQL书写方式，让SQL可以共享，这是大家必须遵守的原则，但后续谈到的所有高效SQL编写原则，都只是建议与一般规则，具体执行还得看SQL执行计划,所以高效的可能也不一定高效 常用语法 变量声明 原则：代码中声明与表的字段相对应的变量时，应保证变量名和字段名相同。 说明：这样要求的目的是增强可读性。 示例： DECLARE v_DateField T_TABLENAME.DATEFIELD%TYPE; BEGIN SELECT DATEFIELD INTO v_DateField FROM T_TABLENAME WHERE ROWNUM = 1; END; 原则：代码中声明与表的字段相对应的变量时，对类型的定义需要使用%TYPE方式。 说明：这样表结构的变动不会影响存储过程，避免表结构变更后因变量长度或类型不一致而导致现有程序报错的问题。 返回值 原则：函数的返回值定义应遵从下列规范：成功出口返回0，失败出口返回大于0整数。过程中定义的用于返回错误码的OUTPUT参数，其定义应遵从下列规范：成功出口返回0，失败出口返回非0整数。 符号* 原则：脚本中不允许出现“*”的用法，必须用实际的字段名代替，INSERT语句必须指定要插入的字段名。 示例： 1、游标定义 错误用法： CURSOR c_CursorName IS SELECT * FROM TABLENAME ... 正确用法： CURSOR c_CursorName IS SELECT FIELD1, FIELD2, ... FROM TABLENAME ... 说明：ORACLE 在解析的过程中, 会将* 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 需要消耗更多的时间，因此查询时，请在SELECT后把需要查询出的字段列举出来，不要用*代替 2、INSERT 语句 错误用法： INSERT INTO TABLENAME VALUES ... 正确用法： INSERT INTO TABLENAME (FIELD1, FIELD2, ...) VALUES ... 包的使用 建议：ORACLE的包类似C++中的Class，有包头和包体两部分组成；包能够把相关的功能封装性在一个包中，包里面里可以有函数和过程。PACKAGE与PROCEDURE和FUNCTION的优点是封装性比较好；在开发过程中，可以把功能点紧密相关的PROCEDURE、FUNCTION封装在一个包里面。在对包体进行编译时，调用该包的存储过程不会失效。 绑定变量 原则：使用绑定变量的SQL语句，能使用绑定变量方式的业务逻辑下不允许使用绑定常量实现。 绑定变量目的：实现SQL共享，减少硬解析，减小CPU耗时 说明：在存储过程拼SQL语句作为动态SQL执行时，尤其需要注意。 示例： 错误用法： v_SQL := DELETE FROM TABLENAME WHERE FIELD1 SYSDATE AND FIELD2 = 102; EXECUTE IMMEDIATE v_SQL; 正确用法： v_ID := 102; v_SQL := DELETE FROM TABLENAME WHERE FIELD1 :1 AND FIELD2 = :2; EXECUTE IMMEDIATE v_SQL USING SYSDATE, v_ID; 原则：为提升性能，减少循环的开销，可以使用批量绑定。 说明：下面示例中的UPDATE语句可以一次将多条记录进行更新，而避免了通过使用循环同一条SQL执行多次。 示例： CREATE OR REPLACE PROCEDURE P_CSP_TEST/* Description : 根据输入的多个工单流水号，更新对应的SERVICECLASSID值 Author : 姓名 工号 Date : YYYY-MM-DD Version : 版本 Caller : 调用者 Callee : 被调