《数据库原理及应用》第2版 课件 第8章 数据库编程

《数据库原理及应用》数据库虚拟教研室制作数据库目录01

数据库编程概述02MySQL编程基本要素03存储过程04存储函数05触发器06事件第8讲MySQL编程数据库编程概述SQL是关系数据库的标准语言，它虽然提供了数据定义、数据操作和数据控制功能，但不支持过程化编程，不能完成复杂的应用需求。如编程实现输入某学生姓名查询该学生的信息；如编程实现根据学生的学号，统计学生的总学分等。

为什么需要学习数据库编程？

面对复杂的应用需求，需要DBMS提供一种过程化的编程方式。数据库编程概述Oracle的PL/SQLSQLServer的Transact-SQLMySQL的数据库编程达梦数据库（DMDatabase）和人大金仓数据库（KingbaseES）的数据库编程类似Oracle的PL/SQL风格阿里云数据库PolarDBMySQL版、RDSforMySQL完全兼容MySQL的过程化编程语言华为云数据库GaussDB(forMySQL)和RDSforMySQL支持所有标准的MySQL过程化编程功能

不同数据库工具的结构化编程：

注意：不同的数据库工具提供的数据库编程的语法差别较大，造成工具间可移植性较差。数据库编程概述

MySQL提供了存储过程、存储函数、触发器和事件等不同类型的存储程序。

存储过程和存储函数由一组SQL语句和流程控制语句构成，它们可以被应用程序、触发器或其他存储过程调用，这样可避免开发人员重复编写SQL代码，提高代码的可复用性。存储过程和存储函数由系统进行预编译，在服务器中存储和执行，因此存储过程和存储函数具有执行速度快、性价比高、安全性高等特点。触发器和事件是与数据表操作相关的特殊类型的存储过程。触发器是满足一定条件自动触发执行的数据库对象，例如，对数据表更新记录时，更新触发器被系统自动触发执行。事件是基于特定时刻或时间间隔，进行周期性调用的数据库对象，例如，在某一时刻定期激活事件向数据表中插入记录。常量字符串常量：

是指用单引号或双引号括起来的字符序列，如'中国'、"China!"。数值常量：分为整型常量和浮点常量，如123、3.14。日期时间型常量：按特定格式表示时间或日期信息的一种字符串。

日期型常量：包括年、月、日，如'2022-05-10'。

时间型常量：包括小时数、分钟数、秒数及微秒数，如'03:28:45:00001'。

日期时间型常量：是日期和时间的组合，如'2022-05-1003:28:45'。布尔型常量：只包含两个可能的值：TRUE和FALSE或0和1。NULL：是无类型的常量。变量MySQL支持3种类型的变量，分别为系统变量、用户变量和局部变量。1.系统变量：在MySQL启动时由系统定义并初始化，默认值可在系统配置文件my.ini或在命令行指定的选项中进行更改。系统变量分为全局系统变量和会话系统变量。全局系统变量：影响服务器整体操作,

@@GLOBAL；会话系统变量：影响正在进行的会话,

@@SESSION或@@。

例1：SET@@GLOBAL.auto_increment_increment=2;例2：SET@@auto_increment_increment=3;2.用户变量:用户变量也称用户会话变量，用户在会话连接空间内定义的变量，因此用户变量属于某个特定会话。并在变量名前加“@”变量用户变量的赋值方式：（1）SET语句的语法格式SET@v_sno='02300';SET@v_sno=(SELECTsnoFROMstudentWHEREsname='王燕');（2）SELECT语句的语法格式SELECT@num:=(SELECTCOUNT(*)FROMstudent);SELECTCOUNT(*)into@numFROMstudent;3.局部变量局部变量是在语句块(BEGIN…END)内部定义的变量，其作用范围仅限于定义该变量的语句块。使用前需用DECLARE语句声明，并指明数据类型。变量例1:定义长度为3的字符串型变量var_s，默认值为“女”，并将其重新赋值为“男”DECLAREvar_sCHAR(3)DEFAULT'女';SETvar_s='男';例2:学号为“001101”学生的姓名赋给字符串型变量var_nameDECLAREvar_nameVARCHAR(10);SELECTsnameINTOvar_nameFROMstudentWHEREsno='001101';或SETvar_name=(SELECTsnameFROMstudentWHEREsno='001101');定义语句结束符在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。但有时候，一次执行多条sql命令，此时必需改变结束符。DELIMITER命令可以重新定义代码执行的结束符，如“//”“$$”等符号。DELIMITER//CREATEPROCEDURE过程名()BEGIN

