MySQL 儲存函式及呼叫

TechSynapse發表於2024-06-10
MySql儲存函式

1.mysql 儲存函式及呼叫

在MySQL中，儲存函式（Stored Function）是一種在資料庫中定義的特殊型別的函式，它可以從一個或多個引數返回一個值。儲存函式在資料庫層面上封裝了複雜的SQL邏輯，使得在應用程式中呼叫時更加簡單和高效。

下面是一個具體的MySQL儲存函式的示例，該函式接受一個整數引數並返回該整數的平方值。

1.1建立儲存函式

首先，我們需要在MySQL資料庫中建立一個儲存函式。這裡我們使用CREATE FUNCTION語句來定義儲存函式。

DELIMITER //  
CREATE FUNCTION square_number(num INT) RETURNS INT  
BEGIN  
    DECLARE result INT;  
    SET result = num * num;  
    RETURN result;  
END //  
DELIMITER ;

在這個示例中：

（1）DELIMITER // 和 DELIMITER ; 是用來改變MySQL語句的結束符的，因為儲存函式的定義中可能包含多個分號，這會導致MySQL直譯器在儲存函式定義內部錯誤地結束語句。

（2）CREATE FUNCTION square_number(num INT) RETURNS INT 宣告瞭一個名為square_number的儲存函式，它接受一個整數引數num，並返回一個整數結果。

（3）在BEGIN ... END塊中，我們定義了儲存函式的主體邏輯。這裡我們宣告瞭一個變數result，並計算num的平方值，然後將結果賦值給result。

（4）RETURN result; 語句將計算得到的result值返回給呼叫者。

1.2呼叫儲存函式

一旦儲存函式被建立，我們就可以在SQL查詢中像呼叫普通函式一樣呼叫它。下面是一個示例，展示如何在SELECT語句中呼叫square_number函式：

sql複製程式碼

SELECT square_number(5);  -- 返回 25

在這個示例中，我們呼叫了square_number函式並傳入引數值5，函式返回了25作為結果。

1.3刪除儲存函式

如果我們不再需要某個儲存函式，可以使用DROP FUNCTION語句來刪除它：

sql複製程式碼

DROP FUNCTION square_number;

這個語句將刪除名為square_number的儲存函式。

1.4注意事項

（1）儲存函式只能返回單一的值，而儲存過程（Stored Procedure）可以返回多個值或結果集。

（2）儲存函式可以在SQL語句的任何地方呼叫，就像呼叫內建函式一樣。

（3）儲存函式通常用於執行計算、資料轉換或複雜的業務邏輯，這些邏輯在多個地方被重複使用。

2.MySQL儲存函式的示例

以下是一些關於MySQL儲存函式的示例，這些示例涵蓋了不同的場景和功能。

2.1示例1：計算兩個數的和

假設我們想要建立一個儲存函式來計算兩個數的和。

DELIMITER //  
CREATE FUNCTION add_numbers(num1 INT, num2 INT) RETURNS INT  
BEGIN  
    DECLARE sum_result INT;  
    SET sum_result = num1 + num2;  
    RETURN sum_result;  
END //  
DELIMITER ;  
  
-- 呼叫儲存函式  
SELECT add_numbers(3, 5);  -- 返回 8

2.2示例2：獲取字串的長度

雖然MySQL已經有一個內建的LENGTH()函式來獲取字串的長度，但我們可以為了示例目的建立一個類似的儲存函式。

DELIMITER //  
CREATE FUNCTION my_string_length(input_string VARCHAR(255)) RETURNS INT  
BEGIN  
    RETURN LENGTH(input_string);  
END //  
DELIMITER ;  
  
-- 呼叫儲存函式  
SELECT my_string_length('Hello, World!');  -- 返回 13

2.3示例3：將整數轉換為字串

我們可以建立一個將整數轉換為字串的儲存函式。

DELIMITER //  
CREATE FUNCTION int_to_string(num INT) RETURNS VARCHAR(255)  
BEGIN  
    RETURN CAST(num AS CHAR);  
