ORACLE存儲過程簡明教程

2024-08-29 13:58:01

字體：大中小

來源：轉載

供稿：網友

本教程為我參考文檔結合實踐所寫，原創。

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

上一篇：Oracle 11g安裝圖解 Oracle 11g安裝教程

下一篇：oracle用戶權限管理使用詳解