语句1;语句2;……[语句n;]END//DELIMITER;流程控制结构流程控制结构主要有顺序结构、分支（选择）结构和循环结构。【例8.7】使用LOOP和LEAVE语句，计算1～100的所有自然数的累加和DELIMITER//CREATEPROCEDUREc_sum()BEGINDECLARE_countINTDEFAULT0;DECLARE_sumINTDEFAULT0;label:LOOPSET_sum=_sum+_count;SET_count=_count+1;

IF_count>100THENLEAVElabel;ENDIF; ENDLOOPlabel;SELECT_sum;END//DELIMITER;存储过程概述存储过程(StoredProcedure)是由一些SQL语句和流程控制语句构成的集合，它可以被应用程序、触发器或另外一个存储过程所调用，执行后能完成预先设定的功能。存储过程具有以下优点：一个存储过程一次可以执行一组SQL语句。存储过程可调用其他存储过程，体现模块化编程。存储过程在创建时就在服务器上进行编译，所以执行速度比单条SQL语句快。存储过程可以重复执行，进而减少重复工作，提高执行效率。创建存储过程定义存储过程：CREATEPROCEDUREprocedure_name([[IN|OUT|INOUT]parameter_nametype[,...]])routine_body；存储过程的参数有三种类型IN、OUT和INOUT。IN输入参数，OUT输出参数，INOUT输入输出参数，默认是IN。routine_body；为过程体，一般为BENG……END格式。调用存储过程：CALLprocedure_name[(procedure_parameter)];

procedure_parameter表示实际参数。如果不需要参数，则语法格式可简化为CALLprocedure_name;或CALLprocedure_name();。创建存储过程【例8.9】创建存储过程pro_del_sc，根据学号和课程号查询score表中的数据，学号和课程号由存储过程的参数传入。DELIMITER//CREATEPROCEDUREpro_del_sc2(v_snoCHAR(6),v_cnoCHAR(3))BEGINDELETEFROMscoreWHEREsno=v_snoANDcno=v_cno;END//DELIMITER;--调用CALLpro_del_sc2('001106','102');创建存储过程【例8.10】创建存储过程pro_inquire，该存储过程通过学生姓或名实现模糊查询，查询出符合条件的学号和姓名，存储过程的参数为学生的姓或名。DELIMITER//CREATEPROCEDUREpro_inquire(v_nameVARCHAR(20))BEGINSELECTsno,snameFROMstudentWHEREsnameLIKECONCAT('%',v_name,'%');END//DELIMITER;--调用CALLpro_inquire('林');1.定义游标DCELARE<游标名>

CURSORFOR<查询语句>；游标2.打开游标OPEN<游标名>；3.使用游标FETCH<游标名>INTO变量名1[,变量名2]……;4.关闭游标CLOSE<游标名>;发生NOTFOUND异常游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行遍历数据的能力。游标使用的流程为：游标【例8.11】根据学号统计某同学所获得的总学分，如果课程成绩达不到60分，该门课程的学分不能计入。DELIMITER//DROPPROCEDUREIFEXISTSupdateToealcredit//CREATEPROCEDUREupdateToealcredit()BEGINDECLAREdoneBOOLEANDEFAULTFALSE;DECLARE_snoCHAR(6);DECLARE_totalcreditDECIMAL(5,1);DECLAREstudent_cursorCURSORFORSELECTsnoFROMstudent;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;OPENstudent_cursor;read_loop:LOOPFETCHstudent_cursorINTO_sno;游标IFdoneTHENLEAVEread_loop;ENDIF;SELECTSUM(IF(grade>=60,credit,0))INTO_totalcreditFROMcourseJOINscoreONo=oWHEREscore.sno=_sno;UPDATEstudentSETtotalcredit=_totalcreditWHEREsno=_sno;ENDLOOP;

