第6章 事物與存儲過程

1、讓IT教學更簡單，讓IT學習更有效讓IT教學更簡單，讓IT學習更有效第六章 事務與存儲過程事務管理存儲過程的使用存儲過程的創(chuàng)建綜合案例讓IT教學更簡單，讓IT學習更有效 學習目標掌握開啟、提交和回滾事務、 存儲過程的創(chuàng)建和使用12熟悉事務的四種隔離級別3了解事務的概念掌握了解熟悉讓IT教學更簡單，讓IT學習更有效 目錄事務管理6.16.1點擊查看本小節(jié)知識架構(gòu)存儲過程的創(chuàng)建6.26.2點擊查看本小節(jié)知識架構(gòu)存儲過程的使用6.36.3綜合案例存儲過程應用6.46.4點擊查看本小節(jié)知識架構(gòu)讓IT教學更簡單，讓IT學習更有效 知識架構(gòu)6.1 事務管理返回目錄6.1.1事務的概念6.1.2事務的提交6

2、.1.3事務的回滾事務的隔離級別6.1.4讓IT教學更簡單，讓IT學習更有效 知識架構(gòu)6.2 存儲過程的創(chuàng)建返回目錄6.2.1創(chuàng)建存儲過程6.2.2變量的使用6.2.3定義條件和處理程序6.2.4光標的使用6.2.5流程控制的使用讓IT教學更簡單，讓IT學習更有效 知識架構(gòu)6.3 存儲過程的使用返回目錄6.3.1調(diào)用存儲過程6.3.2查看存儲過程6.3.3修改存儲過程6.3.4刪除存儲過程讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 所謂的事務就是針對數(shù)據(jù)庫的一組操作，它可以由一條或多條SQL語句組成，同一個事務的操作具備同步的特點，如果其中有一條語句無

3、法執(zhí)行，那么所有的語句都不會執(zhí)行，也就是說，事務中的語句要么都執(zhí)行，要么都不執(zhí)行。 開啟事務的語句，具體如下： 提交事務的語句，具體如下START TRANSACTION;COMMIT;讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 回滾事務的語句，具體如下： 需要注意的是， ROLLBACK語句只能針對未提交的事務執(zhí)行回滾操作，已提交的事務是不能回滾的。 接下來通過一個轉(zhuǎn)賬的案例來演示如何使用事務。ROLLBACK;讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 在演示之前首先需要創(chuàng)建一個名稱為chapter06的數(shù)

4、據(jù)庫，并且在chapter06中創(chuàng)建一個account表，插入相應的數(shù)據(jù)，SQL語句具體如下：讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 【例6-1】首先開啟一個事務，然后通過UPDATE語句將a賬戶的100元錢轉(zhuǎn)給b賬戶，最后提交事務，具體語句如下： 上述語句執(zhí)行成功后，可以使用SELECT語句來查詢account表中的余額，查詢結(jié)果如下：讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 事務有很嚴格的定義，它必須同時滿足四個特性，針對這四個特性的講解，具體如下： （1）原子性（Atomicity） 原子性是指一個

5、事務必須被視為一個不可分割的最小工作單元，只有事務中所有的數(shù)據(jù)庫操作都執(zhí)行成功，才算整個事務執(zhí)行成功，事務中如果有任何一個SQL語句執(zhí)行失敗，已經(jīng)執(zhí)行成功的SQL語句也必須撤銷，數(shù)據(jù)庫的狀態(tài)退回到執(zhí)行事務前的狀態(tài)。讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 事務有很嚴格的定義，它必須同時滿足四個特性，針對這四個特性的講解，具體如下： （2）一致性（Consistency） 一致性是指事務將數(shù)據(jù)庫從一種狀態(tài)轉(zhuǎn)變?yōu)橄乱环N一致的狀態(tài)。例如，在表中有一個字段為姓名，具有唯一約束，即姓名不能重復，如果一個事務對姓名進行了修改，使姓名變得不唯一了，這就破壞了事務的

6、一致性要求，如果事務中的某個動作失敗了，系統(tǒng)可以自動撤銷事務，返回初始化的狀態(tài)。讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 事務有很嚴格的定義，它必須同時滿足四個特性，針對這四個特性的講解，具體如下： （3）隔離性（Isolation） 隔離性還可以稱為并發(fā)控制、可串行化、鎖等，當多個用戶并發(fā)訪問數(shù)據(jù)庫時，數(shù)據(jù)庫為每一個用戶開啟的事務，不能被其他事務的操作數(shù)據(jù)所干擾，多個并發(fā)事務之間要相互隔離。讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.1 事務的概念事務的概念 事務有很嚴格的定義，它必須同時滿足四個特性，針對這四個特性的講解，具體如下

