Oracle数据库开发规范篇「⑤」【开发篇完结】——PLSQL编写规范【下】（批量提交、封装、包、动态SQL)-CSDN博客

本文链接：https://blog.csdn.net/weixin_39534833/article/details/147570077

梁敬彬梁敬弘兄弟出品

完整系列
Oracle数据库开发规范篇「① 」——SQL编写规范【上】（书写、表名、Select/insert、类型转换、NULL、LIKE)
Oracle数据库开发规范篇「②」——SQL编写规范【下】（绑定变量、动态SQL、SQL嵌套、排序、HINT、并行)
Oracle数据库开发规范篇「③」——PLSQL编写规范【上】（注释、最小化测试案例)
Oracle数据库开发规范篇「④」——PLSQL编写规范【中】（绑定变量)
Oracle数据库开发规范篇「⑤」【开发篇完结】——PLSQL编写规范【下】（批量提交、封装、包、动态SQL)

2.4 尽量使用批量提交

Oracle作为企业级数据库在架构设计上有很多闪光点，锁的管理是其中一项，和其他很多数据库不同的是，锁在Oracle中不是以资源形式存在的，因此不需要尽早提交以释放锁资源。由于在提交以前，数据修改的动作实际已经完成，而提交只是处理一些诸如生产SCN号等后续工作，因此提交一万条数据和提交一条数据所消耗的时间几乎是相同的。

因此尽可能采用批量提交，从而得到更好的性能，请看下面的例子，

首先建立一张和dba_objects表结构相同的表t：

Create table t as select * from dba_objects where 1=2;

--采用逐行提交方式，commit在循环内
alter system flush shared_pool;
truncate table t;
set timing on
DECLARE
BEGIN
FOR cur IN (SELECT * FROM dba_objects) LOOP
INSERT INTO t VALUES cur;
COMMIT;
END LOOP;
END;
/
PL/SQL 过程已成功完成。
执行时间: 已用时间: 00: 00: 12.58

--采用批量提交方式，commit在循环外
alter system flush shared_pool;
truncate table t;
set timing on
DECLARE
BEGIN
FOR cur IN (SELECT * FROM dba_objects) LOOP
INSERT INTO t VALUES cur;
END LOOP;
COMMIT;
END;
/
PL/SQL 过程已成功完成。
执行时间: 已用时间: 00: 00: 04.88

通过上面的例子，大家可以发现，第一段脚本和第二段几乎完全一样，差别就在于一个commit在循环内，而另一个在循环外，但是消耗的时间从12秒多减少到了4秒多，这种性能提升还是非常明显的，因此在条件允许的情况下，尽可能的使用批量提交可以有效的提升程序性能。

2.5 同一过程包中出现重复逻辑块需封装，统一调用

代码重复不仅增加维护难度，还可能导致逻辑不一致。将重复逻辑封装为子程序是解决此问题的最佳方式。封装后的代码具有以下优势：

减少代码重复，便于维护
逻辑变更只需在一处修改
提高代码可读性和可理解性

✗ 重复代码示例（不推荐）

-- 过程1：包含重复的日志记录逻辑
CREATE OR REPLACE PROCEDURE process_orders(p_date DATE) IS
BEGIN
  -- 处理订单逻辑
  
  -- 记录日志（重复逻辑）
  INSERT INTO process_log (
    process_name, process_date, status, message
  ) VALUES (
    'PROCESS_ORDERS', SYSDATE, 'SUCCESS', 'Orders processed for '||TO_CHAR(p_date,'YYYY-MM-DD')
  );
END;
/

-- 过程2：同样包含重复的日志记录逻辑
CREATE OR REPLACE PROCEDURE process_payments(p_date DATE) IS
BEGIN
  -- 处理支付逻辑
  
  -- 记录日志（重复逻辑）
  INSERT INTO process_log (
    process_name, process_date, status, message
  ) VALUES (
    'PROCESS_PAYMENTS', SYSDATE, 'SUCCESS', 'Payments processed for '||TO_CHAR(p_date,'YYYY-MM-DD')
  );
END;
/

✓ 封装后代码（推荐）

-- 创建日志记录子程序
CREATE OR REPLACE PROCEDURE log_process(
  p_process_name VARCHAR2,
  p_status VARCHAR2,
  p_message VARCHAR2
) IS
BEGIN
  INSERT INTO process_log (
    process_name, process_date, status, message
  ) VALUES (
    p_process_name, SYSDATE, p_status, p_message
  );
END;
/

-- 修改后的过程1
CREATE OR REPLACE PROCEDURE process_orders(p_date DATE) IS
BEGIN
  -- 处理订单逻辑
  
  -- 使用封装的日志记录程序
  log_process(
    'PROCESS_ORDERS', 
    'SUCCESS', 
    'Orders processed for '||TO_CHAR(p_date,'YYYY-MM-DD')
  );
END;
/

-- 修改后的过程2
CREATE OR REPLACE PROCEDURE process_payments(p_date DATE) IS
BEGIN
  -- 处理支付逻辑
  
  -- 使用封装的日志记录程序
  log_process(
    'PROCESS_PAYMENTS', 
    'SUCCESS', 
    'Payments processed for '||TO_CHAR(p_date,'YYYY-MM-DD')
  );
