tj第六章SQL编程技术.ppt

1,第六章 SQL 编程技术,2,6.1 SQL 编程基础,6.1.1 批处理 批处理是一个以“”为结束的语句集这些语句是作为一个组来执行并一起提交的 脚本是一系列顺序执行的批处理脚本文件的文件类型为 .sql 注意：不是语句,3,例：给出含有三个批处理的脚本,CREATE TABLE 学院 ( 学院名称 char(12) PRIMARY KEY, 院长 char(8) , 学院地址 char(16) , 办公电话 char(8) , 编制人数 smallint ) GO INSERT INTO 学院 VALUES(计算机学院,刘洋,一号楼4,88889999,50) INSERT INTO 学院 VALUES(商学院,梁山,五号楼302,99998888,60) GO SELECT * FROM 学院 GO,4,6.1.2 变量,1.变量的类型 局部变量 -以 引导,由用户定义.其作用域定义的辖域中. 全局变量 -以 引导,由系统提供并赋值.其作用域为全局.,5,6.1.2 变量,2.变量的声明 语句格式: DECLARE 变量名 数据类型 , 变量名 数据类型 例:声明变量 DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT,6,6.1.2 变量,3.变量的赋值 语句格式一: SET 变量名称=表达式 语句格式二: SELECT 变量名称=表达式 语句格式三: SELECT 变量名称=字段(或函数) FROM 表(或视图)名 WHERE ,7,举例,例1: DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT SET Name=李华 SET Sex =女 SET Age=20 例2: DECLARE Name CHAR(8) SELECT Name=李华,8,举例,例3: DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT SELECT Name=姓名,Sex=性别, Age=YEAR(GETDATE()-YEAR(出生日期) FROM 学生 WHERE 学号=00150236 SELECT Name, Sex, Age GO,显示结果,9,6.1.3控制流语句,包含条件控制语句、无条件转移语句、循环语句等。,10,6.1.3控制流语句,.BEGIN END 功能：将一组SQL语句作为一个语句块。 语句格式： BEGIN END 与IF语句和WHILE语句配合使用,11,6.1.3控制流语句,2.IF ELSE 语句 语句格式： IF ELSE ,12,13,举例,USE 教学数据库 GO DECLARE gavg smallint SELECT gavg=AVG(成绩) FROM 选课 WHERE 课程号=(SELECT 课程号 FROM 课程 WHERE 课程名称=大学英语) IF (gavg80) PRINT 大学英语课程的平均成绩高于80分 ELSE BEGIN PRINT 大学英语课程的平均成绩低于80分 select 平均成绩:,gavg END,14,6.1.3控制流语句,3.循环语句 语句格式： WHILE BREAK CONTINUE,15,6.1.3控制流语句,16,举例:,求出大学英语课程的平均成绩 当该分数小于0分时，循环做,给所有成绩高于平均分的成绩加分，当最高分大于时停止加分并显示加的分数 该分数(平均成绩)大于0分时，循环停止并显示加的分数,17,举例,DECLARE gavg smallint,COUN smallint SET COUN=0 SELECT gavg=AVG(成绩) FROM 选课 WHERE 课程号= (SELECT 课程号 FROM 课程 WHERE 课程名称=大学英语) WHILE (gavggavg IF(SELECT max(成绩) FROM 选课 WHERE 课程号= (SELECT 课程号 FROM 课程 WHERE 课程名称=大学英语)100 BREAK END PRINT COUN,18,举例,DECLARE gavg smallint,COUN smallint SET COUN=0 SELECT gavg=AVG(成绩) FROM 选课 WHERE 课程号= (SELECT 课程号 FROM 课程 WHERE 课程名称=大学英语) PRINT gavg WHILE (gavggavg IF(SELECT max(成绩) FROM 选课 WHERE 课程号= (SELECT 课程号 FROM 课程 WHERE 课程名称=大学英语)=100 BREAK ELSE SELECT gavg=AVG(成绩) FROM 选课 WHERE 课程号= (SELECT 课程号 FROM 课程 WHERE 课程名称=大学英语) END PRINT COUN,警告: 聚合或其它 SET 操作消除了空值。 74 （所影响的行数为 4 行） 警告: 聚合或其它 SET 操作消除了空值。 警告: 聚合或其它 SET 操作消除了空值。 （所影响的行数为 4 行） 警告: 聚合或其它 SET 操作消除了空值。 - - 2 75 （所影响的行数为 1 行）,19,4. RETURN语句 语句格式： RETURN 整数 功能：无条件所在批处理、存储过程或触发器。可以返回一个整数值,6.1.3控制流语句,20,举例,题目：根据给定的学号（ param ）检查学生的平均成绩，若75，将返回状态代码 1，将返回状态代码 2。 USE 教学数据库 CREATE PROCEDURE checkavg param varchar(10) AS IF (SELECT AVG(成绩) FROM 选课 WHERE 学号 = param) 75 RETURN ELSE RETURN ,21,执行存储过程：,declare aa smallint exec aa=checkavg 00150236 if aa= print 小于75 else print 大于75,22,6.1.4 EXECUTE语句,功能：执行函数、存储过程 语法格式： EXECUTE output,23,6.1.5 注释,.单行注释 （两个减号） .多行注释 /* */,24,6.1.6 程序设计举例,题目：转帐，若帐户的余额大于等于100元，从帐户上支取100元，存入帐户.否则，不执行转帐操作。,begin transaction update 帐户 set 余额=余额-100 where 帐户=A IF (SELECT 余额 from 帐户 where 帐户=A)=0 begin print金额不够。转帐失败！ rollback transaction end else begin update 帐户 set 余额=余额+100 where 帐户=B print转帐成功！ commit transaction end,25,事务概述,事务是作为单个逻辑工作单元执行的一系列操作。 事务的属性（ACID) 原子性：原子工作单元 一致性：保证数据的一致性 隔离性：并发事务之间所做的修改要隔离 并发操作：几个用户程序同时读写一个数据的情况 持久性：对系统的影响要持久,26,事务操作,语法格式： BEGIN TRANSACTION 事务开始 COMMIT TRANSACTION 提交 ROLLBACK TRANSACTION 回滚 说明：如果没有明确给出BEGIN TRANSACTION语句，则SQL Server是将每个SQL语句都当成一个事务进行执行,27,6.3 存储过程,6.3.1存储过程的概念 是一组被编译在一起的T-SQL语句的集合，它们被集合在一起以完成一个特定的任务。 存储过程的分类 系统存储过程 扩展存储过程（提供从SQL Server到外部程序的接口，以便进行各种维护活动） 用户自定义的存储过程,28,使用存储过程的优势,模块化编程： 创建一个存储过程存放在数据库中后，就可以被其他程序反复使用。 快速执行： 存储过程第一次被执行后，就驻留在内存中。以后执行就省去了重新分析、优化、编译的过程。 减少网络通信量 有了存储过程后，在网络上只要一条语句就能执行一个存储过程。 安全机制 通过隔离和加密的方法提高了数据库的安全性，通过授权可以让用户只能执行存储过程而不能直接访问数据库对象。,29,6.3.2 创建存储过程,创建过程语法格式： CREATE PROCEDURE 数据类型，OUTPUT AS 执行过程语法格式： EXECUTE或EXEC 变量=值 删除存储过程语法格式： DROP PROCEDURE ,30,例：检索某个学生(学号为01111111）的成绩单，包括姓名、课程名、分数。存储过程名为sc_ grade,IF EXISTS(SELECT name FROM sysobjects WHERE name=sc_grade AND type=P) DROP PROCEDURE sc_grade GO CREATE PROCEDURE sc_grade SNO CHAR(8) AS SELECT 姓名,课程名称,成绩 FROM 学生 S,课程 C ,选课 SC WHERE S.学号=SC.学号 AND C.课程号=SC.课程号 AND S.学号=SNO GO,31,select left(name,15)+space(3) name,xtype from sysobjects where xtype in (U,PK,FK,CK,P,TR) and SUBSTRING(name,1,3)dt_,name xtype - - check_trig TR ScroeProc P t1 U PK_t1_17036CC PK t2 U 系科 U PK_系科 PK 选课1 U 选课11 U 选课 U PK_选课 PK 课程 U PK_课程 PK student U PK_student_3E PK student3 U PK_student3_4 PK checkavg P 教师 U PK_教师 PK 学生 U PK_学生 PK dtproperties U pk_dtproperties PK （所影响的行数为 24 行）,32,执行过程,EXEC sc_grade 01111111 或 EXEC sc_grade sno= 01111111 ,33,例在“教学”数据库建立一个名为ScroeProc的存储过程，它带有两个输入参数并返回两个输出参数和一个返回值 。程序清单如下： USE 教学 GO -存储过程ScroeProc若存在，则删除之 IF EXISTS(SELECT name FROM sysobjects WHERE name=Scroe AND type=P) DROP PROCEDURE ScroeProc GO,34,-创建存储过程ScroeProc -输入参数：Dno输入系科号；Cname输入课名 -输出参数：Avg 接受平均分 CREATE PROCEDURE ScroeProc Dno char(4),Cname varchar(30), Avg decimal OUTPUT AS /*声明和初始化一个局部变量，用于保存系统函数ERROR的返回值*/ DECLARE ErrorSave int SET ErrorSave=0,35,-执行一个选择查询，统计指定系的某门课的平均成绩 -通过两个输入参数接受系号和课程名称 -通过一个输出参数Avg接受平均分 SELECT Avg=AVG(成绩) FROM 选课 sc INNER JOIN 学生 st ON sc.学号=st.学号 INNER JOIN 课程 CO ON sc.课程号=co.课程号 where 系科号 =Dno AND 课程名称=Cname IF (ERROR0) SET ErrorSave=ERROR select ErrorSave,36,/*执行一个修改查询，将统计指定系的某门课的高于平均分的加1分*/ UPDATE 选课 set 成绩=成绩+1 where 学号 in (select 学号 st FROM 选课 sc INNER JOIN 学生 st ON sc.学号=st.学号 INNER JOIN 课程 CO ON sc.课程号=co.课程号 where 系科号 =Dno AND 课程名称=Cname) IF (ERROR0) SET ErrorSave=ERROR RETURN ErrorSave,37,执行存储过程: /*声明变量，用于保存返回值和输出结果*/ DECLARE RetCode int, AvgGrade decimal /*执行存储过程，并指定输入参数和输出参数*/ EXECUTE RetCode=ScroeProc 15,数据结构, AvgGrade OUTPUT,38,- 0 （所影响的行数为 1 行） （所影响的行数为 51 行） - - 84 0 （所影响的行数为 1 行）,39,6.4 数据库触发器,6.4.1 触发器的概念 是一种实现复杂完整性约束的特殊存储过程，是能够在符合条件是自动触发的SQL程序。 1.触发器的特点 2.触发器的优点,40,6.4.1 触发器的概念,若触发器所依赖的表中有约束，则在执行时，约束优于触发器，而且如果在操作中触发器和约束发生冲突，触发器将不执行。 触发器操作是一个事务操作。,41,6.4.1 触发器的概念,在下列情况下可以考虑使用触发器： 强制比CHECK约束复杂的数据完整性 使用自定义的错误信息和执行复杂的错误处理 实现多张表的级联修改 比较数据库修改前后数据的状态 维护非规范数据,42,6.4.2 创建触发器,语法格式： CREATE TRIGGER On FOR | AFTER | INSTEAD OF INSERT| UPDATE |DELETE AS 删除触发器语法格式： DROP TRIGGER ,43,语句解释：,AFTER:指定触发器只有在触发 SQL 语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才能执行此触发器。 如果仅指定 FOR 关键字，则 AFTER 是默认设置。 不能在视图上定义 AFTER 触发器。 INSTEAD OF:指定执行触发器而不是执行触发 SQL 语句，从而替代触发语句的操作。 INSERT,UPDATE,DELETE:是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。,44,使用触发器的限制,(1) CREATE TRIGGER 必须是批处理中的第一条语句，并且只能应用到一个表中。 (2) 触发器只能在当前的数据库中创建，但触发器可以引用当前数据库的外部对象。 (3) 如果指定触发器所有者名限定触发器，要以相同的方式限定表名。 (4) 在同一CREATE TRIGGER语句中，可以为多种操作（如 INSERT 和 UPDATE）定义相同的触发器操作。,45,使用触发器的限制,(5) 一个表的外键在 DELETE、UPDATE 操作上定义了级联，不能在该表上定义 INSTEAD OF DELETE、INSTEAD OF UPDATE 触发器。 (6) 触发器中不允许包含以下 T-SQL 语句： CREATE DATABASE 、ALTER DATABASE 、LOAD DATABASE 、RESTORE DATABASE 、DROP DATABASE、LOAD LOG 、RESTORE LOG 、 DISK INIT、DISK RESIZE和RECONFIGURE,46,6.4.3触发器的工作原理,当触发器被触发时，系统会创建两个专用临时表：inserted表和deleted表。这两个表由系统来维护，不允许用户直接对这两个表进行修改（可以读）。它们存放于内存中，不存放在数据库中。这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。,47,6.4.3触发器的工作原理,insterted表： 存放由于INSERT或UPDATE语句的执行而导致要加到该触发表中去的所有新行。即用于插入或更新表的新行值，在插入或更新表的同时，也将其副本存入insterted表中。因此，在insterted表中的行总是与触发表中的新行相同。,48,6.4.3触发器的工作原理,deleted表： 存放由于DELETE或UPDATE语句的执行而导致要从该触发表中删除的所有行。也就是说，把触发表中要删除或要更新的旧行移到deleted表中。因此，deleted表和触发表不会有相同的行。,49,6.4.3触发器的工作原理,对INSERT操作，只在insterted表中保存所插入的新行，而deleted表中无一行数据。 对于DELETE操作，只在deleted表中保存被删除的旧行，而insterted表中无一行数据。 对于UPDATE操作，可以将它考虑为DELETE操作和INSERT操作的结果，所以在inserted表中存放着更新后的新行值，deleted表中存放着更新前的旧行值。,50,例:在学生表上创建触发器reminder，若在学生表中添加、更改和删除数据，则将向客户端显示信息。,USE 教学 IF EXISTS (SELECT name FROM sysobjects WHERE name = reminder AND type = TR) DROP TRIGGER reminder GO CREATE TRIGGER reminder ON 学生 FOR INSERT, UPDATE ,DELETE AS print 注意：学生表数据被修改。 SELECT * FROM INSERTED SELECT * FROM DELETED GO,51,insert into 学生(学号,姓名) values (9999,dd),UPDATE 学生 SET 姓名=WWWW WHERE 学号=9999,delete from 学生 where 学号=9999,52,举例：创建一触发器，当向成绩表插入一记录时，检查该记录的学号在学生表是否存在，检查课程号在课程表中是否存在，若有一项为否，则不允许插入。,Use 教学 GO alter TRIGGER check_trig ON 成绩 for INSERT AS IF EXISTS (SELECT * FROM inserted a WHERE a.学号 NOT IN (SELECT 学号 FROM 学生 ) OR a.课程编号 NOT IN (SELECT 课程编号 FROM 课程 ) BEGIN -SELECT * FROM 成绩 -SELECT * FROM inserted print 违背数据的一致性！ ROLLBACK TRANSACTION END SELECT * FROM 成绩 GO,53,insert into 成绩 values (3012,C6,90) SELECT * FROM 成绩 WHERE 课程编号 LIKE %6,执行插入操作：,insert into 成绩 values (5012,C6,90),服务器: 消息 547，级别 16，状态 1，行 1 INSERT 语句与 COLUMN FOREIGN KEY 约束 FK_成绩_学号_6B24EA82 冲突。该冲突发生于数据库 教学，表 学生, column 学号。 语句已终止。,54,分别删除了成绩表、课程表、学生表的联系,insert into 成绩 values (5012,C6,90),55,6.2 游标及其应用,6.2.1 游标的概念 如果要求每次只显示表格中的一行，该如何处理？ 这在将T-SQL嵌入到其他高级语言（如VC、VB、Delphi等）的编程中经常用到。,56,6.2.1 游标的概念,游标(cursor)是一个存储区域，用来存放结果集。游标的指针，可以指向与它相关联的结果集中的任意一行，以便对当前位置的行进行处理。 游标提供了对一个结果集进行逐行处理的能力： 在结果集中定位特定行 从结果集的当前位置检索行 支持对结果集中当前位置的行进行数据处理（修改/删除）,57,6.2.2 游标的用法,声明游标 打开游标 处理数据（读取/修改/删除