數(shù)據(jù)庫原理與MySQL應(yīng)用-5 存儲函數(shù)與存儲過程

第5章存儲函數(shù)與存儲過程5.1常用的系統(tǒng)函數(shù)數(shù)據(jù)庫原理與設(shè)計(jì)2ABS(x)：返回x的絕對值。PI()：返回圓周率π的值。SQRT()：返回非負(fù)數(shù)的二次方根。MOD(m,n)：返回m被n除后的余數(shù)。ROUND(x,y)：把x四舍五入到y(tǒng)指定的精度返回。如果y為負(fù)數(shù)，則將保留x值到小數(shù)點(diǎn)左邊y位?！纠?-1】示例5.1.1數(shù)學(xué)函數(shù)SELECTSQRT(ROUND(ABS(-4.01*4.01),0)),MOD(-10,3),MOD(10,-3)5.1.2字符串函數(shù)1、計(jì)算字符串字符數(shù)的函數(shù)和字符串長度的函數(shù)

CHAR_LENGTH(str)：返回字符串str所包含的字符個(gè)數(shù)。

LENGTH(str)：返回值為字符串的字節(jié)長度。一個(gè)漢字是3個(gè)字節(jié)，一個(gè)數(shù)字或字母是1個(gè)字節(jié)。【例5-2】示例SELECTCHAR_LENGTH('CHINA'),LENGTH('CHINA');SELECTCHAR_LENGTH('中國')字符數(shù),LENGTH('中國')字符串長度;2.合并字符串函數(shù)

CONCAT(s1,s2,……)返回結(jié)果為s1,s2,……連接成的字符串，如果任何一個(gè)參數(shù)為NULL，則返回值為NULL?！纠?-3】示例SELECTCONCAT('MySQL版本：',@@version)版本信息1,CONCAT_WS('-','MySQL','8.0.27')版本信息2;

CONCAT_WS(sep,s1,s2,……)返回結(jié)果為s1,s2,……連接成的字符串，并用sep字符間隔。3.字符串大小寫轉(zhuǎn)換函數(shù)

LOWER(str)或LCASE(str)是將字符串str中的字母字符全部轉(zhuǎn)換成小寫字母?！纠?-4】根據(jù)用戶名查詢customers表中指定用戶的信息。要求：用戶在輸入用戶名時(shí)不做大小寫字母的限制。SELECT*FROMcustomersWHERELOWER(c_name)=LCASE('redhOOK');UPPER(str)或UCASE(str)是將字符串str中的字母字符全部轉(zhuǎn)換成大寫字母。4.刪除空格函數(shù)

LTRIM(str)返回刪除前端空格的字符串str；【例5-5】根據(jù)用戶名查詢customers表中指定用戶的信息。請考慮到用戶輸入值時(shí)可能存在輸入前端或尾部空格的情況。SELECT*FROMcustomersWHEREUPPER(c_name)=TRIM(UCASE('redhOOK'));TRIM(str)返回刪除前端和尾部空格的字符串str。

RTRIM(str)返回刪除尾部空格的字符串str；【注意】這3個(gè)函數(shù)只刪除字符串前端和尾部空格，不刪除字符串中間的空格。5.取子串函數(shù)

LEFT(str,length)返回字符串str最左側(cè)長度為length的子串；【例5-6】返回fruits表中f_name值以'b'開頭的水果信息。SELECT*FROMfruitsWHERESUBSTRING(f_name,1,1)='b';SUBSTRING(str,start,length)返回字符串str從start開始長度為length的子串。RIGHT(str,length)返回字符串str最右側(cè)長度為length的子串；SELECT*FROMfruitsWHEREf_nameLIKE'b%';6.其它字符串函數(shù)函數(shù)名功能INSERT(s1,x,len,s2)將字符串s1中x位置開始長度為len的子串用s2替換REPLACE(s,s1,s2)將字符串s中所有的子串s1用s2替換STRCMP(s1,s2)比較字符串s1和s2，相等返回0，大于等于返回1，小于返回-1REPEAT(s,n)返回字符串s重復(fù)n次的結(jié)果REVERSE(s)將字符串s反轉(zhuǎn)，返回的字符串順序和s順序相反FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出現(xiàn)的位置，字符串列表s2是一個(gè)由多個(gè)逗號分開的字符串組成的列表POSITION(s1INs2)或LOCATE(s1,s2)返回子串s1在字符串s2中第一次出現(xiàn)的位置5.1.3日期和時(shí)間函數(shù)1.獲取當(dāng)前系統(tǒng)的日期及取日期的年、月、日函數(shù)

