第9章电子商务系统中数据库应用技术

1、2022年5月5日星期四第9章 电子商务系统中数据库应用技术 本章要点v 存储过程v 触发器v 数据库系统体系结构v 电子商务系统中的数据库技术v 电子商务系统中常用的数据库连接技术v 流行的商业数据库介绍v 开发数据库应用系统2022年5月5日星期四4.1 存储过程v存储过程：使用SQL语句、变量定义语句、流程控制语句等编写的独立程序模块，被预先编译好，以独立的数据库对象存储在数据库服务器端的数据库中。存储过程可以接受参数，返回结果值，使用时可以被应用程序调用。 v存储过程的特点：1.存储过程能够减少网络流量 2.存储过程能够实现较快的执行速度 3.保证数据库的安全性和完整性 4.简化开发和

2、系统维护 4.1.1存储过程的概念4.1.2 编写存储过程（SQL/PSM） v以PSM为宿主语言，描述存储过程的创建 1声明存储过程 CREATE PROCEDURE （参数） 2赋值语句 在存储过程中用关键字SET来给变量赋值 例如：SET Status=1 表示给变量Status赋值为1注意赋值语句中变量和值之间的数据类型匹配或一致。3顺序执行 存储过程的过程体中的语句在没有遇到跳转语句及循环语句时，按照语句先后顺序依此执行。4条件分支语句语法结构如下IF THEN ELSE END IF语句体用BEGIN-END 封装条件分支语句中还可以嵌套条件语句，格式如下IF THEN ELSE

