数据基础与应用 8

数据库编程第7章2026数据库原理与应用教材第131—148页本章知识要点本章导入教材第131页002/140数据库编程的概念编程语言与数据库的交互视图的用法存储过程的概念与优势游标的定义及使用触发器的创建与管理本章内容地图本章导入教材第131—148页003/1407.1数据库编程基础7.2视图7.3存储过程与触发器7.4章节结语与实验7.1数据库编程基础7.1数据库编程基础教材第131页004/1407.1数据库编程基础7.1.1数据库编程的概念与目的7.1.2编程语言与数据库交互方式7.1.1数据库编程的概念与目的7.1.1数据库编程的概念与目的教材第131页005/1407.1.1数据库编程的概念与目的数据库编程的定义数据管理、业务逻辑、效率与安全数据库编程的定义7.1.1数据库编程的概念与目的教材第131—132页006/140教材定义数据库编程的定义：数据库编程是指使用编程语言来操作和管理数据库的过程。它涉及到与数据库建立连接、执行查询、插入、更新和删除数据等操作。数据库编程的主要目的(1/2)7.1.1数据库编程的概念与目的教材第132页007/140数据管理：通过编程实现对数据库中数据的高效存储、检索、更新和删除，以满足业务需求。例如，在一个外卖点餐系统中，需要将用户的订单信息、菜品信息等存储到数据库中，并能够根据用户的查询请求快速检索相关数据。业务逻辑实现：将业务规则和逻辑转化为数据库操作，确保数据的完整性和一致性。例如，在订单处理过程中，可能需要检查库存是否充足、计算订单总价等，这些业务逻辑可以通过数据库编程来实现。数据库编程的主要目的(2/2)7.1.1数据库编程的概念与目的教材第132页008/140提高效率：使用编程技术可以优化数据库操作，提高数据处理的速度和效率。例如，通过合理设计数据库索引、使用存储过程等方式，可以减少数据库查询的时间开销。数据安全：通过编程实现对数据库的访问控制和数据加密等安全措施，保护数据的机密性、完整性和可用性。例如，限制用户对敏感数据的访问权限、对密码等敏感信息进行加密存储等。7.1.2编程语言与数据库交互方式7.1.2编程语言与数据库交互方式教材第132页009/1407.1.2编程语言与数据库交互方式ODBC/JDBCORM框架原生SQL查询ODBC与JDBC7.1.2编程语言与数据库交互方式教材第132页010/140(1)ODBC(OpenDatabaseConnectivity)和JDBC(JavaDatabaseConnectivity)：ODBC是一种用于访问不同数据库管理系统的标准接口，它允许应用程序通过一组通用的函数调用来连接和操作各种数据库。JDBC是Java语言中用于连接数据库的API，它提供了一种面向对象的方式来访问数据库。ODBC/JDBC的一般步骤7.1.2编程语言与数据库交互方式教材第132页011/140加载数据库驱动程序：根据要连接的数据库类型，加载相应的数据库驱动程序。建立连接：使用驱动程序提供的连接字符串和认证信息，建立与数据库的连接。执行SQL语句：通过连接对象创建Statement或PreparedStatement对象，执行SQL查询、插入、更新或删除语句。处理结果集：如果执行的是查询语句，需要处理返回的结果集，获取查询结果。关闭连接：在完成数据库操作后，关闭连接以释放资源。ORM框架7.1.2编程语言与数据库交互方式教材第132页012/140(2)ORM(Object-RelationalMapping)框架：ORM框架是一种将对象模型和关系数据库模型进行映射的技术，它允许开发人员使用面向对象的编程方式来操作数据库。常见的ORM框架有Hibernate、MyBatis等。ORM框架的一般步骤7.1.2编程语言与数据库交互方式教材第132页013/140配置ORM框架：根据要连接的数据库类型和项目需求，配置ORM框架的相关参数。定义实体类：将数据库中的表映射为编程语言中的实体类，每个实体类对应一个数据库表。执行数据库操作：通过ORM框架提供的API，执行查询、插入、更新和删除等数据库操作。例如，可以使用对象的方法来查询数据库中的数据，或者使用对象的属性赋值和保存方法来插入或更新数据。处理结果集：ORM框架通常会将查询结果自动转换为实体对象，开发人员可以直接操作这些对象。原生SQL查询7.1.2编程语言与数据库交互方式教材第132—133页014/140原生SQL查询：有些编程语言提供了直接执行原生SQL查询的方法。例如，在Python中，可以使用sqlite3模块直接执行SQL语句来操作SQLite数据库。原生SQL查询的一般步骤7.1.2编程语言与数据库交互方式教材第132—133页015/140建立连接：使用编程语言提供的数据库连接函数或类，建立与数据库的连接。执行SQL语句：使用连接对象执行原生SQL查询语句。处理结果集：根据查询结果的类型，使用相应的方法处理结果集；如果查询结果是表格形式，可以循环遍历并获取每一行数据。关闭连接：在完成数据库操作后，关闭连接以释放资源。7.2视图7.2视图教材第132—136页016/1407.2视图7.2.1建立视图7.2.2删除视图7.2.3查询视图7.2.4更新视图视图的定义7.2视图教材第133页017/140教材定义视图的定义：视图是从一个或几个基本表(或视图)导出的表，它与基本表不同，是一个虚拟表。其结构和数据建立在对表的查询基础上。视图只保存定义，数据仍在基本表中7.2视图教材第133页018/140数据库中只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。基本表中的数据发生变化，从视图中查询出的数据也随之改变。视图可以查询和再定义，更新受到限制7.2视图教材第133页019/140视图一经定义，就可以和基本表一样被查询、删除，也可以在一个视图上再定义新的视图。对视图的更新(增加、修改、删除)操作有一定限制。7.2.1建立视图7.2.1建立视图教材第133页020/1407.2.1建立视图CREATEVIEW语法列名规则与WITHCHECKOPTION单表、多表和视图上的视图CREATEVIEW的一般格式7.2.1建立视图教材第133页021/140建立视图的一般格式为:教材语句CREATEVIEW<视图名>[(<列名>[,<列名>]….)]AS<子查询>[WITHCHECKOPTION];CREATEVIEW只保存定义，不执行子查询7.2.1建立视图教材第133页022/140DBMS执行CREATEVIEW语句时只是把视图的定义存入数据字典，并不执行其中的子查询语句。所以数据库中只存放视图的定义，而不存放对应的数据，这也是视图被称为虚表的原因。必须明确指定视图列名的三种情况7.2.1建立视图教材第133页023/140构成视图的属性列或者全部省略，或者全部给出，没有其他情况。如果全部省略，构成视图的属性列由子查询中SELECT子句中的诸字段确定。但在下列三种情况下必须明确指定组成视图的所有列名。(1)某个目标列是集函数或列表达式；(2)多表连接时选出了几个同名列作为视图的字段;(3)需要在视图中为某个列启用新的更合适的名字。WITHCHECKOPTION的作用7.2.1建立视图教材第133页024/140教材定义WITHCHECKOPTION的定义：WITHCHECKOPTION表示对视图进行插入、删除和更新操作时要保证发生变动的行满足视图定义中的谓词条件(即子查询中的条件表达式)。建立视图的三种来源7.2.1建立视图教材第134页025/1401.在单表上创建视图2.在多个表上创建视图。3.在视图上创建视图教材例7-1：在单表上创建视图7.2.1建立视图教材第133—134页026/140【例7-1】建立商家地址为江苏省南京市的商家的视图。教材语句CREATEVIEWJS_VIEWASSELECT*FROMbusinessWHEREbusinessAddress="江苏省南京市";教材例7-1：视图是基本表的映像7.2.1建立视图教材第134页027/140该语句生成一个名为JS_VIEW的视图，其中的属性列与商家表中的属性列一致，它相当于基本表的一个映像，只有在运行视图的时候，才会得到数据，不运行时，视图只是一个表的模式的定义。SELECT*创建的视图可扩充性差7.2.1建立视图教材第134页028/140需要注意的是，以SELECT*方式创建的视图可扩充性差，应尽可能避免。原因在于该类视图(JS_VIEW)中的属性列与定义视图时的基本表(商家表)中的属性列自动形成映射关系，当基本表的结构发生变化时，视图(JS_VIEW)与基本表(商家表)的映射关系被破坏，导致该类视图不能正确工作。为了避免出现这类问题，最好在修改基本表之后删除由其导出的视图，然后重建视图;或者将视图的建立语句改写为如下形式。教材例7-1：改写为显式列名7.2.1建立视图教材第133—135页029/140修改基本表后应重建视图，或在建立视图时显式列出属性列。教材语句CREATEVIEWJS_VIEW(password,businessName,businessExplain,businessAddress,starPrice,deliveryPrice)ASSELECTpassword,businessName,businessExplain,businessAddress,starPrice,deliveryPriceFROMbusinessWHEREbusinessAddress="江苏省南京市";教材例7-2：在多个表上创建视图7.2.1建立视图教材第134—135页030/140【例7-2】建立商家地址为江苏省南京市的商家信息以及食品信息的视图。教材语句CREATEVIEWDB_S1ASSELECTbusiness.businessName,business.businessAddress,food.foodPriceFROMbusiness,foodWHEREbusiness.businessAddress="江苏省南京市"ANDbusiness.businessId=food.businessId;教材例7-3：用聚合结果创建视图7.2.1建立视图教材第134页031/140【例7-3】创建一个视图，信息包含所有注册商家的总数量、平均起送费、平均配送费、最高起送费、最低起送费、最高配送费、最低配送费、一份最低消费点餐中的最大值(起送费+配送费)，一份最低消费点餐中的最小值(起送费+配送费)。教材语句CREATEviewbusiness_statisticsASSELECTcount(*)商家总数量,avg(starPrice)平均起送费,avg(deliveryPrice)平均配送费,max(starPrice)最高起送费,min(starPrice)最低起送费,max(deliveryPrice)最高配送费,min(deliveryPrice)最低配送费,max(starPrice+deliveryPrice)一份最低消费点餐中的最大值,min(starPrice+deliveryPrice)一份最低消费点餐中的最小值FROMbusiness;教材例7-4：在视图上创建视图7.2.1建立视图教材第134页032/140【例7-4】建立商家地址为江苏省南京市的商家信息以及食品信息并且食品价格在30元及以上的视图。教材语句CREATEVIEWDB_S2ASSELECT*FROMDB_S1WHEREfoodPrice>=30;7.2.2删除视图7.2.2删除视图教材第135页033/1407.2.2删除视图DROPVIEW语法删除后的依赖影响DROPVIEW的一般格式7.2.2删除视图教材第135页034/140删除视图的一般格式为:教材语句DROPVIEW<视图名>删除视图后，派生视图也将失效7.2.2删除视图教材第135页035/140一个视图被删除后，由此视图导出的其他视图也将失效，用户应该使用DROPVIEW语句将它们一一删除。7.2.3查询视图7.2.3查询视图教材第135页036/1407.2.3查询视图查询视图的执行过程视图消解教材例7-5视图消解的定义7.2.3查询视图教材第135页037/140教材定义视图消解的定义：视图定义后，用户就可以像对基本表进行查询一样对视图进行查询。DBMS执行对视图的查询时，首先进行有效性检查，检查查询涉及的表、视图等是否在数据库中存在，如果存在，则从数据字典中取出查询涉及的视图的定义，把定义中的子查询和用户对视图的查询结合起来，转换成对基本表的查询，然后再执行这个经过修正的查询。将对视图的查询转换为对基本表的查询的过程称为视图的消解(ViewResolution)。教材例7-5：查询视图7.2.3查询视图教材第134—135页038/140【例7-5】在江苏省南京市为地址的商家信息中查询起送费低于30的商家。教材语句SELECT*FROMJS_VIEWWHEREstarPrice<=30;7.2.4更新视图7.2.4更新视图教材第135页039/1407.2.4更新视图可更新视图行列子集视图七项更新限制可更新视图的定义7.2.4更新视图教材第135页040/140教材定义可更新视图的定义：更新视图包括插入(INSERT)、删除(DELETE)和修改(UPDATE)三类操作。并非所有的视图都允许更新，只有某些非常简单的视图(称为可更新视图，UpdatableView)，在把对视图的更新操作转换为对基本表的等价操作后，允许对这些视图进行更新。关系系统主要支持行列子集视图更新7.2.4更新视图教材第135页041/140到底什么样的视图是可更新的?若一个视图是从单个基本表导出的，并且只是去掉了某些行和列(不包括关键字)，如视图CSVIEW，称这类视图为行列子集视图。目前，关系系统只提供对行列子集视图的更新，并具有以下限制。视图更新限制(1/2)7.2.4更新视图教材第135页042/140(1)若视图的属性来自属性表达式或常数，则不允许对视图执行INSERT和UPDATE操作，但允许执行DELETE操作。(2)若视图的属性来自库函数，则不允许对此视图更新。(3)若视图定义中有GROUPBY子句，则不允许对此视图更新(4)若视图定义中有DISTINCT选项，则不允许对此视图更新。视图更新限制(2/2)7.2.4更新视图教材第135页043/140(5)若视图定义中有嵌套查询，并且嵌套查询的FROM子句涉及导出该视图的基本表，则不允许对此视图更新。(6)若视图由两个以上的基本表导出，则不允许对此视图更新。(7)如果在一个不允许更新的视图上再定义一个视图，这种二次视图是不允许更新的。教材例7-6：不允许更新的视图7.2.4更新视图教材第135页044/140【例7-6】以下视图是不允许更新的。教材语句CREATEVIEWJS_P_VIEWASSELECTbusinessName,businessExplain,businessAddress,starPriceFROMbusinessWHEREstarPrice>(SELECTAVG(starPrice)FROMbusiness);7.3存储过程与触发器7.3存储过程与触发器教材第135—144页045/1407.3存储过程与触发器7.3.1游标的定义及使用7.3.2存储过程概述7.3.3存储过程相关操作7.3.4—7.3.5触发器商用数据库普遍提供过程化SQL7.3存储过程与触发器教材第136页046/140SQL99标准支持存储过程和函数的概念，商用关系数据库管理系统大多提供过程化的SQL，如Oracle的PL/SQL、MicrosoftSQLServer的Transact-SQL、IBMDB2的SQLPL、Kingbase的PL/SQL等。触发器由事件驱动并实施集中控制7.3存储过程与触发器教材第136页047/140触发器是用户定义在关系表上的一类由事件驱动的特殊过程。触发器类似于约束，但是比约束更加灵活，可以实施更为复杂的检查和违约操作。它不是只提供一种选择，即中止导致违约的事务/操作，因此具有更精细和更强大的数据控制能力。触发器一经定义就被保存在数据库服务器中。任何用户对表的更新操作均由服务器自动激活相应的触发器，在关系数据库管理系统核心层进行集中的完整性控制。触发器在SQL99之后才写入SQL标准，但是不少关系数据库管理系统早就支持触发器因此不同的关系数据库管理系统实现的触发器语法各不相同、互不兼容。7.3.1游标的定义及使用7.3.1游标的定义及使用教材第136页048/1407.3.1游标的定义及使用声明游标打开游标使用游标关闭游标游标的定义7.3.1游标的定义及使用教材第136页049/140教材定义游标的定义：在过程化SQL中，如果SELECT语句只返回一条记录，可以将该结果存放到变量中。当查询返回多条记录时，就要使用游标对结果集进行处理。一个游标与一个SQL语句相关联。使用游标的四个步骤7.3.1游标的定义及使用教材第136页050/140声明游标打开游标使用游标关闭游标声明游标：定义SELECT，不立即执行7.3.1游标的定义及使用教材第136页051/140定义游标仅仅是一条说明性语句，这时关系数据库管理系统并不执行SELECT语句。教材语句DECLARE游标名[(参数1数据类型,参数2数据类型,…)]/*不同的关系数据库产品语法稍有不同*/CURSORFOR/*在Kingbase中是"游标名CURSORFOR"*/SELECT语句;/*在Oracle中是"CURSORFOR游标名"*/打开游标：执行SELECT并取得结果集7.3.1游标的定义及使用教材第136页052/140打开游标实际上是执行相应的SELECT语句，把查询结果取到缓冲区中。这时游标处于活动状态，指针指向查询结果集中的第一条记录。教材语句OPEN游标名[(参数1数据类型,参数2数据类型,…)];FETCH：推进指针并取出当前记录7.3.1游标的定义及使用教材第136页053/140推进游标指针并取当前记录:教材语句FETCH游标名INTO变量1[,变量2,…];FETCH按行读取结果集7.3.1游标的定义及使用教材第136页054/140其中变量必须与SELECT语句中的目标列表达式具有一一对应关系。用FETCH语句把游标指针向前推进一条记录，同时将缓冲区中的当前记录取出送至变量供过程化SQL进一步处理。通过循环执行FETCH语句逐条取出结果集中的行进行处理。关闭游标并释放资源7.3.1游标的定义及使用教材第137页055/140用CLOSE语关闭游标，释放结果集占用的缓冲区及其他资源。CLOSE游标名;游标被关闭后就不再和原来的查询结果集相联系，但被关闭的游标可以再次被打开，与新的查询结果相联系。教材例7-7：游标输出商家食品(1/2)7.3.1游标的定义及使用教材第137页056/140【例7-7】根据给定的商家编号1，使用游标输出该商家的全部出售的食品。教材语句DELIMITER//CREATEPROCEDUREshow_foods_for_merchant(INmerchant_id_inputINT)BEGINDECLAREfood_name_varVARCHAR(100);DECLAREdoneINTDEFAULTFALSE;DECLAREcurCURSORFORSELECTfoodNameFROMfoodWHEREbusinessId=merchant_id_input;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;OPENcur;教材例7-7：游标输出商家食品(2/2)7.3.1游标的定义及使用教材第137页057/140教材语句read_loop:LOOPFETCHcurINTOfood_name_var;IFdoneTHENLEAVEread_loop;ENDIF;SELECTCONCAT('Merchant',merchant_id_input,',Food:',food_name_var);ENDLOOP;CLOSEcur;END//DELIMITER;7.3.2存储过程概述7.3.2存储过程概述教材第137—138页058/1407.3.2存储过程概述存储过程的定义四项优势SQLServer中的过程特征存储过程的定义7.3.2存储过程概述教材第138页059/140教材定义存储过程的定义：存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程的四项优势7.3.2存储过程概述教材第137页060/140(1)提高性能：存储过程在数据库服务器上执行，减少了网络传输开销和客户端的处理负担。(2)增强安全性：可以限制对数据库的直接访问，通过存储过程来执行操作，降低了数据被恶意篡改的风险。(3)代码复用：可以被多个应用程序或用户共享，提高开发效率。(4)易于维护：当业务逻辑发生变化时，只需修改存储过程，而不必在多个地方修改应用程序中的SQL语句。SQLServer存储过程的主要特征7.3.2存储过程概述教材第137页061/140(1)存储过程可以接收参数，并以输出参数的形式返回多个参数给调用存储过程的过程或批处理。(2)存储过程也可以容纳存储过程，可以在对数据库进行查询、修改的编程语句中调用其他的存储过程。(3)可以返回执行存储过程的状态值以反映存储过程的执行情况。存储过程的好处归纳7.3.2存储过程概述教材第138页062/140使用存储过程的好处主要表现为：执行速度快、模块化的程序设计、减少网络通信量、保证系统的安全性。7.3.3存储过程在外卖点餐系统中的相关操作7.3.3存储过程在外卖点餐系统中的相关操作教材第137—141页063/1407.3.3存储过程在外卖点餐系统中的相关操作创建存储过程查看和执行存储过程修改与删除存储过程外卖点餐系统实例CREATEPROCEDURE的基本格式7.3.3存储过程在外卖点餐系统中的相关操作教材第137—139页064/140创建存储过程使用CREATEPROCEDURE语句。其命令格式如下：教材语句CREATEPROCEDURE<存储过程名>([参数列表])BEGIN--SQL语句组END;教材例7-8：按商家编号查询基本情况7.3.3存储过程在外卖点餐系统中的相关操作教材第138页065/140【例7-8】创建一个存储过程，输入某个商家的编号查询商家的基本情况。可用下列语句来完成：教材语句USEtakeaway;DELIMITER//CREATEPROCEDUREMerchantQuery_Proc(INp_merchant_idINT)BEGINIFp_merchant_idISNULLTHENSELECT'PleaseInputMerchantID!';ELSESELECT*FROMbusinessWHEREbusinessId=p_merchant_id;ENDIF;END//DELIMITER;查看存储过程：SHOW命令7.3.3存储过程在外卖点餐系统中的相关操作教材第138—139页066/140SHOWCREATEPROCEDURE查看创建文本；SHOWPROCEDURESTATUS查看一般信息。教材语句SHOWCREATEPROCEDUREMerchantQuery_Proc;SHOWPROCEDURESTATUSLIKE'MerchantQuery_Proc';通过information_schema查询依赖信息7.3.3存储过程在外卖点餐系统中的相关操作教材第138页067/140(3)在MySQL中没有与SQLServer的sp_depends完全对应的命令，但可以通过查询information_schema数据库中的表来获取类似信息。调用存储过程7.3.3存储过程在外卖点餐系统中的相关操作教材第138页068/1403.使用SQL语句执行存储过程如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用该存储过程。教材例7-9：不带参数执行存储过程7.3.3存储过程在外卖点餐系统中的相关操作教材第138页069/140【例7-9】执行存储过程MerchantQuery_Proc(不带参数)。可用下列语句来完成。教材语句USEtakeaway;CALLMerchantQuery_Proc(NULL);教材例7-10：带具体参数执行存储过程7.3.3存储过程在外卖点餐系统中的相关操作教材第138—139页070/140【例7-10】执行存储过程MerchantQuery_Proc，学号参数为具体值。教材语句USEtakeaway;CALLMerchantQuery_Proc(1);MySQL修改存储过程的方式7.3.3存储过程在外卖点餐系统中的相关操作教材第139页071/140使用SQL语句修改存储过程。如果存储过程存在则删除，创建新的存储过程。教材例7-11：按商家名称查询基本信息7.3.3存储过程在外卖点餐系统中的相关操作教材第138—139页072/140【例7-11】将存储过程MerchantQuery_Proc的功能修改为通过输入商家名称查询商家的基本信息。教材语句USEtakeaway;DELIMITER//DROPPROCEDUREIFEXISTSMerchantQuery_Proc//CREATEPROCEDUREMerchantQuery_Proc(INp_merchant_nameVARCHAR(255))BEGINIFp_merchant_nameISNULLTHENSELECT'PleaseInputMerchantName!';ELSESELECT*FROMbusinessWHEREbusinessName=p_merchant_name;ENDIF;END//DELIMITER;教材例7-12：删除存储过程7.3.3存储过程在外卖点餐系统中的相关操作教材第139页073/140【例7-12】删除存储过程MerchantQuery_Proc。教材语句DROPPROCEDURE<存储过程名>;DROPPROCEDUREMerchantQuery_Proc;教材例7-13：计算某商家购物车食品总价7.3.3存储过程在外卖点餐系统中的相关操作教材第139页074/140【例7-13】创建存储过程get_cart_total，返回购物车中选购的某商家的所有食品总价。参数为：用户编号、商家编号。教材例7-13：get_cart_total(1/2)7.3.3存储过程在外卖点餐系统中的相关操作教材第139—140页075/140教材语句CREATEPROCEDUREget_cart_total(INuidVARCHAR(20),INbidINT,OUTtotalINT)BEGINDECLAREdoneBOOLEANDEFAULTFALSE;--判断是否遍历完游标中的所有数据。DECLAREfpINT;--变量名不能与查询字段名相同。DECLAREquINT;DECLAREcurCURSORFOR--将查询数据赋值给fp、qu。SELECTf.foodPricefp,c.quantityquFROMcartcLEFTJOINfoodfONc.foodId=f.foodIdWHEREc.userId=uidANDc.businessId=bid;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;--遍历完成。教材例7-13：get_cart_total(2/2)7.3.3存储过程在外卖点餐系统中的相关操作教材第140—141页076/140教材语句SETtotal=0;OPENcur;read_loop:LOOPFETCHcurINTOfp,qu;IFdoneTHENLEAVEread_loop;ENDIF;SETtotal=total+(fp*qu);ENDLOOP;CLOSEcur;END教材例7-14：变更订单状态7.3.3存储过程在外卖点餐系统中的相关操作教材第140页077/140【例7-14】创建一个存储过程来实现对订单状态的变更。参数为：订单编号、订单状态、新的状态教材例7-14：update_order_status7.3.3存储过程在外卖点餐系统中的相关操作教材第140页078/140教材语句CREATEPROCEDUREupdate_order_status(INTorder_id,ENUMorder_statusnew_status)BEGINUPDATEt_orderSETorder_status=new_statusWHEREorder_id=order_idFORUPDATE;END教材例7-15：生成订单的业务流程7.3.3存储过程在外卖点餐系统中的相关操作教材第140—141页079/140【例7-15】创建存储过程create_order，用于生成订单。生成订单的业务流程如下：根据用户编号、商家编号从购物车表中查询所有订单明细数据。根据用户编号、商家编号、订单日期、送货地址编号、订单总额向订单表中添加一条记录。自动获取生成的新订单的主键。将从购物车中查询出的所有商品信息批量添加到订单明细表中。根据用户编号、商家编号删除购物车表中的数据。参数为：用户编号、商家编号、订单日期、送货地址编号、订单总额。教材例7-15：create_order(1/2)7.3.3存储过程在外卖点餐系统中的相关操作教材第139—141页080/140教材语句CREATEPROCEDUREcreate_order(INuidVARCHAR(20),INbidINT,INodVARCHAR(20),INdidINT,INtotalDECIMAL(7,2))BEGINDECLAREdoneBOOLEANDEFAULTFALSE;DECLAREidINT;--存储自动生成的订单编号。DECLAREfidINT;--存储购物车中的食品编号。DECLAREquINT;DECLAREcurCURSORFOR--业务1:查询购物车订单明细。SELECTfoodIdfid,quantityquFROMcartWHEREuserId=uidANDbusinessId=bid;DECLARECONTINUEHANDLERFORNOTFOUNDSETdone=TRUE;DECLAREEXITHANDLERFORSQLEXCEPTIONROLLBACK;STARTTRANSACTION;SAVEPOINTsp1;INSERTINTOordersVALUES(NULL,uid,bid,od,total,did,0);--业务2教材例7-15：create_order(2/2)7.3.3存储过程在外卖点餐系统中的相关操作教材第140—141页081/140教材语句SETid=LAST_INSERT_ID();--业务3:获取自动生成的订单编号。OPENcur;--业务4:批量添加订单明细。read_loop:LOOPFETCHcurINTOfid,qu;IFdoneTHENLEAVEread_loop;ENDIF;INSERTINTOorderdetailetVALUES(NULL,id,fid,qu);ENDLOOP;CLOSEcur;DELETEFROMcart--业务5:删除购物车中的数据。WHEREuserId=uidANDbusinessId=bidANDcartId>0;RELEASESAVEPOINTsp1;COMMIT;END7.3.4触发器概述7.3.4触发器概述教材第141页082/1407.3.4触发器概述触发器的定义自动触发机制与存储过程的区别触发器的用途触发器的定义7.3.4触发器概述教材第141页083/140教材定义触发器的定义：触发器(Trigger)是一种特殊类型的存储过程。当有操作影响到触发器保护的数据时，触发器就自动发生。触发器是在特定表上定义的，该表也叫触发器表。触发器自动实施完整性约束7.3.4触发器概述教材第141页084/140触发器可用来对表实施复杂的完整性约束。对触发器所保护的数据进行增、删、改时，相应类型的触发器自动执行。触发器基于一个表创建，但可针对多个表进行操作；针对INSERT、DELETE、UPDATE有三种类型的触发器。触发器与存储过程的区别7.3.4触发器概述教材第141页085/140触发器与存储过程的区别是：存储过程由用户直接调用执行，而触发器不能由用户调用执行，是在对触发器表进行操作时自动触发执行的。触发器的主要用途7.3.4触发器概述教材第141页086/140(1)对数据库中相关的表进行级联修改时提示相关信息；(2)在撤消或回滚违反引用完整性操作时给出提示，防止非法修改数据；(3)完成比检查约束更为复杂的约束操作；(4)比较表修改前后数据之间的差别，并根据这些差别来进行相应的操作；(5)对一个表上的不同操作(INSERT、UPDATE或者DELETE)可采取不同的触发器，对一个表上的相同操作也可调用不同的触发器进行不同的操作。7.3.5触发器在外卖点餐系统中的相关操作7.3.5触发器在外卖点餐系统中的相关操作教材第142—144页087/1407.3.5触发器在外卖点餐系统中的相关操作创建触发器查看触发器修改触发器删除触发器CREATETRIGGER的基本格式7.3.5触发器在外卖点餐系统中的相关操作教材第142页088/140在MySQL中创建触发器使用CREATETRIGGER语句。其命令格式如下：教材语句CREATETRIGGERtrigger_nameBEFORE/AFTERINSERT/UPDATE/DELETEONtable_nameFOREACHROWBEGIN--SQL语句组END;教材例7-16：先建立商家插入记录表7.3.5触发器在外卖点餐系统中的相关操作教材第142页089/140【例7-16】创建一个针对商家表的插入触发器。首先创建一个记录商家插入操作的记录表(business_audit)：教材语句CREATETABLEbusiness_audit(audit_idINTAUTO_INCREMENTPRIMARYKEY,business_idINTNOTNULL,action_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,action_typeVARCHAR(50));教材例7-16：创建商家插入触发器7.3.5触发器在外卖点餐系统中的相关操作教材第142页090/140教材语句DELIMITER$$CREATETRIGGERbusiness_insert_trAFTERINSERTONbusinessFOREACHROWBEGININSERTINTObusiness_audit(business_id,action_type)VALUES(NEW.businessId,'INSERT');END$$DELIMITER;教材例7-16：执行时机与记录结果7.3.5触发器在外卖点餐系统中的相关操作教材第142页091/140说明：上述建立的触发器中的“DELIMITER$$”是声明“$$”为结束标记，当遇到“$$”，存储过程就执行结束。上述建立的触发器为当插入一条新的商家信息后，同时也会在商家插入操作记录表中记录该商家的商家编号以及插入时间等信息，该信息的添加需要在商家信息插入成功后才会执行。教材例7-17：生成订单时插入订单明细7.3.5触发器在外卖点餐系统中的相关操作教材第142页092/140【例7-17】创建一个触发器实现订单生成时插入订单明细。教材语句DELIMITER$$CREATETRIGGERafter_order_insertAFTERINSERTONorderFOREACHROWBEGININSERTINTOorderdetailet(orderId,foodId,quantity)SELECTNEW.orderId,cart.foodId,cart.quantityFROMcartJOINfoodONcart.foodId=food.foodIdWHEREcart.userId=NEW.userId;END$$DELIMITER;教材例7-18：生成订单后移除购物车商品7.3.5触发器在外卖点餐系统中的相关操作教材第143页093/140【例7-18】创建一个触发器实现生成订单时从购物车移除商品。教材语句DELIMITER$$CREATETRIGGERafter_order_generatedAFTERINSERTONorderFOREACHROWBEGINDELETEFROMcartWHEREuserId=NEW.userId;END$$DELIMITER;查看触发器的两种方式7.3.5触发器在外卖点餐系统中的相关操作教材第143页094/140使用SQL语句查看触发器在MySQL中，可以使用SHOWTRIGGERS语句来查看数据库中的所有触发器信息，或者查询information_schema.triggers表来获取特定触发器的详细信息。教材例7-19：查看商家表中的触发器7.3.5触发器在外卖点餐系统中的相关操作教材第143页095/140【例7-19】查看商家表中的触发器。教材语句--方法一SHOWTRIGGERSLIKE'%business%';--方法二SELECT*FROMinformation_schema.triggersWHEREtrigger_schema='takeaway'ANDevent_object_table='business';trigger_schema与event_object_table7.3.5触发器在外卖点餐系统中的相关操作教材第143页096/140方法二中trigger_schema指的是使用的数据库名称，而event_object_table指的是使用的表的名称。MySQL修改触发器的方式7.3.5触发器在外卖点餐系统中的相关操作教材第143页097/140使用SQL语句修改触发器在MySQL中如果需要修改触发器，需要先将对应的触发器删除，然后再创建新的触发器。修改触发器：先删除，再创建7.3.5触发器在外卖点餐系统中的相关操作教材第142—143页098/140教材语句DROPTRIGGERIFEXISTSold_trigger_name;CREATETRIGGERnew_trigger_nameBEFORE/AFTERINSERT/UPDATE/DELETEONtable_nameFOREACHROWBEGIN--新的SQL语句组END;教材例7-20：商家接受订单时更新状态7.3.5触发器在外卖点餐系统中的相关操作教材第143页099/140【例7-20】创建一个触发器实现商家接受订单时更新订单状态。向order表添加一个名为merchant_accepted的字段，数据类型为INT，默认值为0(表示未接受)：教材例7-20：完整触发器实现7.3.5触发器在外卖点餐系统中的相关操作教材第142—144页100/140教材语句ALTERTABLEorderADDCOLUMNmerchant_acceptedBOOLEANDEFAULT0;DELIMITER//CREATETRIGGERafter_merchant_accept_orderAFTERUPDATEONorderFOREACHROWBEGINIFNEW.merchant_accepted=1ANDOLD.merchant_accepted=0THENUPDATEorderSETorderState=1WHEREorderId=NEW.orderId;ENDIF;END//DELIMITER;删除触发器的一般格式7.3.5触发器在外卖点餐系统中的相关操作教材第144页101/140删除触发器使用DROPTRIGGER语句。当用户删除某个表时，所有建立在该表上的触发器都将被删除。删除触发器命令格式如下：教材语句DROPTRIGGER触发器名[,…n]教材例7-21：删除商家表触发器7.3.5触发器在外卖点餐系统中的相关操作教材第144页102/140【例7-21】删除商家表中的触发器。教材语句DROPTRIGGERbusiness_insert_tr;7.4章节结语7.4章节结语教材第144页103/1407.4章节结语数据库编程基础与交互方式视图存储过程、触发器与游标本章总结：数据库编程基础与视图7.4章节结语教材第144页104/140本章节详细介绍了数据库编程的基础知识、视图的操作、存储过程与触发器的使用，以及游标的操作。这些内容是数据库编程的核心概念，对于开发高效的数据库应用程序至关重要。首先，章节阐述了数据库编程的概念和目的，包括数据管理、业务逻辑实现、提高效率和数据安全等方面。接着，介绍了编程语言与数据库交互的几种方式，包括ODBC/JDBC、ORM框架和原生SQL查询。在视图部分，章节解释了视图的定义、建立、删除和查询操作，以及视图的可更新性。视图作为一个虚拟表，可以简化复杂的查询操作，同时提高数据的安全性。本章总结：存储过程、触发器与游标7.4章节结语教材第144页105/140存储过程和触发器部分，章节详细介绍了存储过程的定义、优势和在外卖点餐系统中的应用，以及触发器的概述和应用。存储过程可以提高数据库操作的性能和安全性，而触发器则可以自动执行数据库完整性约束。最后，章节介绍了游标的定义和使用，游标允许程序逐行处理SELECT语句返回的多条记录，这对于处理复杂的查询结果非常有用。通过本章节的学习，读者应该能够掌握数据库编程的基本技能，包括视图和存储过程的创建和管理，触发器的使用，以及游标的操作。这些技能对于开发和维护数据库应用程序非常重要。作业及思考题作业及思考题教材第144页106/140作业及思考题选择题1—7填空题8—10思考题11—15作业及思考题：第1题作业及思考题教材第144页107/14011.关于数据库视图，下列哪个说法是正确的()？2A.视图是数据库中实际存储数据的表。3B.视图不能用于插入操作。4C.视图是虚拟表，其内容由查询定义。5D.视图可以跨不同的数据库。作业及思考题：第2题作业及思考题教材第144页108/14012.在SQL中，创建视图的命令是()？2A.CREATEVIEW3B.MAKEVIEW4C.INITVIEW5D.STARTVIEW作业及思考题：第3题作业及思考题教材第145页109/14013.存储过程与普通SQL语句相比，哪个说法是正确的()？2A.存储过程可以减少网络流量。3B.存储过程不能包含控制流语句。4C.存储过程总是比单个SQL语句执行得慢。5D.存储过程不能被调试。作业及思考题：第4题作业及思考题教材第145页110/14014.创建存储过程的SQL命令是()？2A.CREATEPROCEDURE3B.MAKEPROCEDURE4C.INITPROCEDURE5D.STARTPROCEDURE作业及思考题：第5题作业及思考题教材第145页111/14015.在SQL中，创建触发器的命令是()？2A.CREATETRIGGER3B.MAKETRIGGER4C.INITTRIGGER5D.STARTTRIGGER作业及思考题：第6题作业及思考题教材第145页112/14016.关于数据库游标，下列哪个说法是正确的()？2A.游标用于一次性获取查询结果的所有行。3B.游标允许逐行处理查询结果。4C.游标可以提高数据检索的性能。5D.游标只能在存储过程中使用。作业及思考题：第7题作业及思考题教材第145页113/14017.在SQL中，声明游标的命令是()？2A.DECLARECURSOR3B.MAKECURSOR4C.INITCURSOR5D.STARTCURSOR作业及思考题：第8—10题作业及思考题教材第145页114/14018.对用户来讲视图就是关系，对视图的所有操作都会转换成对基本表的操作，原则上可以进行增删改操作的视图称作___________视图。29.触发器是一类特殊的存储过程，其中DML触发器在执行时派生出的两个临时表是________和________。310.在游标(CURSOR)应用中判定FETCH语句是否成功读的全局变量是_____________。作业及思考题：第11—13题作业及思考题教材第145页115/140111.解释存储过程的优点以及它们如何提高数据库性能。212.描述触发器的用途和它们在数据库管理中的重要性。313.讨论游标在数据库操作中的使用场景及其潜在的性能影响。作业及思考题：第14题作业及思考题教材第145页116/140114.在表score(student-id，course-id，grade)上建立一个触发器，当向成绩表插入一条记录时，检查被插入的成绩是否大于等于0和小于等于100。若不满足该条件，不允许插入记录，创建完成后检验insert触发器的作用。作业及思考题：第15题作业及思考题教材第145页117/140115.在数据库中有两张表：商品表(商品号，商品名，商品类型，价格)和销售表(商品号，销售时间，销售数量，销售单价)。用户需统计指定年份每类商品的销售总数量和销售总利润，要求只列出销售总利润最多的前三类商品的商品类别、销售总数量和销售总利润。2(1)为了完成该统计操作，请按要求编写存储过程p_Sum。3(2)调用该存储过程统计2018年每类商品的销售总数量和销售总利润。实验5视图的应用实验5视图的应用教材第145页118/140实验5视图的应用实验内容、目的与方法视图设计与数据操作实验报告实验5：内容、目的与方法实验5视图的应用教材第145—146页119/1401实验内容：在实验2和实验3的基础上定义视图，并在视图上完成查询，以及插入、更新和删除操作。2实验目的：掌握视图定义和应用的方法，体会视图和基本表的异同之处。3实验方法：在实验2和实验3的基础上用CREATEVIEW命令定义视图，然后使用SELECT命令完成查询，使用INSERT、UPDATE和DELETE命令分别完成插入、更新和删除操作。实验5：建立视图(1/2)实验5视图的应用教材第146页120/14011.在实验2建立的基本表的基础上，按如下要求设计和建立视图：2基于单个表按投影操作定义视图。3基于单个表按选择操作定义视图。4基于单个表按选择和投影操作定义视图。实验5：建立视图(2/2)实验5视图的应用教材第146页121/1401基于多个表根据连接操作定义视图。2基于多个表根据嵌套查询定义视图。3定义含有虚字段的视图。实验5：查询、更新与实验报告实验5视图的应用教材第146页122/14012.分别在定义的视图上设计一些查询(包括基于视图和基本表的连接或嵌套查询)。23.在不同的视图上分别设计一些插入、更新和删除操作，分情况讨论哪些操作可以成功完成，哪些操作不能完成，并分析原因。34.在实验报告中要给出具体的视图定义要求和操作要求，并针对各种情况做出具体的分析和讨论。实验5：CREATEVIEW核心格式实验5视图的应用教材第146页123/140定义视图的命令是CREATEVIEW，其核心是SQLSELECT查询，基本命令格式如下：教材语句CREATEVIEW<视图名>AS<SELECT-查询块>实验6游标的应用实验6游标的应用教材第146页124/140实验6游标的应用MySQL游标嵌套游标任务DECLARE、OPEN、FETCH、CLOSE实验6：内容、目的与方法实验6游标的应用教材第146页125/1401实验内容：在MySQL环境下使用MySQL的游标功能完成指定的操作。2实验目的：理解和掌握游标的使用方