《數(shù)據(jù)庫應用技術》課件（共十七章節(jié)-下）

12視圖第章12.1視圖概述視圖是從數(shù)據(jù)庫中的一張或多張表中導出的表。創(chuàng)建視圖時所引用的表稱為基表。視圖中的數(shù)據(jù)并不是像表中那樣存儲在數(shù)據(jù)庫中，它只是讀取基表中的數(shù)據(jù)。對視圖的操作與對表的操作一樣，可以對其進行查詢、修改和刪除等操作。在對視圖中的數(shù)據(jù)進行修改時，相應的基本表中的數(shù)據(jù)也會發(fā)生變化；同時，若基本表的數(shù)據(jù)發(fā)生變化，則這種變化也會反映到視圖中。與直接操作數(shù)據(jù)表相比，視圖主要有以下優(yōu)勢：簡單：視圖中看到的即是需要的，用戶完全不需要考慮對應的表結構、關聯(lián)條件和篩選條件。對于用戶來說，經(jīng)常使用的查詢可以被定義為視圖。安全：視圖可以作為一種安全機制。通過視圖，用戶只能查看和修改他們所看到的數(shù)據(jù)，數(shù)據(jù)庫中的其他數(shù)據(jù)或表既看不見也不可以訪問，這樣用戶就被限制在了數(shù)據(jù)的不同子集上。數(shù)據(jù)獨立：視圖可以使應用程序和數(shù)據(jù)表在一定程度上獨立，創(chuàng)建視圖之后，應用程序可以建立在視圖之上，從而使程序與數(shù)據(jù)表分割開來，這樣就可以屏蔽表結構變化帶來的影響。12.2創(chuàng)建視圖視圖可以以一張表為基礎創(chuàng)建，也可以以多張表為基礎創(chuàng)建。創(chuàng)建視圖使用CREATEVIEW語句，其基本語法格式如下：CREATE[ORREPLACE]VIEWview_name[(column_list)]ASSELECT_statement;創(chuàng)建視圖時應注意以下幾點：定義中引用的任何表或視圖都必須存在。如果在創(chuàng)建視圖后，刪除定義時引用的表或視圖，則使用視圖時將導致錯誤。創(chuàng)建視圖不能引用臨時表。SELECT語句中最大列名長度為64個字符。在MySQL中，可以在單個數(shù)據(jù)表上創(chuàng)建視圖，這也是最簡單的一種創(chuàng)建方法。12.2.1在單表上創(chuàng)建視圖CREATEVIEWv_tableASSELECTid,nameFROMgoods;【實例12-1】以第5章中“實例5-1”創(chuàng)建的goods表為基表創(chuàng)建視圖。步驟1

啟動并登錄MySQL。步驟3

執(zhí)行SQL語句，基于goods表創(chuàng)建視圖v_table。步驟2

執(zhí)行以下語句，選擇數(shù)據(jù)庫db_shop。USEdb_shop;mysql>SELECT*FROMv_table;+-------------+--------------+|id |name |+-------------+--------------+|4 |休閑西服 ||5 |果汁 ||3 |水果糖 ||9 |牛仔褲 ||2 |牛奶糖 ||10 |紅樓夢 ||8 |蘋果 ||1 |西游記 ||7 |西瓜 ||6 |論語 |+-------------+--------------+10rowsinset(0.05sec)步驟4

執(zhí)行SELECT語句，查詢視圖v_table。SQL語句及其執(zhí)行結果如下：默認情況下，視圖的字段名與基表的字段名相同。但是，為了增加數(shù)據(jù)安全性，也可以為視圖字段指定不同的名稱。12.2.1在單表上創(chuàng)建視圖mysql>SELECT*FROMv_table2;+----------------+--------------------------+|goods_id |goods_name |+----------------+--------------------------+|4 |休閑西服

||5 |果汁 ||3 |水果糖 ||9 |牛仔褲 ||2 |牛奶糖

||10 |紅樓夢

||8 |蘋果 ||1 |西游記

||7 |西瓜

||6 |論語 |+----------------+--------------------------+10rowsinset(0.00sec)【實例12-2】以第5章中“實例5-1”創(chuàng)建的goods表為基表創(chuàng)建視圖，并重新為視圖字段命名。SQL語句如下：CREATEVIEWv_table2(goods_id,goods_name)ASSELECTid,nameFROMgoods;語句執(zhí)行成功，查看v_table2視圖中的數(shù)據(jù)，結果如下：12.2.2在多表上創(chuàng)建視圖mysql>SELECT*FROMv_table3;+-----------+-------------------------+|o_id |name |+-----------+-------------------------+|2 |牛奶糖 ||4 |休閑西服 ||6 |論語

||3 |水果糖 ||1 |西游記 ||5 |果汁 |+-----------+-------------------------+6rowsinset(0.03sec)【實例12-3】以第5章中“實例5-1”創(chuàng)建的goods表和“實例5-2”創(chuàng)建的orders表為基表，創(chuàng)建視圖，并重新為視圖字段命名。SQL語句如下：CREATEVIEWv_table3(o_id,name)ASSELECTorders.o_id,FROMgoodsJOINordersONgoods.id=orders.o_id;語句執(zhí)行成功后，查看v_table3視圖中的數(shù)據(jù)，結果如下：使用NavicatforMySQL也可以創(chuàng)建視圖，具體操作步驟如下：12.2.3使用圖形化工具創(chuàng)建視圖步驟1

