儲存過程學習教材

26.1 建立做試驗的表及資料

create table emp_ly as

select * from emp;

說明：注意複製表結構及資料不是sql server裡面的語法select * into emp_ly from emp，這在oracle裡面行不通，當然如果僅複製結構的話使用

create table emp_ly as

select * from emp

where 1<>1;

即可。

26.2建立一個儲存過程用來做試驗

create or replace procedure Sal_raise(emp_no number,shuliang number)

as

begin

update emp_ly set sal=sal + shuliang where empno=emp_no;

end;

/

說明：一定不能少begin end;這兩個關鍵字，不然建立儲存過程會有提示

Warning: Procedure created with compilation errors

意思是：警告，儲存過程雖然建立了，但是有編譯錯誤。

檢視執行錯誤可以輸入 SHOW ERRORS;

儲存過程的宣告引數型別時不能帶長度，精度或者範圍，比如這個儲存過程emp_no number這個引數，就不能宣告為emp_no number(4)，同理，varchar2作為引數的型別是可以的，但是以varchar2(10)作為引數型別是不行的。

在sql plus裡面建立儲存過程，最後一個反斜線不能少，這是告訴編譯器執行這個PL/SQL塊，但是在pl/sql developer的SQL Window建立則不需要這個反斜線，選擇輸入的程式碼按F8鍵執行即可，缺點是編譯出錯不會提示。

26.3 呼叫儲存過程

A．在儲存過程或者觸發器中呼叫儲存過程

使用儲存過程名和引數即可，比如Sal_raise(7369,200);

 

B. 從互動式oracle工具中呼叫儲存過程

在sql plus中呼叫，可以使用匿名塊的方式呼叫，如

begin

Sal_raise(7369,200);

end;

/

還可以使用EXECUTE或者CALL的語法呼叫，如

execute Sal_raise(7369,200);

call Sal_raise(7369,200);

 

值得注意的是，在sql plus中執行完了，如果沒有輸入明確的commit;命令資料是不會實際寫到表的，但是在SQL Window視窗中只能通過

begin

Sal_raise(emp_no=>7369,shuliang=>200);

end;

方式呼叫儲存過程，而且執行結果不需要輸入commit;命令也會直接寫入表。

 

26.4傳入儲存過程引數的三種方法：

Execute Sal_raise(7369,200);   

Execute Sal_raise(shuliang=>200, emp_no=>7369);

execute Sal_raise(7369,shuliang=>200);

第一種為常用方法，傳入引數順序和儲存過程宣告引數順序一樣

第二種為引數名稱和引數值一一對應法，可以不按照儲存過程宣告引數順序傳入。

第三種為混合法，要求沒有引數名稱的值要寫在前面，還需要按照宣告引數的順序才行，比如execute sal_raise(200,emp_no=>7369);就是不行的。

 

26.5 檢視儲存過程程式碼

如果檢視儲存過程的所有者，建立時間，是否有效等資訊可以使用如下語句：

select * from all_objects where object_name='SAL_RAISE';

如果要檢視儲存過程的程式碼，則可以使用如下程式碼：

select text from all_source where name='SAL_RAISE' order by line;

請一定注意的是：上面著兩個sql要有記錄返回，一定要將儲存過程的名字全部用大寫，否則會沒有記錄返回。

 

26.6 帶輸出引數的儲存過程

稍微改造下成為帶輸出引數的儲存過程：

create or replace procedure Sal_raise(emp_no number,shuliang number,sal_now out number)

as

begin

update emp_ly set sal=sal + shuliang where empno=emp_no;

select sal into sal_now from emp_ly where empno=emp_no;

end;

 

呼叫方法和不帶輸出引數的儲存過程差不多，只不過需要宣告一個合適型別的變數來接收輸出引數而已。

set serveroutput on; --主要用於後面的DBMS_OUTPUT要顯示結果

declare sal_now number;

begin

Sal_raise(7369,200,sal_now);

DBMS_OUTPUT.PUT_LINE(TO_CHAR(sal_now));

end;

 

26.5 刪除儲存過程

DROP PROCEDURE Sal_raise

參考文件：

1． Application Developer’s Guide – FundamentalsSQL Reference

2．SQL Reference

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22969361/viewspace-1200813/，如需轉載，請註明出處，否則將追究法律責任。