MySQL数据库原理及应用-项目8-以程序方式处理学生信息管理数据表课件.pptx

1、MySQLMySQL数据库原理及应用数据库原理及应用项目项目8 8 以程序方式处理学生信息管理以程序方式处理学生信息管理数据表数据表v学习目标：学习目标：了解SQL编程基础、游标、存储过程和函数、触发器及事务的作用掌握游标、存储过程和函数、触发器及事务的创建方法 掌握游标、存储过程和函数、触发器及事务的修改及删除方法任务任务8-1 掌握掌握SQL编程基础编程基础v【任务分析】【任务分析】设计人员要编写存储过程和函数、触发器及事务，首先要掌设计人员要编写存储过程和函数、触发器及事务，首先要掌握握SQL语言的语法规范及语言基础。语言的语法规范及语言基础。v【课堂课堂任务】任务】v 本次任务要求熟悉

2、本次任务要求熟悉SQL语言。语言。v SQL语言基础语言基础v SQL的流程控制的流程控制 v 常用函数常用函数v 游标的基本操作游标的基本操作任务任务8-1 掌握掌握SQL编程基础编程基础（一）SQL基础尽管MySQL有各种使用便捷的图形化用户界面，但各种功能的实现基础是SQL语言，只有SQL语言可以直接和数据库进行交互。SQL语言是一系列操作数据库及数据库对象的命令语句，因此了解基本语法和流程语句的构成是必须的。SQL语言基础主要包括什么主要包括常量和变量、表达式、运算符、控制语句等。任务任务8-1 掌握掌握SQL编程基础编程基础（一）SQL基础1、常量与变量 （1）常量。常量也称为文字值

3、或标量值，是指程序运行中值始终不改的量。常量类型常量类型常量表示说明常量表示说明字符串常量字符串常量包括在单引号（包括在单引号（）或双引号（）或双引号（）中，由字母（）中，由字母（az、AZ）、）、数字字符（数字字符（09）以及特殊字符（如感叹号（）以及特殊字符（如感叹号（!）、）、at符（符（）和井字）和井字号（号（#）组成。）组成。示例：示例：China、Output X is:、Nhello（Unicode字符串字符串常量，只能用单引号括起字符串）常量，只能用单引号括起字符串）十 进 制 整 型十 进 制 整 型常量常量使用不带小数点的十进制数据表示。使用不带小数点的十进制数据表示。示例

4、：示例：1234、654、+2008、-123十 六 进 制 整十 六 进 制 整型常量型常量使用前缀使用前缀0 x后跟十六进制数字串表示。后跟十六进制数字串表示。示例：示例：0 x1F00、0 xEEC、0X19日期常量日期常量使用单引号（使用单引号（）将日期时间字符串括起来。）将日期时间字符串括起来。MySQL是按年是按年-月月-日日的顺序表示日期的。中间的间隔符可以用的顺序表示日期的。中间的间隔符可以用“-”，也可以使用如，也可以使用如“”、“/”、“”或或“%”等特殊符号。等特殊符号。示例：示例：2009-01-03、2008/01/09、20101210实型常量实型常量有定点表示和浮

5、点表示两种方式。有定点表示和浮点表示两种方式。示例：示例：897.1、-123.03、19E24、-83E2位字段值位字段值使用使用bvalue符号写位字段值。符号写位字段值。value是一个用是一个用0和和1写成的二进制写成的二进制值。直接显示值。直接显示bvalue的值可能是一系列特殊的符号。例如，的值可能是一系列特殊的符号。例如，b0显示显示为空白，为空白，b1显示为一个笑脸图标。显示为一个笑脸图标。示例：示例：SELECT BIN(b111101+0),OCT(b111101+0)布尔常量布尔常量布尔常量只包含两个可能的值：布尔常量只包含两个可能的值：TRUE和和FALSE。FALSE

6、的数字值的数字值为为“0”，TRUE的数字值为的数字值为“1”。示例：获取示例：获取TRUE和和FALSE的值：的值：SELECT TRUE,FALSENULL值值NULL值可适用于各种列类型，它通常用来表示值可适用于各种列类型，它通常用来表示“没有值没有值”、“无数无数据据”等意义，并且不同于数字类型的等意义，并且不同于数字类型的“0”或字符串类型的空字符串或字符串类型的空字符串任务任务8-1 掌握掌握SQL编程基础编程基础任务任务8-1 掌握掌握SQL编程基础编程基础（一）SQL基础1、常量与变量 （2）变量。变量就是在程序执行过程中，其值是可以改变的量。可以利用变量存储程序执行过程中涉及