啟動NavicatforMySQL并連接MySQL后，雙擊需要操作的數(shù)據(jù)庫，此處為db_shop。步驟2

單擊“視圖”按鈕，顯示前面創(chuàng)建的視圖。單擊“新建視圖”按鈕，可以打開視圖編輯界面，如圖12-1所示。使用NavicatforMySQL也可以創(chuàng)建視圖，具體操作步驟如下：12.2.3使用圖形化工具創(chuàng)建視圖步驟3

切換到“視圖創(chuàng)建工具”選項卡，在左側雙擊要創(chuàng)建視圖的基本表，將其添加至右側窗口，之后在右側相應字段的復選框中單擊可選擇要在視圖中顯示的字段，最后在右下角的窗口中進一步編輯創(chuàng)建視圖的SQL語句，如圖12-2所示。步驟4

編輯好后單擊“保存”按鈕，在彈出的“視圖名”對話框中輸入視圖名，并單擊“確定”按鈕即可。12.3查看視圖使用DESC語句可以查看視圖的結構，其基本語法格式如下：DESC視圖名;mysql>DESCv_table;+-----------+------------+------------+------------+-----------+------------+|Field |Type |Null |Key |Default |Extra |+-----------+------------+------------+------------+-----------+------------+|id |int(11) |NO | |0 | ||name |char(30) |YES | |NULL | |+-----------+------------+------------+------------+-----------+------------+2rowsinset(0.02sec)【實例12-4】使用DESC語句查看視圖v_table的結構。SQL語句及其執(zhí)行結果如下：12.3.1查看視圖基本信息mysql>DESCgoods;+-------------------------+-----------------------------+--------+------------+-----------+-------------------------+|Field |Type |Null|Key |Default |Extra |+-------------------------+-----------------------------+--------+------------+-----------+-------------------------+|id |int(11) |NO|PRI |NULL |auto_increment ||type |char(30) |YES| |NULL | ||name |char(30) |YES|UNI |NULL | ||price |decimal(7,2)unsigned|YES| |NULL | ||num |int(11) |YES| |0 | ||add_time |datetime |YES| |NULL | |+-------------------------+-----------------------------+--------+------------+-----------+-------------------------+6rowsinset(0.00sec)【實例12-5】使用DESC語句查看基本表goods的結構。SQL語句及其執(zhí)行結果如下：12.3.1查看視圖基本信息使用SHOWCREATEVIEW語句可以查看視圖定義語句，其基本語法格式如下：SHOWCREATEVIEW視圖名;mysql>SHOWCREATEVIEWv_table2\G***************************1.row***************************View:v_table2CreateView:CREATEALGORITHM=UNDEFINEDDEFINER=`root`@`localhost`SQLSECURITYDEFINERVIEW`v_table2`ASselect`goods`.`id`AS`goods_id`,`goods`.`name`AS`goods_name`from`goods`character_set_client:gbkcollation_connection:gbk_chinese_ci1rowinset(0.00sec)【實例12-6】使用SHOWCREATEVIEW語句查看視圖v_table2的定義語句。SQL語句及其執(zhí)行結果如下：12.3.2查看視圖定義語句在MySQL中，所有視圖的詳細信息都存儲在系統(tǒng)數(shù)據(jù)庫information_schema下的views表中。通過views表，可以查看數(shù)據(jù)庫中所有視圖的詳細信息。mysql>SELECT*FROMinformation_schema.views\G***************************1.row***************************TABLE_CATALOG:defTABLE_SCHEMA:db_shopTABLE_NAME:v_tableVIEW_DEFINITION:select`db_shop`.`goods`.`id`AS`id`,`db_shop`.`goods`.`name`AS`name`from`db_shop`.`goods`CHECK_OPTION:NONEIS_UPDATABLE:YESDEFINER:root@localhostSECURITY_TYPE:DEFINERCHARACTER_SET_CLIENT:gbkCOLLATION_CONNECTION:gbk_chinese_ci***************************2.row***************************TABLE_CATALOG:defTABLE_SCHEMA:db_shopTABLE_NAME:v_table2 ……【實例12-7】通過views表查看數(shù)據(jù)庫中視圖的詳細信息。SQL語句及其執(zhí)行結果如下：12.3.3通過views表查看視圖詳細信息12.3.4使用圖形化工具查看視圖使用NavicatforMySQL也可以查看視圖，具體操作步驟如下：步驟1

啟動NavicatforMySQL并連接MySQL后，雙擊需要操作的數(shù)據(jù)庫，此處為db_shop。步驟2

單擊“視圖”按鈕，然后右擊要查看的視圖，在彈出的快捷菜單中選擇“對象信息”，如圖12-3所示。12.3.4使用圖形化工具查看視圖步驟3

“常規(guī)”選項卡中顯示了視圖的相關參數(shù)信息，“DDL”選項卡中顯示視圖的定義語句，如圖12-4所示。12.4修改視圖當基本表的某些字段發(fā)生改變時，可以通過修改視圖來保持與基本表的一致性。MySQL提供CREATEORREPLACEVIEW和ALTER語句來修改視圖。使用CREATEORREPLACEVIEW語句修改視圖的語法形式如下：12.4.1使用CREATEORREPLACEVIEW語句修改視圖CREATEORREPLACEVIEWview_name[(column_list)]ASSELECT_statement;【實例12-8】使用CREATEORREPLACEVIEW語句修改視圖v_table。步驟1