7、： （4）持久性（Durability） 事務一旦提交，其所做的修改就會永久保存到數(shù)據(jù)庫中，即使數(shù)據(jù)庫發(fā)生故障也不應該對其有任何影響。需要注意的是，事務的持久性不能做到100%的持久，只能從事務本身的角度來保證永久性，而一些外部原因?qū)е聰?shù)據(jù)庫發(fā)生故障，如硬盤損壞，那么所有提交的數(shù)據(jù)可能都會丟失。讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.2 事務的提交事務的提交 在數(shù)據(jù)庫中事務中的提交操作需要用戶進行確認，當用戶確認提交后，事務中的操作才會執(zhí)行成功，這個過程就是手動提交的過程。 為了說明事務的提交方式為手動提交，接下來，在例6-1的基礎上進行操作，開啟一個事務，使用UPDATE

8、語句實現(xiàn)由b賬戶向a賬戶轉(zhuǎn)100元錢的轉(zhuǎn)賬功能，具體操作如下：讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.2 事務的提交事務的提交 （1）執(zhí)行轉(zhuǎn)賬語句，不提交事務START TRANSACTION;UPDATE account SET money=money+100 WHERE name=a;UPDATE account SET money=money-100 WHERE name=b;開啟事務b賬戶向a賬戶轉(zhuǎn)賬100元讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.2 事務的提交事務的提交 （1）執(zhí)行轉(zhuǎn)賬語句，不提交事務 上述語句執(zhí)行成功后，使用SELECT語句來

9、查詢account表中的余額，查詢結(jié)果如下：mysql SELECT * FROM account;+-+-+-+| id | name | money |+-+-+-+| 1 | a | 1000 | 2 | b | 1000 |+-+-+-+2 rows in set (0.00 sec)在事務中實現(xiàn)了轉(zhuǎn)賬功能讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.2 事務的提交事務的提交 （1）執(zhí)行轉(zhuǎn)賬語句，不提交事務 此時，退出數(shù)據(jù)庫然后重新登錄，并查詢數(shù)據(jù)庫中各賬戶的余額信息，查詢結(jié)果如下：mysql SELECT * FROM account;+-+-+-+| id | nam

10、e | money |+-+-+-+| 1 | a | 900 | 2 | b | 1100 |+-+-+-+2 rows in set (0.00 sec)事務中的轉(zhuǎn)賬操作沒有成功 結(jié)論：在事務中轉(zhuǎn)賬成功后沒有提交事務就退出了數(shù)據(jù)庫，由于事務中的語句不能自動提交，因此當前的操作會被自動取消。讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.2 事務的提交事務的提交 （2）執(zhí)行轉(zhuǎn)賬語句，提交事務START TRANSACTION;UPDATE account SET money=money+100 WHERE name=a;UPDATE account SET money=money

11、-100 WHERE name=b;COMMIT;開啟事務b賬戶向a賬戶轉(zhuǎn)賬100元commit語句用來提交事務讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.2 事務的提交事務的提交 （2）執(zhí)行轉(zhuǎn)賬語句，提交事務 上述語句執(zhí)行成功后，退出數(shù)據(jù)庫然后再重新登錄，使用SELECT語句查詢數(shù)據(jù)庫中各賬戶的余額信息，查詢結(jié)果如下：mysql SELECT * FROM account;+-+-+-+| id | name | money |+-+-+-+| 1 | a | 1000 | 2 | b | 1000 |+-+-+-+2 rows in set (0.00 sec)實現(xiàn)了轉(zhuǎn)賬功

12、能 結(jié)論：事務中的操作都是手動提交的，因此在操作完事務時，一定要使用COMMIT語句提交事務，否則事務操作會失敗。讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.3 事務的回滾事務的回滾 在操作一個事務時，如果發(fā)現(xiàn)當前事務中的操作是不合理的，此時只要還沒有提交事務，就可以通過回滾來取消當前事務。 為了演示事務的回滾操作，在6.1.2小節(jié)的基礎上進行操作，這時的a賬戶有1000元，b賬戶有1000元，開啟一個事務，通過update語句將a賬戶的100元錢轉(zhuǎn)給b賬戶，具體語句如下：START TRANSACTION;UPDATE account SET money=money-100

