5 存储函数与存储过程ppt课件数据库原理与应用 .pptx

《5 存储函数与存储过程ppt课件数据库原理与应用 .pptx》由会员分享，可在线阅读，更多相关《5 存储函数与存储过程ppt课件数据库原理与应用 .pptx（84页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、5 存储函数与存储过程教学课件数据库原理与应用 数据库原理数据库原理及应用及应用5.1常用的系统函数数据库原理与设计数据库原理与设计3数据库原理数据库原理及应用及应用ABS(x)：返回：返回x的绝对值。的绝对值。PI()：返回：返回圆周率圆周率的值。的值。SQRT()：返回：返回非负数的二次方根。非负数的二次方根。MOD(m,n)：返回：返回m被被n除后的余数。除后的余数。ROUND(x,y)：把：把x四舍五入到四舍五入到y指定的精度返回。如果指定的精度返回。如果y为负为负数，则将数，则将保留保留x值到小数点左边值到小数点左边y位。位。【例【例5-15-1】示例示例5.1.1 5.1.1 数学

2、函数数学函数SELECT SQRT(ROUND(ABS(-4.01*4.01),0),MOD(-10,3),MOD(10,-3)数据库原理数据库原理及应用及应用5.1.2 5.1.2 字符串函数字符串函数1、计算字符串字符数的函数和字符串长度的函数计算字符串字符数的函数和字符串长度的函数 CHAR_LENGTH(str)：返回：返回字符串字符串str所包含的字符个数。所包含的字符个数。LENGTH(str)：返回：返回值为字符串的字节长度。一个汉字是值为字符串的字节长度。一个汉字是3个字个字节，一节，一个数字个数字或字母是或字母是1个字节。个字节。【例【例5-5-2 2】示例示例SELECT

3、CHAR_LENGTH(CHINA),LENGTH(CHINA);SELECT CHAR_LENGTH(中国)字符数,LENGTH(中国)字符串长度;数据库原理数据库原理及应用及应用2.合并字符串函数合并字符串函数 CONCAT(s1,s2,)返回结果为返回结果为s1,s2,连接成的字符串，如连接成的字符串，如果任何一个参数为果任何一个参数为NULL，则返回值为，则返回值为NULL。【例【例5-5-3 3】示例示例SELECT CONCAT(MySQL版本：,version)版本信息1,CONCAT_WS(-,MySQL,8.0.27)版本信息2;CONCAT_WS(sep,s1,s2,)返回

4、结果为返回结果为s1,s2,连接成的字连接成的字符串，并用符串，并用sep字符间隔。字符间隔。数据库原理数据库原理及应用及应用3.字符串大小写转换函数字符串大小写转换函数 LOWER(str)或或LCASE(str)是将字符串是将字符串str中的字母字符全部转换中的字母字符全部转换成小写字母。成小写字母。【例【例5-5-4 4】根据用户名查询根据用户名查询customerscustomers表中指定用户的信息。要表中指定用户的信息。要求：用户在输入用户名时不做大小写字母的限制。求：用户在输入用户名时不做大小写字母的限制。SELECT*FROM customers WHERE LOWER(c_n

5、ame)=LCASE(redhOOK);UPPER(str)或或UCASE(str)是将字符串是将字符串str中的字母字符全部转换中的字母字符全部转换成大写字母。成大写字母。数据库原理数据库原理及应用及应用4.删除空格函数删除空格函数 LTRIM(str)返回删除前端空格的字符串返回删除前端空格的字符串str；【例【例5-5-5 5】根据用户名查询根据用户名查询customerscustomers表中指定用户的信息。请表中指定用户的信息。请考虑到用户输入值时可能存在输入前端或尾部空格的情况。考虑到用户输入值时可能存在输入前端或尾部空格的情况。SELECT*FROM customers WHER

6、E UPPER(c_name)=TRIM(UCASE(redhOOK);TRIM(str)返回删除前端和尾部空格的字符串返回删除前端和尾部空格的字符串str。RTRIM(str)返回删除尾部空格的字符串返回删除尾部空格的字符串str；【注意注意】这】这3个函数只删除字符串前端和尾部空格，不删除字符个函数只删除字符串前端和尾部空格，不删除字符串中间的空格。串中间的空格。数据库原理数据库原理及应用及应用5.取子串函数取子串函数 LEFT(str,length)返回字符串返回字符串str最左侧长度为最左侧长度为length的子串；的子串；【例【例5-5-6 6】返回返回fruitsfruits表中表

7、中f_namef_name值以值以bb开头的水果信息。开头的水果信息。SELECT*FROM fruits WHERE SUBSTRING(f_name,1,1)=b;SUBSTRING(str,start,length)返回字符串返回字符串str从从start开始长度为开始长度为length的子串。的子串。RIGHT(str,length)返回字符串返回字符串str最右侧长度为最右侧长度为length的子串；的子串；SELECT*FROM fruits WHERE f_name LIKE b%;数据库原理数据库原理及应用及应用6.其它字符串函数其它字符串函数函数名功 能INSERT(s1,x

8、,len,s2)将字符串s1中x位置开始长度为len的子串用s2替换REPLACE(s,s1,s2)将字符串s中所有的子串s1用s2替换STRCMP(s1,s2)比较字符串s1和s2，相等返回0，大于等于返回1，小于返回-1REPEAT(s,n)返回字符串s重复n次的结果REVERSE(s)将字符串s反转，返回的字符串顺序和s顺序相反 FIND_IN_SET(s1,s2)返回字符串s1在字符串列表s2中出现的位置，字符串列表s2是一个由多个逗号分开的字符串组成的列表POSITION(s1 IN s2)或LOCATE(s1,s2)返回子串s1在字符串s2中第一次出现的位置数据库原理数据库原理及应

9、用及应用5.1.3 5.1.3 日期和时间函数日期和时间函数1.获取当前系统的日期及取日期的年、月、日函数获取当前系统的日期及取日期的年、月、日函数 CURDATE()或或CURRENT_DATE()返回当前系统日期，返回当前系统日期，格式为格式为YYYY-MM-DD。【例【例5-5-8 8】通过通过ordersorders表和表和customerscustomers表，查询表，查询20212021年下订单的年下订单的客户编号、姓名及所在的城市。客户编号、姓名及所在的城市。SELECT distinct c.c_id,c_name,c_city FROM customers c,orders

10、o WHERE c.c_id=o.c_id AND YEAR(o_date)=2021;YEAR(d)、MONTH(d)、DAY(d)分别返回日期或日期时间分别返回日期或日期时间d的的年、月、日的值。年、月、日的值。数据库原理数据库原理及应用及应用2.获取当前系统日期时间函数获取当前系统日期时间函数NOW()SYSDATE()CURRENT_TIMESTAMP()LOCALTIME()格式为格式为YYYY-MM-DD HH:MM:SS。【例【例5-5-9 9】根据出生日期计算年龄。根据出生日期计算年龄。SELECT SYSDATE(),YEAR(SYSDATE()-YEAR(2003/05/2

11、3)年龄;数据库原理数据库原理及应用及应用3.其它日期和时间函数其它日期和时间函数函数名功 能CURTIME()或CURRENT_TIME()返回当前的系统时间DAYOFWEEK(date)返回date所代表的一星期中的第几天(1-7)QUARTER(date)返回date在一年中的季度（1-4）WEEK(date)返回日期date为一年中第几周（0-53）HOUR(time)返回time的小时值（0-23）MINUTE(time)返回time的分钟数（0-59）SECOND(time)返回time的秒数（0-59）DATE_ADD(date,INTERVAL i keyword)返回日期da

12、te加上间隔时间i的结果。如:DATE_ADD(2021-11-29,INTERVAL 2 YEAR)DATE_SUB(date,INTERVAL i keyword)返回日期date减去间隔时间i的结果。如:DATE_SUB(2021-11-29 20:10:58,INTERVAL 10 SECOND)DATEDIFF(date1,date2)返回起始时间date1和结束时间date2之间的天数。数据库原理数据库原理及应用及应用5.1.4 5.1.4 系统信息函数系统信息函数1.获取获取MySQL版本号、用户名和数据库名函数版本号、用户名和数据库名函数USER()返回当前登录的用户名。返回当

13、前登录的用户名。【例【例5-5-1010】显示当前显示当前MySQLMySQL版本号、登录的用户名和使用的数版本号、登录的用户名和使用的数据库。据库。SELECT VERSION()版本号,USER()登录名,DATABASE()数据库名;VERSION()返回返回MySQL服务器版本号。服务器版本号。DATABASE()返回当前使用数据库名。返回当前使用数据库名。数据库原理数据库原理及应用及应用2.其它系统信息函数其它系统信息函数函数名功 能CONNECTION_ID()返回MySQL服务器当前连接的次数，每个连接都有各自唯一的IDFOUND_ROWS()返回最后一个SELECT查询检索的总

14、行数CHARSET(str)返回字符串str的字符集，默认的字符集是utf8LAST_INSERT_ID()返回最后生成的AUTO_INCREMENT值数据库原理数据库原理及应用及应用5.1.5 5.1.5 条件判断函数条件判断函数1.IF()函数函数IF(条件表达式条件表达式,v1,v2)如果条件表达式是真则函数返回如果条件表达式是真则函数返回v1值，否则返回值，否则返回v2的值。的值。【例【例5-5-1111】显示显示customerscustomers表的表的c_idc_id、c_namec_name和和c_emailc_email，当，当c_emailc_email字段值为字段值为NU

15、LLNULL时，显示值为时，显示值为nonenone，否则显示当前字段的值。，否则显示当前字段的值。SELECT c_id,c_name,IF(c_email IS NULL,none,c_email)c_email FROM customers;数据库原理数据库原理及应用及应用2.IFNULL()函数函数IFNULL(v1,v2)返回参数返回参数v1或或v2的值。假如的值。假如v1不为不为NULL，则返回值为，则返回值为v1，否则返回值为否则返回值为v2。【例【例5-5-1212】显示显示customerscustomers表的表的c_idc_id、c_namec_name和和c_email

16、c_email，当，当c_emailc_email字段值为字段值为NULLNULL时，显示值为时，显示值为nonenone，否则显示当前字段，否则显示当前字段的值。的值。SELECT c_id,c_name,IFNULL(c_email,none)c_email FROM customers;数据库原理数据库原理及应用及应用3.CASE函数函数（1）语法格式一语法格式一 CASE 表达式表达式 WHEN v1 THEN r1 WHEN v2 THEN r2 ELSE rn END【例【例5-5-1313】对对fruitsfruits表表按按f_namef_name的升序排列，的升序排列，显示前

17、三条记录的水果显示前三条记录的水果中文名称。中文名称。SELECT f_name,CASE f_name WHEN apple THEN 苹果 WHEN apricot THEN 杏 WHEN banana THEN 香蕉 END 中文名称 FROM fruits ORDER BY f_name LIMIT 3;数据库原理数据库原理及应用及应用（2）语法格式语法格式二二CASE WHEN 条件表达式条件表达式 THEN r1 WHEN 条件表达式条件表达式 THEN r2 ELSE rnEND数据库原理数据库原理及应用及应用【例【例5-5-1414】对对fruitsfruits表按表按f_na

18、mef_name的升序排列，显示前三条的升序排列，显示前三条记录的水果单价与平均单价的比较信息。记录的水果单价与平均单价的比较信息。SELECT f_name,f_price,CASE WHEN f_price(SELECT AVG(f_price)FROM fruits)THEN 高于平均价格 WHEN f_price=3;orderitemsorderitems表表数据库原理数据库原理及应用及应用3）日期和时间常量）日期和时间常量 日期和时间常量使用特定格式的字符日期值表示，用单引号括起来。【例【例5-5-1818】根据根据ordersorders表和表和customerscustomer

19、s表，查询表，查询20212021年年9 9月月3030日的日的订单编号、用户名称及地址信息。订单编号、用户名称及地址信息。SELECT o_num,c_name,c_address FROM orders o,customers c WHERE o.c_id=c.c_id and o_date=2021/09/30;ordersorders表表customerscustomers表表数据库原理数据库原理及应用及应用4）布尔值常量）布尔值常量 布尔值只有true和false两个值，SQL命令运行结果用1代表true，用0代表false。【例【例5-5-1919】查询查询fruitsfruits

20、表中以表中以tt开头的水果编号的开头的水果编号的f_idf_id、f_namef_name及及f_pricef_price是否大于水果平均单价的判断结果。是否大于水果平均单价的判断结果。SELECT f_id,f_name,f_price(SELECT AVG(f_price)FROM fruits)单价比较结果 FROM fruits WHERE f_id LIKE t%;数据库原理数据库原理及应用及应用5）NULL值值 NULL值适用于各种字段类型，通常表示“不确定的值”，NULL值参与的运算，结果仍为NULL值。【例【例5-5-2020】在在fruitsfruits表中插入一条记录，表中

21、插入一条记录，f_idf_id为为t3,f_namet3,f_name为为orangeorange，s_ids_id为为101101。INSERT INTO fruits(f_id,s_id,f_name)VALUES(t3,101,orange);将插入的f_id值为t3的记录的f_price字段值在原有价格的基础增加1.5元。UPDATE fruits SET f_price=f_price+1.5 WHERE f_id=t3;DELETE FROM fruits WHERE f_price IS NULL;数据库原理数据库原理及应用及应用1）局部变量）局部变量 2.变量变量【例【例5-5

22、-2121】查询查询fruitsfruits表中最高单价值赋给变量表中最高单价值赋给变量max_pricemax_price，并显，并显示其值。示其值。SET max_value=(SELECT MAX(f_price)FROM fruits);SELECT max_value 最高单价;（1）局部变量的定义与赋值 SET 局部变量名=表达式1,局部变量名=表达式2,（2）局部变量的显示 SELECT 局部变量名,局部变量名,数据库原理数据库原理及应用及应用【例【例5-5-2222】查询查询supplierssuppliers表中供应商编号为表中供应商编号为101101的供应商姓名和的供应商姓

23、名和电话的值赋给变量电话的值赋给变量namename和和phonephone，并显示两个变量的结果。，并显示两个变量的结果。SELECT s_name,s_call INTO name,phone FROM suppliers WHERE s_id=101;SELECT name,phone;【例【例5-5-2323】根据根据namename变量所给的值查询变量所给的值查询customerscustomers表中指定客户的表中指定客户的信息。信息。SET name=RedHook;SELECT*FROM customers WHERE c_name=name;数据库原理数据库原理及应用及应用2

24、）全局全局变量变量 【例【例5-5-2424】通过全局变量查看通过全局变量查看MySQLMySQL的相关信息。的相关信息。SELECT version,basedir,license,port;全局变量是MySQL系统提供并赋值的变量。用户不能定义全局变量，只能使用。全局变量名称说 明version返回服务器版本号basedir返回MySQL安装基准目录license返回服务器的许可类型port返回服务器侦听TCP/IP连接所用端口数据库原理数据库原理及应用及应用1、语句块、语句块BEGIN SQL语句 SQL语句块END注意：注意：BEGINEND语句块包含了该程序块的所有处理操作，允许语句

25、块包含了该程序块的所有处理操作，允许语句块嵌套。语句块嵌套。在在MySQL中单独使用中单独使用BEGINEND语句块没有任何意义，语句块没有任何意义，只有将其封装在存储过程、存储只有将其封装在存储过程、存储函数等程序函数等程序内部才有意义。内部才有意义。5.2.2 5.2.2 语句块、注释和重置语句结束标记语句块、注释和重置语句结束标记数据库原理数据库原理及应用及应用2、注释、注释1）单行注释）单行注释 使用使用“#”符号作为单行语句的注释符，写在需要注释的行或语符号作为单行语句的注释符，写在需要注释的行或语句后方句后方。【例【例5-5-2525】示例。示例。#求两个数的最小值SET x=5,

26、y=6;#定义两个变量并赋值SELECT IF(xb THEN RETURN a;ELSE RETURN b;END IF;ENDDELIMITER;SELECT max_fn(7,8)最大值;数据库原理数据库原理及应用及应用2）形式二）形式二IF 条件表达式条件表达式1 THEN SQL语句块语句块1;ELSEIF 条件表达式条件表达式2 THEN SQL语句块语句块2;ELSE SQL语句块语句块n;END IF;数据库原理数据库原理及应用及应用【例【例5-5-3232】创建判断某一年是否为闰年的函数创建判断某一年是否为闰年的函数leap_year()leap_year()。闰年的判断条件

27、为：年值能被闰年的判断条件为：年值能被4 4整除但不能被整除但不能被100100整除，或者能被整除，或者能被400400整除。整除。数据库原理数据库原理及应用及应用3、CASE语句语句1）形式一CASE 表达式 WHEN 表达式值1 THEN SQL语句块1;WHEN 表达式值2 THEN SQL语句块2;WHEN 表达式值n THEN SQL语句块n;ELSE SQL语句块n+1;END;数据库原理数据库原理及应用及应用【例【例5-5-3333】创建存储函数创建存储函数emailemail，根据所给的客户编号，根据所给的客户编号c_idc_id值，值，函数返回该客户的邮箱函数返回该客户的邮箱

28、c_emailc_email；然后判断显示；然后判断显示ordersorders表中的表中的o_numo_num、c_idc_id和客户邮箱。和客户邮箱。数据库原理数据库原理及应用及应用2）条件判断）条件判断CASE WHEN 条件条件1 THEN SQL语句块语句块1;WHEN 条件条件2 THEN SQL语句块语句块2;WHEN 条件条件n THEN SQL语句块语句块n;ELSE SQL语句块语句块n+1;END;数据库原理数据库原理及应用及应用【例【例5-5-3434】用第二种形式实现例用第二种形式实现例5-335-33。SELECT o_num,c_id,CASE WHEN c_id

29、=10001 THEN email(10001)WHEN c_id=10002 THEN email(10002)WHEN c_id=10003 THEN email(10003)WHEN c_id=10004 THEN email(10004)END 客户邮箱FROM orders;数据库原理数据库原理及应用及应用1、LOOP循环循环标签标签:LOOP SQL语句块语句块;IF 条件条件 THEN LEAVE 标签标签;END IF;END LOOP;5.3.5.3.2 2 循环语句循环语句数据库原理数据库原理及应用及应用【例【例5-5-3535】创建创建sum_fn()sum_fn()存储

30、函数，计算存储函数，计算1n1n的和。的和。数据库原理数据库原理及应用及应用2、WHILE循环循环WHILE 条件条件 DO SQL语句块语句块;END WHILE;【例【例5-5-3636】计算计算1n1n的和。的和。数据库原理数据库原理及应用及应用3、REPEAT循环循环REPEAT SQL语句块语句块;UNTIL 条件条件END REPEAT;【例【例5-5-3737】计算计算1n1n中能被中能被3 3和和5 5整除的数的和。整除的数的和。5.4 存储过程数据库原理与设计数据库原理与设计55数据库原理数据库原理及应用及应用5.4.1 5.4.1 存储过程概述存储过程概述 存储过程是指用于

31、执行特定操作的SQL语句的集合，在需要时可以直接调用，提高代码的重用性。存储过程的主要优点：存储过程执行速度快。存储过程在创建时被编译，在第一次执行之后，就驻留在内存中，之后每次执行该存储过程均不需要再重新编译，所以使用存储过程可以提高数据库的执行速度。数据库原理数据库原理及应用及应用 存储过程的主要优点：存储过程可以减少网络通信流量。存储过程由多条SQL语句组成，但调用执行仅用一条语句，所以只有少量的SQL语句在网络线上传输，从而减少了网络流量并降低了网络负载。数据库原理数据库原理及应用及应用 存储过程的主要优点：存储过程具有安全特性。参数化的存储过程可以防止SQL注入式攻击，而且系统管理员

32、可以通过GRANT、REVOKE等命令实现对用户数据访问权限的控制，避免了非授权用户对数据的访问，保证了数据的安全。数据库原理数据库原理及应用及应用 存储过程的主要优点：存储过程允许模块化编程。创建一次存储过程，存储在数据库中后，就可以在程序中重复调用任意多次，减少了数据库开发人员的工作量。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码没有丝毫影响。数据库原理数据库原理及应用及应用创建创建存储过程存储过程CREATE PROCEDURE 存储过程名()BEGIN 过程体;END5.4.2 5.4.2 创建存储过程创建存储过程CALL 存储过程名();5.4.3 5.4.3 调用

33、存储过程调用存储过程数据库原理数据库原理及应用及应用【例【例5-5-3838】创建存储过程创建存储过程apple_procapple_proc，在，在fruitsfruits表中查询供应表中查询供应appleapple的供应商编号的供应商编号s_ids_id。SET GLOBAL log_bin_trust_function_creators=1;DELIMITER CREATE PROCEDURE apple_proc()BEGIN SELECT s_id FROM fruits WHERE f_name=apple;END【例【例5-5-3939】调用执行存储过程调用执行存储过程apple

34、_procapple_proc。CALL apple_proc();数据库原理数据库原理及应用及应用CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数1 数据类型,IN|OUT|INOUT 参数2 数据类型,)BEGIN 过程体；END5.4.4 5.4.4 存储过程的参数存储过程的参数 数据库原理数据库原理及应用及应用1、IN输入输入参数参数【例【例5-5-4040】创建一个向创建一个向supplierssuppliers表中插入新记录的存储过程表中插入新记录的存储过程s_in_procs_in_proc。SELECT*FROM suppliers WHERE s_

35、id=108;CALL s_in_proc(108,XiaoTong,GuangZhou,510000,1367845123);数据库原理数据库原理及应用及应用2、OUT输出输出参数参数【例【例5-5-4141】创建存储过程创建存储过程s_out_procs_out_proc，根据提供的供应商编号，根据提供的供应商编号，返回供应商的名称和电话。返回供应商的名称和电话。数据库原理数据库原理及应用及应用3、INOUT输入输出参数输入输出参数【例【例5-5-4242】使用使用INOUTINOUT参数实现两个数的交换。参数实现两个数的交换。数据库原理数据库原理及应用及应用DROP PROCEDURE

36、存储过程名;5.4.5 5.4.5 删除存储过程删除存储过程 【例【例5-5-4343】删除已创建的存储过程删除已创建的存储过程swapswap。DROP PROCEDURE swap;数据库原理数据库原理及应用及应用5.4.6 5.4.6 存储过程和存储函数的区别存储过程和存储函数的区别（1）一般存储过程实现的功能要复杂一点，而函数实现的功能针对性比较强。（2）存储过程可以返回参数，而函数只能返回值。函数只能返回一个变量，存储过程可以返回多个。存储过程的参数可以有IN、OUT、INOUT三种类型，而函数只有IN类型。存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包

37、含一个有效的RETURN语句。（3）函数可以嵌入在SQL语句中使用，可以在SELECT语句中作为查询语句的一部分调用；而存储过程一般是作为一个独立的部分来执行的。5.5 游 标数据库原理与设计数据库原理与设计68数据库原理数据库原理及应用及应用 通过通过SELECT语句查询时，返回的结果是一个由多行记录语句查询时，返回的结果是一个由多行记录组成的集合。而程序设计语言有时要处理以集合形式返回的数组成的集合。而程序设计语言有时要处理以集合形式返回的数据集中的每一行数据，为此，据集中的每一行数据，为此，SQL提供了游标机制提供了游标机制。游标游标充当指针的作用，使应用程序设计语言充当指针的作用，使应

38、用程序设计语言一次只能处理一次只能处理查询结果中的查询结果中的一行一行。数据库原理数据库原理及应用及应用1、声明游标、声明游标 DECLARE 游标名 CURSOR FOR SELECT语句;5.5.1 5.5.1 游标的使用游标的使用【说明】（1）声明游标的作用是得到一个SELECT查询结果集，该结果集中包含了应用程序中要处理的数据，从而为用户提供逐行处理的途径。（2）SELECT语句是对表或视图的查询语句。可以带WHERE条件、ORDER BY或GROUP BY等子句，但不能使用INTO子句。数据库原理数据库原理及应用及应用2、打开游标、打开游标 OPEN 游标名游标名;游标必须先声明后打

39、开。游标必须先声明后打开。打开游标时，打开游标时，SELECT语句的查询结果就被传送到了游标工语句的查询结果就被传送到了游标工作区，以便供用户读取。作区，以便供用户读取。数据库原理数据库原理及应用及应用3、提取数据、提取数据 FETCH 游标名游标名 INTO 变量名变量名1，变量名，变量名2;打开打开游标后，游标指针指向结果集的第一行之前，而游标后，游标指针指向结果集的第一行之前，而FETCH语句将使游标指针指向下一行语句将使游标指针指向下一行。在循环中使用在循环中使用FETCH语句，每次循环都会从表中读取一语句，每次循环都会从表中读取一行数据，然后进行相同的逻辑处理。行数据，然后进行相同的

40、逻辑处理。数据库原理数据库原理及应用及应用4、关闭游标、关闭游标 CLOSE 游标名游标名;游标一旦关闭，游标占用的资源就被释放，用户不能再从结果集中检索数据，如果想重新检索，必须重新打开游标才能使用。数据库原理数据库原理及应用及应用【例【例5-5-4444】用游标提取用游标提取customerscustomers表中指定客户的姓名和联系人。表中指定客户的姓名和联系人。数据库原理数据库原理及应用及应用例：例：创建存储过程创建存储过程f_p1，使用，使用游标提取游标提取fruits表表中中所有所有记录信息记录信息。分析：分析：需要使用循环逐行读取。需要使用循环逐行读取。要解决的问题：要解决的问题

41、：循环的结束条件是什么？循环的结束条件是什么？解决方法：解决方法：借助于借助于异常处理异常处理来实现。来实现。数据库原理数据库原理及应用及应用5.5.2 5.5.2 异常处理异常处理语法语法：DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;【说明】【说明】（1）异常处理）异常处理语句必须放在所有语句必须放在所有变量及游标定义之后变量及游标定义之后，所有，所有MySQL表达式之前表达式之前；数据库原理数据库原理及应用及应用 DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;（2）错误处理错误处理类型类型 错误错误处理类型只有处理类型只有C

42、ONTINUE和和EXIT两种两种。CONTINUE表示错误发生后，表示错误发生后，MySQL立即执行自定义错误处立即执行自定义错误处理程序，然后忽略该错误继续执行其它理程序，然后忽略该错误继续执行其它MySQL语句语句。EXIT表示错误发生后，表示错误发生后，MySQL立即执行自定义错误处理程序，立即执行自定义错误处理程序，然后立刻停止其它然后立刻停止其它MySQL语句的执行语句的执行。数据库原理数据库原理及应用及应用（3）错误条件错误条件 错误错误条件定义了自定义错误处理程序运行的时机。错误条件取值条件定义了自定义错误处理程序运行的时机。错误条件取值：SQLSTATE ANSI标准错误代码

43、标准错误代码：包含：包含5个字符的字符串值个字符的字符串值 DECLARE EXIT HANDLER FOR SQLSTATE 42S02 SET info=NO_SUCH_TABLE;DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;MySQL错误代码错误代码：匹配数值类型的错误代码：匹配数值类型的错误代码 DECLARE EXIT HANDLER FOR 1146 SET info=NO_SUCH_TABLE;数据库原理数据库原理及应用及应用（3）错误条件错误条件 SQLWARNING：匹配所有以：匹配所有以01开头的开头的SQLSTATE错误代码错误代码 DE

44、CLARE EXIT HANDLER FOR SQLWARNING SET info=ERROR;DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;NOT FOUND：匹配所有以：匹配所有以02开头的开头的SQLSTATE错误代码错误代码 DECLARE EXIT HANDLER FOR NOT FOUND SET info=NO_SUCH_TABLE;SQLEXCEPION：匹配所有没有被：匹配所有没有被SQLWARNING或或NOT FOUND捕获的捕获的SQLSTATE错误代码错误代码 DECLARE EXIT HANDLER FOR SQLEXCEPTION

45、 SET info=ERROR;数据库原理数据库原理及应用及应用（4）错误处理程序错误处理程序 错误错误发生后，发生后，MySQL会立即执行自定义错误处理程序中的会立即执行自定义错误处理程序中的MySQL语句。语句。DECLARE EXIT HANDLER FOR NOT FOUND SET info=NO_SUCH_TABLE;DECLARE 错误处理类型 HANDLER FOR 错误条件 错误处理程序;数据库原理数据库原理及应用及应用【例【例5-5-4646】创建存储函数创建存储函数f_in_funf_in_fun，向，向fruitsfruits表插入一条记录，表插入一条记录，f_idf_

46、id、s_ids_id和和f_namef_name字段的值为字段的值为a1,101a1,101和和bananabanana，已知，已知a1a1水果水果编号已存在于编号已存在于fruitsfruits表中，违背了主键约束。表中，违背了主键约束。数据库原理数据库原理及应用及应用下面存储函数的创建，加入了错误处理机制，数据库开发人员控下面存储函数的创建，加入了错误处理机制，数据库开发人员控制程序的运行流程，解决了制程序的运行流程，解决了MySQLMySQL自动终止存储程序执行的问题。自动终止存储程序执行的问题。数据库原理数据库原理及应用及应用【例【例5-5-4848】创建存储过程创建存储过程price_up_procprice_up_proc，使用游标更新，使用游标更新fruitsfruits表中的表中的f_pricef_price的值，的值，f_pricef_price值小于等于值小于等于5 5元单价增加元单价增加20%20%，大于，大于5 5元小于等于元小于等于1010元单元单价增加价增加10%10%，其它增加，其它增加5%5%。数据库原理数据库原理及应用及应用