CURDATE()或CURRENT_DATE()返回當(dāng)前系統(tǒng)日期，格式為'YYYY-MM-DD'。

【例5-8】通過orders表和customers表，查詢2021年下訂單的客戶編號、姓名及所在的城市。SELECTdistinctc.c_id,c_name,c_cityFROMcustomersc,ordersoWHEREc.c_id=o.c_idANDYEAR(o_date)=2021;

YEAR(d)、MONTH(d)、DAY(d)分別返回日期或日期時(shí)間d的年、月、日的值。

2.獲取當(dāng)前系統(tǒng)日期時(shí)間函數(shù)NOW()SYSDATE()CURRENT_TIMESTAMP()LOCALTIME()格式為'YYYY-MM-DDHH:MM:SS'。【例5-9】根據(jù)出生日期計(jì)算年齡。SELECTSYSDATE(),YEAR(SYSDATE())-YEAR('2003/05/23')年齡;3.其它日期和時(shí)間函數(shù)函數(shù)名功能CURTIME()或CURRENT_TIME()返回當(dāng)前的系統(tǒng)時(shí)間DAYOFWEEK(date)返回date所代表的一星期中的第幾天(1-7)QUARTER(date)返回date在一年中的季度（1-4）WEEK(date)返回日期date為一年中第幾周（0-53）HOUR(time)返回time的小時(shí)值（0-23）MINUTE(time)返回time的分鐘數(shù)（0-59）SECOND(time)返回time的秒數(shù)（0-59）DATE_ADD(date,INTERVALikeyword)返回日期date加上間隔時(shí)間i的結(jié)果。如:DATE_ADD('2021-11-29',INTERVAL2YEAR)DATE_SUB(date,INTERVALikeyword)返回日期date減去間隔時(shí)間i的結(jié)果。如:DATE_SUB('2021-11-2920:10:58',INTERVAL10SECOND)DATEDIFF(date1,date2)返回起始時(shí)間date1和結(jié)束時(shí)間date2之間的天數(shù)。5.1.4系統(tǒng)信息函數(shù)1.獲取MySQL版本號、用戶名和數(shù)據(jù)庫名函數(shù)USER()返回當(dāng)前登錄的用戶名。

【例5-10】顯示當(dāng)前MySQL版本號、登錄的用戶名和使用的數(shù)據(jù)庫。SELECTVERSION()版本號,USER()登錄名,DATABASE()數(shù)據(jù)庫名;

VERSION()返回MySQL服務(wù)器版本號。

DATABASE()返回當(dāng)前使用數(shù)據(jù)庫名。2.其它系統(tǒng)信息函數(shù)函數(shù)名功能CONNECTION_ID()返回MySQL服務(wù)器當(dāng)前連接的次數(shù)，每個(gè)連接都有各自唯一的IDFOUND_ROWS()返回最后一個(gè)SELECT查詢檢索的總行數(shù)CHARSET(str)返回字符串str的字符集，默認(rèn)的字符集是utf8LAST_INSERT_ID()返回最后生成的AUTO_INCREMENT值5.1.5條件判斷函數(shù)1.IF()函數(shù)IF(條件表達(dá)式,v1,v2)如果條件表達(dá)式是真則函數(shù)返回v1值，否則返回v2的值?！纠?-11】顯示customers表的c_id、c_name和c_email，當(dāng)c_email字段值為NULL時(shí)，顯示值為none，否則顯示當(dāng)前字段的值。SELECTc_id,c_name,IF(c_emailISNULL,'none',c_email)c_emailFROMcustomers;2.IFNULL()函數(shù)IFNULL(v1,v2)返回參數(shù)v1或v2的值。假如v1不為NULL，則返回值為v1，否則返回值為v2。【例5-12】顯示customers表的c_id、c_name和c_email，當(dāng)c_email字段值為NULL時(shí)，顯示值為none，否則顯示當(dāng)前字段的值。SELECTc_id,c_name,IFNULL(c_email,'none')c_emailFROMcustomers;3.CASE函數(shù)（1）語法格式一CASE表達(dá)式WHENv1THENr1WHENv2THENr2……[ELSErn]END【例5-13】對fruits表按f_name的升序排列，顯示前三條記錄的水果中文名稱。SELECTf_name,CASEf_nameWHEN'apple'THEN'蘋果'WHEN'apricot'THEN'杏'WHEN'banana'THEN'香蕉'END中文名稱FROMfruitsORDERBYf_nameLIMIT3;（2）語法格式二CASEWHEN條件表達(dá)式THENr1WHEN條件表達(dá)式THENr2……[ELSErn]END【例5-14】對fruits表按f_name的升序排列，顯示前三條記錄的水果單價(jià)與平均單價(jià)的比較信息。SELECTf_name,f_price,

