場景
儲存過程
儲存過程是一組為了完成特定功能的SQL語句集合。使用儲存過程的目的是將常用或複雜的工作預先用SQL語句寫好並用一個指定名稱儲存起來，
這個過程經編譯和最佳化後儲存在資料庫伺服器中，因此稱為儲存過程。
當以後需要資料庫提供與己定義好的儲存過程的功能相同的服務時，只需呼叫"CALL 儲存過程名字"即可自動完成。
建立儲存過程
建立儲存過程需要使用 CREATE PROCEDU 語句 基本語法格式如下：
CREATE PROCEDURE sp_name ( [proc_parameter] ) [characteristic .. ·] routine body
CREATE PROCEDURE 為用來建立儲存函式的關鍵字；
sp_name為儲存過程的名稱;
proc_parameter指定儲存過程的引數列表 列表形式如下:
[ IN I OUT I INOUT ) param name type
IN 表示輸入引數， OUT 表示輸出引數， INOUT 表示既可以輸入也可以輸出 param_name 表示引數名稱 type 表示引數的型別，
該型別可以是 MySQL 資料庫中的任意型別。
characteristic 指定儲存過程的特性，有以下取值
(1)LANGUAGE SQL:說明 routine_body部分是由 SQL 語句組成的，SQL是LANGUAGE特性的唯一值。
(2)[NOT] DETERMINISTIC: 指明儲存過程執行的結果是否正確。 DETERMINISTIC表示結果是確定的，
當每次執行儲存過程時相同的的輸入會得到相同的輸出；
NOT DETERMINISTIC 表示結果是不確定的，相同的輸入可能得到不同的輸出。
如果沒有指定任意一個值，預設為NOT DETERMINISTIC。
(3){ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程式使用 SQL語句的限制。
CONTAINS SQL 表明子程式包含SQL語句，但是不包含讀或寫資料的語句。
NOSQL 表明子程式不包含SQL語句。
READS SQL DATA 說明子程式包含讀資料的語句。
MODIFIES SQL DATA 表明子程式包含寫資料的語句。在預設情況下，系統會指定為 CONTAINS SQL。
(4)SQL SECURITY { DEFINER |INVOKER }: 指明誰有許可權來執行。 DEFINER表示只有定義者才能執行。
INVOKER 表示擁有許可權的呼叫者可以執行。在預設情況下 系統指定為 DEFINER。
(5)COMMENT 'string'： 註釋資訊，可以用來描述儲存過程或函式。
routine_ body是SQL程式碼的內容，可以用 BEGIN··· END 來表示 SQL 程式碼的開始和結束。
建立測試表test_student
CREATE TABLE test_student
(
sid int PRIMARY KEY,
sname
VARCHAR(20),
ssex CHAR(2),
sage
int,
did int
);
插入測試資料
INSERT INTO test_student(sid,sname,sage,did)
VALUES (1,'張三',13,101),
(2,'李四',14,101),
(3,'王五',15,102),
(4,'趙六',16,101);
建立檢視student表的儲存過程
CREATE PROCEDURE Proc_student () BEGIN
SELECT
*
FROM
test_student;
END;
呼叫儲存過程
CALL 語句用來呼叫一個使用 PROCEDURE 建立好的儲存過程，基本語法格式如下：
CALL sp name ([parameter [, ···]]}
CALL 呼叫語句中的 sp_name 為儲存過程的名稱， parameter 為儲存過程的引數。
建立儲存過程，查詢某個班級的平均年齡，然後呼叫該儲存過程
CREATE PROCEDURE avg_student (
IN
dep INT,
OUT avg FLOAT ) BEGIN
SELECT
avg( sage ) INTO avg
FROM
test_student
WHERE
did = dep;
END;
CALL avg_student(101,@aa);
--
查詢返回的結果
SELECT @aa;
檢視儲存過程
在儲存過程建立好以後，使用者可以透過如下三種方式進行檢視
方式1：使用SHOW PROCEDURE STATUS 語句檢視儲存過程的狀態
SHOW PROCEDURE STATUS LIKE 'avg_%';
獲取資料庫中所有名稱以字母avg_開頭的儲存過程的資訊。
只能檢視儲存過程操作哪一個資料庫，儲存過程的名稱、型別，誰定義的， 建立和修改時間、字元編碼等資訊，
不能檢視儲存過程的具體定義。 -- 如果需要檢視詳細定 ，需要使用 SHOW CREATE PROCEDURE 語句。
方式2：使用 SHOW CREATE PROCEDURE 語句
SHOW CREATE PROCEDURE avg_student;
它返回一個可用來重新建立已命名儲存過程的確切字串
方式3：透過information_schema.Routines 檢視儲存過程的資訊。
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'avg_student';
修改儲存過程
在儲存過程建立完成後，如果需要修改，可以使用ALTER語句進行修改
修改儲存過程的定義，將讀寫許可權改為MODIFIES SQL DATA，並指明呼叫者可以執行。
ALTER PROCEDURE avg_student
MODIFIES SQL DATA
SQL SECURITY
INVOKER;
刪除儲存過程
使用DROP PROCEDURE 語句
DROP PROCEDURE avg_student;
儲存函式
建立儲存函式
建立儲存函式需要使用 CREATE FUNCTION 語旬
基本語法格式如下
CREATE FUNCTION func_name ( [func_parameter] ) RETURNS type [characteristic···] routine_ body
CREATE FUNCTION 為用來建立儲存函式的關鍵字： func_name 表示儲存函式的名稱；func_parameter 為儲存過程的引數列表，
引數列表形式如下
{IN | OUT | INOUT } param_name type
其中,IN 表示輸入引數,OUT 表示輸出引數,INOUT 表示既可以輸入也可以輸出， param_name 表示引數名稱，
type 表示引數的型別，該型別可以是 MySQL 資料庫中的任意型別。
CREATE FUNCTION name_student ( aa INT ) RETURNS CHAR (
50 ) BEGIN
RETURN ( SELECT sname FROM test_student
WHERE did = aa );
END
引數定義 aa 返回一個CHAR型別結果。 SELECT語句從student 表申查詢 did 等於aa並將該記錄中的sname欄位返回。
如果提示：This function has none of DETERMINISTIC, NO SQL, or
READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe
log_bin_trust_function_creators variable)
執行如下臨時生效，重啟後失效：
set global log_bin_trust_function_creators= TRUE;
呼叫儲存函式
MySQL中，儲存函式的使用方法和MySQL內部函式的使用方法是一樣的。
使用者自己定義的儲存函式與MySQL內部函式的性質相同，區別在於儲存函式是使用者自己定義的，
而內部函式是MySQL開發者定義的。
SELECT name_student(102);
檢視儲存函式
可以使用 SHOW FUNCTION STATUS 語句或 SHOW CREATE FUNCTION 語句來檢視
也可以直接從系統的 information_chema 資料庫中查詢。
SHOW FUNCTION STATUS LIKE 'name_student';
SHOW CREATE FUNCTION name_student;
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME
= 'name_student';
刪除儲存函式
刪除儲存函式可以使用 DROP FUNCTION 語句
DROP FUNCTION name_student;
自定義函式
定義變數
MySQL 中使用 DECLARE 關鍵字來定義變數，定義變數的基本語法格式如下
DECLARE var_name[,···] type [DEFAULT value]
DECLARE 關鍵字用來宣告變數。
var name 數是變數的名稱，可以同時定義多個變數。
type引數用來指定變數的型別。
DEFAULT value 子句為變數提供一個預設值。預設值可以是一個常數，也可以是一個表示式。
如果沒有給變數指定預設值，初始值為NULL
DECLARE studentid char(10) DEFAULT '一年級';
變數賦值
變數賦值使用SET語句
DECLARE v1;
SET v1=66;
-- MySQL中還可以使用SELECT···
INTO 語旬為變數賦值
DECLARE student_name char(50);
SELECT sname into student_name
FROM
test_student
WHERE sid= 2;
流程控制語句
IF語句
IF price>=30 then
SELECT
'價格太高';
ELSE SELECT '價格適中';
END
IF;
--
判斷price的值，如果price大於等於30,輸出字串'價格太高'，否則輸出字串'價格適中'。IF語句都需要END IF來結束
CASE語句用來進行條件判斷，
CASE did
WHEN 101 THEN SELECT '一年級';
WHEN 102
THEN SELECT '二年級'；
END CASE;
LOOP語句
--
LOOP語句可以重複執行特定的語句，實現簡單的迴圈，但是 LOOP 語句本身並不進行條件判斷，
--
沒有停止迴圈的語旬，必須使用LEAVE語句才能停止迴圈，跳出迴圈過程。
-- 基本格式
-- [begin_label:] LOOP
-- statement
list
-- END LOOP [end_label]
--
語法中的 begin_label 引數和 end_label 引數分別表示迴圈開始和結束的標誌，這兩個標誌必須相同 ，
-- 而且都可以省略 statement_list參數列示需要迴圈執行的語句
DECLARE aa int default 0;
Add_sum:loop
Set aa=aa+1;
END loop Add_sum;
--
該例執行的是aa加1的操作，迴圈中沒有跳出迴圈的語旬，所以該迴圈為死迴圈
LEAVE語句
-- LEAVE
語句主要用來跳出任何被標註的流程控制語句
DECLARE aa int default 0;
Add_sum:loop
Set aa=aa+1;
IF aa>50 then leave Add_sum;
END
IF;
END loop Add_sum;
ITERATE語句
--
ITERATE語句也是用來跳出迴圈的語句，但ITERATE只可以出現在LOOP 、REPEAT和WHILE語句內。
-- ITERATE語句是跳出本次迴圈，然後直接進入下次迴圈，ITERATE的意思是再次迴圈
CREATE PROCEDURE pp(a INT)
BEGIN
La: LOOP
SET a=a+1;
IF a<10 THEN ITERATE la;
END
IF;
LEAVE la;
END LOOP la;
SET @x=a;
END;
-- 該例中的a變數為輸入引數，在LOOP迴圈中a的值加1，在 IF 條件語句中進行判斷，如果a的值小
-- 於10,則使用ITERATE la 跳出本次迴圈，又一次從頭開始 LOOP
迴圈，a的值再次加1;若a大於等於
-- 10,則ITERATE la 語句不執行 執行下面的 LEAVE la
語句跳出整個迴圈。
REPEAT 語句
-- REPEAT
語句建立的是帶條件判斷的迴圈過程。迴圈語句每次執行完都會對錶達式進行判斷，若表達
--
式為真，則結束迴圈，否則再次重複執行迴圈中的語句。當條件判斷為真時就會跳出迴圈語句。 REPEAT
--
語句的基本語法格式
-- [begin_label:] REPEAT
-- statement_list
-- UNTIL
search_condition
-- END REPEAT [end_label]
-- 語法中的begin_label end_label為開始標記和結束標記 均可以省略。 statement_list
參數列示循
-- 環的執行語旬， search_condition參數列示結束迴圈的條件，該條件為真時結束跳出迴圈
該引數為假時再
-- 次執行迴圈語句
DECLARE ss int DEFAULT 0;
REPEAT
SET ss=ss+1;
UNTIL ss>=10;
END REPEAT;
WHILE 語句
-- WHILE 語句也是有條件控制的迴圈語句
WHILE 語句和REPEAT 語句是不同的。 WHILE語句在
-- 執行時先對條件表示式進行判斷
若該條件表示式為真 則執行迴圈內的語句，否則退出迴圈過程
DECLARE ss int DEFAULT 0;
WHILE ss<=10 DO
SET ss=ss+1;
END WHILE;
游標/遊標的使用
在儲存過程或自定義函式中的查詢可能會返回多條記錄。可以使用游標來逐條讀取查詢結果集中的記錄。
游標在很多其他書 被稱為遊標。游標的使用包括游標的宣告、開啟游標、使用游標和關閉游標。
需要注意的是，游標必須在處理程式之前宣告 在變數和條件之後宣告。
宣告游標
-- 宣告一個名為cursor_student的游標
DECLARE cursor_student CURSOR FOR SELECT sid,sname FROM
test_student;
開啟游標
OPEN cursor_student;
使用游標
--
使用名稱為cursor_student的游標，將查詢得到的資料儲存在變數e_no e_name
FETCH
cursor_student INTO e_no,e_name;
關閉游標
CLOSE cursor_student;
定義條件和處理程式
在程式的執行過程中可能會遇到問題，此時可以透過定義條件和處理程式來事先定義這些問題，
並且可以在處理程式中定義在遇到這些問題時應該採用什麼樣的處理方式，提出解決方法 保證儲存過程或自
定義函式在遇到警告或錯誤時能夠繼續執行，從而增強程式處理問題的能力，避免程式出現異常，被停止執行
定義條件的語法格式
-- 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
的錯誤。其中 sqlstate_value 為長度為5的字元
--
串型別的錯誤程式碼，mysql_error_code 為數值型別錯誤程式碼。
-- 示例：定 RROR 1110(44000)的錯誤，名稱為 command not find
DECLARE command_not_find CONDITION FOR sqlstate '44000';
DECIMAL command_not_find CONDITION FOR 1110;
定義處理程式
--
其語法格式如下：
-- DECLARE handler_type HANDLER FOR
condition_value [,...] sp_statement
-- 引數說明
-- handler_type : CONTINUE | EXIT | UNDO
-- handler_type 為錯誤處理方式，取上述3個值中的一個。 CONTINUE 表示遇到錯誤不處理，繼續執行
-- EXIT 表示遇到錯誤馬上退出；UNDO 表示遇到錯誤後撤銷之前的操作。
-- condition_value 表示錯誤的型別，該引數可以取以下值。
-- SQLSTATE[VALUE] sqlstate_value 字串錯誤值。
-- condition_name ：使用DECLARE CONDITION 定義的錯誤條件名稱。
-- SQLWARNING: NOT FOUND 匹配所有以 02 開頭的 SQLSTATE 錯誤程式碼
SQLEXCEPTION 匹配所
-- 有沒有被SQLWARNING或NOT FOUND 捕獲的 SQLSTATE
錯誤程式碼。
-- 示例 定義捕獲 sqlstate_value 值。如果遇到 sqlstate_value 值為23SOO
執行 CONTINUE 操作，並且給變數x賦值20
DECLARE CONTINUE HANDLER FOR
SQLSTATE '23S00'
SET @x= 20;
-- 示例 該方法捕獲 mysql_error_code
值。如果mysql_error_code值為1146，執行CONTINUE操作，並且給變數x賦值20
DECLARE CONTINUE HANDLER FOR 1146
SET
@x= 20;
-- 示例 該方法先定義NO TABLE 條件，遇到1150錯誤時執行CONTINUE操作，並輸出"NO
TABLE"資訊。
DECLARE NO_TABLE CONDITION FOR 1150;
DECLARE CONTINUE HANDLER FOR NO_TABLE
SET @info= 'NO_TABLE';
-- 示例
SQLWARNING捕獲所有以01開頭的sqlstate_value值，然後執行EXIT操作，並且輸出 "ERROR"資訊。
DECLARE EXIT HANDLER FOR SQLWARNING SET @info= 'ERROR';