柚子快報激活碼778899分享：數(shù)據(jù)庫 mysql 存儲過程

http://yzkb.51969.com/

mysql存儲過程：事先經(jīng)過編譯并且存儲在數(shù)據(jù)庫中的一段SQL語句集合。調(diào)用存儲過程可以減少數(shù)據(jù)庫和應用程序間傳輸?shù)木W(wǎng)絡性能消耗

目錄

1、創(chuàng)建存儲過程和調(diào)用

1.1、無參存儲過程

1.2、有參存儲過程

1.3、mysql變量

1.3.1、系統(tǒng)變量

1.3.2、用戶自定義變量

1.3.3、局部變量

2、存儲過程中條件判斷 if 的使用

3、存儲過程中case的使用

4、存儲過程中循環(huán)的使用

4.1、while 循環(huán)

4.2、repeat 循環(huán)

4.3、loop 循環(huán)

5、存儲過程-游標 CURSOR

1、創(chuàng)建存儲過程和調(diào)用

創(chuàng)建存儲過程語句

CREATE PROCEDURE 存儲過程名稱([參數(shù)]) BEGIN 內(nèi)容 END;

調(diào)用存儲過程使用CALL

調(diào)用存儲過程語句

CALL 存儲過程名稱([參數(shù)])

1.1、無參存儲過程

示例

創(chuàng)建無參存儲過程，查看數(shù)據(jù)庫版本

CREATE PROCEDURE p_version()

BEGIN

SELECT VERSION();

END;

注意：筆者這里使用的是 navicat 和 mysql5.7.25 版本，如果讀者使用的是 mysql 命令行客戶端，需要使用 DELIMITER $$ ?定義結(jié)束符，因為上邊語句中的 "；" 會被mysql 命令行客戶端識別為結(jié)束符，因此在有"；"的函數(shù)體中，需要讀者額外定義結(jié)束符，如下面代碼

DELIMITER $$

CREATE PROCEDURE p_version()

BEGIN

SELECT VERSION();

END $$

后面的代碼筆者都是直接在navicat上執(zhí)行，因此不再重復說明? DELIMITER $$ ?定義結(jié)束符的事

調(diào)用

CALL p_version();

1.2、有參存儲過程

聲明存儲過程參數(shù)需要3個關(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ù)庫中有張user表，有1條數(shù)據(jù)，上面存儲過程傳入id，返回user的name字段

上面sql中的?name INTO username，是將user表的name賦值給存儲過程的 username，這個INTO后面會詳細說明，這里先看運行效果

調(diào)用存儲過程

CALL p_name(1, @username);

SELECT @username;

?@username是一個變量，用來接收存儲過程輸出的值，再通過SELECT @username;顯示出來

運行效果

1.3、mysql變量

mysql變量有系統(tǒng)變量、用戶自定義變量、局部變量

1.3.1、系統(tǒng)變量

系統(tǒng)變量：是mysql提供的，用戶不能定義，分全局變量（GLOBAL）、會話變量（SESSION）

系統(tǒng)變量是2個@表示，即@@

查看所有系統(tǒng)變量（GLOBAL）

SHOW GLOBAL VARIABLES;

查看所有會話變量（SESSION）

SHOW SESSION VARIABLES;

查看某一系統(tǒng)變量（GLOBAL）

SELECT @@GLOBAL.變量名

查看某一會話變量（SESSION）

SELECT @@SESSION.變量名

設置系統(tǒng)變量（GLOBAL）

SET GLOBAL 變量名 = 值

設置會話變量（SESSION）

SET SESSION 變量名 = 值

注意：這里設置的系統(tǒng)變量（GLOBAL）或會話變量（SESSION），當mysql服務器重啟后都會失效；如果想長期有效需要在mysql配置文件中設置

示例

SHOW GLOBAL VARIABLES;

SHOW SESSION VARIABLES;

SELECT @@GLOBAL.autocommit;

SELECT @@SESSION.autocommit;

SET SESSION autocommit = 0;

運行效果

1.3.2、用戶自定義變量

用戶自定義變量：是用戶自己定義的變量，不用提前聲明，在使用的時候直接 @變量名 即可，作用域是當前連接，用1個@表示

賦值，有4種方式

SET @變量名 = 值

SET @變量名 := 值

SELECT @變量名 := 值