CASE

WHENf_price>(SELECTAVG(f_price)FROMfruits)THEN'高于平均價(jià)格'

WHENf_price<(SELECTAVG(f_price)FROMfruits)THEN'低于平均價(jià)格'

ELSE'等于平均價(jià)格'

END價(jià)格比較FROMfruitsORDERBYf_nameLIMIT3;5.1.6加密函數(shù)MD5(str)，計(jì)算字符串str的MD5128比特校驗(yàn)和。SHA(str)，計(jì)算字符串str的SHA檢驗(yàn)和。SHA加密算法比MD5更加安全。SHA2(str,hash_length)，使用hash_length作為長度，加密str。Hash_length支持224、256、384、512和0，其中0等同于256。【例5-15】創(chuàng)建用戶user表，為每條記錄的密碼字段值進(jìn)行加密。CREATETABLEuser(u_idintNOTNULLAUTO_INCREMENTPRIMARYKEY,u_namevarchar(20),u_passvarchar(256));INSERTINTOuser(u_name,u_pass)VALUES('mary',SHA2('123456',0)),('jack',MD5('jack12345'));

SELECT*FROMuser;5.2

存儲函數(shù)數(shù)據(jù)庫原理與設(shè)計(jì)221）字符串常量字符串常量指用單引號或雙引號括起來的字符序列。在MySQL中推薦使用單引號。如：'SCOTT'5.2.1常量與變量1.常量【例5-16】查詢表fruits中f_name值為apple的供應(yīng)商編號。SELECTs_idFROMfruitsWHEREf_name='apple';SELECTs_idFROMfruitsWHERE'f_name'='apple';是如何進(jìn)行比較的？2）數(shù)值常量

數(shù)值常量可以分為整數(shù)常量和小數(shù)常量?！纠?-17】統(tǒng)計(jì)orderitems表中訂單數(shù)量超過3單（包含3單）的訂單編號o_num。SELECTo_num訂單編號,COUNT(*)訂單數(shù)量FROMorderitemsGROUPBYo_num

HAVING訂單數(shù)量>=3;orderitems表3）日期和時(shí)間常量

日期和時(shí)間常量使用特定格式的字符日期值表示，用單引號括起來。【例5-18】根據(jù)orders表和customers表，查詢2021年9月30日的訂單編號、用戶名稱及地址信息。SELECTo_num,c_name,c_addressFROMorderso,customerscWHEREo.c_id=c.c_idando_date='2021/09/30';orders表customers表4）布爾值常量

布爾值只有true和false兩個(gè)值，SQL命令運(yùn)行結(jié)果用1代表true，用0代表false。【例5-19】查詢fruits表中以’t’開頭的水果編號的f_id、f_name及f_price是否大于水果平均單價(jià)的判斷結(jié)果。SELECTf_id,f_name,f_price>(SELECTAVG(f_price)FROMfruits)單價(jià)比較結(jié)果FROMfruitsWHEREf_idLIKE't%';5）NULL值

NULL值適用于各種字段類型，通常表示“不確定的值”，NULL值參與的運(yùn)算，結(jié)果仍為NULL值。【例5-20】在fruits表中插入一條記錄，f_id為t3,f_name為orange，s_id為101。INSERTINTOfruits(f_id,s_id,f_name)VALUES('t3',101,'orange');將插入的f_id值為t3的記錄的f_price字段值在原有價(jià)格的基礎(chǔ)增加1.5元。UPDATEfruitsSETf_price=f_price+1.5WHEREf_id='t3';DELETEFROMfruitsWHEREf_priceISNULL;1）局部變量

