Oracle存儲過程

概念存儲子程式套用子程式存儲位置 存儲在資料庫中存儲在應用程式中調用方式任何資料庫工具或套用中都可以調用只用在子程式建立的套用中才能調用相互調用 不可以調用套用子程式可以調用存儲子程式建立子程式的文檔存儲的位置

基本介紹

• 中文名：Oracle存儲過程
• 外文名：Oracle Stored procedure
• 涉及技術：Oracle資料庫技術

對比應用程式

存儲在資料庫的數據字典中
存儲在當前的套用中安全性由資料庫提供安全保證，必須通過授權才能使用存儲子程式
安全性靠應用程式來保證，如果能執行應用程式，就能執行該子程式。

概念	存儲子程式	套用子程式
存儲位置	存儲在資料庫中	存儲在應用程式中
調用方式	任何資料庫工具或套用中都可以調用	只用在子程式建立的套用中才能調用
相互調用	不可以調用套用子程式	可以調用存儲子程式
建立子程式的文檔存儲的位置	存儲在資料庫的數據字典中	存儲在當前的套用中
安全性	由資料庫提供安全保證，必須通過授權才能使用存儲子程式	安全性靠應用程式來保證，如果能執行應用程式，就能執行該子程式。

由資料庫提供安全保證，必須通過授權

才能使用存儲子程式

安全性靠應用程式來保證，如果能執

行應用程式，就能執行該子程式。

語法

CREATE [ORReplace]PROCEDURE[schema.] procedure_name
[(argument [{IN|OUT|INOUT}] datatype,
...
argument [{IN|OUT|INOUT}] datatype)]
{IS | AS}
[descriptionpart說明部分]
BEGIN
SQLSTATEMENT語句序列
[EXCEPTION例外處理]
END[procedureName過程名];

語法分析

ORREPLACE
是一個可選的關鍵字，建議用戶使用此關鍵字。如果過程已經存在，該關鍵字將重新創建過程，這樣就不必刪除和重新創建過程。
關鍵字IS和AS均可，
它們本身沒有區別。IS後面是一個完整的PL/SQL塊，可以定義局部變數，但不能以DECLARE開始。局部變數在過程內部存放值。
形式參數可以有三種模式：IN、OUT、INOUT。如果沒有為形式參數指定模式，那么默認的模式是IN。
IN表示輸入參數
OUT表示輸出參數

HelloWorld！
創建第一個存儲過程HelloWorld
SQL>create or replace procedure helloworld

as

begin

dbms_output.put_line('helloworld');

end;

/
註：需要在存儲過程輸入完成後回車，下一行輸入”/”回車，才會創建成功。
當提示Procedurecreated表示存儲過程創建成功。
調用HelloWorld！
執行
SQL>set serveroutput on;
SQL>exec[ute] helloworld;
顯示如下結果
helloworld
PL/SQL procedure successfully completed

編譯過程

注意：
存儲過程不論創建是否成功，創建過程/函式命令CREATEPROCEDURE或CREATEFUNCTION都將自動把其原始碼存入資料庫中，而編譯代碼只有在編譯成功後才能存入資料庫中。
只有編譯代碼被存入到資料庫的存儲過程和函式才能被調用。
也就是說，如果你創建存儲過程的語句是錯誤的，那么存儲過程的原始碼也會放入資料庫，只是被顯示為錯誤。
查看錯誤請用USER_ERRORS數據字典或用SHOWERRORS命令，可以查詢到當前系統中錯誤。

帶參存儲過程

模式描述IN參數（默認模式）（輸入參數）用來從調用環境中向存儲過程傳遞值，不能給IN參數賦值，給此參數傳遞的值可以是常量、有值的變數、表達式等。
OUT參數（輸出參數）用來從過程中返回值給調用者，不能將此參數的值賦給另一個變數，不能是常量或表達式。在過程體內，必須給OUT參數賦值。INOUT參數（輸入輸出參數）既可以從調用者向過程中傳遞值，執行過程後還可返回可能改變了的值給調用者。

模式	描述
IN參數（默認模式）（輸入參數）	用來從調用環境中向存儲過程傳遞值，不能給IN參數賦值，給此參數傳遞的值可以是常量、有值的變數、表達式等。
OUT參數（輸出參數）	用來從過程中返回值給調用者，不能將此參數的值賦給另一個變數，不能是常量或表達式。在過程體內，必須給OUT參數賦值。
IN OUT參數（輸入輸出參數）	既可以從調用者向過程中傳遞值，執行過程後還可返回可能改變了的值給調用者。

模式

描述

用來從調用環境中向存儲過程傳遞值，不能給IN參數賦值，

給此參數傳遞的值可以是常量、有值的變數、表達式等。

用來從過程中返回值給調用者，不能將此參數的值賦給另

一個變數，不能是常量或表達式。在過程體內，必須給

OUT參數賦值。

既可以從調用者向過程中傳遞值，執行過程後還可返回可能

改變了的值給調用者。

1、帶參存儲過程（輸入參數）

創建存儲過程Hello‘Tom’
SQL>create or replace procedure helloTom(pname in varchar2)

as

begin

dbms_output.put_line('hello'||pname||'!');

end;
SQL>/
執行
SQL>set serveroutput on;
SQL>exec helloTom('jerry');
顯示如下結果
hellojerry!
PL/SQL procedure successfully completed
註：由於存儲過程是高度過程化語言，所以‘+’為能像面向對象一樣被重載為字元串相加，所以在存儲過程語法中使用‘||’表示字元串相加操作。輸入參數可省略in如(pname varchar2)

2、帶參存儲過程（輸出參數）

創建存儲過程Write‘Tom’
SQL>create or replace procedure writeTom(pname out varchar2)

as

begin

select name into pname from t1 where rownum=1;

end;
SQL>/
帶輸出參數存儲過程的調用格式：
(1)綁定參數值
variable輸出參數變數1,輸出參數變數2…;
調用存儲過程
EXEC[UTE]procedure_name(參數值1…參數名n,:綁定變數1,,:綁定變數2….);
SQL>variable pname varchar2(40);
SQL>exec writeTom(:pname);
帶輸出參數存儲過程的調用格式：
(2)在程式塊中調用存儲過程
SQL>declare
2pname varchar2(40);
3begin
4writeTom(pname);
5dbms_output.put_line(pname);
6end;
7/

3、帶參存儲過程（inout參數）

創建存儲過程InoutTom向t1表插入一條記錄
SQL>create or replace procedure inoutTom(pname in out varchar2)

as

begin

select name into pname fromt1 where id=pname;

end;
SQL>/
執行
SQL>declare
2pname varchar2(30):='1';
3begin
4inoutTom(pname);
5dbms_output.put_line(pname);
6end;
7/
註：賦值運算符為’:=’，用於給參數賦默認值

4、參數規則

如果形式參數是IN模式的參數，實際參數可以是一個具體的值或一個有值的變數；
如果形式參數是OUT模式的參數，實際參數必須是一個變數，當調用過程後，此變數就被賦值了。可以輸出此變數的值來測試過程執行的結果。
如果形式參數是INOUT模式的參數，則實際參數必須是一個預先已經賦值的變數。執行完過程後，該變數被重新賦值，可以輸出此變數的值來測試過程執行結果。