程序包 Package
断开了依赖链
实验依赖关系:
<1> 首先不使用包
-- 创建表
CREATE table t (x int);
-- 创建视图
create view v as select * from t;
-- 创建存储过程
create procedure p
as
begin
for x in (select * from v)
loop
null;
end loop;
end;
/
-- 创建函数
create function f
return number
as
l_cnt number;
begin
select count(*) into l_cnt from t;
return l_cnt;
end;
/
-- 查询他们之间的依赖关系
select name, type, referenced_name, referenced_type
from user_dependencies
where referenced_owner = user
and name in ('P','T','V','F')
order by name;
-- 目前得到的结果, 全部是 valid 的, 即全部是可用的
-- 测试, 当我修改了 表 t 以后,
alter table t add y number;
-- 确认一下现在对象的状态
SELECT OBJECT_NAME, object_type, status
from user_objects
where object_name in ('T','V','P','F');
-- 看一下上面得到的结果, 除了修改的表 t 以外, 全部是 invalid 的, 换句话说, 全部要重新确认, 编译等.
-- 接下来再创建一个存储过程 P2
create or replace procedure p2
as
begin
p;
end;
/
-- 检查这时候各个对象的可用性
SELECT OBJECT_NAME, object_type, status
from user_objects
where object_name in ('T','V','P','F', 'P2');
我们惊奇的发现, 居然除了 FUNCTION 剩下的全部又可用了,证明创建P2的过程中, 由于调用了P, 而P又使用了V,
-- 另外, 再看看依赖关系
select name, type, referenced_name, referenced_type
from user_dependencies
where referenced_owner = user
and name in ('P','T','V','F', 'P2')
order by name;
P2 –> P –> V –> T
显然, 编译 P2 不仅仅编译了P2, 它还编译了P 和 V.
-- 测试改变表t
alter table t add z number;
-- 查看各个对象的状态
发现除了表 T 意外, 又全部不可用了, 就连新创建的P2也不可用了, 因为P2依赖于P, 这是一个”链”
<2> 下面我们使用package
-- 清理现场
drop procedure p;
drop procedure p2;
drop function f;
-- 创建包 P1
create package p1
as
procedure p;
end;
/
create package body p1
as
procedure p
as
begin
for x in (select * from v)
loop
null;
end loop;
end;
end p1;
/
-- 创建包 P2
create package p2
as
procedure p;
end;
/
create package body p2
as
procedure p
as
begin
p1.p;
end;
end p2;
/
-- 查询依赖关系
select name, type, referenced_name, referenced_type
from user_dependencies
where referenced_owner = user
and name in ('P','T','V','F', 'P2', 'P1')
order by name;
注意: 这时 PACKAGE BODY P2 依赖于 PACKAGE P1, 不依赖于程序包体, 而依赖于程序包(接口), 这表示P1的程序包体可以变成无效, 而不影响别的东西.
-- 目前, 各个对象的状态
SELECT OBJECT_NAME, object_type, status
from user_objects
where object_name in ('T','V','P','F', 'P2', 'P1');
-- 测试修改表
alter table t add a number;
-- 再看各个对象的状态
SELECT OBJECT_NAME, object_type, status
from user_objects
where object_name in ('T','V','P','F', 'P2', 'P1');
发现只有 P1 的PACKAGEBODY, V 两个对象不可用, 因为这两个对象都依赖于表 t.
现在, 只要说明(程序包的接口)不改变, 系统中依赖代码就不会变成无效, 这可以使得改动局部化而不会导致系统大量重新编译.
Oracle 会执行自动依赖编译, 所以, 当我们执行 exec p2.p , 以上所有的对象都会变成可用状态.
增加了名字空间
支持重载
支持封装
支持会话永久变量
支持精心制作的启动代码
把相关的功能组织在一起
使用静态SQL
如果利用静态 SQL 可以达到目的, 那么就尽量不要使用动态 SQL.
静态SQL VS 动态SQL: 静态SQL的好处, 编译时检查, PL/SQL验证数据类型, 数据字典中建立维护依赖, 基础对象改变, 代码会自动适应, 静态SQL可以实现分析一次, 多次执行, 静态SQL更快.
寻找替换动态SQL: 例如下面的动态SQL , 可以通过静态的PL/SQL function 来替换
create or replace function get_value_dyn (p_empno in number, p_cname in varchar2) return varchar2 as l_value varchar2(4000); begin execute immediate 'select ' || p_cname || 'from emp where empno = :x' into l_vale using p_empno; return l_value; end; /
替换:
create or replace function get_value_static (p_empno in number, p_cname in varchar2) return varchar2 as l_value varchar2(4000); begin select decode(upper(p_cname), 'ENAME', ename, 'EMPNO', empno) into l_value from emp where empno = p_empno; return l_value; end; /
使用调用者权限
执行PL/SQL方法两种:
使用定义者权限, 表示执行过程时利用存储过程拥有者的基本权限执行SQL.
使用调用者权限, 表示执行过程时以调用者用户当前的权限执行SQL语句.
基本上可以说, 使用定义者权限就可以了, 如果你考虑安全问题, 那么用别的方法来解决, 这里大师推荐使用定义者权限来执行. 在开发程序阶段, 可以使用调用者权限来进行测试之类的. 因为你使用了调用者权限, 那么就使用了调用者的模式, 比如, 同样有个table 名为 t, 在你测试的模式下也有一个t, 那么, 程序开发阶段使用调用者权限, 使用的是当前开发用户模式下的t, 而真正到了生产环境, 使用的是生产环境下的 t.
隐式游标还是显示游标
隐式游标效率要高于显示游标, 但是在大批量的操作时, 必须使用显示游标.
以下两种情况, 可以使用隐式游标:
-- 1 , 单行取的选择, 在查询中最多检索出一行时
例如: select <column1, column2 …> into <variable1, value2 …> from <table>
--2 , 结果集, 在检索有限数目的行时, (10 行以内)
例如: for x in (select … from … where …) loop process… end loop; -- 10 行以内