Oracle預存程序詳解(引用）+補充(轉)

標籤：

一、過程 (預存程序)

過程是一個能執行某個特定操作的子程式。使用CREATE OR REPLACE建立或者替換儲存在資料庫中的一個子程式。
樣本1：聲明預存程序，該過程返回dept表行數

DECLAREPROCEDURE getDeptCountASdeptCount INT;BEGINSELECT COUNT(*) INTO deptCount FROM DEPT;DBMS_OUTPUT.PUT_LINE(‘DEPT表的共有記錄數：‘||deptCount);END getDeptCount;BEGINgetDeptCount[()];END;

 

注意：此預存程序getDeptCount只在塊運行時有效。
樣本2：建立不帶參數的預存程序，該過程返回dept表行數

CREATE OR REPLACE PROCEDURE getDeptCountAS | ISdeptCount int;BEGINSELECT COUNT(*) INTO deptCount FROM dept;DBMS_OUTPUT.PUT_LINE(‘dept表共有‘||deptCount||‘行記錄‘);END [getDeptCount];

 

當我們建立的預存程序沒有參數時，在預存程序名字後面不能有括弧。在AS或者IS後至BEGIN之前是聲明部分，預存程序中的聲明不使用DECLARE關鍵字。同匿名PL/SQL塊一樣，EXCEPTION和聲明部分都是可選的。
當我們建立的過程帶有錯誤時，我們可以通過SELECT * FROM USER_ERRORS查看，或者使用SHOW ERRORS [ PROCEDURE Proc_Name]查看。
使用以下代碼可以執行預存程序：

BEGINgetDeptCount;END;以上預存程序還可以通過以下代碼來簡化調用：EXEC getDeptCount[;] CALL  getDeptCount();

 

注意：

• 並不是所有的預存程序都可以用這種方式來調用
• 定義無參預存程序時，預存程序名後不能加()
• 在塊中或是通過EXEC調用預存程序時可以省略()
• 通過CALL調用無參預存程序必須加上()

樣本3：建立帶有輸入參數的預存程序，該過程通過員工編號列印工資額

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo NUMBER)  --參數的資料類型不能指定長度ASsalary emp.sal%TYPE;BEGINSELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;DBMS_OUTPUT.PUT_LINE(eNo||‘號員工的工資為‘||salary);EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(‘沒有找到該編號的員工‘);END;

 

當定義的預存程序含有參數時，參數的資料類型不能指定長度。參數還有輸入和輸出之分，本例中沒有指定，預設情況為輸入參數，也可顯示的指定某個參數是輸入參數，如(eNo IN NUMBER)。同樣本1不同，該例中加入了異常處理。同樣本1類似可以使用下面的兩種方式調用預存程序：
BEGIN
getSalaryByEmpNo(7788);
END;
或者
EXEC getSalaryByEmpNo(7788);  或者
CALL getSalaryByEmpNo(7788);
但是如果傳給一個預存程序的參數是變數時，必須使用BEGIN  END塊，如下：

DECLAREno emp.empNo%TYPE;BEGINno:=7788;getSalaryByEmpNo(no);END;

 

如果某個包中含有常量，也可以通過如下的方式調用：
EXEC getSalaryByEmpNo(ConstantPackage.no);
但這種方式不能再使用CALL調用。
樣本4：建立含有輸入和輸出參數的預存程序，該過程通過員工編號尋找工資額，工資額以輸出參數返回

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(eNo IN NUMBER,salary OUT NUMBER)ASBEGINSELECT SAL INTO salary  FROM EMP WHERE EMPNO=eNo;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(‘沒有找到該編號的員工‘);END;

 

當過程中含有輸出參數時，調用時必須通過BEGIN  END塊，不能通過EXEC或CALL調用。如：

DECLAREsalary NUMBER(7,2);BEGINgetSalaryByEmpNo(7788,salary);DBMS_OUTPUT.PUT_LINE(salary);END;

 

樣本5：建立參數類型既是輸入參數也是輸出參數的過程

CREATE OR REPLACE PROCEDURE getSalaryByEmpNo(noSalary IN OUT NUMBER)ASBEGINSELECT SAL INTO noSalary FROM EMP WHERE EMPNO=noSalary;EXCEPTIONWHEN NO_DATA_FOUND THENDBMS_OUTPUT.PUT_LINE(‘沒有找到該編號的員工‘);END;

 

調用如下：

DECLAREno NUMBER(7,2);BEGINno:=7788;getSalaryByEmpNo(no);DBMS_OUTPUT.PUT_LINE(no);END;

 

樣本6：建立帶有預設值的過程

CREATE OR REPLACE PROCEDURE addEmp(empNo NUMBER,eName VARCHAR2,job  VARCHAR2 :=‘CLERK‘,mgr  NUMBER,hiredate DATE  DEFAULT SYSDATE,sal  NUMBER  DEFAULT 1000,comm  NUMBER  DEFAULT 0,deptNo NUMBER  DEFAULT 30)ASBEGININSERT INTO emp VALUES(empNo,eName,job,mgr,hiredate,sal,comm,deptNo);END;

 

調用如下：

