【Oracle】Package 存储过程编写以及其他实用技术

这篇文章是之前自己在公司的一篇技术分享，搬过来就不提供脚本了!

前段时间在处理生产异常时发现，测试数据库和仿真数据库已很久没有同步生产的清洗后数据和数据结构。开发人员在处理生产异常时往往要在测试或者仿真环境中重新模拟数据来进行场景复现，若需要以大量数据为基础进行复现时就比较痛苦了。

目前遇到这种情况还需要知会运维人员到生产数据库备份数据，先导出dmp再导入测试或仿真环境，之后还需要对数据进行敏感信息清洗。由于数据的特殊性，这个过程只能通过运维人员手动处理，非常容易出错且效果不理想（这里会涉及到一些blob字段无法导入到系统中，还有表空间不一致也影响导入的情况）。有鉴于此，我决定写一个同步存储过程实现自动化同步。

与一般的存储过程不一样，本次的存储过程是通过编写Oracle的Package（包头）和 Package Body（包体）来实现同步的。如下图：

其中TIMSSPRD_SYNC是数据库同步入口，而TIMSS_TABLE是同步表方法的入口。

之所以使用Package来编写存储过程的原因在于：

1. 对于程序员来说，Package/Package Body的写法更贴近于面向对象的开发理念（个人认为跟Java中的接口和接口实现类如出一辙）；

2. Package内的方法可以重载，也可以存在多个同名的方法根据参数的个数或者参数的类型找到正确的方法；

3. Package可以被其他用户调用，是全局的方法；

4. Package既然是全局的，那当然也可以被授权咯（可以按需分批，那些用户可以用，那些用户不可以用）；

先打开TIMSSPRD_SYNC，里面有一个TIMSS_SYNC_PRO的方法，里面有四个参数分别是OPER_SORT（同步类型）、OPER_TYPE（同步方式）、OPER_TABLE（同步表名）和OPER_PREFIX（同步前缀），如下图：

1. OPER_SORT（同步类型） ：脚本将根据填入的内容去更新指定的类型信息

2. OPER_TYPE（同步方式） ：是include（包含）还是exclude（排除）

3. OPER_TABLE（同步表名） ：其实这个字段不单单是表名（因为现在只有表），这里应该是同步对象的名称

4. OPER_PREFIX（同步前缀） ：同理，这里应该是同步对象的前缀

按照存储过程的逻辑，如果什么参数都不填的话会执行全类型更新（这无疑跟dmp方式一致，虽然基本不会用到但还是提供吧）。否则按照同步信息去判断需要调用的方法。OK，来到这里先告一段落，下面在看看TIMSS_TABLE这个Packages。

在TIMSS_TABLE中里面就是真正的执行方法共四个，分别是：ORGANIZE_DATA（整理数据）、CREATE_AND_BAK_TABLE（创建与备份表）、CREATE_TABLE_EXEC（执行创建表）和DROP_TABLE（表删除），如下图：

由于涉及到公司内部的同步逻辑，这四个方法的具体内容就不说了但在编写TIMSS_TABLE中用到了6个有用的存储过程知识想给各位分享一下：

1 使用自定义函数去对参数内容进行分割（Oracle是没有自带分割函数）

为了方便使用在网上抄了一个自定义函数_SPLITSTR(需要分割的字符串，分割字符)_，如下图：

这个函数最后是以管道（Pipelined）的方式返回，用一个虚拟的Table将其内容接住 ，如下图：

2 在存储过程中Exception异常捕获的使用

Begin
        执行方法...
        Exception When Others Then
        异常处理信息... 
End；

实际使用效果如下：

这能够更优雅地获取到存储过程中的异常信息。

3 采用Using进行赋值即使变量内容含特殊字符也不会抛错

4 Dbms_Lock.Sleep跟Java线程中Sleep用法一致

有时候某些操作是需要按顺序执行的，存储过程在模拟多线程处理（这个后面会进行分享如何将Oracle数据库模拟多线程操作）时太快很容易发生数据冲突，这时就需要用_Dbms_Lock.Sleep_方法让它暂停线程（不过这个_Dbms_Lock_的包需要管理员权限开放给需要的用户才可以使用，比较麻烦 ）

5 使用Drop Table的时候加上Purge，可以减少表空间的消耗

因为Oracle是有闪回_（FlashBack）的功能的，平常删除的东西其实会存放在回收站（Recycle Bin）_里面不会被立即清除，这是为了方便闪回时恢复数据。所以一般Drop Table后虽然表已经删除了，但表空间是不会释放。为此这次存储过程需加上_Purge_来让这个表彻底删除（这里的处理逻辑是先创建好目标表后删除掉备份表）同时释放表空间。日常使用不建议使用这种方式，误操作后不能通过FlashBack恢复是非常危险的。

6 PL/SQL的Brower里面应是默认设置为“My Objects“

好几次看到同事们的PL/SQL一打开就是“All Objects”这样是非常危险的。如果用DBA的账号登陆你会发现目录树的每个节点都可以看到系统级别内容，像是什么 SYS.XXX开头的文件。万一不小心给删除掉了，那整个实例就完蛋了。建议都将自己的PL/SQL设置一下，哪怕你现在不需要用到DBA的账号也好，养成一个好习惯是没错的。

设置可以通过_Tools->Preferences->User Interface->Browser_点击_Filters..._按钮去进行设置，如下图：

坦白说，这个存储过程其实还存在很多问题，譬如大数据量同步的时候就不能使用这个存储过程了、DBlink本身也存在lob数据类型同步问题、存储过程内没有做同步信息记录（这个需要的话要后续跟进）......