13、WHERE name=a;UPDATE account SET money=money+100 WHERE name=b;開啟事務a賬戶向b賬戶轉(zhuǎn)賬100元讓IT教學更簡單，讓IT學習更有效6.1 事務管理 6.1.3 事務的回滾事務的回滾 上述語句執(zhí)行成功后，使用SELECT語句查詢A賬戶和B賬戶的金額，查詢結(jié)果如下：mysql SELECT * FROM account;+-+-+-+| id | name | money |+-+-+-+| 1 | a | 900 | 2 | b | 1100 |+-+-+-+2 rows in set (0.00 sec)轉(zhuǎn)賬操作成功讓IT教學更簡單，

14、讓IT學習更有效6.1 事務管理 6.1.3 事務的回滾事務的回滾 從上述結(jié)果可以看出，a賬戶成功給b賬戶轉(zhuǎn)賬100元錢，如果此時a賬戶不想給b賬戶轉(zhuǎn)賬了，由于事務還沒有提交，就可以將事務回滾，具體語句如下： ROLLBACK語句執(zhí)行成功后，再次使用SELECT語句查詢數(shù)據(jù)庫，查詢結(jié)果如下：ROLLBACK;mysql SELECT * FROM account;+-+-+-+| id | name | money |+-+-+-+| 1 | a | 1000 | 2 | b | 1000 |+-+-+-+2 rows in set (0.00 sec)回滾操作成功，當前事務中的操作取消了讓I

15、T教學更簡單，讓IT學習更有效6.1 事務管理 6.1.4 事務的隔離級別事務的隔離級別 數(shù)據(jù)庫是多線程并發(fā)訪問的，所以很容易出現(xiàn)多個線程同時開啟事務的情況，這樣就會出現(xiàn)臟讀、重復讀以及幻讀的情況，為了避免這種情況的發(fā)生，就需要為事務設置隔離級別。 在MySQL中，事務有四種隔離級別，接下來將針對這四種隔離級別進行詳細地講解。 （1）READ UNCOMMITTED（讀未提交） （2）READ COMMITTED（讀提交） （3）REPEATABLE READ（可重復讀） （4）SERIALIZABLE（可串行化）讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.1 創(chuàng)建存儲過

16、程創(chuàng)建存儲過程 創(chuàng)建存儲過程，需要使用CREATE PROCEDURE語句，創(chuàng)建存儲過程的基本語法格式如下：CREATE PROCEDURE sp_name(proc_parameter)characteristicsroutine_bodyCREATE PROCEDURE為用來創(chuàng)建存儲過程的關鍵字sp_name為存儲過程的名稱proc_parameter為指定存儲過程的參數(shù)列表characteristics用于指定存儲過程的特性routime_body是SQL代碼的內(nèi)容，可以用BEGINEND來表示SQL代碼的開始和結(jié)束讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.1 創(chuàng)

17、建存儲過程創(chuàng)建存儲過程 proc_parameter為指定存儲過程的參數(shù)列表，它的參數(shù)列表的形式如下： type表示的參數(shù)類型，可以是MySQL數(shù)據(jù)庫中的任意類型。IN|OUT|INOUTparam_name type輸入?yún)?shù)輸出參數(shù)輸入輸出參數(shù)參數(shù)名稱參數(shù)的類型讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程 characteristics用于指定存儲過程的特性，它的取值說明具體如下： （1）LANGUAGE SQL：說明routine_body部分是由SQL語句組成的，當前系統(tǒng)支持的語言為SQL，SQL是LANGUAGE的唯一值。 （2）NOT

18、DETERMINISTIC：指明存儲過程執(zhí)行的結(jié)果是否正確。DETERMINISTIC表示結(jié)果是正確的，NOT DETERMINISTIC表示結(jié)果是不確定的。如果沒有指定任意一個值，默認為NOT DETERMINISTIC。 （3）CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA：指明子程序使用SQL語句的限制。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程 characteristics用于指定存儲過程的特性，它的取值說明具體如下： （4）SQL SECURITYDEFINER|INVOK

