你相信嗎， 相信那一天的夕陽嗎?
上一章簡(jiǎn)單介紹了 MySQL的索引(二十三),如果沒有看過,請(qǐng)觀看上一章
一. 存儲(chǔ)過程
MySQL從5.0版本開始支持存儲(chǔ)過程和函數(shù)。存儲(chǔ)過程和函數(shù)能夠?qū)?fù)雜的SQL邏輯封裝在一起，
應(yīng)用程序無須關(guān)注存儲(chǔ)過程和函數(shù)內(nèi)部復(fù)雜的SQL邏輯，
而只需要簡(jiǎn)單地調(diào)用存儲(chǔ)過程和函數(shù)即可。
一.一 概述
含義：存儲(chǔ)過程的英文是 Stored Procedure。它的思想很簡(jiǎn)單，就是一組經(jīng)過預(yù)先編譯的 SQL 語句的封裝。
執(zhí)行過程：存儲(chǔ)過程預(yù)先存儲(chǔ)在 MySQL 服務(wù)器上，需要執(zhí)行的時(shí)候，客戶端只需要向服務(wù)器端發(fā)出調(diào)用存儲(chǔ)過程的命令，
服務(wù)器端就可以把預(yù)先存儲(chǔ)好的這一系列 SQL 語句全部執(zhí)行。
好處：
1、簡(jiǎn)化操作，提高了sql語句的重用性，減少了開發(fā)程序員的壓力
2、減少操作過程中的失誤，提高效率
3、減少網(wǎng)絡(luò)傳輸量（客戶端不需要把所有的 SQL 語句通過網(wǎng)絡(luò)發(fā)給服務(wù)器）
4、減少了 SQL 語句暴露在網(wǎng)上的風(fēng)險(xiǎn)，也提高了數(shù)據(jù)查詢的安全性
和視圖、函數(shù)的對(duì)比：
它和視圖有著同樣的優(yōu)點(diǎn)，清晰、安全，還可以減少網(wǎng)絡(luò)傳輸量。不過它和視圖不同，視圖是虛擬表，通常不對(duì)底層數(shù)據(jù)表直接操作，而存儲(chǔ)過程是程序化的 SQL，可以直接操作底層數(shù)據(jù)表，相比于面向集合的操作方式，能夠?qū)崿F(xiàn)一些更復(fù)雜的數(shù)據(jù)處理。
一旦存儲(chǔ)過程被創(chuàng)建出來，使用它就像使用函數(shù)一樣簡(jiǎn)單，我們直接通過調(diào)用存儲(chǔ)過程名即可。相較于函數(shù)，存儲(chǔ)過程是沒有返回值的。
一.二 分類
存儲(chǔ)過程的參數(shù)類型可以是IN、OUT和INOUT。根據(jù)這點(diǎn)分類如下：
1、沒有參數(shù)（無參數(shù)無返回）
2、僅僅帶 IN 類型（有參數(shù)無返回）
3、僅僅帶 OUT 類型（無參數(shù)有返回）
4、既帶 IN 又帶 OUT（有參數(shù)有返回）
5、帶 INOUT（有參數(shù)有返回）
注意：IN、OUT、INOUT 都可以在一個(gè)存儲(chǔ)過程中帶多個(gè)。
二. 創(chuàng)建存儲(chǔ)過程
創(chuàng)建語法分析
語法：
CREATE PROCEDURE 存儲(chǔ)過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN
存儲(chǔ)過程體
END
類似于Java中的方法：
修飾符 返回類型 方法名(參數(shù)類型 參數(shù)名,...){
方法體;
}
1、參數(shù)前面的符號(hào)的意思
-
IN：當(dāng)前參數(shù)為輸入?yún)?shù)，也就是表示入?yún)ⅲ?/p>存儲(chǔ)過程只是讀取這個(gè)參數(shù)的值。如果沒有定義參數(shù)種類，
默認(rèn)就是 IN，表示輸入?yún)?shù)。 -
OUT：當(dāng)前參數(shù)為輸出參數(shù)，也就是表示出參；執(zhí)行完成之后，調(diào)用這個(gè)存儲(chǔ)過程的客戶端或者應(yīng)用程序就可以讀取這個(gè)參數(shù)返回的值了。
-
INOUT：當(dāng)前參數(shù)既可以為輸入?yún)?shù)，也可以為輸出參數(shù)。
2、形參類型可以是 MySQL數(shù)據(jù)庫中的任意類型。
3、characteristics 表示創(chuàng)建存儲(chǔ)過程時(shí)指定的對(duì)存儲(chǔ)過程的約束條件，其取值信息如下：
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
-
LANGUAGE SQL：說明存儲(chǔ)過程執(zhí)行體是由SQL語句組成的，當(dāng)前系統(tǒng)支持的語言為SQL。 -
[NOT] DETERMINISTIC：指明存儲(chǔ)過程執(zhí)行的結(jié)果是否確定。DETERMINISTIC表示結(jié)果是確定的。每次執(zhí)行存儲(chǔ)過程時(shí)，相同的輸入會(huì)得到相同的輸出。NOT DETERMINISTIC表示結(jié)果是不確定的，相同的輸入可能得到不同的輸出。如果沒有指定任意一個(gè)值，默認(rèn)為NOT DETERMINISTIC。 -
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }：指明子程序使用SQL語句的限制。- CONTAINS SQL表示當(dāng)前存儲(chǔ)過程的子程序包含SQL語句，但是并不包含讀寫數(shù)據(jù)的SQL語句；
- NO SQL表示當(dāng)前存儲(chǔ)過程的子程序中不包含任何SQL語句；
- READS SQL DATA表示當(dāng)前存儲(chǔ)過程的子程序中包含讀數(shù)據(jù)的SQL語句；
- MODIFIES SQL DATA表示當(dāng)前存儲(chǔ)過程的子程序中包含寫數(shù)據(jù)的SQL語句。
- 默認(rèn)情況下，系統(tǒng)會(huì)指定為CONTAINS SQL。
-
SQL SECURITY { DEFINER | INVOKER }：執(zhí)行當(dāng)前存儲(chǔ)過程的權(quán)限，即指明哪些用戶能夠執(zhí)行當(dāng)前存儲(chǔ)過程。-
DEFINER表示只有當(dāng)前存儲(chǔ)過程的創(chuàng)建者或者定義者才能執(zhí)行當(dāng)前存儲(chǔ)過程； -
INVOKER表示擁有當(dāng)前存儲(chǔ)過程的訪問權(quán)限的用戶能夠執(zhí)行當(dāng)前存儲(chǔ)過程。 - 如果沒有設(shè)置相關(guān)的值，則MySQL默認(rèn)指定值為DEFINER。
-
-
COMMENT 'string'：注釋信息，可以用來描述存儲(chǔ)過程。
4、存儲(chǔ)過程體中可以有多條 SQL 語句，如果僅僅一條SQL 語句，則可以省略 BEGIN 和 END
編寫存儲(chǔ)過程并不是一件簡(jiǎn)單的事情，可能存儲(chǔ)過程中需要復(fù)雜的 SQL 語句。
BEGIN…END：BEGIN…END 中間包含了多個(gè)語句，每個(gè)語句都以（;）號(hào)為結(jié)束符。
DECLARE：DECLARE 用來聲明變量，使用的位置在于 BEGIN…END 語句中間，而且需要在其他語句使用之前進(jìn)行變量的聲明。
SET：賦值語句，用于對(duì)變量進(jìn)行賦值。
SELECT… INTO：把從數(shù)據(jù)表中查詢的結(jié)果存放到變量中，也就是為變量賦值。
5、需要設(shè)置新的結(jié)束標(biāo)記
DELIMITER 新的結(jié)束標(biāo)記
因?yàn)镸ySQL默認(rèn)的語句結(jié)束符號(hào)為分號(hào)‘;’。為了避免與存儲(chǔ)過程中SQL語句結(jié)束符相沖突，需要使用DELIMITER改變存儲(chǔ)過程的結(jié)束符。
比如：“DELIMITER //”語句的作用是將MySQL的結(jié)束符設(shè)置為//，并以“END //”結(jié)束存儲(chǔ)過程。存儲(chǔ)過程定義完畢之后再使用“DELIMITER ;”恢復(fù)默認(rèn)結(jié)束符。DELIMITER也可以指定其他符號(hào)作為結(jié)束符。
當(dāng)使用DELIMITER命令時(shí)，應(yīng)該避免使用反斜杠（‘\’）字符，因?yàn)榉葱本€是MySQL的轉(zhuǎn)義字符。
示例:
DELIMITER //
CREATE PROCEDURE 存儲(chǔ)過程名(IN|OUT|INOUT 參數(shù)名 參數(shù)類型,...)
[characteristics ...]
BEGIN
sql語句1;
sql語句2;
END //
存儲(chǔ)過程舉例， 有一個(gè)表 auth_user
二.一 創(chuàng)建一個(gè)無參的存儲(chǔ)過程
查詢一下郵箱:
-- 切換標(biāo)識(shí)符
delimiter //
-- 創(chuàng)建存儲(chǔ)語句
create PROCEDURE p1()
begin
select email from auth_user where username = 'admin';
-- 結(jié)束
end //
-- 恢復(fù)標(biāo)識(shí)符為 ;
delimiter ;
將這些 sql 一起執(zhí)行， 執(zhí)行完成后，會(huì)展示到 函數(shù)下面 Px 表示存儲(chǔ)過程 (Navicat 展示)
調(diào)用存儲(chǔ)過程 call p1()
call p1();
二.二 創(chuàng)建一個(gè)入?yún)⒌拇鎯?chǔ)過程并調(diào)用
-- 切換標(biāo)識(shí)符
delimiter //
-- 創(chuàng)建存儲(chǔ)語句
create PROCEDURE p2(IN usernameParam varchar(20))
begin
select email from auth_user where username = usernameParam;
-- 結(jié)束
end //
-- 恢復(fù)標(biāo)識(shí)符為 ;
delimiter ;
call p2('admin'); -- 會(huì)展示郵箱
call p2 ('admin222'); -- 查詢不出來，為空
二.三 創(chuàng)建一個(gè)出參的存儲(chǔ)過程并調(diào)用
-- 切換標(biāo)識(shí)符
delimiter //
-- 創(chuàng)建存儲(chǔ)語句
create PROCEDURE p3(OUT s_email varchar(25))
begin
select email into s_email from auth_user where username = 'admin';
-- 結(jié)束
end //
-- 恢復(fù)標(biāo)識(shí)符為 ;
delimiter ;
-- 定義一個(gè)變量，要有默認(rèn)值為 ''
set @s_email = '';
-- 查詢一下自定義的變量值
select @s_email;
-- 調(diào)用存儲(chǔ)函數(shù)
call p3(@s_email);
-- 查詢一下變量，發(fā)現(xiàn)是有值的 為正常的郵箱
select @s_email;
二.四 創(chuàng)建一個(gè)入?yún)⒑统鰠⒕械拇鎯?chǔ)過程并調(diào)用
-- 切換標(biāo)識(shí)符
delimiter //
-- 創(chuàng)建存儲(chǔ)語句
create PROCEDURE p4(OUT s_email varchar(25), IN s_username varchar(20))
begin
select email into s_email from auth_user where username = s_username ;
-- 結(jié)束
end //
-- 恢復(fù)標(biāo)識(shí)符為 ;
delimiter ;
-- 定義一個(gè)變量，要有默認(rèn)值為 ''
set @s_email = '';
-- 查詢一下自定義的變量值
select @s_email;
-- 調(diào)用存儲(chǔ)函數(shù)
call p4(@s_email, 'admin');
-- 查詢一下變量，發(fā)現(xiàn)是有值的
select @s_email;
二.五 創(chuàng)建一個(gè) INOUT 形成的存儲(chǔ)過程并調(diào)用
給郵箱 email 添加一個(gè)前綴， 并返回拼接后的新郵箱信息
-- 切換標(biāo)識(shí)符
delimiter //
-- 創(chuàng)建存儲(chǔ)語句
create PROCEDURE p5(INOUT s_email varchar(100), IN s_username varchar(100))
begin
DECLARE dbEmail varchar(100) default '';
select email into dbEmail from auth_user where username = s_username ;
set s_email = concat(s_email,dbEmail);
-- 結(jié)束
end //
-- 恢復(fù)標(biāo)識(shí)符為 ;
delimiter ;
-- 定義一個(gè)變量，要有默認(rèn)值為 ''
set @s_email = 'hello:';
-- 查詢一下自定義的變量值
select @s_email;
-- 調(diào)用存儲(chǔ)函數(shù)
call p5(@s_email, 'admin');
-- 查詢一下變量，發(fā)現(xiàn)是有值的 hello:1290513799@qq.com
select @s_email;
二.六 IF ELSE 形式的 判斷 存儲(chǔ)過程
DELIMITER $$
CREATE
PROCEDURE `demo`.`p11`(IN `day` INT)
-- 存儲(chǔ)過程體
BEGIN
IF `day` = 0 THEN
SELECT '星期天';
ELSEIF `day` = 1 THEN
SELECT '星期一';
ELSEIF `day` = 2 THEN
SELECT '星期二';
ELSE
SELECT '無效日期';
END IF;
END$$
DELIMITER ;
-- 星期天
call p11(0);
-- 星期一
call p11(1);
-- 無效日期
call p11 (5);
二.七 CASE 形式的 判斷 存儲(chǔ)過程
DELIMITER $$
CREATE
PROCEDURE p12(IN num INT)
BEGIN
CASE -- 條件開始
WHEN num<0 THEN
SELECT '負(fù)數(shù)';
WHEN num>0 THEN
SELECT '正數(shù)';
ELSE
SELECT '不是正數(shù)也不是負(fù)數(shù)';
END CASE; -- 條件結(jié)束
END$$
DELIMITER;
-- 正數(shù)
call p12(3);
-- 負(fù)數(shù)
call p12 (-2);
-- 不是正數(shù)也不是負(fù)數(shù)
call p12 (0);
二.八 While 循環(huán) 計(jì)算總和
DELIMITER $$
CREATE
PROCEDURE p13(IN num INT,OUT SUM INT)
BEGIN
DECLARE i INT;
set i = 1;
set sum = 0;
WHILE i<= num DO -- 循環(huán)開始
SET SUM = SUM+ i;
SET i = i+1;
END WHILE; -- 循環(huán)結(jié)束
END$$
DELIMITER;
set @SUM = 0;
call p13(100, @SUM);
select @SUM; -- 5050
二.九 定義一個(gè) saveOrUpdate 的存儲(chǔ)過程
DELIMITER $$
CREATE
PROCEDURE mysaveOrUpdate(IN s_username VARCHAR(50),IN s_password VARCHAR(50) ,OUT s_result varchar(100))
BEGIN
-- 聲明一個(gè)變量 用來決定這個(gè)名字是否已經(jīng)存在
DECLARE s_count INT DEFAULT 0;
-- 驗(yàn)證這么名字是否已經(jīng)存在
SELECT COUNT(*) INTO s_count FROM auth_user WHERE `username` = s_username;
IF s_count = 0 THEN
INSERT INTO auth_user (`username`, `password`) VALUES(s_username, s_password);
SET s_result = '數(shù)據(jù)添加成功';
ELSE
update auth_user set password = s_password where username = s_username;
SET s_result = '進(jìn)行更新';
END IF;
END$$
DELIMITER;
-- 剛開始查詢沒有數(shù)據(jù)
select * from auth_user where username ='yjl';
set @s_result ='';
-- 調(diào)用 abc123
call mysaveOrUpdate('yjl','abc123', @s_result);
-- 數(shù)據(jù)添加成功
select @s_result;
-- 查詢的時(shí)候，也是有的。
select username,password from auth_user where username ='yjl';
call mysaveOrUpdate('yjl','123456', @s_result);
-- 進(jìn)行更新
select @s_result;
-- 查詢的時(shí)候, 會(huì)發(fā)現(xiàn)數(shù)據(jù)已經(jīng)更新了 123456
select username,password from auth_user where username ='yjl';
二.十 Mybatis 調(diào)用存儲(chǔ)過程
需要設(shè)置 statementType=“CALLABLE”, 然后 { call 存儲(chǔ)過程名(參數(shù)1,參數(shù)2,參數(shù)3) }
如插入的話:
存儲(chǔ)函數(shù)定義為：
CREATE PROCEDURE insert_user(OUT u_id INTEGER,IN u_name VARCHAR(20),IN u_sex VARCHAR(20),IN u_age INTEGER)
BEGIN
INSERT INTO t_user (name,sex,age) VALUES (u_name,u_sex,u_age);
SET u_id=LAST_INSERT_ID();
END
UserMapper.xml:
<!-- 添加用戶 -->
<insert id="addUser" parameterType="com.po.User" statementType="CALLABLE">
{call insert_user(#{id,mode=OUT,jdbcType=INTEGER},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN})}
</insert>
更新的話:
CREATE PROCEDURE updateUser(IN u_id INTEGER,IN u_name VARCHAR(20),IN u_sex VARCHAR(20),IN u_age INTEGER)
BEGIN
UPDATE t_user SET name=u_name,sex=u_sex,age=u_age WHERE id=u_id;
END
UserMapper.xml:
<!-- 更新用戶 -->
<update id="updateUser" parameterType="user" statementType="CALLABLE">
{call updateUser(#{id,mode=IN},#{name,mode=IN},#{sex,mode=IN},#{age,mode=IN})}
</update>
查詢的話:
CREATE PROCEDURE getUserById(IN u_id INTEGER)
BEGIN
SELECT id,name,sex,age FROM t_user WHERE id=u_id;
END
UserMapper.xml:
<!-- 根據(jù)id查詢用戶 -->
<select id="getUserById" parameterType="Integer" resultType="user" statementType="CALLABLE">
{call getUserById(#{id,mode=IN})}
</select>
如刪除的話:
CREATE PROCEDURE deleteUser(IN u_id INTEGER)
BEGIN
DELETE FROM t_user WHERE id=u_id;
END
UserMapper.xml:
<!-- 刪除用戶 -->
<delete id="deleteUser" parameterType="Integer" statementType="CALLABLE">
{call deleteUser(#{id,mode=IN})}
</delete>
三. 存儲(chǔ)過程用法
三.一 調(diào)用存儲(chǔ)過程 call
call 存儲(chǔ)函數(shù)名( 對(duì)應(yīng)的參數(shù)1，對(duì)應(yīng)的參數(shù)2，對(duì)應(yīng)的參數(shù)3)
set @SUM = 0;
call p13(100, @SUM);
select @SUM; -- 5050
三.二 查詢存儲(chǔ)過程
SHOW CREATE PROCEDURE 存儲(chǔ)過程名
show create PROCEDURE p13;
從information_schema.Routines表中查看存儲(chǔ)過程和函數(shù)的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='p13' AND ROUTINE_TYPE = 'PROCEDURE';
三.三 刪除存儲(chǔ)過程
DROP PROCEDURE [IF EXISTS] 存儲(chǔ)過程名
-- 刪除
drop PROCEDURE if EXISTS p13;
-- 刪除之后再查詢就查詢不到了
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='p13' AND ROUTINE_TYPE = 'PROCEDURE';
三.四 展示庫里面所有的存儲(chǔ)過程
SHOW PROCEDURE STATUS;
可以通過 Db(庫) 和 Name (存儲(chǔ)過程名) 進(jìn)行篩選查詢
三.五 修改存儲(chǔ)過程
建議先刪除，再重新創(chuàng)建。
四. 關(guān)于存儲(chǔ)過程使用的爭(zhēng)議
盡管存儲(chǔ)過程有諸多優(yōu)點(diǎn)，但是對(duì)于存儲(chǔ)過程的使用，一直都存在著很多爭(zhēng)議，比如有些公司對(duì)于大型項(xiàng)目要求使用存儲(chǔ)過程，而有些公司在手冊(cè)中明確禁止使用存儲(chǔ)過程，為什么這些公司對(duì)存儲(chǔ)過程的使用需求差別這么大呢？
四.一 優(yōu)點(diǎn)
1、存儲(chǔ)過程可以一次編譯多次使用。 存儲(chǔ)過程只在創(chuàng)建時(shí)進(jìn)行編譯，之后的使用都不需要重新編譯，這就提升了 SQL 的執(zhí)行效率。
2、可以減少開發(fā)工作量。 將代碼封裝成模塊，實(shí)際上是編程的核心思想之一，這樣可以把復(fù)雜的問題拆解成不同的模塊，然后模塊之間可以重復(fù)使用，在減少開發(fā)工作量的同時(shí)，還能保證代碼的結(jié)構(gòu)清晰。
3、存儲(chǔ)過程的安全性強(qiáng)。 我們?cè)谠O(shè)定存儲(chǔ)過程的時(shí)候可以設(shè)置對(duì)用戶的使用權(quán)限，這樣就和視圖一樣具有較強(qiáng)的安全性。
4、可以減少網(wǎng)絡(luò)傳輸量。 因?yàn)榇a封裝到存儲(chǔ)過程中，每次使用只需要調(diào)用存儲(chǔ)過程即可，這樣就減少了網(wǎng)絡(luò)傳輸量。
5、良好的封裝性。 在進(jìn)行相對(duì)復(fù)雜的數(shù)據(jù)庫操作時(shí)，原本需要使用一條一條的 SQL 語句，可能要連接多次數(shù)據(jù)庫才能完成的操作，現(xiàn)在變成了一次存儲(chǔ)過程，只需要連接一次即可。
四.二 缺點(diǎn)
基于上面這些優(yōu)點(diǎn)，不少大公司都要求大型項(xiàng)目使用存儲(chǔ)過程，比如微軟、IBM 等公司。但是國內(nèi)的阿里并不推薦開發(fā)人員使用存儲(chǔ)過程，這是為什么呢？
阿里開發(fā)規(guī)范
【強(qiáng)制】禁止使用存儲(chǔ)過程，存儲(chǔ)過程難以調(diào)試和擴(kuò)展，更沒有移植性。
存儲(chǔ)過程雖然有諸如上面的好處，但缺點(diǎn)也是很明顯的。
1、可移植性差。 存儲(chǔ)過程不能跨數(shù)據(jù)庫移植，比如在 MySQL、Oracle 和 SQL Server 里編寫的存儲(chǔ)過程，在換成其他數(shù)據(jù)庫時(shí)都需要重新編寫。
2、調(diào)試?yán)щy。 只有少數(shù) DBMS 支持存儲(chǔ)過程的調(diào)試。對(duì)于復(fù)雜的存儲(chǔ)過程來說，開發(fā)和維護(hù)都不容易。雖然也有一些第三方工具可以對(duì)存儲(chǔ)過程進(jìn)行調(diào)試，但要收費(fèi)。
3、存儲(chǔ)過程的版本管理很困難。 比如數(shù)據(jù)表索引發(fā)生變化了，可能會(huì)導(dǎo)致存儲(chǔ)過程失效。我們?cè)陂_發(fā)軟件的時(shí)候往往需要進(jìn)行版本管理，但是存儲(chǔ)過程本身沒有版本控制，版本迭代更新的時(shí)候很麻煩。
4、它不適合高并發(fā)的場(chǎng)景。 高并發(fā)的場(chǎng)景需要減少數(shù)據(jù)庫的壓力，有時(shí)數(shù)據(jù)庫會(huì)采用分庫分表的方式，而且對(duì)可擴(kuò)展性要求很高，在這種情況下，存儲(chǔ)過程會(huì)變得難以維護(hù)，增加數(shù)據(jù)庫的壓力，顯然就不適用了。
小結(jié)：文章來源：http://www.zghlxwxcb.cn/news/detail-625309.html
存儲(chǔ)過程既方便，又有局限性。盡管不同的公司對(duì)存儲(chǔ)過程的態(tài)度不一，但是對(duì)于我們開發(fā)人員來說，
不論怎樣，掌握存儲(chǔ)過程都是必備的技能之一。文章來源地址http://www.zghlxwxcb.cn/news/detail-625309.html
謝謝!!!
到了這里，關(guān)于MySQL存儲(chǔ)過程(二十四)的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
