SQL - 儲存過程

---

---

SQL 儲存過程

SQL 儲存過程是一組預編譯的 SQL 語句（準備好的 SQL 程式碼），只需呼叫即可重複使用。

它可以用於執行各種資料庫操作，例如插入、更新或刪除資料、生成報表和執行復雜的計算。儲存過程非常有用，因為它們允許您將一組 SQL 語句封裝（捆綁）為單個單元，並使用不同的引數重複執行它們，從而易於管理和重用程式碼。

過程與函式具有類似的結構：它們接受引數並在我們呼叫它們時執行操作。但是，它們之間的區別在於 SQL 儲存過程更易於編寫或建立，而函式具有更嚴格的結構並且支援較少的子句。

語法

建立 SQL 儲存過程的基本語法如下：

DELIMITER //
CREATE PROCEDURE procedure_name(parameter1 datatype, parameter2 datatype, ...)
BEGIN
   -- SQL statements to be executed
END
DELIMITER ;

• CREATE PROCEDURE 語句用於建立過程。我們可以根據需要定義任意數量的輸入引數。

• 構成過程的 SQL 語句位於 BEGIN 和 END 關鍵字之間。

建立過程

我們可以使用 SQL 中的 CREATE PROCEDURE 語句建立儲存過程。以下是建立儲存過程的簡單步驟：

• 為過程選擇一個名稱。

• 編寫過程的 SQL 程式碼。

• 然後，我們可以透過使用不同的輸入引數執行它來測試儲存過程。

示例

為了更好地理解它，讓我們考慮一下 CUSTOMERS 表，其中包含客戶的個人詳細資訊，包括他們的姓名、年齡、地址和薪水等，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

現在，使用 INSERT 語句將值插入此表，如下所示：

INSERT INTO CUSTOMERS VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00),
(2, 'Khilan', 25, 'Delhi', 1500.00),
(3, 'Kaushik', 23, 'Kota', 2000.00),
(4, 'Chaitali', 25, 'Mumbai', 6500.00),
(5, 'Hardik', 27, 'Bhopal', 8500.00),
(6, 'Komal', 22, 'Hyderabad', 4500.00),
(7, 'Muffy', 24, 'Indore', 10000.00);

表將被建立為：

現在，讓我們來看一個建立儲存過程的簡單示例，該儲存過程接受一個輸入引數並返回一個結果集。

在下面的查詢中，我們正在建立名為 GetCustomerInfo 的儲存過程。然後我們為其提供一個名為 @CutomerAge 的單個輸入引數。然後，儲存過程選擇 CUSTOMERS 表中 CutomerAge 的值與輸入引數匹配的所有記錄。

DELIMITER //
CREATE PROCEDURE GetCustomerInfo(IN CustomerAge INT)
   BEGIN
      SELECT * FROM CUSTOMERS WHERE AGE = CustomerAge;
   END //
DELIMITER ;

輸出

這將產生以下結果：

Query OK, 0 rows affected (0.01 sec)

驗證

我們可以使用CALL語句測試儲存過程，如下所示：

CALL GetCustomerInfo(25);

這將返回 CUSTOMERS 表中客戶年齡為 25 的所有列。

儲存過程引數型別

資料庫系統中的儲存過程可以具有不同型別的引數，這些引數是將在執行儲存過程時傳遞給儲存過程的值的佔位符。以下是 SQL 中不同型別的儲存過程引數：

帶有 IN 引數的過程

IN 是過程的預設引數，它將接收輸入值。當呼叫儲存過程時，我們可以將值作為引數傳遞。

這些值是隻讀的，因此儲存過程無法修改它們。

示例

在下面的查詢中，我們正在建立一個儲存過程，該過程採用客戶 ID 作為輸入引數並返回相應的客戶薪水。

過程主體只是執行 SELECT 語句以從“CUSTOMERS”表中檢索“Salary”列，其中“CustomerID”與輸入引數匹配。

DELIMITER //
CREATE PROCEDURE GetCustomerSalary(IN CustomerID Int)
   BEGIN
      SELECT SALARY FROM CUSTOMERS WHERE ID = CustomerID;
   END //
DELIMITER ;

輸出

這將產生以下結果：

Query OK, 0 rows affected (0.01 sec)

驗證

我們可以透過使用不同的 ID 作為輸入引數執行它來測試它，如下面的查詢所示：

CALL GetCustomerSalary(6);