END;
/

2.6 生产环境尽量使用包来封装过程和函数

包就是一个把各种逻辑相关的类型、常量、变量、异常和子程序组合在一起的模式对象。包通常由两个部分组成：包说明和包体，但有时包体是不需要的。包说明是应用程序接口；它声明了可用的类型、变量、常量、异常、游标和子程序，包体部分完全定义游标和子程序，并对说明中的内容加以实现。

我们可以认为说明部分是一个可选接口，而包体是一个"黑盒"。我们可以调试、增强或替换一个包体而不同改变接口。

在包说明部分声明的内容都是公有的，对应用程序是可见的。我们必须在所有的其他内容(除了用于为一个特殊的函数命名的编译指示；这样的编译指示必须跟在函数说明之后)声明之后才可以声明子程序。包体中的内容有私有的，它实现了说明部分定义的细节内容，并且对应用程序是不可见的。

紧跟着包体声明部分的是一个可选的初始化部分，它用于初始化包中的变量等。 AUTHID语句决定了是否是所有的打包子程序都按定义者权限(默认)或调用者权限执行，其中涉及到的模式对象是在定义者的模式中解析还是在调用者的模式中解析。一个调用说明能让我们在Oracle数据词典中发布一个Java方法或外部C函数。调用说明靠把程序的名称、参数类型和返回类型映射到它们的SQL副本中来发布程序。

在大多数生产环境中，我们会发现几乎所有的存储过程和函数都被封装在程序包里面，很少会见到单独存在的存储过程和函数。之所以会出现这样的情况，主要是因为采用包封装和单独的存储过程和函数相比有着明显的优势，下面我来给大家简单的介绍下这些优势：
包提供了几个优点：模块化、程序设计更加简单、信息隐藏、附加功能，良好的性能和重载。

模块化包能让我们把逻辑相关的类型、常量、变量、异常和子程序等放到一个命名的PL/SQL
模块中。每一个包都容易理解，包与包之间接口简单、清晰。这将有助于程序开发。
程序设计更加简单设计应用程序时，我们首先要确定的是包说明中的接口信息。我们可以在没有包体的条件下编写并编译说明部分。然后引用该包的存储子程序也会被编译。在完成整个应用程序之前，我们是不需要完全实现包体部分的。
信息隐藏有了包，我们就可以指定哪些类型、常量、变量、异常和子程序等是公有(可见和可访问)或私有(隐藏和不可访问)。例如，如果一个包里包含了四个子程序，其中三个是公有的一个是私有的。包就会隐藏私有子程序的实现，这样的话，如果实现内容发生改变，受到影响的只有包本身(不是我们的应用程序)。同样，对用户隐藏实现细节也能保证包的完整性。
附加功能打包公有变量和游标在一个会话期会一直存在。所以，它们可以被当前环境下的所有子程序共享。并且它们允许我们跨事务来维护数据而不用把它保存在数据库中。
良好的性能在我们首次调用打包子程序时，整个包就会被加载到内存中。所以，以后调用包中的相关子程序时，就不需要再次读取磁盘了。包能阻塞级联依赖，这样就能避免不必要的编译。
例如，如果我们改变打包函数的实现，Oracle不需要重新编译调用子程序，因为它们并不依赖于包体。

2.7 重载

在程序包中，我们可以重载一些存储过程和函数，也就是说，我们可以在单个程序包中创建多个拥有不同数量和类型变量但是名称相同的子程序，这点在实际编码的过程中是非常实用的。
鉴于以上这些优势的存在，我们在生产环境中应当极可能的使用程序包，而不是单个的存储过程和函数。

2.8 动态SQL编写需记录真实SQL记录表中

动态SQL是PL/SQL开发过程中经常使用的方法之一。很多情况下，比如根据业务的需要，如果输入不同查询条件，则生成不同的执行SQL查询语句，对于这种情况需要使用动态SQL来完成。再比如，对于分页的情况，对于不同的表，必定存在不同的字段，因此使用静态SQL则只能针对某几个特定的表来形成分页。而使用动态的SQL，则可以对不同的表，不同的字段进行不同的分页。这些情况的处理通常都是用动态SQL来完成。

在实际开发过程中，由于业务逻辑的复杂性，我们会大量的使用动态SQL，这样做在带来极大的便利的同时，往往也会产生一些不可预知的后果，比如操作的不可追踪性，容易产生SQL注入，为数据库安全带来隐患等。

为了有效的降低使用动态SQL带来的副作用，一个比较可行的做法是建立一种动态SQL执行日志表，将所有被执行的动态SQL以及执行者，执行时间等相关信息记录到该日志表中。
以便检查是否动态SQL在按照自己的最初设计在执行，以及在发现数据异常的时候可以进行反向溯查。

同样为了避免在所有执行动态SQL时都去做维护日志信息的操作，这样做一来增加工作量，同时也经常会被遗忘。可以参照上面子程序的相关内容，把执行动态SQL以及维护日志信息的操作写入一个子程序，这样以后在执行动态SQL的时候直接调用这个子程序即可。这样可以减少开发开发工作量且保证所有的动态SQL操作都被记录。