簡介
存盤程序（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 陳述句集，它存盤在資料庫中，一次編譯后永久有效，用戶通過指定存盤程序的名字并給出引數（如果該存盤程序帶有引數）來執行它，存盤程序是資料庫中的一個重要物件，
存盤程序的使用
創建存盤程序
語法：
CREATE PROCEDURE 存盤程序名( IN|OUT|INOUT 引數名 資料型別 , ...)
BEGIN
...
END;
MySQL存盤程序的引數型別：
-
IN，表示存盤程序的輸入引數，該引數的值將會傳遞給存盤程序，在存盤程序中可以對該引數進行修改，但是在存盤程序回傳時，該引數值不會被回傳，相當于在存盤程序中對該引數的修改對呼叫者來說是不可見的，
-
OUT，表示存盤程序的輸入引數，該引數的值會在存盤程序中初始化為NULL，當存盤程序回傳時，該值也會被回傳，呼叫者可以看到被修改后的值，
-
INOUT，表示存盤程序的輸入輸出引數，該引數由呼叫者初始化，在存盤程序中的做的任何更改都會被回傳，呼叫者可以看到修改后的值，
存盤程序創建示例：
創建存盤程序student_procedure，student_procedure有一個輸入引數age和一個輸出引數num，查詢tb_student表學生年齡大于等于輸入引數age的人數，并將人數設定到num，
CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
BEGIN
SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
END;
MySQL命令列創建存盤程序：
如果是在MySQL命令列創建存盤程序，則需要臨時的修改陳述句分隔符，因為MySQL默認陳述句分隔符是;，會使存盤程序中的陳述句被直接決議而導致語法錯誤，
-- 設定//為陳述句分隔符
mysql> DELIMITER //
mysql> CREATE PROCEDURE student_procedure(IN age TINYINT, OUT num INT)
-> BEGIN
-> SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;
-> END;
-> //
Query OK, 0 rows affected
--恢復為原來的分隔符
mysql> DELIMITER ;
呼叫存盤程序
tb_student表資料：
+----+------+-----+-------------+-----------+----------+
| id | name | age | phone | address | class_id |
+----+------+-----+-------------+-----------+----------+
| 1 | 小明 | 18 | 188xxxx1234 | xxxxxxxxx | 1 |
| 2 | 小米 | 28 | 188xxxx1234 | xxxxxxxxx | 2 |
| 3 | 小看 | 28 | 188xxxx1234 | xxxxxxxxx | 3 |
| 4 | 小阿 | 38 | 188xxxx1234 | xxxxxxxxx | 3 |
| 5 | 小鬼 | 48 | 188xxxx1234 | xxxxxxxxx | 3 |
+----+------+-----+-------------+-----------+----------+
呼叫存盤程序，查詢年齡大于38的學生人數：
-- 呼叫存盤程序
mysql> CALL student_procedure(38, @num);
Query OK, 1 row affected
-- 查看回傳結果
mysql> select @num;
+------+
| @num |
+------+
| 2 |
+------+
查看存盤程序的定義
語法：
SHOW CREATE PROCEDURE proc_name;
如查看student_procedure的定義：
mysql> SHOW CREATE PROCEDURE student_procedure;
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure | sql_mode | Create Procedure | character_set_client | collation_connection | Database Collation |
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| student_procedure | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` PROCEDURE `student_procedure`(IN age TINYINT, OUT num INT) BEGIN SELECT COUNT(*) INTO num FROM tb_student t WHERE t.age>=age;END | utf8 | utf8_general_ci | utf8_general_ci |
+-------------------+----------------------------------------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
修改存盤程序
語法：
ALTER PROCEDURE proc_name [characteristic ...]
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
ALTER PROCEDURE 陳述句用于修改存盤程序的某些特征，如果要修改存盤程序的內容，可以先洗掉原存盤程序，再以相同的命名創建新的存盤程序，
洗掉存盤程序
語法：
DROP PROCEDURE [ IF EXISTS ] proc_name
如洗掉student_procedure：
mysql> DROP PROCEDURE student_procedure;
Query OK, 0 rows affected
mysql> CALL student_procedure(38, @num);
1305 - PROCEDURE student_procedure does not exist
流程控制陳述句
IF陳述句
語法：
IF 判斷條件 THEN 處理陳述句
[ELSEIF 判斷條件 THEN 處理陳述句]...
[ELSE 處理陳述句]
END IF
示例：
CREATE PROCEDURE test1(IN sex TINYINT)
BEGIN
IF sex=1 THEN SET @sex='男';
ELSEIF sex=0 THEN SET @sex='女';
ELSE SET @sex='未知';
END IF;
END;
CASE陳述句
CASE陳述句有兩種寫法：
語法1：
CASE 值
WHEN 值1 THEN 處理陳述句
[WHEN 值2 THEN 處理陳述句]...
[ELSE 處理陳述句]
END CASE
語法2：
CASE
WHEN 條件判斷 THEN 處理陳述句
[WHEN 條件判斷 THEN 處理陳述句] ...
[ELSE 處理陳述句]
END CASE
示例：
-- 寫法1
CREATE PROCEDURE test2(IN sex TINYINT)
BEGIN
CASE sex
WHEN 1 THEN SET @sex='男';
WHEN 0 THEN SET @sex='女';
ELSE SET @sex='未知';
END CASE;
END;
-- 寫法2
CREATE PROCEDURE test3(IN sex TINYINT)
BEGIN
CASE
WHEN sex=1 THEN SET @sex='男';
WHEN sex=0 THEN SET @sex='女';
ELSE SET @sex='未知';
END CASE;
END;
LOOP陳述句
LOOP回圈是一個死回圈，一般情況需要配合LEAVE陳述句和ITERATE陳述句使用，LEAVE陳述句表示跳出該回圈（類似Java中的break），ITERATE陳述句表示跳出本次回圈（類似Java中的continue），
語法：
[別名:]LOOP
處理邏輯
END LOOP [別名]
示例：
CREATE PROCEDURE test4()
BEGIN
SET @num=0;
add_num:LOOP
SET @num=@num+1;
IF @num=10 THEN LEAVE add_num;
END IF;
END LOOP add_num;
END;
REPEAT陳述句
REPEAT陳述句是自帶條件判斷的回圈陳述句，每次陳述句執行完畢后，會對條件進行判斷，如果為true則退出回圈，否則繼續回圈，（類似Java中的do while回圈）
語法：
[別名:] REPEAT
處理陳述句
UNTIL 條件判斷
END REPEAT [別名]
示例：
CREATE PROCEDURE test5()
BEGIN
SET @num=0;
add_num:REPEAT
SET @num=@num+1;
UNTIL @num=10 END REPEAT add_num;
END;
WHILE陳述句
WHILE陳述句也是自帶條件判斷的回圈，和REPEAT陳述句的區別在于WHILE陳述句會先進行條件判斷，當條件判斷為true時才繼續執行回圈中的陳述句，為false則直接退出回圈，（類似于Java中的while回圈）
語法：
[別名:] WHILE 條件判斷 DO
處理邏輯
END WHILE [別名]
示例：
CREATE PROCEDURE test6()
BEGIN
SET @num=0;
add_num:WHILE @num<10 DO
SET @num=@num+1;
END WHILE add_num;
END;
游標
游標是用來逐行處理某個查詢的結果集，
游標的宣告必須出現在HANDLER宣告之前宣告，變數和條件宣告之后宣告，
創建游標：
DECLARE 游標名稱 CURSOR FOR sql查詢;
打開游標：
OPEN 游標名稱;
使用游標：
FETCH 游標名稱 INTO 變數1 [,變數2]...
將結果集中的資料保存到對應的變數當中去，游標第一次使用時默認讀取結果集中的第一行，一般配合回圈陳述句逐行處理整個結果集，
關閉游標：
CLOSE 游標名稱;
CLOSE釋放游標使用的所有內部記憶體和資源，因此每個游標不再需要時都應該關閉，游標關閉后不能使用，如果需要使用則需要重新打開游標，
示例
查詢tb_student表，將所有學生名稱連接成一個字串設定到變數@name_Str中，
CREATE PROCEDURE test7()
BEGIN
-- 宣告區域變數student_name，用于接收資料集中的資料
DECLARE student_name VARCHAR(10);
-- 宣告區域變數done，用于判斷是否退出回圈，默認值為FALSE
DECLARE done INT DEFAULT FALSE;
-- 宣告游標my_cursor
DECLARE my_cursor CURSOR FOR SELECT `name` FROM tb_student;
-- 宣告continue handler句柄，當出現SQLSTATE '02000'時將done設定為TRUE
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;
-- 設定用戶變數@name_Str為空字串
SET @name_Str='';
-- 打開游標
OPEN my_cursor;
-- 開始LOOP回圈
concat_name:LOOP
-- 將資料集中的一行資料存放到指定的變數中
FETCH my_cursor INTO student_name;
-- 判斷是否退出回圈
IF done THEN LEAVE concat_name;
END IF;
-- 連接學生名稱字串
SET @name_Str = CONCAT(@name_Str,student_name);
END LOOP concat_name;
-- 關閉游標
CLOSE my_cursor;
END;
結果：
mysql> call test7();
Query OK, 0 rows affected
mysql> select @name_Str;
+----------------------+
| @name_Str |
+----------------------+
| 小明小米小看小阿小鬼 |
+----------------------+
關于SQLSTATE '02000'
在使用游標時，可以通過FETCH將資料集中的資料保存到變數中進行處理，但是當整個資料集已經FETCH結束的時候，再去FETCH就會拋例外：
1329 - No data - zero rows fetched, selected, or processed
該例外對應的SQLSTATE為02000，所以需要指定句柄捕獲這種例外情況來給標志賦值，后續就可以通過這個標志來判斷資料集回圈讀取結束，
例外資訊詳見：Error Reference
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/274020.html
標籤：其他
上一篇：DQL查詢資料語言（MySQL）
下一篇：mysql (2)-單表查詢