徹底搞懂MySQL存儲過程和函數(shù)
1.0 創(chuàng)建存儲過程和函數(shù)
創(chuàng)建存儲過程和函數(shù)就是將經(jīng)常使用的一組 SQL 語句組合在一起,并將這些 SQL 語句當(dāng)作一個整體存儲在 MySQL 服務(wù)器
1. 創(chuàng)建存儲過程
CREATE PROCEDURE procedure_name ([proc_param[,...]]) [characteristic ...] routine_body
- procedure_name 表示所要創(chuàng)建的存儲過程的名字
- proc_param 表示存儲過程的參數(shù)
- characteristic 表示存儲過程的特性
- routine_body 表示存儲過程的 SQL 語句
procedure_name 不能出現(xiàn)重名
proc_param 中每個參數(shù)的語法形式如下,每個參數(shù)由三部分組成,分別為輸入/輸出類型、參數(shù)名和參數(shù)類型。輸入/輸出類型有三種類型,分別是 IN(輸入類型)、OUT(輸出類型)、INOUT(輸入/輸出類型)。param_name 表示參數(shù)名,type 表示參數(shù)類型
[ IN | OUT | INOUT ] param_name type
characteristic 指定存儲過程的特性,有以下取值:
- LANGUAGE SQL:說明存儲過程中使用的是 SQL 語言編寫的
- [NOT] DETERMINISTIC:說明存儲過程執(zhí)行的結(jié)果是否正確,DETERMINISTIC 表示結(jié)果是確定的,即每次執(zhí)行存儲過程,相同的輸入會得到相同的輸出;NOT DETERMINISTIC 表示結(jié)果是不確定的,相同的輸入可能得到不同的輸出,默認(rèn)為 NOT DETERMINISTIC
- {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:指明子程序使用 SQL 語句的限制,CONTAINS SQL 表示子程序不包含讀或者寫數(shù)據(jù)的語句,NO SQL 表示子程序不包含 SQL,READS SQL DATA 表示子程序包含讀數(shù)據(jù)的語句,但是不包含寫數(shù)據(jù)的語句,MODIFIES SQL DATA 表示子程序包含寫數(shù)據(jù)的語句
- SQL SECURITY {DEFINER | INVOKER}:指明誰有權(quán)限執(zhí)行,DEFINER 表示只有定義者才能執(zhí)行,INVOKER 表示擁有權(quán)限的調(diào)用者可以執(zhí)行,默認(rèn)為 DEFINER
- COMMENT 'string':注釋信息,可以用來描述存儲過程或函數(shù)
示例:創(chuàng)建存儲過程,查詢表 t_employee 中所有員工的薪水的存儲過程
DELIMITER $$ CREATE PROCEDURE proc_employee() COMMENT '查詢員工薪水' BEGIN SELECT salary FROM t_employee END; $$ DELIMITER ;
MySQL 中默認(rèn)以分號 ; 作為語句結(jié)束符,存儲過程中的 SQL 語句需要分號來結(jié)束,為了避免沖突,先用 DELIMITER $$ 將 MySQL 的結(jié)束符設(shè)置為 $$,再用 DELIMITER ; 將結(jié)束符恢復(fù)為分號
2. 創(chuàng)建存儲函數(shù)
CREATE FUNCTION fun_name([func_param,[,...]]) [characteristic ...] routine_body
- fun_name 表示函數(shù)名字
- func_param 表示函數(shù)從參數(shù)
- characteristic 表示函數(shù)特性,取值與存儲過程的取值相同
- routine_body 表示函數(shù)的 SQL 語句
func_param 中每個參數(shù)的語法形式如下,分別為參數(shù)名和參數(shù)類型
param_type type
還是之前的示例
DELIMITER $$ CREATE FUNCTION func_employee(id INT(4)) RETURNS INT(6) COMMENT '查詢員工薪水' BEGIN SELECT salary FROM t_employee END; $$ DELIMITER ;
2|0變量
在 MySQL 中,可以定義和使用變量,這些變量的作用范圍在 BEGIN ... END 程序段中
1. 定義變量
DECLARE var_name[,...] type [DEFAULT value]
- 關(guān)鍵字 DECLARE 用來聲明變量
- 參數(shù) var_name 是變量的名稱,可以同時定義多個變量
- 參數(shù) type 用來指定變量的類型
- DEFAULT value 子句將變量默認(rèn)值設(shè)置為 value,沒有使用 DEFAULT 子句時,默認(rèn)值為 NULL
示例:定義變量 sql_test,數(shù)據(jù)類型為 INT 型,默認(rèn)值為 10
DECLARE test_sql INT DEFAULT 10;
2. 變量賦值
SET var_name=expr[,var_name=expr]...
- 關(guān)鍵字 SET 用來為變量賦值
- 參數(shù) var_name 是變量的名稱
- 參數(shù) expr 是賦值表達(dá)式
示例:將變量 sql_test 賦值為 30
SET test_sql = 30;
3|0定義條件和處理程序
定義條件和處理程序是事先定義程序執(zhí)行過程中可能遇到的問題,并且可以在處理程序中定義解決這些問題的能力
1. 定義條件
DECLARE condition_name CONDITION FOR condition_value condition_value: SQLSTATE[VALUE] sqlstate_value|mysql_error_code
- 參數(shù) condition_name 表示條件名稱
- 參數(shù) condition_value 表示條件類型
- 參數(shù) sqlstate_value 和 參數(shù) 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 指明錯誤的處理方式:該參數(shù)有三個取值:
- CONTINUE:表示遇到錯誤不處理,繼續(xù)向下執(zhí)行
- 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 匹配數(shù)值類型錯誤代碼
proc_statement 匹配數(shù)值類型錯誤代碼
下面是定義處理程序的幾種示例方式:
// 方法一:捕獲sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' SET @info='NOT FOUND'; // 方法二:使用mysql_error_code DECLARE CONTINUE HANDLER FOR 1146 SET @info='NOT FOUND'; // 方法三:先定義條件,然后調(diào)用 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';
4|0光標(biāo)
查詢語句可能查詢出多條記錄,在存儲過程和函數(shù)使用光標(biāo)來逐條讀取查詢結(jié)果集中的記錄。光標(biāo)必須聲明在處理程序之前,聲明在變量和條件之后
1. 聲明光標(biāo)
DECLARE cursor_name CURSOR FOR select_statement;
- 參數(shù) cursor_name 表示光標(biāo)名稱
- 參數(shù) select_statement 表示 SELECT 語句
下面聲明一個名為 cur_employee 的光標(biāo)
DECLARE cur_employee CURSOR FOR SELECT name,age FROM t_employee;
2. 打開光標(biāo)
OPEN cursor_name;
- 參數(shù) cursor_name 表示光標(biāo)的名稱
下面打開一個名為 cur_employee 的光標(biāo)
OPEN cur_employee;
3. 使用光標(biāo)
FETCH cursor_name INTO var_name[,var_name...]
- cursor_name 表示光標(biāo)的名稱
- var_name 表示將光標(biāo)中的 SELECT 語句查詢出來的信息存入該參數(shù),該參數(shù)必須在聲明光標(biāo)之前就定義好
下面打開一個名為 cur_employee 的光標(biāo),將查詢的數(shù)據(jù)存入 emp_name 和 emp_age 這兩個變量中
FETCH cur_employee INTO emp_name,emp_age;
4. 關(guān)閉光標(biāo)
CLOSE cursor_name
- cursor_name 表示光標(biāo)的名稱
5|0流程控制
1. IF 語句
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF
- 參數(shù) search_condition 表示條件判斷語句
- 參數(shù) statement_list 表示不同條件的執(zhí)行語句
下面是一個 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
- 參數(shù) case_value 表示條件判斷的變量
- 參數(shù) when_value 表示變量的取值
- 參數(shù) statement_list 表示不同 when_value 的執(zhí)行語句
下面的是一個 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]
- 參數(shù) begin_label 和參數(shù) end_label 分別表示循環(huán)開始和結(jié)束的標(biāo)志,必須相同,而且都可以省略
- 參數(shù) statement_list 表示需要循環(huán)執(zhí)行的語句
下面是一個 LOOP 語句的示例
add_num:LOOP SELECT @count1=@count1+1; END LOOP add_num
4. LEAVE 語句
主要用于跳出循環(huán)
LEAVE label
- 參數(shù) label 表示循環(huán)的標(biāo)志
下面是一個 LEAVE 語句的示例
add_num:LOOP SELECT @count1=@count1+1; IF @count1=100 THEN LEAVE add_num; END LOOP add_num
5. ITERATE 語句
也是用于跳出循環(huán),不過是跳出本次循環(huán),直接進(jìn)入下次循環(huán),類似 Java 的 continue
ITERATE label
ITERATE label
- 參數(shù) label 表示循環(huán)的標(biāo)志
下面是一個 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 語句是由條件的控制循環(huán)語句,當(dāng)滿足特定條件就會退出循環(huán)語句
[begin_label:]REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
- 參數(shù) statement_list 表示需要循環(huán)執(zhí)行的語句
- 參數(shù) search_condition 表示結(jié)束循環(huán)的條件,滿足該條件即結(jié)束循環(huán)
下面是一個 REPEAT 語句的示例
add_num:REPEAT SELECT @count1=@count1+1; UNTIL @count1=100 END REPEAT add_num
7. WHILE 語句
WHILE 語句也是有條件控制的循環(huán)語句,與 REPEAT 語句不同的是,WHILE 語句是當(dāng)滿足條件時執(zhí)行循環(huán)內(nèi)的語句
[begin_label:]WHILE search_condition DO statement_list END WHILE [end_label]
- 參數(shù) statement_list 表示需要循環(huán)執(zhí)行的語句
- 參數(shù) search_condition 表示結(jié)束循環(huán)的條件,滿足該條件即結(jié)束循環(huán)
下面是一個 WHILE 語句的示例
WHILE @count<100 DO SET @count=@count+1; END WHILE;
6|0操作存儲過程和函數(shù)
1. 調(diào)用存儲過程
在 MySQL 中,使用 CALL 語句來調(diào)用存儲過程
CALL proc_name([parameter[,...]])
- proc_name 是存儲過程的名稱
- paramter 是指存儲過程的參數(shù)
2. 調(diào)用存儲函數(shù)
在 MySQL 中,存儲函數(shù)的使用方法和 MySQL 內(nèi)部函數(shù)是一樣的
3. 使用 SHOW STATUS 語句查看存儲過程和函數(shù)的狀態(tài)
SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}
- 參數(shù) PROCEDURE 表示查詢存儲過程,F(xiàn)UNCTION 表示存儲函數(shù)
- 參數(shù) LIKE 'pattern' 用來匹配存儲過程或函數(shù)的名稱
4. 使用 SHOW CREATE 語句查看存儲過程和函數(shù)的定義
SHOW CREATE {PROCEDURE|FUNCTION} proc_name
5. 從 information_schema.Routine 表中查看存儲過程和函數(shù)的信息
SELECT * FROM information_schema.Routine WHERE ROUTINE_NAME='proc_name'
6. 修改存儲過程和函數(shù)
ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...]; characteristic: {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA} |SQL SECURITY{DEFINER|INVOKER} |COMMENT 'string'
- 參數(shù) proc_name 表示存儲過程或函數(shù)的名稱
- 參數(shù) characteristic 指定存儲過程的特性:
- CONTAINS SQL 表示子程序包含 SQL 語句,但不包含讀或?qū)憯?shù)據(jù)的語句
- NO SQL 表示子程序不包含 SQL 語句
- READS SQL DATA 表示子程序中包含讀數(shù)據(jù)的語句
- MODIFIES SQL DATA 表示子程序中包含寫數(shù)據(jù)的語句
- SQL SECURITY{DEFINER|INVOKER} 指明誰有權(quán)限執(zhí)行:
- DEFINER 表示只有定義者才能執(zhí)行
- INVOKER 表示調(diào)用者可以執(zhí)行
- COMMENT 'string' 是注釋信息
7. 刪除存儲過程和函數(shù)
通過 DROP 語句刪除存儲過程
DROP PROCEDURE proc_name;
通過 DROP FUNCTION 語句刪除存儲函數(shù)
DROP FUNCTION func_name;
總結(jié)
到此這篇關(guān)于MySQL存儲過程和函數(shù)的文章就介紹到這了,更多相關(guān)MySQL存儲過程和函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Navicat遠(yuǎn)程連接SQL Server并轉(zhuǎn)換成MySQL步驟詳解
最近遇到一個需求是將SQL Server轉(zhuǎn)換為 MySQL的格式,由于不想在本地安裝 SQL Server,所以決定在遠(yuǎn)程的 Windows 服務(wù)器上安裝,并在本地使用Navicat遠(yuǎn)程連接它,然而在實(shí)現(xiàn)過程中遇到了諸多問題,記錄于此。感興趣的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧。2017-01-01windows10下同時安裝兩個mysql服務(wù)的方法步驟
我的電腦已經(jīng)安裝了8.0.18,現(xiàn)在再安裝個8.0.25,本文主要介紹了windows10下同時安裝兩個mysql服務(wù)的方法步驟,具有一定的參考價值,感興趣的可以了解一下2023-09-09MySQL 5.7并發(fā)復(fù)制隱式bug實(shí)例分析
這篇文章主要給大家介紹了關(guān)于MySQL 5.7并發(fā)復(fù)制隱式bug的相關(guān)資料,文中介紹的非常詳細(xì),對大家學(xué)習(xí)或者使用mysql5.7具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2018-11-11mysql 大表批量刪除大量數(shù)據(jù)的實(shí)現(xiàn)方法
這篇文章主要介紹了mysql 大表批量刪除大量數(shù)據(jù)的實(shí)現(xiàn)方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02MySQL函數(shù)CONCAT、CONCAT_WS、GROUP_CONCAT用法詳解
這篇文章主要介紹了MySQL函數(shù)CONCAT、CONCAT_WS、GROUP_CONCAT用法詳解,CONCAT 函數(shù)用于將兩個字符串連接為一個字符串,本文通過實(shí)例代碼詳細(xì)講解,需要的朋友可以參考下2023-02-02Mysql的數(shù)據(jù)庫遷移到另一個機(jī)器上的方法詳解
今天小編就為大家分享一篇關(guān)于Mysql的數(shù)據(jù)庫遷移到另一個機(jī)器上的方法詳解,小編覺得內(nèi)容挺不錯的,現(xiàn)在分享給大家,具有很好的參考價值,需要的朋友一起跟隨小編來看看吧2019-04-04mysql報錯:1406 Data too long for colu
這篇文章給大家介紹了多種解決mysql報錯:1406, Data too long for column的解決方法,如果有遇到相同問題的朋友可以參考閱讀本文,對解決問題有一定的幫助,需要的朋友可以參考下2023-09-09