第八章 MySQL存儲過程

課程回顧

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

存儲過程簡介

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

Paste_Image.png

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

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

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

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

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

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

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

創(chuàng)建存儲過程：
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)用存儲過程時指定（不能返回）
  • OUT，表示該參數(shù)的值可以被存儲過程改變，并且可以返回（輸出）
  • INOUT，表示該參數(shù)在調(diào)用時指定，并且可以被改變和返回

特性：
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語句，不能通過存儲過程創(chuàng)建數(shù)據(jù)表和數(shù)據(jù)庫，任意是指對記錄的增刪改查以及多表的連接等操作

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

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

CREATE PROCEDURE sp1() SELECT VERSION();

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

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

如果存儲過程沒有參數(shù)：帶不帶小括號都可以，如果還有參數(shù)，則必須帶有參數(shù)
CALL sp1;#不帶有小括號，執(zhí)行成功，結(jié)果為：5.5.37
CALL sp1();#帶有小括號，執(zhí)行成功，結(jié)果為：5.5.37

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

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

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

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

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

刪除存儲過程：
DROP PROCEDURE [IF EXISTS] sp_name

刪除存儲過程：
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)用存儲過程：
CALL removeUserById(22);#修改成功

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

從users表中刪除id不固定的記錄，并且返回剩余的記錄。需要兩條語句，一個是刪除記錄的語句，一個是獲取剩余記錄的語句，所以必須要添加BEGIN...END語句，第二個參數(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)用存儲過程：
CALL removeUserAndReturnUserNums(27,@nums);#刪除id為27的記錄并且把剩余記錄的條數(shù)放到nums中
SELECT @nums;#得到的結(jié)果為剩余記錄的條數(shù)

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

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

系統(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)建一個存儲過程，有一個IN類型參數(shù)age，和兩個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)用存儲過程：
CALL removeUserByAgeAndReturnInfos(20,@a,@b);

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

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

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

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

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

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

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

刪除存儲過程：
DROP PROCEDURE [IF EXISTS] sp_name

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容