執(zhí)行以下語句，選擇數(shù)據(jù)庫db_shop，并使用DESC語句查看視圖結構。USEdb_shop;mysql>DESCv_table;+-----------+------------+------------+------------+-----------+------------+|Field |Type |Null |Key |Default |Extra |+-----------+------------+------------+------------+-----------+------------+|id |int(11) |NO | |0 | ||name |char(30) |YES | |NULL | |+-----------+------------+------------+------------+-----------+------------+2rowsinset(0.02sec)12.4.1使用CREATEORREPLACEVIEW語句修改視圖mysql>DESCv_table;+-----------+---------------------------------------+------------+-----------+------------+------------+|Field |Type |Null |Key |Default |Extra |+-----------+---------------------------------------+------------+-----------+------------+------------+|id |int(11) |NO | |0 | ||name |char(30) |YES | |NULL | ||price |decimal(7,2)unsigned |YES | |NULL | |+-----------+---------------------------------------+------------+------------+-----------+------------+3rowsinset(0.00sec)步驟3

使用DESC語句查看視圖結構。SQL語句及其執(zhí)行結果如下：步驟2

使用CREATEORREPLACEVIEW語句修改視圖v_table。SQL語句及其執(zhí)行結果如下：mysql>CREATEORREPLACEVIEWv_tableASSELECTid,name,priceFROMgoods;QueryOK,0rowsaffected(0.03sec)12.4.2使用ALTER語句修改視圖ALTERVIEWview_name[(column_list)]ASSELECT_statement;【實例12-9】使用ALTER語句修改視圖v_table2。步驟1

執(zhí)行以下語句，選擇數(shù)據(jù)庫db_shop，并使用DESC語句查看視圖結構。USEdb_shop;mysql>DESCv_table2;+------------------+-----------------+--------+------+------------+-------+|Field |Type|Null|Key|Default|Extra|+------------------+-----------------+--------+------+------------+-------+|goods_id|int(11)|NO||0|||goods_name|varchar(30)|YES||NULL||+------------------+----------------+---------+------+------------+-------+2rowsinset(0.00sec)12.4.2使用ALTER語句修改視圖步驟2

執(zhí)行ALTER語句，修改視圖v_table2。SQL語句及其執(zhí)行結果如下：mysql>ALTERVIEWv_table2(goods_id,goods_name,goods_price)ASSELECTid,name,priceFROMgoods;QueryOK,0rowsaffected(0.04sec)步驟3

使用DESC語句查看視圖結構。SQL語句及其執(zhí)行結果如下：mysql>DESCv_table2;+-----------------------------+-----------------------------+--------------+--------------+--------------+--------------+|Field |Type |Null |Key |Default |Extra |+-----------------------------+-----------------------------+--------------+--------------+--------------+--------------+|goods_id |int(11) |NO | |0 | ||goods_name |char(30) |YES | |NULL | ||goods_price |decimal(7,2)unsigned|YES | |NULL | |+-----------------------------+------------------------------+--------------+--------------+-------------+--------------+3rowsinset(0.00sec)12.4.3使用圖形化工具修改視圖步驟1

啟動NavicatforMySQL并連接MySQL后，雙擊需要操作的數(shù)據(jù)庫，此處為db_shop。步驟2

單擊“視圖”按鈕，然后選中需要修改的視圖，并單擊“設計視圖”按鈕，如圖12-5所示。圖12-5修改視圖12.4.3使用圖形化工具修改視圖步驟3

打開視圖編輯界面，可在“視圖創(chuàng)建工具”選項卡右下方編輯SQL語句，如圖12-6所示。圖12-6修改視圖定義語句12.5刪除視圖使用DROPVIEW語句可刪除一個或多個視圖。其基本語法格式如下：DROPVIEW[IFEXISTS]視圖1,……視圖n;【實例12-10】刪除視圖v_table。步驟1

執(zhí)行以下語句，選擇數(shù)據(jù)庫db_shop。步驟2

執(zhí)行以下語句，刪除視圖v_table。USEdb_shop;DROPVIEWIFEXISTSv_table;12.5.1使用DROPVIEW語句刪除視圖步驟3

執(zhí)行SQL語句，查看數(shù)據(jù)庫中的數(shù)據(jù)表。SQL語句及其執(zhí)行結果如右：mysql>SHOWTABLES;+--------------------------------------+|Tables_in_db_shop |+--------------------------------------+|goods ||new_goods ||orders ||people ||people1 ||people2 ||people3 ||people4 ||reply ||test_goods ||v_table2 ||v_table3 |+---------------------------------------+12rowsinset(0.00sec)12.5.2使用圖形化工具刪除視圖步驟1

啟動NavicatforMySQL并連接MySQL后，雙擊需要操作的數(shù)據(jù)庫，此處為db_shop。步驟2

單擊“視圖”按鈕，然后選擇需要刪除的視圖，單擊“刪除試圖”按鈕，最后確認刪除即可，如圖12-7所示。圖12-7刪除視圖12.6操作視圖中數(shù)據(jù)由于視圖是一張?zhí)摂M表，所以對視圖中的數(shù)據(jù)進行操作，實際上就是對基表進行操作。當視圖中的數(shù)據(jù)被修改，基表中的數(shù)據(jù)會同時被修改。同樣，當基表中的數(shù)據(jù)被修改，視圖中的數(shù)據(jù)也會被修改?！緦嵗?2-11】修改視圖v_table2中的數(shù)據(jù)。步驟1