END //  
DELIMITER ;  
  
-- 呼叫儲存函式  
SELECT int_to_string(123);  -- 返回 '123'

2.4示例4：判斷是否為閏年

我們可以建立一個儲存函式來判斷一個給定的年份是否為閏年。

DELIMITER //  
CREATE FUNCTION is_leap_year(year_value INT) RETURNS BOOLEAN  
BEGIN  
    IF (year_value % 4 = 0 AND year_value % 100 != 0) OR (year_value % 400 = 0) THEN  
        RETURN TRUE;  
    ELSE  
        RETURN FALSE;  
    END IF;  
END //  
DELIMITER ;  
  
-- 呼叫儲存函式  
SELECT is_leap_year(2020);  -- 返回 1（TRUE）  
SELECT is_leap_year(2021);  -- 返回 0（FALSE）

請注意，MySQL的BOOLEAN型別實際上是TINYINT(1)的一個別名，所以TRUE返回1，FALSE返回0。

2.5示例5：計算字串中某個字元的出現次數

這個示例展示瞭如何計算一個字串中某個字元的出現次數。

DELIMITER //  
CREATE FUNCTION char_count(input_string VARCHAR(255), search_char CHAR(1)) RETURNS INT  
BEGIN  
    DECLARE char_count_result INT DEFAULT 0;  
    DECLARE str_len INT DEFAULT LENGTH(input_string);  
    DECLARE i INT DEFAULT 1;  
    WHILE i <= str_len DO  
        IF SUBSTRING(input_string, i, 1) = search_char THEN  
            SET char_count_result = char_count_result + 1;  
        END IF;  
        SET i = i + 1;  
    END WHILE;  
    RETURN char_count_result;  
END //  
DELIMITER ;  
  
-- 呼叫儲存函式  
SELECT char_count('hello world', 'o');  -- 返回 2

這些示例涵蓋了從簡單到稍微複雜的儲存函式功能，並且應該能夠幫助我們理解如何在MySQL中建立和使用儲存函式。

3.如何在MySQL中建立儲存函式

在MySQL中建立儲存函式，我們需要使用CREATE FUNCTION語句。以下是一個基本的步驟和示例，說明如何建立一個簡單的儲存函式。

3.1建立儲存函式的步驟

（1）選擇資料庫（如果尚未選擇）：

sql複製程式碼

USE your_database_name;

（2）編寫儲存函式：
使用CREATE FUNCTION語句，指定函式名、引數、返回型別和函式體。

（3）執行建立語句：
在我們的MySQL客戶端（如MySQL命令列、MySQL Workbench等）中執行上述SQL語句。

（3）測試儲存函式：
在SELECT語句或其他SQL語句中呼叫儲存函式，以驗證其是否按預期工作。

3.2建立儲存函式的示例

以下是一個簡單的示例，展示如何建立一個儲存函式來計算一個整數的平方：

DELIMITER //  
CREATE FUNCTION square(num INT) RETURNS INT  
BEGIN  
    RETURN num * num;  
END //  
DELIMITER ;

在這個示例中：

（1）DELIMITER // 和 DELIMITER ; 用於更改預設的語句結束符，以便在函式定義中使用分號（;）。

（2）CREATE FUNCTION square(num INT) RETURNS INT 定義了一個名為square的函式，它接受一個整數引數num，並返回一個整數。

（3）在BEGIN ... END塊中，我們定義了函式的主體，它返回num的平方。

3.3呼叫儲存函式

一旦儲存函式被建立，我們就可以在SQL查詢中像呼叫內建函式一樣呼叫它：

sql複製程式碼

SELECT square(5);  -- 返回 25

3.4刪除儲存函式

如果我們不再需要某個儲存函式，可以使用DROP FUNCTION語句來刪除它：

sql複製程式碼

DROP FUNCTION square;

這個語句將刪除名為square的儲存函式。請注意，在刪除儲存函式之前，確保沒有其他的資料庫物件（如表或檢視）依賴於該函式，否則可能會導致錯誤。

