目錄
?前言
一、創(chuàng)建存儲過程
二、在存儲過程中使用變量
1.定義變量
2.為變量賦值
三、光標的使用
1.打開光標
2.打開光標
3.使用光標
4.關(guān)閉光標
四、流程控制的作用
1.IF語句
2.CASE語句
3.LOOP語句
4.LEAVE語句
5.ITERATE語句
6.REPEAT語句
7.WHILE語句
五、流程控制綜合運用
六、查看存儲過程
七、存儲過程的刪除
總結(jié)
????大家好，我是會飛的魚-blog，今天我來給大家介紹一下Mysql，有不足之處，請大家多多指教。感謝大家支持?。?！
前言
????????存儲過程和函數(shù)是在數(shù)據(jù)庫中定義的一些SQL語句的集合，然后直接調(diào)用這些存儲過程和函數(shù)來執(zhí)行已經(jīng)定義好的SQL語句。存儲過程和函數(shù)可以避免開發(fā)人員重復(fù)編寫相同的SQL語句。而且，存儲過程和函數(shù)是在MySQL服務(wù)器中存儲和執(zhí)行的，可以減少客戶器端和服務(wù)端的數(shù)據(jù)傳輸。?通過本次的學(xué)習(xí)，將了解存儲過程的定義、作用，還可以了解創(chuàng)建、使用、查看、修改及刪除存儲過程及函數(shù)的方法。
???????
一、創(chuàng)建存儲過程
????????創(chuàng)建存儲過程和函數(shù)是指將經(jīng)常使用的一組SQL語句組合在一起，并將這些SQL語句當作一個整體存儲在MySQL服務(wù)器中。存儲程序可以分為存儲過程和函數(shù)。在MySQL中創(chuàng)建存儲過程使用的語句CREATE PROCEDURE。其語法形式如下：
????????CREATE PROCEDURE procedure_name([proc_param[,…]])???
???????????????? routine_body??
????????在上述語句中，參數(shù)procedure_name表示所要創(chuàng)建的存儲過程名字，參數(shù)proc_param表示存儲過程的參數(shù)，參數(shù)routine_body表示存儲過程的SQL語句代碼，可以用BEGIN…END來標志SQL語句的開始和結(jié)束。
????????提示: 在具體創(chuàng)建存儲過程時，存儲過程名不能與已經(jīng)存在的存儲過程名重名，實戰(zhàn)中推薦存儲過程名命名為procedure_xxx或者proc_xxx。
????????proc_param中每個參數(shù)的語法形式如下：
??????????????????? [IN|OUT|INOUT] param_name type
????????在上述語句中，每個參數(shù)由三部分組成，分別為輸入/輸出類型、參數(shù)名和參數(shù)類型。其中，輸入/輸出類型有三種類型，分別為IN（表示輸入類型）、OUT（表示輸出類型）、INOUT（表示輸入/輸出類型）。param_name表示參數(shù)名；type表示參數(shù)類型，可以是MySQL軟件所支持的任意一個數(shù)據(jù)類型。
【示例11-1】
| mysql>? use school;?? #選擇數(shù)據(jù)庫school???????????????????????????????????????????? mysql> DELIMITER $$?????????????????????????????????????????????? ????????????????????????????????? mysql> create PROCEDURE? proc_delete_student (IN sid int )?????????????????????????????????? ??????? BEGIN ???????? declare cid? int ; ??#定義變量cid??????????????????????????? ???Select class_id into cid from student where id = sid;?#通過查詢語句設(shè)置變量?????????????????????????????????????????????????????????????????????? ???????? delete from grade where id = sid;? #刪除成績表中的記錄? ???????? delete from student where id = sid;?? #刪除學(xué)生表中的記錄?????????????????????????????????????? ????????????? ???????? update class set count=count-1 where id = cid; #更新班級表中的記錄?? ??????? END;??????????????????????????????????????????????????????????????????????? ??????? $$?????????????????????????????????????????????????????????????????????????? ???????? DELIMITER ;???????????????????????????????????????????????????????????????? mysql> ?call proc_delete_student(2);??? #調(diào)用存儲過程?????????????????????????????????????????????????????????? |
二、在存儲過程中使用變量
????????在存儲過程和函數(shù)中，可以定義和使用變量。用戶可以使用關(guān)鍵字DECLARE來定義變量，然后為變量賦值。這些變量的作用范圍是在BEGIN…END程序段中。
1.定義變量
????????????????在MySQL中，可以使用DECLARE關(guān)鍵字來定義變量。定義變量的基本語法如下：
??????????????????? DECLARE var_name[,…] type [DEFAULT value]??
????????????????其中，關(guān)鍵字DECLARE是用來聲明變量的；參數(shù)var_name是變量的名稱，可以同時定義多個變量；參數(shù)type用來指定變量的類型；DEFAULT value子句將變量默認值設(shè)置為value，沒有使用DEFAULT子句時，默認值為NULL。
????????????????定義變量cid，數(shù)據(jù)類型為INT型，默認值為10，代碼如下：
????????????????? DECLARE cid INT DEFAULT 10;???
2.為變量賦值
????????????????在MySQL中可以使用關(guān)鍵字SET來為變量賦值，SET語句的基本語法如下：
?????????????????? ?SET var_name=expr[,var_name=expr]…
????????????????其中，關(guān)鍵字SET用來為變量賦值；參數(shù)var_name是變量的名稱；參數(shù)expr是賦值表達式。一個SET語句可以同時為多個變量賦值，各個變量的賦值語句之間用逗號隔開。
????????????????例如，將變量tmp_id賦值為88，代碼如下：??????????????
??SET tmp_id = 88;
????????????????在MySQL中，還可以使用SELECT…INTO語句為變量賦值。其基本語法如下：
???????????????????? SELECT col_name[,…] INTO var_name[,…]????
??????????????????? ?FROM table_name WHERE condition????????? ? ? ? ? ? 其中，參數(shù)col_name表示查詢的字段名稱；參數(shù)var_name是變量的名稱；參數(shù)table_name指表的名稱；參數(shù)condition指查詢條件。
????????【示例11-2】從表employee中查詢id為3的記錄，將該記錄的id值賦給變量tmp_id，代碼如下：?????????????????
SELECT id INTO tmp_id
????????????????FROM grade WEHRE id=sid;??| mysql>? use school;?? #選擇數(shù)據(jù)庫school???????????????????????????????????????????? mysql>? drop? PROCEDURE if exists query_student_class_info;??????????????????????????????????????????? ????????????????????????????????? mysql> DELIMITER $$??????????????????????????????????????????????????????????????????????????????? mysql> create procedure? query_student_class_info (IN sid int, OUT cname varchar(128), OUT ccount? int)?????????????????? ???????????????? ??????? BEGIN?? ??????????? declare tmp_name varchar(128); ??????????? declare tmp_count int; ??????????? declare tmp_cid? int; ??????????? select class_id into tmp_cid from student where id = sid;???????? ??????????? select name, count into tmp_name, tmp_count from class where id = tmp_cid; ??????????? set cname = tmp_name, ccount = tmp_count; ???????? END;??? ???????? $$?????????????????????????????????????????????????????????????????????????? ???????? DELIMITER ;???????????????????? ???????????????????????????????????????????? mysql> ?call query_student_class_info(4, @name, @count);??? #調(diào)用存儲過程? mysql> ?select @name, @count;???????????????????????????????????????????????????????? |
三、光標的使用
????????查詢語句可能查詢出多條記錄，在存儲過程和函數(shù)中使用光標來逐條讀取查詢結(jié)果集中的記錄。有些書上將光標稱為游標。光標的使用包括聲明光標、打開光標、使用光標和關(guān)閉光標。光標必須聲明在處理程序之前，并且聲明在變量和條件之后。
1.打開光標
????????在MySQL中，可以使用DECLARE關(guān)鍵字來聲明光標，其基本語法如下：
??????????????????? DECLARE cursor_name CURSOR????????
??????????????????? FOR select_statement;?????????????????
????????其中，參數(shù)cursor_name表示光標的名稱；參數(shù)select_statement表示SELECT語句的內(nèi)容。
????????【示例11-2】下面聲明一個名為cur_student的光標，代碼如下：
| mysql>use school;#選擇數(shù)據(jù)庫? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?? mysql> DELIMITER $$??????????????????????????????????????????????? ???????????????????????????????? mysql> create procedure? query_student (IN sid int, OUT cname varchar(128), OUT class_id? int )????????????????????????????????????? ??????? BEGIN????????????????????????????????????????????????? ??????????? DECLARE cur_student CURSOR???????????????????? ??????????????? FOR SELECT name, class_id FROM? student;??? ???????? END;????????????????????????????????????????????????? ???????? $$?????????????????????????????????????????????????????????????????????????? ???????? DELIMITER ;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? |
????????在上面的示例中，光標的名稱為cur_student；SELECT語句部分是從表student中查詢出字段name和class_id的值。
2.打開光標
????????在MySQL中，使用關(guān)鍵字OPEN來打開光標，其基本語法如下：
??????????? OPEN cursor_name;
????????其中，參數(shù)cursor_name表示光標的名稱。
????????下面代碼打開一個名為cur_student的光標，代碼如下：
??????????? OPEN cur_student;
3.使用光標
????????? FETCH cursor_name
????????????? INTO var_name[,var_name…];
????????其中，參數(shù)cursor_name表示光標的名稱；參數(shù)var_name表示將光標中的SELECT語句查詢出來的信息存入該參數(shù)中。var_name必須在聲明光標之前就定義好。
use school;?? #選擇數(shù)據(jù)庫school
DELIMITER $$?
create procedure query_student (IN sid int, OUT cname varchar(128), OUT cid int)???????????????????????????????????????????????????????????????????????????????????
BEGIN
? ? ? ? declare tmp_name varchar(128);
? ? ? ? declare tmp_cid int;
? ? ? ? declare done int default 0;
? ? ? ? declare cur_student CURSOR FOR SELECT name,class_Id from student where id=sid;
????????declare continue handler for not found set done = 1; #將結(jié)束標志綁定到游標上
????????open? cur_student;
?????????select done;
????????fetch cur_student into tmp_name, tmp_cid;
????????select done;
?????????select tmp_name, tmp_cid;
?????????close cur_student;?
????????? set cname = tmp_name, cid = tmp_cid;
????????END;
????????$$
????????DELIMITER ;4.關(guān)閉光標
????????在MySQL中，使用關(guān)鍵字CLOSE來關(guān)閉光標，其基本語法如下：
??????????? CLOSE cursor_name;
????????其中，參數(shù)cursor_name表示光標的名稱。
????????例如: 關(guān)閉一個名為cur_student的光標，代碼如下：
???????????
CLOSE cur_student;????????在上面的示例中，關(guān)閉了這個名稱為cur_student的光標。關(guān)閉了之后就不能使用FETCH來使用光標了。提示
????????如果存儲過程或函數(shù)中執(zhí)行了SELECT語句，并且SELECT語句會查詢出多條記錄，這種情況最好使用光標來逐條讀取記錄，光標必須在處理程序之前且在變量和條件之后聲明，而且光標使用完畢后一定要關(guān)閉。
四、流程控制的作用
1.IF語句
????????IF語句用來進行條件判斷。根據(jù)條件執(zhí)行不同的語句。其語法的基本形式如下：
IF search_condition THEN statement_list??????
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]??????????????????????????
END? IF?????????????????????????? ???????????
????????參數(shù)search_condition表示條件判斷語句；參數(shù)statement_list表示不同條件的執(zhí)行語句。
---【示例11-4】下面是一個IF語句的示例，代碼如下：
?IF age>20 THEN SET @count1=@count1+1;??
??? ELSEIF age=20 THEN @count2=@count2+1;
??? ELSE @count3=@count3+1;???????????????
? END IF;??????????????????? ??????????????????
????????該示例根據(jù)age與20的大小關(guān)系來執(zhí)行不同的SET語句。如果age值大于20，將count1的值加1；如果age值等于20，就將count2的值加1；其他情況將count3的值加1。IF語句都需要使用END IF來結(jié)束。
| #課堂范例 mysql>? use school;?? #選擇數(shù)據(jù)庫school???????????????????????????????????????????????????????????????????????????????????? ???????????????????????????????????? mysql> DELIMITER $$??????????????????????????????????????????????????????????????????????????????? mysql> create procedure proc_test_if (IN input int, OUT output int) ??????? begin ??????????? if input>20 then set input=input+1; ??????????? elseif input=20 then? set input=input+2; ??????????? else? set input = input+3; ??????????? end if; ??????????? set output = input; ??????? end; mysql> ?$$??????????????????????????????????????????????????????????????????????????? ????? mysql>? DELIMITER ;???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? |
2.CASE語句
????????CASE語句可實現(xiàn)比IF語句更復(fù)雜的條件判斷，其語法的基本形式如下：
CASE case_value?????????????????????????????????
WHEN when_value THEN statement_list???????????
[ WHEN when_value THEN statement_list ]?????????
[ELSE statement_list]??????????????????????????????
END CASE????????????????????????????????????????
????????其中，參數(shù)case_value表示條件判斷的變量；參數(shù)when_value表示變量的取值；參數(shù)statement_list表示不同when_value值的執(zhí)行語句。
---【示例11-5】下面是一個CASE語句的示例。代碼如下：
CASE level???????????????????????????????????
????? WHEN 20 THEN SET attack = attack + 5;?
????? WHEN 30 THEN SET attack = attack + 10;
????? WHEN 40 THEN SET attack = attack + 15;
????? ELSE SET attack = attack + 1;
END CASE???????????
????????當級別level值為20時，attack值加5；當級別level值為30時，attack值加10；當級別level值為40時，attack值加15；否則，attack + 1。CASE語句使用END CASE結(jié)束。
3.LOOP語句
????????LOOP語句可以使某些特定的語句重復(fù)執(zhí)行，實現(xiàn)一個簡單的循環(huán)。LOOP語句本身沒有停止循環(huán)，只有遇到LEAVE語句等才能停止循環(huán)。LOOP語句的語句形式如下：
?? [begin_label:] LOOP????????????
?? statement_list?????????????????
?? END LOOP [end_label]?????????
????????其中，參數(shù)begin_label和參數(shù)end_label分別表示循環(huán)開始和結(jié)束的標志，這兩個標志必須相同，而且都可以省略；參數(shù)statement_list表示需要循壞執(zhí)行的語句。
---【示例11-6】下面是一個LOOP語句的示例，代碼如下：
add_num:LOOP????????????????
???? SET @count = @count + 1;
END LOOP add_num;???????????????????該示例循環(huán)執(zhí)行count加1的操作。因為沒有跳出循環(huán)的語句，這個循環(huán)成了一個死循環(huán)。LOOP循環(huán)都以END LOOP結(jié)束。
4.LEAVE語句
????????LEAVE語句主要用于跳出循環(huán)控制，其語法形式如下：
LEAVE label?????????????????
????????其中，參數(shù)label表示循環(huán)的標志。
---【示例11-7】下面是一個LEAVE語句的示例。代碼如下：
add_num: LOOP???????? ????
SET @count=@count + 1;
Select @count;
IF @count = 100 THEN
??? LEAVE add_num;????
END IF;
END LOOP add_num;?????????????該示例循環(huán)執(zhí)行count值加1的操作。當count的值等于100時，LEAVE語句跳出循環(huán)。
5.ITERATE語句
????????ITERATE語句也是用來跳出循環(huán)的語句，但是ITERATE語句是跳出本次循環(huán)，然后直接進入下一次循環(huán)，ITERATE語句的語法形式如下：
? ITERATE label?
????????其中，參數(shù)label表示循環(huán)的標志。
---【示例11-8】下面是一個ITERATE語句的示例。代碼如下：
add_num1:LOOP?????????????
??? Set @count = @count +1
??? IF @count=100 THEN????
??????? LEAVE add_num1??????
??? ELSE IF MOD(@count, 3) = 0 then
??????? ITERATE add_num1;??
???? Select * from student;???
END LOOP add_num1;??????????????該示例循環(huán)執(zhí)行count加1的操作，count的值為100時結(jié)束循環(huán)。如果count的值能夠整除3，就跳出本次循環(huán)，不再執(zhí)行下面的SELECT語句。
????????注意： LEAVE語句和ITERATE語句都用來跳出循環(huán)語句，但是兩者的功能是不一樣的。LEAVE語句是跳出整個循環(huán)，然后執(zhí)行循環(huán)后面的程序，和C++ break 相似。ITERATE語句是跳出本次循環(huán)，然后進入下一次循環(huán),和C++ continue 相似。使用這兩個語句時一定要區(qū)分清楚。
6.REPEAT語句
????????REPEAT語句是有條件控制的循環(huán)語句。當滿足特定條件時，就會跳出循環(huán)語句。REPEAT語句的基本語法形式如下：
[begin_label:] REPEAT???????
??????? statement_list;??????
???? UNTIL search_condition
END REPEAT [end_label]?????
????????其中，參數(shù)statement_list表示循環(huán)的執(zhí)行語句；參數(shù)search_condition表示結(jié)束循環(huán)的條件，滿足該條件時循環(huán)結(jié)束。
---【示例11-9】下面是一個REPEAT語句的示例。代碼如下：
REPEAT???????????????????????
???? SET @count=@count+1;??
???? UNTIL @count=100???????
END REPEAT;??????????????????
????????該示例循環(huán)執(zhí)行count加1的操作，count值為100時結(jié)束循環(huán)。REPEAT循環(huán)都用END REPEAT結(jié)束。
7.WHILE語句
????????WHILE語句也是有條件控制的循環(huán)語句，但WHILE語句和REPEAT語句是不一樣的。WHILE語句是當滿足條件時執(zhí)行循環(huán)內(nèi)的語句。WHILE語句的基本語法形式如下：
[begin_label:] WHILE search_condition DO??
??????? Statement_list?????????????????????
END WHILE [end_label]????????????????????
????????其中，參數(shù)statement_condition表示循環(huán)執(zhí)行的條件，滿足該條件時循環(huán)執(zhí)行；參數(shù)statement_list表示循環(huán)的執(zhí)行語句。
---【示例11-10】下面是一個WHILE語句的示例。代碼如下：
WHILE @count<100 DO??????
??? SET @count = @count + 1;
END WHILE;??????????????????
五、流程控制綜合運用
mysql>? use school;?? #選擇數(shù)據(jù)庫school
mysql> DELIMITER $$???????????????????????????????????????????????????????????????????????????????
mysql> create procedure query_all_students (IN sid int, OUT cname varchar(128), OUT cid int)
BEGIN
????????declare tmp_name varchar(128);??? #必須定義在聲明光標之前
????????declare tmp_cid? int;
?????????declare? done int default 0;
????????declare cur_student CURSOR FOR SELECT name, class_id FROM? student ;
????????declare continue handler for not found set done = 1; #將結(jié)束標志綁定到游標上
????????open? cur_student;
????????read_loop:LOOP????? #循環(huán)讀取
? ??????fetch cur_student into tmp_name, tmp_cid;
????????????????IF done=1 then
????????????????????????Leave read_loop;
????????????????END IF;
????????????????select tmp_name, tmp_cid; ?
????????END LOOP read_loop;
????????close cur_student;
????????set cname = tmp_name, cid = tmp_cid;
END;?
mysql> ?$$
mysql>? DELIMITER ;??
【示例11-12】在學(xué)生表中插入一條記錄，并返回記錄的自增長id
mysql>? use school;?? #選擇數(shù)據(jù)庫school?
mysql> DELIMITER $$??
mysql> create procedure fetch_insert_student_id (IN p_name varchar(128), in p_class_id int, IN p_sex char(1), OUT rid int)
????????????????BEGIN
????????????????????????Insert into student (name, class_id, sex) values(p_name, p_class_id, p_sex);
????????????????????????select last_insert_id() as rid;?
????????????????END;
mysql> ?$$
mysql>? DELIMITER ;?六、查看存儲過程
????????存儲過程創(chuàng)建以后，用戶可以通過SHOW STATUS語句來查看存儲過程的狀態(tài)，也可以通過SHOW CREATE語句來查看存儲過程的定義。用戶也可以通過查詢information_schema數(shù)據(jù)庫下的Routines表來查看存儲過程的信息。本節(jié)將詳細講解查看存儲過程的狀態(tài)與定義的方法。
1.SHOW STATUS語句查看存儲過程
????????在MySQL中，可以通過SHOW STATUS語句。其基本語法形式如下：
?? ??SHOW PROCEDURE STATUS? [ like ‘pattern’ ] ;?
????????其中，參數(shù)PROCEDURE表示查詢存儲過程；參數(shù)LIKE 'pattern'用來匹配存儲過程的名稱。
圖11-13的執(zhí)行結(jié)果顯示了存儲過程的創(chuàng)建時間、修改時間和字符集等信息。
2.使用SHOW CREATE語句查看存儲過程的定義
????????在MySQL中，可以通過SHOW CREATE語句查看存儲過程的狀態(tài)，語法形式如下：
??? SHOW CREATE PROCEDURE proc_name?????
????????其中，參數(shù)PROCEDURE表示查詢存儲過程；參數(shù)proc_name表示存儲過程的名稱。
---【示例11-14】查詢名為proc_delete_student的存儲過程的狀態(tài)，代碼如下，執(zhí)行結(jié)果如下圖所示。
???
SHOW CREATE PROCEDURE proc_delete_student \G
3.從information_schema.Routine表中查看存儲過程的信息
????????存儲過程和函數(shù)的信息存儲在information_schema數(shù)據(jù)庫下的Routines表中?？梢酝ㄟ^查詢該表的記錄來查詢存儲過程和函數(shù)的信息。其基本語法形式如下：
????????SELECT * FROM information_schema.Routines
?????????Where ROUTINE_NAME = ‘proc_name’;
????????其中，字段ROUTINE_NAME是Routines 存儲存儲過程和函數(shù)的列名稱；參數(shù)proc_name表示存儲過程或函數(shù)的名稱。
---【示例11-15】下面從Routines表中查詢名為proc_delete_student的存儲過程信息，具體SQL代碼如下，執(zhí)行結(jié)果如下圖所示。
select routine_definition from information_schema.Routines where ????????routine_name='proc_delete_student';
七、存儲過程的刪除
????????在MySQL中刪除存儲過程通過SQL語句DROP完成：
??? DROP PROCEDURE proc_name;??????? ????
????????在上述語句中，關(guān)鍵字DROP PROCEDURE用來表示實現(xiàn)刪除存儲過程，參數(shù)proc_name表示所要刪除的存儲過程名稱。
---【示例11-16】執(zhí)行SQL語句DROP PROCEDURE，刪除存儲過程對象proc_delete_student，具體步驟如下：文章來源：http://www.zghlxwxcb.cn/news/detail-438875.html
DROP PROCEDURE proc_delete_student;總結(jié)
???以上就是我學(xué)習(xí)的總結(jié)，希望大家一起探討、探討，一起加油，邁向更好的明天?。?！文章來源地址http://www.zghlxwxcb.cn/news/detail-438875.html
到了這里，關(guān)于MySQL——存儲過程和函數(shù)從零基礎(chǔ)到入門必學(xué)教程(涵蓋基礎(chǔ)實戰(zhàn))的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
