18.oracle的過程和函數

oracle11g的過程和函數

一、過程（Procedure）
- 1、子程序
- 2、過程的相關語法
二、函數（Function）
- 1、函數的概念
- 2、函數的創建
- 3、案例

在Oracle數據庫中，過程和函數都是用來封裝一系列SQL語句和邏輯操作的數據庫對象，以便在需要時可以重復使用。它們都可以接受參數并返回值，但在使用上有一些區別。
zohanshu

一、過程（Procedure）

過程是一組SQL語句和邏輯操作的集合，它可以接受輸入參數，但不一定返回數值。
過程可以執行數據操作、流程控制和業務邏輯，它可以包含事務控制語句，如COMMIT和ROLLBACK。
過程通常用于執行一系列的操作，例如數據處理、數據導入導出等。

1、子程序

什么是子程序

命名的 PL/SQL 塊，編譯并存儲在數據庫中。
- 子程序的各個部分：
  - 聲明部分
  - 可執行部分
  - 異常處理部分(可選)
- 子程序的分類：
  - 過程－執行某些操作
  - 函數－執行操作并返回值
子程序的優點：
- 模塊化：將程序分解為邏輯模塊
- 可重用性：可以被任意數目的程序調用
- 可維護性：簡化維護操作
- 安全性：通過設置權限，使數據更安全

2、過程的相關語法

創建語法
```
CREATE OR REPLACE PROCEDURE procedure_name (parameter1 IN datatype1, parameter2 OUT datatype2)
AS
BEGIN-- 存儲過程的邏輯代碼
END;
/
```
在上面的語法中：
- CREATE OR REPLACE用于創建或替換已存在的存儲過程。
- PROCEDURE關鍵字用于聲明創建一個存儲過程。
- procedure_name是存儲過程的名稱。
- parameter1是輸入參數，使用IN關鍵字聲明，后面跟著參數的數據類型。
- parameter2是輸出參數，使用OUT關鍵字聲明，后面跟著參數的數據類型。
- AS和BEGIN之間是存儲過程的實際邏輯代碼。
- END;表示存儲過程的結束。
下面是一個示例，展示了如何創建一個簡單的存儲過程：
```
CREATE OR REPLACE PROCEDURE get_employee_name (p_employee_id IN NUMBER, p_employee_name OUT VARCHAR2)
AS
BEGINSELECT employee_name INTO p_employee_name FROM employees WHERE employee_id = p_employee_id;
END;
/
```
在上面的示例中，我們創建了一個名為get_employee_name的存儲過程。它接受一個員工ID作為輸入參數，并返回該員工的姓名。在實際使用時，可以通過調用這個存儲過程來獲取員工的姓名。
過程參數的三種模式

在Oracle中，存儲過程和函數的參數可以使用以下三種模式進行聲明和傳遞：

IN模式：
- 當參數聲明為IN模式時，表示該參數是一個輸入參數，即存儲過程或函數可以讀取該參數的值，但不能修改它。
- 在調用存儲過程或函數時，需要提供IN模式參數的值作為輸入。
- 例如：
```
CREATE OR REPLACE PROCEDURE my_procedure (p_input_param IN VARCHAR2) AS
BEGIN-- 讀取p_input_param的值，但不能修改它
END;
```
OUT模式：
- 當參數聲明為OUT模式時，表示該參數是一個輸出參數，即存儲過程或函數可以修改該參數的值，并且該值將在存儲過程或函數執行完畢后返回給調用者。
- 在調用存儲過程或函數時，OUT模式參數通常不需要提供初始值，因為它的值將由存儲過程或函數賦予。
- 例如：
```
CREATE OR REPLACE PROCEDURE my_procedure (p_output_param OUT NUMBER) AS
BEGINp_output_param := 100; -- 修改p_output_param的值
END;
```
IN OUT模式：
- 當參數聲明為IN OUT模式時，表示該參數既是輸入參數，又是輸出參數，即存儲過程或函數可以讀取并修改該參數的值，并且該值將在存儲過程或函數執行完畢后返回給調用者。
- 在調用存儲過程或函數時，需要提供IN OUT模式參數的初始值作為輸入，并且在存儲過程或函數執行完畢后，該參數的值將被修改并返回給調用者。
- 例如：
```
CREATE OR REPLACE PROCEDURE my_procedure (p_in_out_param IN OUT VARCHAR2) AS
BEGINp_in_out_param := p_in_out_param || '_modified'; -- 修改p_in_out_param的值
END;
```
通過使用這三種參數模式，可以在存儲過程和函數中實現對參數的不同操作和傳遞方式，從而滿足不同的業務需求。