修改視圖中數(shù)據(jù)之前，分別執(zhí)行SQL語句查看視圖和基表的信息。結果如下：mysql>SELECT*FROMv_table2;+----------------+------------------+------------------+|goods_id|goods_name|goods_price|+----------------+-----------------+----------------+|1 |西游記|50.40||2 |牛奶糖|7.50||3 |水果糖|2.50||4 |休閑西服|800.00||5 |果汁|2.50||6 |論語|109.00||7 |西瓜|1.50||8 |蘋果|3.00||9 |牛仔褲|120.00||10 |紅樓夢|50.50|+----------------+-----------------+----------------+10rowsinset(0.04sec)12.6.1修改視圖中數(shù)據(jù)mysql>SELECTid,name,priceFROMgoods;+---+--------------+------------------------+|id|name|price |+---+--------------+------------------------+|1|西游記|50.40 ||2|牛奶糖|7.50 ||3|水果糖|2.50 ||4|休閑西服|800.00 ||5|果汁|2.50 ||6|論語|109.00 ||7|西瓜|1.50 ||8|蘋果|3.00 ||9|牛仔褲|120.00 ||10|紅樓夢|50.50 |+---+--------------+------------------------+10rowsinset(0.00sec)步驟2

執(zhí)行SQL語句，修改視圖中數(shù)據(jù)。執(zhí)行結果如下：12.6.1修改視圖中數(shù)據(jù)mysql>UPDATEv_table2SETgoods_name='哈姆雷特'WHEREgoods_id=1;QueryOK,1rowaffected(0.10sec)Rowsmatched:1Changed:1Warnings:0步驟3

