柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫(kù) mysql 存儲(chǔ)過(guò)程
柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫(kù) mysql 存儲(chǔ)過(guò)程
mysql存儲(chǔ)過(guò)程：事先經(jīng)過(guò)編譯并且存儲(chǔ)在數(shù)據(jù)庫(kù)中的一段SQL語(yǔ)句集合。調(diào)用存儲(chǔ)過(guò)程可以減少數(shù)據(jù)庫(kù)和應(yīng)用程序間傳輸?shù)木W(wǎng)絡(luò)性能消耗
目錄
1、創(chuàng)建存儲(chǔ)過(guò)程和調(diào)用
1.1、無(wú)參存儲(chǔ)過(guò)程
1.2、有參存儲(chǔ)過(guò)程
1.3、mysql變量
1.3.1、系統(tǒng)變量
1.3.2、用戶自定義變量
1.3.3、局部變量
2、存儲(chǔ)過(guò)程中條件判斷 if 的使用
3、存儲(chǔ)過(guò)程中case的使用
4、存儲(chǔ)過(guò)程中循環(huán)的使用
4.1、while 循環(huán)
4.2、repeat 循環(huán)
4.3、loop 循環(huán)
5、存儲(chǔ)過(guò)程-游標(biāo) CURSOR
1、創(chuàng)建存儲(chǔ)過(guò)程和調(diào)用
創(chuàng)建存儲(chǔ)過(guò)程語(yǔ)句
CREATE PROCEDURE 存儲(chǔ)過(guò)程名稱([參數(shù)]) BEGIN 內(nèi)容 END;
調(diào)用存儲(chǔ)過(guò)程使用CALL
調(diào)用存儲(chǔ)過(guò)程語(yǔ)句
CALL 存儲(chǔ)過(guò)程名稱([參數(shù)])
1.1、無(wú)參存儲(chǔ)過(guò)程
示例
創(chuàng)建無(wú)參存儲(chǔ)過(guò)程，查看數(shù)據(jù)庫(kù)版本
CREATE PROCEDURE p_version()
BEGIN
SELECT VERSION();
END;
注意：筆者這里使用的是 navicat 和 mysql5.7.25 版本，如果讀者使用的是 mysql 命令行客戶端，需要使用 DELIMITER $$ ?定義結(jié)束符，因?yàn)樯线呎Z(yǔ)句中的 "；" 會(huì)被mysql 命令行客戶端識(shí)別為結(jié)束符，因此在有"；"的函數(shù)體中，需要讀者額外定義結(jié)束符，如下面代碼
DELIMITER $$
CREATE PROCEDURE p_version()
BEGIN
SELECT VERSION();
END $$
后面的代碼筆者都是直接在navicat上執(zhí)行，因此不再重復(fù)說(shuō)明? DELIMITER $$ ?定義結(jié)束符的事
調(diào)用
CALL p_version();
1.2、有參存儲(chǔ)過(guò)程
聲明存儲(chǔ)過(guò)程參數(shù)需要3個(gè)關(guān)鍵字
IN
OUT
INOUT
IN：表示輸入的參數(shù)
OUT：表示輸出的參數(shù)
INOUT：即可以表示輸入的參數(shù)，也可以表示輸出的參數(shù)
示例sql
CREATE PROCEDURE p_name(IN userid int, OUT username varchar(20))
BEGIN
SELECT name INTO username from user where id = userid;
END;
數(shù)據(jù)庫(kù)中有張user表，有1條數(shù)據(jù)，上面存儲(chǔ)過(guò)程傳入id，返回user的name字段
上面sql中的?name INTO username，是將user表的name賦值給存儲(chǔ)過(guò)程的 username，這個(gè)INTO后面會(huì)詳細(xì)說(shuō)明，這里先看運(yùn)行效果
調(diào)用存儲(chǔ)過(guò)程
CALL p_name(1, @username);
SELECT @username;
?@username是一個(gè)變量，用來(lái)接收存儲(chǔ)過(guò)程輸出的值，再通過(guò)SELECT @username;顯示出來(lái)
運(yùn)行效果
1.3、mysql變量
mysql變量有系統(tǒng)變量、用戶自定義變量、局部變量
1.3.1、系統(tǒng)變量
系統(tǒng)變量：是mysql提供的，用戶不能定義，分全局變量（GLOBAL）、會(huì)話變量（SESSION）
系統(tǒng)變量是2個(gè)@表示，即@@
查看所有系統(tǒng)變量（GLOBAL）
SHOW GLOBAL VARIABLES;
查看所有會(huì)話變量（SESSION）
SHOW SESSION VARIABLES;
查看某一系統(tǒng)變量（GLOBAL）
SELECT @@GLOBAL.變量名
查看某一會(huì)話變量（SESSION）
SELECT @@SESSION.變量名
設(shè)置系統(tǒng)變量（GLOBAL）
SET GLOBAL 變量名 = 值
設(shè)置會(huì)話變量（SESSION）
SET SESSION 變量名 = 值
注意：這里設(shè)置的系統(tǒng)變量（GLOBAL）或會(huì)話變量（SESSION），當(dāng)mysql服務(wù)器重啟后都會(huì)失效；如果想長(zhǎng)期有效需要在mysql配置文件中設(shè)置
示例
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;
SELECT @@GLOBAL.autocommit;
SELECT @@SESSION.autocommit;
SET SESSION autocommit = 0;
運(yùn)行效果
1.3.2、用戶自定義變量
用戶自定義變量：是用戶自己定義的變量，不用提前聲明，在使用的時(shí)候直接 @變量名 即可，作用域是當(dāng)前連接，用1個(gè)@表示
賦值，有4種方式
SET @變量名 = 值
SET @變量名 := 值
SELECT @變量名 := 值
SELECT 字段名 INTO @變量名 FROM 表名
使用用戶自定義變量
SELECT @變量名
示例
SET @demo_name = '霧失樓臺(tái)';
SET @demo_age := 18;
SELECT @demo_gender := '女';
SELECT name INTO @demo_username FROM user;
SELECT @demo_name;
SELECT @demo_age,@demo_gender,@demo_username;
運(yùn)行效果
1.3.3、局部變量
局部變量：在局部生效的變量，使用之前需要用 DECLARE 關(guān)鍵字先聲明，可作為存儲(chǔ)過(guò)程內(nèi)的局部變量和輸入?yún)?shù)，作用范圍是局部變量聲明的存儲(chǔ)過(guò)程的BEGIN?END間
聲明
DECLARE 變量名 變量類(lèi)型
變量類(lèi)型就是數(shù)據(jù)庫(kù)字段的類(lèi)型，int、varchar、date等
賦值，有3種方式
SET 變量名 = 值
SET 變量名 := 值
SELECT 字段名 INTO 變量名 FROM 表名
使用局部變量
SELECT 變量名
示例
CREATE PROCEDURE p_localvar()
BEGIN
DECLARE demo_name VARCHAR(20);
SELECT name into demo_name from user;
SELECT demo_name;
END;
CALL p_localvar();
CREATE PROCEDURE p_localvar2()
BEGIN
DECLARE demo_name VARCHAR(20);
SET demo_name = '尊前談笑人依舊';
SELECT demo_name;
END;
CALL p_localvar2();
CREATE PROCEDURE p_localvar3()
BEGIN
DECLARE demo_name VARCHAR(20);
SET demo_name := '雨打梨花深閉門(mén)，忘了青春，誤了青春';
SELECT demo_name;
END;
CALL p_localvar3();
運(yùn)行效果
2、存儲(chǔ)過(guò)程中條件判斷 if 的使用
存儲(chǔ)過(guò)程中可以寫(xiě) if 條件判斷
語(yǔ)法格式
IF 條件1 THEN 內(nèi)容 ELSEIF 條件2 THEN 內(nèi)容 ELSE? 內(nèi)容 END IF;
示例
CREATE PROCEDURE p_judging_age(IN age int)
BEGIN
DECLARE content VARCHAR(20);
IF age < 18 THEN
SET content := '未成年人';
ELSEIF 18<= age AND age <=65 THEN
SET content := '青年人';
ELSEIF 66<= age && age <=79 THEN
SET content := '中年人';
ELSE
SET content := '老年人';
END IF;
SELECT content;
END;
if 中多條件可以使用 AND 或?OR
CALL p_judging_age(17);
CALL p_judging_age(18);
CALL p_judging_age(19);
CALL p_judging_age(65);
CALL p_judging_age(66);
CALL p_judging_age(98);
運(yùn)行效果
3、存儲(chǔ)過(guò)程中case的使用
case when的作用和 if 判斷類(lèi)似?
語(yǔ)法格式1
CASE case_value ?? ?WHEN when_value1 THEN ?? ??? ?statement_list1 ?? ?WHEN when_value2 THEN ?? ??? ?statement_list2 ?? ?ELSE ?? ??? ?statement_list3 END CASE;
當(dāng)case_value的值符合when_case1時(shí)，執(zhí)行statement_list1
當(dāng)case_value的值符合when_case2時(shí)，執(zhí)行statement_list2
其他情況執(zhí)行 ELSE 中的statement_list3
語(yǔ)法格式2
CASE? ? WHEN search_condition1 THEN ?? ? ?statement_list1 ?? ?WHEN search_condition2 THEN ?? ? ?statement_list2 ? ELSE ?? ? ?statement_list3 END CASE;
當(dāng)search_condition1條件成立時(shí)，執(zhí)行statement_list1
當(dāng)search_condition2條件成立時(shí)，執(zhí)行statement_list2
其他情況執(zhí)行statement_list3
示例語(yǔ)法格式1
先創(chuàng)建一張userinfo表，用于測(cè)試
DROP TABLE IF EXISTS `userinfo`;
CREATE TABLE `userinfo` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`age` int(11) NULL DEFAULT NULL,
`gender` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of userinfo
-- ----------------------------
INSERT INTO `userinfo` VALUES (1, '李白', 27, 'm');
INSERT INTO `userinfo` VALUES (2, '朱淑真', 18, 'f');
INSERT INTO `userinfo` VALUES (3, '張先', 53, NULL);
userinfo表中有3條數(shù)據(jù)
?下面存儲(chǔ)過(guò)程傳入 userinfo 表 id，顯示對(duì)應(yīng) id 用戶的性別信息
CREATE PROCEDURE p_judging_gender(IN userid int)
BEGIN
DECLARE user_gender CHAR(1);
DECLARE gender_string VARCHAR(10);
SELECT gender INTO user_gender from userinfo where id = userid;
CASE user_gender
WHEN 'm' THEN
SET gender_string := '男';
WHEN 'f' THEN
SET gender_string := '女';
ELSE
SET gender_string := '未知';
END CASE;
SELECT gender_string;
END;
調(diào)用
CALL p_judging_gender(1);
CALL p_judging_gender(2);
CALL p_judging_gender(3);
運(yùn)行效果
示例語(yǔ)法格式2
還是上面的例子，傳入 userinfo 表 id，顯示對(duì)應(yīng) id 用戶的性別信息，換一種寫(xiě)法
CREATE PROCEDURE p_judging_gender2(IN userid int)
BEGIN
DECLARE user_gender CHAR(1);
DECLARE gender_string VARCHAR(10);
SELECT gender INTO user_gender from userinfo where id = userid;
CASE
WHEN user_gender = 'm' THEN
SET gender_string := '男';
WHEN user_gender = 'f' THEN
SET gender_string := '女';
ELSE
SET gender_string := '未知';
END CASE;
SELECT gender_string;
END;
調(diào)用
CALL p_judging_gender2(1);
CALL p_judging_gender2(2);
CALL p_judging_gender2(3);
運(yùn)行效果
4、存儲(chǔ)過(guò)程中循環(huán)的使用
在存儲(chǔ)過(guò)程中可以寫(xiě)循環(huán)
4.1、while 循環(huán)
while循環(huán)語(yǔ)法格式
WHILE 條件 DO ?? ?內(nèi)容 END WHILE;
條件為true時(shí)，執(zhí)行內(nèi)容
示例
CREATE PROCEDURE p_circulate()
BEGIN
DECLARE num int;
SET num := 10;
WHILE num > 0 DO
SELECT num;
set num := num - 1;
END WHILE;
END;
調(diào)用
CALL p_circulate();
運(yùn)行效果
4.2、repeat 循環(huán)
repeat循環(huán)語(yǔ)法格式
REPEAT ?? ?內(nèi)容 UNTIL 條件? END REPEAT;
repeat 循環(huán)先執(zhí)行內(nèi)容，然后判斷條件，如果條件為 true 則退出循環(huán)，否則繼續(xù)循環(huán)
示例
CREATE PROCEDURE p_circulate_repeat()
BEGIN
DECLARE num int;
SET num := 10;
REPEAT
SELECT num;
set num := num - 1;
UNTIL num <= 0
END REPEAT;
END;
調(diào)用
CALL p_circulate_repeat();
運(yùn)行效果
4.3、loop 循環(huán)
?語(yǔ)法格式
label: LOOP ?? ?statement_list
?? ?IF exit_condition THEN ?? ??? ?LEAVE label;? ?? ?END IF;? END LOOP label;
label是一個(gè)標(biāo)記，通過(guò)它控制循環(huán)的結(jié)束
loop 循環(huán)中使用?LEAVE label; 退出循環(huán)，如果loop中沒(méi)有?LEAVE label;則是死循環(huán)
除了LEAVE外循環(huán)中還可以有 ITERATE，ITERATE的作用是跳過(guò)當(dāng)前循環(huán)剩下的內(nèi)容，直接進(jìn)入下一次循環(huán)
示例1
CREATE PROCEDURE p_circulate_loop()
BEGIN
DECLARE num int;
SET num := 10;
looplabel: LOOP
IF num <= 0 THEN
LEAVE looplabel;
END IF;
SELECT num;
SET num := num - 1;
END LOOP looplabel;
END;
調(diào)用
CALL p_circulate_loop();
運(yùn)行效果
示例2
加入?ITERATE，當(dāng) num = 5 時(shí)跳過(guò)
CREATE PROCEDURE p_circulate_loop2()
BEGIN
DECLARE num int;
SET num := 10;
looplabel: LOOP
IF num <= 0 THEN
LEAVE looplabel;
END IF;
IF num = 5 THEN
SET num := num - 1;
ITERATE looplabel;
END IF;
SELECT num;
SET num := num - 1;
END LOOP looplabel;
END;
調(diào)用
CALL p_circulate_loop2();
運(yùn)行效果
?沒(méi)有輸出5
5、存儲(chǔ)過(guò)程-游標(biāo) CURSOR
游標(biāo)：是保存查詢結(jié)果集的類(lèi)型，在存儲(chǔ)過(guò)程和自定義函數(shù)中可以使用游標(biāo)對(duì)結(jié)果集進(jìn)行處理。游標(biāo)的使用包括聲明、打開(kāi)（OPEN）、獲?。‵ETCH）、關(guān)閉（CLOSE）
聲明
DECLARE 游標(biāo)名稱 CURSOR FOR 查詢語(yǔ)句;
打開(kāi)
OPEN 游標(biāo)名稱;
獲取
FETCH 游標(biāo)名稱 INTO 變量;
關(guān)閉
CLOSE 游標(biāo)名稱;
示例
CREATE PROCEDURE p_cursor()
BEGIN
DECLARE username VARCHAR(10);
DECLARE userage int;
DECLARE count int;
DECLARE userinfo_cursor CURSOR FOR SELECT name, age FROM userinfo;
SELECT count(*) INTO count FROM userinfo;
OPEN userinfo_cursor;
WHILE count > 0 DO
FETCH userinfo_cursor INTO username, userage;
SET count := count - 1;
SELECT username, userage;
END WHILE;
CLOSE userinfo_cursor;
END;
查詢 userinfo 表 name age 字段
查詢 userinfo 表總數(shù)據(jù)條數(shù)用來(lái)計(jì)算循環(huán)次數(shù)，當(dāng)然這里結(jié)束循環(huán)也可以使用 mysql自帶的條件處理程序，這里不做過(guò)多介紹
調(diào)用
CALL p_cursor();
運(yùn)行效果
至此完
柚子快報(bào)激活碼778899分享：數(shù)據(jù)庫(kù) mysql 存儲(chǔ)過(guò)程
好文鏈接
本文內(nèi)容根據(jù)網(wǎng)絡(luò)資料整理，出于傳遞更多信息之目的，不代表金鑰匙跨境贊同其觀點(diǎn)和立場(chǎng)。
轉(zhuǎn)載請(qǐng)注明，如有侵權(quán)，聯(lián)系刪除。
Idealo優(yōu)選網(wǎng)
