oracle 存儲過程

創建存儲過程，需要有CREATE PROCEDURE或CREATE ANY PROCEDURE的係統權限。該權限可由係統管理員授予。創建一個存儲過程的基本語句如下：

CREATE [OR REPLACE] PROCEDURE 存儲過程名[(參數[IN|OUT|IN OUT] 數據類型...)] 
{AS|IS} 
[說明部分] 
BEGIN 
可執行部分 
[EXCEPTION 
錯誤處理部分] 
END [過程名];

其中：

可選關鍵字ORREPLACE 表示如果存儲過程已經存在，則用新的存儲過程覆蓋，通常用於存儲過程的重建。

參數部分用於定義多個參數(如果沒有參數，就可以省略)。參數有三種形式：IN、OUT和IN OUT。如果沒有指明參數的形式，則默認為IN。
關鍵字AS也可以寫成IS，後跟過程的說明部分，可以在此定義過程的局部變量。

例一：

創建一個顯示雇員總人數的存儲過程。

CREATE OR REPLACE PROCEDURE EMP_COUNT
AS
V_TOTAL NUMBER(10);
BEGIN
 SELECT COUNT(*) INTO V_TOTAL FROM EMP;
 DBMS_OUTPUT.PUT_LINE('¹ÍÔ±×ÜÈËÊýÎª£º'||V_TOTAL);
END;

測試：

step1：在PlSql中找到Procedures，右擊，如下圖：

step2：單擊Test，進入下圖：

step3：單擊執行，在DBMS Output中可以看到執行結果，如下圖：

說明：

在該例子中，V_TOTAL變量是存儲過程定義的局部變量，用於接收查詢到的雇員總人數。
注意：

如果在存儲過程中引用了其他用戶的對象，比如表，則必須有其他用戶授予的對象訪問權限。一個存儲過程一旦編譯成功，就可以由其他用戶或程序來引用。但存儲過程或函數的所有者必須授予其他用戶執行該過程的權限。
存儲過程沒有參數，在調用時，直接寫過程名即可。

例二：

編寫顯示雇員信息的存儲過程EMP_LIST，並引用EMP_COUNT存儲過程。

	CREATE OR REPLACE PROCEDURE EMP_LIST   
	        AS  
	         CURSOR emp_cursor IS    
	        SELECT empno,ename FROM emp;   
	        BEGIN  
	FOR Emp_record IN emp_cursor LOOP      
	    DBMS_OUTPUT.PUT_LINE(Emp_record.empno||Emp_record.ename);   
	        END LOOP;   
	        EMP_COUNT;   
	        END;

測試過程與例一一樣，結果如下：

說明：

以上的EMP_LIST存儲過程中定義並使用了遊標，用來循環顯示所有雇員的信息。然後調用已經成功編譯的存儲過程EMP_COUNT，用來附加顯示雇員總人數。通過EXECUTE命令來執行EMP_LIST存儲過程。

參數傳遞
參數的作用是向存儲過程傳遞數據，或從存儲過程獲得返回結果。正確的使用參數可以大大增加存儲過程的靈活性和通用性。
參數的類型有三種，如下所示。

IN  定義一個輸入參數變量，用於傳遞參數給存儲過程  
OUT 定義一個輸出參數變量，用於從存儲過程獲取數據  
IN OUT  定義一個輸入、輸出參數變量，兼有以上兩者的功能

參數的定義形式和作用如下：
參數名 IN 數據類型 DEFAULT 值；
  定義一個輸入參數變量，用於傳遞參數給存儲過程。在調用存儲過程時，主程序的實際參數可以是常量、有值變量或表達式等。DEFAULT 關鍵字為可選項，用來設定參數的默認值。如果在調用存儲過程時不指明參數，則參數變量取默認值。在存儲過程中，輸入變量接收主程序傳遞的值，但不能對其進行賦值。
參數名 OUT 數據類型；
  定義一個輸出參數變量，用於從存儲過程獲取數據，即變量從存儲過程中返回值給主程序。
在調用存儲過程時，主程序的實際參數隻能是一個變量，而不能是常量或表達式。在存儲過程中，參數變量隻能被賦值而不能將其用於賦值，在存儲過程中必須給輸出變量至少賦值一次。
參數名 IN OUT 數據類型 DEFAULT 值；
  定義一個輸入、輸出參數變量，兼有以上兩者的功能。在調用存儲過程時，主程序的實際參數隻能是一個變量，而不能是常量或表達式。DEFAULT 關鍵字為可選項，用來設定參數的默認值。在存儲過程中，變量接收主程序傳遞的值，同時可以參加賦值運算，也可以對其進行賦值。在存儲過程中必須給變量至少賦值一次。
  如果省略IN、OUT或IN OUT，則默認模式是IN。

例一：

編寫給雇員增加工資的存儲過程CHANGE_SALARY，通過IN類型的參數傳遞要增加工資的雇員編號和增加的工資額。

CREATE OR REPLACE PROCEDURE CHANGE_SALARY(P_EMPNO IN NUMBER DEFAULT 7788,P_RAISE NUMBER DEFAULT 10)
		AS
		 V_ENAME VARCHAR2(10);
V_SAL NUMBER(5);
		BEGIN
 		SELECT ENAME,SAL INTO V_ENAME,V_SAL FROM EMP WHERE EMPNO=P_EMPNO;
		 UPDATE EMP SET SAL=SAL+P_RAISE WHERE EMPNO=P_EMPNO;
		 DBMS_OUTPUT.PUT_LINE('雇員'||V_ENAME||'的工資被改為'||TO_CHAR(V_SAL+P_RAISE));
COMMIT;
		EXCEPTION
		 WHEN OTHERS THEN
 		DBMS_OUTPUT.PUT_LINE('發生錯誤，修改失敗！');
 		ROLLBACK;
		END;

測試過程如下：

說明：從執行結果可以看到，雇員SCOTT的工資已由原來的3000改為3080。

例二：

使用OUT類型的參數返回存儲過程的結果。

CREATE OR REPLACE PROCEDURE EMP_COUNT(P_TOTAL OUT NUMBER)
		AS
		BEGIN
		SELECT COUNT(*) INTO P_TOTAL FROM EMP;
		END;

測試結果如下：

例三：

使用IN OUT類型的參數，給電話號碼增加區碼。

CREATE OR REPLACE PROCEDURE ADD_REGION(P_HPONE_NUM IN OUT VARCHAR2)
		AS
		BEGIN
		 P_HPONE_NUM:='0755-'||P_HPONE_NUM;
		END;

測試結果如下：

本文例子代碼，來自於：點擊打開鏈接

最後更新：2017-04-03 12:54:44

oracle 存儲過程

上一篇： uva 10635 - Prince and Princess LCS

下一篇： oracle 存儲過程

相關內容

熱門內容

最新內容