ORACLE PL/SQL子程式–過程和函數學習筆記

溫故而知新，果然如此呀，第二次再翻開同樣的內容果然有不同的收穫，有些是第一次看的時候沒有仔細理解的，還有些可能是在第一次看匆匆就跳過的，當然，可能還有部分是自己當時記住了完了又給忘記了。今天第二次看到子程式這一章節，發現了些新的內容，呵呵。在這裡我就寫下一些基本內容和容易忘記的，免得下次又給忘了。內容可能不太全面，有點針對我個人哦，呵呵！

 

1.建立子程式的文法

建立過程

CREATE [OR REPLACE] PROCEDURE procedure_name</p><p>[(argument [{IN | OUT| IN OUT}] type,</p><p> ...</p><p> argument [{IN | OUT| IN OUT}] type)]</p><p>{IS | AS} --IS和AS沒區別</p><p>procedure_body<br />

 

建立函數

 

CREATE [OR REPLACE] FUNCTION function_name</p><p>[(argument [{IN | OUT| IN OUT}] type,</p><p> ...</p><p> argument [{IN | OUT| IN OUT}] type)]</p><p>RETURN return_type {IS | AS} --IS和AS沒區別</p><p>procedure_body</p><p>

 

2.參數模式

IN 

調用過程的時候，實際參數的值就會傳遞到該過程中。在過程內部，形式參數就像PL/SQL的常量一樣--它會被看作是唯讀且不能修改的。當過程執行完畢，程式的控制返回到調用環境的時候，實際參數的值不會有任何變化。

 

OUT

調用過程的時候，會忽略所有實際參數的值。在過程內部，形式參數就像未初始化的PL/SQL變數一樣，也會有一個NULL值。形式參數的值既可以讀取，也可以寫入。過程執行完成以後，控制就會返回到調用環境，同時會將形式參數的值賦給實際參數。

 

IN OUT

這是IN和OUT的複合模式。調用過程的時候，實際參數的值會傳遞到過程中。在過程內部，形式參數就像初始化以後的變數，可以讀取和寫入。過程結束以後，控制會返回到調用環境，同時會將形式參數的內容分配給實際參數。

 

3.以傳值和傳引用的方式傳遞參數

傳遞子程式參數的方式有兩種--傳值和傳引用。當以引用的方式傳遞參數的時候，就將指向實際參數的一個指標傳遞到相應的形式參數。另一方面，當以傳值的方式傳遞參數的時候，就將實際參數的值複製到相應的形式參數。以引用的方式傳遞參數通常會更快，因為它避免了複製。對集合類型的參數而言，這表現更加明顯，因為集合類型的資料一般都非常多。

預設情況下，PL/SQL對IN參數都使用傳引用的方式，而對IN OUT和OUT參數都使用傳值的方式。

 

1）NOCOPY的使用方法

 

parameter_name [mode] NOCOPY datatype

 

其中parameter_name是參數的名稱，mode是參數模式，而datatype是參數類型。如果有NOCOPY，PL/SQL編譯器就會嘗試通過傳引用的方式傳遞參數，而不是通過傳值方式傳遞參數。注意，NOCPY只是一個編譯器提示，而不是編譯器命令，因此，這種提示並不一定總會被接受。

 

-- This procedure demonstrates the syntax of the NOCOPY compiler<br />-- hint.<br />CREATE OR REPLACE PROCEDURE NoCopyTest (<br /> p_InParameter IN NUMBER,<br /> p_OutParameter OUT NOCOPY VARCHAR2,<br /> p_InOutParameter IN OUT NOCOPY CHAR) IS<br />BEGIN<br /> NULL;<br />END NoCopyTest;<br />/

 

在IN參數上使用NOCOPY時，會引發一個編譯錯誤，因為IN參數總是以傳引用方式傳遞參數的，因此不允許使用編譯器提示NOCOPY。

 

2）帶NOCOPY的異常語義

以傳遞引用的方式傳遞參數的時候，對形式參數所做的任何更改都會同時反應到實際參數上，因為這二者指向的是同一個位置。這也意味著，如果過程在形式參數的值發生了變化以後，又以一個未處理的異常結束，那麼實際參數的原始值也會丟失。

 

CREATE OR REPLACE PROCEDURE RaiseErrorNoCopy (<br /> p_Raise IN BOOLEAN,<br /> p_ParameterA OUT NOCOPY NUMBER) AS<br />BEGIN<br /> p_ParameterA := 7;<br /> IF p_Raise THEN<br /> RAISE DUP_VAL_ON_INDEX;<br /> ELSE<br /> RETURN;<br /> END IF;<br />END RaiseErrorNoCopy;<br />/</p><p>DECLARE<br /> v_Num NUMBER := 1;<br />BEGIN<br /> DBMS_OUTPUT.PUT_LINE('Value before first call: ' || v_Num);<br /> RaiseErrorNoCopy(FALSE, v_Num);<br /> DBMS_OUTPUT.PUT_LINE('Value after successful call: ' || v_Num);<br /> DBMS_OUTPUT.PUT_LINE('');</p><p> v_Num := 2;<br /> DBMS_OUTPUT.PUT_LINE('Value before second call: ' || v_Num);<br /> RaiseErrorNoCopy(TRUE, v_Num);<br />EXCEPTION<br /> WHEN OTHERS THEN<br /> DBMS_OUTPUT.PUT_LINE('Value after unsuccessful call: ' || v_Num);<br />END;<br />/</p><p>--結果如下<br />Value before first call:1<br />Value after successful call:7</p><p>Value before second call:2<br />Value after unsuccessful call:7<br />

 

