《MySQL數(shù)據(jù)庫應(yīng)用與管理（第3版）》 課件 魯大林 第8章 存儲過程和存儲函數(shù)

存儲過程和存儲函數(shù)

存儲過程和存儲函數(shù)概述

本節(jié)內(nèi)容1.存儲過程和存儲函數(shù)的概念2.存儲過程和存儲函數(shù)的優(yōu)點存儲過程和存儲函數(shù)概述MySQL的存儲過程和存儲函數(shù)是一種保存在數(shù)據(jù)庫服務(wù)器上、可以用來執(zhí)行特定工作的一組SQL代碼的程序段。存儲過程和存儲函數(shù)可以包含針對數(shù)據(jù)庫操作的SQL語句，還可以在其內(nèi)部進(jìn)行流程控制，而且執(zhí)行速度快，所以在數(shù)據(jù)庫應(yīng)用開發(fā)中廣泛使用。1.存儲過程和存儲函數(shù)的概念存儲過程和存儲函數(shù)概述MySQL的存儲過程和存儲函數(shù)的語法非常接近，但也是有區(qū)別的，主要如下：存儲過程的參數(shù)可以有IN，OUT，INOUT三種類型；而存儲函數(shù)只能有IN類型。存儲過程聲明時不需要返回類型；而存儲函數(shù)聲明時需要描述返回類型，且存儲函數(shù)體中必須包含一個通過RETURN語句的返回值。存儲過程中的語句功能更強(qiáng)大，可以實現(xiàn)很復(fù)雜的業(yè)務(wù)邏輯；而存儲函數(shù)則有很多限制，也就是說，存儲函數(shù)實現(xiàn)的功能針對性比較強(qiáng)。存儲過程可以調(diào)用存儲函數(shù)；而存儲函數(shù)不能調(diào)用存儲過程。存儲過程一般是作為一個獨立的部分來執(zhí)行（使用CALL語句調(diào)用）；而存儲函數(shù)可以作為查詢語句的一個部分來使用。1.存儲過程和存儲函數(shù)的概念存儲過程和存儲函數(shù)概述存儲過程和存儲函數(shù)允許標(biāo)準(zhǔn)組件式編程，提高了SQL語句的重用性、共享性和可移植性。存儲過程和存儲函數(shù)是在MySQL服務(wù)器上執(zhí)行的，執(zhí)行速度快、網(wǎng)絡(luò)通信流量小。存儲過程和存儲函數(shù)可以作為一種安全機(jī)制來利用，其權(quán)限可以與數(shù)據(jù)表的權(quán)限不同，保證數(shù)據(jù)的安全性。2.存儲過程和存儲函數(shù)的優(yōu)點謝謝存儲過程和存儲函數(shù)

存儲過程

本節(jié)內(nèi)容1.局部變量2.使用CREATEPROCEDURE語句創(chuàng)建存儲過程3.創(chuàng)建帶有輸入、輸出參數(shù)的存儲過程本節(jié)內(nèi)容4.調(diào)用執(zhí)行存儲過程5.使用ALTERPROCEDURE語句修改存儲過程6.使用DROPPROCEDURE語句刪除存儲過程存儲過程在存儲過程或存儲函數(shù)中，可以定義和使用變量。定義變量使用DECLARE語句，定義在存儲過程或存儲函數(shù)中的變量稱為局部變量，其作用范圍是本存儲過程或存儲函數(shù)，定義后就可以給變量進(jìn)行賦值。1.局部變量存儲過程（1）定義變量使用DECLARE語句可以定義變量。其語法格式如下：

DECLARE<變量名>[,…]<數(shù)據(jù)類型>[DEFAULT默認(rèn)值]說明：可以同時定義多個變量，變量名之間用逗號隔開；也可以設(shè)置變量的默認(rèn)值，如果沒有設(shè)置，則默認(rèn)值為NULL。1.局部變量例如：定義一個名為myVar的變量，INT類型，默認(rèn)值為10。

DECLAREmyVarINTDEFAULT10;存儲過程（2）給變量賦值使用SET語句可以給變量賦值。其語法格式如下：

SET<變量名1>=<賦值表達(dá)式1>[,<變量名2>=<賦值表達(dá)式2>,…]說明：可以同時為多個變量賦值，各個變量的賦值語句之間用逗號隔開。1.局部變量例如：為變量myVar賦值為30。