4.儲存函式和函式有什麼區別

在MySQL的上下文中，當我們提到“儲存函式”（Stored Function）時，我們通常指的是在資料庫中定義的、可以像內建函式一樣在SQL查詢中呼叫的特殊函式。這些儲存函式通常用於封裝複雜的SQL邏輯，以便在多個地方重複使用。

然而，當我們提到“函式”（Function）時，這個術語可能具有更廣泛的意義，它取決於上下文。在程式設計和資料庫領域，函式通常指的是一個可以接收輸入（引數）並返回輸出的程式碼塊。這個術語可以應用於多種不同的環境和語言，包括程式語言（如C、Java、Python等）、資料庫（如MySQL、PostgreSQL等）以及數學和邏輯運算。

在MySQL的特定上下文中，我們可以將“函式”分為兩種主要型別：

（1）內建函式（Built-in Functions）：這些是MySQL資料庫管理系統提供的預定義函式，用於執行各種常見的資料庫操作，如字串操作、日期和時間計算、數值計算等。例如，CONCAT()函式用於連線兩個或多個字串，NOW()函式返回當前日期和時間。

（2）儲存函式（Stored Functions）：這些是使用者定義的函式，它們使用CREATE FUNCTION語句在資料庫中建立，並像內建函式一樣在SQL查詢中呼叫。儲存函式封裝了複雜的SQL邏輯，可以在多個地方重複使用，從而提高了程式碼的可維護性和重用性。

（3）儲存函式和內建函式的區別：

  • 定義者：內建函式是由資料庫管理系統提供的，而儲存函式是由資料庫使用者定義的。
  • 可定製性：內建函式的功能是固定的，使用者無法更改它們的行為。而儲存函式允許使用者定義自己的邏輯，並根據需要進行修改。
  • 使用場景：內建函式通常用於執行常見的、簡單的資料庫操作。而儲存函式則更適用於封裝複雜的SQL邏輯，以便在多個地方重複使用。

（4）儲存函式和儲存過程（Stored Procedures）的區別：

  • 返回值：儲存函式必須返回一個值，而儲存過程可以返回多個值或結果集，也可以不返回任何值。
  • 呼叫方式：儲存函式可以在SELECT語句中直接呼叫，並像其他列一樣在結果集中顯示。而儲存過程通常需要使用CALL語句來呼叫，並且它們的輸出通常需要透過輸出引數或結果集來獲取。
  • 語法和用途：儲存函式的語法類似於其他程式語言中的函式，而儲存過程則更類似於一組為了完成特定功能的SQL語句集合。在用途上，儲存函式通常用於計算或轉換資料，而儲存過程則更適用於執行復雜的資料庫操作或事務處理。

5.觸發器和函式的區別

觸發器和函式在MySQL中有顯著的不同，主要體現在以下幾個方面：

（1）用途：

  • 函式（Function）：通常用於封裝可重複使用的邏輯，這些邏輯可以在多個地方被其他過程或觸發器主動呼叫。函式體現的是一個獨立的功能，它們封裝了用於執行特定任務的程式碼，並返回一個值。
  • 觸發器（Trigger）：通常用於在資料庫發生特定事件時自動執行一些邏輯。觸發器與表相關聯，並在表的資料發生變化（如INSERT、UPDATE、DELETE操作）時觸發執行。它們主要用於實現資料完整性、審計、日誌記錄等自動化操作。

（2）觸發方式：

  • 函式：可以由其他過程主動呼叫，是顯式的執行方式。
  • 觸發器：只能在特定條件下自動觸發執行，是隱式的執行方式。它們不需要手動呼叫，而是在滿足定義的條件時自動執行。

（3）執行時機：

  • 函式：在具體需要時被呼叫執行。
  • 觸發器：在觸發條件滿足時自動執行。它們可以在資料庫操作之前或之後觸發，具體取決於觸發器的定義。

（4）返回值：

  • 函式：必須返回一個值，這個值可以是任何資料型別，取決於函式的定義。
  • 觸發器：不直接返回值。它們透過執行一系列的動作來影響資料庫的狀態，如修改表中的資料、插入資料到其他表、呼叫其他儲存過程等。