執行過程的語法
在Oracle中，執行存儲過程的語法如下：
```
EXECUTE procedure_name(parameter1, parameter2, ...);
```
或者可以使用以下語法：
```
CALL procedure_name(parameter1, parameter2, ...);
```
在上面的語法中：
- EXECUTE或CALL關鍵字用于執行存儲過程。
- procedure_name是要執行的存儲過程的名稱。
- parameter1, parameter2, …是存儲過程的參數，如果存儲過程有參數的話，需要根據參數的順序傳遞參數的值。
例如，如果有一個名為get_employee_name的存儲過程，它接受一個員工ID作為輸入參數，并返回員工的姓名作為輸出參數，那么可以使用以下語法來執行該存儲過程：
```
EXECUTE get_employee_name(100, :employee_name);
```
或者使用CALL語法：
```
CALL get_employee_name(100, :employee_name);
```
在上面的示例中，100是作為輸入參數傳遞給存儲過程的員工ID，:employee_name是一個輸出參數，用于接收存儲過程返回的員工姓名。

通過使用上述語法，可以執行存儲過程并傳遞參數，從而實現對數據庫的操作。
過程的執行權限授予
要將存儲過程的執行權限授予其他用戶，你可以使用Oracle的GRANT語句。以下是授予其他用戶執行存儲過程權限的示例：
```
GRANT EXECUTE ON procedure_name TO other_user;
```
在上面的示例中：
- procedure_name是要授予權限的存儲過程的名稱。
- other_user是要授予權限的其他用戶的名稱。
執行上述GRANT語句后，other_user將獲得對procedure_name存儲過程的執行權限，允許該用戶執行該存儲過程。

如果你希望其他用戶能夠執行某個schema中的所有存儲過程，可以使用以下語法：
```
GRANT EXECUTE ON SCHEMA_NAME TO other_user;
```
在上面的示例中，SCHEMA_NAME是包含存儲過程的schema的名稱，other_user將獲得對該schema中所有存儲過程的執行權限。

請注意，授予執行權限是一種敏感的操作，應該謹慎使用。確保只授予必要的權限，并且只授予給需要執行存儲過程的用戶。

二、函數（Function）

1、函數的概念

函數也是一組SQL語句和邏輯操作的集合。
函數是可以返回值的命名的 PL/SQL 子程序。
函數通常用于計算和返回一個值，例如在查詢中調用函數來進行數學運算、字符串處理或者日期計算。
函數可以被用于SELECT語句的列中，也可以在其他SQL語句中調用。

在Oracle 11g數據庫中，過程和函數都可以使用PL/SQL（Procedural Language/Structured Query Language）來編寫，PL/SQL是Oracle數據庫的過程化擴展語言，它允許開發人員在數據庫中編寫存儲過程、函數、觸發器等。

無論是過程還是函數，它們都可以提高數據庫的性能和安全性，減少重復編寫相同邏輯的工作，同時也可以簡化數據庫應用程序的開發和維護工作。

2、函數的創建