2.變量【例5-21】查詢fruits表中最高單價(jià)值賦給變量max_price，并顯示其值。SET@max_value=(SELECTMAX(f_price)FROMfruits);SELECT@max_value最高單價(jià);（1）局部變量的定義與賦值

SET@局部變量名=表達(dá)式1[,@局部變量名=表達(dá)式2,……]（2）局部變量的顯示

SELECT@局部變量名[,@局部變量名,……]【例5-22】查詢suppliers表中供應(yīng)商編號為101的供應(yīng)商姓名和電話的值賦給變量name和phone，并顯示兩個(gè)變量的結(jié)果。SELECTs_name,s_callINTO@name,@phoneFROMsuppliersWHEREs_id=101;SELECT@name,@phone;【例5-23】根據(jù)name變量所給的值查詢customers表中指定客戶的信息。SET@name='RedHook';SELECT*FROMcustomersWHEREc_name=@name;2）全局變量

【例5-24】通過全局變量查看MySQL的相關(guān)信息。SELECT@@version,@@basedir,@@license,@@port;

全局變量是MySQL系統(tǒng)提供并賦值的變量。用戶不能定義全局變量，只能使用。全局變量名稱說明@@version返回服務(wù)器版本號@@basedir返回MySQL安裝基準(zhǔn)目錄@@license返回服務(wù)器的許可類型@@port返回服務(wù)器偵聽TCP/IP連接所用端口1、語句塊BEGINSQL語句｜SQL語句塊END注意：①BEGIN…END語句塊包含了該程序塊的所有處理操作，允許語句塊嵌套。②在MySQL中單獨(dú)使用BEGIN…END語句塊沒有任何意義，只有將其封裝在存儲過程、存儲函數(shù)等程序內(nèi)部才有意義。5.2.2語句塊、注釋和重置語句結(jié)束標(biāo)記2、注釋1）單行注釋

使用“#”符號作為單行語句的注釋符，寫在需要注釋的行或語句后方?！纠?-25】示例。#求兩個(gè)數(shù)的最小值SET@x=5,@y=6;#定義兩個(gè)變量并賦值SELECTIF(@x<@y,@x,@y)最小值;#比較兩個(gè)變量并輸出最小值

2）多行注釋使用/*和*/括起來可以連續(xù)書寫多行的注釋語句?！纠?-26】示例。/*在MySQLWorkbench工具下，MySQL執(zhí)行UPDATE或DELETE語句時(shí)，如果WHERE短語中沒有給出包含主鍵的條件，執(zhí)行報(bào)錯(cuò)，WHERE短語中包含主鍵條件則執(zhí)行正常。因?yàn)镸ySQL運(yùn)行在safe-updates模式下，該模式會導(dǎo)致非主鍵條件下無法執(zhí)行UPDATE或DELETE語句，需要執(zhí)行命令SETSQL_SAFE_UPDATES=0修改數(shù)據(jù)庫模式。*/SETSQL_SAFE_UPDATES=0;DELETEFROMfruitsWHEREf_name='xxtt';3、重置命令結(jié)束標(biāo)記1）為什么要重置命令結(jié)束標(biāo)記

在MySQL中，服務(wù)器處理的語句是以分號為結(jié)束標(biāo)記的。但在創(chuàng)建存儲函數(shù)、存儲過程的時(shí)候，函數(shù)體或存儲過程體中可以包含多個(gè)SQL語句，每個(gè)SQL語句都是以分號結(jié)尾，而服務(wù)器處理程序時(shí)遇到第一個(gè)分號則結(jié)束程序執(zhí)行，這時(shí)就需要使用DELIMITER語句將MySQL語句的結(jié)束標(biāo)記修改為其它符號。2）語法：

DELIMITER符號符號可以是一些特殊符號，如兩個(gè)#、兩個(gè)@、兩個(gè)$、兩個(gè)%等?！纠?-27】示例。DELIMITER@@SELECT*FROMfruits@@DELIMITER;SELECT*FROMfruits;5.2.3存儲函數(shù)1、創(chuàng)建存儲函數(shù)CREATEFUNCTION

函數(shù)名([參數(shù)名參數(shù)數(shù)據(jù)類型[,…]])RETURNS