7、的数据，如计算结果、用户输入的字符串以及对象的状态等。变量由变量名和变量值构成，其类型与常量一样。变量名不能与命令和函数名相同，这里的变量和在数学中所遇到的变量的概念基本上是一样的，可以随时改变它所对应的数值。在MySQL系统中，存在3种类型的变量：第1种是系统变量全局（global）变量会话（session）变量第2种是用户变量第3种是局部变量任务任务8-1 掌握掌握SQL编程基础编程基础（一）SQL基础1、常量与变量 （2）变量。全局变量和会话变量。查看全局变量的值例：查看当前的版本信息SELECT version AS 当前MySQL,CURRENT_DATE;例：查看全局变量SHOW

8、GLOBAL variables;SHOW variables WHERE variable_name LIKE collation%;任务任务8-1 掌握掌握SQL编程基础编程基础（一）SQL基础1、常量与变量 （2）变量。全局变量和会话变量。设置全局变量的值【例8.1】将全局系统变量sort_buffer_size的值改为25000，执行命令如下。SET global.sort_buffer_size=25000;【例8.2】对于当前会话，把系统变量SQL_SELECT_LIMIT的值设置为100。这个变量决定了SELECT语句的结果集中的最大行数。SET SESSION.SQL_SELE

9、CT_LIMIT=100;SELECT LOCAL.SQL_SELECT_LIMIT;任务任务8-1 掌握掌握SQL编程基础编程基础 用户变量。语法结构：SET 变量名:=表达式;例如：创建用户变量name，并赋值为“王小强”。（1）SET name=王小强;（2）SET name:=王小强;（3）SELECT name:=王小强;【例8.3】创建用户变量user1并赋值为1，user2赋值为2，user3赋值为3。SET user1=1,user2=2,user3=3;或SELECT user1:=1,user2:=2,user3:=3;【例8.4】创建用户变量user4，它的值为user3

10、的值加1。SET user4=user3+1;或SELECT user4:=user3+1;任务任务8-1 掌握掌握SQL编程基础编程基础【例8.5】查询【例8.3】和【例8.4】中创建的变量user1、user2、user3和user4的值。SELECT user1,user2,user3,user4;+-+-+-+-+|user1|user2|user3|user4|+-+-+-+-+|1|2|3|4|+-+-+-+-+1 row in set任务任务8-1 掌握掌握SQL编程基础编程基础 用户变量。【例8.6】使用查询结果给变量赋值。USE gradem；SET student=(SEL

11、ECT sname FROM student WHERE sno=2007010120);【例8.7】查询表student中名字等于例8.6中student值的学生信息。SELECT sno,sname,sbirthday FROM student WHERE sname=student;【例8.8】利用SELECT语句将表中数据赋值给变量。select name:=password from suser limit 0,1;任务任务8-1 掌握掌握SQL编程基础编程基础 用户变量。【例8.9】查看“gradem”数据库中的学生信息，而条件只是查看student表中“系别”为“软件工程系”的学

12、生信息。USE gradem;SET 系别=软件工程系;SELECT sno,sname,saddress FROM student WHERE sdept=系别;注意：只要关闭查询窗口，用户变量就失效任务任务8-1 掌握掌握SQL编程基础编程基础 局部变量。例如，定义局部变量myvar，数据类型为INT，默认值为10，代码如下。DECLARE myvar int DEFAULT 10;下面给局部变量myvar赋值为100，代码如下。SET myvar=100;任务任务8-1 掌握掌握SQL编程基础编程基础2表达式在SQL语言中，表达式就是常量、变量、列名、复杂计算、运算符和函数的组合。一个表

