《第10章存储过程电子课件 MySQL数据库管理与应用.pptx》由会员分享，可在线阅读，更多相关《第10章存储过程电子课件 MySQL数据库管理与应用.pptx（50页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、第10章存储过程电子课件 MySQL数据库管理与应用第10章 存储过程主要内容10.1 存储过程概述10.2 创建存储过程10.3 调用存储过程 10.4 查看存储过程10.5 修改存储过程10.6 删除存储过程10.7 常量和变量10.8 流程控制语句10.9 错误处理10.10 游标10.11 本章小结10.1 存储过程概述将多条SQL命令组合在一起形成一个程序一次性执行，我们称之为存储程序。存储程序包括存储过程和存储函数，它们是在数据库中定义的一些SQL语句的集合，可以直接调用这些存储过程和存储函数来执行已经定义好的SQL语句。存储过程是存放在数据库中的一段程序，是数据库的对象之一。它由
2、声明式的SQL语句（如DDL语句、DML语句）和过程式的SQL语句（如选择语句、循环语句）组成。10.1 存储过程概述存储过程通常有如下优点：封装性：在程序中多次调用，而不必重新编写该存储过程的SQL语句，并且可以随时对存储过程的特性进行修改，而不会影响到调用该存储过程的应用程序源代码。增强SQL语句的功能和灵活性：可以用流程控制语句来完成一些复杂的判断或计算，有很强的灵活性。减少网络传输：存储过程是在服务器中存储和运行的，当客户端调用存储过程时，只需要从客户端或应用程序传递给数据库必要的参数即可，网络中传输的只是相应的调用语句。提高数据的安全性：对一些重要的表我们可以禁止客户端访问，只把相关
3、存储过程的创建权限或者调用权限赋予必要的用户，从而防止了对表的误操作。10.2 创建存储过程10.2.1 使用命令创建存储过程语法格式：CREATE PROCEDURE IF NOT EXISTS sp_name(proc_parameter,)characteristic routine_body10.2 创建存储过程10.2.1 使用命令创建存储过程其中：proc_parameter：表示存储过程的参数列表，它由三部分组成，形式为IN|OUT|INOUT param_name type。characteristic：指定存储过程的特性，有以下5个取值。LANGUAGE SQLNOT DET
4、ERMINISTICCONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA SQL SECURITYDEFINER|INVOKER COMMENT string10.2 创建存储过程10.2.1 使用命令创建存储过程在定义存储过程之前，应该先使用DELIMITER关键字更改结束标记。结束标记可以是一个字符，也可以是多个字符，如“$”、“#”等。程序执行完后，考虑到用户使用SQL命令的习惯性，一般要将结束标记再恢复成默认的分号。DELIMITER关键字和后面的结束标记中间一定要有空格。10.2 创建存储过程10.2.1 使用命令创建存储过程【例1
5、0.2】创建存储过程，可以根据学号查询学生的相关信息。在MySQL命令行客户端输入命令：DELIMITER/CREATE PROCEDURE select_student (IN p_sno CHAR(11)BEGIN SELECT*FROM student WHERE sno=p_sno;END/DELIMITER;10.2 创建存储过程10.2.1 使用命令创建存储过程【例10.3】创建一个存储过程，根据学号查询并输出学生的姓名和专业号。在MySQL命令行客户端输入命令：DELIMITER/CREATE PROCEDURE select_stu (IN p_sno CHAR(11),OUT
6、 p_sname VARCHAR(10),OUT p_mno CHAR(4)BEGIN SELECT sname,mno INTO p_sname,p_mno FROM student WHERE sno=p_sno;END/DELIMITER;10.2 创建存储过程10.2.2 使用图形化工具创建存储过程打开Workbench工具，连接到MySQL服务器。选中“Schemas”，打开“jwgl”数据库，找到“Stored Procedures”选项，右键单击“Create Stored Procedure”，或者单击 图标。10.2 创建存储过程10.2.2 使用图形化工具创建存储过程在界面
7、中填写相应的存储过程代码（注意：在该界面中不需要输入DELIMITER语句），然后单击“Apply”，此时出现查看存储过程的SQL脚本的界面。确认无误后单击右下角的“Apply”，在出现的界面中单击“Finish”，此时存储过程创建成功。10.3 调用存储过程10.3.1 使用命令调用存储过程 语法格式：CALL sp_name(proc_parameter,)【例10.4】调用存储过程select_student和select_stu。在MySQL命令行客户端输入命令：CALL select_student(20190101001);CALL select_stu(20190101001,s
8、name,mno);SELECT sname,mno;10.3 调用存储过程10.3.2 使用图形化工具调用存储过程 （1）打开Workbench工具，连接到MySQL服务器。（2）选中“Schemas”标签，打开“jwgl”数据库，在“Stored Procedures”下找到想要调用的存储过程名称，如select_stu，单击其右侧的 图标，此时出现调用存储过程的界面，如图所示。（3）在文本框中依次输入参数值，如20190101001、sname、mno，然后单击“Execute”，此时出现调用结果界面。10.4 查看存储过程10.4.1 SHOW STATUS语句可以使用SHOW STA
9、TUS语句查看存储过程的特征。语法格式：SHOW PROCEDURE|FUNCTION STATUS LIKE pattern【例10.5】查看名字以“select”开头的存储过程的特征。在MySQL命令行客户端输入命令：SHOW PROCEDURE STATUS LIKE select%G10.4 查看存储过程10.4.2 SHOW CREATE语句可以使用SHOW CREATE语句查看存储过程或者存储函数的状态和当前的定义语句。语法格式：SHOW CREATE PROCEDURE sp_name【例【例10.6】查看存储过程select_stu的定义。在MySQL命令行客户端输入命令：SH
10、OW CREATE PROCEDURE select_stu G10.4 查看存储过程10.4.3 查询routines表可以通过查询routines表中的记录来获取存储过程和函数的信息。【例【例10.7】从routines表中查询存储过程select_student的信息。在MySQL命令行客户端输入命令：SELECT*FROM information_schema.routines WHERE routine_name=select_student AND routine_type=PROCEDURE G10.4 查看存储过程10.4.4 使用图形化工具查看存储过程（1）打开Workben
11、ch工具，连接到MySQL服务器。（2）选中“Schemas”标签下的“jwgl”数据库，然后单击工具栏的 图标，或者单击“jwgl”数据库后面的 图标。（3）单击“Stored Procedures”标签，进入如图所示界面，可以看到操作jwgl数据库的所有存储过程的特征信息。10.5 修改存储过程10.5.1 使用命令修改存储过程使用ALTER PROCEDURE语句可以修改存储过程的特性，但不能修改存储过程的参数和过程体定义语句。语法格式：ALTER PROCEDURE sp_name characteristic10.6 删除存储过程10.6.1 使用命令删除存储过程语法格式：DROP
12、PROCEDURE IF EXISTS sp_name【例10.9】删除存储过程select_stu。在MySQL命令行客户端输入命令：DROP PROCEDURE select_stu;10.6 删除存储过程10.6.2 使用图形化工具删除存储过程（1）打开Workbench工具，连接到MySQL服务器。（2）打开“Stored Procedures”可以看到操作当前数据库的所有存储过程，选中你想要删除的存储过程名称，如：select_student，右键单击“Drop Stored Procedure”选项，弹出如图所示界面。（3）在该弹出的对话框中，“Review SQL”选项可以看到删
13、除该存储过程的SQL代码，然后再单击“Execute”即可完成删除操作；或者在所示界面中单击“Drop Now”选项，即可直接删除该存储过程。10.7 常量和变量10.7.1 常量字符串常量：用单引号或者双引号标注起来的字符序列，荐使用单引号作为字符串的定界符。数值常量：包括整数常量和浮点数常量。日期时间常量：本质上是一个符合特殊格式的字符串常量，是指用单引号将表示日期时间的字符串标注起来构成的。布尔常量：包含TRUE和FALSE两个值。二进制常量：前缀“b”后面跟一个二进制字符串，如b111101。十六进制常量：前缀是“X”或者“0 x”，后面跟一个十六进制字符串，如 0 x4D795351
14、4C。NULL值：表示“没有值”、“值不确定”等含义。10.7 常量和变量10.7.2 变量会话变量：以“”作为起始字符，不区分大小写，在整个会话过程中都有效，表现形式如name、b等。局部变量：使用关键字DECLARE声明，后面跟变量名称和数据类型，也可以用关键字DEFAULT为变量指定默认值，如 DECLARE id INT;系统变量：以“”开头，根据其作用范围可分为全局级(GLOBAL)和会话级(SESSION)，前者是对整个MySQL服务器生效，后者只对当前连接的客户端会话生效。10.7 常量和变量10.7.2 变量给用户变量赋值（1）用SET语句给会话变量或局部变量赋值 语法格式如下
15、：SET var_name=exper,var_name=exper【例10.10】用SET语句给会话变量赋值并查看其值。在MySQL命令行客户端输入命令：SET name=努力学习;SET a=(SELECT sname FROM student WHERE sno=20190101001);SELECT name,a;10.7 常量和变量10.7.2 变量给用户变量赋值（2）用SELECT.INTO语句给会话变量和局部变量赋值 语法格式如下：SELECT col_name,.INTO var_name,.FROM table WHERE condition【例10.11】用SELECT.I
16、NTO语句给会话变量赋值并查看其值。在MySQL命令行客户端输入命令：SELECT sname into na FROM student WHERE sno=20190101001;SELECT na;10.7 常量和变量10.7.2 变量给用户变量赋值（3）用SELECT关键字给变量赋值 语法格式如下：SELECT var_name:=exper如：SELECT name:=数据库编程;注意：此时的赋值符号是冒号和等号“:=”。一般不推荐使用这种赋值语句，建议使用SET语句和SELECT.INTO语句这两种方式给用户变量赋值。10.8 流程控制语句10.8.1 条件语句（1）IF语句语法格式
17、：IF search_condition THEN statement_listELSEIF search_condition THEN statement_list.ELSE statement_listEND IF10.8 流程控制语句10.8.1 条件语句（1）IF语句【例10.13】创建存储过程，输入两个数，输出较大的数。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE pro_max(IN p_a INT,IN p_b INT,OUT p_out INT)BEGIN IF p_ap_b THEN SET p_out=p_a;ELSESET p_
18、out=p_b;END IF;END /DELIMITER ;调用存储过程：CALL pro_max(3,5,c);SELECT c;10.8 流程控制语句10.8.1 条件语句（2）CASE语句语法格式一：CASE case_valueWHEN when_value THEN statement_listWHEN when_value THEN statement_list.ELSE statement_listEND CASE语法格式二：CASEWHEN search_condition THEN statement_listWHEN search_condition THEN state
19、ment_list.ELSE statement_listEND CASE10.8 流程控制语句10.8.1 条件语句（2）CASE语句【例10.14】创建存储过程，输入一个数，判断该数对应的是星期几。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE pro_week(IN p_nu INT,OUT p_week CHAR(3)BEGIN CASE p_nu WHEN 1 THEN SET p_week=星期日;WHEN 2 THEN SET p_week=星期一;WHEN 3 THEN SET p_week=星期二;WHEN 4 THEN SET p_
20、week=星期三;WHEN 5 THEN SET p_week=星期四;WHEN 6 THEN SET p_week=星期五;WHEN 7 THEN SET p_week=星期六;ELSE SET p_week=错误;END CASE;END /DELIMITER ;调用存储过程:CALL pro_week(3,week);SELECT week;10.8 流程控制语句10.8.2 循环语句（1）WHILE语句 语法格式：label:WHILE search_condition DO statement_listEND WHILE label【例10.15】创建存储过程，计算1+2+3+100
21、的值。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE pro_while(OUT p_out INT)BEGIN DECLARE i INT DEFAULT 1;SET p_out=0;WHILE i100 THEN LEAVE label;END IF;SET p_out=p_out+i;SET i=i+1;END LOOP;END/DELIMITER ;调用存储过程:CALL pro_loop(d);SELECT d;10.8 流程控制语句10.8.2 循环语句（3）REPEAT语句 语法格式：label:REPEAT statement_list
22、 UNTIL search_conditionEND REPEAT label【例10.17】创建存储过程，计算1+2+3+100的值。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE pro_repeat(OUT p_out INT)BEGIN DECLARE i INT DEFAULT 1;SET p_out=0;REPEAT SET p_out=p_out+i;SET i=i+1;UNTIL i100 END REPEAT;END/DELIMITER ;调用存储过程:CALL pro_repeat(e);SELECT e;10.9 错误处理10.9
23、.1 可能出现的错误【例10.18】创建存储过程，向student表中插入学生记录。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE insert_student(IN p_sno CHAR(11),IN p_sname VARCHAR(10)BEGIN INSERT INTO student(sno,sname)VALUES(p_sno,p_sname);END/DELIMITER ;该存储过程创建完成后，我们用相同的参数连着两次调用该存储过程：CALL insert_student(1111,张三);CALL insert_student(1111,
24、张三);10.9 错误处理10.9.2 定义错误处理可以使用DECLARE HANDLER语句来定义错误处理代码段，其语法格式如下所示：DECLARE handler_action HANDLER FOR condition_value,condition_value.Statement其中handler_action指示处理程序在执行处理语句后采取的操作，它有三个取值CONTINUE、EXIT和UNDO。10.9 错误处理10.9.2 定义错误处理【例10.19】创建存储过程，向student表中插入学生记录，并进行相应的错误处理。在MySQL命令行客户端输入命令：DELIMITER /CR
25、EATE PROCEDURE insert_stu(IN p_sno CHAR(11),IN p_sname VARCHAR(10),IN p_mno CHAR(4),OUT p_out_no INT,OUT p_out_msg VARCHAR(20)BEGIN DECLARE EXIT HANDLER FOR 1062 BEGIN SET p_out_no=-1;SET p_out_msg=学号重复;END;DECLARE EXIT HANDLER FOR 1452 BEGIN SET p_out_no=-2;SET p_out_msg=专业代码错误;END;INSERT INTO stud
26、ent(sno,sname,mno)VALUES(p_sno,p_sname,p_mno);SET p_out_no=0;SET p_out_msg=添加成功;END/DELIMITER ;10.9 错误处理10.9.2 定义错误处理三次调用该存储过程：CALL insert_stu(2222,李四,0701,result_no,result_msg);SELECT result_no,result_msg;CALL insert_stu(2222,李四,0701,result_no,result_msg);SELECT result_no,result_msg;CALL insert_stu
27、(3333,王五,666,result_no,result_msg);SELECT result_no,result_msg;10.9 错误处理10.9.3 自定义错误条件语法格式：DECLARE condition_name CONDITION FOR condition_valueDECLARE foreign_key_error CONDITION FOR 1452;此时可以将“1452”的错误处理写成如下代码：DECLARE EXIT HANDLER FOR foreign_key_error BEGIN SET p_out_no=-2;SET p_out_msg=专业代码错误;END
28、;10.10 游标10.10.1 游标的概念在存储过程中，如果想把表中查询到的结果存放到变量中，可以通过SELECT INTO语句完成。但SELECT INTO语句是有局限性的，它只能把查询到的一条数据存放到变量中，如果SELECT查询到多行数据结果，那么程序会报错。游标本质上是一种能从包括多条数据记录的SELECT结果集中每次提取一条记录的机制，它的作用类似于C语言中的指针，一次只能指向内存中存放的结果集中的一行，通过控制游标的移动，我们能够遍历结果集中的每一行。10.10 游标10.10.2 游标的使用游标的使用包括四个步骤，分别是声明游标、打开游标、获取数据和关闭游标。（1）声明游标语法
29、格式：DECLARE cursor_name CURSOR FOR select_statement注意：SELECT语句不能带INTO子句。此时只是声明了游标，与它关联的SELECT语句还没有执行，服务器的内存中并不存在与SELECT语句对应的结果集。10.10 游标10.10.2 游标的使用（2）打开游标语法格式：OPEN cursor_name说明：打开游标后，对应的SELECT语句执行，查询到的结果集存放在MySQL服务器的内存中。10.10 游标10.10.2 游标的使用（3）获取数据语法格式：FETCH NEXT FROM cursor_name INTO var_name,var
30、_name 说明：第一次执行FETCH语句时，从结果集中提取第一条记录，然后游标指针后移一行，以此类推。当成功提取最后一行记录后，指针再次后移，指向最后一条记录的后面，这时再次执行该FETCH语句，将产生ERROR 1329（02000）错误，可以针对错误代码1329或者NOT FOUND定义错误处理代码，以便结束游标循环，正常执行程序。10.10 游标10.10.2 游标的使用（4）关闭游标语法格式：CLOSE cursor_name10.10 游标10.10.2 游标的使用【例10.20】创建存储过程，利用游标读取student表中总人数，该功能可以使用COUNT()函数直接完成，此实例主
31、要为演示游标的使用方法。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE student_count(OUT p_num INT)BEGIN DECLARE v_sno CHAR(11);DECLARE finish BOOLEAN DEFAULT FALSE;DECLARE cur_stu CURSOR FOR SELECT sno FROM student;DECLARE CONTINUE HANDLER FOR NOT FOUND SET finish=TRUE;10.10 游标10.10.2 游标的使用SET p_num=0;OPEN cur_s
32、tu;count_loop:LOOP FETCH cur_stu INTO v_sno;IF finish THEN LEAVE count_loop;END IF;SET p_num=p_num+1;END LOOP;CLOSE cur_stu;END/DELIMITER ;调用该存储过程：CALL student_count(a);SELECT a;10.10 游标10.10.2 游标的使用【例10.21】创建存储过程，根据给定的课程名称，利用游标查询并输出选修该门课程的学生学号及成绩。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE course_
33、score(IN p_cname VARCHAR(10),OUT p_sno CHAR(11),OUT p_grade TINYINT)BEGIN DECLARE flag BOOLEAN DEFAULT TRUE;DECLARE cur_score CURSOR FOR SELECT sno,grade FROM score WHERE cno=(SELECT cno FROM course WHERE cname=p_cname);10.10 游标10.10.2 游标的使用 DECLARE EXIT HANDLER FOR NOT FOUND SET flag=FALSE;OPEN cur
34、_score;WHILE flag DO FETCH cur_score INTO p_sno,p_grade;SELECT p_sno,p_grade;END WHILE;END/DELIMITER ;调用该存储过程：CALL course_score(高等数学,sno,grade);CALL course_score(国际经济,sno,grade);10.10 游标10.10.2 游标的使用【例10.22】改写例题10.21，使结果集中显示。在MySQL命令行客户端输入命令：DELIMITER /CREATE PROCEDURE course_score(IN p_cname VARCHA
35、R(10),OUT p_sno CHAR(11),OUT p_grade TINYINT)BEGIN DECLARE flag BOOLEAN DEFAULT TRUE;DECLARE cur_score CURSOR FOR SELECT sno,grade FROM score WHERE cno=(SELECT cno FROM course WHERE cname=p_cname);DECLARE EXIT HANDLER FOR NOT FOUND BEGIN SELECT*FROM tem;END;10.10 游标10.10.2 游标的使用DROP TABLE IF EXISTS
36、tem;CREATE TEMPORARY TABLE tem(tsno CHAR(11),tgrade TINYINT);OPEN cur_score;WHILE flag DO FETCH cur_score INTO p_sno,p_grade;INSERT INTO tem VALUES(p_sno,p_grade);END WHILE;END/DELIMITER ;调用该存储过程：CALL course_score(高等数学,sno,grade);CALL course_score(国际经济,sno,grade);10.11 本章小结存储过程是存放在数据库中的一段程序，是数据库的对象之一。它由声明式的SQL语句和过程式的SQL语句组成，可以有效提高数据的处理速度和数据库编程的灵活性。本章详细介绍了MySQL中存储过程的管理操作，包括创建存储过程、查看存储过程、删除存储过程和调用存储过程。在创建存储过程中，涉及到变量、流程控制语句、游标的使用和错误处理语句的定义，这些都是本章的重点以及难点。