函數(shù)返回值的數(shù)據(jù)類型BEGIN

函數(shù)體;

RETURN

語句;END2、調(diào)用存儲函數(shù)SELECT

函數(shù)名([參數(shù)值[,…]])【例5-28】創(chuàng)建存儲函數(shù)sphone，根據(jù)所給的供應(yīng)商編號s_id值，函數(shù)返回該供應(yīng)商的電話s_call。DELIMITER@@CREATEFUNCTIONsphone(sidINT)RETURNSchar(20)BEGINRETURN(SELECTs_callFROMsupperliersWHEREs_id=sid);END@@（1）報(bào)錯(cuò)的原因：MySQL開啟了bin-log日志，所以創(chuàng)建函數(shù)或者存儲過程時(shí)，必須聲明其為確定性的，或者聲明為不修改數(shù)據(jù)。（2）解決方法：設(shè)置log_bin_trust_routine_creators全局變量為1，信任存儲程序的創(chuàng)建者?！纠?-28】創(chuàng)建存儲函數(shù)sphone，根據(jù)所給的供應(yīng)商編號s_id值，函數(shù)返回該供應(yīng)商的電話s_call。SETGLOBALlog_bin_trust_function_creators=1;DELIMITER@@CREATEFUNCTIONsphone(sidINT)RETURNSchar(20)BEGINRETURN(SELECTs_callFROMsupperliersWHEREs_id=sid);END@@DELIMITER;SELECTsphone(101);3.刪除存儲函數(shù)DROPFUNCTION函數(shù)名;【注意】函數(shù)名后沒有括號【例5-29】刪除例5-28創(chuàng)建的sphone存儲函數(shù)。DROPFUNCTIONsphone;5.3

程序流程控制語句數(shù)據(jù)庫原理與設(shè)計(jì)405.3.1條件判斷語句1、程序中變量的使用1）聲明變量

DECLARE局部變量名[,……]數(shù)據(jù)類型[DEFAULT默認(rèn)值];

【說明】（1）DECLARE聲明的局部變量，變量名前不能加@。（2）DEFUALT子句提供了一個(gè)默認(rèn)值，如果沒有給默認(rèn)值，局部變量初始值默認(rèn)為NULL。

2）為變量賦值SET局部變量名=表達(dá)式1[,局部變量名=表達(dá)式2,……];【例5-30】創(chuàng)建求任意兩個(gè)數(shù)和的存儲函數(shù)sum_fn()。DELIMITER@@CREATEFUNCTIONsum_fn(afloat,bfloat)RETURNSfloatBEGINDECLAREx,yfloat;SETx=a,y=b;RETURNx+y;END@@SELECTsum_fn(7,3);2、IF語句1）形式一IF條件表達(dá)式THENSQL語句塊1;[ELSESQL語句塊2;]ENDIF;【例5-31】創(chuàng)建函數(shù)max_int，判斷整型變量a和b的大小。DELIMITER@@CREATEFUNCTIONmax_fn(aint,bint)RETURNSINTBEGINIFa>bTHENRETURNa;ELSERETURNb;ENDIF;END@@DELIMITER;SELECTmax_fn(7,8)最大值;

2）形式二IF條件表達(dá)式1THENSQL語句塊1;ELSEIF條件表達(dá)式2THENSQL語句塊2;……ELSESQL語句塊n;ENDIF;【例5-32】創(chuàng)建判斷某一年是否為閏年的函數(shù)leap_year()。閏年的判斷條件為：年值能被4整除但不能被100整除，或者能被400整除。3、CASE語句1）形式一CASE

表達(dá)式

WHEN

表達(dá)式值1THENSQL語句塊1;

WHEN

表達(dá)式值2THENSQL語句塊2;……

WHEN

表達(dá)式值nTHENSQL語句塊n;

[ELSE

SQL語句塊n+1;]END;【例5-33】創(chuàng)建存儲函數(shù)email，根據(jù)所給的客戶編號c_id值，函數(shù)返回該客戶的郵箱c_email；然后判斷顯示orders表中的o_num、c_id和客戶郵箱。2）條件判斷CASE

WHEN條件1

THENSQL語句塊1;

WHEN條件2THENSQL語句塊2;……

WHEN

條件nTHENSQL語句塊n;

