Oracle學習筆記二十二：存儲過程

一、存儲過程簡介

• 存儲過程（Stored Procedure）是一組為了完成特定功能的PL/SQL語句塊，經編譯後存儲在資料庫中。

• 存儲過程經編譯和SQL優化後存儲在資料庫伺服器中，使用時只要調用即可。

• 存儲過程是資料庫中的一個重要對象，任何一個設計良好的資料庫應用程式都應該用到存儲過程。

• 用戶通過指定存儲過程的名字給出參數（如果該存儲過程帶有參數）來執行它。

二、存儲過程分類

1）系統存儲過程

　　• 系統存儲過程就是由Oracle預先提供的一組完成特定功能的存儲過程，安裝完Oracle就有了。

2）自定義存儲過程

　　• 自定義存儲過程就是存在Oracle資料庫里由一組PL/SQL語句組成的自定義過程（Procedure）。它可以供其它Oracle自定義存儲過程、自定義函數和Job調用或者由客戶端程式調用。

三、存儲過程語法

• 定義存儲過程的語法：

　　CREATE [OR REPLACE] PROCEDURE 存儲過程名

　　　　[(參數名1 [參數模式] 參數數據類型1,參數名2 [參數模式] 參數數據類型2)]

　　IS [AS]

　　　　[變數 [constant] 類型 [預設值]]

　　BEGIN

　　　　PL/SQL 語句塊;

　　END 存儲過程名;

四、無參存儲過程

1）定義無參存儲過程

create or replace procedure PrintToday
is
begin
    dbms_output.put_line(sysdate);
end;

2）執行無參存儲過程

begin
    PrintToday();
end;

五、存儲過程參數

• 建立存儲過程時，既可以指定存儲過程的參數，也可以不提供任何參數。

• 存儲過程的參數主要有三種類型：輸入參數(IN)、輸出參數(OUT)、輸入輸出參數(IN OUT)，其中IN用於接收調用環境的輸入參數，OUT用於輸出數據傳遞到調用環境，IN OUT不僅要接收數據，而且要輸出數據到調用環境。

• 在建立存儲過程時，輸入參數的IN可以省略。

• 註意：當定義存儲過程的參數時，只能指定數據類型，不能指定數據長度。

六、有參存儲過程

6.1、帶輸入參數存儲過程

1）定義帶輸入參數存儲過程

create or replace procedure InsertEmp
(
    p_empno varchar2,
    p_ename varchar2
)
is
begin
    insert into emp (empno,ename) values (p_empno,p_ename);
    commit;
end;

2）執行帶輸入參數存儲過程

begin
    InsertEmp ('1004','Green');
end;

6.2、帶輸出參數存儲過程

1）定義帶輸出參數存儲過程

create or replace procedure CountEmp
(
    p_ename in varchar2,
    out_value out number
)
is
begin
    select count(1) into out_value from emp where ename=p_ename;
end;

2）執行帶輸出參數存儲過程

declare
    out_value number;
begin
    CountEmp ('James',out_value);
    dbms_output.put_line(out_value);
end;

七、維護存儲過程

• 修改存儲過程

　　• 與創建的語法相同，使用REPLACE替換即可。

• 刪除存儲過程

　　• drop procedure [schema.]過程名