第六章SQL编程技术－金锄头文库

资源描述

《第六章SQL编程技术》由会员分享，可在线阅读，更多相关《第六章SQL编程技术（63页珍藏版）》请在金锄头文库上搜索。

1、第六章 SQL 编程技术16.1 SQL 编程基础6.1.1 6.1.1 批处理批处理批处理是一个以批处理是一个以“”为结为结束的语句集这些语句是作为一个束的语句集这些语句是作为一个组来执行并一起提交的组来执行并一起提交的脚本是一系列顺序执行的批处脚本是一系列顺序执行的批处理脚本文件的文件类型为理脚本文件的文件类型为. .sqlsql注意：不是语句注意：不是语句2 2例：给出含有三个批处理的脚本CREATE TABLE CREATE TABLE CREATE TABLE CREATE TABLE 学院学院学院学院( ( ( ( 学院名称学院名称学院名称学院名称 char(12) PRIMARY

2、 KEY,char(12) PRIMARY KEY,char(12) PRIMARY KEY,char(12) PRIMARY KEY,院长院长院长院长 char(8) ,char(8) ,char(8) ,char(8) ,学院地址学院地址学院地址学院地址 char(16) ,char(16) ,char(16) ,char(16) ,办公电话办公电话办公电话办公电话 char(8) ,char(8) ,char(8) ,char(8) ,编制人数编制人数编制人数编制人数 smallintsmallintsmallintsmallint ) ) ) ) GOGOGOGOINSERT INTO

3、 INSERT INTO INSERT INTO INSERT INTO 学院学院学院学院 VALUES(VALUES(VALUES(VALUES(计算机学院计算机学院计算机学院计算机学院,刘洋刘洋刘洋刘洋,一号楼一号楼一号楼一号楼4,88889999,50)4,88889999,50)4,88889999,50)4,88889999,50)INSERT INTO INSERT INTO INSERT INTO INSERT INTO 学院学院学院学院 VALUES(VALUES(VALUES(VALUES(商学院商学院商学院商学院,梁山梁山梁山梁山,五号楼五号楼五号楼五号楼302,99998

4、888,60)302,99998888,60)302,99998888,60)302,99998888,60)GOGOGOGOSELECT * FROM SELECT * FROM SELECT * FROM SELECT * FROM 学院学院学院学院GOGOGOGO3 36.1.2 变量1.1.变量的类型变量的类型局部变量局部变量 -以以引导引导, ,由用户由用户定义定义. .其作用域定义的辖域中其作用域定义的辖域中. . 全局变量全局变量 -以以引导引导, ,由系统提由系统提供并赋值供并赋值. .其作用域为全局其作用域为全局. .4 46.1.2 变量2.2.变量的声明变量的声明

5、语句格式语句格式: : DECLARE DECLARE 变量名变量名数据类型数据类型 , , 变量名变量名数据类型数据类型例例: :声明变量声明变量DECLARE Name CHAR(8), Sex CHAR(2),DECLARE Name CHAR(8), Sex CHAR(2),Age SMALLINTAge SMALLINT5 56.1.2 变量3.3.变量的赋值变量的赋值语句格式一语句格式一: : SET SET 变量名称变量名称= =表达式表达式语句格式二语句格式二: : SELECT SELECT 变量名称变量名称= =表达式表达式语句格式三语句格式三: : SELEC

6、TSELECT 变量名称变量名称= =字段字段( (或函数或函数) ) FROM FROM 表表( (或视图或视图) )名名 WHERE WHERE 6 6举例例例1:1:DECLARE Name CHAR(8), Sex CHAR(2),DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT Age SMALLINTSET Name=SET Name=李华李华SET Sex =SET Sex =女女SET Age=20 SET Age=20 例例2: DECLARE Name CHAR(8)2: DECLARE Name CHAR(8) SELECT

7、Name= SELECT Name=李华李华7 7举例例例例例3:3:3:3:DECLARE Name CHAR(8), Sex CHAR(2), DECLARE Name CHAR(8), Sex CHAR(2), DECLARE Name CHAR(8), Sex CHAR(2), DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINTAge SMALLINTAge SMALLINTAge SMALLINTSELECT Name=SELECT Name=SELECT Name=SELECT Name=姓名姓名姓名姓名,Sex=,Sex=,Sex=,S

