5.4 以程序化方式操作数据库9-18

MySQL数据库项目五：图书管理数据库MySQL数据库5.4.1SQL编程基础任务字符串、数值、十六进制、日期时间、位字段值、布尔值、NULL值常量12用户变量、系统变量变量常量1．字符串常量字符串是指用单引号或双引号括起来的字符序列，分为ASCII字符串常量和Unicode字符串常量。ASCII字符串常量是用单引号括起来的，由ASCII字符构成的符号串，例如：‘hello’ ‘Howareyou!’Unicode字符串常量与ASCII字符串常量相似，但它前面有一个N标志符（N代表SQL-92标准中的国际语言（NationalLanguage））。N前缀必须为大写。只能用单引号括起字符串，例如：N‘hello’ N‘Howareyou!’Unicode数据中的每个字符用两个字节存储，每个ASCII字符用一个字节存储。常量在字符串中可以使用普通的字符，也可使用转义序列，用来表示特殊的字符，如下表。序

列含

义\0一个ASCII0(NUL)字符\n一个换行符\r一个回车符（Windows中使用\r\n作为新行标志）\t一个定位符\b一个退格符\Z一个ASCII26字符（CTRL+Z）\'一个单引号（“'”）\"一个双引号(“"”)\\一个反斜线（“\”）\%一个“%”符。它用于在正文中搜索“%”的文字实例，否则这里“%”将解释为一个通配符\_一个“_”符。它用于在正文中搜索“_”的文字实例，否则这里“_”将解释为一个通配符常量1．字符串常量执行下面的语句：select'hello','"hello"','""hello""','hel''lo','\'hello';常量2．数值常量数值常量可以分为整数常量和浮点数常量。整数常量即不带小数点的十进制数，例如：1894,2,+145,-21。浮点数常量是使用小数点的数值常量，例如：5.26,1.39,101.5E5。SELECT1894,2,+145,-21;SELECT5.26,1.39,101.5E5;常量3．十六进制常量MySQL支持十六进制值。一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母“X”或小写字“x”。在引号中只可以使用数字“0”到“9”及字母“a”到“f”或“A”到“F”。例如：X'41'表示大写字母A。x'4D7953514C'表示字符串MySQL。十六进制数值不区分大小写，其前缀“X”或“x”可以被“0x”取代而且不用引号。即X'41'可以替换为0x41，注意：“0x”中x一定要小写。十六进制值的默认类型是字符串。如果想要确保该值作为数字处理，可以使用CAST(...ASUNSIGNED)。执行如下语句：select0x41,cast(0x41asunsigned);执行结果如图所示。常量如果要将一个字符串或数字转换为十六进制格式的字符串，可以用HEX()函数。【例】将字符串CAT转换为16进制。selecthex('CAT');执行结果为：练习：查看82以及Mysql的十六进制常量4．日期时间常量日期时间常量：用单引号将表示日期时间的字符串括起来构成。日期型常量包括年、月、日，数据类型为DATE，表示为“2022-04-25”这样的值。时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为TIME，表示为“18:29:55”这样的值。MySQL还支持日期/时间的组合，数据类型为DATETIME或TIMESTAMP，如“2022-04-2518:29:55”。输出当前时间

SELECTCURRENT_TIMESTAMP;常量5．位字段值可以使用b'value'符号写位字段值。value是一个用0和1写成的二进制值。直接显示b'value'的值可能是一系列特殊的符号。例如，b'0'显示为空白，b'1'显示为一个笑脸图标。使用BIN函数可以将位字段常量显示为二进制格式。使用OCT函数可以将位字段常量显示为数值型格式。执行下列语句：selectBIN(b'111101'+0),OCT(b'111101'+0);执行结果如图所示。

常量6．布尔值布尔值只包含两个可能的值：TRUE和FALSE。FALSE的数字值为“0”，TRUE的数字值为“1”。【例】获取TRUE和FALSE的值。

selectTRUE,FALSE;执行结果如图所示。7．NULL值NULL值可适用于各种列类型，它通常用来表示“没有值”、“无数据”等意义，并且不同于数字类型的“0”或字符串类型的空字符串。变量1．用户变量用户可以在表达式中使用自己定义的变量，这样的变量叫做用户变量。在使用用户变量前必须定义和初始化。如果使用没有初始化的变量，它的值为NULL。定义和初始化一个变量可以使用SET语句语法格式为：SET@用户变量1＝表达式1[,用户变量2＝表达式2,…]其中，用户变量1、用户变量2为用户变量名，变量名可以由当前字符集的文字、数字字符、“.”、“_”和“$”组成。

