第八章 MySQL存儲(chǔ)過程

課程回顧

自定義函數(shù)：簡(jiǎn)稱UDF溢谤；是對(duì)MySQL擴(kuò)展的一種途徑
創(chuàng)建自定義函數(shù)：CREATE FUNCTION......
自定義函數(shù)的兩個(gè)必要條件
- 參數(shù)：可以有零個(gè)或多個(gè)
- 返回值：只能有一個(gè)返回值
具有符合結(jié)構(gòu)的函數(shù)體需要使用BEGIN...END來包含

存儲(chǔ)過程簡(jiǎn)介

在對(duì)數(shù)據(jù)表進(jìn)行插入晕讲，更新脱惰，查詢香府，刪除（CURD）MySQL的執(zhí)行過程如下：

Paste_Image.png

如果省略了語法分析和編譯的環(huán)節(jié)，MySQL的執(zhí)行效率就會(huì)提高绞蹦，所以就可以使用存儲(chǔ)過程

存儲(chǔ)過程：存儲(chǔ)過程是SQL語句和控制語句的預(yù)編譯集合氮发，以一個(gè)名稱存儲(chǔ)并作為一個(gè)單元處理

存儲(chǔ)在數(shù)據(jù)庫內(nèi)，可以由應(yīng)用程序調(diào)用執(zhí)行砸捏，而且允許用戶聲明變量谬运，進(jìn)行流程控制，可以接收參數(shù)垦藏、輸入類型的參數(shù)梆暖、輸出類型的參數(shù)，并且可以存在多個(gè)返回值

如果有兩條插入的語句掂骏，不使用存儲(chǔ)過程時(shí)需要對(duì)每條語句進(jìn)行編譯執(zhí)行轰驳，如果使用存儲(chǔ)過程，就可以直接調(diào)用弟灼，省去了一次編譯的過程级解，效率會(huì)提高很多

存儲(chǔ)過程的優(yōu)點(diǎn)：

增強(qiáng)SQL語句的功能和靈活性
實(shí)現(xiàn)較快的執(zhí)行速度
減少網(wǎng)絡(luò)流量

存儲(chǔ)過程語法結(jié)構(gòu)分析

創(chuàng)建存儲(chǔ)過程：
CREATE
[DEFINER={user | CURRENT_USER}]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic...] routine_body

proc_parameter:
[IN | OUT | INOUT] param_name type

DEFINER為創(chuàng)建者，如果省略DEFINER語句默認(rèn)為當(dāng)前登陸到MySQL的用戶

參數(shù)：

IN田绑，表示該參數(shù)的值必須在調(diào)用存儲(chǔ)過程時(shí)指定（不能返回）
OUT勤哗，表示該參數(shù)的值可以被存儲(chǔ)過程改變，并且可以返回（輸出）
INOUT掩驱，表示該參數(shù)在調(diào)用時(shí)指定芒划，并且可以被改變和返回

特性：
COMMENT 'string‘
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA | }
| SQL SECURITY { DEFINER | INVOKER }

COMMENT：注釋
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í)行

過程體：

過程體由合法的SQL語句構(gòu)成
過程體可以是任意的SQL語句
過程體如果為復(fù)合結(jié)構(gòu)則使用BEGIN...END語句
復(fù)合結(jié)構(gòu)可以包含聲明欧穴，循環(huán)民逼，控制結(jié)構(gòu)

任意SQL語句：并不是所欲的SQL語句，不能通過存儲(chǔ)過程創(chuàng)建數(shù)據(jù)表和數(shù)據(jù)庫涮帘，任意是指對(duì)記錄的增刪改查以及多表的連接等操作

創(chuàng)建不帶參的存儲(chǔ)過程

創(chuàng)建存儲(chǔ)過程sp1功能是獲取MySQL客戶端的版本號(hào)

CREATE PROCEDURE sp1() SELECT VERSION();

調(diào)用存儲(chǔ)過程：

CALL sp_name([parameter[,...]])
CALL sp_name[()]

如果存儲(chǔ)過程沒有參數(shù)：帶不帶小括號(hào)都可以拼苍，如果還有參數(shù)，則必須帶有參數(shù)
CALL sp1;#不帶有小括號(hào)调缨，執(zhí)行成功映屋，結(jié)果為：5.5.37
CALL sp1();#帶有小括號(hào)苟鸯，執(zhí)行成功，結(jié)果為：5.5.37

創(chuàng)建帶有IN類型參數(shù)的存儲(chǔ)過程

創(chuàng)建一個(gè)存儲(chǔ)過程棚点，帶有一個(gè)IN參數(shù)id，功能是刪除參數(shù)為id的記錄
修改MySQL定界符：DELIMITER //

CREATE PROCEDURE removeUserById(IN id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = id;
END
//

調(diào)用：
因?yàn)檫@個(gè)存儲(chǔ)過程有參數(shù)湾蔓，所以需要使用小括號(hào)
修改定界符：DELIMITER ;
CALL removeUserById(3);調(diào)用成功
使用查看表記錄瘫析，發(fā)現(xiàn)i數(shù)據(jù)庫中所有記錄都被刪除
因?yàn)镸ySQL把id=id中的兩個(gè)id都當(dāng)成了字段，而不是參數(shù)
所以默责，存儲(chǔ)引擎的參數(shù)名稱不能和數(shù)據(jù)庫中的字段名一樣
所以需要修改存儲(chǔ)過程贬循，存儲(chǔ)過程的修改注釋，內(nèi)容類型等等特性桃序，并不能修改過程體杖虾，如果需要修改過程體，只能先刪除再重新創(chuàng)建

