数据库编程ppt课件

《数据库编程ppt课件》由会员分享，可在线阅读，更多相关《数据库编程ppt课件（57页珍藏版）》请在金锄头文库上搜索。

1、数据库编程数据库编程数据库编程数据库编程1项目知识要点与目标项目知识要点项目知识要点知识能力目标知识能力目标学时学时8.1 8.1 任务任务1 MySQL1 MySQL语言结构语言结构8.1.1 8.1.1 常量与变量常量与变量8.1.2 8.1.2 表达式与函数表达式与函数8.2 8.2 任务任务2 2 存储过程存储过程8.2.1 8.2.1 创建存储过程创建存储过程8.2.2 8.2.2 调用存储过程调用存储过程8.2.3 8.2.3 删除存储过程删除存储过程8.2.4 8.2.4 流程控制语句流程控制语句8.3 8.3 任务任务3 3 存储函数存储函数8.3.1 8.3.1 创建存储函数

2、创建存储函数8.3.2 8.3.2 调用存储函数调用存储函数8.4 8.4 任务任务4 4 触发器触发器8.4.1 8.4.1 创建触发器创建触发器8.4.2 8.4.2 删除触发器删除触发器8.5 8.5 综合实例综合实例 PetStorePetStore数据库编程数据库编程【情境】：【情境】：PetstorePetstore数据库操作程数据库操作程序化序化【职业岗位】：程序员【职业岗位】：程序员【典型工作任务】：通过数据库编【典型工作任务】：通过数据库编程操作数据库。程操作数据库。【技能要求】：【技能要求】：1 1、流程控制语句的应用、流程控制语句的应用2 2、存储过程的编写、应用与管理、

3、存储过程的编写、应用与管理3 3、触发器编写、应用与管理、触发器编写、应用与管理【知识要求】：【知识要求】：1 1、顺序结构、分支结构和循环结、顺序结构、分支结构和循环结构的应用构的应用2 2、存储过程创建、调用删除、存储过程创建、调用删除3 3、触发器创建、调用及删除、触发器创建、调用及删除4 4项目实训八项目实训八“员工管理系统员工管理系统”编程操作编程操作4 42任务一 MySQL语言结构1 1字符串常量字符串常量 字符串是指用单引号或双引号括起来的字符序列，分为字符串是指用单引号或双引号括起来的字符序列，分为ASCIIASCII字字符串常量和符串常量和Unicode Unicode 字

4、符串常量。字符串常量。 ASCII ASCII字符串常量是用单引号括起来的，由字符串常量是用单引号括起来的，由ASCIIASCII字符构成的符字符构成的符号串。举例：号串。举例：hellohello，How are you!How are you! Unicode Unicode 字符串常量与字符串常量与ASCIIASCII字符串常量相似，但它前面有一个字符串常量相似，但它前面有一个N N标志符（标志符（N N代表代表 SQL-92SQL-92标准中的国际语言（标准中的国际语言（National LanguageNational Language）。）。N N前缀必须为大写。只能用单引号括起字

5、符串。前缀必须为大写。只能用单引号括起字符串。举例：举例：N Nhellohello，N NHow are you!How are you! Unicode Unicode 数据中的每个字符用两个字节存储，而每个数据中的每个字符用两个字节存储，而每个ASCIIASCII字符字符用一个字节存储。用一个字节存储。 在字符串中不仅可以使用普通的字符，也可使用几个转义序列，在字符串中不仅可以使用普通的字符，也可使用几个转义序列，它们用来表示特殊的字符它们用来表示特殊的字符。【例【例8.18.1】 执行如下语句：执行如下语句： SELECT ThisnIsnFournLines;SELECT Thisn

6、IsnFournLines; 其中，其中，“nn”表示回车。表示回车。3常量2 2数值常量数值常量 数值常量可以分为整数常量和浮点数常量。数值常量可以分为整数常量和浮点数常量。3 3十六进制常量十六进制常量 一个十六进制值通常指定为一个字符串常量，每对十六进制数字一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母被转换为一个字符，其最前面有一个大写字母“X X”或小写字或小写字“x x”。4 4日期时间常量日期时间常量 日期时间常量：用单引号将表示日期时间的字符串括起来构成。日日期时间常量：用单引号将表示日期时间的字符串括起来构成。日期型常量包括年

7、、月、日，数据类型为期型常量包括年、月、日，数据类型为DATEDATE，表示为，表示为“1999-06-171999-06-17”这这样的值。样的值。 时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为时间型常量包括小时数、分钟数、秒数及微秒数，数据类型为TIMETIME，如，如“12:30:43.0001312:30:43.00013”。 日期日期/ /时间的组合，数据类型为时间的组合，数据类型为DATETIMEDATETIME或或TIMESTAMPTIMESTAMP，如，如“1999-1999-06-17 12:30:4306-17 12:30:43”。4常量5 5位字段值位字段值 可