变量用于临时存放数据，变量有名字及其数据类型两个属性，变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。MySQL中根据变量的定义方式，变量可分为用户变量和系统变量。变量【例】创建用户变量name并赋值为“王林”。SET@name='王林';【例】创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3。SET@user1=1,@user2=2,@user3=3;【例】创建用户变量user4，它的值为user3的值加1。定义用户变量时变量值可以是一个表达式。SET@user4=@user3+1;

在一个用户变量被创建后，它可以以一种特殊形式的表达式用于其他SQL语句中。变量名前面也必须加上符号@。变量【例】查询图书表中书号为C0120的书名，并存储在变量b_name中。SET@b_name=(SELECT书名FROM图书表WHERE书号='C0120');查询图书表中名字等于@b_name值的图书信息。SELECT*FROM图书表WHERE书名=@b_name;SET@name='王林';SELECT@name;【例】创建并查询用户变量name的值。变量【例】获得现在使用的MySQL版本。SELECT@@VERSION;说明：在MySQL中，系统变量VERSION的值设置为版本号。在变量名前必须加两个@符号才能正确返回该变量的值。

MySQL有一些特定的设置，当MySQL数据库服务器启动的时候，这些设置被读取来决定下一步骤。例如，有些设置定义了数据如何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。和用户变量一样，系统变量也是一个值和一个数据类型，但不同的是，系统变量在MySQL服务器启动时就被引入并初始化为默认值。变量大多数的系统变量应用于其他SQL语句中时，必须在名称前加两个@符号，而为了与其他SQL产品保持一致，某些特定的系统变量是要省略这两个@符号的。【例】获得系统当前时间。selectCURRENT_TIME;

执行结果如图所示。MySQL数据库5.4.2常用函数任务数值函数12字符串函数数值函数数值函数数值函数是MySQL中一种很重要的函数，主要用于处理数值方面的运算。如果没有这些函数，用户在编写有关数值运算方面的代码时将会复杂很多。例如，如果没有ABS求绝对值函数，要取一个数的绝对值，就需要进行多次判断，直接使用该函数可以大大提高用户的工作效率。函

数功

能ABS(x)返回数值x的绝对值MOD(x,y)返回数值x除以数值y后的余数CEIL(x)返回大于数值x的最小整数值FLOOR(x)返回小于数值x的最大整数值RAND()返回0～1内的随机数ROUND(x)返回对参数x进行四舍五入后的值，ROUND(x)返回整数值，ROUND(x,y)返回参数x四舍五入后保留y位小数的值TRUNCATE(x,y)对数值x进行截取，保留小数点后y位数字表MySQL中常用的数值函数及其功能数值函数【实例1】执行SQL语句，求5，-5和-5.5的绝对值，执行结果如下：SELECTABS(5),ABS(-5),ABS(-5.5);函数ABS(x)的返回值是数值x的绝对值。正数的绝对值是其本身，负数的绝对值是其相反数。1求绝对值函数数值函数函数MOD(x,y)的返回值是数值x除以数值y后的余数。与x%y的结果相同，除数和被除数任何一个为NULL，返回结果都将为NULL；除数为0将是非法运算，返回结果为NULL。SELECTMOD(6,4),MOD(6,-4),MOD(NULL,6),MOD(6,0),MOD(0,2.5);【实例2】执行SQL语句，求6除以4，6除以-4，NULL除以6，6除以0，及0除以2.5的余数，执行结果如下：2求余函数数值函数SELECTCEIL(2.45),CEIL(-2.45);【实例3】执行SQL语句，分别求大于数值2.45和-2.45的最小整数值，执行结果如下：3用于获取整数的函数CEIL(x)1MySQL中用于获取整数的函数主要有CEIL(x)和FLOOR(x)。函数CEIL(x)的返回值是大于数值x的最小整数值数值函数3用于获取整数的函数SELECTFLOOR(2.45),FLOOR(-2.45);【实例4】执行SQL语句，求小于数值2.45和-2.45的最大整数值，执行结果如下：FLOOR(x)2函数FLOOR(x)的返回值是小于数值x的最大整数值。数值函数函数RAND()的返回值是0～1内的小数，并且每次的运行结果都不同。【实例5】执行SQL语句，使用函数RAND()获取随机数，执行结果如下：4获取随机数的函数SELECTRAND(),RAND(),RAND();数值函数