SELECT 字段名 INTO @變量名 FROM 表名

使用用戶自定義變量

SELECT @變量名

示例

SET @demo_name = '霧失樓臺';

SET @demo_age := 18;

SELECT @demo_gender := '女';

SELECT name INTO @demo_username FROM user;

SELECT @demo_name;

SELECT @demo_age,@demo_gender,@demo_username;

運行效果

1.3.3、局部變量

局部變量：在局部生效的變量，使用之前需要用 DECLARE 關(guān)鍵字先聲明，可作為存儲過程內(nèi)的局部變量和輸入?yún)?shù)，作用范圍是局部變量聲明的存儲過程的BEGIN?END間

聲明

DECLARE 變量名 變量類型

變量類型就是數(shù)據(jù)庫字段的類型，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 := '雨打梨花深閉門，忘了青春，誤了青春';

SELECT demo_name;

END;

CALL p_localvar3();

運行效果

2、存儲過程中條件判斷 if 的使用

存儲過程中可以寫 if 條件判斷

語法格式

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);

運行效果

3、存儲過程中case的使用

case when的作用和 if 判斷類似?

語法格式1

CASE case_value ?? ?WHEN when_value1 THEN ?? ??? ?statement_list1 ?? ?WHEN when_value2 THEN ?? ??? ?statement_list2 ?? ?ELSE ?? ??? ?statement_list3 END CASE;

當case_value的值符合when_case1時，執(zhí)行statement_list1

當case_value的值符合when_case2時，執(zhí)行statement_list2

其他情況執(zhí)行 ELSE 中的statement_list3

語法格式2

CASE? ? WHEN search_condition1 THEN ?? ? ?statement_list1 ?? ?WHEN search_condition2 THEN ?? ? ?statement_list2 ? ELSE ?? ? ?statement_list3 END CASE;

當search_condition1條件成立時，執(zhí)行statement_list1

當search_condition2條件成立時，執(zhí)行statement_list2

其他情況執(zhí)行statement_list3

示例語法格式1

先創(chuàng)建一張userinfo表，用于測試

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ù)

?下面存儲過程傳入 userinfo 表 id，顯示對應 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);

運行效果

示例語法格式2

還是上面的例子，傳入 userinfo 表 id，顯示對應 id 用戶的性別信息，換一種寫法

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);

運行效果

4、存儲過程中循環(huán)的使用

在存儲過程中可以寫循環(huán)

4.1、while 循環(huán)

while循環(huán)語法格式

WHILE 條件 DO ?? ?內(nèi)容 END WHILE;

條件為true時，執(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();

運行效果

4.2、repeat 循環(huán)

repeat循環(huán)語法格式

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();

運行效果

4.3、loop 循環(huán)

?語法格式

label: LOOP ?? ?statement_list

?? ?IF exit_condition THEN ?? ??? ?LEAVE label;? ?? ?END IF;? END LOOP label;

label是一個標記，通過它控制循環(huán)的結(jié)束

loop 循環(huán)中使用?LEAVE label; 退出循環(huán)，如果loop中沒有?LEAVE label;則是死循環(huán)

除了LEAVE外循環(huán)中還可以有 ITERATE，ITERATE的作用是跳過當前循環(huán)剩下的內(nèi)容，直接進入下一次循環(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();

運行效果

示例2

加入?ITERATE，當 num = 5 時跳過

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();

運行效果

?沒有輸出5

5、存儲過程-游標 CURSOR

游標：是保存查詢結(jié)果集的類型，在存儲過程和自定義函數(shù)中可以使用游標對結(jié)果集進行處理。游標的使用包括聲明、打開（OPEN）、獲?。‵ETCH）、關(guān)閉（CLOSE）

聲明

DECLARE 游標名稱 CURSOR FOR 查詢語句;

打開

OPEN 游標名稱;

獲取

FETCH 游標名稱 INTO 變量;

關(guān)閉

CLOSE 游標名稱;

示例

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ù)用來計算循環(huán)次數(shù)，當然這里結(jié)束循環(huán)也可以使用 mysql自帶的條件處理程序，這里不做過多介紹

調(diào)用

CALL p_cursor();

運行效果

至此完

柚子快報激活碼778899分享：數(shù)據(jù)庫 mysql 存儲過程

http://yzkb.51969.com/

好文鏈接