8、以使用可以使用bvaluebvalue符号写位字段值。符号写位字段值。valuevalue是一个用是一个用0 0和和1 1写成的写成的二进制值。直接显示二进制值。直接显示bvaluebvalue的值可能是一系列特殊的符号。例如，的值可能是一系列特殊的符号。例如，b0b0显示为空白，显示为空白，b1b1显示为一个笑脸图标。显示为一个笑脸图标。 使用使用BINBIN函数可以将位字段常量显示为二进制格式。函数可以将位字段常量显示为二进制格式。6 6布尔值布尔值 布尔值只包含两个可能的值：布尔值只包含两个可能的值：TRUETRUE和和FALSEFALSE。 FALSE FALSE的数字值为的数字值为“

9、0 0”，TRUETRUE的数字值为的数字值为“1 1”。7 7NULLNULL值值 NULL NULL值可适用于各种列类型，它通常用来表示值可适用于各种列类型，它通常用来表示“没有值没有值”、“无无数据数据”等意义，并且不同于数字类型的等意义，并且不同于数字类型的“0 0”或字符串类型的空字符或字符串类型的空字符串。串。5用户变量 变量用于临时存放数据，变量有名字及其数据类型两个属变量用于临时存放数据，变量有名字及其数据类型两个属性，变量名用于标识该变量，变量的数据类型确定了该变量存性，变量名用于标识该变量，变量的数据类型确定了该变量存放值的格式及允许的运算。放值的格式及允许的运算。MySQ

10、LMySQL中根据变量的定义方式，变量中根据变量的定义方式，变量可分为用户变量和系统变量。可分为用户变量和系统变量。 1 1用户变量用户变量 用户可以在表达式中使用自己定义的变量，这样的变量叫用户可以在表达式中使用自己定义的变量，这样的变量叫做用户变量。在使用用户变量前必须定义和初始化。如果使用做用户变量。在使用用户变量前必须定义和初始化。如果使用没有初始化的变量，它的值为没有初始化的变量，它的值为NULLNULL。 定义和初始化一个变量可以使用定义和初始化一个变量可以使用SETSET语句语句 语法格式为：语法格式为： SET SET user_variable1user_variable1e

11、xpression1 expression1 ,user_variable2= expression2 , ,user_variable2= expression2 , 其中，其中，user_variable1user_variable1、user_variable2user_variable2为用户变量名，为用户变量名，变量名可以由当前字符集的文字数字字符、变量名可以由当前字符集的文字数字字符、“. .”、“_ _”和和“$ $”组成。组成。6变量举例【例【例8.28.2】 创建用户变量创建用户变量namename并赋值为并赋值为“王林王林”。 SET name=SET name=王林王林;

12、 还可以同时定义多个变量，中间用逗号隔开。还可以同时定义多个变量，中间用逗号隔开。【例【例8.38.3】 创建用户变量创建用户变量user1user1并赋值为并赋值为1 1，user2user2赋值为赋值为2 2，user3user3赋赋值为值为3 3。 SET user1=1, user2=2, user3=3;SET user1=1, user2=2, user3=3; 定义用户变量时变量值可以是一个表达式。定义用户变量时变量值可以是一个表达式。【例【例8.48.4】 创建用户变量创建用户变量user4user4，它的值为，它的值为user3user3的值加的值加1 1。 SET user

13、4=user3+1;SET user4=user3+1;在一个用户变量被创建后，它可以以一种特殊形式的表达式用于其他在一个用户变量被创建后，它可以以一种特殊形式的表达式用于其他SQLSQL语句中。变量名前面也必须加上符号语句中。变量名前面也必须加上符号 。【例【例8.58.5】 创建并查询用户变量创建并查询用户变量namename的值。的值。 SET name=SET name=王林王林; SELECT name; SELECT name;7变量举例【例【例8.68.6】 使用查询给变量赋值。使用查询给变量赋值。 SET student=(SELECT SET student=(SELECT

14、姓名姓名 FROM XS WHERE FROM XS WHERE 学号学号=081101);=081101);【例【例8.78.7】 查询表查询表XSXS中名字等于例中名字等于例6.116.11中中studentstudent值的学生信息。值的学生信息。 SELECT SELECT 学号学号, , 姓名姓名, , 专业名专业名, , 出生日期出生日期 FROM XSFROM XSWHERE WHERE 姓名姓名=student;=student;说明：在说明：在SELECTSELECT语句中，表达式发送到客户端后才进行计算。这说明语句中，表达式发送到客户端后才进行计算。这说明在在HAVINGH

15、AVING、GROUP BYGROUP BY或或ORDER BYORDER BY子句中，不能使用包含子句中，不能使用包含SELECTSELECT列表中列表中所设的变量的表达式。所设的变量的表达式。 对于对于SETSET语句，可以使用语句，可以使用“= =”或或“:=:=”作为分配符。分配给每个作为分配符。分配给每个变量的值可以为整数、实数、字符串或变量的值可以为整数、实数、字符串或NULLNULL值。值。也可以用其他也可以用其他SQLSQL语句代替语句代替SETSET语句来为用户变量分配一个值。在这种语句来为用户变量分配一个值。在这种情况下，分配符必须为情况下，分配符必须为“:=:=”，而不能