函数ROUND()的作用是对数值执行四舍五入操作，当函数格式为ROUND(x)时，返回值为整数；当函数格式为ROUND(x,y)时，对数值x进行四舍五入并保留小数点后y位。SELECTROUND(100.144),ROUND(100.568),ROUND(100.144,2),ROUND(100.568,2);【实例6】执行SQL语句，使用函数ROUND(x)和ROUND(x,y)对数值进行四舍五入操作，执行结果如下：5四舍五入函数数值函数函数TRUNCATE(x,y)的作用是对数值x进行截取，保留小数点后y位。其与ROUND()函数的区别是，ROUND()函数在截取值时会四舍五入；而TRUNCATE(x,y)函数直接截取值，并不进行四舍五入。SELECTTRUNCATE(1.42,1),TRUNCATE(1.58,1),ROUND(1.58,1);【实例7】执行SQL语句，使用函数TRUNCATE(x,y)和ROUND(x,y)分别截取数值，执行结果如下：6截取小数函数字符串函数字符串函数

字符串函数是MySQL中使用最频繁的函数，主要用于处理数据库中字符串类型的数据。表MySQL中常用的字符串函数及其功能函

数功

能LENGTH(str)，CHAR_LENGTH(str)返回字符串长度或字符个数CONCAT(str1,str2…strn)，CONCAT_WS(x,str1,str2…strn)合并字符串INSERT(str,x,y,instr)，REPLACE(str,a,b)替换字符串LOWER(str)，UPPER(str)字符大小写转换LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)获取字符串的一部分LPAD(str1,n,str2)，RPAD(str1,n,str2)填充字符串LTRIM(str)，RTRIM(str)，TRIM(str)删除字符串左侧、右侧或两侧空格REPEAT(str,n)返回字符串str重复n次的结果LOCATE(str1,str)返回子字符串的开始位置REVERSE(str)反转字符串字符串函数1返回字符串长度和字符串中字符个数的函数函数LENGTH(str)用于返回字符串的长度，一个英文字符和数字占用1个字节。SELECTLENGTH('abcdef'),LENGTH('字符长度');【实例8】执行SQL语句，使用函数LENGTH(str)返回字符串长度，执行结果如下：字符串函数1返回字符串长度和字符串中字符个数的函数函数CHAR_LENGTH(str)用于返回字符串中的字符个数。SELECTCHAR_LENGTH('abcdef'),CHAR_LENGTH('字符个数');【实例9】执行SQL语句，使用函数CHAR_LENGTH(str)返回字符串中的字符个数，执行结果如下：字符串函数2合并字符串的函数函数CONCAT(str1,str2…strn)可以将多个字符串拼接成为一个字符串，但如果参数中有一个NULL值，则返回结果都将为NULL。SELECTCONCAT('abcd','efg'),CONCAT('abcd',NULL,'efg');【实例10】执行SQL语句，使用函数CONCAT(str1,str2…strn)拼接字符串，执行结果如下：字符串函数2合并字符串的函数函数CONCAT_WS(x,str1,str2…strn)是函数CONCAT(str1,str2…strn)的特殊形式，作用是以第一个参数为分隔符，连接后面的多个字符串。SELECTCONCAT_WS('_','ab','cd','ef'),CONCAT_WS('_','gh',NULL,'ij');【实例11】执行SQL语句，使用函数CONCAT_WS(x,str1,str2…strn)拼接字符串，执行结果如下：

提示由执行结果可以看出，函数CONCAT_WS(x,str1,str2…strn)会忽略分隔符后的NULL值，但如果分隔符为NULL，则返回结果为NULL。字符串函数3替换字符串的函数函数INSERT(str,x,y,instr)的作用是将字符串str从第x位置开始，y个字符长的子串替换为字符串instr。SELECTINSERT('beijinglvyoushichang',13,8,'gonglue');【实例12】执行SQL语句，使用函数INSERT(str,x,y,instr)把字符串“beijinglvyoushichang”从第13个字符开始后面的8个字符替换为字符串“gonglue”，执行结果如下：字符串函数4字母大小写转换函数函数LOWER(str)用于将字符串str中的字母全部转换为小写字母，函数UPPER(str)用于将字符串str中的字母全部转换为大写字母。SELECTLOWER('aBcD'),UPPER('aBcD');【实例13】执行SQL语句，分别使用函数LOWER(str)和UPPER(str)把字符串“aBcD”转换为小写字母和大写字母，执行结果如下：字符串函数5获取指定长度字符串的函数函数LEFT(str,x)用于获取字符串str中最左边的x个字符，函数RIGHT(str,x)用于获取字符串str中最右边的x个字符。SELECTLEFT('beijinglvyougonglue',7),RIGHT('beijinglvyougonglue',7);【实例14】执行SQL语句，分别使用函数LEFT(str,x)和RIGHT(str,x)获取字符串“beijinglvyougonglue”左边和右边的7个字符，执行结果如下：小结1数值函数2字符串函数