19、ER：指明誰有權(quán)限來執(zhí)行。DEFINER表示只有定義者才能執(zhí)行。INCOKER表示擁有權(quán)限的調(diào)用者可以執(zhí)行。默認情況下，系統(tǒng)指定為DEFINER。 （5）COMMENTstring：注釋信息，可以用來描述存儲過程。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程 接下來通過一個案例演示一下存儲過程的創(chuàng)建，首先在數(shù)據(jù)庫chapter06中創(chuàng)建表student，創(chuàng)建student表的SQL語句如下所示：建表語句讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程 然后使用INSERT語句向student表中插入

20、4條記錄，INSERT語句如下所示：讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.1 創(chuàng)建存儲過程創(chuàng)建存儲過程 【例6-2】創(chuàng)建一個查看student表的存儲過程，其創(chuàng)建語句如下： 上述代碼創(chuàng)建了一個存儲過程Proc，每次調(diào)用這個存儲過程的時候都會執(zhí)行SELECT語句查看表的內(nèi)容，代碼的執(zhí)行過程如下：將將MySQL的結(jié)束符設置為的結(jié)束符設置為/讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.2 變量的使用變量的使用 在MySQL中，變量可以在子程序中聲明并使用，這些變量的作用范圍是在BEGINEND程序中。 在存儲過程中使用DECLARE語句定義變量，具體

21、語法格式如下： 上述語法格式中，var_name為局部變量的名稱。DEFAULT value子句給變量提供一個默認值。該值除了可以被聲明為一個常數(shù)之外，還可以被指定為一個表達式。如果沒有DEFAULT子句，變量的初始值為NULL。DECLARE var_name,varnamedate_typeDEFAULT value;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.2 變量的使用變量的使用 定義一個名稱為myvariable的變量，類型為INT類型，默認值為100，示例代碼如下： 定義變量之后，為變量賦值可以改變變量的默認值，MySQL中使用SET語句為變量賦值，語法格式

22、如下：DECLARE myvariable INT DEFAULT 100;SET var_name = expr,var_name = expr;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.2 變量的使用變量的使用 聲明3個變量，分別為var1、var2、var3，數(shù)據(jù)類型為INT，使用SET為變量賦值，示例代碼如下：DECLARE var1,var2,var3 INT;SET var1=10,var2=20;SET var3=var1+var2;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.2 變量的使用變量的使用 除了可以使用SET語句為變量賦值

23、外，MySQL中還可以通過SELECTINTO為一個或多個變量賦值，該語句可以把選定的列直接存儲到對應位置的變量。 使用SELECTINTO的具體語法格式如下： 在上述語法格式中，col_name表示字段名稱；var_name表示定義的變量名稱；table_expr表示查詢條件表達式，包括表名稱和WHERE子句。SELECT col_name INTO var_name table_expr;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.2 變量的使用變量的使用 【例6-3】聲明變量s_grade和s_gender，通過SELECTINTO語句查詢指定記錄并為變量賦值，具體

24、代碼如下： 上述語句將student表中name為rose的同學的成績和性別分別存入到了變量s_grade和s_gender中。DECLARE s_grade FLOAT;DECLARE s_gender CHAR(2);SELECT grade, gender INTO s_grade, s_genderFROM student WHERE name = rose;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 定義條件是事先定義程序執(zhí)行過程中遇到的問題，處理程序定義了在遇到這些問題時應當采取的處理方式，并且保證存儲過程在遇到警告或

25、錯誤時能繼續(xù)執(zhí)行。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 1、定義條件 在編寫存儲過程時，定義條件使用DECLARE語句，語法格式如下：DECLARE condition_name CONDITION FOR condition_type;/ condition_type的兩種形式：condition_type:SQLSTATEVALUE sqlstate_value|mysql_error_codecondition_name表示所定義的條件的名稱；condition_type表示條件的類型；sqlstate_value和m

26、ysql_error_code都可以表示MySQL的錯誤，sqlstate_value是長度為5的字符串類型錯誤代碼，mysql_error_code為數(shù)值類型的錯誤代碼。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 【例6-4】定義“ERROR1148（42000）”錯誤，名稱為command_not_allowed?？梢杂脙煞N不同的方法類來定義，具體代碼如下：/方法一：使用sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE42000;/方法二：使用my

