编写健壮的PL/SQL代码（一）：同字段同类型、%TYPE、SUBTYPE

面对每天繁重的开发任务和交付工期的压力
我们不会花太多的时间（更多的时候是根本没时间去考虑或经验不够丰富）考虑自己的代码在未来会如何？
或者说在相关环境变化后还能否工作？面对需求变化被修改的量有多大？
说的直白一点，就我们写出的代码没有前瞻性，不够健壮，自适应能力太差
将来整个业务系统中某一个模块的微小改动，都可能导致其他一个或多个协同工作的模块不WORK

每个开发Team对质量的要求"标杆"是不一样的
高效、稳定、可靠的系统是设计出来的，不是维护出来的

本文通过一个简单的PL/SQL案例来告诉大家设计健壮代码的重要意义

在我以前做的一个系统中，有5-6个核心业务数据库，各个数据库之间因为业务需要，有很多数据需要交互
特别是一些关键的数据模型在设计上，各个数据库之间含义是一致的
典型的比如app_col(我这里用这个app_col代表某一类字段)这样的字段
在各个数据库上的很多表结构中均包含这样的关键字段
但实际情况却是很让人头疼，我们的设计人员会设计出行行色色的定义来

比如在ora_a数据库上:

table_aa.app_col number(4),
table_ab.app_col char(3),
table_ac.app_col varchar2(5),
table_ad.app_col number,
table_ae.app_col varchar2(4)

ora_b数据库上

table_ba.app_col number,
table_bb.app_col char(4),
table_bc.app_col varchar2(6)
...

其实，在整个系统（所有数据库）中，这个app_col column的含义都是一样的
但因为各个模块的设计人员随意性和理解上的偏差，导致系统中出现了众多的定义方式
最终导致的后果就是各个模块之间交换数据带来了很大的不变
同样的一段pl/sql代码，到另外一个db上就invalid了，就需要调整代码中变量的类型定义了

可以设想一下：一个已经创建了包括 5000个表/per db和 20000 个模块的综合应用系统
因为业务发展的需要，客户要求对其进行改造(实际上这样的需求在通信行业是很正常的)
经过系统设计专家的分析，如果为了满足本次业务需要，我们需要在整个系统中的某几个相关的DB上
对一些表a.A、b.B、b.C上增加一个新列，同时需要对其中的20多个模块代码进行修改
其中有50个模块引用了这些需要被修改结构的表中的数据
实际情况可能更糟糕，我们的系统设计人员，可能根本无法准确的给出哪些模块中引用那些本次业务支撑需要修改结构的表中的数据

下面看一段代码：

create table test
( id number primary key,
name varchar2(32),
address varchar2(32),
phone varchar2(11)
);

create or replace procedure proc_test (p_id in number) IS 
v_buf VARCHAR2(200);
begin
select name||','||address||','||phone into v_buf from test where id = p_id;
/*其他的业务处理代码部分...*/ 
end;
/

在上面的这段代码中，我们可以说没有问题，也可以说存在一些"潜在"(需要一定条件才能触发)的问题
假定因为业务的需要，客户希望将来支持ID中包含字符的工号信息，所以要提前做好业务支撑
那么我们就需要修改ID字段的数据类型由NUMBER变为VARCHAR2,那么这个时候我的这个proc_test程序将变为invalid
因为这个业务是将来才去支撑，所以发布的时候也不会引起任何问题，测试也没有问题
过了2个月后，客户真正使用的时候才发现这个问题，类似的case见过了太多

如果把代码改成如下的形式，效果是不是好很多呢？

create or replace procedure proc_test (p_id in test.id%TYPE) IS 
v_buf VARCHAR2(200);
begin
select name||','||address||','||phone into v_buf from test where id = p_id;
/*其他的业务处理代码部分...*/ 
end;
/

现在将参数p_id的数据类型直接锁定在该表的列的数据类型上
每次编译过程时，PL/SQL都将从数据字典中查询该列的数据类型，并将其用于被编译的代码中
所以，如果真的将该列设定为字母数字型的，那么也不会对此代码造成任何伤害
对比上面的写法，是不是健壮了许多？

再看上面的一行硬编码 v_buf varchar2(200);
一般情况下，这个也不会导致什么问题，v_buf变量是一个用来存放导出数据的缓存
原来的test表结构中name,address,phone三个字段的最大长度加起来为75
所以我们给200 byte的长度是OK的了
突然某一天，客户又提需求了：我要求我的ID对应完整的联系信息，address要精确到门牌号码
很不幸，我们又得修改我们的表结构和代码了，是不是感觉很烦？

简单的我们直接重新声明v_buf为
v_buf varchar2(1000);
如果我的应用中有上百个类似的调整呢？修改100段代码的工作量是不是让你发疯？而且你都不能保证你的修改是否考虑周全

对于这种情况，Oracle提供了一个很好的解决方案：使用SUBTYPE
SUBTYPE是另一个已定义的数据类型的一个别名
例如，针对本案例，定义一个listbuffer_t的新数据类型,它其实就是VARCHAR2(1000)的另一个名字

CREATE OR REPLACE PACKAGE app_def AS
SUBTYPE listbuffer_t IS VARCHAR2 (1000);
END app_def;

有了这个SUBTYPE，可以将我的声明调整为：
v_buf app_def.listbuffer_t;
这样我就从proc_test过程的声明中去除了硬编码

如果大家在声明类似的变量时，都利用预定义的、应用程序特定的数据类型，如listbuffer_t
那么我们就可以错误点削减到代码中的一个位置，比如本案例中的app_def package中
调整起来是不是也很方便？如果1000字节还不够用，那么我只需要修改app_def中的这一行代码，并重新编译就OK了
是不是相对比较健壮呢？

修改后的解决方案：

SQL> create table test
2 ( id number primary key,
3 name varchar2(32),
4 address varchar2(32),
5 phone varchar2(11)
6 );
Table created
SQL> 
SQL> CREATE OR REPLACE PACKAGE app_def AS
2 SUBTYPE listbuffer_t IS VARCHAR2 (1000);
3 END app_def;
4 /
Package created

SQL> create or replace procedure proc_test (p_id in test.id%TYPE) IS
2 v_buf app_def.listbuffer_t;
3 begin
4 select name||','||address||','||phone into v_buf from test where id = p_id;
5 /*其他的业务处理代码部分...*/
6 end;
7 /
Procedure created
SQL>