PL/SQL最差实践14例

最近的position是pl/sql编写专业户，网上找到了这篇文章，感觉还不错。摘来记录下。

1. 超长的PL/SQL代码
　　影响：可维护性，性能
　　症状：
　　在复杂的企业应用中，存在动辄成百上千行的存储过程或上万行的包。
　　为什么是最差：
　　太长的PL/SQL代码不利于阅读，第三方工具在调试时也会出现代码行混乱等问题。PL/SQL存储对象(存储过程、包、函数、触发器等)行数上限约为6000000行，但实际工作中，当包大小超过5000行就会出现调试问题。
　　解决之道：
　　PL/SQL代码在执行前会被加载到shared pool中，shared
pool以字节为单位，UNIX下为64K，桌面环境下为32K，可以通过查询数据字典USER_OBJECT_SIZE的PARSED_SIZE字段查看对象大小。对于较大的包，应采用拆包策略，抽取复用部分，减少重复代码;对于较大的存储过程，应将存储过程组织到包中，易于管理;对于较大的匿名块，应将匿名块重新定义成子过程保存在数据库中。
　　2. 脱离控制的全局变量
　　影响：可维护性
　　症状：在包中使用了全局变量，在多个位置对全局变量进行操作。
　　CREATE OR REPLACE PACKAGE BODY PKG_TEST IS
　　GN_全局变量 NUMBER(12, 2);
　　PROCEDURE 过程A IS
　　BEGIN
　　GN_全局变量:=1;
　　END;
　　PROCEDURE 过程B IS
　　BEGIN
　　GN_全局变量:=2; -- 这里对全局变量进行了另外的操作
　　END;
　　为什么是最差：
　　全局变量可以在整个包范围内被访问到，因此对全局变量的跟踪和调试会比较困难。如果变量是在package中定义的，变量还可以被其他包访问，这将会更为危险。
　　解决之道：
　　减少或取缔全局变量的使用，对于要在过程间交互的变量，通过参数传递来实现。如果必须使用全局变量，应对全局变量进行get/set函数封装，规范对全局变量的访问。

3. PL/SQL中嵌入复杂SQL语句
　　影响：可维护性
　　症状：
　　在PL/SQL代码中嵌入SQL语句，如：
　　...
　　PROCEDURE 过程A IS
　　BEGIN
　　UPDATE T_A SET COL1 = 10;
　　END;
　　PROCEDURE 过程B IS
　　BEGIN
　　DELETE FROM T_A WHERE COL1=10;
　　END;
　　...
　　为什么是最差：
　　? PL/SQL代码中嵌入SQL语句使得代码含义变得难于阅读和理解
　　? 在多个位置对表进行访问，不利于SQL优化
　　解决之道：
　　?
将分散SQL语句进行封装，例如上例中的删除语句，可以封装为“prc_删除T_A()”过程参数为T_A的type类型，对T_A的删除操作都委托此过程处理，当T_A表增加或删除字段时，主要的变化都集中在这些过程中，对其他逻辑影响较少
　　? 对SQL的优化集中在封装的过程中
 

4. “异常”的异常处理
　　影响：可维护性，健壮性
　　症状：我们来看下面的代码：
　　PROCEDURE 过程A(错误代码 out varchar2,错误信息 out varchar2) IS
　　BEGIN
　　...
　　UPDATE T_A SET COL1 = 10;
　　SELECT ... FROM T_A WHERE ...;
　　DELETE FROM T_A WHERE COL1 = 20;
　　...
　　EXCEPTION
　　WHEN OTHERS THEN
　　...
　　END;
　　为什么是最差：
　　整个过程只有一个WHEN OTHERS 的异常段，示例中的三个语句发生的异常只能被最外层捕捉，无法区分发生异常的种类和位置。
　　解决之道：
　　? 不使用WHEN OTHERS捕捉所有异常，例如不应该捕捉NO_DATA_FOUND异常，使用专用的Exception来捕捉特定的异常。
　　? 声明自己的异常处理机制，处理与业务相关的异常，将业务异常与系统运行期异常分开处理。
　　? 自定义完整的异常信息，异常信息中包含异常发生时的场景。