13、达式通常都有返回值。与常量和变量一样，表达式的值也具有某种数据类型。根据表达式的值的类型，表达式可分为字符型表达式、数值型表达式和日期型表达式。表达式一般用在SELECT及SELECT语句的WHERE子句中。任务任务8-1 掌握掌握SQL编程基础编程基础例如，如下所示为一个使用表达式的SELECT查询语句。SELECT A.sno,AVG(degree)AS 平均成绩,CONCAT(sname,SPACE(6),ssex,SPACE(4),classno,班,SPACE(4),left(classno,4),年级)AS 考生信息FROM sc A INNER JOIN student B ON

14、 A.sno=B.snoGROUP BY A.sno,sname,ssex,classnoORDER BY 平均成绩 DESC;+-+-+-+|sno|平均成绩|考生信息|+-+-+-+|2007030436|94.5000|徐小栋 男 20070304班 2007年级|2007030409|94.0000|刘明海 男 20070304班 2007年级|2007030408|93.0000|刘众林 男 20070304班 2007年级|2007030420|92.7500|李鹏飞 男 20070304班 2007年级|2007010106|92.6667|孙晋梅 女 20070101班 200

15、7年级|任务任务8-1 掌握掌握SQL编程基础编程基础（二）SQL的流程控制1、结构化程序设计语言的基本结构是（1）顺序结构（2）条件分支结构（3）循环结构顺序结构是一种自然结构，条件分支结构和循环结构需要根据程序的执行情况对程序的执行顺序进行调整和控制。在SQL语言中，流程控制语句就是用来控制程序执行流程的语句，也称流控制语句或控制流语句。在MySQL中，这些流程控制语句和局部变量只能在存储过程或函数、触发器或事务的定义中。任务任务8-1 掌握掌握SQL编程基础编程基础（二）SQL的流程控制（1）BEGINEND语句块。关键字BEGIN定义SQL语句的起始位置，END定义同一块SQL语句的结

16、尾。它的语法格式如下。BEGINsql_statement|statement_block;END;sql_statement是使用语句块定义的任何有效的SQL语句；statement_block是使用语句块定义的任何有效SQL语句块。任务任务8-1 掌握掌握SQL编程基础编程基础（二）SQL的流程控制（2）IFELSE条件语句。用于指定SQL语句的执行条件。如果条件为真，则执行条件表达式后面的SQL语句。当条件为假时，可以用ELSE关键字指定要执行的SQL语句。它的语法格式如下。IF search_condition THEN statement_listELSEIF search_cond

17、ition THEN statement_listELSE statement_listEND IF;其中，search_condition是返回true或false的逻辑表达式。如果逻辑表达式中含有SELECT语句，必须用圆括号将SELECT语句括起来任务任务8-1 掌握掌握SQL编程基础编程基础（二）SQL的流程控制【例8.10】使用IFELSE条件语句查询计算机系的办公室位置。如果查询结果为空，则显示“办公地点不详”，否则显示其办公地点。分析：（1）查询出计算机系的办公位置 select office FROM department WHERE deptname=计算机工程系;（2）根据

18、结果是否为空，分别处理 IF()THEN BEGIN ELSE END IF;任务任务8-1 掌握掌握SQL编程基础编程基础（二）SQL的流程控制DECLARE sofficename char(20);select sofficename:=office FROM department WHERE deptname=计算机工程系;IF(sofficename IS NULL)THEN BEGIN SELECT 办公地点不详 AS 办公地点;SELECT*FROM department WHERE deptname=计算机工程系;END;ELSE SELECT office FROM depa

19、rtment WHERE deptname=计算机工程系;END IF;任务任务8-1 掌握掌握SQL编程基础编程基础（二）SQL的流程控制（3）CASE分支语句。CASE关键字可根据表达式的真假来确定是否返回某个值，可以允许使用表达式的任何位置使用这一关键字。使用CASE语句可以进行多个分支的选择，CASE语句具有如下两种格式。简单格式：将某个表达式与一组简单表达式进行比较以确定结果。简单CASE格式的语法如下。CASEWHEN when_expression THEN result_expression;nELSE else_result_expression;END CASE;任务任务8

20、-1 掌握掌握SQL编程基础编程基础 搜索格式：计算一组布尔表达式以确定结果。搜索CASE格式的语法如下。CASEWHEN Boolean_expression THEN result_expression;nELSE else_result_expression;END CASE;语句中参数的含义与CASE格式的参数含义类似。任务任务8-1 掌握掌握SQL编程基础编程基础declare dj int default 0;SELECT count(*)into dj FROM sc WHERE degree=100 THEN SELECT 不及格门数较多 as 档次;WHEN dj=50 AN

