技術文章 » mysql中儲存過程和儲存函數是什麼？

mysql中儲存過程和儲存函數是什麼？

2020-10-15 12:00:26

在mysql中，儲存過程和儲存函數都是資料庫中定義的一些SQL語句的集合。其中，儲存函數可以通過return語句返回函數值，主要用於計算並返回一個值；而儲存過程沒有直接返回值，主要用於執行操作。

（推薦教學：）

mysql中的儲存過程

編寫儲存過程並不是件簡單的事情，但是使用儲存過程可以簡化操作，且減少冗餘的操作步驟，同時，還可以減少操作過程中的失誤，提高效率，因此應該儘可能的學會使用儲存過程。

下面主要介紹如何建立儲存過程。

可以使用 CREATE PROCEDURE 語句建立儲存過程，語法格式如下：

CREATE PROCEDURE <過程名> ( [過程引數[,…] ] ) <過程體>

[過程引數[,…] ] 格式

[ IN | OUT | INOUT ] <引數名> <型別>

語法說明如下：

1) 過程名

儲存過程的名稱，預設在當前資料庫中建立。若需要在特定資料庫中建立儲存過程，則要在名稱前面加上資料庫的名稱，即 db_name.sp_name。

需要注意的是，名稱應當儘量避免選取與 MySQL 內建函數相同的名稱，否則會發生錯誤。

2) 過程引數

儲存過程的參數列。其中，<引數名>為引數名，<型別>為引數的型別（可以是任何有效的 MySQL 資料型別）。當有多個引數時，參數列中彼此間用逗號分隔。儲存過程可以沒有引數（此時儲存過程的名稱後仍需加上一對括號），也可以有 1 個或多個引數。

MySQL 儲存過程支援三種型別的引數，即輸入引數、輸出引數和輸入/輸出引數，分別用 IN、OUT 和 INOUT 三個關鍵字標識。其中，輸入引數可以傳遞給一個儲存過程，輸出引數用於儲存過程需要返回一個操作結果的情形，而輸入/輸出引數既可以充當輸入引數也可以充當輸出引數。

需要注意的是，引數的取名不要與資料表的列名相同，否則儘管不會返回出錯資訊，但是儲存過程的 SQL 語句會將引數名看作列名，從而引發不可預知的結果。

3) 過程體

儲存過程的主體部分，也稱為儲存過程體，包含在過程呼叫的時候必須執行的 SQL 語句。這個部分以關鍵字 BEGIN 開始，以關鍵字 END 結束。若儲存過程體中只有一條 SQL 語句，則可以省略 BEGIN-END 標誌。

在儲存過程的建立中，經常會用到一個十分重要的 MySQL 命令，即 DELIMITER 命令，特別是對於通過命令列的方式來操作 MySQL 資料庫的使用者，更是要學會使用該命令。

在 MySQL 中，伺服器處理 SQL 語句預設是以分號作為語句結束標誌的。然而，在建立儲存過程時，儲存過程體可能包含有多條 SQL 語句，這些 SQL 語句如果仍以分號作為語句結束符，那麼 MySQL 伺服器在處理時會以遇到的第一條 SQL 語句結尾處的分號作為整個程式的結束符，而不再去處理儲存過程體中後面的 SQL 語句，這樣顯然不行。

為解決以上問題，通常使用 DELIMITER 命令將結束命令修改為其他字元。語法格式如下：

DELIMITER $$

語法說明如下：

  • $$ 是使用者定義的結束符，通常這個符號可以是一些特殊的符號，如兩個「?」或兩個「￥」等。

  • 當使用 DELIMITER 命令時，應該避免使用反斜槓「\」字元，因為它是 MySQL 的跳脫字元。

在 MySQL 命令列使用者端輸入如下 SQL 語句。

mysql > DELIMITER ??

成功執行這條 SQL 語句後，任何命令、語句或程式的結束標誌就換為兩個問號「??」了。

若希望換回預設的分號「;」作為結束標誌，則在 MySQL 命令列使用者端輸入下列語句即可：

mysql > DELIMITER ;

注意：DELIMITER 和分號「;」之間一定要有一個空格。在建立儲存過程時，必須具有 CREATE ROUTINE 許可權。

mysql中的儲存函數

在 MySQL 中，使用 CREATE FUNCTION 語句來建立儲存函數，其語法形式如下：

CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body

其中：

  • sp_name 引數：表示儲存函數的名稱；

  • func_parameter：表示儲存函數的參數列；

  • RETURNS type：指定返回值的型別；

  • characteristic 引數：指定儲存函數的特性，該引數的取值與儲存過程是一樣的；

  • routine_body 引數：表示 SQL 程式碼的內容，可以用 BEGIN...END 來標示 SQL 程式碼的開始和結束。

注意：在具體建立函數時，函數名不能與已經存在的函數名重名。除了上述要求外，推薦函數名命名（識別符號）為 function_xxx 或者 func_xxx。

func_parameter 可以由多個引陣列成，其中每個引數由引數名稱和引數型別組成，其形式如下：
[IN | OUT | INOUT] param_name type;

其中：

  • IN 表示輸入引數，OUT 表示輸出引數，INOUT 表示既可以輸入也可以輸出；

  • param_name 引數是儲存函數的引數名稱；

  • type 引數指定儲存函數的引數型別，該型別可以是 MySQL 資料庫的任意資料型別。

例 1

使用 CREATE FUNCTION 建立查詢 tb_student 表中某個學生姓名的函數，SQL 語句和執行過程如下：

mysql> USE test;
Database changed
mysql> DELIMITER //
mysql> CREATE FUNCTION func_student(id INT(11))
    -> RETURNS VARCHAR(20)
    -> COMMENT '查詢某個學生的姓名'
    -> BEGIN
    -> RETURN(SELECT name FROM tb_student WHERE tb_student.id = id);
    -> END //
Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER ;

上述程式碼中，建立了 func_student 函數，該函數擁有一個型別為 INT(11) 的引數 id，返回值為 VARCHAR(20) 型別。SELECT 語句從 tb_student 表中查詢 id 欄位值等於所傳入引數 id 值的記錄，同時返回該條記錄的 name 欄位值。

建立函數與建立儲存過程一樣，需要通過命令 DELIMITER // 將 SQL 語句的結束符由「;」修改為「//」，最後通過命令 DELIMITER ; 將結束符號修改成 SQL 語句中預設的結束符號。

如果在儲存函數中的 RETURN 語句返回一個型別不同於函數的 RETURNS 子句中指定型別的值，返回值將被強制為恰當的型別。比如，如果一個函數返回一個 ENUM 或 SET 值，但是 RETURN 語句返回一個整數，對於 SET 成員集的相應的 ENUM 成員，從函數返回的值是字串。

拓展閱讀

由於儲存函數和儲存過程的檢視、修改、刪除等操作幾乎相同，所以我們不再詳細講解如何操作儲存函數了。

檢視儲存函數的語法如下：

SHOW FUNCTION STATUS LIKE 儲存函數名;
SHOW CREATE FUNCTION 儲存函數名;
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=儲存函數名;

可以發現，操作儲存函數和操作儲存過程不同的是將 PROCEDURE 替換成了 FUNCTION。同樣，修改儲存函數的語法如下：

ALTER FUNCTION 儲存函數名 [ 特徵 ... ]

儲存函數的特徵與儲存過程的基本一樣。

以上就是mysql中儲存過程和儲存函數是什麼？的詳細內容，更多請關注TW511.COM其它相關文章！