PRAGMA SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE

ZWF.YUDONG>CREATE OR REPLACE PACKAGE pkg_test
AS
PROCEDURE p_test;
END pkg_test;
/ 2 3 4 5

Package created.

ZWF.YUDONG>CREATE OR REPLACE PACKAGE BODY pkg_test
2 AS
3 /* Private package data */
4 TYPE t_rec IS RECORD (
5 id VARCHAR2 (50),
6 mc VARCHAR2 (50)
7 );
8
9 TYPE t_tab_type IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
10 v_tab t_tab_type;
11 i BINARY_INTEGER;
12 PROCEDURE p_test
13 AS
14 BEGIN
15 i := v_tab.COUNT + 1;
16 v_tab (i).id := '0001';
17 v_tab (i).mc := 'test';
18 DBMS_OUTPUT.put_line ('v_tab.count: ' || v_tab.COUNT);
19 END p_test;
20 END pkg_test;
21 /

Package body created.

ZWF.YUDONG>set serverout on

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 2

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 3

PL/SQL procedure successfully completed.

ZWF.YUDONG>disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ZWF.YUDONG>conn zwf/zwf
Connected.

1 row selected.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 2

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 3

PL/SQL procedure successfully completed.

v_tab是一個包級的變數，多次執行後，其值會發生永續性的變化。一旦斷開並重新連線，它的值才被重新初始化。如果我們使用了 SERIALLY_REUSABLE pragma，每次呼叫包都會先初始化變數

ZWF.YUDONG>CREATE OR REPLACE PACKAGE pkg_test
AS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE p_test;
END pkg_test;
/ 2 3 4 5 6

Package created.

ZWF.YUDONG>CREATE OR REPLACE PACKAGE BODY pkg_test
2 AS
3 PRAGMA SERIALLY_REUSABLE;
4 /* Private package data */
5 TYPE t_rec IS RECORD (
6 id VARCHAR2 (50),
7 mc VARCHAR2 (50)
8 );
9
10 TYPE t_tab_type IS TABLE OF t_rec INDEX BY BINARY_INTEGER;
11 v_tab t_tab_type;
12 i BINARY_INTEGER;
13 PROCEDURE p_test
14 AS
15 BEGIN
16 i := v_tab.COUNT + 1;
17 v_tab (i).id := '0001';
18 v_tab (i).mc := 'test';
19 DBMS_OUTPUT.put_line ('v_tab.count: ' || v_tab.COUNT);
20 END p_test;
21 END pkg_test;
22 /

Package body created.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

ZWF.YUDONG>disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ZWF.YUDONG>conn zwf/zwf
Connected.

1 row selected.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

ZWF.YUDONG>exec pkg_test.p_test;
v_tab.count: 1

PL/SQL procedure successfully completed.

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242500/，如需轉載，請註明出處，否則將追究法律責任。