[ELSE

SQL語句塊n+1;]END;【例5-34】用第二種形式實(shí)現(xiàn)例5-33。SELECTo_num,c_id,CASE

WHEN

c_id=10001THENemail(10001)WHENc_id=10002THENemail(10002)WHENc_id=10003THENemail(10003)WHENc_id=10004THENemail(10004)END客戶郵箱FROMorders;1、LOOP循環(huán)[標(biāo)簽:]LOOP

SQL語句塊;IF

條件

THEN

LEAVE

標(biāo)簽;ENDIF;ENDLOOP;5.3.2

循環(huán)語句【例5-35】創(chuàng)建sum_fn()存儲函數(shù)，計(jì)算1~n的和。2、WHILE循環(huán)WHILE

條件

DOSQL語句塊;ENDWHILE;【例5-36】計(jì)算1~n的和。3、REPEAT循環(huán)REPEATSQL語句塊;

UNTIL條件ENDREPEAT;【例5-37】計(jì)算1~n中能被3和5整除的數(shù)的和。5.4

存儲過程數(shù)據(jù)庫原理與設(shè)計(jì)545.4.1存儲過程概述存儲過程是指用于執(zhí)行特定操作的SQL語句的集合，在需要時(shí)可以直接調(diào)用，提高代碼的重用性。

存儲過程的主要優(yōu)點(diǎn)：存儲過程執(zhí)行速度快。存儲過程在創(chuàng)建時(shí)被編譯，在第一次執(zhí)行之后，就駐留在內(nèi)存中，之后每次執(zhí)行該存儲過程均不需要再重新編譯，所以使用存儲過程可以提高數(shù)據(jù)庫的執(zhí)行速度。

存儲過程的主要優(yōu)點(diǎn)：存儲過程可以減少網(wǎng)絡(luò)通信流量。存儲過程由多條SQL語句組成，但調(diào)用執(zhí)行僅用一條語句，所以只有少量的SQL語句在網(wǎng)絡(luò)線上傳輸，從而減少了網(wǎng)絡(luò)流量并降低了網(wǎng)絡(luò)負(fù)載。

存儲過程的主要優(yōu)點(diǎn)：存儲過程具有安全特性。參數(shù)化的存儲過程可以防止SQL注入式攻擊，而且系統(tǒng)管理員可以通過GRANT、REVOKE等命令實(shí)現(xiàn)對用戶數(shù)據(jù)訪問權(quán)限的控制，避免了非授權(quán)用戶對數(shù)據(jù)的訪問，保證了數(shù)據(jù)的安全。

存儲過程的主要優(yōu)點(diǎn)：存儲過程允許模塊化編程。創(chuàng)建一次存儲過程，存儲在數(shù)據(jù)庫中后，就可以在程序中重復(fù)調(diào)用任意多次，減少了數(shù)據(jù)庫開發(fā)人員的工作量。而且數(shù)據(jù)庫專業(yè)人員可以隨時(shí)對存儲過程進(jìn)行修改，對應(yīng)用程序源代碼沒有絲毫影響。創(chuàng)建存儲過程CREATEPROCEDURE存儲過程名()BEGIN

過程體;END5.4.2創(chuàng)建存儲過程CALL存儲過程名();5.4.3調(diào)用存儲過程【例5-38】創(chuàng)建存儲過程apple_proc，在fruits表中查詢供應(yīng)apple的供應(yīng)商編號s_id。SETGLOBALlog_bin_trust_function_creators=1;DELIMITER@@CREATEPROCEDUREapple_proc()BEGINSELECTs_idFROMfruitsWHEREf_name='apple';END@@【例5-39】調(diào)用執(zhí)行存儲過程apple_proc。CALLapple_proc();CREATEPROCEDURE存儲過程名(

[

IN

|

OUT

|

INOUT]參數(shù)1數(shù)據(jù)類型,

[IN|OUT|INOUT]參數(shù)2數(shù)據(jù)類型,……)BEGIN

過程體；END5.4.4存儲過程的參數(shù)※※※