我們可以看到，即使發生了異常，還是兩次修改了實際參數的值。

 

3）使用NOCOPY的一些限制

在某些情況下，編譯器會忽略NOCOPY的存在，參數仍然以傳值的方式進行傳遞，而且也不會產生任何錯誤。記住，NOCOPY只是一種pragma，編譯器沒有責任完全遵守這個提示。在下面幾種情形中，會忽略NOCOPY的存在：

• 實際參數是聯合數組的一個成員。但是，如果實際參數是整個數組，就不受這種約束的限制。
• 使用長度、精度或NOT NULL約束限制的實際參數。
• 實際參數和形式參數都是記錄，並且它們要麼被隱式聲明為一個迴圈變數，要麼是使用%ROWTYPE進行聲明的，而且相應欄位上的約束又不同。
• 傳遞的實際參數需要進行隱式的資料類型轉換。
• 子程式被包含在進行遠端程序呼叫（remote procedures call，RPC）中。

4.子程式的調用

如果過程或者函數沒有參數，那麼在過程聲明或程序呼叫的時候都不需要括弧了。

既然說到這裡了，那就簡單的說一下EXEC和CALL的區別吧。

a.

 

exec是sqlplus裡的命令，只能在sqlplus中執行。

b.

call是sql命令，任何工具都可以使用，使用的時候必須加括弧。

 

位置標識法和帶名標識法

 

CREATE OR REPLACE PROCEDURE CallMe (<br /> p_ParameterA VARCHAR2,<br /> p_ParameterB NUMBER,<br /> p_ParameterC BOOLEAN,<br /> p_ParameterD DATE) AS<br />BEGIN<br /> NULL;<br />END CallMe;<br />/</p><p>DECLARE<br /> v_Variable1 VARCHAR2(10);<br /> v_Variable2 NUMBER(7,6);<br /> v_Variable3 BOOLEAN;<br /> v_Variable4 DATE;<br />BEGIN<br /> CallMe(v_Variable1, v_Variable2, v_Variable3, v_Variable4);<br />END;<br />/</p><p>DECLARE<br /> v_Variable1 VARCHAR2(10);<br /> v_Variable2 NUMBER(7,6);<br /> v_Variable3 BOOLEAN;<br /> v_Variable4 DATE;<br />BEGIN<br /> CallMe(p_ParameterA => v_Variable1,<br /> p_ParameterB => v_Variable2,<br /> p_ParameterC => v_Variable3,<br /> p_ParameterD => v_Variable4);<br />END;<br />/</p><p>DECLARE<br /> v_Variable1 VARCHAR2(10);<br /> v_Variable2 NUMBER(7,6);<br /> v_Variable3 BOOLEAN;<br /> v_Variable4 DATE;<br />BEGIN<br /> CallMe(p_ParameterB => v_Variable2,<br /> p_ParameterC => v_Variable3,<br /> p_ParameterD => v_Variable4,<br /> p_ParameterA => v_Variable1);<br />END;<br />/</p><p>DECLARE<br /> v_Variable1 VARCHAR2(10);<br /> v_Variable2 NUMBER(7,6);<br /> v_Variable3 BOOLEAN;<br /> v_Variable4 DATE;<br />BEGIN<br /> -- First 2 parameters passed by position, the second 2 are<br /> -- passed by name.<br /> CallMe(v_Variable1, v_Variable2,<br /> p_ParameterC => v_Variable3,<br /> p_ParameterD => v_Variable4);<br />END;<br />/<br />

 

 

5.子程式的位置

1）內建子程式（Stored Subprogram）

當通過CREATE OR REPLACE命令建立子程式的時候，它被儲存在資料庫中。該子程式是以被編譯的形式進行儲存的，這就是p-code。

 

2）本地子程式

 

先看一個例子吧，這個例子說明了在PL/SQL塊的聲明部分聲明的本地子程式

 