再次查看視圖和基表數(shù)據(jù)。結果如下：mysql>SELECT*FROMv_table2;+-------------+------------------+--------------------------+|goods_id |goods_name|goods_price |+-------------+------------------+--------------------------+|1 |哈姆雷特|50.40 ||2 |牛奶糖|7.50 ||3 |水果糖|2.50 ||4 |休閑西服|800.00 ||5 |果汁|2.50 ||6 |論語|109.00 ||7 |西瓜|1.50 ||8 |蘋果|3.00 ||9 |牛仔褲|120.00 ||10 |紅樓夢|50.50 |+-------------+------------------+--------------------------+10rowsinset(0.00sec)mysql>SELECTid,name,priceFROMgoods;+--------------+-------------+--------------+|id |name |price |+--------------+-------------+--------------+|1 |哈姆雷特 |50.40 ||2 |牛奶糖 |7.50 ||3 |水果糖 |2.50 ||4 |休閑西服 |800.00 ||5 |果汁 |2.50 ||6 |論語 |109.00 ||7 |西瓜 |1.50 ||8 |蘋果 |3.00 ||9 |牛仔褲 |120.00 ||10 |紅樓夢 |50.50 |+--------------+-------------+--------------+10rowsinset(0.00sec)刪除視圖中數(shù)據(jù)的方法與刪除表中數(shù)據(jù)相同?！緦嵗?2-12】刪除視圖v_table2中的第10條數(shù)據(jù)。SQL語句及其執(zhí)行結果如下：12.6.2刪除視圖中數(shù)據(jù)mysql>DELETEFROMv_table2WHEREgoods_id=10;QueryOK,1rowaffected(0.05sec)查看視圖數(shù)據(jù)：SELECT*FROMv_table2;查看基表數(shù)據(jù)：SELECTid,name,priceFROMgoods;可分別執(zhí)行SQL語句查看視圖和表中數(shù)據(jù)。向視圖中插入數(shù)據(jù)的方法與向表中插入數(shù)據(jù)相同?！緦嵗?2-13】向視圖v_table2中插入數(shù)據(jù)。SQL語句及其執(zhí)行結果如下：12.6.3向視圖中插入數(shù)據(jù)mysql>INSERTINTOv_table2values(10,'紅樓夢',138);QueryOK,1rowaffected(0.06sec)在向視圖中插入數(shù)據(jù)時，應注意視圖中必須包含基表中不允許為NULL的所有列，否則在插入數(shù)據(jù)時將會報錯。13存儲過程和函數(shù)第章13.1存儲過程和函數(shù)概述針對表的一個完整操作往往不是單條SQL語句就能實現(xiàn)的，而是需要一組SQL語句來實現(xiàn)。例如，要完成一個購買商品的訂單的處理，一般需要考慮以下幾步：可將一個完整操作中所包含的多條SQL語句創(chuàng)建為存儲過程或函數(shù)，以方便應用。（1）在生成訂單之前，首先需要查看商品庫存中是否有相應商品；（2）如果商品庫存中不存在相應商品，需要向供應商訂貨；（3）如果商品庫存中存在相應商品，需要預定商品，并修改庫存數(shù)量。存儲過程和函數(shù)可以簡單地理解為一組經(jīng)過編譯并保存在數(shù)據(jù)庫中的SQL語句的集合，可以隨時被調用。允許標準組件式編程：存儲過程和函數(shù)在創(chuàng)建后可以在程序中被多次調用。較快的執(zhí)行速度：如果某一操作包含大量的事務處理代碼，并且被多次執(zhí)行，那么存儲過程要比批處理的執(zhí)行速度快很多。減少網(wǎng)絡流量：對于大量的SQL語句，將其組織成存儲過程，會比一條一條的調用SQL語句要大大節(jié)省網(wǎng)絡流量，降低網(wǎng)絡負載。安全：數(shù)據(jù)庫管理員通過設置執(zhí)行某一存儲過程的權限，從而限制相應數(shù)據(jù)的訪問權限，避免非授權用戶對數(shù)據(jù)的訪問，保證數(shù)據(jù)的安全。13.2創(chuàng)建并調用存儲過程和函數(shù)創(chuàng)建存儲過程使用SQL語句CREATEPROCEDURE來實現(xiàn)，其語法形式如下：CREATEPROCEDUREproc_name([proc_parameter[,…])[characteristic…]routine_bodyproc_parameter表示存儲過程的參數(shù)，參數(shù)形式如下：存儲程序可以分為存儲過程和函數(shù)。存儲過程和函數(shù)的操作主要包括創(chuàng)建存儲過程和函數(shù)、調用存儲過程和函數(shù)、查看存儲過程和函數(shù)，以及修改和刪除存儲過程和函數(shù)。13.2.1創(chuàng)建存儲過程[IN|OUT|INOUT]parameter_nameTYPEcharacteristic表示存儲過程的特性，可取值及其意義如下：LANGUAGESQL：表示存儲過程的routine_body部分使用SQL語言編寫。[NOT]DETERMINISTIC：DETERMINISTIC表示存儲過程的執(zhí)行結果是確定的；默認為NOTDETERMINISTIC，表示執(zhí)行結果不確定。{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}：指明子程序使用SQL語句的限制。SQLSECURITY{DEFINER|INVOKER}：指定可執(zhí)行存儲過程的用戶，DEFINER表示只有創(chuàng)建者才能執(zhí)行，INVOKER表示擁有權限的調用者可以執(zhí)行。COMMENT'string'：表示存儲過程或者函數(shù)的注釋信息。13.2.1創(chuàng)建存儲過程【實例13-1】創(chuàng)建一個名為proc的簡單存儲過程，用于獲取goods表中的記錄數(shù)。在創(chuàng)建存儲過程前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。然后執(zhí)行以下語句：mysql>DELIMITER$$mysql>CREATEPROCEDUREproc(OUTnumINT)->BEGIN->SELECTCOUNT(*)INTOnumFROMgoods;->END$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;

提示“DELIMITER$$”的作用是將語句的結束符“;”修改為“$$”，這樣存儲過程中的SQL語句結束符“;”就不會被MySQL解釋成語句的結束而提示錯誤。在存儲過程創(chuàng)建完成后，應使用“DELIMITER;”語句將結束符修改為默認結束符。13.2.2創(chuàng)建存儲函數(shù)創(chuàng)建存儲函數(shù)使用SQL語句CREATEFUNCTION來實現(xiàn)，其語法形式如下：CREATEFUNCTIONfunc_name([parameter_name[,…])RETURNSTYPE[characteristic…]routine_body【實例13-2】創(chuàng)建一個名為func的簡單存儲函數(shù)，用于獲取goods表中的記錄數(shù)。在創(chuàng)建存儲函數(shù)前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句的執(zhí)行結果如下：mysql>DELIMITER$$mysql>CREATEFUNCTIONfunc()->RETURNSINT(11)->RETURN(SELECTCOUNT(*)FROMgoods)->$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;

提示RETURNSTYPE子句對于存儲函數(shù)而言是必須存在的，如果RETURN子句返回值的數(shù)據(jù)類型與RETURNSTYPE子句指定的數(shù)據(jù)類型不同，MySQL會將返回值強制轉換為RETURNSTYPE子句指定的類型。13.2.3調用存儲過程和函數(shù)通常使用關鍵字CALL調用存儲過程，其語法形式如下：CALLprocedure_name([parameter[,…]]);存儲過程必須使用關鍵字CALL調用，而存儲函數(shù)與MySQL內置函數(shù)的調用相同，使用關鍵字SELECT。調用存儲過程1【實例13-3】調用實例13-1創(chuàng)建的存儲過程proc()，查看其返回值。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結果如下：mysql>CALLproc(@num);QueryOK,1rowaffected(0.06sec)mysql>SELECT@num;+----------+|@num |+----------+|10 |+----------+1rowinset(0.01sec)調用存儲過程的執(zhí)行結果與直接執(zhí)行查詢語句SELECTCOUNT(*)FROMgoods;的執(zhí)行結果相同，但是存儲過程的好處在于處理邏輯都封裝在數(shù)據(jù)庫端，調用者不需要了解中間的處理邏輯，當處理邏輯發(fā)生變化時，只需要修改存儲過程即可，而對調用者的程序完全沒有影響。13.2.3調用存儲過程和函數(shù)通常使用關鍵字SELECT調用存儲函數(shù)，其語法形式如下：SELECTfunction_name([parameter[,……]]);【實例13-4】調用實例13-2創(chuàng)建的存儲函數(shù)func()，查看其返回值。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結果如下：mysql>SELECTfunc();+----------+|func() |+----------+|10|+----------+1rowinset(0.04sec)調用存儲函數(shù)2使用NavicatforMySQL也可以創(chuàng)建存儲過程和函數(shù)，具體操作如下。13.2.4使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟1

使用NavicatforMySQL連接MySQL后，雙擊需要操作的數(shù)據(jù)庫“test_db”，然后單擊“函數(shù)”按鈕。步驟2

單擊“新建函數(shù)”按鈕，選擇需要創(chuàng)建的類型，此處選擇創(chuàng)建存儲過程，如圖13-1所示。圖13-1選擇類型13.2.4使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟3

在編輯區(qū)填寫存儲過程需要的參數(shù)，單擊編輯區(qū)左下方的“+”按鈕可以添加參數(shù)，單擊“－”按鈕可以刪除參數(shù)，如果存儲過程沒有參數(shù)，直接單擊“完成”按鈕即可，如圖13-2所示。圖13-2填寫參數(shù)13.2.4使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟4

在BEGIN…END語句中編輯需要執(zhí)行的SQL語句，如圖13-3所示。圖13-3編輯SQL語句13.2.4使用圖形化工具創(chuàng)建存儲過程和函數(shù)步驟5

在“高級”選項卡中可以設置存儲程序的特性，最后單擊“保存”按鈕，輸入名稱確定即可，如圖13-4所示。圖13-4保存存儲過程13.3關于存儲過程和函數(shù)的表達式13.3.1變量變量是表達式中最基本的元素，可用于存儲臨時數(shù)據(jù)。本節(jié)簡單介紹變量的分類，以及在存儲過程和函數(shù)中應用變量的方法。本節(jié)詳細介紹存儲過程和函數(shù)中所包含的表達式語句。此處的表達式同其他高級語言中一樣，主要由變量、運算符和流程控制語句構成。用戶變量（User-DefinedVariables）：帶有前綴@，只能被定義它的用戶使用，作用于當前整個連接，當前連接斷開后，所定義的用戶變量會被全部釋放。用戶變量不用提前定義就可以直接使用。局部變量（LocalVariables）：沒有前綴，一般用于SQL語句塊中，比如存儲過程的BEGIN…END中。局部變量使用前需要先通過DECLARE聲明。如沒有聲明，則初始值為NULL。系統(tǒng)變量（ServerSystemVariables）：帶有前綴@@，MySQL有許多已經(jīng)設置默認值的系統(tǒng)變量。系統(tǒng)變量包含全局變量和會話變量。全局變量會影響整個服務器，而會話變量只影響個人客戶端連接。變量的分類113.3.1變量在存儲過程和函數(shù)中應用變量2局部變量可以在子程序中定義并應用，其作用范圍是BEGIN…END語句塊。1）定義變量在存儲過程中使用DECLARE語句定義局部變量，其語法形式如下：DECLAREvar_name[,…]type[DEFAULTvalue];例如，定義一個INT類型的變量，名稱為var1：DECLAREvar1INT;

提示變量的定義必須在復合語句開頭，并且在任何其他語句前面。也就是說，DECLARE語句在存儲過程和函數(shù)中使用時，必須出現(xiàn)在BEGIN…END語句塊的最前面，并且變量名不區(qū)分大小寫?？梢砸淮温暶鞫鄠€相同類型的變量。13.3.1變量2）為變量賦值定義變量之后，可以使用SET關鍵字為變量賦值，語法形式如下：SETvar_name=expr[,var_name=expr]…;為前面定義的變量var1賦值，具體如下：SETvar1=3;變量值可以為常量或者表達式。另外，也可以使用SELECT…INTO…查詢語句將查詢結果賦給變量，這要求查詢結果必須只有一行，具體語法形式如下：SELECTcol_name[,……]INTOvar_name[,……]FROMtable_name;13.3.1變量mysql>DELIMITER$$mysql>CREATEPROCEDUREproc1()->BEGIN->DECLAREvar1,var2,var3,g_idINT;->SETvar1=1,var2=2;->SETvar3=var1+var2;->SELECTidintog_idFROMgoodsWHEREid=1;->END$$QueryOK,0rowsaffected(0.10sec)mysql>DELIMITER;【實例13-5】執(zhí)行SQL語句，定義變量并為其賦值。首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。然后創(chuàng)建存儲過程并定義變量，SQL語句及其執(zhí)行結果如下：13.3.2定義條件和處理程序條件和處理程序是MySQL提供的一種異常處理機制，定義條件是事先定義程序執(zhí)行過程中可能會遇到的問題；定義處理程序是定義在遇到問題時執(zhí)行的相應處理方法，并且保證存儲過程和函數(shù)在遇到問題時不終止。定義條件1在MySQL中定義條件使用DECLARE…CONDITION語句，其語法形式如下：DECLAREcondition_nameCONDITIONFOR[condition_type];mysql_error_code：表示數(shù)值類型錯誤代碼。sqlstate_value：表示長度為5的字符串類型錯誤代碼。下面以名為“not_found_database”的條件“ERROR1049(42000)”的定義為例，來看看這兩種形式的區(qū)別。DECLAREnot_found_databaseCONDITIONFOR1049;DECLAREnot_found_databaseCONDITIONFORSQLSTATE‘42000’;

提示數(shù)值類型的錯誤代碼不要使用0，因為0表示成功而不是錯誤；字符串類型的錯誤代碼不要使用'00'，因為'00'表示成功而不是錯誤。13.3.2定義條件和處理程序定義處理程序2在定義條件之后，可以使用DECLARE…HANDLER語句定義處理程序，語法形式如下：DECLAREhandler_typeHANDLERFORcondition_value[...]statementCONTINUE：表示遇到錯誤不處理，程序繼續(xù)執(zhí)行。EXIT：表示遇到錯誤立即退出程序。UNDO：表示遇到錯誤后撤回之前的操作，目前MySQL暫不支持該操作。mysql_error_code：表示數(shù)值類型的錯誤代碼。sqlstate_value：表示包含5個字符的字符串錯誤值。condition_name：表示使用DECLARE…CONDITION語句定義的條件名。SQLWARNING：匹配所有以01開頭的SQLSTATE錯誤代碼。NOTFOUND：匹配所有以02開頭的SQLSTATE錯誤代碼。SQLEXCEPTION：匹配所有未被SQLWARNING和NOTFOUND捕獲的SQLSTATE錯誤代碼。13.3.2定義條件和處理程序statement為程序語句段，表示在遇到定義的異常條件時，需要執(zhí)行的存儲過程或函數(shù)。DECLARECONTINUEHANDLERFORSQLSTATE'42s02'SET@info='cannotfind';方法一：捕獲sqlstate_valueDECLARECONTINUEHANDLERFOR1146SET@info='cannotfind';方法二：捕獲mysql_error_codeDECLAREcan_not_findCONDITIONFOR1146;DECLARECONTINUEHANDLERFORcan_not_findSET@info='cannotfind';方法三：先定義條件，然后再調用條件DECLAREEXITHANDLERFORSQLWARNINGSET@info='error';方法四：使用SQLWARNINGDECLAREEXITHANDLERFORNOTFOUNDSET@info='cannotfind';方法五：使用NOTFOUND13.3.2定義條件和處理程序DECLAREEXITHANDLERFORSQLEXCEPTIONSET@info='error';方法六：使用SQLEXCEPTIONmysql>DELIMITER$$mysql>CREATEPROCEDUREhandlerdemo()->BEGIN->DECLARECONTINUEHANDLERFORSQLSTATE'23000'SET@x2=1;->SET@x=1;->INSERTINTOt_handlerVALUES(1);->SET@x=2;->INSERTINTOt_handlerVALUES(1);->SET@x=3;->END$$QueryOK,0rowsaffected(0.03sec)mysql>DELIMITER;【實例13-6】執(zhí)行SQL語句，在存儲過程中定義條件和處理程序，體驗異常處理機制在存儲過程中的作用。步驟1

登錄MySQL，并選擇任一數(shù)據(jù)庫，之后執(zhí)行以下SQL語句，創(chuàng)建t_handler表。CREATETABLEt_handler(s1INT,PRIMARYKEY(s1));步驟2

在存儲過程和函數(shù)中定義條件和處理程序，SQL語句及其執(zhí)行結果如下。13.3.2定義條件和處理程序mysql>CALLhandlerdemo();QueryOK,0rowsaffected(0.11sec)mysql>SELECT@X;+----------+|@X |+----------+|3 |+----------+1rowinset(0.00sec)步驟3

調用存儲過程，查看變量x的值，SQL語句及其執(zhí)行結果如下。mysql>DELIMITER$$mysql>CREATEPROCEDUREhandlerdemo2()->BEGIN->SET@x=1;->INSERTINTOt_handlerVALUES(1);->SET@x=2;->INSERTINTOt_handlerVALUES(1);->SET@x=3;->END$$QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;步驟4

在存儲過程中不定義條件和處理程序，SQL語句及其執(zhí)行結果如下。13.3.2定義條件和處理程序mysql>CALLhandlerdemo2();ERROR1062(23000):Duplicateentry'1'forkey'PRIMARY'mysql>SELECT@X;+----------+|@X |+----------+|1 |+----------+1rowinset(0.00sec)步驟5

調用存儲過程，查看變量x的值，SQL語句及其執(zhí)行結果如下。由結果可以看出，當在存儲過程中定義條件和處理程序時，程序在遇到錯誤后會繼續(xù)執(zhí)行直到最后一步，為變量@x賦值為3；而當存儲過程中沒有定義條件和處理程序時，程序在遇到錯誤后會立即終止，此時變量@x的值為1。13.3.3游標的使用在存儲過程和函數(shù)中，當查詢語句返回多條記錄時，可以使用游標對結果集進行逐條讀取。本節(jié)介紹定義、打開、使用和關閉游標的方法。定義游標1在MySQL中，使用DECLARE關鍵字來定義游標，其語法形式如下：DECLAREcursor_nameCURSORFORselect_statement;打開游標2打開游標的關鍵字為OPEN，其語法形式如下：OPENcursor_name;使用游標3使用游標的關鍵字是FETCH，其語法形式如下：FETCHcursor_nameINTOvar_name[,var_name]…關閉游標4關閉游標的關鍵字為CLOSE，其語法形式如下：CLOSEcursor_name;13.3.3游標的使用mysql>DELIMITER$$mysql>CREATEPROCEDUREproc9(INflagVARCHAR(10),OUTgnameVARCHAR(30),OUTgpriceDECIMAL(7,2))->BEGIN->DECLAREg_idINT;->DECLAREg_nameVARCHAR(30);->DECLAREg_priceDECIMAL(7,2);->DECLAREg_cursorCURSORFORSELECTid,name,priceFROMdb_shop.goods;->DECLAREEXITHANDLERFORNOTFOUNDCLOSEg_cursor;->OPENg_cursor;->REPEAT->FETCHg_cursorINTOg_id,g_name,g_price;->IFg_id=flagTHEN->SELECTg_name,g_priceINTOgname,gprice;->ENDIF;->UNTILg_id=flag->ENDREPEAT;->CLOSEg_cursor;->END$$QueryOK,0rowsaffected(0.12sec)mysql>DELIMITER;【實例13-7】創(chuàng)建存儲過程，并在存儲過程中使用游標。在創(chuàng)建存儲過程前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結果如下：13.3.3游標的使用mysql>CALLproc9(5,@gname,@gprice);QueryOK,1rowaffected(0.06sec)mysql>SELECT@gname,@gprice;+-------------------------+-------------------------+|@gname |@gprice |+-------------------------+-------------------------+|果汁 |2.50 |+-------------------------+-------------------------+1rowinset(0.01sec)調用存儲過程，查看結果：13.3.4流程控制的使用流程控制語句是指可以控制程序運行順序的指令，程序運行順序主要包括順序執(zhí)行、條件執(zhí)行和循環(huán)執(zhí)行。MySQL支持的流程控制語句包括IF語句、CASE語句、LOOP語句、REPEAT語句、WHILE語句、LEAVE語句、ITERATE語句和RETURN語句。IF語句1IF實現(xiàn)條件判斷，語句中可以包含多個判斷條件，系統(tǒng)會根據(jù)條件的結果是否為TRUE執(zhí)行相應的操作，語法形式如下：IFsearch_conditionTHENstatement_list[ELSEIFsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDIF13.3.4流程控制的使用CASE語句2CASE語句可以實現(xiàn)比IF語句更復雜的條件構造，該語句有兩種使用形式。第1種語法形式如下：CASEcase_exprWHENwhen_valueTHENstatement_list[WHENwhen_valueTHENstatement_list]...[ELSEstatement_list]ENDCASECASE語句的第2種語法形式如下：CASEWHENsearch_conditionTHENstatement_list[WHENsearch_conditionTHENstatement_list]...[ELSEstatement_list]ENDCASE13.3.4流程控制的使用LOOP語句和LEAVE語句3LOOP語句可以實現(xiàn)簡單的循環(huán)，使得系統(tǒng)能夠重復執(zhí)行循環(huán)結構內的語句列表。該語句列表由一條或多條語句組成，每條語句使用分號（;）隔開。語法形式如下：[loop_label:]LOOPstatement_listENDLOOP[end_label]如果不在statement_list中增加退出循環(huán)的語句，LOOP語句可以實現(xiàn)簡單的死循環(huán)。使用LEAVE語句可以退出循環(huán)。語法形式如下：LEAVElabel;REPEAT語句4REPEAT語句可以實現(xiàn)一個帶條件判斷的循環(huán)結構。語法形式如下：[repeat_label:]REPEATstatement_listUNTILsearch_conditionENDREPEAT[repeat_label]13.3.4流程控制的使用WHILE語句5WHILE語句同樣可以實現(xiàn)一個帶條件判斷的循環(huán)結構，但與REPEAT語句不同的是，WHILE語句會先對條件進行判斷，如果為TRUE，才會執(zhí)行需要循環(huán)的操作，否則終止循環(huán)，語法形式如下：[while_label:]WHILEsearch_conditionDOstatement_listENDWHILE[while_label]ITERATE語句6ITERATE語句只可以出現(xiàn)在LOOP語句、REPEAT語句和WHILE語句中，意義為再次執(zhí)行循環(huán)，語法形式如下：ITERATElabel;13.3.4流程控制的使用mysql>DELIMITER$$mysql>CREATEPROCEDUREproc8(p1int)->BEGIN->label1:LOOP->SETp1=p1+1;->IFp1<10THEN->ITERATElabel1;->ENDIF;->LEAVElabel1;->ENDLOOPlabel1;->SET@y=p1;->END$$QueryOK,0rowsaffected(0.00sec)mysql>DELIMITER;【實例13-8】創(chuàng)建存儲過程，并在存儲過程中使用ITERATE語句。在創(chuàng)建存儲過程前首先登錄MySQL，并選擇數(shù)據(jù)庫db_shop。SQL語句及其執(zhí)行結果如下：mysql>CALLproc8(1);QueryOK,0rowsaffected(0.01sec)mysql>SELECT@y;+----------+|@y |+----------+|10 |+----------+1rowinset(0.00sec)調用存儲過程，查看結果。13.4查看存儲過程和函數(shù)創(chuàng)建完存儲過程和函數(shù)后，MySQL存儲了其狀態(tài)信息和定義語句，用戶可以分別使用SHOWSTATUS和SHOWCREATE語句進行查看，也可以在系統(tǒng)數(shù)據(jù)庫information_schema中查看。使用SHOWSTATUS語句可以查看存儲過程和函數(shù)的狀態(tài)?；菊Z法形式如下：13.4.1查看存儲過程和函數(shù)的狀態(tài)SHOW{PROCEDURE|FUNCTION}STATUS[LIKE'pf_name'];【實例13-9】執(zhí)行SQL語句，查看存儲過程proc的基本信息。首先登錄MySQL，然后執(zhí)行SHOWSTATUS語句，執(zhí)行結果如下：mysql>SHOWPROCEDURESTATUSLIKE'proc'\G***************************1.row***************************Db:db_shopName:procType:PROCEDUREDefiner:root@localhostModified:2018-06-0915:06:48Created:2018-06-0915:06:48Security_type:DEFINERComment:character_set_client:gbkcollation_connection:gbk_chinese_ciDatabaseCollation:utf8_general_ci1rowinset(0.02sec)使用SHOWCREATE語句可以查看存儲過程和函數(shù)的定義語句，語法形式如下：13.4.2查看存儲過程和函數(shù)的定義SHOWCREATE{PROCEDURE|FUNCTION}pf_name;【實例13-10】執(zhí)行SQL語句，查看存儲過程proc的定義語句。首先登錄MySQL，然后執(zhí)行SHOWCREA