《数据库编程基础》PPT课件.ppt

第章 数据库编程基础,本章主要内容,1. 游标 .动态SQL .存储过程 .触发器,SQL的宿主使用,SQL不仅可以作为独立的数据语言直接以交互的方式使用；SQL还可以作为子语言嵌入在宿主语言中使用，这里所说的宿主语言就是指我们常见的高级程序设计语言，如C语言等。,把SQL嵌入到宿主语言中使用必须要解决以下三个方面的问题：,嵌入识别问题：宿主语言的编译程序不能识别SQL语句，所以首要的问题就是要解决如何区分宿主语言的语句和SQL语句； 宿主语言与SQL语言的数据交互问题：SQL语句的查询结果必须能够交给宿主语言处理，宿主语言的数据也要能够交给SQL语句使用； 宿主语言的单记录与SQL的多记录的问题：宿主语言一般一次处理一条记录，而SQL常常处理的是记录（元组）的集合，这个矛盾必须解决。,如何解决嵌入识别问题？,为了区分主语言和SQL语句，为SQL语句加一个识别前缀，常用前缀格式是： EXEC SQL 比如： EXEC SQL INSERT INTO 职工 VALUES(WH8,E20,1560) 计算机的编译系统如何识别这些前缀？,预编译技术,在编译之前首先将SQL语句转换为主语言的合法函数调用或合法语句，这个过程称为预编译。,嵌入了SQL的应用程序的执行过程,INCLUDE SQLCA 如：sqlca.sqlcode 什么是主变量？,如何解决宿主语言和 SQL语言的数据交换问题？,设立数据通讯区、引入主变量的概念,主变量的说明格式,BEGIN DECLARE SECTION 主变量说明 END DECLARE SECTION,主变量说明的例：,EXEC SQL BEGIN DECLARE SECTION ； char whnumb5 char city12 int wh_area EXEC SQL END DECLARE SECTION；,使用主变量的例子：,UPDATE 仓库 SET 面积 = :wh_area WHERE 仓库号 = :whnumb ；,Cursor,DECLARE CURSOR OPEN FETCH CLOSE DEALLOCATE,为了解决宿主语言一次只能处理一条记录，而SQL语言一次处理多条记录的矛盾，引入了Cursor的概念。相关的语句有：,DECLARE CURSOR语句的格式是：,DECLARE INSENSITIVE SCROLL CURSOR FOR FOR READ ONLY|UPDATE OF 列名，列名,INSENSITIVE说明用数据的临时拷贝来定义游标，所有对游标的请求都反映在这个临时表上，因此这时的游标实际上是不允许修改的； SCROLL说明可以用所有的方法来存取数据，允许删除和更新（假定没有使用INSENSITIVE选项）； FOR READ ONLY或FOR UPDATE说明游标为只读的或可修改的。 定义一个游标（文件），它的内容是的查询结果（多个记录组成的临时表）。,OPEN语句的格式是：,OPEN ,该语句的功能是打开或启动指出的游标，该游标名是用DECLARE CURSOR语句已经定义好的。执行该语句意味着执行在DECLARE CURSOR语句中定义的SELECT查询，并使游标指针指向查询结果的第一条记录。,从游标中读记录,FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n FROM INTO :,: ,该语句的功能是取出游标的当前记录并送入主变量，同时使游标指针指向下一条记录（NEXT，或根据选项指向某条记录）。这里的游标必须是已经说明并打开了的，INTO后的主变量要与在DECLARE CURSOR中SELECT的字段相对应。,关闭游标,CLOSE ,释放游标,DEALLOCATE 该命令的功能是删除由DECLARE说明的游标。该命令不同于CLOSE命令，CLOSE命令只是关闭游标，需要时还可以重新打开；而DEALLOCATE命令则要释放和删除与游标有关的所有数据结构和定义。,理解游标,与程序设计语言中的文件相对照： DECLARE CURSOR相当于说明了一个文件； OPEN相当于打开文件； FETCH相当于读一条记录； CLOSE相当于关闭文件； DEALLOCATE语句相当于删除文件。,使用游标的一个C语言程序段, EXEC SQL BEGIN DECLARE SECTION char whnumb5 char city12 int wh_area EXEC SQL END DECLARE SECTION EXEC SQL DECLARE c1 CURSOR FOR SELECT 仓库号,城市,面积 FROM 仓库 WHERE 面积 = :wh_area EXEC SQL OPEN c1 while ( SQLCA.sqlcode 100 ) EXEC SQL FETCH c1 INTO :whnumb,:city,:wh_area EXEC SQL CLOSE c1 ,利用游标进行删除和更新操作,在T-SQL中，CURSOR不仅仅可以用来浏览查询结果，还可以用UPDATE语句修改CURSOR对应的当前行或用DELETE命令删除对应的当前行。,使用游标的UPDATE命令,UPDATE SET =|NULL ,=|NULL WHERE CURRENT OF ,使用游标的DELETE命令,DELETE FROM WHERE CURRENT OF ,2.动态SQL,动态SQL语句的划分 动态定义功能 动态操作功能 动态查询功能 举例,动态SQL语句的划分,没有参数、没有返回结果的SQL语句，这类语句主要是建立数据库对象的语句，如动态生成的CREATE TABLE语句； 有参数、但没有返回结果的SQL语句，这类语句主要是完成数据库操作的语句，如动态生成的INSERT、UPDATE和DELETE语句； 有参数、有返回结果的SQL语句，这类语句主要是对数据库进行动态查询的语句，也称作动态游标（DYNAMIC CURSOR）语句。,动态定义功能,一般格式是： EXECUTE IMMEDIATE SQLStatement 其中SQLStatement是构成合法SQL语句的字符串（一般应该是变量）,动态操作功能,这种格式的动态SQL语句实际包含了两条语句，第一条是准备SQL的语句： PREPARE SQLSA FROM SQLStatement 第二条是执行SQLSA中准备好的SQL语句： EXECUTE SQLSA USING ParameterList 其中： SQLSA是类似于SQLCA的系统对象变量 SQLStatement含有合法SQL语句的字符串 ParameterList传递参数的主变量表,动态查询功能,一般格式包括： 说明动态游标的语句 DECLARE Cursor DYNAMIC CURSOR FOR SQLSA 为动态游标准备SQL语句 PREPARE SQLSA FROM SQLStatement 打开动态游标的语句 OPEN DYNAMIC Cursor USING ParameterList 从游标读记录的语句 FETCH Cursor INTO HostVariableList 关闭游标的语句 CLOSE Cursor,3.存储过程,客户/服务器数据库与传统的数据库结构的一个很重要的区别是，在传统的数据库中只存放数据，所有的应用程序都在用户端，都与用户实际运行的应用程序捆绑在一起；而在客户/服务器结构的数据库中，在数据库中还可以存放程序，即存储过程。,基本概念,存储过程是事先编好的、存储在数据库中的程序，这些程序用来完成对数据库的指定操作。,系统存储过程,SQL Server本身提供了一些存储过程，用于管理SQL Server和显示有关数据库和用户的信息，把这些存储过程称之为系统存储过程。,用户存储过程,用户也可以编写自己的存储过程，并把它存放在数据库中。这样安排的主要目的就是要充分发挥数据库服务器的功能，尽量减少网络上的堵塞。,创建存储过程,CREATE PROCedure procedure_name ; number parameter data_type = default , AS sql_statement,s procedure_name：给出存储过程名； s number：对同名的存储过程指定一个序号； s parameter：给出参数名； s data_type：指出参数的数据类型； s = default：给出参数的默认值； s sql_statement：存储过程所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。,例：创建一个最简单的存储过程：,CREATE PROCedure sp_getemp;1 AS SELECT * FROM 职工,例：带参数的存储过程：,CREATE PROC sp_getemp;2 (salary int) AS SELECT * FROM 职工 WHERE 工资 salary,存储过程一般用来完成数据查询和数据处理操作，所以在存储过程中不可以使用创建数据库对象的语句，即在存储过程中一般不能含有以下语句： CREATE TABLE CREATE VIEW CREATE DEFAULT CREATE RULE CREATE TRIGGER CREATE PROCEDURE,执行存储过程格式,EXECute = =|,例：执行带参数的存储过程 sp_getemp;2,execute sp_getemp;2 1240,存储过程的返回值和状态信息,无论什么时候执行存储过程，总要返回一个结果码，用以指示存储过程的执行状态。如果存储过程执行成功，返回的结果码是0；如果存储过程执行失败，返回的结果码一般是一个负数，它和失败的类型有关。我们在创建存储过程时，也可以定义自己的状态码和错误信息。,CREATE PROCedure sp_getemp;3 (salary int =NULL) AS IF salary IS NULL BEGIN PRINT 必须提供一个数值作参数！ RETURN 13 END IF NOT EXISTS (SELECT * FROM 职工 WHERE 工资 salary) BEGIN PRINT 没有满足条件的记录！ RETURN -103 END SELECT * FROM 职工 WHERE 工资 salary RETURN 0,例：带参数和返回状态值的存储过程。,例：执行以上存储过程。,DECLARE status int EXECUTE status=sp_getemp;3 1200 print status,存储过程的修改和删除,修改存储过程的语句是（一般格式）： ALTER PROCedure procedure_name ; number parameter data_type = default , AS sql_statement 删除存储过程的语句是： DROP PROCedure procedure_name 注意：删除存储过程的语句中不能指定序号。也就是说，该语句将同时删除同名的所有存储过程。,要用好存储过程,存储过程是客户/服务器机制的一个重要组成部分，如果使用客户/服务器机制的数据库管理系统，但是不理解存储过程或没有充分利用存储过程，那将使客户/服务器机制的功能大打折扣，使系统的整体性能可能降低很多。,4. 触发器,触发器可以看作是一类特殊的存储过程，它在满足某个特定条件时自动触发执行。,触发器是为表上的更新、插入、删除操作定义的，也就是说当表上发生更新、插入或删除操作时触发器将执行。,触发器的用途,1. 更新传播：触发器可以通过级联的方式对相关的表进行修改。比如，对父表的修改，可以引起对子孙表的一系列修改，从而保证数据的一致性和完整性。 2. 触发器可以禁止或撤消违反参照完整性的修改。 3. 触发器可以强制比用CHECK约束定义更加复杂的限制。 4.转换约束：在数据更新前后比较值的完整性约束，例如“工资增加不能超过10%”。 5.例外报告：例如可以不直接拒绝工资增加超过10%，而是由触发器建立一个例外记录，并通知管理员。 6.审计追踪：创建一个事务的历史记录。,触发器是依附于表的数据库对象,一个触发器和三部分内容有关： 定义触发器的表 激活触发器的数据操作语句 触发器要采取的动作,建立触发器的语句,CREATE TRIGGER trigger_name ON table FOR INSERT | UPDATE | DELETE AS IF UPDATE(column) AND | OR UPDATE(column) sql_statement,s trigger_name：给出了触发器的名称； s table：说明了定义触发器的表或视图； s FOR INSERT | UPDATE | DELETE ：说明了激活触发器的数据操作语句； s IF UPDATE(column)：对应于UPDATE类触发器，说明如果更新某（些）列则做如何处理； s sql_statement：触发器所要执行的SQL语句，它可以是一组SQL语句，可以包含流程控制语句等。,例：建立一个简单的触发器。,CREATE TRIGGER wh_trigger ON 仓库 FOR INSERT AS PRINT 插入了一个仓库元组,插入视图和删除视图,为触发器运行而自动派生的两个视图： inserted存放刚插入的新记录 deleted存放刚删除的旧记录,删除类触发器,删除类触发器就是当表上发生删除操作时所触发执行的程序。,例：定义一个触发器，使得当删除仓库记录时，同时将所属所有职工记录的仓库号字段值置为空值NULL：,CREATE TRIGGER w_del_trigger ON 仓库 FOR DELETE AS UPDATE 职工 SET 仓库号=NULL WHERE 仓库号 = (SELECT 仓库号 FROM deleted),插入类触发器,插入类触发器就是当表上发生插入操作时所触发执行的程序。,例：对职工表的插入操作定义一个触发器，使得当插入职工记录时，检查相应的仓库元组是否存在，如果不存在则撤消所做的插入操作。,CREATE TRIGGER e_ins_trigger ON 职工 FOR INSERT AS IF (SELECT COUNT(*) FROM 仓库 w , inserted i WHERE w.仓库号 = i.仓库号) = 0 BEGIN RAISERROR (非法仓库号！,1,1) ROLLBACK TRANSACTION END,更新类触发器,更新类触发器就是当表上发生更新操作时所触发执行的程序。,例：对职工表的更新操作定义一个触发器，使得当职工变换所属仓库时，检查相应的仓库元组是否存在，如果不存在则撤消所做的更新操作，如果新的仓库号是WH2则将工资提高10%。,CREATE TRIGGER e_upd_trigger ON 职工 FOR UPDATE AS DECLARE wh_no CHAR(4) IF UPDATE(仓库号) BEGIN IF (SELECT COUNT(*) FROM 仓库 w , inserted i WHERE w.仓库号 = i.仓库号) = 0 BEGIN RAISERROR (非法仓库号！, 16, 1) ROLLBACK TRANSACTION END ELSE BEGIN SELECT wh_no = 仓库号 FROM inserted IF wh_no = WH2 UPDATE 职工 SET 工资 = 工资*1.10 WHERE 职工号 = (SELECT 职工号 FROM inserted) END END,CREATE TRIGGER up_price ON Products FOR UPDATE AS IF UPDATE(UnitPrice) DECLARE new real,old real SELECT new=UnitPrice FROM inserted SELECT old=UnitPrice FROM deleted IF ABS(new-old)/old0.1 BEGIN RAISERROR (单价更新超过10%！, 16, 1) ROLLBACK TRANSACTION END,对Products表更新单价字段值时，如果超过10%则拒绝更新。,对Order Details表更新单价字段值时，如果超过10%则创建例外记录。,假设用OrderDetails表存储例外记录 SELECT * INTO OrderDetails FROM “Order Details“ WHERE 1=0,CREATE TRIGGER upd_price ON “Order Details“ FOR UPDATE AS IF UPDATE(UnitPrice) DECLARE new real,old real SELECT new=UnitPrice FROM inserted SELECT old=UnitPrice FROM deleted IF ABS(new-old)/old0.1 BEGIN INSERT INTO OrderDetails SELECT * FROM deleted INSERT INTO OrderDetails SELECT * FROM inserted END,对Order Details表更新单价字段值时，如果超过10%则创建例外记录。,复杂完整性约束举例,假设有如下关系模式： 管理(仓库号，设备号，职工号) 它所包含的语义是： 一个仓库可以有多个职工； 一名职工仅在一个仓库工作； 在每个仓库一种设备仅由一名职工保管（但每名职工可以保管多种设备）。 显然该关系模式的关键字是(仓库号,设备号)。进一步假设，该关系中已经有元组： (WH1,P1,E1) (WH1,P2,E1) 现在接着要插入元组(WH2,P2,E1)，注意：这种操作显然违反了语义“一名职工仅在一个仓库工作”，但它不违反实体完整性约束，一般的域完整性约束也无济于事。这时则只能通过触发器来实现数据完整性约束。,建立触发器,CREATE TRIGGER wh_emp ON 管理 FOR INSERT,UPDATE AS DECLARE emp CHAR(6),wh CHAR(6) SELECT wh=仓库号,emp=职工号 FROM inserted IF (SELECT COUNT(*) FROM 管理 WHERE 职工号=emp AND 仓库号wh) 0 BEGIN RAISERROR (该职工已经属于其他仓库！, 16, 1) ROLLBACK TRANSACTION END,规则和默认值,在第2章已经介绍了在关系数据模型上数据完整性的概念和规则；在第3章介绍了CREATE TABLE语句中可以实现的一些完整性约束。这里介绍独立的数据库对象规则和默认值，它们是与数据完整性有关的内容。 .,规则,在CREATE TABLE语句中可以使用CHECK子句实现一些用户定义完整性或域完整性约束。另外还可以通过“规则”（RULE）来实现用户定义完整性或域完整性。 CHECK约束固定在一个表的一个列上，它只在指定的列上起作用。如果在不同的列上有相同的约束条件，则可以使用规则，一个规则可以绑定在多个列上。 规则是一种独立的数据库对象，它可以绑定到一个列上来约束该列的取值范围等。,建立规则的命令是：,CREATE RULE rule AS condition_expression rule：给出新建规则的名称； condition_expression：定义规则的条件，可以是任何有效的表达式，并且可以包含诸如算术运算符、关系运算符和谓词（如IN、LIKE、BETWEEN）之类的元素。 注意：规则不能引用列或其它数据库对象，规则可以包含不引用数据库对象的内置函数；condition_expression需要包含一个变量，变量的前面有一个前缀；该表达式引用通过 UPDATE或INSERT语句输入或传递的字段值。,例如，规定某类数值对象的取值范围是10003000，则可以定义规则：,CREATE RULE range_rule AS range=1000 AND range=3000,规则的绑定,规则是独立的数据库对象，要通过系统存储过程sp_bindrule把规则绑定到数据列上，该系统存储过程的格式是： sp_bindrule rulename , objname , futureonly rulename是用CREATE RULE命令建立的规则名； objname指出要绑定的表和列或用户定义的数据类型； futureonly，当绑定规则到用户定义的数据类型时可以选用此项，该选项是禁止已经存在的、用用户定义数据类型定义的列遵循新的规则。,例如，将规则range_rule绑定到职工表的工资列上,sp_bindrule range_rule,职工.工资 使用CREATE RULE命令创建的规则对象，可以绑定到多个数据列上，即一个规则可以反复使用。,绑定的消除和规则的删除,绑定到数据列上的规则可以去除，相应的系统存储过程是sp_unbindrule。 例如，取消绑定在职工表工资列上的规则可以使用如下语句： sp_unbindrule 职工.工资 规则可以删除，删除规则的命令是DROP RULE，但是删除规则之前，必须首先解除所有的绑定。,默认值,在CREATE TABLE命令中可以使用DEFAULT约束为数据列定义默认值。这里介绍另外一种方法：使用CREATE DEFAULT命令创建默认值对象。,CREATE DEFAULT命令的格式,CREATE DEFAULT default AS constant_expression default：是建立的默认值对象名； constant_expression：定义默认值的常量表达式。,例如，定义一个值为“北京”的默认值对象val_bj,CREATE DEFAULT val_bj AS 北京,绑定默认值,默认值是独立的数据库对象，它要作用于某个数据对象，则也和绑定规则一样，需要用类似的系统存储过程把默认值绑定到列，绑定默认值的系统存储过程是sp_bindefault，具体格式是： sp_bindefault defname , objname , futureonly defname：是用CREATE DEFAULT命令建立的默认值对象名； objname：指出要绑定的表和列或用户定义的数据类型； futureonly，当绑定默认值到用户定义的数据类型时可以选用此项，该选项是禁止已经存在的、用用户定义数据类型定义的列遵循新的默认值约定。,例如，将定义的默认值对象val_bj绑定到仓库关系的城市列上和供应商关系的地址列上：,sp_bindefault val_bj,仓库.城市 sp_bindefault val_bj,供应商.地址,绑定的去除和默认值的删除,绑定到数据列上的默认值可以去除，相应的系统存储过程是sp_unbindefault。 例如，取消绑定在供应商表地址列上的默认值可以使用如下语句： sp_unbindefault 供应商.地址 默认值也可以删除，删除默认值的命令是DROP DEFAULT，但是删除默认值之前，必须首先解除所有的绑定。,【本章小节】,存储过程和触发器，它们都是独立的数据库对象和存储在数据库上的特殊的程序。 存储过程由用户调用，完成指定的数据处理任务；触发器则由特定的操作触发，从而自动完成相关的处理任务。,使用触发器可以实现更强的数据完整性方面的约束。 规则、默认值。,