第6章 事物與存儲(chǔ)過程

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

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

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

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

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

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

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

8、語句實(shí)現(xiàn)由b賬戶向a賬戶轉(zhuǎn)100元錢的轉(zhuǎn)賬功能，具體操作如下：讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.1 事務(wù)管理 6.1.2 事務(wù)的提交事務(wù)的提交 （1）執(zhí)行轉(zhuǎn)賬語句，不提交事務(wù)START TRANSACTION;UPDATE account SET money=money+100 WHERE name=a;UPDATE account SET money=money-100 WHERE name=b;開啟事務(wù)b賬戶向a賬戶轉(zhuǎn)賬100元讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.1 事務(wù)管理 6.1.2 事務(wù)的提交事務(wù)的提交 （1）執(zhí)行轉(zhuǎn)賬語句，不提交事務(wù) 上述語句執(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)在事務(wù)中實(shí)現(xiàn)了轉(zhuǎn)賬功能讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.1 事務(wù)管理 6.1.2 事務(wù)的提交事務(wù)的提交 （1）執(zhí)行轉(zhuǎn)賬語句，不提交事務(wù) 此時(shí)，退出數(shù)據(jù)庫(kù)然后重新登錄，并查詢數(shù)據(jù)庫(kù)中各賬戶的余額信息，查詢結(jié)果如下：mysql SELECT * FROM account;+-+-+-+| id | nam

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

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

12、能 結(jié)論：事務(wù)中的操作都是手動(dòng)提交的，因此在操作完事務(wù)時(shí)，一定要使用COMMIT語句提交事務(wù)，否則事務(wù)操作會(huì)失敗。讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.1 事務(wù)管理 6.1.3 事務(wù)的回滾事務(wù)的回滾 在操作一個(gè)事務(wù)時(shí)，如果發(fā)現(xiàn)當(dāng)前事務(wù)中的操作是不合理的，此時(shí)只要還沒有提交事務(wù)，就可以通過回滾來取消當(dāng)前事務(wù)。 為了演示事務(wù)的回滾操作，在6.1.2小節(jié)的基礎(chǔ)上進(jìn)行操作，這時(shí)的a賬戶有1000元，b賬戶有1000元，開啟一個(gè)事務(wù)，通過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;開啟事務(wù)a賬戶向b賬戶轉(zhuǎn)賬100元讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.1 事務(wù)管理 6.1.3 事務(wù)的回滾事務(wù)的回滾 上述語句執(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教學(xué)更簡(jiǎn)單，

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

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

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

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

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

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

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

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

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

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

24、代碼如下： 上述語句將student表中name為rose的同學(xué)的成績(jī)和性別分別存入到了變量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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 定義條件是事先定義程序執(zhí)行過程中遇到的問題，處理程序定義了在遇到這些問題時(shí)應(yīng)當(dāng)采取的處理方式，并且保證存儲(chǔ)過程在遇到警告或

25、錯(cuò)誤時(shí)能繼續(xù)執(zhí)行。讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 1、定義條件 在編寫存儲(chǔ)過程時(shí)，定義條件使用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的錯(cuò)誤，sqlstate_value是長(zhǎng)度為5的字符串類型錯(cuò)誤代碼，mysql_error_code為數(shù)值類型的錯(cuò)誤代碼。讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 【例6-4】定義“ERROR1148（42000）”錯(cuò)誤，名稱為command_not_allowed。可以用兩種不同的方法類來定義，具體代碼如下：/方法一：使用sqlstate_valueDECLARE command_not_allowed CONDITION FOR SQLSTATE42000;/方法二：使用my

27、sql_error_codeDECLARE command_not_allowed CONDITION FOR 1148;讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(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為錯(cuò)誤處理方式，參數(shù)取3個(gè)值：CONTINUE、EXIT和UNDO。CONTINUE表示遇到錯(cuò)誤不處理，繼續(xù)執(zhí)行；EXIT遇到錯(cuò)誤馬上退出；UNDO表示遇到錯(cuò)誤后撤回之前的操作，MySQL中暫時(shí)不支持這樣的操作。sp_statement參數(shù)為程序語句段，表示在遇到定義的錯(cuò)誤時(shí)，需要執(zhí)行的存儲(chǔ)過程；condition_value表示錯(cuò)誤類型。讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 2、定義處理程序 condition_value表示錯(cuò)誤類型，可

29、以有以下取值： SQLSTATEVALUE sqlstate_value 包含5個(gè)字符的字符串錯(cuò)誤值。 condition_name表示DECLARE CONDITION 定義的錯(cuò)誤條件名稱。 SQLWARNING匹配所有以01開頭的SQLSTATE錯(cuò)誤代碼。 NOT FOUND匹配所有以02開頭的SQLSTATE錯(cuò)誤代碼。 SQLEXCEPTION匹配所有沒有被SQLWARNING或NOT FOUND捕獲的SQLSTATE錯(cuò)誤代碼。 mysql_error_code匹配數(shù)值類型錯(cuò)誤代碼。讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(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錯(cuò)誤就執(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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.3 定義條件和處理程序定義條件和處理程序 接下來，通過一

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

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

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

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

36、ndition THEN statement_list ELSEIF expr_condition THEN statement_list ELSE statement_listEND IFexpr_condition表示判斷條件，statement_list表示SQL語句列表，它可以包括一個(gè)或多個(gè)語句。如果expr_condition求值為TRUE，相應(yīng)的SQL語句列表就會(huì)被執(zhí)行；如果沒有expr_condition匹配，則ELSE子句里的語句列表被執(zhí)行。讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 2、CASE語句 CASE是另一個(gè)進(jìn)行條件判斷的語句，該語句有兩種語句格式，第一種格式如下：CASE case_exprWHEN w

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

39、使用流程控制的使用 【例6-7】使用CASE流程控制語句的第一種格式，判斷val值等于1、等于2，或者兩者都不等，語句如下： 在上述代碼中，當(dāng)val值為1，輸出字符串”val is 1”；當(dāng)val值為2時(shí)，輸出字符串“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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 2、

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

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

42、加1的操作。當(dāng)id值小于10時(shí)，循環(huán)重復(fù)執(zhí)行；當(dāng)id值大于或者等于10時(shí)，使用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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 4、LEAVE語句 LEAVE語句用于退出任何被標(biāo)注的流程控制構(gòu)造。 LEAVE語句基本語法格式如下： 在上述語法結(jié)構(gòu)中，label表示循環(huán)的標(biāo)志。通常情況下，LEAVE語句與BEGINEN

43、D、循環(huán)語句一起使用。LEAVE lable讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 6、REPEAT語句 REPEAT語句用于創(chuàng)建一個(gè)帶有條件判斷的循環(huán)過程，每次語句執(zhí)行完畢之后，會(huì)對(duì)條件表達(dá)式進(jìn)行判斷，如果表達(dá)式為真，則循環(huán)結(jié)束；否則重復(fù)執(zhí)行循環(huán)中的語句。 REPE

45、AT語句的基本語法格式如下：repeat_lable: REPEATstatement_listUNTIL expr_conditionEND REPEATrepeat_lable讓IT教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(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教學(xué)更簡(jiǎn)單，讓IT學(xué)習(xí)更有效6.2 存儲(chǔ)過程的創(chuàng)建 6.2.5 流程控制的使用流程控制的使用 7、WHILE語句 W

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

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

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

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