21、D dj=1 AND dj 50 THEN SELECT 不及格门数较少 as 档次;ELSE SELECT 没有不及格的 as 档次;END CASE;任务任务8-1 掌握掌握SQL编程基础编程基础（4）循环语句。WHILEEND WHILE语句。WHILE语句是设置重复执行SQL语句或语句块的条件。当指定的条件为真时，重复执行循环语句。可以在循环体内设置LEAVE和ITERATE语句，以便控制循环语句的执行过程，其语法格式如下。begin_label:WHILE Boolean_expression DO sql_statement|statement_block;LEAVE begin_

22、label;sql_statement|statement_block;ITERATE begin_label;sql_statement|statement_block;END WHILE;任务任务8-1 掌握掌握SQL编程基础编程基础例如，使用WHILE语句求1100之和。SET i=1,sum=0;WHILE i100END REPEAT;SELECT sum;任务任务8-1 掌握掌握SQL编程基础编程基础 LOOPEND LOOP语句。LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句才能停止循环。其语法格式如

23、下。begin_label:LOOP sql_statement|statement_block;LEAVE begin_label;sql_statement|statement_block;ITERATE begin_label;sql_statement|statement_block;END LOOP;任务任务8-1 掌握掌握SQL编程基础编程基础例如，使用LOOP语句求1100之和。SET i=1,sum=0;add_sum:LOOP BEGIN SET sum=sum+i;SET i=i+1;END;IF i100 THEN LEAVE add_sum;END LOOP;SELEC

24、T sum;任务任务8-1 掌握掌握SQL编程基础编程基础2、条件和处理程序的定义特定条件需要特定处理。这些条件可能涉及错误以及子程序中的一般流程控制。定义条件和处理程序是事先定义程序执行过程中可能遇到的问题，并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。任务任务8-1 掌握掌握SQL编程基础编程基础2、条件和处理程序的定义【例8.12】定义条件和处理程序，具体的执行代码如下。-首先建立测试表testmysqlCREATE TABLE