這將返回 ID 為 6 的客戶的薪水，假設“CUSTOMERS”表中存在相應的行：

帶有 OUT 引數的過程

OUT 引數用於從過程返回值。

請注意，當使用 OUT 引數時，在將其傳遞到儲存過程時，我們必須在引數名稱前指定關鍵字 OUT。這告訴 SQL 資料庫該引數是輸出引數，並且應該在儲存過程中賦值。

示例

在下面的查詢中，我們正在建立一個儲存過程，用於計算具有相同年齡的客戶的記錄數，並將此計數分配給儲存記錄數的“total”變數。

過程主體執行 SELECT 語句以獲取“CUSTOMERS”表中具有相同年齡的記錄數

DELIMITER //
CREATE PROCEDURE GetDetail(OUT total INT)
   BEGIN
      SELECT COUNT(AGE) INTO total FROM CUSTOMERS
      WHERE AGE = 25;
   END //
DELIMITER ;

呼叫已建立的過程並傳遞“total”引數

CALL GetDetail(@total);

在這裡，我們使用 SELECT 語句並獲取計數：

SELECT @total;

輸出

這將產生以下結果：

驗證

要驗證是否建立了過程，我們可以使用以下查詢：

SHOW CREATE PROCEDURE GetDetails;

帶有 INOUT 引數的過程

INOUT 引數是 IN 引數和 OUT 引數的組合。您可以使用相同的引數將資料傳遞到儲存過程並從儲存過程接收資料。

要在儲存過程中宣告 INOUT 引數，我們需要在引數名稱前指定INOUT關鍵字。

示例

在下面的查詢中，我們為儲存過程提供了兩個 INOUT 引數：cust_id 和 curr_Salary。這兩個引數都用作輸入和輸出引數。

儲存過程首先使用 cust_id 引數從資料庫中檢索客戶的當前薪水。然後它將薪水增加 10%，並使用相同的引數更新資料庫中的客戶薪水。

DELIMITER //
CREATE PROCEDURE increaseSalary(INOUT Cust_Id Int,  INOUT curr_Salary Int)
   BEGIN
      SELECT SALARY INTO curr_Salary From CUSTOMERS Where ID = Cust_Id;
      SET curr_Salary = curr_Salary * 1.1;
      Update CUSTOMERS SET SALARY = curr_Salary Where ID = Cust_Id;
   END //
DELIMITER ;

輸出

這將產生以下結果：

Query OK, 0 rows affected (0.01 sec)

驗證

我們可以透過使用不同的 ID 或輸入引數執行它來測試它，如下面的查詢所示：

SET @customerID = 1;
SET @salary = 0.0;
CALL increaseSalary(@customerID, @salary);

以下是從儲存過程選擇更新後的薪水的查詢

SELECT @salary AS updated_salary;

獲得的結果集為：

儲存過程的優點

以下是儲存過程的優點：

• 效能改進：儲存過程是預編譯的並存儲在伺服器上，因此它們的執行速度比從客戶端應用程式傳送的 SQL 語句快。

• 程式碼重用：儲存過程可以從不同的客戶端應用程式呼叫，這意味著相同的程式碼可以在不同的應用程式中重用。這減少了開發時間和維護成本。

• 減少網路流量：由於儲存過程在伺服器上執行，只有結果返回給客戶端，從而減少網路流量並提高應用程式效能。

• 增強安全性：儲存過程可用於強制執行安全規則並防止對敏感資料的未授權訪問。它們還可以限制使用者可以執行的操作，從而更容易維護資料完整性和一致性。

• 簡化維護：透過將SQL程式碼儲存在一個位置，更容易維護和更新程式碼。這使得修復錯誤、新增新功能和最佳化效能更加容易。

儲存過程的缺點

以下是儲存過程的缺點：

• 增加開銷：儲存過程比簡單的SQL語句消耗更多的伺服器資源，尤其是在頻繁使用或用於複雜操作時。

• 可移植性受限：儲存過程通常特定於某個資料庫管理系統（DBMS），這意味著它們可能不容易移植到其他DBMS。

• 除錯挑戰：除錯儲存過程可能比除錯簡單的SQL語句更具挑戰性，尤其是在涉及多層程式碼時。

• 安全風險：如果儲存過程編寫不正確，則可能構成安全風險，尤其是在用於訪問敏感資料或執行可能危及資料庫完整性的操作時。