27、sql_error_codeDECLARE command_not_allowed CONDITION FOR 1148;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 2、定義處理程序 MySQL書用DECLARE語句定義處理程序，具體語法格式如下：DECLARE handler_type HANDLER FOR condition_value, sp_statementhandler_type:CONTINUE|EXIT|UNDOcondition_value:|condition_name|SQLWARNING|NOT FOUN

28、D|SQLEXCEPTION|mysql_error_codehandler_type為錯誤處理方式，參數(shù)取3個值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯誤不處理，繼續(xù)執(zhí)行；EXIT遇到錯誤馬上退出；UNDO表示遇到錯誤后撤回之前的操作，MySQL中暫時不支持這樣的操作。sp_statement參數(shù)為程序語句段，表示在遇到定義的錯誤時，需要執(zhí)行的存儲過程；condition_value表示錯誤類型。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 2、定義處理程序 condition_value表示錯誤類型，可

29、以有以下取值： SQLSTATEVALUE sqlstate_value 包含5個字符的字符串錯誤值。 condition_name表示DECLARE CONDITION 定義的錯誤條件名稱。 SQLWARNING匹配所有以01開頭的SQLSTATE錯誤代碼。 NOT FOUND匹配所有以02開頭的SQLSTATE錯誤代碼。 SQLEXCEPTION匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯誤代碼。 mysql_error_code匹配數(shù)值類型錯誤代碼。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序

30、 【例6-5】定義處理程序的幾種方式，具體代碼如下：第一種方法是捕獲sqlstate_value值。如果遇到sqlstate_value值為“42S02”,則執(zhí)行CONTINUE操作，并且輸出“NO_SUCH_TABLE” 信息。第二種方法是捕獲mysql_error_code值，如果遇到mysql_error_code值為1146，則執(zhí)行CONTINUE操作，并且輸出“NO_SUCH_TABLE” 信息。第三種方法是先定義條件，然后再調(diào)用條件。這里先定義no_such_table條件，遇到1146錯誤就執(zhí)行CONTINUE操作。第四種方法是使用SQLWARNING，SQLWARNING捕獲所

31、有以01開頭的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出“ERROR”信息。第五種方法是使用NOT FOUND, NOT FOUND捕獲所有以02開頭的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出“NO_SUCH_TABLE”信息。第六種方法是使用SQLEXCEPTION，SQLEXCEPTION捕獲所有沒有被SQLWARNING或NOT FOUND捕獲的sqlstate_value值，然后執(zhí)行EXIT操作，并且輸出“ERROR”信息。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 接下來，通過一

32、個案例來演示如何定義條件和處理程序，如例6-6所示。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.4 光標的使用光標的使用 在編寫存儲過程時，查詢語句可能會返回多條記錄，如果數(shù)據(jù)量非常大，則需要使用光標來逐條讀取查詢結(jié)果集中的記錄。光標是一種用于輕松處理多行數(shù)據(jù)的機制。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.4 光標的使用光標的使用 1、光標的聲明 光標必須聲明在聲明變量、條件之后，聲明處理程序之前。 MySQL中使用DECLARE關鍵字來聲明光標，聲明光標的具體語法格式如下：DECLARE cursor_name CURSOR FOR selec

33、t_statement 接下來聲明一個名為cursor _student的光標，示例代碼如下：DECLARE cursor_student CURSOR FOR select s_name,s_gender FROM student;cursor_name表示光標的名稱；select_statement表示SELECT語句的內(nèi)容，返回一個用于創(chuàng)建光標的結(jié)果集。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.4 光標的使用光標的使用 2、光標的使用 MySQL中打開和使用光標的語法格式如下：OPEN cursor_nameFETCH cursor_name INTO var_n

34、ame,var_namecursor_name表示參數(shù)的名稱；var_name表示將光標中的SELECT語句查詢出來的信息存入該參數(shù)中。 需要注意的是，var_name必須在聲明光標之前就定義好。 使用名稱為cursor_student的光標。將查詢出來的信息存入s_name和s_ gender中，示例代碼如下：FETCH cursor_student INTO s_name, s_gender;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.4 光標的使用光標的使用 3、光標的關閉 關閉光標的語法格式如下： 值得一提的是，如果沒有明確的關閉光標，它會在其聲明的復合語句的末尾

