MySQL之存儲過程與存儲函數(shù)使用及說明
1、創(chuàng)建存儲過程
存儲過程就是一條或者多條 SQL 語句的集合，可以視為批文件。它可以定義批量插入的語句，也可以定義一個接收不同條件的 SQL。
創(chuàng)建存儲過程的語句為 “create procedure”，創(chuàng)建存儲函數(shù)的語句為 “create function”。
調用存儲過程的語句為 “CALL”。
調用存儲函數(shù)的形式就像調用 MySQL 內部函數(shù)一樣。
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
INSERT INTO t_student VALUES(NULL,'大宇',22),(NULL,'小宇',20);
如上述，t_student 表中的數(shù)據(jù)有兩條。如果我們要分別查詢出來這兩條數(shù)據(jù)，顯然就是根據(jù) ID 來查詢。查詢出來了第一條數(shù)據(jù)后，我們可能會去做其它的操作。等過兩天，我們要查詢另外一條記錄的時候，可能又要再寫一次這樣的查詢語句。
存儲過程和存儲函數(shù)應運而生，這樣就可以對某些 SQL 語句進行封裝，從而實現(xiàn)功能的復用。
定義一個根據(jù) ID 查詢學生記錄的存儲過程：
DROP PROCEDURE IF EXISTS getStuById; DELIMITER // -- 定義存儲過程結束符號為// CREATE PROCEDURE getStuById(IN stuId INT(11),OUT stuName VARCHAR(255),OUT stuAge INT(11)) -- 定義輸入與輸出參數(shù) COMMENT 'query students by their id' -- 提示信息 SQL SECURITY DEFINER -- DEFINER指明只有定義此SQL的人才能執(zhí)行，MySQL默認也是這個 BEGIN SELECT name ,age INTO stuName , stuAge FROM t_student WHERE id = stuId; -- 分號要加 END // -- 結束符要加 DELIMITER ; -- 重新定義存儲過程結束符為分號
語法：“create procedure sp_name(定義輸入輸出參數(shù)) 【存儲特性】begin SQL語句；END”
IN 表示輸入?yún)?shù)；OUT 表示輸出參數(shù)；INOUT 表示既可以輸入也可以輸出的參數(shù)；sp_name 為存儲過程的名字。
如果此存儲過程沒有任何輸入、輸出，其實就沒有什么意義了，但是 sp_name() 的括號不能省略。
查看剛才創(chuàng)建的存儲過程。
SHOW PROCEDURE STATUS LIKE 'g%'
下面是調用儲存過程。對于存儲過程提供的臨時變量而言，MySQL 規(guī)定要加上 “@” 開頭。
#study 是當前數(shù)據(jù)庫名稱 CALL study.getStuById(1,@name,@age); SELECT @name AS stuName,@age AS stuAge;
CALL getStuById(2,@name,@age); SELECT @name AS stuName,@age AS stuAge;
這樣的好處是，如果一段較為復雜的 SQL 語句，我們可能過了幾天再去寫它，又費時費力。存儲過程可以
封裝我們寫過的 SQL，在下次只需要調用它的時候，直接提供參數(shù)并指明查詢結果輸出到哪些變量中即可。
提示：如果存儲過程一次查詢出兩個記錄，將會提示出錯。"[Err] 1172 - Result consisted of more than one row"。
所以需要在存儲過程的 SQL 后面加上 “limit 1”。從位偏移量為 0 的，即從查詢結果的第一條數(shù)據(jù)開始，查詢一條記錄。
2、創(chuàng)建存儲函數(shù)
存儲函數(shù)與存儲過程本質上是一樣的，都是封裝一系列 SQL 語句，簡化調用。
我們自己編寫的存儲函數(shù)可以像 MySQL 函數(shù)那樣自由的被調用。
DROP FUNCTION IF EXISTS getStuNameById; DELIMITER // CREATE FUNCTION getStuNameById(stuId INT) -- 默認是IN，但是不能寫上去。stuId視為輸入的臨時變量 RETURNS VARCHAR(255) -- 指明返回值類型 RETURN (SELECT name FROM t_student WHERE id = stuId); // -- 指明SQL語句,并使用結束標記。注意分號位置 DELIMITER ;
使用存儲函數(shù)：
SELECT getStuNameById(1);
提示：在 return 語句后面，有趣的是，分號在 SQL 語句的外面。如果不加分號，查詢結果居然查詢出兩條記錄。
從上述存儲函數(shù)的寫法上來看，存儲函數(shù)有一定的缺點。首先與存儲過程一樣，只能返回一條結果記錄。另外就是存儲函數(shù)只能指明一列數(shù)據(jù)作為結果，而存儲過程能夠指明多列數(shù)據(jù)作為結果。
3、定義變量
如果希望 MySQL 執(zhí)行批量插入的操作，那么至少要有一個計數(shù)器來計算當前插入的是第幾次。
這里的變量是用在存儲過程中的 SQL 語句中的，變量的作用范圍在 “begin … end” 中。
沒有 default 子句，初始值為 NULL。
定義變量的操作：
DECLARE name,address VARCHAR; -- 發(fā)現(xiàn)了嗎，SQL中一般都喜歡先定義變量再定義類型，與Java是相反的。 DECLARE age INT DEFAULT 20; -- 指定默認值。若沒有DEFAULT子句，初始值為NULL。
為變量賦值：
SET name = 'jay'; -- 為name變量設置值 DECLARE var1,var2,var3 INT; SET var1 = 10,var2 = 20; -- 其實為了簡化記憶其語法，可以分開來寫 -- SET var1 = 10; -- SET var2 = 20; SET var3 = var1 + var2;
使用變量實例。如下表，在做了去除主鍵約束后，我又添加了一條 “id=1” 的數(shù)據(jù)?，F(xiàn)在希望查詢出 “id=1” 記錄的數(shù)量。
DROP PROCEDURE IF EXISTS contStById;
DELIMITER // -- 定義存儲過程結束符號為//
CREATE PROCEDURE contStById(IN sid INT(11),OUT result INT(11)) -- 定義輸入變量
BEGIN
DECLARE sCount INT;
SELECT COUNT(*) INTO sCount FROM t_student WHERE id = sid;
SET result = sCount; -- 用變量為輸出結果設值
END // -- 結束符要加
DELIMITER ; -- 重新定義存儲過程結束符為分號
CALL contStById(1,@result);
SELECT @result;
顯然，在存儲過程中的變量，可以直接與輸出變量進行相應的計算。本例直接把 “sCount” 這個變量的值賦值到輸出中。
4、定義條件與定義處理程序
- 定義條件 condition：指的是在執(zhí)行存儲過程中的 SQL 語句時，可能出現(xiàn)的問題；
- 定義處理程序 handler：當遇到了指定問題時應該如何處理，避免存儲過程因執(zhí)行異常而停止。
定義條件和定義處理程序的位置應該在 “begin … end” 之間。
定義條件的語法：“declare condition_name condition for 錯誤碼或錯誤值;”
錯誤碼可以視為一個錯誤的引用，比如 404，它代表的就是找不到頁面的錯誤，而它的錯誤值可能為 Null Pointer Exception。
DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000'; -- 錯誤值 DECLARE command_not_allowed CONDITION FOR 1148; -- 錯誤碼
定義處理程序語法：“declare handler_type handler for condition_name sp_statement;”
handler_type 的值有三種，其中 MySQL支持的有兩種： continue 是指遇到錯誤忽略，繼續(xù)執(zhí)行下面的 SQL。exit 表示遇到錯誤退出，默認的策略就是 exit。（undo 表示遇到錯誤后撤回之前的操作，MySQL 目前還不支持）
condition_name 可以是我們自己定義的條件，也可以是 MySQL 內置的條件，比如 SQL WARNING。sp_statement 指遇到錯誤的時候，需要執(zhí)行餓存儲過程或存儲函數(shù)。
DECLARE CONTINUE HANDLER FOR SQLSATTE '42S02' SET @info = 'NO_SUCH_TABLE'; -- 忽略錯誤值為42S02的SQL異常 DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info = 'ERROR_OCCUR'; -- 捕獲SQL執(zhí)行異常并輸出信息 DECLARE no_such_table CONDITION FOR 1146; -- 為錯誤碼為1146的錯誤定義條件 DECLARE CONTINUE HANDLER FOR no_such_table SET @info = 'no_such_table'; -- 為指定的條件設置處理程序
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
現(xiàn)在通過存儲過程，為這張表插入數(shù)據(jù)。因為 id 屬性有主鍵約束，所以不能插入相同的 id。
DROP PROCEDURE IF EXISTS insertStu;
DELIMITER // -- 定義存儲過程結束符號為//
CREATE PROCEDURE insertStu(OUT result INT) -- 指定輸出結果
BEGIN
DECLARE flag INT(11) DEFAULT 0; -- 指定變量為0
DECLARE primary_key_limit CONDITION FOR SQLSTATE '23000'; -- 主鍵約束的錯誤值
DECLARE CONTINUE HANDLER FOR primary_key_limit SET @info = -1; -- 設計如果出現(xiàn)錯誤，@info將會被設置為 -1
INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，設置主鍵為1
SET flag = 1; -- 普通變量設值為1
SET result = flag; -- 如果下面的SQL執(zhí)行出現(xiàn)異常，那么就退出，只有上面的SQL生效。將普通變量的值給輸出
INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，設置主鍵為1
SET flag = 2; -- 如果處理程序是EXIT，那么就不會執(zhí)行到這一步了
SET result = flag; -- 將普通變量的值給輸出
END // -- 結束符要加
DELIMITER ; -- 重新定義存儲過程結束符為分號
continue 是指遇到錯誤忽略，繼續(xù)執(zhí)行下面的 SQL。因為是 continue 來處理程序，所以遇到錯誤后將會繼續(xù)執(zhí)行。
另外，第二次插入記錄，因為違反了主鍵約束，所以插入失敗，但是存儲過程仍然繼續(xù)執(zhí)行完畢。
CALL insertStu(@result); SELECT @result,@info; -- @info沒有申明就能調用到，可能是是全局變量吧
運行結果：
再次查看 t_student 表，只插入了一條記錄，但是所有的存儲過程都執(zhí)行完畢了。
現(xiàn)在，重新執(zhí)行下面的 SQL。先重新建表，再將處理程序的處理策略換位 exit；在執(zhí)行存儲過程中遇到了錯誤，那么就立即退出。
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
DROP PROCEDURE IF EXISTS insertStu;
DELIMITER // -- 定義存儲過程結束符號為//
CREATE PROCEDURE insertStu(OUT result INT) -- 指定輸出結果
BEGIN
DECLARE flag INT(11) DEFAULT 0; -- 指定變量為0
DECLARE primary_key_limit CONDITION FOR SQLSTATE '23000'; -- 主鍵約束的錯誤值
DECLARE EXIT HANDLER FOR primary_key_limit SET @info = -1; -- 使用EXIT策略，遇到SQL錯誤將會結束這次存儲過程
-- 出現(xiàn)SQL錯誤則直接退出存儲過程的執(zhí)行
INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，設置主鍵為1
SET flag = 1; -- 普通變量設值為1
SET result = flag; -- 如果下面的SQL執(zhí)行出現(xiàn)異常，那么就退出，只有上面的SQL生效。將普通變量的值給輸出
INSERT INTO t_student(id,name,age) VALUES(1,'dayu',22); -- 插入值，設置主鍵為1
SET flag = 2; -- 如果處理程序是EXIT，那么就不會執(zhí)行到這一步了
SET result = flag; -- 將普通變量的值給輸出
END // -- 結束符要加
DELIMITER ; -- 重新定義存儲過程結束符為分號
CALL insertStu(@result);
SELECT @result,@info; -- @info沒有申明就能調用到，可能是是全局變量吧
@result 的結果為 1，說明執(zhí)行第二條 SQL 的時候，出現(xiàn)了異常。同樣，@info 的值為 -1，也提示處理條件中定義的存儲過程被觸發(fā)。
最后，數(shù)據(jù)庫表中的數(shù)據(jù)也是：
如果都是正確的 SQL，會是什么情況呢？
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
DROP PROCEDURE IF EXISTS insertStu;
DELIMITER // -- 定義存儲過程結束符號為//
CREATE PROCEDURE insertStu(OUT result INT) -- 指定輸出結果
BEGIN
DECLARE flag INT(11) DEFAULT 0; -- 指定變量為0
DECLARE primary_key_limit CONDITION FOR SQLSTATE '23000'; -- 主鍵約束的錯誤值
DECLARE EXIT HANDLER FOR primary_key_limit SET @info = -1; -- 設計如果出現(xiàn)錯誤，@info將會被設置為 -1
INSERT INTO t_student(id,name,age) VALUES(NULL,'dayu',22); --
SET flag = 1; -- 普通變量設值為1
SET result = flag; -- 如果下面的SQL執(zhí)行出現(xiàn)異常，那么就退出，只有上面的SQL生效。將普通變量的值給輸出
INSERT INTO t_student(id,name,age) VALUES(NULL,'dayu',22); --
SET flag = 2; -- 如果處理程序是EXIT，那么就不會執(zhí)行到這一步了
SET result = flag; -- 將普通變量的值給輸出
END // -- 結束符要加
DELIMITER ; -- 重新定義存儲過程結束符為分號
CALL insertStu(@result);
SELECT @result,@info; -- @info沒有申明就能調用到，可能是是全局變量吧
6、流程控制的使用
（1）if 語句的使用
DROP PROCEDURE IF EXISTS testIf;
DELIMITER //
CREATE PROCEDURE testIf(OUT result VARCHAR(255))
BEGIN
DECLARE val VARCHAR(255);
SET val = 'a';
IF val IS NULL
THEN SET result = 'IS NULL';
ELSE SET result = 'IS NOT NULL';
END IF;
END //
DELIMITER ;
CALL testIf(@result);
SELECT @result;
（2）case 語句
DROP PROCEDURE IF EXISTS testCase;
DELIMITER //
CREATE PROCEDURE testCase(OUT result VARCHAR(255))
BEGIN
DECLARE val VARCHAR(255);
SET val = 'a';
CASE val IS NULL
WHEN 1 THEN SET result = 'val is true';
WHEN 0 THEN SET result = 'val is false';
ELSE SELECT 'else';
END CASE;
END //
DELIMITER ;
CALL testCase(@result);
SELECT @result;
（3）loop
loop 用于重復執(zhí)行 SQL。leave 用于退出循環(huán)。
DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(OUT result VARCHAR(255))
BEGIN
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF id>10 THEN LEAVE add_loop; -- 可在此處修改成批量插入
END IF;
SET result = id;
END LOOP add_loop;
END //
DELIMITER ;
CALL testLoop(@result);
SELECT @result;
下面是一個批量插入的例子：
DROP TABLE IF EXISTS t_student;
CREATE TABLE t_student
(
id INT(11) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
age INT(11) NOT NULL
);
DROP PROCEDURE IF EXISTS testLoop;
DELIMITER //
CREATE PROCEDURE testLoop(IN columnCount INT(11))
BEGIN
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF id>columnCount THEN LEAVE add_loop;
END IF;
INSERT INTO t_student(id,name,age) VALUES(id,'dayu',22);
END LOOP add_loop;
END //
DELIMITER ;
CALL testLoop(15);
（4）while
DROP PROCEDURE IF EXISTS testWhile;
DELIMITER //
CREATE PROCEDURE testWhile(IN myCount INT(11),OUT result INT(11))
BEGIN
DECLARE i INT DEFAULT 0 ; -- 定義變量
WHILE i < myCount DO -- 符合條件就循環(huán)
-- 核心循環(huán)SQL;
SET i = i + 1 ; -- 計數(shù)器+1
END WHILE; -- 當不滿足條件，結束循環(huán) --分號一定要加！
SET result = i; -- 將變量賦值到輸出
END //
CALL testWhile(10,@result); SELECT @result AS 循環(huán)次數(shù);
7、使用 “show status” 查看存儲過程或函數(shù)的狀態(tài)
SHOW PROCEDURE STATUS LIKE 'C%';
SHOW FUNCTION STATUS LIKE 'C%';
知道了存儲過程，如果希望查看具體的存儲過程或者存儲函數(shù)的定義：
SHOW CREATE PROCEDURE study.CountStu; -- Create Procedure 列為核心語句 CREATE DEFINER=`root`@`localhost` PROCEDURE `CountStu`(IN stu_sex CHAR,OUT num INT) BEGIN SELECT COUNT(*) INTO num FROM t_student WHERE sex = stu_sex; END
提示：帶上數(shù)據(jù)庫的名字，小心查詢不到。
查看存儲函數(shù)有哪些：
SHOW FUNCTION STATUS LIKE 'C%'
查看具體的存儲函數(shù)創(chuàng)建語句：
SHOW CREATE FUNCTION study.countStu2 -- Create Function 列的語句 CREATE DEFINER=`root`@`localhost` FUNCTION `countStu2`(stu_sex CHAR) RETURNS int(11) RETURN (SELECT COUNT(*) FROM t_student WHERE sex = stu_sex)
8、從 information_schema.Routines 表中查詢存儲過程與函數(shù)
原來，MySQL 中的存儲過程與存儲函數(shù)都存放在 information_schema 數(shù)據(jù)庫下的 Routines 表中。
SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME LIKE 'C%'
如果什么時候忘記了存儲函數(shù)或者存儲過程的名字，可以查詢這張表的數(shù)據(jù)。然后確定了是某個存儲過程或者是存儲函數(shù)，就可以使用 “show create procedure/function 數(shù)據(jù)庫.sp_name” 查看指定的創(chuàng)建語句了。
9、修改存儲過程
語法：“alter procedure | function sp_name [存儲特性]”
修改存儲過程，將讀寫權限改為 MODIFIES SQL DATE 并指明調用者 ALTER PROCEDURE countStu2 MODIFIES SQL DATE -- 表示子程序中包含寫數(shù)據(jù)的語句 SQL SECURITY INVOKER -- 表示調用者才能執(zhí)行
存儲過程與存儲函數(shù)的補充
1、存儲過程如何修改代碼？
雖然提供了 “alter procedure sp_name [存儲特性]”，但是只能修改存儲過程的存儲特性，不能修改 SQL。需要刪除并重新創(chuàng)建。
2、存儲過程中能調用其它存儲過程嗎？
可以在存儲過程中的 SQL 中通過 CALL 調用其它存儲過程，但是不能用 drop 刪除其它存儲過程。
3、存儲過程中的 in 參數(shù)可能是中文怎么辦？
在定義存儲過程的時候，加上 “character set gbk”
DELIMITER //
CREATE PROCEDURE getAddressByName(IN u_name VARCHAR(50) character set gbk , OUT address VARCHAR(50))
BEGIN
SQL;
END//
DELIMITER ;
總結
以上為個人經驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
這篇文章主要介紹了MySql 存儲引擎和索引相關知識總結，文中講解非常細致，代碼幫助大家更好的理解和學習，感興趣的朋友可以了解下2020-06-06
在一次開發(fā)中使用 MySQL PREPARE 以后，從 prepare 直接取 name 賦值給 lex->prepared_stmt_name 然后給 EXECUTE 用，發(fā)現(xiàn)有一定概率找不到 prepare stmt 的 name，于是開始動手調查問題發(fā)生的原因，本文給大家分享mysql prepare使用及問題解決，感興趣的朋友一起看看吧2022-05-05
這篇文章主要為大家介紹了Mysql?中的事務，包括事務的基本概念和?ACID?特性、事務的隔離級別和具體實現(xiàn)方法等，并提供相應的代碼示例，希望對大家有所幫助2023-05-05
MySQL快速復制一張表的四種核心方法(包括表結構和數(shù)據(jù))
本文詳細介紹了四種復制MySQL表（結構+數(shù)據(jù)）的方法,并對每種方法進行了對比分析,適用于不同場景和數(shù)據(jù)量的復制需求,特別是針對超大表（1億+行）和跨實例復制提供了具體的操作命令和注意事項,感興趣的朋友跟隨小編一起看看吧2025-12-12
如果直接用綠色版的mysql，則下載后解壓，只需對目錄下的my.ini文件的basedir（mysql的基本目錄）和datadir（mysql數(shù)據(jù)目錄）指定就可以，如下所示。2013-06-06
windows下如何解決mysql secure_file_priv null問題
這篇文章主要介紹了windows下如何解決mysql secure_file_priv null問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-01-01
在MySQL中,CTE是一種簡潔而強大的語法,用于構建臨時的結果集,通常用于簡化復雜查詢,下面通過本文給大家介紹MySQL CTE 通用表達式,感興趣的朋友一起看看吧2025-06-06
本篇文章主要介紹了 session 加入mysql的方法,大家在開發(fā)過程中會遇到對數(shù)據(jù)庫的操作,有時會遇到Session加入mysql,這里給大家提供了方法,2016-07-07
在快速迭代的互聯(lián)網時代,數(shù)據(jù)已成為驅動業(yè)務增長和創(chuàng)新的核心要素,然而,隨著應用場景的多樣化和復雜化,傳統(tǒng)固定表結構常常成為開發(fā)者的噩夢,在?MySQL?中使用?JSON?數(shù)據(jù)類型,可以簡化半結構化或層級數(shù)據(jù)的處理流程,本文小編給大家詳細說說2026-05-05
這篇文章主要給大家介紹了關于Mysql啟動時報ERROR：2002問題的分析與解決方法，文中通過示例代碼介紹將該問題分析的非常詳細，對同樣遇到這個問題的朋友們具有一定的參考學習價值，需要的朋友們下面來一起看看吧。2017-08-08