1、IN輸入?yún)?shù)【例5-40】創(chuàng)建一個(gè)向suppliers表中插入新記錄的存儲過程s_in_proc。SELECT*FROMsuppliersWHEREs_id=108;CALLs_in_proc(108,'XiaoTong','GuangZhou','510000','1367845123');2、OUT輸出參數(shù)【例5-41】創(chuàng)建存儲過程s_out_proc，根據(jù)提供的供應(yīng)商編號，返回供應(yīng)商的名稱和電話。3、INOUT輸入輸出參數(shù)【例5-42】使用INOUT參數(shù)實(shí)現(xiàn)兩個(gè)數(shù)的交換。DROPPROCEDURE存儲過程名;5.4.5刪除存儲過程

【例5-43】刪除已創(chuàng)建的存儲過程swap。DROPPROCEDUREswap;5.4.6存儲過程和存儲函數(shù)的區(qū)別（1）一般存儲過程實(shí)現(xiàn)的功能要復(fù)雜一點(diǎn)，而函數(shù)實(shí)現(xiàn)的功能針對性比較強(qiáng)。（2）存儲過程可以返回參數(shù)，而函數(shù)只能返回值。函數(shù)只能返回一個(gè)變量，存儲過程可以返回多個(gè)。存儲過程的參數(shù)可以有IN、OUT、INOUT三種類型，而函數(shù)只有IN類型。存儲過程聲明時(shí)不需要返回類型，而函數(shù)聲明時(shí)需要描述返回類型，且函數(shù)體中必須包含一個(gè)有效的RETURN語句。（3）函數(shù)可以嵌入在SQL語句中使用，可以在SELECT語句中作為查詢語句的一部分調(diào)用；而存儲過程一般是作為一個(gè)獨(dú)立的部分來執(zhí)行的。5.5

游標(biāo)數(shù)據(jù)庫原理與設(shè)計(jì)67通過SELECT語句查詢時(shí)，返回的結(jié)果是一個(gè)由多行記錄組成的集合。而程序設(shè)計(jì)語言有時(shí)要處理以集合形式返回的數(shù)據(jù)集中的每一行數(shù)據(jù)，為此，SQL提供了游標(biāo)機(jī)制。

游標(biāo)充當(dāng)指針的作用，使應(yīng)用程序設(shè)計(jì)語言一次只能處理查詢結(jié)果中的一行。1、聲明游標(biāo)

DECLARE游標(biāo)名CURSORFORSELECT語句;5.5.1游標(biāo)的使用【說明】（1）聲明游標(biāo)的作用是得到一個(gè)SELECT查詢結(jié)果集，該結(jié)果集中包含了應(yīng)用程序中要處理的數(shù)據(jù)，從而為用戶提供逐行處理的途徑。（2）SELECT語句是對表或視圖的查詢語句?？梢詭HERE條件、ORDERBY或GROUPBY等子句，但不能使用INTO子句。2、打開游標(biāo)

OPEN游標(biāo)名;游標(biāo)必須先聲明后打開。打開游標(biāo)時(shí)，SELECT語句的查詢結(jié)果就被傳送到了游標(biāo)工作區(qū)，以便供用戶讀取。3、提取數(shù)據(jù)

FETCH游標(biāo)名INTO變量名1[，變量名2……];打開游標(biāo)后，游標(biāo)指針指向結(jié)果集的第一行之前，而FETCH語句將使游標(biāo)指針指向下一行。在循環(huán)中使用FETCH語句，每次循環(huán)都會從表中讀取一行數(shù)據(jù)，然后進(jìn)行相同的邏輯處理。4、關(guān)閉游標(biāo)

CLOSE游標(biāo)名;

游標(biāo)一旦關(guān)閉，游標(biāo)占用的資源就被釋放，用戶不能再從結(jié)果集中檢索數(shù)據(jù)，如果想重新檢索，必須重新打開游標(biāo)才能使用?！纠?-44】用游標(biāo)提取customers表中指定客戶的姓名和聯(lián)系人。例：創(chuàng)建存儲過程f_p1，使用游標(biāo)提取fruits表中所有記錄信息。分析：需要使用循環(huán)逐行讀取。要解決的問題：循環(huán)的結(jié)束條件是什么？解決方法：借助于異常處理來實(shí)現(xiàn)。5.5.2異常處理語法：DECLARE

錯(cuò)誤處理類型

HANDLER

FOR

錯(cuò)誤條件

錯(cuò)誤處理程序;【說明】（1）異常處理語句必須放在所有變量及游標(biāo)定義之后，