修改存儲(chǔ)過程：
ALTER PROCEDURE sp_name [characteristic...]
COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }

刪除存儲(chǔ)過程：
DROP PROCEDURE [IF EXISTS] sp_name

刪除存儲(chǔ)過程：
DROP PROCEDURE removeUserById;#刪除成功

修改MySQL定界符：DELIMITER //

CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id = p_id;
END
//

修改定界符：DELIMTER ;
調(diào)用存儲(chǔ)過程：
CALL removeUserById(22);#修改成功

創(chuàng)建帶有IN和OUT類型參數(shù)的存儲(chǔ)過程

從users表中刪除id不固定的記錄媒熊，并且返回剩余的記錄奇适。需要兩條語句，一個(gè)是刪除記錄的語句芦鳍，一個(gè)是獲取剩余記錄的語句嚷往，所以必須要添加BEGIN...END語句，第二個(gè)參數(shù)是需要返回的柠衅，所以需要時(shí)OUT類型
演示：
DELIMITER //#修改定界符

CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)
BEGIN
DELETE FROM user WHERE id = p_id;
SELECT count(id) FROM users INTO userNums;#INTO是把查詢結(jié)果放到變量里
END
//

創(chuàng)建成功
DELIMITER ;#修改定界符
調(diào)用存儲(chǔ)過程：
CALL removeUserAndReturnUserNums(27,@nums);#刪除id為27的記錄并且把剩余記錄的條數(shù)放到nums中
SELECT @nums;#得到的結(jié)果為剩余記錄的條數(shù)

在BEGIN...END之間也可以聲明變量皮仁，但是在在BEGIN...END之間聲明的變量作用域只是在BEGIN...END之間，在BEGIN...END語句執(zhí)行完成后菲宴，局部變量就消失了贷祈。而且在BEGIN...END之間聲明變量時(shí)DECLARE語句必須要位于語句的第一行。@nums是一個(gè)用戶變量喝峦，用戶變量可以通過SELECT...INTO...語句或者SET語句聲明（如SET @i = 7;#聲明變量i的值為7）势誊，用戶變量是對(duì)MySQL的客戶端定義的，通過這兩種方法只針對(duì)當(dāng)前用戶所使用的客戶端生效

創(chuàng)建帶有多個(gè)OUT類型參數(shù)的存儲(chǔ)過程

系統(tǒng)函數(shù)ROW_COUNT(),功能是得到被影響的條數(shù)（插入愈犹、刪除键科、更新）
演示：
在test表中插入三條記錄：
INSERT test(username) VALUES('A','B','C');
使用ROW_COUNT()函數(shù)：
SELECT ROW_COUNT();#得到上一條語句被影響的條數(shù)，結(jié)果為3

更新test表中的userame字段：
UPDATE test SET username = CONCAT(username,'--imooc') WHERE id<=2;#將id<=2的記錄的用戶名后加上'--imooc'
SELECT ROW_COUNT();#得到剛才更新記錄被影響的條數(shù)漩怎，結(jié)果為2

創(chuàng)建一個(gè)存儲(chǔ)過程勋颖，有一個(gè)IN類型參數(shù)age，和兩個(gè)OUT類型參數(shù)勋锤，功能是將age記錄刪除饭玲，并且返回被刪除記錄和剩余的記錄
DELIMITER //#修改定界符

CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED)
BEGIN
DELETE FROM users WHERE age = p_age;
SELECT ROW_COUNT() INTO deleteUsers;
SELECT COUNT(id) FROM users INTO userCOUNTS;
END
//

創(chuàng)建成功
DELIMITER ;#修改定界符
調(diào)用存儲(chǔ)過程：
CALL removeUserByAgeAndReturnInfos(20,@a,@b);

SELETE @a,@b;#結(jié)果為3和13

如果創(chuàng)建錯(cuò)誤，可以刪除存儲(chǔ)過程DROP PROCEDURE removeUserByAgeAndReturnInfos;

如果存儲(chǔ)過程中有某些語句書寫錯(cuò)誤叁执，在調(diào)用過程中沒有錯(cuò)誤的語句會(huì)執(zhí)行成功

存儲(chǔ)過程與自定義函數(shù)的區(qū)別

存儲(chǔ)過程實(shí)現(xiàn)的功能要復(fù)雜一些茄厘，而函數(shù)的針對(duì)性更強(qiáng)
存儲(chǔ)過程可以返回多個(gè)值矮冬，函數(shù)只能有一個(gè)返回值
存儲(chǔ)過程一般獨(dú)立的來執(zhí)行；而函數(shù)可以作為其他SQL語句的組成部分來出現(xiàn)次哈。

實(shí)際應(yīng)用中胎署，通常把一些復(fù)雜的需要重復(fù)調(diào)用的過程封裝成存儲(chǔ)過程，因?yàn)榇鎯?chǔ)過程比一條一條語句查詢效率高很多窑滞，經(jīng)常使用存儲(chǔ)過程操作數(shù)據(jù)表琼牧，很少使用函數(shù)操作表

修改存儲(chǔ)過程：
ALTER PROCEDURE sp_name [characteristic...]
COMMENT 'string'
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
不能修改過程體，如果需要修改哀卫，就要先刪除存儲(chǔ)過程然后重新創(chuàng)建