35、被關閉。CLOSE cursor_name讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 流程控制語句用于將多個SQL語句劃分或組合成符合業(yè)務邏輯的代碼塊。 MySQL中的流程控制語句包括：IF語句、CASE語句、LOOP語句、WHILE語句、LEAVE語句、ITERATE語句、REPEAT語句和WHILE語句。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 1、IF語句 IF語句是指如果滿足某種條件，就根據(jù)判斷的結(jié)果為TRUE或FALSE執(zhí)行相應的語句，其語法格式如下：IF expr_co

36、ndition THEN statement_list ELSEIF expr_condition THEN statement_list ELSE statement_listEND IFexpr_condition表示判斷條件，statement_list表示SQL語句列表，它可以包括一個或多個語句。如果expr_condition求值為TRUE，相應的SQL語句列表就會被執(zhí)行；如果沒有expr_condition匹配，則ELSE子句里的語句列表被執(zhí)行。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 1、IF語句 接下來演示一下IF語句的

37、用法，示例代碼如下：IF val IS NULLTHEN SELECT val is NULL;ELSE SELECT val is not NULL;END IF;上述代碼中，判斷val值是否為空，如果val值為空，輸出字符串“val is NULL”；否則輸出字符串“val is not NULL”。需要注意的是，IF語句都需要使用END IF來結(jié)束，不可省略。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 2、CASE語句 CASE是另一個進行條件判斷的語句，該語句有兩種語句格式，第一種格式如下：CASE case_exprWHEN w

38、hen_value THEN statement_listWHEN when_value THEN statement_listELSE statement_listEND CASE在上述語法格式中，case_expr表示條件判斷的表達式，決定了哪一個WHEN子句會被執(zhí)行；when_value表示表達式可能的值，如果某個when_value表達式與case_expr表達式結(jié)果相同，則執(zhí)行對應THEN關鍵字后的statement_list中的語句，statement_list表示不同when_value值的執(zhí)行語句。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的

39、使用流程控制的使用 【例6-7】使用CASE流程控制語句的第一種格式，判斷val值等于1、等于2，或者兩者都不等，語句如下： 在上述代碼中，當val值為1，輸出字符串”val is 1”；當val值為2時，輸出字符串“val is 2”；否則輸出字符串“val is not 1 or 2”。CASE valWHEN 1 THEN SELECT val is 1;WHEN 2 THEN SELECT val is 2;ELSE SELECT val is not 1or 2;END CASE;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 2、

40、CASE語句 CASE語句的第二種格式如下： 需要注意的是，這里講解的用在存儲過程里的CASE語句與“控制流程函數(shù)”里描述的SQL CASE表達式CASE語句有些不同。存儲過程里的CASE語句不能有ELSE NULL子句，并且用END CASE替代END來終止。CASEWHEN expr_condition THEN statement_listWHEN expr_condition THEN statement_listELSE statement_listEND CASE;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 3、LOOP語句

41、LOOP循環(huán)語句用來重復執(zhí)行某些語句，與IF和CASE語句相比，LOOP只是創(chuàng)建一個循環(huán)操作的過程，并不進行條件判斷。LOOP內(nèi)的語句一直重復執(zhí)行直到跳出循環(huán)語句。 LOOP語句的基本格式如下：loop_label:LOOPstatement_listEND LOOP loop_label上述語法格式中，loop_label表示LOOP語句的標注名稱，該參數(shù)可以省略；statement_list表示需要循環(huán)執(zhí)行的語句。讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 【例6-8】使用LOOP語句進行循環(huán)操作，具體代碼如下： 例中，循環(huán)執(zhí)行了id

42、加1的操作。當id值小于10時，循環(huán)重復執(zhí)行；當id值大于或者等于10時，使用LEAVE語句退出循環(huán)。DECLARE id INT DEFAULT 0;add_loop:LOOPSET id=id+1;IF id=10 THEN LEAVE add_loop;END IF;END LOOP add_loop;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 4、LEAVE語句 LEAVE語句用于退出任何被標注的流程控制構(gòu)造。 LEAVE語句基本語法格式如下： 在上述語法結(jié)構(gòu)中，label表示循環(huán)的標志。通常情況下，LEAVE語句與BEGINEN

43、D、循環(huán)語句一起使用。LEAVE lable讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 5、ITERATE語句 ITERATE的意思是再次循環(huán)，ITERATE語句用于將執(zhí)行順序轉(zhuǎn)到語句段的開頭處。 ITERATE語句的基本語法格式如下： 需要注意的是，ITERATE語句只可以出現(xiàn)在LOOP、REPEAT和WHILE語句內(nèi)。ITERATE lable讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 【例6-9】演示了ITERATE語句在LOOP語句內(nèi)的使用，具體代碼如下：CREATE PRO