16、用，而不能用“= =”，因为在非，因为在非SETSET语句中语句中“= =”被视为比较操作符。被视为比较操作符。【例【例8.88.8】 执行如下语句：执行如下语句： SELECT t2:=(t2:=2)+5 AS t2;SELECT t2:=(t2:=2)+5 AS t2; 结果结果t2t2的值为的值为7 7。 8系统变量 MySQL MySQL有一些特定的设置，当有一些特定的设置，当MySQLMySQL数据库服务器启动的时候，数据库服务器启动的时候，这些设置被读取来决定下一步骤。例如，有些设置定义了数据如这些设置被读取来决定下一步骤。例如，有些设置定义了数据如何被存储，有些设置则影响到处理速

17、度，还有些与日期有关，这何被存储，有些设置则影响到处理速度，还有些与日期有关，这些设置就是系统变量。和用户变量一样，系统变量也是一个值和些设置就是系统变量。和用户变量一样，系统变量也是一个值和一个数据类型，但不同的是，系统变量在一个数据类型，但不同的是，系统变量在MySQLMySQL服务器启动时就服务器启动时就被引入并初始化为默认值。被引入并初始化为默认值。【例【例8.98.9】 获得现在使用的获得现在使用的MySQLMySQL版本。版本。 SELECT VERSION ;SELECT VERSION ;说明：在说明：在MySQLMySQL中，系统变量中，系统变量VERSIONVERSION的

18、值设置为版本号。在变量的值设置为版本号。在变量名前必须加两个名前必须加两个 符号才能正确返回该变量的值。符号才能正确返回该变量的值。 大多数的系统变量应用于其他大多数的系统变量应用于其他SQLSQL语句中时，必须在名称前加语句中时，必须在名称前加两个两个 符号，而为了与其他符号，而为了与其他SQLSQL产品保持一致，某些特定的系统变产品保持一致，某些特定的系统变量是要省略这两个量是要省略这两个 符号的。如符号的。如CURRENT_DATECURRENT_DATE（系统日期）（系统日期）【例【例8.108.10】 获得系统当前时间。获得系统当前时间。 SELECT CURRENT_TIME;SE

19、LECT CURRENT_TIME;9运算符1 1算术运算符算术运算符 算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字算术运算符在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型。算术运算符有：数据类型。算术运算符有：+ +（加）、（加）、 （减）、（减）、* *（乘）、（乘）、/ /（除）和（除）和% %（求模）（求模）5 5种运算。种运算。2 2比较运算符比较运算符 比较运算符（又称关系运算符），用于比较两个表达式的值，其运算结果比较运算符（又称关系运算符），用于比较两个表达式的值，其运算结果为逻辑值，可以为三种之一：为逻辑值，可以为三种之一：1 1（真）、（

20、真）、0 0（假）及（假）及 NULL NULL（不能确定）。（不能确定）。3 3、逻辑运算符、逻辑运算符 逻辑运算符用于对某个条件进行测试，运算结果为逻辑运算符用于对某个条件进行测试，运算结果为TRUETRUE（1 1）或）或FALSEFALSE（0 0）。）。4 4、运算符优先级、运算符优先级 当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后当一个复杂的表达式有多个运算符时，运算符优先级决定执行运算的先后次序。执行的顺序会影响所得到的运算结果。运算符优先级如下表所示。次序。执行的顺序会影响所得到的运算结果。运算符优先级如下表所示。运运 算算 符符优先级优先级运运 算算 符符

21、优先级优先级+ +（正）、（正）、（负）（负）1 1NOTNOT5 5* *（乘）、（乘）、/ /（除）、（除）、% %（模）（模） 2 2ANDAND6 6+ +（加）、（加）、（减）（减）3 3OROR7 7= =， ， =，=，!= != ，! ! ，! !K2 THENSET K3= 大于;ELSEIF K1=K2 THENSET K3= 等于;ELSE SET K3= 小于;END IF;END$DELIMITER ;说明：存储过程中说明：存储过程中K1K1和和K2K2是输入参数，是输入参数，K3K3是输出参数。是输出参数。要比较的数存入要比较的数存入K1,K2K1,K2“大于”=K

22、3K1K2?K1=K2？“等于”=K3“小于”=K3输出K3是否是否【例例8.238.23】 创建存储过程，判断两个输入的参数哪一个更大。创建存储过程，判断两个输入的参数哪一个更大。25存储过程创建完后，可以在程序、触发器或者存储过程中被调用，存储过程创建完后，可以在程序、触发器或者存储过程中被调用，但是都必须使用到但是都必须使用到CALLCALL语句。语句。语法格式：语法格式：CALL sp_name(parameter,.)CALL sp_name(parameter,.)parameterparameter为调用该存储过程使用的参数，这条语句中的参数个数必为调用该存储过程使用的参数，这条

