当对包进行第一次实例的时候,将从磁盘读入该包的中间代码并将其放入系统全局工作区SGA的共享缓冲区中。然而,包的运行状态,即打包的变量和游标,将存放在用户全局区(UGA)的会话存储区中。这就保证了每个会话都将有其自己的包运行状态的副本。在包头包括在包体的首部声明的变量与游标的作用域为全局范围,这些变量对于具有EXECUTE 特权的任何PL/SQL块都是可见的。由于包的运行状态时在UGA中存放的,所以他们有与数据库会话相同的生命期。当包被实例时,其运行状态也得到初始化,并且这些状态直到会话结束才被释放。即使包本身由于超时被从共享缓冲区中删除,但该包的状态仍然保持。
[@more@]一、创建普通的包
CREATE OR REPLACE PACKAGE pkg_test IS
TYPE t_tbls IS TABLE OF VARCHAR2(100);
t_tbl t_tbls;
PROCEDURE up_init;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_test IS
PROCEDURE up_init IS
BEGIN
IF t_tbl IS NULL THEN
t_tbl := t_tbls();
END IF;
t_tbl.extend;
t_tbl(t_tbl.count) := t_tbl.count;
dbms_output.put_line('up_init:' || t_tbl.count);
END;
END;
/
二、包内变量的变化:
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 t_tbl pkg_test.t_tbls := pkg_test.t_tbls();
3 BEGIN
4 pkg_test.up_init;
5 t_tbl := pkg_test.t_tbl;
6 dbms_output.put_line(t_tbl.count);
7 END;
8 /
up_init:1
1
PL/SQL procedure successfully completed
SQL>
SQL> DECLARE
2 t_tbl pkg_test.t_tbls := pkg_test.t_tbls();
3 BEGIN
4 pkg_test.up_init;
5 t_tbl := pkg_test.t_tbl;
6 dbms_output.put_line(t_tbl.count);
7 END;
8 /
up_init:2
2
PL/SQL procedure successfully completed
SQL> alter system flush shared_pool;
System altered
SQL>
SQL> DECLARE
2 t_tbl pkg_test.t_tbls := pkg_test.t_tbls();
3 BEGIN
4 pkg_test.up_init;
5 t_tbl := pkg_test.t_tbl;
6 dbms_output.put_line(t_tbl.count);
7 END;
8 /
up_init:3
3
PL/SQL procedure successfully completed
SQL>
SQL> disc
Not logged on
SQL> conn dbo/dbo
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Connected as DBO
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 t_tbl pkg_test.t_tbls := pkg_test.t_tbls();
3 BEGIN
4 pkg_test.up_init;
5 t_tbl := pkg_test.t_tbl;
6 dbms_output.put_line(t_tbl.count);
7 END;
8 /
up_init:1
1
PL/SQL procedure successfully completed
SQL>
经过以上测试发现,就算是清除共享区,包中的变量什依然没有被重置,只有会话断掉才会被重置。
三、创建PRAGMA SERIALLY_REUSABLE包
CREATE OR REPLACE PACKAGE pkg_test IS PRAGMA SERIALLY_REUSABLE;
TYPE t_tbls IS TABLE OF VARCHAR2(100);
t_tbl t_tbls;
PROCEDURE up_init;
END;
/
CREATE OR REPLACE PACKAGE BODY pkg_test IS PRAGMA SERIALLY_REUSABLE;
PROCEDURE up_init IS
BEGIN
IF t_tbl IS NULL THEN
t_tbl := t_tbls();
END IF;
t_tbl.extend;
t_tbl(t_tbl.count) := t_tbl.count;
dbms_output.put_line('up_init:' || t_tbl.count);
END;
END;
/
四、包内变量的变化
SQL> set serveroutput on
SQL>
SQL> DECLARE
2 t_tbl pkg_test.t_tbls := pkg_test.t_tbls();
3 BEGIN
4 pkg_test.up_init;
5 t_tbl := pkg_test.t_tbl;
6 dbms_output.put_line(t_tbl.count);
7 END;
8 /
up_init:1
1
PL/SQL procedure successfully completed
SQL>
SQL> DECLARE
2 t_tbl pkg_test.t_tbls := pkg_test.t_tbls();
3 BEGIN
4 pkg_test.up_init;
5 t_tbl := pkg_test.t_tbl;
6 dbms_output.put_line(t_tbl.count);
7 END;
8 /
up_init:1
1
PL/SQL procedure successfully completed
SQL>
五、可连续重用包与非连续重用包的区别
运行状态保存在SGA中,每次数据库调用后都将该运行状态释放,所用的最大内存与包的并发用户数量成正比。后者运行状态保存在UGA中,其生存期与数据库会话相同,所用最大内存数与当前登录的用户数目成正比
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10455206/viewspace-1037204/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10455206/viewspace-1037204/