ABS(x)MOD(x,y)CEIL(x)FLOOR(x)RAND()ROUND(x)TRUNCATE(x,y)LENGTH(str)，CHAR_LENGTH(str)CONCAT(str1,str2…strn)，CONCAT_WS(x,str1,str2…strn)INSERT(str,x,y,instr)，REPLACE(str,a,b)LOWER(str)，UPPER(str)LEFT(str,x)，RIGHT(str,x)，SUBSTRING(str,x,y)MySQL数据库5.4.3存储过程、存储函数、触发器以程序方式操作数据

创建与使用存储过程（不带参数的存储过程）

存储过程是数据库服务器上一组预先编译好的SQL语句的集合，作为一个单元存储在数据库中，可以被应用程序作为一个整体来进行调用。在调用过程中，存储过程可以接收参数，执行后返回参数值。

存储过程概述数据库开发人员在进行数据库开发时，为了实现一定的功能，经常会将负责不同功能的语句集中起来而且按照用途分别独立放置，以便能够反复调用，而这些独立放置且拥有不同功能的语句块，称之为“过程”（Procedure）。存储过程（StoredProcedure）是一组完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过过程名和给出参数值来调用它们。存储过程基本概念MySQL的存储程序可分以下四大类:(1)存储函数(storedfunction)。返回一个计算结果，该结果可以用在表达式里。(2)存储过程(storedprocedure)。不直接返回一个结果，但可以用来完成一般的运算或是生成一个结果集并传递回客户。(3)触发器(trigger)。与数据表相关联，当那个数据表被INSERT、DELETE、UPDATE语句修改时，触发器将自动执行。(4)事件(event)。根据时间表在预订时刻自动执行。

存储过程的类型(1)存储过程的使用，提高了程序设计的灵活性，增强了SQL语言的功能。(2)存储过程把一组功能代码作为单位组件。(3)使用存储过程有利于提高程序的执行速度。(4)使用存储过程能减少网络访问的负荷。(5)作为一种安全机制，系统管理员可以充分利用存储过程对相应的数据的访问权限的进行限制。存储过程的作用创建存储过程语法格式如下：CREATEPROCEDURE存储过程名([参数1，参数2，…])[特征参数1

特征参数2...]

存储过程体;参数:[IN|OUT|INOUT]参数名

