MySQL 儲存過程和函式

低吟不作語發表於2022-04-23
MySql儲存過程函式

建立儲存過程和函式

建立儲存過程和函式就是將經常使用的一組 SQL 語句組合在一起，並將這些 SQL 語句當作一個整體儲存在 MySQL 伺服器

1. 建立儲存過程

CREATE PROCEDURE procedure_name ([proc_param[,...]])
    [characteristic ...] routine_body
  • procedure_name 表示所要建立的儲存過程的名字
  • proc_param 表示儲存過程的引數
  • characteristic 表示儲存過程的特性
  • routine_body 表示儲存過程的 SQL 語句

procedure_name 不能出現重名

proc_param 中每個引數的語法形式如下，每個引數由三部分組成，分別為輸入/輸出型別、引數名和引數型別。輸入/輸出型別有三種型別，分別是 IN（輸入型別）、OUT（輸出型別）、INOUT（輸入/輸出型別）。param_name 表示引數名，type 表示引數型別

[ IN | OUT | INOUT ] param_name type

characteristic 指定儲存過程的特性，有以下取值：

  • LANGUAGE SQL：說明儲存過程中使用的是 SQL 語言編寫的
  • [NOT] DETERMINISTIC：說明儲存過程執行的結果是否正確，DETERMINISTIC 表示結果是確定的，即每次執行儲存過程，相同的輸入會得到相同的輸出；NOT DETERMINISTIC 表示結果是不確定的，相同的輸入可能得到不同的輸出，預設為 NOT DETERMINISTIC
  • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：指明子程式使用 SQL 語句的限制，CONTAINS SQL 表示子程式不包含讀或者寫資料的語句，NO SQL 表示子程式不包含 SQL，READS SQL DATA 表示子程式包含讀資料的語句，但是不包含寫資料的語句，MODIFIES SQL DATA 表示子程式包含寫資料的語句
  • SQL SECURITY {DEFINER | INVOKER}：指明誰有許可權執行，DEFINER 表示只有定義者才能執行，INVOKER 表示擁有許可權的呼叫者可以執行，預設為 DEFINER
  • COMMENT 'string'：註釋資訊，可以用來描述儲存過程或函式

示例：建立儲存過程，查詢表 t_employee 中所有員工的薪水的儲存過程

DELIMITER $$
CREATE PROCEDURE proc_employee()
COMMENT '查詢員工薪水'
BEGIN
	SELECT salary FROM t_employee
END;
$$
DELIMITER ;

MySQL 中預設以分號 ; 作為語句結束符，儲存過程中的 SQL 語句需要分號來結束，為了避免衝突，先用 DELIMITER $$ 將 MySQL 的結束符設定為 $$，再用 DELIMITER ; 將結束符恢復為分號

2. 建立儲存函式

CREATE FUNCTION fun_name([func_param,[,...]])
	[characteristic ...] routine_body
  • fun_name 表示函式名字
  • func_param 表示函式從引數
  • characteristic 表示函式特性，取值與儲存過程的取值相同
  • routine_body 表示函式的 SQL 語句

func_param 中每個引數的語法形式如下，分別為引數名和引數型別

param_type type

還是之前的示例

DELIMITER $$
	CREATE FUNCTION func_employee(id INT(4))
		RETURNS INT(6)
	COMMENT '查詢員工薪水'
	BEGIN
		SELECT salary FROM t_employee
	END;
	$$
DELIMITER ;

變數

在 MySQL 中，可以定義和使用變數，這些變數的作用範圍在 BEGIN ... END 程式段中

1. 定義變數

DECLARE var_name[,...] type [DEFAULT value]
  • 關鍵字 DECLARE 用來宣告變數
  • 引數 var_name 是變數的名稱，可以同時定義多個變數
  • 引數 type 用來指定變數的型別
  • DEFAULT value 子句將變數預設值設定為 value，沒有使用 DEFAULT 子句時，預設值為 NULL

示例：定義變數 sql_test，資料型別為 INT 型，預設值為 10

DECLARE test_sql INT DEFAULT 10;

2. 變數賦值

SET var_name=expr[,var_name=expr]...
  • 關鍵字 SET 用來為變數賦值
  • 引數 var_name 是變數的名稱
  • 引數 expr 是賦值表示式

示例：將變數 sql_test 賦值為 30

