数据库高级SQl相关技术.ppt

第6章 高级SQL相关技术,6.1 SQL编程基础 6.2 事务 6.3 游标 6.4 存储过程 6.5 触发器 6.6 嵌入式SQL,Sample库中几张表,表6.1 客户数据表,几张表,表6.2 员工数据表,几张表,表6.3 项目数据表,6.1 SQL编程基础,T-SQL是针对数据库的查询语言，它的编程结构比其他程序设计语言更为简单,一、 批处理,批处理是一组T-SQL语句的集合，可以是一条语句，也可以由多条语句组成。 批处理语句以GO为结束标志。 GO只是个标志语句，并不被执行。 在T-SQL存储过程调用时，如果调用语句不是一段批语句的第一句，则调用前需要加EXEC； 创建表和创建视图的语句只能存在于一个单独的批处理语句中,二、变量,变量是T-SQL用来在其语句间传递数据的。由系统或用户定义并赋值。,1、全局变量,全局变量往往用来记录数据库的活动状态。用“变量名”表示 几个常用的全局变量 ERROR返回上一条T-SQL语句执行后的状态值。如果为0表示语句执行正确，非0表示发生错误。 FETCH_STATUS返回被FETCH语句执行的最后游标状态，如果为0表示FETCH执行成功，非0表示发生错误。 ROWCOUNT返回上一条T-SQL语句影响到的记录个数。,2、局部变量,用“变量名”表示。声明局部变量使用DECLARE语句，后面跟上变量名和变量数据类型。局部变量赋值可以使用SELECT语句或SET语句 例6.1 USE SAMPLE GO DECLARE Mychar VARCHAR(10),Max_Salary Decimal(7,2) SET Mychar=最高工资 SELECT Max_Salary=MAX(工资) FROM 员工数据表,3、函数,常用的函数有系统函数、日期函数、字符串函数、数学函数以及用户自定义函数等,4、程序流程控制,BEGINEND。用于将多条语句组合起来 IFELSE 。用于条件的测试 WHILE。当循环逻辑表达式结果为真时，重复执行后面的语句或语句块 CASE。可以使用户方便地实现多重选择 RETURN。用于无条件终止查询、存储过程或批处理。,例6.2检索员工数据表，如果存在工资大于5000元的员工，则输出这些员工的姓名；否则输出消息，说明没有员工工资在5000以上。,USE SAMPLE GO DECLARE Message VARCHAR(100) IF ( SELECT MAX(工资) FROM 员工数据表) 5000 BEGIN SET Message = 下列人员的工资超过5000: PRINT Message SELECT 员工姓名，工资 FROM 员工数据表 WHERE 工资 5000 END ELSE BEGIN SET Message = 没有工资超过5000的员工。 PRINT Message END GO,例6.3员工发放的奖金是工资数额的30%，判断是否有员工奖金少于300，如果有，则将所有员工的工资增加500，直到所有员都多于300或有员工的工资超过了10000。,USE SAMPLE GO WHILE ( SELECT * FROM 员工数据表 WHERE 工资*0.3 10000 BREAK END GO,例6.4分别输出员工姓名和所在的部门，并在部门后面添加说明 。,USE SAMPLE GO SELECT 姓名, “部门说明”= /* 分别为个部门添加说明 */ CASE 所属部门 WHEN 办公室 THEN 在办公室工作，该部门主要负责各部门的办公事务。 WHEN 项目部 THEN 在项目部工作，该部门主要负责工程项目事务。 WHEN 生产部 THEN 在生产部工作，该部门主要负责生产事务。 WHEN 检验处 THEN 在检验处工作，该部门主要负责产品检验事务。 END FROM 员工数据表 GO,6.2 事务,一、什么是事务 二、如何定义事务 三、事务的特性,一、什么是事务,事务(Transaction)是用户定义的一个数据库操作序列，这些操作要么全做，要么全不做，是一个不可分割的工作单位 事务和程序是两个概念 在关系数据库中，一个事务可以是一条SQL语句，一组SQL语句或整个程序 一个应用程序通常包含多个事务 事务是恢复和并发控制的基本单位,二、如何定义事务,显式定义方式 BEGIN TRANSACTION BEGIN TRANSACTION SQL 语句1 SQL 语句1 SQL 语句2 SQL 语句2 。 。 COMMIT ROLLBACK 隐式方式 当用户没有显式地定义事务时， DBMS按缺省规定自动划分事务。,事务结束,COMMIT 事务正常结束 提交事务的所有操作（读+更新） 事务中所有对数据库的更新永久生效 ROLLBACK 事务异常终止 事务运行的过程中发生了故障，不能继续执行 回滚事务的所有更新操作 事务滚回到开始时的状态,保存点,目前在很多DBMS中对事务处理除了上述三条语句外，还增加了一条保存点设置的语句： SAVE TRANSACTION后跟存储点名代表设置保存点，也可以书写成SAVE TRAN。,例6.5在员工数据表中插入两行数据，如果出错，则回滚到事务开始处。,BEGIN TRAN tran_exam1 /* 事务开始 */ USE SAMPLE INSERT INTO 员工数据表 (员工编号,姓名,工资) VALUES (1000,李晓丽,2400) /* 在员工数据表中插入一行数据*/ INSERT INTO 员工数据表 (姓名,工资) VALUES (张正枫,2000) /* 在员工数据表中插入一行，但未插入员工编号*/ IF error0 ROLLBACK /* 如果插入语句出现错误则回滚到事务开始*/ COMMIT TRAN tran_exam1 GO,例6.6在员工数据表中插入两行数据，如果出错，则回滚到保存点 。,BEGIN TRAN tran_exam2 USE SAMPLE INSERT INTO 员工数据表 (员工编号,姓名,工资) VALUES (1000,李晓丽,2400) SAVE TRAN insertsav /* 在此设置保存点，保存前面的结果*/ INSERT INTO 员工数据表 (姓名,工资) VALUES (张正枫,2000) IF error0 /* 如果插入语句出现错误则回滚到保存点*/ ROLLBACK TRAN insertsav PRINT “继续执行程序” COMMIT TRAN tran_exam2 GO,三、事务的特性(ACID特性),事务的ACID特性： 原子性（Atomicity） 一致性（Consistency） 隔离性（Isolation） 持续性（Durability ）,6.3 游标（cursor）,为什么要使用游标 SQL语言与主语言具有不同数据处理方式 SQL语言是面向集合的，一条SQL语句原则上可以产生或处理多条记录,游标（续）,主语言是面向记录的，一组主变量一次只能存放一条记录 仅使用主变量并不能完全满足SQL语句向应用程序输出数据的要求 嵌入式SQL引入了游标的概念，用来协调这两种不同的处理方式,游标（续）,什么是游标 游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果 每个游标区都有一个名字 用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理,游标的使用过程,打开(open),声明(declare),处理,关闭(close),解除(deallocate),1. 申明游标,语句格式 DECLARE 游标名称 SCROLL CURSOR FOR SELECT语句 FOR READ ONLY | UPDATE OF 列1,列2,列3, 是一条说明性语句，这时DBMS并不执行SELECT指定的查询操作。 当cursor申请成功后，游标名就成为该cursor的识别，后续对该cursor的处理必须指定该游标名。游标名在同一程序（存储过程）内不可重复。,申明游标（续）,SCROLL：如果选用了此关键字，表示可以使用相应的关键字指定游标的移动位置。如果不用SCROLL，则表示游标的移动只能是NEXT。,READ ONLY | UPDATE OF 列1,列2,列3,：设置游标的只读属性和更新属性。方括号中定义游标内可更新的列，如果没有指定具体的列名，则表示能修改所有的列。,2. 打开游标,语句格式 OPEN ; 功能 打开游标实际上是执行相应的SELECT语句，把所有满足查询条件的记录从指定表取到缓冲区中 这时游标处于活动状态，指针指向查询结果集中第一条记录之前,3. 移动游标指针，然后取当前记录,语句格式 FETCH NEXT|PRIOR|FIRST|LAST FROM INTO ,.;,4. 关闭游标,语句格式 CLOSE ; 功能 关闭游标，释放结果集占用的缓冲区及其他资源 说明 游标被关闭后，就不再和原来的查询结果集相联系 被关闭的游标可以再次被打开，与新的查询结果相联系,例6.7声明一个游标的基础上，利用该游标变量读取结果集中的所有记录。,USE SAMPLE DECLARE cursor_exam1 CURSOR /* 声明游标 */ FOR SELECT * FROM 员工数据表 WHERE 所属部门=项目部 ORDER BY 姓名 FOR READ ONLY OPEN cursor_exam1 /* 打开游标 */ FETCH FROM cursor_exam1 /* 读取结果集中的数据*/ WHILE FETCH_STATUS = 0 /* 根据游标状态来确定是否继续读取数据*/ FETCH FROM cursor_exam1,例6.8使用游标，将客户数据表中的公司名称和联系人显示出来。,USE SAMPLE DECLARE comname VERCHAR(50),conname VARCHAR(10) /* 定义两变量，类型和客户数据表中的“公司名称”、“联系人”字段相同 */ DECLARE cur_exam2 CURSOR /* 声明游标 */ FOR SELECT 公司名称，联系人 FROM 客户数据表 ORDER BY 客户编号 FOR READ ONLY /* 定义只读游标在查询“客户数据表”的公司名称和联系人姓名上 */ OPEN cur_exam2 /* 打开游标 */ FETCH FROM cur_exam2 INTO comyname ,conname PRINT “公司名称：”+ RTRIM(comname)+”联系人：”+RTRIM(conname) /* 使用游标取查询结果集中的第一行数据赋给变量并打印出来，同时给游标状态赋初值 */,例6.8（续）,WHILE FETCH_STATUS = 0 /* 根据游标状态来确定是否继续读取数据*/ BEGIN FETCH FROM cur_exam2 INTO comname ,conname PRINT “公司名称：”+ RTRIM(comname)+”联系 人：”+RTRIM(conname) END /* 循环取数直到游标状态值FETCH_STATUS非0，即没有数据了 */ CLOSE cur_exam2 /* 关闭游标 */ DEALLOCATE cur_exam2 /* 释放游标 */,6.4 存储过程,存储过程是一段预先编译好地T-SQL程序，有自己的名字，可以作为一个独立的数据库对象，也可以作为一个单元供用户在应用程序中调用。存储过程有以下优点： 执行速度快 提高工作效率 规范程序设计 提高系统的安全性,一、创建存储过程,命令的语法为： CREATE PROCEDURE 存储过程名称 parameter data_type OUTPUT ,n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION AS SQL语句语句组,例6.9在SAMPLE数据库中创建一存储过程，返回指定的时间段中立项的项目的经费总额。,USE SAMPLE IF EXISTS(SELECT name FROM sysobjects WHERE name=pro_exam1 AND type=P) DROP PROC pro_exam1 /* 如果数据库SAMPLE对象系统表中已经有名为pro_exam1的存储过程，则删除 */ CREATE PROC pro_exam1 beginning_data DATETIME ,ending_date DATETIME , all_outlay decimal(9,2) OUTPUT /* 给存储过程定义三个参数：开始时间和结束时间以及经费总额 */ AS SELECT all_outlay=SUM(经费) FROM 项目数据表 WHERE 开始时间 BETWEEN beginning_data AND ending_date /* 查询出要求的时间内所立项目的经费总额 */ GO,二、 执行和删除存储过程,只有对存储过程有访问权限的用户才可以调用存储过程，调用时如果不是一个批的首行用EXECUTE 命令，可简写为EXEC。例： DECLARE all_outlay DECIMAL(9,2) EXEC pro_exam12004-1-1,2004-12- 31,all_outlay OUTPUT SELECT 2004年立项的项目总经费是：RTRIM(CAST(all_outlay AS VARCHAR(10) GO 删除存储过程。 DROP PROC 存储过程名称,6.5 触发器,触发器是一种数据库基本表被修改时自动执行的内嵌程序，当使用UPDATE、INSERT、或DELETE命令在指定表中对数据进行修改时，用来保证数据正确性。触发器可以查询其他表，而且可以包含复杂的SQL语句，它们主要用于强制较为复杂的业务规则或要求。使用触发器能够帮助保证数据的一致性和完整性。 你可把存储过程想象成手枪，它必须有人扣扳机才会射出子弹，触发器则较像地雷，当用户踩到它（条件符合）时它便会“引爆”（被执行）,触发器优点,级联修改数据库中相关联的表 执行比CHECK约束更为复杂的数据完整性约束 使用自定义的错误信息 比较数据表修改前后得数据 维护非规范化数据,触发器的类型,传统的触发器（INSERT、UPDATE、DELETE）：是由一个动作或事件的发生而触发的，现在的Microsoft把它们划分为AFTER触发器。这种类型的触发器将在数据变动完成后才被触发。对变动的数据进行检查，如果发现错误，则拒绝或回滚。 INSTEAD OF触发器：这种类型的触发器并不是有动作或事件触发，一般在数据变动之间被触发，并取代变动数据的操作（INSERT、UPDATE、DELETE），转而去执行触发器定义的操作。一般用在视图操作中。,一、 创建触发器,创建触发器语法 CREATE TRIGGER 触发器名称 ON 表名|视图名 WITH ENCRYPTION FOR |AFTER|INSTEAD OF DELETE,INSERT,UPDATE AS SQL 语句组,例6.10在SAMPLE数据库中，在“员工数据表”中创建一触发器，实现在删除某一员工时，将该员工担任负责人的项目也删除 。,USE SAMPLE GO CREATE TRIGGER tri_exam1 ON 员工数据表 FOR DELETE /* 在“员工数据表”中创建DELETE触发器 */ AS IF rowcount = 0 RETURN /* 如果删除0行，则不启动触发器 */ DELETE 项目数据表 WHERE 负责人编号 IN (SELECT 员工编号 FROM DELETED ) /* 启动触发器，删除“项目数据表”中所有以删除的员工作为负责人的项目记录 */ /*系统提供INSERTED和DELETED两个虚拟表。插入一行时，INSERT表保存一份插入行的拷贝。删除一行时，DELETE表保存了删除行的拷贝。更新一行时，DELETED和INSERTED表分别保存了更新前后的数据。 */,例6.10（续）,IF error !=0 BEGIN ROLLBACK TRAN RETURN END /* 如果触发器执行过程中出错，则整个程序回滚到原始状态 */ RETURN GO 在“员工数据表”中创建该触发器，如果应用程序执行以下语句： USE SAMPLE DELETE 员工数据表 WHERE 员工编号 = 2011 GO 在删除“员工数据表”中2011号员工的同时，也删除了“项目数据表中2011号员工负责的项目,二、 删除触发器,语句格式为： DROP TRIGGER 触发器名称,6.6 嵌入式SQL,SQL语言提供了两种不同的使用方式： 交互式 嵌入式 为什么要引入嵌入式SQL SQL语言是非过程性语言 事务处理应用需要高级语言 这两种方式细节上有差别，在程序设计的环境下，SQL语句要做某些必要的扩充,一、嵌入式SQL的一般形式,为了区分SQL语句与主语言语句，需要: 结束标志：随主语言的不同而不同 前缀：EXEC SQL 以C为主语言的嵌入式SQL语句的一般形式 EXEC SQL ; 例：EXEC SQL DROP TABLE Student; 以COBOL作为主语言的嵌入式SQL语句的一般形式 EXEC SQL END-EXEC 例： EXEC SQL DROP TABLE Student END-EXEC,DBMS处理宿主型数据库语言SQL 的方法,预编译 修改和扩充主语言使之能处理SQL语句,预编译,1由DBMS的预处理程序对源程序进行扫描，识别出SQL语句 2把它们转换成主语言调用语句，以使主语言编译程序能识别它 3最后由主语言的编译程序将整个源程序编译成目标码。,嵌入SQL语句,说明性语句 嵌入SQL语句 数据定义 可执行语句 数据控制 数据操纵 允许出现可执行的高级语言语句的地方，都可以写可执行SQL语句 允许出现说明语句的地方，都可以写说明性SQL语句,二、嵌入式SQL语句与主语言之间的通信,将SQL嵌入到高级语言中混合编程，程序中会含 有两种不同计算模型的语句 SQL语句 描述性的面向集合的语句 负责操纵数据库 高级语言语句 过程性的面向记录的语句 负责控制程序流程,工作单元之间的通信方式,1. SQL通信区 向主语言传递SQL语句的执行状态信息 主语言能够据此控制程序流程 2. 主变量 1）主语言向SQL语句提供参数 2）将SQL语句查询数据库的结果交主语言进一步处理 3. 游标 解决集合性操作语言与过程性操作语言的不匹配,1. SQL通信区,SQLCA： SQL Communication Area SQLCA是一个数据结构 SQLCA的用途 SQL语句执行后，DBMS反馈给应用程序信息 描述系统当前工作状态 描述运行环境 这些信息将送到SQL通信区SQLCA中 应用程序从SQLCA中取出这些状态信息，据此决定接下来执行的语句,SQLCA的内容,与所执行的SQL语句有关 与该SQL语句的执行情况有关 例：在执行删除语句DELETE后，不同的执行情况，SQLCA中有不同的信息： 违反数据保护规则，操作拒绝 没有满足条件的行，一行也没有删除 成功删除，并有删除的行数 无条件删除警告信息 由于各种原因，执行出错,SQLCA的使用方法,定义SQLCA 用EXEC SQL INCLUDE SQLCA加以定义 使用SQLCA SQLCA中有一个存放每次执行SQL语句后返回代码的变量SQLCODE 如果SQLCODE等于预定义的常量SUCCESS，则表示SQL语句成功，否则表示出错 应用程序每执行完一条SQL 语句之后都应该测试一下SQLCODE的值，以了解该SQL语句执行情况并做相应处理,2. 主变量,什么是主变量 嵌入式SQL语句中可以使用主语言的程序变量来输入或输出数据 在SQL语句中使用的主语言程序变量简称为主变量（Host Variable）,主变量（续）,主变量的类型 输入主变量 由应用程序对其赋值，SQL语句引用 输出主变量 由SQL语句赋值或设置状态信息，返回给应用程序 一个主变量有可能既是输入主变量又是输出主变量,主变量（续）,主变量的用途 输入主变量 指定向数据库中插入的数据 将数据库中的数据修改为指定值 指定执行的操作 指定WHERE子句或HAVING子句中的条件 输出主变量 获取SQL语句的结果数据 获取SQL语句的执行状态,主变量（续）,指示变量 一个主变量可以附带一个指示变量（Indicator Variable） 什么是指示变量 整型变量 用来“指示”所指主变量的值或条件 指示变量的用途 输入主变量可以利用指示变量赋空值 输出主变量可以利用指示变量检测出是否空值，值是否被截断,主变量（续）,在SQL语句中使用主变量和指示变量的方法 1) 说明主变量和指示变量 BEGIN DECLARE SECTION . . (说明主变量和指示变量) . END DECLARE SECTION,主变量（续）,2) 使用主变量 说明之后的主变量可以在SQL语句中任何一个能够使用表达式的地方出现 为了与数据库对象名（表名、视图名、列名等）区别，SQL语句中的主变量名前要加冒号（:）作为标志,主变量（续）,3) 使用指示变量 指示变量前也必须加冒号标志 必须紧跟在所指主变量之后 在SQL语句之外(主语言语句中)使用主变量和指示变量的方法 可以直接引用，不必加冒号,例6.11从屏幕输入某一职工的工号、姓名和工资，插入“员工数据表”中。, EXEC SQL INCLUDE SQLCA; /* 定义SQL通信区 */ EXEC SQL BEGIN DECLARE SECTION; /* 主变量说明开始 */ Int eno; char ename(8); char dno(8); EXEC SQL END DECLARE SECTION; /* 主变量说明结束 */ printf(“请输入职工工号、姓名、工资：”); scanf(“%d,%s,%s”， ,例6.12查询全部客户的信息，然后根据客户的变更情况修改其中某些客户的联系方式。, EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; Int cus_no; Char link_name(10); char link_addr(50); char new_link_addr(50); EXEC SQL END DECLARE SECTION; EXEC SQL DECLARE cur_update CURSOR FOR /* 定义游标cur_update*/ SELECE 客户编号，联系人，联系方式 /* 使其与查询结果集相联系 */ FROM 客户数据表; EXEC SQL OPEN cur_update;,例6.12（续）,while(1) /* 用循环结构逐条处理结果集中的记录 */ EXEC SQL FETCH cur_update /* 从结果集中取当前行，送相应*