|
下面这个例子更有代表性，案例发生的环境与背景还是与上一节所讲的一样，是在一个复杂的综合各种应用的业务系统中。其中某个业务系统中的一张表zrp（结构见后面），存放了公司员工的基本信息，这张表有多个应用模块在访问，其中有一个模块(proc_demo)用来定期将zrp表中的记录的前3个字段抽取出来供另外一个模块使用（这个模块不关zrp结构是否发生变化，永远就提取前3个字段） 备注：我这里为了模拟场景，数据抽取出来仅仅做一个display，用来模拟其他业务。 所以，项目一开始的时候，我们的开发人员是这样设计代码的： 第一步：创建一张表zrp,并导入原始的员工信息 create table zrp
( id number,
name varchar2(32),
address varchar2(32)
);
insert into zrp values(1,'Oracle','china beijing');
insert into zrp values(2,'IBM','china beijing');
insert into zrp values(3,'HP','china beijing');
commit; 注：字段ID,NAME,ADDRESS是模块要访问的信息 第二步：定义一个package. 该package包括一个REF CURSOR类型声明和一个函数，函数用来返回zrp表中所有行的所有列. create or replace package apk_demo IS
TYPE zrp_rc IS REF CURSOR return zrp%ROWTYPE;
function get_info return apk_demo.zrp_rc;
end apk_demo;
/
create or replace package body apk_demo IS
function get_info RETURN apk_demo.zrp_rc IS
v_ret apk_demo.zrp_rc;
begin
open v_ret for select * from zrp;
return v_ret;
end get_info;
end apk_demo;
/ 第三步：编写proc_demo模块，实现数据抽取 create or replace procedure proc_demo IS
v_zrp_buf apk_demo.zrp_rc;
v_id number;
v_name varchar2(32);
v_address varchar2(32);
begin
v_zrp_buf := apk_demo.get_info;
loop
fetch v_zrp_buf INTO v_id,v_name,v_address;
exit when v_zrp_buf%NOTFOUND;
dbms_output.put_line(v_id||'#'||v_name||'#'||v_address);
end loop;
close v_zrp_buf;
end;
/ 可以看到，在proc_demo模块中对列的信息使用硬编码的形式，这在特定的情况下是要出"问题".（见第一节） 注：我这里提到的问题，其实也不能算是特别大的问题，只要你能cover的住就不能叫问题。 发布后，模块工作的很好。下面是测试效果 SQL> create table zrp
2 ( id number,
3 name varchar2(32),
4 address varchar2(32)
5 );
Table created
SQL> insert into zrp values(1,'Oracle','china beijing');
1 row inserted
SQL> insert into zrp values(2,'IBM','china beijing');
1 row inserted
SQL> insert into zrp values(3,'HP','china beijing');
1 row inserted
SQL> commit;
Commit complete
SQL>
SQL> create or replace package apk_demo IS
2 TYPE zrp_rc IS REF CURSOR return zrp%ROWTYPE;
3 function get_info return apk_demo.zrp_rc;
4 end apk_demo;
5 /
Package created
SQL> create or replace package body apk_demo IS
2 function get_info RETURN apk_demo.zrp_rc IS
3 v_ret apk_demo.zrp_rc;
4 begin
5 open v_ret for select id,name,address from zrp;
6 return v_ret;
7 end get_info;
8 end apk_demo;
9 /
Package body created
SQL>
SQL> create or replace procedure proc_demo IS
2 v_zrp_buf apk_demo.zrp_rc;
3 v_id number;
4 v_name varchar2(32);
5 v_address varchar2(32);
6 begin
7 v_zrp_buf := apk_demo.get_info;
8 loop
9 fetch v_zrp_buf INTO v_id,v_name,v_address;
10 exit when v_zrp_buf%NOTFOUND;
11 dbms_output.put_line(v_id||'#'||v_name||'#'||v_address);
12 end loop;
13 close v_zrp_buf;
14 end;
15 /
Procedure created
SQL> set serveroutput on
SQL> exec proc_demo;
1#Oracle#china beijing
2#IBM#china beijing
3#HP#china beijing
PL/SQL procedure successfully completed
SQL> 系统运行了半年后，突然有一天，一个客户说，我的另外一个报表模块中，需要提供员工的联系信息，要求在员工信息表(zrp)增加一个字段，存放员工的手机号码。而且，只有我这个一个报表需要这个手机号码字段，不影响其他现有模块。 于是，我们的需求分析人员，把客户的需求反馈到了开发team,而这个开发team是一个不太规范的team,设计人员也没有全局的分析这样一个“微小”的动作会导致什么后果，直接下单子给报表开发小组，修改了报表代码，并通知实施team按照预定的时间实施了该变更。 DBA按照change的要求，修改了该表的结构，增加了一列 SQL> alter table zrp add phone varchar2(11);
Table altered
SQL> select * from zrp;
ID NAME ADDRESS PHONE
---------- ------------- ----------------- --------
1 Oracle china beijing
2 IBM china beijing
3 HP china beijing
SQL> select object_name,object_type,status from user_objects where status='INVALID';
OBJECT_NAME OBJECT_TYPE STATUS
--------------------- ------------------- -------
APK_DEMO PACKAGE BODY INVALID
APK_DEMO PACKAGE INVALID
PROC_DEMO PROCEDURE INVALID
SQL> alter procedure proc_demo compile;
Warning: Procedure altered with compilation errors
SQL> show error
Errors for PROCEDURE STUDY.PROC_DEMO:
LINE/COL ERROR
-------- -----------------------------------------------------------------------
9/6 PLS-00394: wrong number of values in the INTO list of a FETCH statement
9/6 PL/SQL: SQL Statement ignored
SQL>
接着，DBA发现，系统中有一些依赖表zrp的对象invalid了，然后他尝试去compile 他们，但他发现有几个对象始终无法compile成功。很明显，我上面的函数get_info在返回的时候是zrp的一行的所有列，而之前因为特定的业务需求，我指定了列名，同时一个proc_demo在fetch的时候，在修改了结构，必须要包含4个变量，而现在into到了3个变量中。所以他们变成了invalid. 这个时候，报表的那个及其他模块均测试OK了。就剩这2个东西编不过去了，没办法，只能叫开发人员过来调整了，回退是不可能的，客户要求今天必须上。于是一通电话后（在一个走流程管理和版本控制相对严格的的公司里，这个协调是个头疼的事儿），开发人员气喘吁吁的赶到了客户现场，分析了问题后修改并重新发部了该脚本。 这样的场景，不知道大家遇到的多不多，我想做运维但每权修改代码的DBA还是比较有体会的。 那么，这样的问题，我们在设计pl/sql代码的时候如何避免呢？ 其实Oracle对这个问题有一个很简单的解决方案：不要使用单个变量的列表，而采用记录结构。 下面是针对这个问题的修改方案： create or replace package body apk_demo IS
function get_info RETURN apk_demo.zrp_rc IS
v_ret apk_demo.zrp_rc;
begin
open v_ret for select * from zrp;
return v_ret;
end get_info;
end apk_demo;
/
create or replace procedure proc_demo IS
v_zrp_buf apk_demo.zrp_rc;
v_zrp_row zrp%rowtype;
begin
v_zrp_buf := apk_demo.get_info;
loop
fetch v_zrp_buf INTO v_zrp_row;
exit when v_zrp_buf%NOTFOUND;
dbms_output.put_line(v_zrp_row.id||'#'||v_zrp_row.name||'#'||v_zrp_row.address); --模拟数据处理
end loop;
close v_zrp_buf;
end;
/
下面看看这样修改后的效果： SQL> create or replace package body apk_demo IS
2 function get_info RETURN apk_demo.zrp_rc IS
3 v_ret apk_demo.zrp_rc;
4 begin
5 open v_ret for select * from zrp;
6 return v_ret;
7 end get_info;
8 end apk_demo;
9 /
Package body created
SQL> create or replace procedure proc_demo IS
2 v_zrp_buf apk_demo.zrp_rc;
3 v_zrp_row zrp%rowtype;
4 begin
5 v_zrp_buf := apk_demo.get_info;
6 loop
7 fetch v_zrp_buf INTO v_zrp_row;
8 exit when v_zrp_buf%NOTFOUND;
9 dbms_output.put_line(v_zrp_row.id||'#'||v_zrp_row.name||'#'||v_zrp_row.address);
10 end loop;
11 close v_zrp_buf;
12 end;
13 /
Procedure created
SQL> set serveroutput on
SQL> exec proc_demo
1#Oracle#china beijing
2#IBM#china beijing
3#HP#china beijing
PL/SQL procedure successfully completed
下面的代码用来再次模拟增加的字段支撑其他功能，看看对proc_demo模块是否有影响：
SQL> alter table zrp add zipcode varchar2(6);
Table altered
SQL> alter package apk_demo compile body;
Package body altered
SQL> alter procedure proc_demo compile;
Procedure altered
SQL> exec proc_demo
1#Oracle#china beijing
2#IBM#china beijing
3#HP#china beijing
PL/SQL procedure successfully completed
SQL> OK，到现在，proc_demo是不是显得健壮一些了呢？
|