創捷語法
```
CREATE OR REPLACE FUNCTION function_name (parameter1 IN datatype1) RETURN datatype2
ASvariable datatype2;
BEGIN-- 函數的邏輯代碼RETURN variable;
END;
/
```
在上面的語法中：
- CREATE OR REPLACE用于創建或替換已存在的函數。
- FUNCTION關鍵字用于聲明創建一個函數。
- function_name是函數的名稱。
- parameter1是輸入參數，使用IN關鍵字聲明，后面跟著參數的數據類型。
- RETURN datatype2用于聲明函數的返回類型。
- AS和BEGIN之間是函數的實際邏輯代碼。
- RETURN variable;用于返回函數的結果。
下面是一個示例，展示了如何創建一個簡單的函數：
```
CREATE OR REPLACE FUNCTION calculate_bonus (p_salary IN NUMBER) RETURN NUMBER
ASv_bonus NUMBER;
BEGINIF p_salary > 50000 THENv_bonus := p_salary * 0.1;ELSEv_bonus := p_salary * 0.05;END IF;RETURN v_bonus;
END;
/
```
以上代碼是一個創建或替換函數的示例。該函數名為calculate_bonus，接受一個輸入參數p_salary（工資）并返回一個數值類型的結果，表示獎金金額。
函數的邏輯如下：
- 如果輸入的工資p_salary大于50000，則獎金金額為工資的10%；
- 否則，獎金金額為工資的5%。
在函數體內部，使用了一個局部變量v_bonus來存儲計算得到的獎金金額，并使用IF-ELSE條件語句來根據不同的情況進行計算。最后，使用RETURN語句返回計算得到的獎金金額。

通過使用上述代碼，可以在數據庫中創建一個名為calculate_bonus的函數，當需要根據工資計算獎金時，可以調用此函數來獲得相應的獎金金額。
函數的限制：

函數的參數類型和數量必須與函數聲明中的參數類型和數量匹配。
函數的返回類型必須與函數聲明中指定的返回類型匹配。
函數體內部的代碼必須遵循數據庫管理系統所使用的特定語法和規則。
在函數體內部，可以訪問和操作數據庫表、視圖、其他函數、存儲過程等數據庫對象，但需要確保有足夠的權限來執行這些操作。

訪問函數的兩種方式：

直接調用：在SQL語句中可以直接調用函數，例如：
```
SELECT calculate_bonus(60000) FROM dual;
```
這里的calculate_bonus是函數的名稱，60000是傳遞給函數的參數。
作為表達式的一部分：函數可以作為SQL表達式的一部分來使用，例如：
```
SELECT employee_name, calculate_bonus(salary) AS bonus FROM employees;
```
在這個例子中，calculate_bonus函數被用作一個表達式，用來計算每個員工的獎金，并將結果作為一個新的列返回。

通過這兩種方式，可以方便地使用函數來進行計算和數據處理，從而提高數據庫查詢和操作的靈活性和功能性。

3、案例

案例題目
已經執行了下面語句
```
create table  分數表 (student_no number(3),name varchar2(10), score number(3));insert into   分數表  values (1,'張一', 56);
insert into   分數表 values(2,'張二', 82);
insert into   分數表 values  (3,'張三', 90);
```
要求：創建一個函數，可以接受用戶輸入的學號，得到該學生的名次，并輸出這個名次。
要實現這個功能，可以創建一個函數，函數的輸入參數是學生的學號，函數的輸出是該學生的名次。下面是一個示例函數的定義：
案例解答：
```
CREATE OR REPLACE FUNCTION get_student_rank(p_student_no IN NUMBER) RETURN NUMBER
ASv_rank NUMBER;
BEGINSELECT COUNT(*) INTO v_rankFROM 分數表WHERE score > (SELECT score FROM 分數表 WHERE student_no = p_student_no);RETURN v_rank + 1; -- 因為排名從1開始，所以需要加1
END;
/
```
在上面的函數中，我們創建了一個名為get_student_rank的函數，它接受一個學號作為輸入參數，并返回該學生的名次。函數的邏輯如下：
- 首先，我們使用一個SELECT語句來計算比指定學生分數更高的學生數量，這個數量就是指定學生的名次。
- 然后，我們將這個名次返回作為函數的結果。
使用這個函數，可以通過以下方式獲取指定學生的名次：
```
SELECT get_student_rank(2) FROM dual;
```
這將返回學號為2的學生的名次。

以下對過程和函數進行的比較：

特性	過程	函數
返回值	可以有OUT參數來返回值	返回單個值
在SQL中的使用	不能直接在SQL語句中使用	可以直接在SQL語句中使用
錯誤處理	可以使用異常處理來處理錯誤	可以使用異常處理來處理錯誤
事務處理	可以提交或回滾事務	不能提交或回滾事務
調用方式	可以從其他過程或觸發器中調用	可以從SQL語句或其他函數中調用
	CALL my_procedure(param1, param2);	SELECT my_function(param1, param2) FROM dual;