#存儲(chǔ)過(guò)程和函數(shù)

/*

存儲(chǔ)過(guò)程和函數(shù)類似于Java中的方法

好處：

1、提高代碼重用性

2、簡(jiǎn)化操作

*/

#存儲(chǔ)過(guò)程

/*

含義：一組編譯好的SQL語(yǔ)句的集合，理解成批處理語(yǔ)句

1、提高代碼的重用性

2、簡(jiǎn)化操作

3、減少了編譯次數(shù)

4、減少了和數(shù)據(jù)庫(kù)服務(wù)器連接次數(shù)，提高了效率

*/

#一、創(chuàng)建

CREATE PROCEDURE 存儲(chǔ)過(guò)程名(參數(shù)列表)

BEGIN

存儲(chǔ)過(guò)程體(一組合法的SQL語(yǔ)句)

END

注意：

1、參數(shù)列表包含3個(gè)部分

參數(shù)模式 參數(shù)名 參數(shù)類型

舉例：

IN stuname VARCHAR(20)

參數(shù)模式：

IN：修飾的參數(shù)可以作為入口，也就是說(shuō)該參數(shù)需要調(diào)用方傳入值

OUT：修飾的參數(shù)可以作為出口，也就是說(shuō)該參數(shù)可以作為返回值

INOUT：修飾的參數(shù)可以作為出口/入口，既可以傳入值又可以返回值

2、如果存儲(chǔ)過(guò)程體僅僅只有1句話，BEGIN END可以省略

3、存儲(chǔ)過(guò)程體中的每條SQL語(yǔ)句的結(jié)尾要求必須加分號(hào)，

存儲(chǔ)過(guò)程的結(jié)尾可以使用DELIMITER重新設(shè)置

語(yǔ)法：

DELIMITER 結(jié)束標(biāo)記

案例：

DELIMITER $

#二、調(diào)用

CALL 存儲(chǔ)過(guò)程名(實(shí)參列表);

#1、空白列表

#案例：插入到admin表中5條記錄

SELECT * FROM admin;

#設(shè)置結(jié)束標(biāo)志

DELIMITER $

#定義

CREATE PROCEDURE myp1()

BEGIN

INSERT INTO admin(username,`password`)

VALUES('john1','0000'),('lily','0000'),('rose','0000'),('jack','0000'),('tom','0000');

END $

#如果SQLyog中運(yùn)行不了，就粘貼到cmd打開(kāi)girls數(shù)據(jù)庫(kù)，再運(yùn)行

#調(diào)用

CALL myp1()$#之后結(jié)束符號(hào)都不是;了，都換成$

SELECT * FROM admin$

#如果SQLyog中運(yùn)行不了，就粘貼到cmd打開(kāi)girls數(shù)據(jù)庫(kù)，再運(yùn)行

#2、創(chuàng)建帶in模式參數(shù)的存儲(chǔ)過(guò)程

#案例1：創(chuàng)建存儲(chǔ)過(guò)程實(shí)現(xiàn)，根據(jù)女神名查詢對(duì)應(yīng)的男神信息

#IN 可以省略，但是不建議

#定義

CREATE PROCEDURE myp2(IN beautyName VARCHAR(20))

BEGIN

SELECT bo.*

FROM boys bo

RIGHT JOIN beauty b ON bo.id = b.boyfriend_id

WHERE b.name=beautyName;

END$

#調(diào)用

CALL myp2('宋茜')$

#如果出現(xiàn)報(bào)錯(cuò)： Incorrect string value: '\xC1\xF8\xD1\xD2' for column 'beautyName' at row 8

#說(shuō)明是字符集的問(wèn)題，這時(shí)要重新設(shè)置一下字符集

SET NAMES gbk$

CALL myp2('宋茜')$#不報(bào)錯(cuò)了

#案例2：創(chuàng)建存儲(chǔ)過(guò)程實(shí)現(xiàn)，用戶登陸是否成功

#定義

CREATE PROCEDURE myp5(IN username VARCHAR(20),IN PASSWORD VARCHAR(20))

BEGIN

#聲明變量，存放存儲(chǔ)結(jié)果

DECLARE result INT DEFAULT 0;#聲明并初始化

#賦值

SELECT COUNT(*) INTO result

FROM admin

WHERE admin.`username`=username

AND admin.`password`=PASSWORD;

#使用

SELECT IF(result>0,'成功','失敗');

END$

#調(diào)用

CALL myp5('張飛','8888')$

#3、創(chuàng)建帶out模式的存儲(chǔ)過(guò)程

#案例1：根據(jù)女神名，返回對(duì)應(yīng)的男神名

CREATE PROCEDURE myp6(IN beautyName VARCHAR(20),OUT boyname VARCHAR(20))

BEGIN

SELECT bo.boyName INTO boyName

FROM boys bo

INNER JOIN beauty b ON b.boyfriend_id=bo.id

WHERE b.name=beautyName;

END$

#調(diào)用

#第二個(gè)參數(shù)就是一個(gè)變量，不需要有值，用于接收返回值

SET @bName$#定義一個(gè)用戶變量,甚至可以不定義，直接用@bName

CALL myp6('宋茜',@bName)$

SELECT @bName$#這就是男神名