SETmyVar=30;存儲過程（2）給變量賦值使用SELECT…INTO語句可以給變量賦值。其語法格式如下：

SELECT<字段名1>[,字段名2…]INTO<變量名1>[,變量名2…] FROM<表名>WHERE<查詢條件>1.局部變量例如：在學(xué)生表（student）中查詢出學(xué)號為“1308013101”的學(xué)生的班級，并把它賦給變量myVar。

DECLAREmyVarVARCHAR(30); SELECTdeptNameINTOmyVarFROMstudentWHEREsNo='1308013101';存儲過程創(chuàng)建存儲過程使用CREATEPROCEDURE語句，創(chuàng)建不帶有參數(shù)的存儲過程的語法格式如下：

CREATEPROCEDURE<存儲過程名>() [characteristic…] <存儲過程體>2.使用CREATEPROCEDURE語句創(chuàng)建存儲過程存儲過程說明：characteristic參數(shù)指定存儲過程的特性，其主要取值及說明如下：[NOT]DETERMINISTIC：指定DETERMINISTIC的優(yōu)化器是否開啟，默認(rèn)選項為NOTDETERMINISTIC。CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA：指定子程序使用SQL語句的限制，默認(rèn)指定為CONTAINSSQL。SQLSECURITY{DEFINER|INVOKER}：指定誰有權(quán)限來執(zhí)行，默認(rèn)指定為DEFINER。COMMENT'string'：注釋信息。<存儲過程體>是SQL代碼的內(nèi)容，可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束。2.使用CREATEPROCEDURE語句創(chuàng)建存儲過程操作案例知識點概述示例8-1運(yùn)行結(jié)果如下：創(chuàng)建一個不帶有參數(shù)的存儲過程up_softwareStudent，查詢出“軟件131”班級中的所有學(xué)生記錄。存儲過程創(chuàng)建帶有輸入、輸出參數(shù)的存儲過程的語法格式如下：

CREATEPROCEDURE<存儲過程名>([參數(shù)1[,參數(shù)2[,…[,參數(shù)n]]]]) [characteristic…] <存儲過程體>3.創(chuàng)建帶有輸入、輸出參數(shù)的存儲過程存儲過程說明：<參數(shù)>的格式為：[IN|OUT|INOUT]<參數(shù)名><類型>。IN表示輸入?yún)?shù)；OUT表示輸出參數(shù)；INOUT表示既可以是輸入、也可以是輸出參數(shù)。<參數(shù)名>表示存儲過程的參數(shù)名稱。<類型>表示存儲過程的參數(shù)類型，可以是MySQL數(shù)據(jù)庫的任意數(shù)據(jù)類型。3.創(chuàng)建帶有輸入、輸出參數(shù)的存儲過程操作案例知識點概述示例8-2運(yùn)行結(jié)果如下：創(chuàng)建一個帶有輸入?yún)?shù)的存儲過程up_deptStudentInfo，通過一個給定的班級，查詢出該班級中的所有學(xué)生記錄。操作案例知識點概述示例8-3運(yùn)行結(jié)果如下：創(chuàng)建一個帶有輸入和輸出參數(shù)的存儲過程up_scoreGradeInfo，通過一個給定的學(xué)號，查詢出該學(xué)生選修課程的數(shù)量及平均分，并通過輸出參數(shù)返回。存儲過程存儲過程是存儲在服務(wù)器端的SQL語句的集合，要使用這些已經(jīng)定義好的存儲過程，就必須通過調(diào)用的方式來實現(xiàn)。存儲過程被調(diào)用后，數(shù)據(jù)庫系統(tǒng)將執(zhí)行存儲過程中的語句。執(zhí)行存儲過程需要擁有EXECUTE權(quán)限。調(diào)用存儲過程使用CALL語句，其語法格式如下：

CALL<存儲過程名>([<實際參數(shù)值1>[,實際參數(shù)值2….]])4.調(diào)用執(zhí)行存儲過程操作案例知識點概述示例8-4運(yùn)行結(jié)果如下：調(diào)用示例8-2中的存儲過程up_deptStudentInfo。操作案例知識點概述示例8-5運(yùn)行結(jié)果如下：調(diào)用示例8-3中的存儲過程up_scoreGradeInfo。存儲過程修改存儲過程使用ALTERPROCEDURE語句，其語法格式如下：

ALTERPROCEDURE<存儲過程名>[characteristic…]