CLOSEstudent_cursor;END//DELIMITER;1.定义条件定义条件的语句用于将MySQL中的错误代码命名为异常名，并将异常名与指定的错误条件关联，其语法格式为：DECLAREcondition_nameCONDITIONFOR{SQLSTATEsqlstate_value|MySQL_error_code};异常处理例违反外键约束条件异常的定义条件的语句为：DECLAREerror_foreignkeyCONDITIONFOR1452;

DECLAREerror_foreignkeyCONDITIONFORSQLSTATE'23000';存储过程调用中，常常有异常情况出现。为保证顺利调用存储过程，需要对异常进行处理。流程为：先对存储程序执行中可能出现的异常进行声明，再对出现的异常进行适当的处理，保证存储程序在系统警告或报错的情况下仍能继续执行。条件处理器既增强了存储程序处理问题的能力，也避免了程序因异常而停止运行。2.定义处理程序语法格式为：DECLARE{CONTINUE|EXIT}HANDLERFOR{condition_value}statement;异常处理CONTINUE：表示遇到错误不处理，继续执行。

EXIT：表示遇到错误退出。condition_value包括:SQLSTATE‘sqlstate_value’中的’sqlstate_value’是长度为5的字符串错误代码；MySQL_error_code：指数值类型错误代码；condition_name：指通过定义条件语句命名的异常名；SQLWARNING指以01开头的所有SQLSTATE码所对应的异常；NOTFOUND指以02开头的所有SQLSTATE码所对应的异常；SQLEXCEPTION指不以01或02开头的SQLSTATE码所对应的异常【例8.12】创建存储过程insert_sc，该存储过程将完成向score表插入数据，其中学号和课程号由输入参数给出，同时程序返回是否插入成功的结果。DELIMITER//CREATEPROCEDUREinsert_sc(v_snoCHAR(6),v_cnoCHAR(3),v_gradeINT,OUTv_messageVARCHAR(100))BEGINDECLAREdoneBOOLEANDEFAULT0;DECLAREfk_errorCONDITIONFOR1452;//1452是违反外键约束代码DECLARECONTINUEHANDLERFORfk_errorSETdone=1;//fk_error也可以直接用1452代替，省略上行INSERTINTOscoreVALUES(NULL,v_sno,v_cno,v_grade);IFdone=1THENSETv_message:='error_foreignkey';ELSESETv_message:='success';ENDIF;END//DELIMITER;异常处理调用、查看和删除存储过程查看存储过程SHOWCREATEPROCEDUREprocedure_name;删除存储过程DROPPROCEDUREprocedure_name;调用带参存储过程形参与实参一一对应，输出参数要定义变量保存返回的值CALLinsert_sc('001101','701',60,@message);SELECT@message;存储函数存储函数可以由用户自定义创建，能通过RETURN语句返回一个函数值。创建用户自定义函数语句:CREATEFUNCTIONfunc_name([func_param[...]])RETURNStype[characteristic]func_body;[characteristic...]特征参数是可选的，通常取:DETERMINISTIC[dɪˌtɜːmɪˈnɪstɪk]：确定的输入得到确定的输出NOSQL：子程序不包含sqlREADSSQLDATA：子程序包含读数据的语句，但是不包含写数据的语句。注意：这些特征值目前提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况，因此选择这三者中的任何一项都可以。【例8.14】创建存储函数fun_st_num，统计student表中某个系别的人数，系名由函数的参数给出DELIMITER//DROPFUNCTIONIFEXISTSfun_st_num//CREATEFUNCTIONfun_st_num(v_deptVARCHAR(20))RETURNSINTREADSSQLDATABEGINDECLAREv_numINTDEFAULT0;SELECTCOUNT(*)INTOv_numFROMstudentWHEREdept=v_dept;