#案例2：根據(jù)女神名，返回對(duì)應(yīng)的男神名和男神的魅力值

CREATE PROCEDURE myp7(IN beautyName VARCHAR(20),OUT boyName VARCHAR(20),OUT userCP INT)

BEGIN

SELECT bo.boyName,bo.userCP INTO boyName,userCP

FROM boys bo

INNER JOIN beauty b ON b.boyfriend_id=bo.id

WHERE b.name=beautyName;

END$

#調(diào)用

CALL myp7('宋茜',@bName,@usercp)$

SELECT @bName,@usercp$

#4、創(chuàng)建帶有inout模式參數(shù)的存儲(chǔ)過(guò)程

#案例1：傳入a和b兩個(gè)值，最終a和b都翻倍并返回

CREATE PROCEDURE myp8(INOUT a INT, INOUT b INT)

BEGIN

SET a=a*2;

SET b=b*2;

END$

#調(diào)用

#注意：不可以直接往存儲(chǔ)過(guò)程中傳常數(shù)，如果傳常數(shù)，返回的值將沒(méi)有位置放

#因此在調(diào)用之前，必須要先定義兩個(gè)有值的變量

SET @m=10$

SET @n=20$

CALL myp8(@m,@n)$

SELECT @m,@n$

#三、存儲(chǔ)過(guò)程的刪除

/*

語(yǔ)法：drop procedure 存儲(chǔ)過(guò)程名稱

說(shuō)明：每次只能刪除一個(gè)【在SQLyog中就可以運(yùn)行】

*/

DROP PROCEDURE test_prop3;

#四、查看存儲(chǔ)過(guò)程信息

SHOW CREATE PROCEDURE myp2;

#desc myp2是錯(cuò)誤寫法

#【練習(xí)】

#練習(xí)1：創(chuàng)建存儲(chǔ)過(guò)程，實(shí)現(xiàn)傳入用戶名和密碼，插入到admin表中

CREATE PROCEDURE test_pro1(IN username VARCHAR(20),IN loginPwd VARCHAR(20))

BEGIN

INSERT admin(admin.username,PASSWORD)

VALUES(userName,loginPwd);

END;$#這里也可以加個(gè)分號(hào)

CALL test_pro1('admin','0000')$

SELECT * FROM admin$

#練習(xí)2：創(chuàng)建存儲(chǔ)過(guò)程實(shí)現(xiàn)傳入女神編號(hào)，返回女神名稱和女神電話

CREATE PROCEDURE test_pro2(IN id INT, OUT `name` VARCHAR(20) , OUT phone VARCHAR(20))

BEGIN

SELECT b.name,b.phone INTO `name`,phone

FROM beauty b

WHERE b.id=id;

END$

CALL test_pro2(7,@n,@p)$

SELECT @n,@p$

#練習(xí)3：創(chuàng)建存儲(chǔ)過(guò)程或函數(shù)實(shí)現(xiàn)傳入兩個(gè)女神生日，返回大小

CREATE PROCEDURE test_pro3(IN birth1 DATETIME, IN birth2 DATETIME,OUT result INT)

BEGIN

SELECT DATEDIFF(birth1,birth2) INTO result;

END$

CALL test_pro3('1998-1-1',NOW(),@result)$

SELECT @result$

#練習(xí)4：創(chuàng)建存儲(chǔ)過(guò)程或函數(shù)實(shí)現(xiàn)傳入一個(gè)日期，格式化成xx年xx月xx日并返回

CREATE PROCEDURE test_pro4(IN mydate DATETIME,OUT strDate VARCHAR(50))

BEGIN

SELECT DATE_FORMAT(mydate,'%y年%m月%d日') INTO strDate;

#DATE_FORMAT將日期轉(zhuǎn)換為字符串并且返回一個(gè)字符串

END$

CALL test_pro4(NOW(),@str)$

SELECT @str$

#練習(xí)5：創(chuàng)建存儲(chǔ)過(guò)程或函數(shù)實(shí)現(xiàn)傳入女神名稱，返回：女神 and 男神 格式的字符串

CREATE PROCEDURE test_pro5(IN beautyName VARCHAR(20),OUT str VARCHAR(50))

BEGIN

SELECT CONCAT(beautyName,' and ',IFNULL(boyName,'null')) INTO str

FROM boys bo

RIGHT JOIN beauty b ON b.boyfriend_id=bo.id

WHERE b.name=beautyName;

END$

CALL test_pro5('宋茜',@str)$

SELECT @str$

#練習(xí)6：創(chuàng)建存儲(chǔ)過(guò)程函數(shù)，根據(jù)傳入的條目數(shù)和起始索引，查詢beauty表的記錄

CREATE PROCEDURE test_pro6(IN startIndex INT, IN size INT)

BEGIN

SELECT * FROM beauty LIMIT startIndex,size;

END$

CALL test_pro6(1,2)$#索引和id無(wú)關(guān)

創(chuàng)作挑戰(zhàn)賽新人創(chuàng)作獎(jiǎng)勵(lì)來(lái)咯，堅(jiān)持創(chuàng)作打卡瓜分現(xiàn)金大獎(jiǎng)

總結(jié)

以上是生活随笔為你收集整理的mysql storage_mySQL__storage课堂笔记和练习的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。