23、语句中的参数个数必须总是等于存储过程的参数个数。须总是等于存储过程的参数个数。如果是输出变量，前面加如果是输出变量，前面加 调用该存储过程：调用该存储过程：CALL COMPAR(3, 6, K);CALL COMPAR(3, 6, K);SELECT K;SELECT K;说明：说明：3 3和和6 6相当于输入参数相当于输入参数K1K1和和K2K2，用户变量，用户变量K K相当于输出参数相当于输出参数K3K3。可以看到，由于可以看到，由于363=5 AND sl10 THEN UPDATE Sell SET 订购单价订购单价=订购单价订购单价*0.8 WHERE 身份证号身份证号=sfz A

24、ND 图书编号图书编号=bh; END IF; END IF; END$DELIMITER ;调用存储过程调用存储过程dj_update： CALL dj_update (张三张三, 网络数据库网络数据库);调用存储过程举例27CASE语句（2 2）CASECASE语句语句语法格式为：语法格式为： CASE 表达式表达式 WHEN 值值1 THEN 语句序列语句序列1 WHEN值值2 THEN语句序列语句序列2 . ELSE语句序列语句序列e END CASE或者：或者： CASE WHEN 条件条件1 THEN 语句序列语句序列1 WHEN条件条件2 THEN语句序列语句序列2 . ELSE

25、语句序列语句序列e END CASE 第一种格式中第一种格式中表达式表达式是要被判断的值或表达式，接下来是一系列的是要被判断的值或表达式，接下来是一系列的WHEN-THEN块，每一块的块，每一块的值值参数都要与参数都要与表达式表达式比较的值，如果为真，就执行比较的值，如果为真，就执行语句序列语句序列中的中的SQL语句。如果前面的每一个块都不匹配就会执行语句。如果前面的每一个块都不匹配就会执行ELSE块指定的语句。块指定的语句。CASE语句最后以语句最后以END CASE结束。结束。 第二种格式中第二种格式中CASE关键字后面没有参数，在关键字后面没有参数，在WHEN-THEN块中，块中，条件条

26、件指指定了一个比较表达式，表达式为真时执行定了一个比较表达式，表达式为真时执行THEN后面的语句。后面的语句。 第二种格式与第一种格式相比，更能够实现更为复杂的条件判断，使用起来第二种格式与第一种格式相比，更能够实现更为复杂的条件判断，使用起来更方便。更方便。28【例例8.25】 创建一个存储过程，当给定参数为时返回创建一个存储过程，当给定参数为时返回“上升上升”，给定参数为，给定参数为时返回时返回“下降下降”，给定其他参数时返回，给定其他参数时返回“不变不变”。 DELIMITER $ CREATE PROCEDURE var_cp(IN str VARCHAR(1), OUT direct

27、 VARCHAR(4) ) BEGIN CASE strWHEN U THEN SET direct =上升上升;WHEN D THEN SET direct =下降下降;ELSE SET direct =不变不变; END CASE; END$ DELIMITER ;以上的以上的CASE语句用第二种格式来写如下：语句用第二种格式来写如下： CASE WHEN str= U THEN SET direct =上升上升;WHEN str= D THEN SET direct =下降下降;ELSE SET direct =不变不变; END CASE;CASE语句举例29课堂练习1 1、创建存储过

28、程，创建存储过程，输入员工编号输入员工编号bhbh ，输出该员工的性别。，输出该员工的性别。2 2、创建存储过程，创建存储过程，输入员工编号输入员工编号bhbh和级别和级别jbjb两个参数，如果两个参数，如果jbjb为为A A，该员，该员工收入增加工收入增加500500元，元， jbjb为为B B，该员工收入增加，该员工收入增加300300元，元，jbjb为为C C，该员工收入，该员工收入增加增加150150元，元，jbjb为为A A、B B、C C以外的其他值，该员工收入增加以外的其他值，该员工收入增加5050元。元。3 3、创建存储过程，比较两个员工的实际收入，如前者比后者高就输出、创建存

29、储过程，比较两个员工的实际收入，如前者比后者高就输出0 0，否则输出否则输出1 1。并调用该存储过程比较。并调用该存储过程比较”000001000001”和和”108991108991”两员工的收两员工的收入。入。30课堂练习参考答案1 1、创建存储过程、创建存储过程create procedure cp(in id1 char(6),in id2 char(6),out bj int)begin declare sr1,sr2 float(8);select income-outcome into sr1 from salary where employeeid=id1;select inc

30、ome-outcome into sr2 from salary where employeeid=id2;if id1id2 then set bj=0;elseset bj=1;end if;end 2 2、 调用存储过程：调用存储过程： call cp(000001,108991,bj)3 3、输出结果、输出结果 select bj;31循环语句（3 3）循环语句）循环语句MySQLMySQL支持支持3 3条用来创建循环的语句：条用来创建循环的语句：WHILEWHILE、REPEATREPEAT和和LOOPLOOP语句。语句。 WHILEWHILE语句语法格式为：语句语法格式为： 开始标