8、ex=性别性别性别性别, , , , Age=YEAR(GETDATE()-YEAR( Age=YEAR(GETDATE()-YEAR( Age=YEAR(GETDATE()-YEAR( Age=YEAR(GETDATE()-YEAR(出生日期出生日期出生日期出生日期) ) ) )FROM FROM FROM FROM 学生学生学生学生WHERE WHERE WHERE WHERE 学号学号学号学号=00150236=00150236=00150236=00150236SELECT Name, Sex, AgeSELECT Name, Sex, AgeSELECT Name, Sex, Age

9、SELECT Name, Sex, AgeGOGOGOGO显示显示结果结果8 86.1.3控制流语句包含条件控制语句、无条件转移语句、循环包含条件控制语句、无条件转移语句、循环包含条件控制语句、无条件转移语句、循环包含条件控制语句、无条件转移语句、循环语句等。语句等。语句等。语句等。控制控制语语句句说说明明IF.ELSEIF.ELSEIF.ELSEIF.ELSE条件条件条件条件语语句句句句GOTOGOTOGOTOGOTO无条件无条件无条件无条件转转移移移移语语句句句句WHILEWHILEWHILEWHILE循循循循环语环语句句句句CONTINUECONTINUECONTINUECONTINUE

10、用于重新开始下一次循用于重新开始下一次循用于重新开始下一次循用于重新开始下一次循环环BREAKBREAKBREAKBREAK用于退出最内用于退出最内用于退出最内用于退出最内层层的循的循的循的循环环RETURNRETURNRETURNRETURN无条件返回无条件返回无条件返回无条件返回9 96.1.3控制流语句.BEGIN END.BEGIN END.BEGIN END.BEGIN END功能：将一组功能：将一组功能：将一组功能：将一组SQLSQLSQLSQL语句作为一个语句块。语句作为一个语句块。语句作为一个语句块。语句作为一个语句块。语句格式：语句格式：语句格式：语句格式： BEGIN BE

11、GIN BEGIN BEGIN SQL SQL SQL END END END END与与与与IFIFIFIF语句和语句和语句和语句和WHILEWHILEWHILEWHILE语句配合使用语句配合使用语句配合使用语句配合使用10106.1.3控制流语句2.IF ELSE 2.IF ELSE 语句语句语句格式：语句格式： IF IF SQL1 ELSE ELSE SQL211111212举例USE USE USE USE 教学数据库教学数据库教学数据库教学数据库GO GO GO GO DECLARE DECLARE DECLARE DECLARE gavggavggavggavg smallint

12、smallintsmallintsmallintSELECT SELECT SELECT SELECT gavggavggavggavg=AVG(=AVG(=AVG(=AVG(成绩成绩成绩成绩) FROM ) FROM ) FROM ) FROM 选课选课选课选课 WHERE WHERE WHERE WHERE 课程号课程号课程号课程号=(SELECT =(SELECT =(SELECT =(SELECT 课程号课程号课程号课程号 FROM FROM FROM FROM 课程课程课程课程 WHERE WHERE WHERE WHERE 课程名称课程名称课程名称课程名称=大学英大学英大学英大学英

13、语语语语)IFIFIFIF ( ( ( (gavggavggavggavg80)80)80)80) PRINT PRINT PRINT PRINT 大学英语课程的平均成绩高于大学英语课程的平均成绩高于大学英语课程的平均成绩高于大学英语课程的平均成绩高于80808080分分分分 ELSEELSEELSEELSE BEGINBEGINBEGINBEGIN PRINT PRINT PRINT PRINT 大学英语课程的平均成绩低于大学英语课程的平均成绩低于大学英语课程的平均成绩低于大学英语课程的平均成绩低于80808080分分分分 select select select select 平均成绩平均

14、成绩平均成绩平均成绩:,:,:,:,gavggavggavggavg ENDENDENDEND13136.1.3控制流语句3.3.循环语句循环语句语句格式：语句格式：WHILE WHILE SQL 1 BREAK BREAK SQL1 CONTINUE CONTINUE 14146.1.3控制流语句1515求出大学英语课程的平均成绩，当求出大学英语课程的平均成绩，当该分数小于该分数小于0 0分时，循环做分时，循环做, ,给所给所有成绩高于平均分的成绩加分，有成绩高于平均分的成绩加分，当最高分大于时停止加分当最高分大于时停止加分并显示加的分数并显示加的分数该分数大于该分数大于0 0分时，循环停止

15、并分时，循环停止并显示加的分数显示加的分数1616举例DECLARE DECLARE DECLARE DECLARE gavggavggavggavg smallint,COUNsmallint,COUNsmallint,COUNsmallint,COUN smallintsmallintsmallintsmallintSET COUN=0SET COUN=0SET COUN=0SET COUN=0SELECT SELECT SELECT SELECT gavggavggavggavg=AVG(=AVG(=AVG(=AVG(成绩成绩成绩成绩) FROM ) FROM ) FROM ) FROM

16、选课选课选课选课 WHERE WHERE WHERE WHERE 课程号课程号课程号课程号= = = = (SELECT (SELECT (SELECT (SELECT 课程号课程号课程号课程号 FROM FROM FROM FROM 课程课程课程课程 WHERE WHERE WHERE WHERE 课程名称课程名称课程名称课程名称=大学英语大学英语大学英语大学英语)PRINT PRINT PRINT PRINT gavggavggavggavgWHILE (WHILE (WHILE (WHILE (gavggavggavggavg80)80)80)gavggavggavggavg IF(S

17、ELECT max( IF(SELECT max( IF(SELECT max( IF(SELECT max(成绩成绩成绩成绩) FROM ) FROM ) FROM ) FROM 选课选课选课选课 WHERE WHERE WHERE WHERE 课程号课程号课程号课程号= = = = (SELECT (SELECT (SELECT (SELECT 课程号课程号课程号课程号 FROM FROM FROM FROM 课程课程课程课程 WHERE WHERE WHERE WHERE 课程名称课程名称课程名称课程名称=大学英语大学英语大学英语大学英语)=100)=100)=100)=100 BREA

18、K BREAK BREAK BREAK ELSE ELSE ELSE ELSE SELECT SELECT SELECT SELECT gavggavggavggavg=AVG(=AVG(=AVG(=AVG(成绩成绩成绩成绩) FROM ) FROM ) FROM ) FROM 选课选课选课选课 WHERE WHERE WHERE WHERE 课程号课程号课程号课程号= = = = (SELECT (SELECT (SELECT (SELECT 课程号课程号课程号课程号 FROM FROM FROM FROM 课程课程课程课程 WHERE WHERE WHERE WHERE 课程名称课程名称课

19、程名称课程名称=大学英语大学英语大学英语大学英语) END END END END PRINT COUN PRINT COUN PRINT COUN PRINT COUN18184. RETURN4. RETURN语句语句语句格式：语句格式：RETURN 整数功能：无条件所在批处理、存储过程或触发器。可以返回一个整数值6.1.3控制流语句1919举例题目：根据给定的学号（题目：根据给定的学号（ paramparam ）检查学生的）检查学生的平均成绩，若平均成绩，若7575，将返回状态代码，将返回状态代码 1 1，将返回，将返回状态代码状态代码 2 2。USE USE 教学数据库教学数据库C

20、REATE PROCEDURE CREATE PROCEDURE checkavgcheckavg paramparam varchar(10)varchar(10)ASASIF (SELECT AVG(IF (SELECT AVG(成绩成绩) FROM ) FROM 选课选课 WHERE WHERE 学号学号 = = paramparam) 75) 75 RETURN RETURN ELSEELSE RETURN RETURN 2020执行存储过程：declare aa declare aa smallintsmallintexec aa=exec aa=checkavgcheckavg 0

21、0150236 00150236if aa=if aa= print print 小于小于7575else else print print 大于大于757521216.1.4 EXECUTE语句功能：执行函数、存储过程功能：执行函数、存储过程语法格式：语法格式：EXECUTE EXECUTE outputoutput22226.1.5 注释. .单行注释单行注释（两个减号）（两个减号）. .多行注释多行注释/*/* */ /23236.1.6 程序设计举例题目：转帐，若帐户的余额大于等于题目：转帐，若帐户的余额大于等于题目：转帐，若帐户的余额大于等于题目：转帐，若帐户的余额大于等于10010

22、0100100元，从帐户上支元，从帐户上支元，从帐户上支元，从帐户上支取取取取100100100100元，存入帐户元，存入帐户元，存入帐户元，存入帐户. . . .否则，不执行转帐操作。否则，不执行转帐操作。否则，不执行转帐操作。否则，不执行转帐操作。begin transactionbegin transactionupdate update 帐户帐户帐户帐户 set set 余额余额余额余额= =余额余额余额余额-100 where -100 where 帐户帐户帐户帐户=A=AIF (SELECT IF (SELECT 余额余额余额余额 from from 帐户帐户帐户帐户 where

23、where 帐户帐户帐户帐户=A)=0=A)=0 begin begin print print金额不够。转帐失败！金额不够。转帐失败！金额不够。转帐失败！金额不够。转帐失败！ rollback transaction rollback transaction end endelseelse begin begin update update 帐户帐户帐户帐户 set set 余额余额余额余额= =余额余额余额余额+100 where +100 where 帐户帐户帐户帐户=B=B print print转帐成功！转帐成功！转帐成功！转帐成功！ commit transaction commi

24、t transactionend end 2424事务概述事务是作为单个逻辑工作单元事务是作为单个逻辑工作单元执行执行的一系列操作。的一系列操作。事务的属性事务的属性（ACID)ACID)原子性原子性原子性原子性：原子工作单元：原子工作单元：原子工作单元：原子工作单元一致性一致性一致性一致性：保证数据的一致性：保证数据的一致性：保证数据的一致性：保证数据的一致性隔离性隔离性隔离性隔离性：并发事务之间所做的修改要：并发事务之间所做的修改要：并发事务之间所做的修改要：并发事务之间所做的修改要隔离隔离隔离隔离并发操作：几个用户程序同时读写一个并发操作：几个用户程序同时读写一个并发操作：几个用户程序

25、同时读写一个并发操作：几个用户程序同时读写一个数据的情况数据的情况数据的情况数据的情况持久性持久性持久性持久性：对系统的影响要持久：对系统的影响要持久：对系统的影响要持久：对系统的影响要持久2525事务操作语法格式：语法格式：1.1.BEGIN TRANSACTIONBEGIN TRANSACTIONBEGIN TRANSACTIONBEGIN TRANSACTION 事务开始事务开始事务开始事务开始2.2.COMMIT TRANSACTION COMMIT TRANSACTION COMMIT TRANSACTION COMMIT TRANSACTION 提交提交提交提交3.3.ROLLBA

26、CK TRANSACTIONROLLBACK TRANSACTIONROLLBACK TRANSACTIONROLLBACK TRANSACTION回滚回滚回滚回滚说明：如果没有明确给出说明：如果没有明确给出说明：如果没有明确给出说明：如果没有明确给出BEGIN BEGIN BEGIN BEGIN TRANSACTIONTRANSACTIONTRANSACTIONTRANSACTION语句，则语句，则语句，则语句，则SQL ServerSQL ServerSQL ServerSQL Server是是是是将将将将每个每个每个每个SQLSQLSQLSQL语句语句语句语句都当成一个事务进行都当成一个

27、事务进行都当成一个事务进行都当成一个事务进行执行执行执行执行26266.3 存储过程6.3.16.3.16.3.16.3.1存储过程的概念存储过程的概念存储过程的概念存储过程的概念是一组被编译在一起的是一组被编译在一起的是一组被编译在一起的是一组被编译在一起的T-SQLT-SQLT-SQLT-SQL语句的集合，语句的集合，语句的集合，语句的集合，它们被集合在一起以完成一个特定的任务它们被集合在一起以完成一个特定的任务它们被集合在一起以完成一个特定的任务它们被集合在一起以完成一个特定的任务。存储过程的分类存储过程的分类存储过程的分类存储过程的分类系统存储过程系统存储过程系统存储过程系统存储过程扩

28、展存储过程（提供从扩展存储过程（提供从扩展存储过程（提供从扩展存储过程（提供从SQL ServerSQL ServerSQL ServerSQL Server到外部程到外部程到外部程到外部程序的接口，以便进行各种维护活动）序的接口，以便进行各种维护活动）序的接口，以便进行各种维护活动）序的接口，以便进行各种维护活动）用户自定义的存储过程用户自定义的存储过程用户自定义的存储过程用户自定义的存储过程27276.3.2 创建存储过程创建过程语法格式：创建过程语法格式：CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE

29、数据类型，数据类型，数据类型，数据类型，OUTPUTOUTPUTOUTPUTOUTPUTASASASAS SQL SQL SQL 执行过程语法格式：执行过程语法格式：EXECUTEEXECUTEEXECUTEEXECUTE或或或或EXEC EXEC EXEC EXEC 变量变量变量变量= = = =值值值值删除存储过程语法格式：删除存储过程语法格式： DROP PROCEDURE DROP PROCEDURE DROP PROCEDURE DROP PROCEDURE 2929例：检索某个学生例：检索某个学生( (学号为学号为 0111111101111111）的成绩单，包括姓名、）的成绩单

30、，包括姓名、课程名、分数。存储过程名为课程名、分数。存储过程名为sc_ grade sc_ grade IF EXISTS(SELECT name FROM IF EXISTS(SELECT name FROM IF EXISTS(SELECT name FROM IF EXISTS(SELECT name FROM sysobjectssysobjectssysobjectssysobjects WHERE name= WHERE name= WHERE name= WHERE name=sc_gradesc_gradesc_gradesc_grade AND type=P) AND typ

31、e=P) AND type=P) AND type=P)DROP PROCEDURE DROP PROCEDURE DROP PROCEDURE DROP PROCEDURE sc_gradesc_gradesc_gradesc_gradeGOGOGOGOCREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE CREATE PROCEDURE sc_gradesc_gradesc_gradesc_grade SNO CHAR(8) SNO CHAR(8) SNO CHAR(8) SNO CHAR(8)ASASASAS SELECT SELECT S

32、ELECT SELECT 姓名姓名姓名姓名, , , ,课程名称课程名称课程名称课程名称, , , ,成绩成绩成绩成绩 FROM FROM FROM FROM 学生学生学生学生 S, S, S, S,课程课程课程课程 C , C , C , C ,选课选课选课选课 SC SC SC SC WHERE S. WHERE S. WHERE S. WHERE S.学号学号学号学号=SC.=SC.=SC.=SC.学号学号学号学号 AND C. AND C. AND C. AND C.课程号课程号课程号课程号=SC.=SC.=SC.=SC.课程号课程号课程号课程号 AND S. AND S. AND S

33、. AND S.学号学号学号学号=SNO=SNO=SNO=SNOGO GO GO GO 3030执行过程EXEC EXEC sc_gradesc_grade 01111111 01111111或或EXEC EXEC sc_gradesc_grade snosno= = 01111111011111110111111101111111 31316.4 数据库触发器6.4.1 6.4.1 触发器的概念触发器的概念是一种实现复杂完整性约束的特是一种实现复杂完整性约束的特殊存储过程，是能够在符合条件是殊存储过程，是能够在符合条件是自动触发的自动触发的SQLSQL程序。程序。 1.1.触发器的特点触发

34、器的特点 2.2.触发器的优点触发器的优点37376.4.1 触发器的概念若触发器所依赖的表中有约束，则若触发器所依赖的表中有约束，则在执行时，在执行时，约束优于触发器约束优于触发器，而且，而且如果在操作中触发器和约束发生冲如果在操作中触发器和约束发生冲突，触发器将不执行。突，触发器将不执行。触发器操作是一个事务操作。触发器操作是一个事务操作。38386.4.1 触发器的概念在下列情况下可以考虑使用触在下列情况下可以考虑使用触发器：发器：强制比强制比CHECKCHECK约束复杂的数据完约束复杂的数据完整性整性使用自定义的错误信息和执行复使用自定义的错误信息和执行复杂的错误处理杂的错误处理实现多

36、除触发器语法格式：删除触发器语法格式： DROP TRIGGER DROP TRIGGER DROP TRIGGER DROP TRIGGER 4040语句解释：AFTER:AFTER:AFTER:AFTER:指定触发器只有在触发指定触发器只有在触发指定触发器只有在触发指定触发器只有在触发 SQL SQL SQL SQL 语句中指定的所有语句中指定的所有语句中指定的所有语句中指定的所有操作都已成功执行后才激发。所有的引用级联操作和操作都已成功执行后才激发。所有的引用级联操作和操作都已成功执行后才激发。所有的引用级联操作和操作都已成功执行后才激发。所有的引用级联操作和约束检查也必须成功完成后，才

37、能执行此触发器。约束检查也必须成功完成后，才能执行此触发器。约束检查也必须成功完成后，才能执行此触发器。约束检查也必须成功完成后，才能执行此触发器。如果仅指定如果仅指定如果仅指定如果仅指定 FOR FOR FOR FOR 关键字，则关键字，则关键字，则关键字，则 AFTER AFTER AFTER AFTER 是默认设置。是默认设置。是默认设置。是默认设置。不能在视图上定义不能在视图上定义不能在视图上定义不能在视图上定义 AFTER AFTER AFTER AFTER 触发器。触发器。触发器。触发器。INSTEAD OF:INSTEAD OF:INSTEAD OF:INSTEAD OF:指定执

38、行触发器而不是执行触发指定执行触发器而不是执行触发指定执行触发器而不是执行触发指定执行触发器而不是执行触发 SQL SQL SQL SQL 语语语语句，从而替代触发语句的操作。句，从而替代触发语句的操作。句，从而替代触发语句的操作。句，从而替代触发语句的操作。 INSERT,UPDATE,DELETE:INSERT,UPDATE,DELETE:INSERT,UPDATE,DELETE:INSERT,UPDATE,DELETE:是指定在表或视图上执行哪些是指定在表或视图上执行哪些是指定在表或视图上执行哪些是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。必须至少指数据修改语句时将激活

39、触发器的关键字。必须至少指数据修改语句时将激活触发器的关键字。必须至少指数据修改语句时将激活触发器的关键字。必须至少指定一个选项。在触发器定义中允许使用以任意顺序组定一个选项。在触发器定义中允许使用以任意顺序组定一个选项。在触发器定义中允许使用以任意顺序组定一个选项。在触发器定义中允许使用以任意顺序组合的这些关键字。如果指定的选项多于一个，需用逗合的这些关键字。如果指定的选项多于一个，需用逗合的这些关键字。如果指定的选项多于一个，需用逗合的这些关键字。如果指定的选项多于一个，需用逗号分隔这些选项。号分隔这些选项。号分隔这些选项。号分隔这些选项。4141使用触发器的限制(1) CREATE TR

40、IGGER (1) CREATE TRIGGER 必须是批处理必须是批处理中的第一条语句，并且只能应用中的第一条语句，并且只能应用到一个表中。到一个表中。 (2) (2) 触发器只能在当前的数据库中触发器只能在当前的数据库中创建，但触发器可以引用当前数创建，但触发器可以引用当前数据库的外部对象。据库的外部对象。 (3) (3) 如果指定触发器所有者名限定如果指定触发器所有者名限定触发器，要以相同的方式限定表触发器，要以相同的方式限定表名。名。 (4) (4) 在同一在同一CREATE TRIGGERCREATE TRIGGER语句中，语句中，可以为多种操作（如可以为多种操作（如 INSERT

41、INSERT 和和 UPDATEUPDATE）定义相同的触发器操作。）定义相同的触发器操作。 4242使用触发器的限制(5) (5) (5) (5) 一个表的外键在一个表的外键在一个表的外键在一个表的外键在 DELETEDELETEDELETEDELETE、UPDATE UPDATE UPDATE UPDATE 操操操操作上定义了级联，不能在该表上定义作上定义了级联，不能在该表上定义作上定义了级联，不能在该表上定义作上定义了级联，不能在该表上定义 INSTEAD OF DELETEINSTEAD OF DELETEINSTEAD OF DELETEINSTEAD OF DELETE、INSTE

42、AD OF INSTEAD OF INSTEAD OF INSTEAD OF UPDATE UPDATE UPDATE UPDATE 触发器。触发器。触发器。触发器。 (6) (6) (6) (6) 触发器中不允许包含以下触发器中不允许包含以下触发器中不允许包含以下触发器中不允许包含以下 T-SQL T-SQL T-SQL T-SQL 语句：语句：语句：语句： CREATE DATABASE CREATE DATABASE 、ALTER DATABASE ALTER DATABASE 、LOAD DATABASE LOAD DATABASE 、RESTORE DATABASE RESTORE

43、DATABASE 、DROP DROP DATABASEDATABASE、LOAD LOG LOAD LOG 、RESTORE LOG RESTORE LOG 、 DISK INITDISK INIT、DISK RESIZEDISK RESIZE和和RECONFIGURERECONFIGURE 43436.4.3触发器的工作原理触发器的工作原理当触发器被触发时，系统会创建两个当触发器被触发时，系统会创建两个专用临时表：专用临时表：insertedinserted表和表和deleteddeleted表。表。这两个表由系统来维护，不允许用户直这两个表由系统来维护，不允许用户直接对这两个表进行修改

44、（可以读）。它接对这两个表进行修改（可以读）。它们存放于内存中，不存放在数据库中。们存放于内存中，不存放在数据库中。这两个表的结构总是与被该触发器作用这两个表的结构总是与被该触发器作用的表的结构相同。触发器工作完成后，的表的结构相同。触发器工作完成后，与该触发器相关的这两个表也会被删除。与该触发器相关的这两个表也会被删除。 44446.4.3触发器的工作原理触发器的工作原理insterted表：存放由于INSERT或UPDATE语句的执行而导致要加到该触发表中去的所有新行。即用于插入或更新表的新行值，在插入或更新表的同时，也将其副本存入insterted表中。因此，在insterted表中的

45、行总是与触发表中的新行相同。45456.4.3触发器的工作原理触发器的工作原理deleted表：存放由于DELETE或UPDATE语句的执行而导致要从该触发表中删除的所有行。也就是说，把触发表中要删除或要更新的旧行移到deleted表中。因此，deleted表和触发表不会有相同的行。46466.4.3触发器的工作原理触发器的工作原理对对对对INSERTINSERTINSERTINSERT操作，只在操作，只在操作，只在操作，只在instertedinstertedinstertedinsterted表中保存所插入的表中保存所插入的表中保存所插入的表中保存所插入的新行，而新行，而新行，而新行，而

46、deleteddeleteddeleteddeleted表中无一行数据。表中无一行数据。表中无一行数据。表中无一行数据。对于对于对于对于DELETEDELETEDELETEDELETE操作，只在操作，只在操作，只在操作，只在deleteddeleteddeleteddeleted表中保存被删除表中保存被删除表中保存被删除表中保存被删除的旧行，而的旧行，而的旧行，而的旧行，而instertedinstertedinstertedinsterted表中无一行数据。表中无一行数据。表中无一行数据。表中无一行数据。对于对于对于对于UPDATEUPDATEUPDATEUPDATE操作，可以将它考虑为操

47、作，可以将它考虑为操作，可以将它考虑为操作，可以将它考虑为DELETEDELETEDELETEDELETE操作和操作和操作和操作和INSERTINSERTINSERTINSERT操作的结果，所以在操作的结果，所以在操作的结果，所以在操作的结果，所以在insertedinsertedinsertedinserted表中存放着表中存放着表中存放着表中存放着更新后的新行值，更新后的新行值，更新后的新行值，更新后的新行值，deleteddeleteddeleteddeleted表中存放着更新前的旧表中存放着更新前的旧表中存放着更新前的旧表中存放着更新前的旧行值。行值。行值。行值。 4747例例: :在

48、学生表上创建触发器在学生表上创建触发器reminderreminder，若在学生表，若在学生表中添加、更改和删除数据，则将向客户端显示信息。中添加、更改和删除数据，则将向客户端显示信息。 USE USE USE USE 教学教学教学教学 IF EXISTS (SELECT name FROM IF EXISTS (SELECT name FROM IF EXISTS (SELECT name FROM IF EXISTS (SELECT name FROM sysobjectssysobjectssysobjectssysobjects WHERE name = WHERE name = WH

49、ERE name = WHERE name = reminderreminderreminderreminder AND AND AND AND type = TR) type = TR) type = TR) type = TR) DROP TRIGGER DROP TRIGGER DROP TRIGGER DROP TRIGGER reminderreminderreminderreminder GO GO GO GO CREATE TRIGGER CREATE TRIGGER CREATE TRIGGER CREATE TRIGGER reminderreminderreminderre

50、minder ON ON ON ON 学生学生学生学生FOR INSERT, UPDATE ,DELETEFOR INSERT, UPDATE ,DELETEFOR INSERT, UPDATE ,DELETEFOR INSERT, UPDATE ,DELETE AS AS AS AS print print print print 注意：学生表数据被修改。注意：学生表数据被修改。注意：学生表数据被修改。注意：学生表数据被修改。 SELECT * FROM INSERTED SELECT * FROM INSERTED SELECT * FROM INSERTED SELECT * FROM

51、INSERTED SELECT * FROM DELETED SELECT * FROM DELETED SELECT * FROM DELETED SELECT * FROM DELETED GO GO GO GO4848INSERTEDINSERTED表表表表DELETEDDELETED表表表表insert into insert into 学生学生( (学号学号, ,姓名姓名) values (9999,dd) values (9999,dd)UPDATE UPDATE 学生学生 SET SET 姓名姓名=WWWW =WWWW WHERE WHERE 学号学号=9999=9999DELE

52、TEDDELETED表表表表INSERTEDINSERTED表表表表delete from delete from 学生学生 where where 学号学号=9999=99994949举例：创建一触发器，当向成绩表插入一记录时，检查该记录的举例：创建一触发器，当向成绩表插入一记录时，检查该记录的学号在学生表是否存在，检查课程号在课程表中是否存在，若有学号在学生表是否存在，检查课程号在课程表中是否存在，若有一项为否，则不允许插入。一项为否，则不允许插入。Use Use Use Use 教学教学教学教学GOGOGOGOCREATE TRIGGER CREATE TRIGGER CREATE TR

53、IGGER CREATE TRIGGER check_trigcheck_trigcheck_trigcheck_trig ON ON ON ON 成绩成绩成绩成绩 FOR INSERTFOR INSERTFOR INSERTFOR INSERTASASASAS IF EXISTS (SELECT * IF EXISTS (SELECT * IF EXISTS (SELECT * IF EXISTS (SELECT * FROM inserted a FROM inserted a FROM inserted a FROM inserted a WHERE a.WHERE a.WHERE a.

54、WHERE a.学号学号学号学号 NOT IN (SELECT NOT IN (SELECT NOT IN (SELECT NOT IN (SELECT 学号学号学号学号 FROM FROM FROM FROM 学生学生学生学生 ) ) ) ) OR a. OR a. OR a. OR a.课程编号课程编号课程编号课程编号 NOT IN (SELECT NOT IN (SELECT NOT IN (SELECT NOT IN (SELECT 课程编号课程编号课程编号课程编号 FROM FROM FROM FROM 课程课程课程课程 )BEGINBEGINBEGINBEGIN RAISERROR

55、 (RAISERROR (RAISERROR (RAISERROR (违背数据的一致性！违背数据的一致性！违背数据的一致性！违背数据的一致性！,10,1),10,1),10,1),10,1) ROLLBACK TRANSACTIONROLLBACK TRANSACTIONROLLBACK TRANSACTIONROLLBACK TRANSACTIONENDENDENDENDGOGOGOGO5050insert into 成绩 values (3012,C6,90)SELECT * FROM SELECT * FROM 成绩成绩 WHERE WHERE 课程编号课程编号 LIKE %6LIKE

56、%6执行插入操作：insert into 成绩 values (5012,C6,90)服务器服务器: : 消息消息 547547，级别，级别 1616，状态，状态 1 1，行，行 1 1INSERT INSERT 语句与语句与 COLUMN FOREIGN KEY COLUMN FOREIGN KEY 约束约束 FK_FK_成绩成绩_学学号号_6B24EA82 _6B24EA82 冲突。该冲突发生于数据库冲突。该冲突发生于数据库教学教学，表，表学生学生, , column column 学号学号。语句已终止。语句已终止。5151分别删除了成分别删除了成绩表、课程表、绩表、课程表、学生表

57、的联系学生表的联系insert into 成绩 values (5012,C6,90)52526.2 游标及其应用6.2.16.2.1游标的概念游标的概念如果要求每次只显示表格中的一行，该如何处理？这在将T-SQL嵌入到其他高级语言（如VC、VB、Delphi等）的编程中经常用到。 53536.2.1游标的概念游标游标游标游标(cursor)(cursor)(cursor)(cursor)是一个存储区域，是一个存储区域，是一个存储区域，是一个存储区域，用来存放结果集。游标的指针，用来存放结果集。游标的指针，用来存放结果集。游标的指针，用来存放结果集。游标的指针，可以指向与它相关联的结果集中可以

58、指向与它相关联的结果集中可以指向与它相关联的结果集中可以指向与它相关联的结果集中的任意一行，以便对当前位置的的任意一行，以便对当前位置的的任意一行，以便对当前位置的的任意一行，以便对当前位置的行进行处理。行进行处理。行进行处理。行进行处理。游标提供了对一个结果集进行逐游标提供了对一个结果集进行逐游标提供了对一个结果集进行逐游标提供了对一个结果集进行逐行处理的能力：行处理的能力：行处理的能力：行处理的能力：在结果集中定位特定行在结果集中定位特定行在结果集中定位特定行在结果集中定位特定行从结果集的当前位置检索行从结果集的当前位置检索行从结果集的当前位置检索行从结果集的当前位置检索行支持对结果集中当

59、前位置的行进行支持对结果集中当前位置的行进行支持对结果集中当前位置的行进行支持对结果集中当前位置的行进行数据处理（修改数据处理（修改数据处理（修改数据处理（修改/ / / /删除）删除）删除）删除）54546.2.2 游标的用法声明游标声明游标打开游标打开游标处理数据（读取处理数据（读取/ /修改修改/ /删除）删除）可以和其他可以和其他T-SQLT-SQL语句配合灵活使语句配合灵活使用用关闭游标（与打开游标配对）关闭游标（与打开游标配对）释放游标（与声明游标配对，此时释放游标（与声明游标配对，此时释放分配给游标的所有资源）释放分配给游标的所有资源）5555定义游标定义游标DECLARE DE

60、CLARE CURSORCURSORFORFOR SELECT 打开游标打开游标OPEN OPEN 使用游标使用游标FETCH NEXT|PRIOR|FIRST|LASTFETCH NEXT|PRIOR|FIRST|LASTFROM FROM INTO INTO 关闭游标关闭游标CLOSE CLOSE 释放游标释放游标DEALLOCATION DEALLOCATION 5656游标的状态值使用使用FETCH_STATUS:FETCH_STATUS:该全局变量该全局变量该全局变量该全局变量/ / / /配置函数返回被最后配置函数返回被最后配置函数返回被最后配置函数返回被最后FETCHFETCHF

61、ETCHFETCH语句执行的游标的状态，返回语句执行的游标的状态，返回语句执行的游标的状态，返回语句执行的游标的状态，返回类型为类型为类型为类型为intintintint： 0 0 0 0：FETCHFETCHFETCHFETCH语句成功语句成功语句成功语句成功 1:FETCH1:FETCH1:FETCH1:FETCH语句失败语句失败语句失败语句失败, , , , 2 2 2 2：被提取的行不存在：被提取的行不存在：被提取的行不存在：被提取的行不存在5757例:显示学号是02开头的学生的姓名、性别和年龄DECLARE DECLARE DECLARE DECLARE snamesnamesnam

62、esname char(8),ssex char(2),sage char(2) char(8),ssex char(2),sage char(2) char(8),ssex char(2),sage char(2) char(8),ssex char(2),sage char(2)DECLARE DECLARE DECLARE DECLARE stu_crstu_crstu_crstu_cr CURSOR CURSOR CURSOR CURSOR FOR SELECT FOR SELECT FOR SELECT FOR SELECT 姓名姓名姓名姓名, , , ,性别性别性别性别, , ,

63、,year(getdateyear(getdateyear(getdateyear(getdate()-year()-year()-year()-year(出生日期出生日期出生日期出生日期) ) ) ) FROM FROM FROM FROM 学生学生学生学生 WHERE WHERE WHERE WHERE 学号学号学号学号 like 02%like 02%like 02%like 02%OPEN OPEN OPEN OPEN stu_crstu_crstu_crstu_crFETCH NEXT FROM FETCH NEXT FROM FETCH NEXT FROM FETCH NEXT F

64、ROM stu_crstu_crstu_crstu_cr INTO INTO INTO INTO sname,ssex,sagesname,ssex,sagesname,ssex,sagesname,ssex,sageWHILE FETCH_STATUS=0WHILE FETCH_STATUS=0WHILE FETCH_STATUS=0WHILE FETCH_STATUS=0 BEGIN BEGIN BEGIN BEGIN PRINT space(2)+sname+ssex+space(2)+cast(sage as PRINT space(2)+sname+ssex+space(2)+cas

65、t(sage as PRINT space(2)+sname+ssex+space(2)+cast(sage as PRINT space(2)+sname+ssex+space(2)+cast(sage as char(8)char(8)char(8)char(8) FETCH NEXT FROM FETCH NEXT FROM FETCH NEXT FROM FETCH NEXT FROM stu_crstu_crstu_crstu_cr INTO INTO INTO INTO sname,ssex,sagesname,ssex,sagesname,ssex,sagesname,ssex,

66、sage PRINT FETCH_STATUS PRINT FETCH_STATUS PRINT FETCH_STATUS PRINT FETCH_STATUS END END END ENDCLOSE CLOSE CLOSE CLOSE stu_crstu_crstu_crstu_crDEALLOCATE DEALLOCATE DEALLOCATE DEALLOCATE stu_crstu_crstu_crstu_cr585859596.5 嵌入式SQL6.5.16.5.16.5.16.5.1嵌入式嵌入式嵌入式嵌入式SQLSQLSQLSQL的特点的特点的特点的特点区分区分区分区分SQLSQ

67、LSQLSQL语句与主语言语句，需要语句与主语言语句，需要语句与主语言语句，需要语句与主语言语句，需要: : : : 前缀：前缀：前缀：前缀：EXEC SQLEXEC SQLEXEC SQLEXEC SQL 结束标志：随主语言的不同而不同结束标志：随主语言的不同而不同结束标志：随主语言的不同而不同结束标志：随主语言的不同而不同通过主变量实现通过主变量实现通过主变量实现通过主变量实现SQLSQLSQLSQL语句与主语言交换数据语句与主语言交换数据语句与主语言交换数据语句与主语言交换数据嵌入式嵌入式嵌入式嵌入式SQLSQLSQLSQL语句中可以使用主语言的程序变量语句中可以使用主语言的程序变量语

68、句中可以使用主语言的程序变量语句中可以使用主语言的程序变量来输入或输出数据来输入或输出数据来输入或输出数据来输入或输出数据在在在在SQLSQLSQLSQL语句中使用的主语言程序变量简称为主语句中使用的主语言程序变量简称为主语句中使用的主语言程序变量简称为主语句中使用的主语言程序变量简称为主变量，在变量名前需要加变量，在变量名前需要加变量，在变量名前需要加变量，在变量名前需要加： 60606.5.2嵌入式SQL的游标6161嵌入式SQL执行过程主语言主语言 + + 嵌入嵌入SQLSQL 预处理预处理主语言主语言 + + 函数调用函数调用主语言编译器主语言编译器主语言执行程序主语言执行程序 62626.5.3嵌入式SQL的预处理6363

展开阅读全文

第六章SQL编程技术

最新文档