3、IF THEN ELSE IF THEN 。END IF；【例9.1】 创建一个存储过程，判断是否为订单用户，否则将该用户删除v解：CREATE PROCEDURE DEL_USERv( IN UserID CHAR(6) ,v OUT Status INTEGER ,v OUT StatusMsg CHAR VARYING(100)v BEGIN ATOMIC v-这里的ATOMIC确保将整个程序块当作一个原子单元来执行，要么执行完成，v要么执行的部分结果回滚。相当于SQL-Server2005中提供的事务概念。v DECLARE message CHAR VARYING(50) DEFAU

4、LT 遇到问题;v DECLARE Success INTEGER DEFAULT 0;v DECLARE Failure INTEGER DEFAULT -1;vIF 0 (SELECT COUNT(*) FROM ORDERS C WHERE C. UserID= UserID) vTHENv BEGINv SET StatusMsg=用户存在订单,不能删除用户;v SET Status=Failure;v ENDvELSEv BEGIN- - 分块可以限制错误处理程序的范围v DECLARE UNDO HANDLER FOR SQLEXCEPTIONv BEGINv SET Status

5、Msg= message | | cannot delete ;v SET Status=Failure;v ENDv DELETE FROM USERS WHERE UserID= UserID;v ENDvEND IF;v END;v【例9.2】上例中在SQL-Server2005中的存储过程的写法vCREATE PROCEDURE DEL_USERv( UserID CHAR(6) ,vStatus INTEGER OUTPUT ,vStatusMsg CHAR VARYING(100) OUTPUT)v/ SQL-Server2005中的变量使用符号来标明/vASvBEGINvDECL

6、ARE message CHAR VARYING(50)vSET message=遇到问题vDECLARE Success INTEGERvSET Success = 0vDECLARE Failure INTEGERvSET Failure= -1vIF 0 (SELECT COUNT(*) FROM C WHERE C. UserID= UserID)vBEGINSET StatusMsg=用户存在订单,不能删除用户SET Status=FailureENDELSE-BEGIN- - 分块可以限制错误处理程序的范围-DECLARE UNDO HANDLER FOR SQLEXCEPTION

7、BEGINSET StatusMsg= message + cannot deleteSET Status=Failure-ENDDELETE FROM USER WHERE UserID= UserID;ENDENDGO 5多条件分支 CASE WHEN 值1 THEN WHEN 值2 THEN 。WHEN 值n THEN ELSEEND CASE; 【例9.3】创建一个带多条件分支的存储过程,根据图书的类型修改某个出版社的图书折扣价格.CREATE PROCEDURE Update_DiscountPrice( IN Categoryid CHAR(4),IN PublisherID CH

8、AR(4)LANGUAGE SQLBEGINDECLARE SQLSTATE CHAR(5);DECLARE EXIT HANDLER FOR not_found- DECLARE EXIT HANDLER是标准制定的语句，不同的厂商支持不同SIGNAL SQLSTATE 02444;CASE CategoryidWHEN TP THEN UPDATE BOOKSET DiscountPrice = Price * 0.8WHERE PublisherID = PublisherID;WHEN H31 THEN 第9章 电子商务系统中数据库应用技术UPDATE BOOKSET Discount

9、Price = Price * 0.7WHERE PublisherID = PublisherID;ELSEUPDATE BOOKSET DiscountPrice = Price * 0.75WHERE PublisherID = PublisherID;END CASE;END ; 第9章 电子商务系统中数据库应用技术6循环结构 标准的WHILE 循环结构 WHILE DO END WHILE； 标准的REPEAT循环结构REPEAT UNTIL END REPEAT；第9章 电子商务系统中数据库应用技术7. 在存储过程中使用游标v【例9.4】 创建一个带游标的存储过程，输出用户的订单号

10、。v CREATE PROCEDURE USER_ORDERv( IN UserID CHAR(6),v OUT OrderID CHAR(9) )v BEGIN ATOMIC v DECLARE cur_order INSENSITIVE CURSOR FORv SELECT OrderID FROM ORDERS WHERE UserID= UserIDv FOR READONLY;v-定义游标，生成游标所对应的结果集v OPEN cur_orderv-打开游标v FETCH NEXT cur_order INTO :OrderID;v -将游标值放入变量中v EXEC SQL CLOSE

11、 cur_orderv END 第9章 电子商务系统中数据库应用技术9.2 9.2 触发器触发器9.2.1 9.2.1 触发器的概念触发器的概念【定义9.2】触发器是数据库模式的一个元素，能够由系统自动执行对数据库中对象的一些操作。也称为ECA规则（event-condition-action），即事件条件规则。触发器的结构如下：ON 事件 IF 前提条件 THEN 行为第9章 电子商务系统中数据库应用技术在传统和新的数据库技术中，触发器有以下的作用： 维护数据库中数据的参照完整性。 执行更复杂的约束。 用于业务规则的设定。 在主动数据库中可以用来监控物理对象的状态信息，提高系统的反应能力。

12、对异常处理的模块化。 第9章 电子商务系统中数据库应用技术9.2.2 9.2.2 触发器的语法结构触发器的语法结构 本节主要介绍SQL:1999标准中的通用语法，这有利于理解触发器的工作原理和结构。不同的数据库厂商在自己的产品中都包含了各自的触发器，具体应用可以在标准的基础上参照不同产品的参考手册。 首先用trigger-name确定触发器的名称。 利用BEFORE或AFTER关键字定义BEFORE或AFTER触发器，确定系统在事件发生之前还是之后检查由WHEN确定的前提条件。 触发事件。 目标表。第9章 电子商务系统中数据库应用技术 触发器的粒度。 触发的前提条件。触发器执行的语句体。 RE

13、FERENCING子句。在触发器定义中需要注意的几点： BEFORE触发器。【例9.5】设计一个触发器，如果某本书在订单表中的订购量大于图书的库存量，不能接受新的订单。 解：可以设计一个触发器，它监视插入到订单细节表ORDERDETAIL (OrderID, BookID, Quantity)中的每个元组，如果计算超过这本书的库存量则拒绝插入操作。CREATE TRIGGER trigger-StockCheck BEFORE INSERT ON ORDERDETAIL REFERENCING NEW AS T第9章 电子商务系统中数据库应用技术FOR EACH ROW WHEN(SELECT

14、 COUNT(*) FROM ORDERDETAIL O WHERE O.OrderID=T.OrderID AND O. BookID=T. BookID )=(SELECT TotalNum FROM BOOK BWHERE B. BookID= T. BookID) ROLLBACK AFTER触发器。【例9.6】下面的例子说明如何用AFTER触发器来完成一个系统业务的自动执行。监控图书表的折扣价不能过低，如果修改的折扣价低于5折,要按5折重新调整折扣价。 CREATE TRIGGER triggerlimitDiscount AFTER UPDATE OF DiscountPrice

15、ON BOOK REFERENCING NEW AS N OLD AS O FOR EACH ROW 第9章 电子商务系统中数据库应用技术 WHEN(O. Price-N. DiscountPrice) 0.05* O. Price) UPDATE BOOK SET DiscountPrice= Price*0.5 WHERE BookID = O. BookID INSTEAD OF触发器【例9.7】 下面是一个INSTEAD OF触发器的例子，创建一个视图包括图书编号,出版社。触发器用来完成将删除的图书的出版社设置为空。下面的例子是INSTEAD OF触发器在Oracle DBMS中的使用

16、方法。分析：首先建立符合要求的视图CREATE VIEW BOOKIN( BookID, Name ) ASSELECT BookID, Name FROM BOOK B, PUBLISHER PWHERE B. PublisherID= P. PublisherID第9章 电子商务系统中数据库应用技术 语句级的触发器。【例9.8】在日志中记录调整的日期和所有书目的平均价CREATE TRIGGER RecordNewAverage AFTER UPDATE OF DiscountPrice ON BOOK FOR EACH STATEMENT INSERT INTO LOG VALUES (

17、 CURRENT_DATE, (SELECT (AVG(DiscountPrice) FROM BOOK)第9章 电子商务系统中数据库应用技术9.39.3数据库系统体系结构数据库系统体系结构1集中式数据库系统图9-1 单用户集中式DBS第9章 电子商务系统中数据库应用技术图9-2 主从式DBS结构第9章 电子商务系统中数据库应用技术2.客户/服务器数据库系统图9-3客户/服务器DBS的结构图第9章 电子商务系统中数据库应用技术3.分布式数据库系统 分布式数据库系统分为三类：同构同质型DDBS，同构异质型DDBS，异构DDBS。（1）同构同质型DDBS：是指各个数据库节点都采用同一类型的数据模型

18、，并且是同一型号数据库管理系统。（2）同构异质型DDBS：是指各个数据库节点都采用同一类型的数据模型，但是数据库管理系统是不同型号的。（3）异构型DDBS：是指各个数据库节点的数据模型是不同的类型。 第9章 电子商务系统中数据库应用技术图9-4分布式数据库系统的结构第9章 电子商务系统中数据库应用技术4. 并行数据库系统。 根据并行DBMS的结构不同，并行DBS有三种主要结构，如图9-5所示：1）共享存储器2）共享磁盘3）无共享 图9-5-1共享存储器 图9-5-2共享磁盘 第9章 电子商务系统中数据库应用技术图9-5-3 不共享第9章 电子商务系统中数据库应用技术5Web数据库系统图9-6

19、web数据库系统结构 第9章 电子商务系统中数据库应用技术9.49.4电子商务系统中的数据库技术电子商务系统中的数据库技术9.4.1电子商务相关技术 1客户端技术的发展 静态文档图9-9 主页和网页之间的树型结构第9章 电子商务系统中数据库应用技术 样式表CSS在客户端页面的应用 插件技术在客户端页面的应用 DHTML在客户端页面的应用2 服务器端技术的发展 CGI（Common Gateway Interface）技术服务器端的脚本语言。 PHP ASP JSP第9章 电子商务系统中数据库应用技术3. Web2.0Blog博客/网络日志。RSS站点摘要。Wiki百科全书。TAG网摘。SNS社

20、会网络（Social Network Software） P2P对等联网。IM即时通讯。第9章 电子商务系统中数据库应用技术9.4.2 网络数据库的发展 根据Web与数据库服务器连接使用的不同技术，网络数据库的发展经历了以下几个阶段。基于通用网关接口CGI图9-11 基于CGI的web应用的结构 第9章 电子商务系统中数据库应用技术基于服务器扩展的API图9-12 API的应用流程基于JDBC的Web数据库技术第9章 电子商务系统中数据库应用技术总结上面的数据库访问技术，与传统方式相比，通过WWW访问数据库的优点在于：利用现有的浏览器软件，无需开发数据库应用系统的前端界面。标准统一。支持跨平台

21、。降低应用系统的使用费用。便于数据资源共享。 第9章 电子商务系统中数据库应用技术9.4.3 电子商务系统中常用的数据库系统结构本节主要讨论电子商务系统中常用的数据库的体系结构。 Client/Server数据库结构。 浏览器/服务器数据库结构。图9-13 B/S数据库系统模式结构第9章 电子商务系统中数据库应用技术四层B/S模式图9-14 四层B/S模式体系结构第9章 电子商务系统中数据库应用技术9.5 电子商务系统中常用的数据库连接技术9.5.1 ODBC技术1ODBC的基本思想ODBC具有以下的特点：1） 使用户程序有很高的互操作性，相同的目标代码适用于不同的DBMS；2） 由于ODBC

22、的开放性，为客户机/服务器结构提供了有效的支持；3） 由于应用与底层网络环境和DBMS分开，简化了开发维护上的困难。第9章 电子商务系统中数据库应用技术 2ODBC的体系结构 ODBC驱动程序管理器SQL Server驱动程序Sybase驱动程序等DB2驱动程序Oracle驱动程序Foxpro驱动程序SQLServe数据源Oracle数据源FoxPro数据源Sybase数据源DB2数据源DBDBDBDBDB图9-15 ODBC的的体系结构第9章 电子商务系统中数据库应用技术ODBC由4部分构成，其主要功能如下：（1）ODBC数据库应用程序（Application）（2）ODBC驱动程序管理器（

23、Driver Manager） （3）DBMS驱动程序（DBMS Driver） （4）数据源（Data Source） 数据源分为3类： 用户数据源 系统数据源 文件数据源 第9章 电子商务系统中数据库应用技术 3ODBC的数据源的配置和使用（1）打开ODBC 数据源管理器（2）配置ODBC 数据源（3）ODBC 数据源的连接第9章 电子商务系统中数据库应用技术9.5.2 OLE DB技术 OLE DB 是一种基于组件对象模型（Component Object Model,COM）技术标准，目的是为用户提供一种统一的数据访问接口。 OLE DB 标准的具体实现是一组C+ API 函数，就像O

24、DBC 标准中的ODBC API 一样，不同的是OLE DB 的API 是符合COM 标准的。 运用OLE DB方法建立应用程序与数据库的连接， 不需要在ODBC管理器中创建 ODBC DSN数据源。在ASP中可以直接编写如下的脚本和数据源建立连接。 第9章 电子商务系统中数据库应用技术9.5.3 JDBC技术1. JDBC技术概述 JDBC是一种可用于执行SQL语句的JavaAPI(Application Programming Interface，应用程序设计接口)， JDBC给数据库应用开发人员、数据库前台工具开发人员提供了一种标准的应用程序设计接口，使开发人员可以用纯Java语言编写完

25、整的数据库应用程序。具体如何实现对底层数据库的操作依赖于具体的JDBC驱动程序。 第9章 电子商务系统中数据库应用技术2JDBC的主要功能 简单地说，JDBC能完成下列功能：首先和一个数据库所在的DBMS建立连接；然后向DBMS发送SQL语句；最后处理DBMS返回的结果。当程序调用JDBC接口访问数据库的时候，JDBC驱动程序负责将这些程序调用转化为符合底层数据库交互协议的信息，并将这些信息发送给底层数据库管理系统，由底层数据库管理系统执行相应的操作，最后处理返回的结果。第9章 电子商务系统中数据库应用技术3. JDBC的驱动程序类型 根据JDBC的驱动程序的实现机制，可以将JDBC的驱动程序

26、分成4种类型： JDBC-ODBC桥。 图9-26 JDBC-ODBC桥的原理图第9章 电子商务系统中数据库应用技术 JDBC驱动程序直接访问数据库。 图9-27 JDBC驱动程序直接访问数据库的原理图第9章 电子商务系统中数据库应用技术 JDBC网络纯Java驱动器 图9-28 JDBC网络纯Java驱动器工作示意图第9章 电子商务系统中数据库应用技术本地API驱动。 图9-29本地API驱动的工作原理第9章 电子商务系统中数据库应用技术4.使用JDBC-ODBC桥来建立数据库连接的方法 以网上书店的BookStore数据库为例，下面详细说明在SQL Server中如何利用JDBC-ODBC

27、桥来建立数据库连接。首先在ODBC管理器中建立数据源，具体的建立方法参见本节9.5.1中第3部分关于如何建立数据源。这里不做赘述。 加载JDBC-ODBC桥驱动程序。语句片段如下 Class.forNname(“sun.jdbc.JdbcOdbcDrive”); 通过数据源建立与数据库的连接。 第9章 电子商务系统中数据库应用技术5.直接通过JDBC连接数据库 直接通过JDBC连接数据库首先需要安装不同数据库系统的JDBC驱动程序，SUN公司在官方网站上维护了一份各种JDBC驱动程序的列表，网址是：http:/ 用户只需要输入JDBC的版本、支持的数据库软件、驱动程序属于那类就可以找到满足条件

28、的JDBC驱动程序列表。 下载的文件是一个压缩文件，解压后可以看到文件Merlia.jar是一个驱动程序包。为了方便的使用驱动程序， 可以将文件Merlia.jar所在的完整路径加入到操作系统的环境变量的系统变量classpath中。若文件Merlia.jar所在的完整路径是：c:j2sdklib，那就在classpath的变量值的末尾加上“; ;c:j2sdklib”。第9章 电子商务系统中数据库应用技术9.5.4对象/关系的映射技术1对象/关系的映射的概念2对象/关系的映射的优势3当前基于Java的主流对象-关系映射中间件产品第9章 电子商务系统中数据库应用技术9.6.1 微软公司的SQL

29、 Server SQL Server是Microsoft公司的一个关系数据库管理系统，最早起源于1987年的Sybase SQL Server。SQL Server最初是由Microsoft、Sybase 和Ashton-Tate三家公司共同开发的，1988年Microsoft公司、Sybase公司和Aston-Tate公司把该产品移植到OS/2上。后来Aston-Tate公司退出了该产品的开发，而Microsoft公司、Sybase公司则签署了一项共同开发协议，1992年将SQL Server移植到了Windows NT平台上。在SQL Server 4版本发行以后，Microsoft公司和

30、Sybase公司在SQL Server的开发方面分道扬镳，取消了合同，各自开发自己的SQL Server。Microsoft公司专注于Windows NT平台上的SQL Server开发，而Sybase公司则致力于UNIX平台上的SQL Server的开发。第9章 电子商务系统中数据库应用技术SQL Server的特点：易于安装和学习。SQL Server除了具有扩展性、可靠性以外，还具有可以迅速 开发新的互联网系统的功能。 SQL Server 2000和2005非常明显的改进就是增加了OLAP(联机分析处理)功能。 SQL Server 2000和2005简化了管理、优化工作，并且增强了迅

31、速、成功的部署在线商务应用程序所需的可靠性和伸缩性。增强了在线商务的功能。第9章 电子商务系统中数据库应用技术9.6.2 OracleOracle的特点：支持大数据库、多用户的高性能的事务处理。Oracle遵守数据存取语言、操作系统、用户接口和网络通信协议的工业标准。实施安全性控制和完整性控制。支持分布式数据库和分布处理。具有可移植性、可兼容性和可连接性。第9章 电子商务系统中数据库应用技术9.6.3 IBM DB2IBM DB2的特点：支持面向对象的编程支持多媒体应用程序备份和恢复能力支持存储过程和触发器，用户可以在建表时显示的定义复杂的完整性规则支持异构分布式数据库访问支持数据复制第9章 电子商务系统中数据库应用技术9.6.4 MYSQL数据库MySQL数据库的特性：开发语言为C/C+，支持多种编译器。具有良好的内存管理机制，尤其是内存泄漏的管理支持多种平台，如AIX、Solaris、HP、FreeBSD、Tru64、SGI、Windows等使用多线程的技术灵活、安全的权限和密码系统，密码在传输中加密传送，允许主机端验证密码第9章 电子商务系统中数据库应用技术9.6.5 Sybase数据库Sybase数据库的特点： 它是基于客户/服务器体系结构的数据库。 它是真正开放的数据库。 它是一种高性能的