31、号开始标号: WHILE条件条件 DO程序段程序段 END WHILE 结束标号结束标号 语句首先判断语句首先判断条件条件是否为真，为真则执行是否为真，为真则执行程序段程序段中的语句，然后再中的语句，然后再次进行判断，为真则继续循环，不为真则结束循环。次进行判断，为真则继续循环，不为真则结束循环。 开始标号开始标号和和结束标号结束标号是是WHILE语句的标注。除非语句的标注。除非开始标号开始标号存在，存在，否则否则结束标号结束标号，并且如果两者都出现，它们的名字必须是相同的。，并且如果两者都出现，它们的名字必须是相同的。32WHILE语句举例【例例8.26】 创建一个带创建一个带WHILE执行

32、执行5次循环的存储过程。次循环的存储过程。 DELIMITER $ CREATE PROCEDURE dowhile() BEGIN DECLARE a INT DEFAULT 5; WHILE a 0 DO SET a = a 1; END WHILE; END$ DELIMITER ;33REPEAT语句 REPEATREPEAT语句格式如下：语句格式如下： 开始标号开始标号: REPEAT 程序段程序段 UNTIL 条件条件 END REPEAT 结束标号结束标号用用REPEATREPEAT语句替换例语句替换例8.268.26的的WHILEWHILE循环过程如下：循环过程如下： REPE

33、AT REPEAT a=a a=a 1;1; UNTIL a1; UNTIL a1; END REPEAT; END REPEAT;说明：说明：REPEATREPEAT语句和语句和WHILEWHILE语句的区别在于：语句的区别在于：REPEATREPEAT语句先执行语句，后语句先执行语句，后进行判断；而进行判断；而WHILEWHILE语句是先判断，条件为真时才执行语句。语句是先判断，条件为真时才执行语句。34LOOP语句 LOOP LOOP语句语法格式如下：语句语法格式如下： 开始标号开始标号: LOOP 程序段程序段 END LOOP 结束标号结束标号说明：说明：LOOPLOOP允许某特定语

34、句或语句群的重复执行，实现一个简单的循环构造，允许某特定语句或语句群的重复执行，实现一个简单的循环构造，程序段程序段是需要重复执行的语句。在循环内的语句一直重复至循环被退出，退出时是需要重复执行的语句。在循环内的语句一直重复至循环被退出，退出时通常伴随着一个通常伴随着一个LEAVE LEAVE 语句。结构如下：语句。结构如下：LEAVE label【例例8.278.27】 使用使用LOOPLOOP语句重写语句重写8.268.26的存储过程。的存储过程。 DELIMITER $ DELIMITER $ CREATE PROCEDURE doloop() CREATE PROCEDURE dolo

35、op() BEGIN BEGIN SET a=5; SET a=5; Label: LOOP Label: LOOP SET a=a SET a=a 1;1; IF a1 THEN IF a1 THEN LEAVE Label; LEAVE Label; END IF; END IF; END LOOP Label; END LOOP Label; END$ END$ DELIMITER ; DELIMITER ;35存储过程的嵌套【例例8.28】 创建一个存储过程创建一个存储过程sell_insert()，作用是向，作用是向Sell表中插入一行数据。创表中插入一行数据。创建另外一个存储过程建