RETURNv_num;END//DELIMITER;存储函数调用函数在MySQL中，自定义函数的调用方法与MySQL内置函数的调用方法是一样的。MySQL内置函数是系统自带的，自定义函数为用户创建的。调用方式一：在语句中直接调用func_name（func_parameter）调用方式二：SELECTfunc_name（func_parameter）;存储函数存储函数查看存储函数创建信息SHOWCREATEFUNCTIONfunction_name;删除存储函数DROPFUNCTIONfunction_name;练习【例8.11】根据学号统计某同学所获得的总学分，如果课程成绩达不到60分，该门课程的学分不能计入。要求将根据学号计算总学分的部分改为存储函数来实现。DELIMITER$$DROPFUNCTIONIFEXISTSsumTotalcredit$$CREATEFUNCTIONsumTotalcredit(_snoCHAR(6))RETURNSDECIMALNOSQLBEGINDECLARE_totalcreditDECIMAL(5,1);SELECTSUM(IF(grade>=60,credit,0))INTO_totalcreditFROMcourseJOINscoreONo=oWHEREscore.sno=_sno;RETURN_totalcredit;END$$DELIMITER;UPDATEstudentSETtotalcredit=sumTotalcredit(_sno)WHEREsno=_sno;触发器触发器（trigger）是一种特殊的存储过程，编译后存储在数据库服务器中。当特定事件发生时，由系统自动调用执行。触发器不接受任何输入参数。触发器是在特定表上进行定义的，该表也称为触发器表。当有针对触发器表的操作时，触发器就自动触发执行。操作通常为：插入（Insert）删除（Delete）修改（Update）触发事件：插入（Insert）、删除（Delete）、修改（Update）；触发时机：事件前（before）、事件后(after)；触发级别：表级(只触发一次)、行级(foreachrow每行触发一次)；触发器引用记录：新记录表(new)旧记录表(old)：

插入：新记录在插入时放入new表：

删除：原记录在删除时放入old表：

修改：原记录→(old)新记录→(new)。创建触发器创建触发器创建触发器的格式：CREATETRIGGER[IFNOTEXISTS]<trigger_name>{BEFORE|AFTER}{UPDATE|INSERT|DELETE}ON<table_name>[FOREACHROW]TRIGGER_BODY;触发事件：指定INSERT、DELETE或UPDATE事件，MySQL不支持多个事件触发，即一个触发器只支持一个事件。【例8.16】创建一个保存成绩表修改操作的日志表score_log(id,operate_date,operate_user，log_text)，

用于记录对score表所做的修改操作的相关信息。（1）创建日志表CREATETABLEscore_log(idINTNOTNULLAUTO_INCREMENTPRIMARYKEY,operate_dateDATE,operate_userVARCHAR(20),log_textVARCHAR(200));创建触发器（2）创建触发器trig_score。DELIMITER//DROPTRIGGERIFEXISTStrig_score//CREATETRIGGERtrig_scoreAFTERUPDATEONscoreFOREACHROWBEGININSERTINTOscore_log(id,operate_date,operate_user,log_text)VALUES(NULL,NOW(),USER(),CONCAT(‘学号:’,OLD.sno,‘,’课程号:’,OLD.CNO,’原成绩:',OLD.grade,'新成绩',NEW.grade));END//DELIMITER;创建触发器（3）验证触发器，修改score表数据后，查看score_log内容。

UPDATEscoreSETgrade=80WHEREsno=‘001103’ANDcno=‘102‘;SELECT*FROMscore_log;创建触发器【例8.18】创建触发器sum_credit，实现对student表总学分的累加，当score中添加记录时，student表总学分的值做相应改变。当课程成绩大于等于60分时，将该课程的学分加到该学生的总学分中。DELIMITER$$DROPTRIGGERIFEXISTSsum_credit$$CREATETRIGGERsum_creditAFTERINSERTONscoreFOREACHROWBEGINUPDATEstudentSETtot