DECLARE<br /> CURSOR c_AllStudents IS<br /> SELECT first_name, last_name<br /> FROM students;</p><p> v_FormattedName VARCHAR2(50);</p><p> /* Function which will return the first and last name<br /> concatenated together, separated by a space. */<br /> FUNCTION FormatName(p_FirstName IN VARCHAR2,<br /> p_LastName IN VARCHAR2)<br /> RETURN VARCHAR2 IS<br /> BEGIN<br /> RETURN p_FirstName || ' ' || p_LastName;<br /> END FormatName;</p><p>-- Begin main block.<br />BEGIN<br /> FOR v_StudentRecord IN c_AllStudents LOOP<br /> v_FormattedName :=<br /> FormatName(v_StudentRecord.first_name,<br /> v_StudentRecord.last_name);<br /> INSERT INTO temp_table (char_col)<br /> VALUES (v_FormattedName);<br /> END LOOP;</p><p> COMMIT;<br />END;<br />/

 

上面這個例子中的FormatName函數是在匿名塊的聲明部分進行聲明的。函數名是一個PL/SQL標識符，因為遵循其他PL/SQL標識符所遵循的同樣的範圍和可見度規則。

特別地，它僅在聲明所在的塊中是可見的。它的範圍從聲明所在的位置開始一直持續到該塊的結尾。其他的塊都不能調用FormatName，因為它對其他的塊來說是不可見的。

所有本地子程式都必須在聲明部分的末尾進行聲明。

 

這裡就不能不提到一個概念：

前向聲明（Forward Declaration）

 

因為本地PL/SQL子程式的名字是標識符，所以它們必須要在被引用之前進行聲明。通常，這不是一個問題。但是，對於相互交叉引用的子程式，這會出現一個問題。考慮下面這個例子

 

DECLARE<br /> v_TempVal BINARY_INTEGER := 5;</p><p> -- Local procedure A. Note that the code of A calls procedure B.<br /> PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS<br /> BEGIN<br /> IF p_Counter > 0 THEN<br /> B(p_Counter);<br /> p_Counter := p_Counter - 1;<br /> END IF;<br /> END A;</p><p> -- Local procedure B. Note that the code of B calls procedure A.<br /> PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS<br /> BEGIN<br /> p_Counter := p_Counter - 1;<br /> A(p_Counter);<br /> END B;<br />BEGIN<br /> B(v_TempVal);<br />END;<br />/<br />

 

這個例子是不可能通過編譯的。因為過程A和B互相調用。為瞭解決這個問題，我們可以使用前向聲明。這隻是一個過程名和它的形式參數，這樣就可以在程式中使用相互的交叉引用過程了。如下所示

 

DECLARE<br /> v_TempVal BINARY_INTEGER := 5;</p><p> -- Forward declaration of procedure B.<br /> PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);</p><p> PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS<br /> BEGIN<br /> IF p_Counter > 0 THEN<br /> B(p_Counter);<br /> p_Counter := p_Counter - 1;<br /> END IF;<br /> END A;</p><p> PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS<br /> BEGIN<br /> p_Counter := p_Counter - 1;<br /> A(p_Counter);<br /> END B;<br />BEGIN<br /> B(v_TempVal);<br />END;<br />/<br />

 

6.子程式授權問題

 

先來看如下代碼

CREATE OR REPLACE PROCEDURE RecordFullClasses AS<br /> CURSOR c_Classes IS<br /> SELECT department, course<br /> FROM classes;<br />BEGIN<br /> FOR v_ClassRecord IN c_Classes LOOP<br /> -- Record all classes which don't have very much room left<br /> -- in temp_table.<br /> IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN<br /> INSERT INTO temp_table (char_col) VALUES<br /> (v_ClassRecord.department || ' ' || v_ClassRecord.course ||<br /> ' is almost full!');<br /> END IF;<br /> END LOOP;<br />END RecordFullClasses;<br />/

 

假設RecordFullClasses以及其所依賴的對象（函數almostfull和表classes以及temp_table）都是由資料庫使用者USERA所擁有。如果我們把對RecordFullClasses的EXECUTE許可權使用下面的命令授予USERB資料庫使用者

 

GRANT EXECUTE ON RecordFullClasses TO USERB

 

那麼USERB可以通過下述的塊來執行RecordFullClasses

 

BEGIN

USERA.RecordFullClasses;

END;

 

現在假設USERB有另外一個表，也叫temp_table，那麼如果USERB調用USERA.RecordFullClasses，那麼哪一個表會被改變呢？答案是USERA中的表會被改變。這個概念可以這樣表述：

子程式在其擁有者的許可權的控制下執行

既然USERB調用RecordFullClasses，而RecordFullClasses由USERA所擁有。這樣標識符temp_table必須要對屬於USERA而不是屬於USERB的表進行求值。

 

還有一點值得注意的是子程式在授權時不能通過角色進行

 

GRANT SELECT ON CLASEES TO USERB;

GRANT EXECUTE ON ALMOST FULL TO USERB;

 

通過間接的角色授權則不行

CREATE ROLE USERA_ROLE;

 

 

 

GRANT EXECUTE ON ALMOST FULL TO USERA_ROLE;

GRANT USERA_ROLE TO USERB;

GRANT SELECT ON CLASEES TO USERA_ROLE;