36、另外一个存储过程sell_update，在其中调用第一个存储过程，如果给定参数为，在其中调用第一个存储过程，如果给定参数为0，则修改由第一个存储过程插入记录的是否发货字段为则修改由第一个存储过程插入记录的是否发货字段为已发货已发货，如果给定参数为，如果给定参数为1则删除则删除第一个存储过程插入的记录，并将操作结果输出。第一个存储过程插入的记录，并将操作结果输出。第一个存储过程：向第一个存储过程：向Sell表中插入一行数据表中插入一行数据 CREATE PROCEDURE sell_insert() INSERT INTO Sell VALUES(17,430103198608201963, I

37、SBN7-301-06342-3,4, 30, 2013-03-05, NULL, NULL, NULL);第二个存储过程：调用第一个存储过，并输出结果第二个存储过程：调用第一个存储过，并输出结果 CREATE PROCEDURE sell_update(IN X INT(1), OUT STR CHAR(8) BEGIN CALL sell_insert(); CASE WHEN x=0 THEN UPDATE Sell SET 是否发货=已发货 WHERE 订单号=17; SET STR=修改成功; WHEN X=1 THEN DELETE FROM Sell WHERE 订单号=17;

38、SET STR=删除成功; END CASE; END 36存储过程的嵌套接下来调用存储过程接下来调用存储过程sell_update来查看结果：来查看结果： CALL sell_update (1, str); SELECT str; 结果为：删除成功结果为：删除成功 CALL sell_update (0, str); SELECT str; 结果为：修改成功结果为：修改成功37课堂练习1 1、创建存储过程，创建存储过程，输入一个数输入一个数x x ，输出，输出x!x!。2 2、创建存储过程创建存储过程DO_hz1DO_hz1，作用是向，作用是向salarysalary表中插入汇总行（合计，

39、收入表中插入汇总行（合计，收入总和，支出总和）。创建另外一个存储过程总和，支出总和）。创建另外一个存储过程DO_show2DO_show2，在其中调用第一个，在其中调用第一个存储过程，并输出汇总结果。存储过程，并输出汇总结果。38任务三 存储函数存储函数也是过程式对象之一，与存储过程很相似。它们都是由存储函数也是过程式对象之一，与存储过程很相似。它们都是由SQLSQL和过程和过程式语句组成的代码片断，并且可以从应用程序和式语句组成的代码片断，并且可以从应用程序和SQLSQL中调用。然而，它们也有一中调用。然而，它们也有一些区别：些区别：（1 1）存储函数不能拥有输出参数，因为存储函数本身就是输

40、出参数；）存储函数不能拥有输出参数，因为存储函数本身就是输出参数；（2 2）不能用）不能用CALLCALL语句来调用存储函数；语句来调用存储函数；（3 3）存储函数必须包含一条）存储函数必须包含一条RETURNRETURN语句，而这条特殊的语句，而这条特殊的SQLSQL语句不允许包含语句不允许包含于存储过程中。于存储过程中。创建存储函数使用创建存储函数使用CREATE FUNCTIONCREATE FUNCTION语句。语句。语法格式：语法格式： CREATE FUNCTION 存储函数名存储函数名 (参数参数,.) RETURNS 类型类型 函数体函数体39创建存储函数【例例8.298.29

41、】 创建一个存储函数，它返回创建一个存储函数，它返回BookBook表中图书数目作为结果。表中图书数目作为结果。 DELIMITER $ DELIMITER $ CREATE FUNCTION num_book() CREATE FUNCTION num_book() RETURNS INTEGER RETURNS INTEGER BEGIN BEGINRETURN (SELECT COUNT(*) FROM Book);RETURN (SELECT COUNT(*) FROM Book); END$ END$ DELIMITER ; DELIMITER ;RETURNRETURN子句中包含子

42、句中包含SELECTSELECT语句时，语句时，SELECTSELECT语句的返回结果只能是一行且只能有一语句的返回结果只能是一行且只能有一列值。列值。虽然此存储函数没有参数，使用时也要用（），如虽然此存储函数没有参数，使用时也要用（），如num_book()num_book()。要查看数据库中有哪些存储函数，可以使用要查看数据库中有哪些存储函数，可以使用SHOW FUNCTION STATUSSHOW FUNCTION STATUS命令。命令。 SHOW FUNCTION STATUS调用存储函数调用存储函数存储函数创建完后存储函数创建完后，就如同系统提供的内置函数（如就如同系统提供的内置函

43、数（如VERSION()VERSION()），所以调），所以调用存储函数的方法也差不多，都是使用用存储函数的方法也差不多，都是使用SELECTSELECT关键字。关键字。语法格式为：语法格式为：SELECT sp_name (func_parameter,.)调用调用【例例8.298.29】存储函数存储函数: :Select NUM_book()40存储函数举例【例例8.308.30】 创建一个存储函数，返回创建一个存储函数，返回BookBook表中某本书的作者姓名。表中某本书的作者姓名。 DELIMITER $ DELIMITER $ CREATE FUNCTION author_book(

44、b_name CHAR(20) CREATE FUNCTION author_book(b_name CHAR(20) RETURNS CHAR(8) RETURNS CHAR(8) BEGIN BEGINRETURN (SELECT RETURN (SELECT 作者作者 FROM Book WHERE FROM Book WHERE 书名书名= b_name);= b_name); END$ END$ DELIMITER ; DELIMITER ;此存储函数给定书名，返回该书的作者。如要查询此存储函数给定书名，返回该书的作者。如要查询 计算机应用基础计算机应用基础 的作的作者，用者，用au

45、thor_book(author_book(计算机应用基础计算机应用基础)。41【例8.31】创建一个存储函数来删除创建一个存储函数来删除SellSell表中有但表中有但BookBook表中不存在的记录。表中不存在的记录。 CREATE FUNCTION del_Sell1(b_bh CHAR(20) CREATE FUNCTION del_Sell1(b_bh CHAR(20)RETURNS BOOLEANRETURNS BOOLEAN BEGIN BEGINDECLARE bh CHAR(20);DECLARE bh CHAR(20);SELECT SELECT 图书编号图书编号 INTO

46、 bh FROM Book WHERE INTO bh FROM Book WHERE 图书编号图书编号=b_bh;=b_bh;IF bh IS NULL THENIF bh IS NULL THENDELETE FROM Sell WHERE DELETE FROM Sell WHERE 图书编号图书编号=b_bh; =b_bh; RETURN TRUE;RETURN TRUE;ELSE ELSE RETURN FALSE;RETURN FALSE;END IF;END IF; END$ END$存储函数举例42课堂练习1 1、创建一个存储函数，返回员工的总人数、创建一个存储函数，返回员工的

47、总人数EM_NUM()EM_NUM()2 2、创建一个存储函数，判断员工是否在研发部工作，若是则、创建一个存储函数，判断员工是否在研发部工作，若是则返回其学历，若不是则返回返回其学历，若不是则返回“NONO”。43课堂练习参考答案1 1、创建一个存储函数，返回员工的总人数、创建一个存储函数，返回员工的总人数EM_NUM()EM_NUM()CREATE FUNCTION EM_NUM() RETURNS Integer RETURN ( SELECT count( * ) FROM Employees);2 2、创建一个存储函数，判断员工是否在研发部工作，若是则返回其、创建一个存储函数，判断员工

48、是否在研发部工作，若是则返回其学历，若不是则返回学历，若不是则返回“NONO”。CREATE FUNCTION XL(XH CHAR(6)RETURNS CHAR(10)BEGINDECLARE BM,ED CHAR(6);SELECT departmentID,education INTO BM,ED FROM employees WHERE employeeID =XH;IF bm=“4” THEN RETURN ED;ELSE RETURN “NO”;END IF;END44任务四 触发器创建触发器语法格式：创建触发器语法格式： CREATE TRIGGER 触发器名触发器名 触发时间触

49、发时间 触发事件触发事件 ON 表名表名 FOR EACH ROW 触发器动作触发器动作触发时间触发时间：触发器触发的时刻，有两个选项：触发器触发的时刻，有两个选项：AFTER和和BEFORE，以表示触发器，以表示触发器是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行几是在激活它的语句之前或之后触发。如果想要在激活触发器的语句执行之后执行几个或更多的改变，通常使用个或更多的改变，通常使用AFTER选项；如果想要验证新数据是否满足使用的限制，选项；如果想要验证新数据是否满足使用的限制，则使用则使用BEFORE选项。选项。触发事件触发事件：指明了激活触发程序的语句的类型。：指

50、明了激活触发程序的语句的类型。触发事件触发事件可以是下述值之一：可以是下述值之一：INSERT：将新行插入表时激活触发器。例如，通过：将新行插入表时激活触发器。例如，通过INSERT、LOAD DATA和和REPLACE语句。语句。UPDATE：更改某一行时激活触发器。例如，通过：更改某一行时激活触发器。例如，通过UPDATE语句。语句。DELETE：从表中删除某一行时激活触发器。例如，通过：从表中删除某一行时激活触发器。例如，通过DELETE和和REPLACE语语句。句。触发器动作触发器动作：包含触发器激活时将要执行的语句。如果要执行多个语句，可使用包含触发器激活时将要执行的语句。如果要执行

51、多个语句，可使用BEGIN . END复合语句结构。这样，就能使用存储过程中允许的相同语句。复合语句结构。这样，就能使用存储过程中允许的相同语句。45 创建触发器举例【例例8.348.34】 创建一个表创建一个表table1table1，其中只有一列，其中只有一列a a。在表上创建一个。在表上创建一个触发器，每次插入操作时，将用户变量触发器，每次插入操作时，将用户变量strstr的值设为的值设为“TRIGGER IS TRIGGER IS WORKINGWORKING”。CREATE TABLE table1(a INTEGER);CREATE TRIGGER table1_insert AF

52、TER INSERTON table1 FOR EACH ROWSET str= TRIGGER IS WORKING ;向向table1table1中插入一行数据：中插入一行数据： INSERT INTO table1 VALUES(10);查看查看strstr的值：的值： SELECT str;在在MySQLMySQL触发器中的触发器中的SQLSQL语句可以关联表中的任意列。但不能直接使用列的语句可以关联表中的任意列。但不能直接使用列的名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列

53、的旧名同时存在。因此必须用这样的语法来标志：添加了新的列名，而列的旧名同时存在。因此必须用这样的语法来标志：“NEW.column_nameNEW.column_name”或者或者“OLD.column_nameOLD.column_name”。NEW.column_nameNEW.column_name用来引用新用来引用新行的一列，行的一列，OLD.column_nameOLD.column_name用来引用更新或删除它之前的已有行的一列。用来引用更新或删除它之前的已有行的一列。对于对于INSERTINSERT语句，只有语句，只有NEWNEW是合法的；对于是合法的；对于DELETEDELET

54、E语句，只有语句，只有OLDOLD才合法；才合法；而而UPDATEUPDATE语句可以与语句可以与NEWNEW或或OLDOLD同时使用。同时使用。46创建触发器举例【例例8.358.35】 创建一个触发器，当删除表创建一个触发器，当删除表BookBook表中某图书的信息时，同时表中某图书的信息时，同时将将SellSell表中与该图书有关的数据全部删除。表中与该图书有关的数据全部删除。 DELIMITER $ DELIMITER $ CREATE TRIGGER book_del AFTER DELETE CREATE TRIGGER book_del AFTER DELETEON Book F

55、OR EACH ROWON Book FOR EACH ROW BEGIN BEGINDELETE FROM Sell WHERE DELETE FROM Sell WHERE 图书编号图书编号=OLD.=OLD.图书编号图书编号; ; END$ END$ DELIMITER ; DELIMITER ;现在验证一下触发器的功能：现在验证一下触发器的功能： DELETE FROM Book WHERE 图书编号图书编号=ISBN 7-5006-6625;使用使用SELECT语句查看语句查看Sell表中的情况：表中的情况： SELECT * FROM Sell WHERE 图书编号图书编号=ISB

56、N 7-5006-6625;这时可以发现，图书编号为这时可以发现，图书编号为ISBN 7-5006-6625在在Sell表中的所有表中的所有信息已经被删除了。信息已经被删除了。47课堂练习创建触发器，在创建触发器，在EmployeesEmployees表中当删除员工信息的同时将表中当删除员工信息的同时将SalarySalary表表中与该员工有关的数据全部删除。中与该员工有关的数据全部删除。48DELIMITER $CREATE TRIGGER EM_DELETE AFTER DELETEON Employees FOR EACH ROWBEGINDELETE FROM salary WHERE

57、 employeeID=OLD.employeeID;END$DELIMITER ;课堂练习参考答案课堂练习参考答案49创建触发器举例【例例8.36】 创建一个触发器，当修改创建一个触发器，当修改Sell表中订购册数时，如果修改后的订购表中订购册数时，如果修改后的订购册数小于册数小于5本，则触发器将该对应的折扣修改为本，则触发器将该对应的折扣修改为1,否则，折扣修改为否则，折扣修改为0.8。 DELIMITER $ CREATE TRIGGER sell_update BEFORE UPDATEON Sell FOR EACH ROW BEGIN IF NEW.订购册数订购册数10，Book表

58、中折扣在原折扣基表中折扣在原折扣基础上再打础上再打0.95折，否则折扣不变。折，否则折扣不变。 DELIMITER $ CREATE TRIGGER Sell_ins AFTER INSERTON Sell FOR EACH ROW BEGIN IF NEW.订购册数订购册数10 THEN UPDATE Book SET 折扣折扣=折扣折扣*.95 WHERE 图书编号图书编号=NEW.图书编号图书编号; END IF; END$ DELIMITER ;创建触发器举例51课堂练习创建触发器，实现当向创建触发器，实现当向EmployeesEmployees表插入一行数据时，对表插入一行数据时，对

59、SalarySalary表表也插入一行，也插入一行，EmployeeIDEmployeeID与与EmployeesEmployees表中的表中的EmployeeIDEmployeeID相同，相同，InComeInCome和和outcomeoutcome为为0.0.52DELIMITER $CREATE TRIGGER EM_INSERT AFTER INSERTON Employees FOR EACH ROWBEGININSERT INTO SALARY VALUES(NEW. EmployeeID,0,0);END$DELIMITER ;课堂练习参考答案课堂练习参考答案53触发器中调用存储

60、过程【例例8.38】 假设假设Bookstore数据库中有一个与数据库中有一个与Members表结构完全一样的表结构完全一样的表表member_backup，创建一个触发器，在，创建一个触发器，在Members表中添加数据的时候，表中添加数据的时候，调用存储过程，将调用存储过程，将member_backup表中的数据与表中的数据与Members表同步。表同步。 首先，定义存储过程：创建一个与首先，定义存储过程：创建一个与Members表结构完全一样的表表结构完全一样的表member_backup DELIMITER $ CREATE PROCEDURE data_copy() BEGINREP

61、LACE member_backup SELECT * FROM Members; END$ 接着创建触发器：调用存储过程接着创建触发器：调用存储过程data_copy() DELIMITER $ CREATE TRIGGER members_ins AFTER INSERT ON Members FOR EACH ROW CALL data_copy(); DELIMITER ;54触发器的删除和其他数据库对象一样，使用和其他数据库对象一样，使用DROPDROP语句即可将触发器从数据库中删除。语句即可将触发器从数据库中删除。语法格式：语法格式： DROP TRIGGER 触发器名触发器名【例

62、例8.398.39】 删除触发器删除触发器members_insmembers_ins。 DROP TRIGGER members_ins; DROP TRIGGER members_ins;创建触发器使用创建触发器使用CREATE TRIGGERCREATE TRIGGER语句语句 ，要查看数据库中有哪些触发，要查看数据库中有哪些触发器可以使用器可以使用SHOW TRIGGERSSHOW TRIGGERS命令。命令。 SHOW TRIGGERS55课堂练习创建触发器，实现若将创建触发器，实现若将EmployeesEmployees表中员工的工作年限增加表中员工的工作年限增加n n年，年，收入增加收入增加n*500n*500。56DELIMITER $CREATE TRIGGER EM_update AFTER updateON Employees FOR EACH ROWBEGINDECLARE years INT(1);set years=NEW.workyear-OLD.workyear; IF years=0 THEN UPDATE salary SET income=income+500*years where employeeID=NEW.employeeid;END IF;END$DELIMITER ;课堂练习参考答案课堂练习参考答案57