SET test_sql = 30;

定義條件和處理程式

定義條件和處理程式是事先定義程式執行過程中可能遇到的問題，並且可以在處理程式中定義解決這些問題的能力

1. 定義條件

DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE[VALUE] sqlstate_value|mysql_error_code
  • 引數 condition_name 表示條件名稱
  • 引數 condition_value 表示條件型別
  • 引數 sqlstate_value 和 引數 mysql_error_code 都可以表示 MySQL 的錯誤

示例：定義 ERROR 1146(42S02) 錯誤，名稱為 can_not_find，可以用兩種不同的方法來定義

// 方法一:使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';
// 方法二:使用mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;

2. 定義處理程式

DECLARE handler_type HANDLER FOR condition_value[,...] proc_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:
SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code

  • handler_type 指明錯誤的處理方式：該引數有三個取值：

    • CONTINUE：表示遇到錯誤不處理，繼續向下執行
    • EXIT：表示遇到錯誤馬上退出
    • UNDO：表示遇到錯誤後撤回之前的操作

  • condition_value 表示錯誤型別，可以有以下取值：

    • SQLSTATE[VALUE]sqlstate_value 包含 5 個字元的字串錯誤值
    • condition_name 表示 DECLARE CONDITION 定義的錯誤條件名稱
    • SQLWARNING 匹配所有 01 開頭的 SQLSTATE 錯誤程式碼
    • NOT FOUND 匹配所有 02 開頭的 SQLSTATE 錯誤程式碼
    • SQLEXCEPTION 匹配所有沒有被 SQLWARNING 或 NOT FOUND 捕獲的 SQLSTATE 錯誤程式碼
    • mysql_error_code 匹配數值型別錯誤程式碼

  • proc_statement 匹配數值型別錯誤程式碼

下面是定義處理程式的幾種示例方式：

// 方法一:捕獲sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='NOT FOUND';
// 方法二:使用mysql_error_code 
DECLARE CONTINUE HANDLER FOR 1146
SET @info='NOT FOUND';
// 方法三:先定義條件,然後呼叫
ECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find
SET @info='NOT FOUND';
// 方法四:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING 
SET @info='ERROR';
// 方法五:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND
SET @info='NOT FOUND';
// 方法六:使用SQLEXCEPTION 
DECLARE EXIT HANDLER FOR 使用SQLEXCEPTION
SET @info='ERROR';

游標

查詢語句可能查詢出多條記錄，在儲存過程和函式使用游標來逐條讀取查詢結果集中的記錄。游標必須宣告在處理程式之前，宣告在變數和條件之後

1. 宣告游標

DECLARE cursor_name CURSOR
FOR select_statement;
  • 引數 cursor_name 表示游標名稱
  • 引數 select_statement 表示 SELECT 語句

下面宣告一個名為 cur_employee 的游標

DECLARE cur_employee CURSOR
FOR SELECT name,age FROM t_employee;

2. 開啟游標

OPEN cursor_name;
  • 引數 cursor_name 表示游標的名稱

下面開啟一個名為 cur_employee 的游標

OPEN cur_employee;

3. 使用游標

FETCH cursor_name
INTO var_name[,var_name...]
  • cursor_name 表示游標的名稱
  • var_name 表示將游標中的 SELECT 語句查詢出來的資訊存入該引數，該引數必須在宣告游標之前就定義好

下面開啟一個名為 cur_employee 的游標，將查詢的資料存入 emp_name 和 emp_age 這兩個變數中

FETCH cur_employee INTO emp_name,emp_age;

4. 關閉游標

CLOSE cursor_name
  • cursor_name 表示游標的名稱

流程控制

1. IF 語句

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list]...
 [ELSE statement_list]
END IF
  • 引數 search_condition 表示條件判斷語句
  • 引數 statement_list 表示不同條件的執行語句

下面是一個 IF 語句的示例

IF age>20 THEN SET @count1=@conut1+1;
	ELSEIF age=20 THEN @count2=@conut2+1;
	ELSE @count3=@count3+1;
END IF;

2. CASE 語句

CASE case_value
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END CASE
  • 引數 case_value 表示條件判斷的變數
  • 引數 when_value 表示變數的取值
  • 引數 statement_list 表示不同 when_value 的執行語句

下面的是一個 CASE 語句的示例