characteristic: {CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}| SQLSECURITY{DEFINER|INVOKER}| COMMENT'string'說明：characteristic參數(shù)的取值及說明與創(chuàng)建存儲過程中的一樣。5.使用ALTERPROCEDURE語句修改存儲過程操作案例知識點概述示例8-6運(yùn)行結(jié)果如下：修改示例8-2中存儲過程up_deptStudentInfo的定義，將訪問數(shù)據(jù)權(quán)限更改為READSSQLDATA。存儲過程刪除存儲過程使用DROPPROCEDURE語句，其語法格式如下：

DROPPROCEDURE[IFEXISTS]<存儲過程名>6.使用DROPPROCEDURE語句刪除存儲過程操作案例知識點概述示例8-7運(yùn)行結(jié)果如下：刪除示例8-1的存儲過程up_softwareStudent。謝謝存儲過程和存儲函數(shù)

存儲函數(shù)

本節(jié)內(nèi)容1.使用CREATEFUNCTION語句創(chuàng)建存儲函數(shù)2.調(diào)用執(zhí)行存儲函數(shù)3.使用ALTERFUNCTION語句修改存儲函數(shù)4.使用DROPFUNCTION語句刪除存儲函數(shù)存儲函數(shù)存儲函數(shù)即用戶自定義函數(shù)，創(chuàng)建存儲函數(shù)使用CREATEFUNCTION語句，其語法格式如下：

CREATEFUNCTION<存儲函數(shù)名>([參數(shù)1[,參數(shù)2[,…[,參數(shù)n]]]]) RETURNS<數(shù)據(jù)類型> [characteristic…] <存儲函數(shù)體>1.使用CREATEFUNCTION語句創(chuàng)建存儲函數(shù)存儲函數(shù)說明：<參數(shù)>的格式為：<參數(shù)名><類型>。characteristic參數(shù)指定函數(shù)的特性，其取值與存儲過程中的取值一樣。<存儲函數(shù)體>是SQL代碼的內(nèi)容，可以用BEGIN…END來標(biāo)志SQL代碼的開始和結(jié)束。函數(shù)體中必須包含使用RETURN語句的返回值，該返回值的數(shù)據(jù)類型由之前的“RETURNS<數(shù)據(jù)類型>”指定。1.使用CREATEFUNCTION語句創(chuàng)建存儲函數(shù)操作案例知識點概述示例8-8運(yùn)行結(jié)果如下：創(chuàng)建一個存儲函數(shù)func_getStudentName，通過一個給定的學(xué)號，返回該學(xué)生的姓名。操作案例知識點概述示例8-9運(yùn)行結(jié)果如下：創(chuàng)建一個存儲函數(shù)func_getGradeBySNoCNo，通過一個給定的學(xué)號和課程號，返回該學(xué)生指定課程的成績。操作案例知識點概述示例8-10運(yùn)行結(jié)果如下：創(chuàng)建一個存儲函數(shù)func_getStuNoNameById，通過一個給定的學(xué)生Id，返回該學(xué)生的“學(xué)號-姓名”。存儲函數(shù)用戶自己定義的存儲函數(shù)與MySQL的內(nèi)部函數(shù)，它們是一個性質(zhì)的。區(qū)別在于：存儲函數(shù)是用戶自己定義的，而內(nèi)部函數(shù)是MySQL的開發(fā)者定義的。調(diào)用存儲函數(shù)與使用MySQL的內(nèi)部函數(shù)的方法是一樣的。執(zhí)行存儲函數(shù)需要擁有EXECUTE權(quán)限。2.調(diào)用執(zhí)行存儲函數(shù)操作案例知識點概述示例8-11運(yùn)行結(jié)果如下：調(diào)用示例8-8的存儲函數(shù)func_getStudentName，返回學(xué)號為“1308013101”學(xué)生的姓名。操作案例知識點概述示例8-12運(yùn)行結(jié)果如下：調(diào)用示例8-9的存儲函數(shù)func_getGradeBySNoCNo，返回學(xué)號為“1308013101”學(xué)生的“01001”課程的成績。操作案例知識點概述示例8-13運(yùn)行結(jié)果如下：調(diào)用示例8-10的存儲函數(shù)func_getStuNoNameById，在成績表（score）中查詢成績大于等于90分的學(xué)生成績情況。存儲函數(shù)修改存儲存儲函數(shù)使用ALTERFUNCTION語句，其語法格式如下：