　5. 固定的变量长度和变量类型
　　影响：可维护性
　　症状：当声明基于字段类型的变量时，尤其是varchar2类型，直接使用固定长度声明。
　　为什么是最差：
　　? 这种硬编码的变量大小很可能与数据库中实际大小不符
　　? 如果字段的类型、大小等发生变化，还需要到PL/SQL中调整变量
　　解决之道：
　　使用%Type声明与字段类型相关的变量。
　　6. 不做单元测试
　　影响：健壮性
　　症状：PL/SQL代码中蕴含大量的业务逻辑，这些逻辑编写完毕后，没有提供合适的单元测试用例用于验证。
　　为什么是最差： 不做单元测试的危害这里就不再废话了。
　　解决之道：
　　PL/SQL并没有提供诸如JUnit之类易用的单元测试工具。现在有一些开源工具可以使用。使用utPLSQL(http://utplsql.sourceforge.net/)工具进行单元测试，或DBUnit进行二次开发，满足不同应用的需要。
　　7. 使用代码值而不使用代码名称
　　影响：可维护性
　　症状：我们看下面的代码：
　　方法1：
　　V_sex:=’1’; -- 男
　　方法2：
　　CONST_MALE CONSTANT VARCHAR2(1) := '1'; -- 定义常量 男
　　V_sex:=CONST_MALE;
　　为什么是最差：
　　?
从例子中可以看出，同样是使用性别，方法1是直接使用代码值，方法2是使用常量，看上去似乎方法2要比方法1麻烦一些，但方法2比方法1更为直观，代码的可读性也更好，代码的阅读者不需要关注“1”代表什么含义。
　　? 当其他项目男性性别定义修改为“2”时，采用方法1编码的程序需要仔细查找每一段代码，容易产生错误，而采用方法2编码的程序只修改常量定义即可。
　　解决之道：
　　将常量定义放入到公共的代码包中，供其他程序共享，所有涉及到代码值的比较、引用等都必须使用常量名，而不能直接书写代码值。对于一些复杂的代码值间的关系可以进一步封装，以函数的方式提供调用。

　8. 不对PL/SQL对象进行配置管理
　　影响：可维护性
　　症状：PL/SQL对象(package、package body、trigger、procedure、type、type
body、函数等)的代码没有使用配置管理工具进行维护和更新。
　　为什么是最差：
　　因为Oracle内部结构的差异，对象的管理具有一定的难度，尤其是在并行开发的情况下。
　　? 对象职责划分不清，造成多人同时修改一个对象，在编译时，如果后来者没有获取最新的代码，会造成前一个开发人员修改的代码被覆盖
　　? Oracle对象不能追溯既往，数据库中只能保存最新
　　解决之道：
　　? 规范开发过程，以配置管理工具上的PL/SQL代码为最新。
　　? 使用第三方插件减少同步工作量，如PL/SQL Developer下的VCS版本控制插件。
　　9. IF … ELSE …的坏味道
　　影响：可维护性
　　症状：大量使用IF … ELSE
　　为什么是最差：
　　大量存在IF/ELSE，造成代码逻辑混乱、不易修改。无论是PL/SQL还是其他编程语言，这种代码都已经飘着“bad smell”了。
　　解决之道：
　　? 使用Oracle数据库的继承特性，通过type实现对象的继承，利用策略模式封装差异，对外提供统一的调用接口
　　? 将频繁使用的IF/ELSE代码重构为单独的过程或函数，供其他代码复用

　10. 在非自治事务中控制事务
　　影响：数据一致性
　　症状：
　　在PL/SQL非自治事务代码中控制事务，例如：
　　PROCEDURE 过程A(错误代码 out varchar2,错误信息 out varchar2) IS
　　BEGIN
　　...
　　SAVEPOINT A;
　　UPDATE T_A SET COL1 = 10;
　　COMMIT;
　　DELETE FROM T_A WHERE COL1 = 20;
　　ROLLBACK TO A;
　　...
　　EXCEPTION
　　WHEN OTHERS THEN
　　...
　　END;
　　为什么是最差：
　　这种行为是我认为最差实践中危害最大的一种。随处可见的事务控制代码会造成数据不一致，引发的问题难于跟踪和调试。
　　解决之道：
　　? 由调用者决定何时提交或回滚事务。
　　? 对于需要特殊事务管理的过程如记载日志，使用自治事务。

　11. 不使用绑定变量
　　影响：性能
　　症状：直接使用值而不使用绑定变量进行查询。尤其是在拼写sql的程序中，这种情况更突出。
　　为什么是最差：
　　这是一个常见问题，当代码中大量充斥固定的代码值时，数据库引擎每次都需要重新解析，不能使用既有的执行计划。
　　解决之道：对于这种经常执行的语句，使用绑定变量而非实际参数值执行。
　　12. 慎用ROWNUM=1
　　影响：可维护性、数据一致性
　　症状：在读取数据时，有时只需要取一行，这时WHERE条件中就会用到ROWNUM=1。
　　为什么是最差：
　　之所以将这个实践评成最差，是因为笔者在实际工作中曾经遇到过这类问题，跟踪和调试都很困难。ROWNUM本身的处理顺序是在ORDER BY
之前，所以当ROWNUM=1时产生的结果很可能是随机的。
　　解决之道：了解要查询数据的含义，使用其他条件限制结果集。
　　13. 灵活的动态SQL
　　影响：可维护性、性能
　　症状：EXECUTE IMMEDIATE ‘SELECT A FROM TAB1’ INTO v_a;
　　为什么是最差：
　　动态SQL失去了编译期检查能力，将发生问题的可能性推迟到运行期。动态SQL也不利于优化，因为只有在运行期才能得到完整的SQL语句。
　　解决之道：尽量避免使用动态SQL，对于易变的业务逻辑可以抽取到中间层实现。
　　14. 对ROWID进行访问
　　影响：数据一致性
　　症状：使用ROWID作为数据更新、删除的WHERE条件
　　为什么是最差：
　　ROWID属于Oracle底层存储结构，会随着数据的迁移、导入、导出发生变化，而业务逻辑则不应依赖底层存储结构。
　　解决之道：使用主键进行数据操作。