CASE age
	WHEN 20 THEN SET @count1=@count1+1;
	ELSE SET @count2=@count2+1;
END CASE;

3. LOOP 語句

[begin_label:]LOOP
	statement_list
END LOOP [end_label]
  • 引數 begin_label 和引數 end_label 分別表示迴圈開始和結束的標誌，必須相同，而且都可以省略
  • 引數 statement_list 表示需要迴圈執行的語句

下面是一個 LOOP 語句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
END LOOP add_num

4. LEAVE 語句

主要用於跳出迴圈

LEAVE label
  • 引數 label 表示迴圈的標誌

下面是一個 LEAVE 語句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
	IF @count1=100 THEN
		LEAVE add_num;
END LOOP add_num

5. ITERATE 語句

也是用於跳出迴圈，不過是跳出本次迴圈，直接進入下次迴圈，類似 Java 的 continue

ITERATE label
  • 引數 label 表示迴圈的標誌

下面是一個 ITERATE 語句的示例

add_num:LOOP
	SELECT @count1=@count1+1;
	IF @count1=100 THEN
		LEAVE add_num;
	ELSE IF MOD(@count1,3)=0 THEN
		ITERATE add_num;
	SELECT * FROM employee;
END LOOP add_num

6. REPEAT 語句

REPEAT 語句是由條件的控制迴圈語句，當滿足特定條件就會退出迴圈語句

[begin_label:]REPEAT
	statement_list
	UNTIL search_condition
END REPEAT [end_label]
  • 引數 statement_list 表示需要迴圈執行的語句
  • 引數 search_condition 表示結束迴圈的條件，滿足該條件即結束迴圈

下面是一個 REPEAT 語句的示例

add_num:REPEAT
	SELECT @count1=@count1+1;
	UNTIL @count1=100
END REPEAT add_num

7. WHILE 語句

WHILE 語句也是有條件控制的迴圈語句，與 REPEAT 語句不同的是，WHILE 語句是當滿足條件時執行迴圈內的語句

[begin_label:]WHILE search_condition DO
	statement_list
END WHILE [end_label]
  • 引數 statement_list 表示需要迴圈執行的語句
  • 引數 search_condition 表示結束迴圈的條件，滿足該條件即結束迴圈

下面是一個 WHILE 語句的示例

WHILE @count<100 DO
	SET @count=@count+1;
END WHILE;

操作儲存過程和函式

1. 呼叫儲存過程

在 MySQL 中，使用 CALL 語句來呼叫儲存過程

CALL proc_name([parameter[,...]])
  • proc_name 是儲存過程的名稱
  • paramter 是指儲存過程的引數

2. 呼叫儲存函式

在 MySQL 中，儲存函式的使用方法和 MySQL 內部函式是一樣的

3. 使用 SHOW STATUS 語句檢視儲存過程和函式的狀態

SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}
  • 引數 PROCEDURE 表示查詢儲存過程，FUNCTION 表示儲存函式
  • 引數 LIKE 'pattern' 用來匹配儲存過程或函式的名稱

4. 使用 SHOW CREATE 語句檢視儲存過程和函式的定義

SHOW CREATE {PROCEDURE|FUNCTION} proc_name

5. 從 information_schema.Routine 表中檢視儲存過程和函式的資訊

SELECT * FROM information_schema.Routine
WHERE ROUTINE_NAME='proc_name'

6. 修改儲存過程和函式

ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...];
characteristic:
	{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
|COMMENT 'string'
  • 引數 proc_name 表示儲存過程或函式的名稱
  • 引數 characteristic 指定儲存過程的特性：
    • CONTAINS SQL 表示子程式包含 SQL 語句，但不包含讀或寫資料的語句
    • NO SQL 表示子程式不包含 SQL 語句
    • READS SQL DATA 表示子程式中包含讀資料的語句
    • MODIFIES SQL DATA 表示子程式中包含寫資料的語句
  • SQL SECURITY{DEFINER|INVOKER} 指明誰有許可權執行：
    • DEFINER 表示只有定義者才能執行
    • INVOKER 表示呼叫者可以執行
  • COMMENT 'string' 是註釋資訊

7. 刪除儲存過程和函式

通過 DROP 語句刪除儲存過程

DROP PROCEDURE proc_name;

通過 DROP FUNCTION 語句刪除儲存函式

DROP FUNCTION func_name;

相關文章