数据类型特征参数:LANGUAGESQL|[NOT]DETERMINISTIC|{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string'存储过程体:语句序列创建和执行存储过程创建存储过程语法格式如下：CREATEPROCEDURE存储过程名()BEGIN

存储过程体;END//

创建和执行不带参数的存储过程DELIMITER命令在MySQL中，服务器处理语句的时候是以分号为结束标志的。但是在创建存储过程的时候，存储过程体中可能包含多个SQL语句，每个SQL语句都是以分号为结尾的，这时服务器处理程序的时候遇到第一个分号就会认为程序结束，这肯定是不行的。所以这里使用DELIMITER命令将MySQL语句的结束标志修改为其他符号。说明：$$是用户定义的结束符，通常这个符号可以是一些特殊的符号，如两个“#”，两个“￥”等。DELIMITER语法格式为：DELIMITER$$【例】将MySQL结束符修改为两个斜杠“/”符号。DELIMITER//要想恢复使用分号“;”作为结束符，运行下面命令即可：DELIMITER;使用SQL语句在“

librarydb”数据库中创建一个名为t_du1的存储过程。该存储过程返回图书表中所有类别为“计算机”的记录。对应的SQL语句如下：DELIMITER$$CREATEPROCEDUREt_du1()BEGINSELECT*FROM图书表WHERE类别='计算机';END$$DELIMITER;实例USElibrarydb;CALLt_du1();调用该存储过程：

创建和执行不带参数的存储过程DELIMITER//CREATEPROCEDUREt_du11()BEGINSELECT*FROM图书表WHERE类别='计算机';END//DELIMITER;创建和执行不带参数的存储过程调用存储过程

创建存储过程实现查询读者表中人数的功能,并执行它。说明：通常SELECT语句不会被直接用在存储过程中。首先创建查询读者表中人数的存储过程：CREATEPROCEDUREquery_members() SELECTCOUNT(*)FROM读者表;这是一个不带参数的简单的存储过程。CALLquery_members();调用该存储过程：实例

查看存储过程的定义可以使用下面两种方法查看存储过程的定义、权限、字符集等信息。1．使用showprocedurestatus命令查看存储过程的定义。2．使用MySQL命令“showcreateprocedure存储过程名;”可以查看指定数据库指定存储过程的详细信息。例如查看query_members()存储过程的详细信息，可以使用“showcreateprocedurequery_members()”例：查看query_members()存储过程的详细信息。

查看语句：“showcreateprocedurequery_members()”删除存储过程【例】删除存储过程t_du11()。DROPPROCEDUREIFEXISTSt_du11;存储过程创建后需要删除时使用DROPPROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。语法格式为：

DROPPROCEDURE[IFEXISTS]存储过程名说明：存储过程名是要删除的存储过程的名称。IFEXISTS子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。以程序方式操作数据创建与使用存储过程（带参数的存储过程）

存储过程（1）存储过程在服务器端运行，执行速度快。（2）存储过程执行一次后，其执行规划就驻留在高速缓冲存储器，在以后的操作中，只需从高速缓冲存储器中调用已编译好的二进制代码执行，提高了系统性能。（3）确保数据库的安全。使用存储过程可以完成所有数据库操作，并可通过编程方式控制上述操作对数据库信息访问的权限。使用存储过程的优点：创建存储过程创建存储过程可以使用CREATEPROCEDURE语句语法格式：CREATEPROCEDURE存储过程名([参数[,…]])存储过程体参数：存储过程的参数，格式如下：当有多个参数的时候中间用逗号隔开。存储过程可以有0个、1个或多个参数。关键字分别是IN、OUT和INOUT分别为输入参数、输出参数和输入/输出参数，输入参数使数据可以传递给一个存储过程。当需要返回一个答案或结果的时候，存储过程使用输出参数。输入/输出参数既可以充当输入参数，也可以充当输出参数。存储过程也可以不加参数，但是名称后面的括号是不可省略的。参数的名字不要等于列的名字。[IN|OUT|INOUT]参数名类型创建存储过程举例【例】编写一个存储过程，实现的功能是删除一个指定用户姓名的用户信息。在关键字BEGIN和END之间指定了存储过程体，因为在程序开始用DELIMITER语句转换了语句结束标志为“$$”，所以BEGIN和END被看成是一个整体，在END后用“$$”结束。当然，BEGIN-END复合语句还可以嵌套使用。DELIMITER$$CREATEPROCEDUREdel_member(INxmCHAR(8))BEGINDELETEFROM读者表WHERE姓名=xm;END$$DELIMITER;要想查看数据库中有哪些存储过程，可以使用SHOWPROCEDURE命令。SHOWPROCEDURESTATUS；要查看某个存储过程的具体信息，可使用SHOWCREATEPROCEDUREdel_member命令，其中del_member是存储过程的名称。SHOWCREATEPROCEDUREdel_member；DECLARE语句1.局部变量在存储过程中可以声明局部变量，它们可以用来存储临时结果。要声明局部变量必须使用DECLARE语句。在声明局部变量的同时也可以对其赋一个初始值。语法格式：DECLARE变量[,…]类型[DEFAULT值]说明：DEFAULT子句给变量指定一个默认值，如果不指定，默认为NULL。【例】声明一个整型变量和两个字符变量。DECLAREnumINT(4);DECLAREstr1,str2VARCHAR(6);说明：局部变量只能在BEGIN…END语句块中声明。局部变量必须在存储过程的开头就声明，声明完后，可以在声明它的BEGIN…END语句块中使用该变量，其他语句块中不可以使用它。SET语句2.使用SET语句赋值要给局部变量赋值可以使用SET语句。语法格式为：SETvar_name=expr【例】在存储过程中给局部变量赋值。

SETnum=1,str1='hello';SELECT...INTO语句3.SELECT...INTO语句使用这个SELECT…INTO语法可以把选定的列值直接存储到变量中。因此，返回的结果只能有一行。语法格式为：

SELECT列名[,…]INTO变量名[,…]数据来源表达式【例】在存储过程体中将图书表中的书名为“庄子”的作者姓名和出版社的值分别赋给变量name和publish。

SELECT作者,出版社INTOname,publish FROM图书表 WHERE书名='庄子';调用存储过程存储过程创建完后，可以在程序、触发器或者存储过程中被调用，调用时都必须使用到CALL语句，其语法格式：CALL存储过程名([参数[,…]])例如：调用del_member存储过程，删除“赵晓光”的用户信息。

CALLdel_member('赵晓光');说明：存储过程名为存储过程的名称，如果要调用某个特定数据库的存储过程，则需要在前面加上该数据库的名称。参数为调用该存储过程使用的参数，这条语句中的参数个数必须总是等于存储过程的参数个数。调用存储过程举例说明：3和6相当于输入参数K1和K2，用户变量K相当于输出参数K3。可以看到，由于3<6，输出参数K的值就为“小于”。调用存储过程是用call和过程名以及一个括号，括号⾥面根据需要，加⼊参数，参数包括输入参数、输出参数、输入输出参数。如果是输出变量，前面加@例：调用存储过程COMPARE：CALLCOMPARE(3,6,@K);SELECT@K;调用存储过程举例【例】

创建一个存储过程，输入月份数字1~12，返回月份所在的季度。DELIMITER$$CREATEPROCEDUREq_quarter(INmonINT,OUTq_nameVARCHAR(8))BEGINCASEWHENmonIN(1,2,3)THENSETq_name='一季度';WHENmonIN(4,5,6)THENSETq_name='二季度';WHENmonIN(7,8,9)THENSETq_name='三季度';WHENmonIN(10,11,12)THENSETq_name='四季度';ELSESETq_name='输入错误';ENDCASE;END$$DELIMITER;调用该存储过程：CALLq_quarter(6,@R);SELECT@r;

调用存储过程举例【例】

创建存储过程，给定书号，到库存表中统计其数量.并用此数量修改图书表中该书的数量。调用该存储过程，修正书号为”A0120”的图书的数量。DELIMITER$$createproceduretj_b(inc_shchar(20))begindeclareslint;selectcount(*)intoslfrom库存表groupby书号having书号=c_sh;update图书表set数量=slwhere书号=c_sh;end$$DELIMITER;

调用该存储过程：calltj_b('A0120');调用存储过程举例创建存储过程，对于读者编号，逐条检查借阅表中该读者的借阅情况：还书日期为空记录，借阅天数=系统日期-借阅日期，如借阅天数<15天，则输出“正常”；15天≤借阅天数≤30天，则输出“通知还书”；借阅天数>30天，则输出“逾期”。调用该存储过程，测试读者编号为“0001”的借阅情况。delimiter$$createprocedurejy_b(inc_dzchar(10),outc_qkvarchar(100))begin

declaretmchar(20);

declarerq1,rq2date;

declaretsint;

declarestatechar(10)default'ok';

declarezt_ccursorforselect条码,借阅日期,还书日期

from

借阅表where读者编号=c_dz;

declarecontinuehandlerfor1329setstate='ERROR';

setc_qk='

';

openzt_c;

repeat

fetchzt_cintotm,rq1,rq2;

if(rq2isNULL)then

set

ts=TIMESTAMPDIFF(day,rq1,curdate());

else

setts=TIMESTAMPDIFF(day,rq1,rq2);

endif;Ifstate='ok'thenbegin

if(ts<=15)then

setc_qk=CONCAT_WS('#',c_qk,tm,'正常');

ENDIF;

IF(ts>15ANDts<=30)THEN

SETc_qk=CONCAT_WS('#',c_qk,tm,'通知还书');

ENDIF;

IF(ts>30)THEN

SETc_qk=CONCAT_WS('#',c_qk,tm,'逾期');

ENDIF;END;ENDIF;

UNTILstate='error'ENDREPEAT;CLOSEzt_c;END$$

DELIMITER;CALLjy_b('0001',@a);SELECT@a;调用存储过程举例调用存储过程jy_b删除存储过程【例】删除存储过程del_member(INxmCHAR(8))。DROPPROCEDUREIFEXISTSdel_member;存储过程创建后需要删除时使用DROPPROCEDURE语句。在此之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。语法格式为：

DROPPROCEDURE[IFEXISTS]存储过程名说明：存储过程名是要删除的存储过程的名称。IFEXISTS子句是MySQL的扩展，如果程序或函数不存在，它防止发生错误。以程序方式操作数据创建与使用存储函数

存储函数存储函数也是过程式对象之一，与存储过程很相似。它们都是由SQL和过程式语句组成的代码片断，并且可以从应用程序和SQL中调用。然而，它们也有一些区别：存储函数不能拥有输出参数，因为存储函数本身就是输出参数；不能用CALL语句来调用存储函数；存储函数必须包含一条RETURN语句，而这条特殊的SQL语句不允许包含于存储过程中。创建存储函数使用CREATEFUNCTION语句。语法格式：CREATEFUNCTION存储函数名([参数[,…]])RETURNS类型DETERMINISTIC

函数体说明：存储函数的定义格式和存储过程相差不大。存储函数的名称不能拥有与存储过程相同的名字。存储函数的参数，只有名称和类型，不能指定IN、OUT和INOUT。RETURNStype子句声明函数返回值的数据类型。存储函数的主体，也叫存储函数体，所有在存储过程中使用的SQL语句在存储函数中也适用。创建存储函数【例】创建一个存储函数，它返回图书表中某本书的作者姓名。DELIMITER$$CREATEFUNCTIONauthor_book11(b_nameCHAR(20))RETURNSCHAR(8)DETERMINISTICBEGINRETURN(SELECT作者FROM图书表WHERE书名=b_name);END$$DELIMITER;说明：RETURN子句中包含SELECT语句时，SELECT语句的返回结果只能是一行且只能有一列值。要查看数据库中有哪些存储函数，可以使用：

SHOWFUNCTIONSTATUS调用存储函数存储函数创建完后，就如同系统提供的内置函数（如VERSION()），所以调用存储函数的方法也差不多，都是使用SELECT关键字。

SELECT存储函数名([参数[,...]])调用author_book11

存储函数：SELECTauthor_book11('庄子');调用author_book11

存储函数存储函数举例【例】创建一个存储函数、返回图书表中所有图书的金额总和。CREATEFUNCTIONSP_NUM()RETURNSFLOAT(7,2)DETERMINISTICRETURN(SELECTSUM(单价*数量)FROM图书表);调用此存储函数：SELECTsp_num();存储函数举例【例】创建一个存储函数，给定读者姓名、判断其类别，若是学生，则返回其可借天数，若不是则返回“-1”。DELIMITER$$CREATEFUNCTIONSP_dz(xmCHAR(10))RETURNSINTDETERMINISTICBEGINDECLARElpCHAR(2);DECLAREtsINT;SELECT读者类型表.类别号,可借天数INTOlp,tsFROM读者类型表,读者表WHERE读者类型表.类别号=读者表.类别号AND姓名=xm;IFlp='1'THENRETURNts;ELSERETURN-1;ENDIF;END$$DELIMITER;，此存储函数给定读者姓名作为输入参数，先按给定的读者姓名到读者类型表,读者表查找类别号，如果类别号等于1，表示学生，返回其可借天数；如果类别号不等于1，

则返回“-1”。MySQL数据库网络课程

同存储过程相同，存储函数被创建之后，用户也可以使用同样的方法来查看用户创建的存储函数的相关信息。

可以用以下三种方法显示数据库内存储函数的信息。

（1）使用“SELECTroutine_nameFROMinformation_schema.ROUTINESWHEREroutine_schema='数据库名'”显示数据库中存储过程和存储函数的名称。

（2）使用“SHOWFUNCTIONstatusWHEREdb='数据库名'”可以显示数据库内所有存储函数名称和存储函数的详细信息。

（3）使用“SHOWCREATEFUNCTION数据库.存储函数名”可以查看指定存储函数的定义信息。

查看存储函数

修改存储函数是由ALTERFUNCTION语句来完成的，其语法格式如下：ALTERFUNCTION存储函数名[特征参数...]

特征参数:{CONTAINSSQL|NOSQL|READSSQLDATA|MODIFIESSQLDATA}|SQLSECURITY{DEFINER|INVOKER}|COMMENT'string’注意：使用ALTER语句只能修改函数的特性。如果要重新完整定义已有的函数，建议先删除该函数，然后再进行创建。修改存储函数存储函数的删除是通过DROPFUNCTION语句来实现的。其语法格式为：DROPFUNCTION[IFEXISTS]fn_name;例如：删除存储函数func_user。DROPFUNCTIONIFEXISTSfunc_user;

删除存储函数思考题：如果你在创建存储函数时提⽰以下错误：[Err]1418-ThisfunctionhasnoneofDETERMINISTIC,NOSQL,orREADSSQLDATAinitsdeclarationandbinarylogging

is

enabled(you*might*wanttousethelesssafelog_bin_trust_function_creatorsvariable？两者定义的区别

存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储函数是由一个或多个SQL语句组成的子程序，可用于封装代码以便重新使用。两者声明的区别

存储过程：

CREATEORREPLACEPROCEDUREmy_proc存储函数：

CREATEORREPLACEFUNCTIONmy_func(p_nameINVARCHAR2:='John’)两者应用的区别

当存储过程和函数被执行的时候，SQLManager会到procedurecache中去取相应的查询语句，如果在procedurecache⾥没有相应的查询语句，SQLManager就会对存储过程和函数进行编译。存储过程和存储函数的区别MySQL数据库5.4.4创建与使用触发器以程序方式操作数据创建与使用触发器优点：①触发器可以确保数据的完整性。②触发器可以帮助记录操作日志。利用触发器可以具体记录时间及事件。③触发器还可以用在操作数据前，对数据进行合法性检查。缺点：①触发器最大的一个问题就是可读性差。因为触发器存储在数据库中，并且由事件驱动，这就意味着触发器有可能不受应用层的控制。这对系统维护是非常有挑战的。②相关数据的变更，可能会导致触发器出错。特别是数据表结构的变更，都可能会导致触发器出错，进而影响数据操作的正常运行。这些都会由于触发器本身的隐蔽性，影响到应用中错误原因排查的效率。

触发器的优缺点

触发器的创建语法格式：CREATETRIGGER触发器名触发时间触发事件ON表名FOREACHROW触发器动作触发时间：触发器触发的时刻，有AFTER和BEFORE两个选项，表示触发器是在激活它的语句之前或之后触发。触发事件：指明了激活触发程序的语句的类型。触发事件可以是下述值之一。INSERT：将新行插入表时激活触发器。如使用INSERT、LOADDATA和REPLACE语句。UPDATE：更改数据时激活触发器。例如，使用UPDATE语句。DELETE：从表中删除某一行时激活触发器。例如，使用DELETE和REPLACE语句。表名：与触发器相关的表名，在该表上发生触发事件才会激活触发器。对于某一表，不能有两个BEFOREUPDATE触发器，但可以有1个BEFOREUPDATE触发器和1个BEFOREINSERT触发器，或1个BEFOREUPDATE触发器和1个AFTERUPDATE触发器。FOREACHROW：这个声明用来指定，对于受触发事件影响的每一行，都要激活触发器的动作。触发器动作：包含触发器激活时将要执行的语句。如果要执行多个语句，可使用BEGIN…END复合语句结构。触发器不能返回任何结果到客户端，也不能调用将数据返回客户端的存储过程。为了阻止从触发器返回结果，不要在触发器定义中包含SELECT语句。创建触发器举例【例】创建触发器，在读者表中删除某读者记录的同时，将借阅表中与该读者有关的借阅

数据全部删除。DELIMITER$$CREATETRIGGERdz_dlAFTERDELETEON读者表FOREACHROWBEGINDELETEFROM借阅表WHERE读者编号=OLD.读者编号;END$$DELIMITER;现在验证一下触发器的功能：

DELETEFROM读者表WHERE读者编号='1001';使用SELECT语句查看借阅表中的情况：SELECT*FROM借阅表WHERE读者编号='1001';这时读者编号为'1001'在借阅表中的所有信息已经被删除了。创建触发器举例【例】创建触发器，实现当向借阅表插入一行数据时，将库存表中该条码的图书的库存状态

改为“借出”。DELIMITER$$CREATETRIGGERjy_insAFTERINSERTON借阅表FOREACHROWBEGINUPDATE库存表SET库存状态='借出'WHERE条码=NEW.条码;END$$DELIMITER;

因为是修改了借阅表的记录后才执行触发器程序修改库存表中的记录，此时借阅表中该记录已经修改了，所以只能用NEW.条码来表示这个修改后的记录的条码，库存表使用WHERE条码=NEW.条码条件查找要修改的记录。创建触发器举例【例】创建触发器，实现若修改借阅表中借阅状态为“已还”，则同时修改库存表中库存

状态为“在馆”。DELIMITER$$CREATETRIGGERjy_upAFTERUPDATEON借阅表FOREACHROWBEGINUPDATE库存表SET库存状态='在馆'WHERE条码=NEW.条码;END$$DELIMITER;触发器的删除、查看和其他数据库对象一样，使用DROP语句即可将触发器从数据库中删除。语法格式：DROPTRIGGER触发器名例如删除触发器members_ins。

DROPTRIGGERmembers_ins;查看当前数据库的所有触发器的定义：SHOWTRIGGERS查看当前数据库中某个触发器的定义：SHOWCREATETRIGGER触发器名;MySQL数据库5.4.5事务任务事务的基本概念、特性12事务的相关操作方法事务的概念事务实际上指的是数据库中的一个操作序列，由一组DML语句组成。事务ACID属性原子性（A）：原子性意味着每个