場景

存儲過程

存儲過程是一組為了完成特定功能的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 () BEGINSELECT*FROMtest_student;
END;

調用存儲過程

CALL 語句用來調用一個使用 PROCEDURE 創建好的存儲過程，基本語法格式如下：

CALL sp name ([parameter [, ···]]}

CALL 調用語句中的 sp_name 為存儲過程的名稱， parameter 為存儲過程的參數。

創建存儲過程，查詢某個班級的平均年齡，然后調用該存儲過程

CREATE PROCEDURE avg_student (IN dep INT,OUT avg FLOAT ) BEGINSELECTavg( sage ) INTO avgFROMtest_studentWHEREdid = 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 ) BEGINRETURN ( 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 thenSELECT '價格太高';
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:loopSet aa=aa+1;
END loop Add_sum;

該例執行的是aa加1的操作，循環中沒有跳出循環的語旬，所以該循環為死循環

LEAVE語句

LEAVE 語句主要用來跳出任何被標注的流程控制語句

DECLARE aa int default 0;
Add_sum:loopSet 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)
BEGINLa: LOOPSET 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;
REPEATSET 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';