ALTERFUNCTION<函數(shù)名>[characteristic…]

characteristic:{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string‘說明：characteristic參數(shù)的取值及說明與創(chuàng)建存儲函數(shù)中的一樣。3.使用ALTERFUNCTION語句修改存儲函數(shù)操作案例知識點概述示例8-14運(yùn)行結(jié)果如下：修改示例8-8的存儲函數(shù)func_getStudentName的定義，將訪問數(shù)據(jù)權(quán)限更改為READSSQLDATA，并加上注釋信息“根據(jù)學(xué)號查找學(xué)生姓名”。存儲函數(shù)刪除存儲函數(shù)使用DROPFUNCTION語句，其語法格式如下：

DROPFUNCTION[IFEXISTS]<存儲函數(shù)名>4.使用DROPFUNCTION語句刪除存儲函數(shù)操作案例知識點概述示例8-15運(yùn)行結(jié)果如下：刪除示例8-8的存儲函數(shù)func_getStudentName。謝謝存儲過程和存儲函數(shù)

流程控制語句

本節(jié)內(nèi)容1.IF語句2.CASE語句3.WHILE語句4.REPEAT語句5.LOOP、LEAVE語句6.ITERATE語句流程控制語句IF語句用來進(jìn)行條件判斷，根據(jù)是否滿足條件，將執(zhí)行不同的語句。其語法格式如下：

IF<條件表達(dá)式1>THEN<語句塊1> [ELSEIF<條件表達(dá)式2>THEN<語句塊2>] … [ELSE<語句塊n>] ENDIF1.IF語句操作案例知識點概述示例8-16運(yùn)行結(jié)果如下：創(chuàng)建一個存儲過程up_scoreStateInfo，通過一個給定的學(xué)號和課程號，查詢出該學(xué)生指定課程的成績，如果成績合格，則返回1；否則返回0。操作案例知識點概述示例8-17運(yùn)行結(jié)果如下：調(diào)用示例8-16的存儲過程up_scoreStateInfo，獲取學(xué)號為“1308013101”學(xué)生的“01001”課程的成績情況（0/1）。操作案例知識點概述示例8-18運(yùn)行結(jié)果如下：創(chuàng)建一個存儲過程up_scoreRankInfo，通過一個給定的學(xué)號和課程號，查詢出該學(xué)生指定課程的成績，并把成績轉(zhuǎn)換為等級制進(jìn)行返回。操作案例知識點概述示例8-19運(yùn)行結(jié)果如下：調(diào)用示例8-18的存儲過程up_scoreRankInfo，獲取學(xué)號為“1308013101”學(xué)生的“01001”課程的成績等級。流程控制語句簡單CASE語句語法格式如下：

CASE<表達(dá)式名稱> WHEN<表達(dá)式值1>THEN<結(jié)果1> [WHEN<表達(dá)式值2>THEN<結(jié)果2>]… [ELSE<結(jié)果n>] END[CASE]2.CASE語句流程控制語句搜索CASE語句語法格式如下：

CASE WHEN<條件表達(dá)式1>THEN<結(jié)果1> [WHEN<條件表達(dá)式2>THEN<結(jié)果2>]… [ELSE<結(jié)果n>] END[CASE]2.CASE語句操作案例知識點概述示例8-20運(yùn)行結(jié)果如下：創(chuàng)建一個存儲過程up_scoreRankInfo1，通過一個給定的學(xué)號和課程號，查詢出該學(xué)生指定課程的成績，并把成績轉(zhuǎn)換為等級制進(jìn)行返回（使用CASE語句實現(xiàn)）。操作案例知識點概述示例8-21運(yùn)行結(jié)果如下：調(diào)用示例8-20的存儲過程up_scoreRankInfo1，獲取學(xué)號為“1308013101”學(xué)生的“01002”課程的成績等級。操作案例知識點概述示例8-22運(yùn)行結(jié)果如下：查詢成績表（score），輸出學(xué)號、課程編號、成績以及成績等級。操作案例知識點概述示例8-23運(yùn)行結(jié)果如下：查詢學(xué)生表（student），輸出學(xué)號、姓名、性別和班級，要求將性別“男”替換為“♂”、性別“女”替換為“♀”。流程控制語句WHILE語句是有條件控制的循環(huán)語句，當(dāng)滿足條件時，執(zhí)行循環(huán)體內(nèi)的語句。其語法格式如下：

[label:]WHILE<條件表達(dá)式>DO <語句塊> ENDWHILE[label]3.WHILE語句操作案例知識點概述示例8-24運(yùn)行結(jié)果如下：創(chuàng)建一個存儲函數(shù)func_sum，用來計算1+2+3+…+n的和。操作案例知識點概述示例8-25運(yùn)行結(jié)果如下：調(diào)用示例8-24的存儲函數(shù)func_sum，返回1+2+3+…+100的和。流程控制語句REPEAT語句也是有條件控制的循環(huán)語句，當(dāng)滿足特定條件時，則會終止循環(huán)，跳出循環(huán)體。其語法格式如下：

[label:]REPEAT <語句塊> UNTIL<條件表達(dá)式> ENDREPEAT[label]4.REPEAT語句操作案例知識點概述示例8-26運(yùn)行結(jié)果如下：創(chuàng)建一個存儲函數(shù)func_sum1，用來計算1+2+3+…+n的和（使用REPEAT語句實現(xiàn)）。操作案例知識點概述示例8-27運(yùn)行結(jié)果如下：調(diào)用示例8-26的存儲函數(shù)func_sum1，返回1+2+3+…+100的和。流程控制語句LOOP語句可以使某些特定的語句重復(fù)執(zhí)行，實現(xiàn)一個簡單的循環(huán)。但是LOOP語句本身沒有終止循環(huán)的語句，必須配合LEAVE語句使用才更有意義，否則是一個死循環(huán)。其語法格式如下：

[label:]LOOP <語句塊> [LEAVElabel] ENDLOOP[label]5.LOOP、LEAVE語句操作案例知識點概述示例8-28運(yùn)行結(jié)果如下：創(chuàng)建一個存儲函數(shù)func_sum2，用來計算1+2+3+…+n的和（使用LOOP和LEAVE語句實現(xiàn)）。操作案例知識點概述示例8-29運(yùn)行結(jié)果如下：調(diào)用示例8-28的存儲函數(shù)func_sum2，返回1+2+3+…+100的和。流程控制語句TERATE語句可用于跳過本次循環(huán)中尚未執(zhí)行的語句，即ITERATE語句后面的任何語句不再執(zhí)行，重新開始新一輪的循環(huán)。其語法格式如下： ITERATElabel6.ITERATE語句操作案例知識點概述示例8-30運(yùn)行結(jié)果如下：創(chuàng)建一個存儲函數(shù)func_sum3，用來計算1+2+3+…+n的和，但不包括同時能被3和7整除的數(shù)（使用WHILE和ITERATE語句來實現(xiàn)）。操作案例知識點概述示例8-31運(yùn)行結(jié)果如下：調(diào)用示例8-30的存儲函數(shù)func_sum3，返回1+2+3+…+100中不包括同時能被3和7整除的數(shù)的和。謝謝存儲過程和存儲函數(shù)

游標(biāo)

本節(jié)內(nèi)容1.游標(biāo)的操作2.游標(biāo)的使用游標(biāo)游標(biāo)是一種可以對查詢結(jié)果集進(jìn)行按行處理的數(shù)據(jù)結(jié)構(gòu)。在存儲過程中，可以把查詢結(jié)果保存到游標(biāo)中，并可對結(jié)果集中的數(shù)據(jù)逐行地進(jìn)行處理。游標(biāo)中的數(shù)據(jù)保存在內(nèi)存中，從其中提取數(shù)據(jù)的速度要比從數(shù)據(jù)表中直接提取數(shù)據(jù)的速度快得多。游標(biāo)（1）聲明游標(biāo)聲明游標(biāo)是指使用DECLARE語句聲明并創(chuàng)建一個游標(biāo)。其語法格式如下：

DECLARE<游標(biāo)名稱>CURSORFOR<select語句>1.游標(biāo)的操作例如：聲明一個名為myCursor的游標(biāo)，從學(xué)生表（student）中查詢出sName和deptName字段的值。

DECLAREmyCursorCURSORFORSELECTsName,deptNameFROMstudent;游標(biāo)（2）打開游標(biāo)打開游標(biāo)是指使用OPEN語句打開已經(jīng)聲明但尚未打開的游標(biāo)，并執(zhí)行游標(biāo)中定義的查詢語句以填充數(shù)據(jù)。其語法格式如下：

OPEN