44、CEDURE doiterate()BEGIN DECLARE p1 INT DEFAULT 0;my_loop:LOOPSET p1=p1+1;IF p120 THEN LEAVE my_loop;END IF;SELECT p1 is between 10 and 20;END LOOP my_loop;END讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 6、REPEAT語句 REPEAT語句用于創(chuàng)建一個帶有條件判斷的循環(huán)過程，每次語句執(zhí)行完畢之后，會對條件表達式進行判斷，如果表達式為真，則循環(huán)結(jié)束；否則重復執(zhí)行循環(huán)中的語句。 REPE

45、AT語句的基本語法格式如下：repeat_lable: REPEATstatement_listUNTIL expr_conditionEND REPEATrepeat_lable讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 【例6-10】演示使用REPEAT語句執(zhí)行循環(huán)過程，具體代碼如下：DECLARE id INT DEFAULT 0;REPEATSET id=id+1;UNTIL id=10;END REPEAT;讓IT教學更簡單，讓IT學習更有效6.2 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 7、WHILE語句 W

46、HILE語句創(chuàng)建一個帶條件判斷的循環(huán)過程，與REPEAT不同的是，WHILE在語句執(zhí)行時，先對指定的表達式進行判斷，如果為真，則執(zhí)行循環(huán)內(nèi)的語句，否則退出循環(huán)。 REPEAT語句的基本語法格式如下：while_lable: WHILE expr_condition DOStatement_listEND WHILE while_lable在上述語法格式中，while_lable為WHILE語句的標注名稱；expr_condition為進行判斷的表達式，如果表達式結(jié)果為真，WHILE語句內(nèi)的語句或語句群被執(zhí)行，直至expr_condition為假，退出循環(huán)讓IT教學更簡單，讓IT學習更有效6.2

47、 存儲過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 【例6-11】演示了使用WHILE語句進行循環(huán)操作，具體代碼如下：DECLARE i INT DEFAULT 0;WHILE i 10 DOSET i=i+1;END WHILE;讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.1 調(diào)用存儲過程調(diào)用存儲過程 存儲過程必須使用CALL語句調(diào)用，并且存儲過程和數(shù)據(jù)庫相關，如果要執(zhí)行其他數(shù)據(jù)庫中的存儲過程，需要指定數(shù)據(jù)庫名稱。 調(diào)用存儲過程的語法格式如下： 上述語法格式中，sp_name為存儲過程的名稱，parameter為存儲過程的參數(shù)。CALL sp_name(para

48、meter,)讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.1 調(diào)用存儲過程調(diào)用存儲過程 【例6-12】定義一個名為CountProc1的存儲過程，然后調(diào)用這個存儲過程，具體操作如下： 1、定義存儲過程： 2、調(diào)用存儲過程：讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.1 調(diào)用存儲過程調(diào)用存儲過程 3、查看返回結(jié)果：讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.2 查看存儲過程查看存儲過程 1、SHOW STATUS語句查看存儲過程的狀態(tài) 使用SHOW STATUS語句查看，基本語法結(jié)構(gòu)如下： 上述語法格式中，PROCEDURE和FU

49、NCTION分別表示查看存儲過程和函數(shù)，LIKE語句表示匹配的名稱。CALL sp_name(parameter,)讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.2 查看存儲過程查看存儲過程 【例6-13】SHOW STATUS 語句的示例代碼如下： 代碼執(zhí)行如下： SHOW PROCEDURE STATUS LIKEC%G讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.2 查看存儲過程查看存儲過程 2、SHOW CREATE語句查看存儲過程的狀態(tài) 使用SHOW CREATE語句查看，基本語法格式如下：SHOW CREATEPROCEDURE|FUNCTION sp_name讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.2 查看存儲過程查看存儲過程 【例6-14】SHOW CREATE 語句的示例代碼如下： 代碼執(zhí)行如下：SHOW CREATE PROCEDURE chapter06.CountProc1G讓IT教學更簡單，讓IT學習更有效6.3 存儲過程的使用 6.3.2 查看存儲過程查看存儲過程 3、從information_schema.Routines表中查看存儲過程的信息 在MySQL中存儲過程和函數(shù)的