EXEC addEmp(7776,‘zhangsan‘,‘CODER‘,7788,‘06-1月-2000‘,2000,0,10);  --沒有使用預設值EXEC addEmp(7777,‘lisi‘,‘CODER‘,7788,‘06-1月-2000‘,2000,NULL,10);  --可以使用NULL值EXEC addEmp(7778,‘wangwu‘,mgr=>7788);  --使用預設值EXEC addEmp(mgr=>7788,empNo=>7779,eName=>‘sunliu‘);  --更改參數順序

 

樣本7：使用NOCOPY編譯提示
當參數是大型資料結構時，如集合、記錄和對象執行個體，把它們的內容全部拷貝給形參會降低執行速度，消耗大量記憶體。為了防止這樣的情況發生，我們可以使用 NOCOPY提示來讓編譯器按引用傳遞方式給IN OUT模式的參數。

DECLARETYPE DeptList IS TABLE OF VARCHAR2(10);dList  DeptList:=DeptList(‘CORESUN‘,‘CORESUN‘,‘CORESUN‘,‘CORESUN‘);PROCEDURE My_Proc(d IN OUT NOCOPY DeptList)AS...

 

注意：NOCOPY只是一個提示，而不是指令。即使有時候我們使用了NOCOPY,但編譯器有可能仍然會進行值拷貝。通常情況下NOCOPY是可以成功的。

二、維護過程

1、刪除預存程序

DROP PROCEDURE Proc_Name;

2、查看過程狀態

SELECT object_name,status  FROM USER_OBJECTS WHERE object_type=‘PROCEDURE‘;

 

3、重新編譯過程

ALTER PROCEDURE Proc_Name COMPILE;

 

4、查看過程代碼

SELECT * FROM USER_SOURCE WHERE TYPE=‘PROCEDURE‘;

 

三、參數的理解

-- 輸出參數不可以修改解決的方法有兩種
--1 把參數改成輸入參數 
--2 就是參數改成 可輸入輸出的參數;
調用過程的 三個方式
1 就是使用call
在只用call方式調用函數的時候,必須加要括弧,有參數,還要加參數值

這個方式在命令視窗,調用過程,將不會出現輸入的資料.
2 就是使用exec 命令,進行命令調用過程, 使用命令,就必須在命令列裡面輸入
過程名,這個命令視窗中,可加可不加() ,如果有參數的,就一定要加,還有參數值,參數值的類型要與
變數類型相同.
3 在語句塊中進行調用過程,這個方式和命令模式類似,他們都是可要可不要(),
-- 在2 和 3 中的 沒有括弧的情況是,過程沒有參數 ,如果有,就必須要有()
輸出參數的特點
1 一個過程中,如果有輸出參數(OUT 參數),在調用過程的使用,也要傳入一個參數, 這個參數可以不用在調用的地方
進行賦值,就直接傳入一個聲明好的一個變數,用來接受預存程序中的輸出參數的值(OUT 參數)
2 輸入參數 值不可以改變在過程中,
注意: 在預存程序中,他的參數類型不可以設定它的大小 ; 
例如;

CREATE OR REPLACE PROCEDURE hello(p_name IN VARCHAR2(12),p_age OUT NUMBER(10,2))ISBEGIN 

如果有輸出參數就必須有有一個參數進行接收 ;

CREATE OR REPLACE PROCEDURE hello(p_name IN VARCHAR2,p_age OUT emp.sal%TYPE)ISBEGINSELECT emp.sal + 3131 INTO p_age FROM emp WHERE empno = 7788 ;dbms_output.put_line( p_age);END ;

--------- 塊中調用方法

DECLAREv_nanme varchar2(12);v_age NUMBER (12,2);BEGINhello (v_nanme,v_age);dbms_output.put_line(v_age);END ;

-- 在這個過程中 傳入的v_age 就是接受 預存程序輸出參數的值 ; 類似於Java的中的傳回值

-- 理解 in out 參數

CREATE OR REPLACE PROCEDURE hello1 (p_name IN OUT emp.ename%TYPE)ISBEGIN-- SELECT emp.ename INTO p_name FROM emp ;p_name:=‘a;sk , ‘ || p_name ;END ;--------------------------------------------------------------------------DECLAREv_nanme varchar2(12);BEGIN v_nanme:=‘12312‘;hello1(v_nanme);

補充：sqlplus中執行含有輸出參數為遊標的預存程序

執行個體6：

sqlplus建立預存程序，使用如下：

SQL>create or replace procedure test1(rcursor out sys_refcursor) asbegin  open rcursor for  select decode(row_number() over(partition by deptno order by ename),                  1,                   deptno,                   null) deptno,             t.ename        from scott.emp t;end;/

--使用sqlplus執行上面建立的帶有遊標輸出參數的預存程序

SQL> var cur refcursorSQL> exec test1(:cur);PL/SQL procedure successfully completed.SQL> print cur;    DEPTNO ENAME---------- ----------        10 CLARK           KING           MILLER        20 ADAMS           FORD           JONES           SCOTT           SMITH        30 ALLEN           BLAKE           JAMES    DEPTNO ENAME---------- ----------           MARTIN           TURNER           WARD14 rows selected.

Oracle預存程序詳解(引用）+補充(轉)