PL/SQL學習筆記-5

PL/SQL學習筆記-5

第10章

[@more@]

第10章 使用過程、函式和包
10.1 子程式位置
當用CREATE OR REPLACE建立子程式時，該子程式被儲存到資料字典中。除源文字外，子程式以編譯形式儲存，這叫做p-code(偽碼)。
Oracle9i允許你把一個子程式編譯成本地作業系統程式碼，而非虛擬碼。區別在於執行的時候虛擬碼任然需要被解釋，原生程式碼不需要，所以執行速度快。但是虛擬碼可以在不同的平臺下執行。具體相關內容參考oracle文件。
user_objects 包含當前使用者所有的物件的資訊
user_souce 包含原始程式碼

區域性子程式
* 區域性子程式可以被宣告為儲存子程式宣告段的一部分。
* 任何區域性子程式必須在宣告段的最後進行宣告。
DECLARE
...
FUNCTION ...
END
BEGIN
...
END;
* 預先宣告，子程式可以預先宣告
DELCARE
FUNCTION ...;
...
FUNCTION ... IS
...
END
BEGIN
...

區域性子程式一般用在避免單個語句塊中的程式碼重複。

可以使用DBMS_SHARED_POOL.KEEP 包過程把已編譯虛擬碼鎖定在共享池。

10.2 儲存子程式和包的考慮事項
子程式依賴性，當子程式中的表或引用的其他子程式有變動的時候，這個子程式就變為無效。引用該子程式的子程式也會無效。
但是如果引用的子程式是遠端的話，則當子程式改變時，當前程式不會變成無效的。
包有特例，包體中的函式或過程的改變，不會影響包頭(除非需要改動包頭)。所以引用包頭的子程式不會受到影響。
決定一個子程式是否需要重新編譯，有2中方法： 時間戳 和 簽名。
將引數 REMOTE_DEPENDENCIES_MODE引數設定為 SIGNATURE。 可以使用簽名模式。
ALTER PROCEDURE procedure_name COMPILE
ALTER FUNCTION function_name COMPILE
ALTER PACKAGE package_name COMPLIE
ALTER PACKAGE package_name COMPLIE SPECIFICATION
ALTER PACKAGE package_name COMPLIE BODY

在包頭宣告的變數具有全域性範圍，包的狀態一直保持到會話結束。
仔細看下面的例子，區分可序列復用的包和不可序列復用的包的區別。pragma serially_reusable
-----------------------------------------------------------------------------------------------
SQL> create or replace package test_pack as
2 procedure test1;
3 end test_pack;
4 /

Package created
SQL> create or replace package body test_pack as
2 num int := 1;
3 procedure test1 is
4 begin
5 num:=num+1;
6 dbms_output.put_line(num);
7 end test1;
8 end test_pack;
9 /

Package body created

SQL> exec test_pack.test1;
2

PL/SQL procedure successfully completed

SQL> exec test_pack.test1;
3

PL/SQL procedure successfully completed

SQL> exec test_pack.test1;
4

PL/SQL procedure successfully completed

SQL>

SQL> create or replace package test_pack as
2 pragma serially_reusable;
3 procedure test1;
4 end test_pack;
5 /

Package created

SQL> create or replace package body test_pack as
2 pragma serially_reusable;
3 num int := 1;
4 procedure test1 is
5 begin
6 num:=num+1;
7 dbms_output.put_line(num);
8 end test1;
9 end test_pack;
10 /

Package body created

SQL> exec test_pack.test1;
2

PL/SQL procedure successfully completed

SQL> exec test_pack.test1;
2

PL/SQL procedure successfully completed

-----------------------------------------------------------------------------------------------
可序列復用的包： 執行時狀態儲存在共享儲存器中，並在每一次資料庫呼叫後釋放 使用的最大儲存器與同時使用該包的使用者數成正比。
不可序列復用的包：執行時狀態儲存在過程儲存器中，並在整個資料庫會話中持續 使用的最大儲存器與同時登陸的使用者數成正比，儲存器一般會高得多。

子程式使用顯示地(而不是透過角色)授予其所有者的特權而執行。
因為PL/SQL使用早期繫結，但是由於角色的不確定性，所以在儲存過程、函式、包和觸發器內部，所有角色都被禁止。

