ORACLE存儲過程簡明教程

2024-08-29 13:58:01

字體：大中小

供稿：網(wǎng)友

本教程為我參考文檔結(jié)合實踐所寫，原創(chuàng)。

26.1 創(chuàng)建做試驗的表及數(shù)據(jù)

create table emp_ly as

select * from emp;

說明：注意復制表結(jié)構及數(shù)據(jù)不是sql server里面的語法select * into emp_ly from emp，這在oracle里面行不通，當然如果僅復制結(jié)構的話使用

create table emp_ly as

select * from emp

where 1<>1;

即可。

26.2創(chuàng)建一個存儲過程用來做試驗

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;這兩個關鍵字，不然創(chuàng)建存儲過程會有提示

Warning: Procedure created with compilation errors

意思是：警告，存儲過程雖然創(chuàng)建了，但是有編譯錯誤。

查看運行錯誤可以輸入 SHOW ERRORS;

存儲過程的聲明參數(shù)類型時不能帶長度，精度或者范圍，比如這個存儲過程emp_no number這個參數(shù)，就不能聲明為emp_no number(4)，同理，varchar2作為參數(shù)的類型是可以的，但是以varchar2(10)作為參數(shù)類型是不行的。

在sql plus里面創(chuàng)建存儲過程，最后一個反斜線不能少，這是告訴編譯器運行這個PL/SQL塊，但是在pl/sql developer的SQL Window創(chuàng)建則不需要這個反斜線，選擇輸入的代碼按F8鍵執(zhí)行即可，缺點是編譯出錯不會提示。

26.3 調(diào)用存儲過程

A．在存儲過程或者觸發(fā)器中調(diào)用存儲過程

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

B. 從交互式oracle工具中調(diào)用存儲過程

在sql plus中調(diào)用，可以使用匿名塊的方式調(diào)用，如

begin

Sal_raise(7369,200);

end;

/

還可以使用EXECUTE或者CALL的語法調(diào)用，如

execute Sal_raise(7369,200);

call Sal_raise(7369,200);

值得注意的是，在sql plus中執(zhí)行完了，如果沒有輸入明確的commit;命令數(shù)據(jù)是不會實際寫到表的，但是在SQL Window窗口中只能通過

begin

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

end;

方式調(diào)用存儲過程，而且執(zhí)行結(jié)果不需要輸入commit;命令也會直接寫入表。

26.4傳入存儲過程參數(shù)的三種方法：

Execute Sal_raise(7369,200);

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

execute Sal_raise(7369,shuliang=>200);

第一種為常用方法，傳入?yún)?shù)順序和存儲過程聲明參數(shù)順序一樣

第二種為參數(shù)名稱和參數(shù)值一一對應法，可以不按照存儲過程聲明參數(shù)順序傳入。

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

26.5 查看存儲過程代碼

如果查看存儲過程的所有者，創(chuàng)建時間，是否有效等信息可以使用如下語句：

select * from all_objects where object_name='SAL_RAISE';

如果要查看存儲過程的代碼，則可以使用如下代碼：

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

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

26.6 帶輸出參數(shù)的存儲過程

稍微改造下成為帶輸出參數(shù)的存儲過程：

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;

調(diào)用方法和不帶輸出參數(shù)的存儲過程差不多，只不過需要聲明一個合適類型的變量來接收輸出參數(shù)而已。

set serveroutput on; --主要用于后面的DBMS_OUTPUT要顯示結(jié)果

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用戶權限管理使用詳解