25、 test(t1 int,primary key(t1);Query OK,0 rows affectedmysqlDELIMITER/#重新定义命令结束符为/mysqlCREATE PROCEDURE handlertest()-BEGIN -DECLARE CONTINUE handler FOR SQLSTATE 23000 SET x1=1;-SET x=1;-INSERT INTO test VALUES(1);-SET x=2;-INSERT INTO test VALUES(1);-SET x=3;-SELECT x,x1;-END;-/任务任务8-1 掌握掌握SQL编程基础编程

26、基础3、注释注释是程序代码中不被执行的文本字符串，用于对代码进行说明或进行诊断的部分语句。（1）井字符（#）：从井字符到行尾都是注释内容。（2）双连线字符（-）：从双连线字符到行尾都是注释内容。注意，双连线后一定要加一个空格。（3）正斜杠星号字符（/*/）：开始注释对（/*）和结束注释对（*/）之间的所有内容均视为注释。任务任务8-1 掌握掌握SQL编程基础编程基础例如，下面的程序代码中包含注释符号。USE gradem;-打开数据库#查看学生的所有信息SELECT*FROM student;/*查看软件系的所有学生的学号、姓名、系名及性别附加条件是女生 */SELECT sno,sname,

27、sdeptname,ssex FROM student a,department b WHERE a.deptno=b.deptno AND ssex=女;任务任务8-1 掌握掌握SQL编程基础编程基础（三）MySQL常用函数MySQL提供了大量丰富的系统函数，它们功能强大、方便易用。使用这些函数，可以极大提高用户对数据库的管理效率，更加灵活地满足不同用户的需求。从功能上可以分为以下几类函数：字符串函数、数学函数、日期和时间函数、条件判断函数、系统信息函数和加密函数等。任务任务8-1 掌握掌握SQL编程基础编程基础（四）游标游标（Cursor）是类似于C语言指针一样的结构，在MySQL中它是一

28、种数据访问机制，允许用户访问单独的数据行，而不是对整个行集进行操作。在MySQL中，游标主要包括游标结果集和游标位置两部分，游标结果集是由定义游标的SELECT语句返回行的集合，游标位置则是指向这个结果集中的某一行的指针。在使用游标之前首先要声明游标，定义SQL服务器游标的属性，例如游标的滚动行为和用于生成游标所操作的结果集的查询。声明游标的语法格式如下。DECLARE cursor_name CURSORFOR select_statement;任务任务8-1 掌握掌握SQL编程基础编程基础（四）游标例如，在gradem数据库中为了teacher表创建一个普通的游标，定义名称为T_curso

29、r，可用语句如下所示。DECLARE T_cursor CURSORFOR SELECT Tno,Tname FROM teacher;在声明游标以后，就可对游标进行操作。主要包括打开游标、检索游标、关闭游标和释放游标。任务任务8-1 掌握掌握SQL编程基础编程基础1打开游标使用游标之前必须首先打开游标，打开游标的语法如下所示。OPEN cursor_name;例如，打开前面创建的T_cursor游标，使用如下语句。OPEN T_cursor;任务任务8-1 掌握掌握SQL编程基础编程基础 2检索游标在打开游标以后，就可以打开游标提取数据。FETCH语句的功能是获取游标当前指针的记录，并传给指

30、定变量列表，注意变量数必须与MySQL游标返回的字段数一致。要获得多行数据，需要使用循环语句去执行FETCH，其语法如下。FETCH cursor_name INTO var1,var2,;MySQL的游标是向前只读的，也就是说，只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。任务任务8-1 掌握掌握SQL编程基础编程基础首先，FETCH离不开循环语句。一般使用Loop和while比较清楚，而且代码简单。这里使用Loop为例，代码如下。fetchLoop:Loop FETCH T_Cursor INTO v_tno,v_tname;end Loop;上述循环是死循环，没

31、有退出的条件。与SQL和ORACLE不同，MySQL是通过一个Error handler的声明来进行判断的。DECLARE CONTINUE handler FOR NOT FOUND;任务任务8-1 掌握掌握SQL编程基础编程基础下面的代码就是使用FETCH语句来检索游标中可用的数据。CREATE PROCEDURE proccursor()BEGINDECLARE done int DEFAULT 0;DECLARE v_tno varchar(4)DEFAULT;DECLARE v_tname varchar(8)DEFAULT;DECLARE T_cursor CURSOR FOR S

32、ELECT Tno,Tname FROM teacher;#定义游标DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;#定义处理程序SET done=0;OPEN T_Cursor;#打开游标fetch_Loop:LOOPFETCH T_Cursor INTO v_tno,v_tname;#检索游标IF done=1 THEN LEAVE fetch_Loop;ELSE SELECT v_tno,v_tname;END IF;END LOOP fetch_Loop;END上述语句中的变量done保存的就是FETCH操作的结束信息。如果其值为零，

33、则表示有记录检索成功；如果值不为零，则FETCH语句由于某种原因而操作失败。任务任务8-1 掌握掌握SQL编程基础编程基础3关闭游标打开游标以后，MySQL服务器会专门为游标开辟一定的内存空间，以存放游标操作的数据结果集，同时游标的使用也会根据具体情况对某些数据进行封锁。所以在不使用游标的时候，一定要关闭游标，以通知服务器释放游标所占用的资源。在一个游标关闭后，如果没有重新打开，则不能使用它。但是，使用声明过的游标不需要再次声明。如果不明确关闭游标，MySQL将会在到达END语句时自动关闭它。关闭游标的具体语法如下所示。CLOSE cursor_name;在检索游标T_cursor后可用如下语

34、句来关闭它。CLOSE T_cursor;任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数v【任务分析】【任务分析】为了提高访问数据的速度与效率，设计人为了提高访问数据的速度与效率，设计人员可以利用存储过程和函数管理数据库。员可以利用存储过程和函数管理数据库。v【课堂课堂任务】任务】v 本次任务要求掌握存储过程和函数的概念及本次任务要求掌握存储过程和函数的概念及应用。应用。v 存储过程和函数的概念存储过程和函数的概念v 存储过程和函数的创建及调用存储过程和函数的创建及调用v 存储过程和函数中参数的使用存储过程和函数中参数的使用v 存储过程和函数查看及删除存储过程和函数查

35、看及删除任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数v（一）存储过程和存储函数概述（一）存储过程和存储函数概述1什么是存储过程和存储函数 存储过程（Stored Procedure）和函数（Stored Funtion）是在数据库中定义一些完成特定功能的SQL语句集合，经编译后存储在数据库中。存储过程和函数中可包含流程控制语句及各种SQL语句。它们可以接受参数、输出参数、返回单个或者多个结果。任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数v（一）存储过程和存储函数概述（一）存储过程和存储函数概述2存储过程的优点在MySQL中使用存储过程，而

36、不是用存储在客户端计算机本地的SQL程序有以下几点优点。（1）存储过程增强了SQL语言的功能和灵活性。（2）存储过程允许标准组件是编程。（3）存储过程能实现较快的执行速度。（4）存储过程能够减少网络流量。（5）存储过程可被作为一种安全机制来充分利用。任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数（二）创建存储过程在MySQL中，创建存储过程和函数必须具有CREATE ROUTINE权限，并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。创建存储过程有两种实现方式，具体介绍如下：任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函

37、数（二）创建存储过程（1）利用CREATE PROCEDURE语句创建。用户可以使用CREATE PROCEDURE语句创建存储过程，其基本语法如下。CREATE PROCEDURE procedure_name(proc_parameter,)characteristic,Routine_body任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数【例8.13】创建一个存储过程，从数据库gradem的student表中检索出所有籍贯为“青岛”的学生的学号、姓名、班级号及家庭地址等信息。具体语句如下。mysqlUSE gradem;mysqlDELIMITER/mysqlC

38、REATE PROCEDURE proc_stud()-READS SQL DATA -BEGIN -SELECT sno,sname,classno,saddress FROM student -WHERE saddress LIKE%青岛%ORDER BY sno;-END/Query OK,0 rows affectedmysqlDELIMITER;任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数【例8.14】创建一个名为num_sc的存储过程，统计某位同学的考试门数，代码如下。mysqlDELIMITER/mysqlCREATE PROCEDURE num_sc

39、(IN tmp_sno char(10),OUT count_num INT)-READS SQL DATA -BEGIN -SELECT COUNT(*)INTO count_num FROM sc -WHERE sno=tmp_sno;-END/Query OK,0 rows affectedmysqlDELIMITER;任务任务8-2 创建与使用存储过程和函数创建与使用存储过程和函数（2）利用Navicat图形工具创建。利用Navicat图形工具创建存储过程方便、简单，且易操作。任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数（三）调用存储过程在MySQL系统中，

40、因为存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要打开相应的数据库或指定数据库名称。存储函数的调用与MySQL内部函数的调用方式相同。在Navicat工具中，调用存储过程的方法非常简单也可以利用CALL命令调用存储过程，其语法格式如下。CALL dbname.sp_name(parameter,);任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数【例8.15】调用例8.14所创建的存储过程num_sc，代码如下。mysqlCALL num_sc(2007030101,num);#调用存储过程Query OK,0 rows affectedmysqlSELE

41、CT num;#查询返回结果+-+|num|+-+|4|+-+1 row in set由上面代码看出，使用CALL语句来调用存储过程，使用SELECT语句来查询存储过程的输出结果值。任务任务8-2 创建与使用存储过程和函数创建与使用存储过程和函数（四）创建存储函数（1）利用CREATE FUNCTION语句创建。在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。两者唯一的区别在于矛盾，前面都是相同，这里又讲区别，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。在MySQL中，创建存储函数的基

42、本语法如下。CREATE FUNCTION func_name(func_parameter,)RETURNS type characteristic,Routine_body 任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数【例8.16】创建一个名为func_name的存储函数返回某班级的辅导员姓名，代码如下。mysql DELIMITER&mysql CREATE FUNCTION func_name(class_no varchar(8)-RETURNS varchar(8)-BEGIN -RETURN (SELECT header FROM class -WHE

43、RE classno=class_no);-END&Query OK,0 rows affectedmysql DELIMITER;任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数 利用Navicat图形工具创建。利用Navicat图形工具创建存储函数的方法与存储过程相似任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数（五）调用存储函数在Navicat工具中，调用存储函数的方法也非常简单，先展开【mysql】|【gradem】，单击工具栏上的【函数】按钮，单击窗格上方的【】按钮，或用鼠标右键单击相应函数，在弹出的快捷菜单中选择【运行函数】命令。也

44、可以双击要执行的函数，在编辑窗口中单击【】按钮。根据系统的提示，输入相应的参数即可。也可以利用SELECT语句调用存储函数，其语法格式如下。SELECT dbname.func_name(parameter,);其中，dbname是数据库名称，默认为当前数据库。func_name是存储函数的名称，parameter是指存储函数的参数。任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数【例8.17】调用存储函数func_teacher，代码如下。mysqlSELECT func_teacher(李新);#调用存储函数+-+|func_teacher(李新)|+-+|计算机工

45、程系|+-+1 row in set任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数（六）查看存储过程和存储函数查看存储过程的状态信息，可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可以通过查询information_schema数据库下的Routines表来查看存储过程和函数的信息。（1）利用SHOW STATUS语句查看。在MySQL中，可以通过SHOW STATUS语句查看存储过程和函数的状态。其基本语法形式如下。SHOW PROCEDURE|FUNCTION STATUS LIKE pattern;其中，PROCEDURE参数表示查询存

46、储过程，FUNCTION参数表示查询存储函数。LIKE pattern参数用来匹配存储过程或函数的名称。【例8.18】查询名为num_sc存储过程的状态，代码如下。mysqlSHOW PROCEDURE STATUS LIKE num_sc;任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数（2）利用SHOW CREATE语句查看。除了SHOW STATUS之外，MySQL还可以通过SHOW CREATE语句查看存储过程和函数的状态。其基本语法形式如下。SHOW CREATE PROCEDURE|FUNCTION sp_name;其中，PROCEDURE参数表示查询存储过

47、程，FUNCTION参数表示查询存储函数。sp_name表示存储过程或函数的名称。例如，查询名为func_teacher存储函数的状态，代码如下。mysqlSHOW CREATE FUNCTION func_teacher;任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数（3）从information_schema.Routines表中查看存储过程和函数的信息。存储过程和函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息，其基本语法形式如下。SELECT*FROM information_

48、schema.Routines WHERE ROUTINE_NAME=sp_name;其中，ROUTINE_NAME字段中存储的是存储过程和函数的名称，sp_name参数表示存储过程或函数的名称。【例8.19】从Routines表中查询名为num_sc的存储过程的信息，代码执行如下。mysql SELECT*FROM information_schema.Routines WHERE ROUTINE_NAME=num_sc G 任务任务8-2 创建与使用存储过程和存储函数创建与使用存储过程和存储函数（七）删除存储过程和存储函数可使用DROP PROCEDURE或者DROP FUNCTION语句

49、从当前的数据库中删除用户定义的存储过程和函数。删除存储过程和函数的基本语法格式如下。DROP PROCEDURE|FUNCTIONIF EXISTSsp_name;任务任务8-3 创建和使用触发器创建和使用触发器v【任务分析】【任务分析】为了确保数据完整性，设计人员可以用触发器实现为了确保数据完整性，设计人员可以用触发器实现更加复杂的业务规则。更加复杂的业务规则。v【课堂课堂任务】任务】v 本次任务要求掌握触发器的概念及应用。本次任务要求掌握触发器的概念及应用。v 触发器的概念触发器的概念v 创建创建触发器触发器v 查看触发器查看触发器v 删除触发器删除触发器任务任务8-3 创建和使用创建和使

50、用触发器触发器（一）触发器概述 触发器（Trigger）是一种特殊的存储过程，它与表紧密相连，可以是表定义的一部分。当预定义的事件（如用户修改指定表或者视图中的数据时）发生时，触发器将会自动执行。触发器基于一个表创建，但是可以针对多个表进行操作。所以触发器可以用来对表实施复杂的完整性约束，当触发器所保存的数据发生改变时，触发器被自动激活，从而防止对数据进行不正确的修改。任务任务8-3 创建和使用创建和使用触发器触发器触发器的优点如下所述触发器的优点如下所述：（1）触发器自动执行，在表的数据做了任何修改（比如手工输入或者使用程序采集的操作）之后立即激活。（2）触发器可以通过数据库中的相关表进行层