CREATE [OR REPLACE] [FUNCTION|PROCEDURE|PACKAGE] ...
...
[AUTHID {CURRENT_USER|DEFINER}] {IS|AS}
如果宣告 CURRENT_USER 則物件將擁有呼叫者的許可權。如果指定 DEFINER，那麼物件將擁有定義者的許可權。

------------------------------------------------------------------------------------------------------------
SQL> create or replace procedure p_test2
2 authid current_user as
3 begin
4 dbms_output.put_line('this is p_test2');
5 end;
6 /

Procedure created

SQL> create or replace procedure p_test1
2 authid current_user as
3 begin
4 p_test2;
5 insert into ipnet.t_test values(1);
6 dbms_output.put_line('this is p_test1');
7 end;
8 /

Procedure created

SQL> exec ipnet.p_test1;
BEGIN ipnet.p_test1; END;

*
ERROR 位於第 1 行:
ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必須說明識別符號 'IPNET.P_TEST1'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored

ipnet使用者賦execute p_test1 的許可權給 ipnet1 使用者：
SQL> grant execute on p_test1 to ipnet1;

Grant succeeded

ipnet1使用者再次執行p_test1：
SQL> exec ipnet.p_test1;
BEGIN ipnet.p_test1; END;

*
ERROR 位於第 1 行:
ORA-00942: 表或檢視不存在
ORA-06512: 在"IPNET.P_TEST1", line 5
ORA-06512: 在line 1

ipnet使用者賦 insert on t_test1 的許可權給 ipnet1 使用者：
SQL> grant insert on t_test to ipnet1;

Grant succeeded

ipnet1使用者再次執行p_test1：
SQL> exec ipnet.p_test1;
this is p_test2
this is p_test1

PL/SQL 過程已成功完成。

可以看到 對於 p_test1 和 t_test 的許可權必須付給ipnet1使用者，但是 p_test2 的 execute 許可權卻不需要付給ipnet1使用者。
------------------------------------------------------------------------------------------------------------

10.3 在SQL語句中使用儲存函式
函式有四個不同的純度等級。
WNDS 不寫資料庫狀態 該函式不修改任何資料庫表(使用DML語句)
RNDS 不讀資料庫狀態 該函式不讀取任何資料庫表(使用SELECT語句)
WNPS 不寫包狀態 該函式不修改任何封裝變數(在賦值語句左邊或在FETCH語句中不使用封裝變數)
RNPS 不讀包狀態 該函式不檢查任何封裝變數(封裝變數不在賦值語句右邊出現，也不能作為過程或SQL表示式的一部分)

PRAGMA RESTRICT_REFERENCES(subprogram_or_package_name,WNDS[,WNPS][,RNDS][,RNPS])
在8i以前，打包函式需要編譯指示。8i之後，資料庫將在執行時驗證函式的純度等級。


CREATE OR REPLACE PACKAGE student as
FUNCTION FullName(p_studentid IN students.ID%TYPE) RETURN VARCHAR2;
PRAGMA RESTRICT_REFERENCES(FullName,WNDS,WNPS,RNPS);
...
END ;

DEFAULT 關鍵字
在SQL中呼叫函式時，必須輸入所有的引數，不能使用預設引數方式。

在Oracle8i之前，從DML語句中呼叫的函式不能更新資料庫，但是8i之後可以，只要不是修改該DML語句修改的物件表。


10.4 在共享池中的鎖定
DBMS_SHARED_POOL
.KEEP(name VARCHAR2,flag CHAR) 用來在共享池中鎖定物件。包、觸發器、序列、物件型別和Java物件。
name 物件的名稱
flag P-包、函式或過程 Q-序列 R-觸發器 T-物件型別
JS-java原始碼 JC-java類 JR-java資源 JD-java共享資料
C-SQL遊標
.UNKEEP(name VARCHAR2,flag CHAR) 作用和KEEP相反，引數含義一樣。
.SIZE(minsize NUMBER) 大小大於minsize的物件將被返回。
.ABORTED_REQUEST_THRESHOLD(threshold_size NUMBER)
一旦掉用，Oracle將不會開始從池中釋放物件，除非至少需要threshold_size個位元組。