（5）應用場景：

  • 函式：常用於封裝複雜的邏輯運算、資料轉換等任務，以提高程式碼的複用性和可維護性。
  • 觸發器：常用於實現資料完整性約束、日誌記錄和審計、資料的備份和同步等自動化操作。它們可以在資料發生變化時自動執行相應的邏輯，以確保資料的準確性和一致性。

觸發器和函式在MySQL中各有其獨特的作用和應用場景。函式主要用於封裝可重複使用的邏輯並返回結果值，而觸發器則用於在資料庫發生特定事件時自動執行相應的邏輯。

6.如何在MySQL中建立觸發器

在MySQL中，觸發器（Trigger）是一種特殊的儲存過程，它會在指定的表上進行INSERT、UPDATE或DELETE操作之前或之後自動執行。要使用觸發器，我們需要使用CREATE TRIGGER語句。

以下是建立觸發器的基本步驟和示例：

6.1建立觸發器的步驟

（1）選擇資料庫（如果尚未選擇）：

sql複製程式碼

USE your_database_name;

（2）編寫觸發器：
使用CREATE TRIGGER語句，指定觸發器的名稱、觸發時間（BEFORE或AFTER）、觸發事件（INSERT、UPDATE或DELETE）、關聯的表和要執行的SQL語句。

（3）執行建立語句：
在我們的MySQL客戶端（如MySQL命令列、MySQL Workbench等）中執行上述SQL語句。

6.2建立觸發器的示例

假設我們有一個名為orders的表，每當有新訂單（即插入新行）時，我們想要記錄一條日誌到另一個名為order_logs的表中。

首先，確保我們有order_logs表，其結構可能如下：

CREATE TABLE order_logs (  
    log_id INT AUTO_INCREMENT PRIMARY KEY,  
    order_id INT,  
    log_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,  
    action VARCHAR(50) NOT NULL  
);

然後，我們可以建立一個觸發器，當在orders表中插入新行時，向order_logs表中插入一條日誌記錄：

DELIMITER //  
CREATE TRIGGER after_order_insert  
AFTER INSERT ON orders  
FOR EACH ROW  
BEGIN  
    INSERT INTO order_logs (order_id, action) VALUES (NEW.order_id, 'Order inserted');  
END;  
//  
DELIMITER ;

在這個示例中：

  • after_order_insert 是觸發器的名稱。
  • AFTER INSERT ON orders 指定觸發器在orders表上插入新行之後觸發。
  • FOR EACH ROW 表示觸發器會對受影響的每一行執行一次。
  • 在BEGIN ... END塊中，我們定義了觸發器要執行的SQL語句，即向order_logs表中插入一條記錄。注意，我們使用NEW.order_id來引用新插入的orders表中的order_id值。

6.3注意事項

  • 觸發器中的SQL語句通常是對資料表進行增刪改查的操作，但不能呼叫儲存過程。
  • 觸發器中的SQL語句可以引用被觸發表中的列，但只能引用被觸發行的列（使用NEW或OLD關鍵字，具體取決於觸發時間）。
  • 觸發器是基於表的，不是基於整個資料庫的。每個觸發器只能和一個表關聯。
  • 觸發器不能透過直接呼叫執行，只能透過觸發事件隱式觸發。
  • 如果觸發器中的SQL語句執行失敗，那麼原始操作（INSERT、UPDATE或DELETE）也會被回滾。
  • 觸發器可以巢狀使用，即一個觸發器可以呼叫另一個觸發器，但巢狀的最大層數取決於MySQL伺服器的配置。

6.4刪除觸發器

如果我們不再需要某個觸發器，可以使用DROP TRIGGER語句來刪除它：

sql複製程式碼

DROP TRIGGER after_order_insert;

這個語句將刪除名為after_order_insert的觸發器。請注意，刪除觸發器時不需要指定資料庫名稱，只需要指定觸發器的名稱。

相關文章