Oracle基礎 預存程序

標籤：

一、子程式

　　子程式是已命名的PL/SQL塊，它們儲存在資料庫中，可以Wie它們指定參數，可以從任何資料庫用戶端和應用程式中調用它們。子程式包括預存程序和函數。

　　子程式包括：

　　1、聲明部分：聲明部分包括類型、遊標、常量、變數、異常和嵌套子程式的聲明。這些項都是局部的，在退出後就不複存在。

　　2、可執行部分：可執行部分包括賦值、控制執行過程以及操縱ORacle資料的語句。

　　3、異常處理部分：　　異常處理部分包括例外處理常式，負責處理執行預存程序中出現的異常。

 

　　子程式的有點：

　　1、模組化：通過子程式，可以將程式分解為可管理的、明確的邏輯模組。

　　2、可重用性：子程式在建立並執行後，就可以再任意數目的應用程式中使用。

　　3、可維護性：子程式可以簡化維護操作，因為如果一個子程式受到影響，則只需修改該子程式的定義。

　　4、安全性：使用者可以設定許可權，使得訪問資料的唯一方式就是通過使用者提供的預存程序和函數。不僅可以讓資料更安全，而且可以保證它的正確性。

 

二、預存程序

　　預存程序是執行某些操作的子程式，是執行特定任務的模組。從根本上講，預存程序就是明明的PLSQL塊，它可以被賦予參數，儲存在資料庫中，然後由一個應用程式或其他PLSQL程式調用。

　　1、建立預存程序:

　　文法：

　　CREATE [OR REPLACE] PROCEDURE procedure_name

　　[(paraameter_list)]

　　{IS/AS}

　　[local_declarations]

　　BEGIN

　　　　executable_statements;

　　　　[EXCEPTION]

　　　　[exception_handlers]

　　END [procedure_name]

　　說明：

　　procedure_name：為預存程序的名字；

　　paraameter_list：參數列表，參數中可以使用in和out表示輸入和輸出參數。可選

　　AS表示其他變數聲明，IS表示顯示遊標聲明

　　local_declarations：局部聲明，可選

　　executable_statements：可執行語句

　　exception_handlers：異常處理語句，可選

　　OR REPLACE：可選。如果不包含，建立預存程序如果存在會報錯，包含存在會替換。

　　例：添加員工資訊　　

--添加員工資訊create or replace procedure add_emp(eno number,ename varchar2,job varchar2,mgr NUMBER,salary number,hiredate DATE,com NUMBER,dno number) isBEGIN  dbms_output.put_line(‘添加員工資訊‘);  INSERT INTO emp VALUES(eno,ename,job,mgr,hiredate,salary,com,dno);  EXCEPTION     WHEN OTHERS THEN      dbms_output.put_line(‘添加員工失敗‘);end add_emp;

　　注意：

　　預存程序中只宣告類型，不指定長度。

　　AS後的變數聲明以;結束。

　　

　　2、調用預存程序：　　

　　文法：

　　exec[ute] procedure_name (parameters_list);

　　說明：

　　execute：執行命令，可以縮寫為exec。

　　procedure_name：預存程序的名稱。

　　parameters_list：預存程序的參數列表。c

　　

　　調用預存程序有兩種方式，命令列方式和PL/SQL方式。

　　1）命令列方式：開啟命令列直接輸入預存程序名稱進行調用。　　

EXEC ADD_EMP(8888,‘zhangsan‘,‘clerk‘,7902,2000,SYSDATE,500,30);

 

　　2）PLSQL方式：必須在pl/sql塊中調用預存程序，不使用EXEC關鍵字，直接預存程序名稱即可。

BEGIN    --PL/SQL方式，不需要使用exec    add_emp(8989,‘LISI‘,‘clerk‘,7902,1000,SYSDATE,NULL,30);END;

 

　　參數的傳遞方式：

　　1）按位置傳遞：

　　按照參數的書序，依次寫入參數內容。調用的參數順序和定義的參數順序必須一一對應。　　

EXEC ADD_EMP(8888,‘zhangsan‘,‘clerk‘,7902,2000,SYSDATE,500,30);

 

　　2）按名稱傳遞：

　　按名稱調用時按名稱對應，名稱對應的關係最重要，次序不重要。　　

EXEC add_emp(ENO => 8989,ENAME => ‘LISI‘,JOB => ‘clerk‘,MGR => 7902,SALARY => 1000,HIREDATE => SYSDATE,COM => NULL,DNO => 30);

　　

　　3）混合方式傳遞：

　　同時使用位置傳遞和參數傳遞，採用這種方式必須將位置參數放在名稱參數的前面，只要第一個採用了名稱傳遞法，湖面所有的參數必須使用名稱傳遞法。　　

EXEC add_emp(8989,‘LISI‘,JOB => ‘clerk‘,MGR => 7902,SALARY => 1000,HIREDATE => SYSDATE,COM => NULL,DNO => 30);

 

　　

　　3、預存程序的參數模式：

　　預存程序參數有三種模式：IN、OUT和IN OUT，即輸入、輸出、輸入/輸出。

　　定義預存程序參數文法：

　　parameter_name [IN|OUT|IN OUT] dateType [{:= | default} expression]

　　注意：

　　1）參數IN模式是預設模式。如果未指定參數模式，則預設為IN。對於OUT和IN OUT參數，必須明確指定。

　　2）可以再參數列表中為IN參數指定預設值，OUT和IN OUT不可用。

　　帶OUT參數的預存程序：

　　例：根據empno查詢員工資訊返回。　　

CREATE OR REPLACE PROCEDURE QUERY_EMP(  V_EMPNO IN EMP.EMPNO%TYPE,       --輸入參數  V_ENAME OUT EMP.ENAME%TYPE,      --輸出參數  V_SAL   OUT EMP.SAL%TYPE) IS     --輸出參數BEGIN  SELECT ename,sal INTO v_ename,v_sal FROM emp  WHERE empno = v_empno;  dbms_output.put_line(‘資料已查到‘);END QUERY_EMP;

　　調用帶輸出參數的預存程序，首先定義兩個變數儲存輸出參數返回的內容。　　

DECLARE   v_name emp.ename%TYPE;  v_sal emp.sal%TYPE;BEGIN  query_emp(7788,v_name,v_sal);  dbms_output.put_line(‘name:‘||v_name||‘  sal:‘||v_sal);END;

 

　　帶IN OUT參數的預存程序：

　　資料交換;

CREATE OR REPLACE PROCEDURE swap(  v_num1 IN OUT NUMBER,  v_num2 IN OUT NUMBER)AS  v_temp NUMBER;BEGIN  v_temp := v_num1;  v_num1 := v_num2;  v_num2 := v_temp;END;

--調用DECLARE  v_n1 NUMBER := 10;  v_n2 NUMBER := 20;BEGIN  swap(v_n1,v_n2);  dbms_output.put_line(v_n1||‘    ‘||v_n2);END;

 

　　 4、預存程序的存取權限

　　預存程序建立後，只有建立該預存程序的使用者和管理員才能有權執行。其他使用者如果要調用該預存程序，需要得到預存程序的EXECUTE許可權。

　　例：

--將swap的執行許可權授予user1GRANT EXECUTE ON swap TO user1;   --將swap的執行許可權授予user1，並且user1可以對其他使用者進行授權。GRANT EXECUTE ON swap TO user1 WITH GRANT OPTION;  --撤銷user1使用者的執行swap許可權。REVOKE EXECUTE ON swap FROM user1   

 

　　5、刪除預存程序　　

DROP procedure swap;

 

　　

　　

Oracle基礎 預存程序