第8章-1 SQL编程基础.ppt

1、第8章-1 SQL编程技术,概述：在第七章里已经详细地介绍了交互式SQL语言，这种SQL语言仅限于以单命令的方式操作数据库，缺少灵活的数据处理能力。当前，许多DBMS系统提供了编程环境下的SQL语言。在标准SQL语言基础上，将高级语言程序设计的方法引入SQL语言，扩充了程序流程控制语句，使其既有较强的数据库操作功能，又兼顾高级语言的编程特点。本章将在交互式SQL的基础上，增加有关SQL程序设计的内容，其中包括局部变量、全局变量、控制流语句、游标等编程基础知识。然后，介绍存储过程、触发器和嵌入式SQL等具体的SQL编程技术。,2,第8章 SQL编程技术,SQL编程兼顾交互式SQL和高级语言编程的

2、特点，在学习的过程中，要善于理解SQL语言和高级程序设计语言的区别，用熟悉的知识来理解新学的内容，以达到事半功倍的效果。 本文以SQL Server2005为实验平台，SQL程序也以SQL Server2005的编程方法为基础。且尽量选择比较通用的方法，便于读者能够学以致用。,3,8.1 SQL编程基础,程序设计必然要运用内存变量、分支、循环等程序设计方法，SQL程序设计也不例外，都有这些基本的程序设计元素。,4,8.1 SQL编程基础 8.1.1 批处理,批处理和脚本只是SQL Server中使用的两个术语，为了编写SQL Server程序，我们这里先给出这两个概念的含义。 批处理是一个以“

3、GO”结束的SQL语句集，这些语句是作为一个组来执行并一起提交的。 “GO”是SQL Server2005批处理的结束标志，不是SQL语句。,5,8.1 SQL编程基础 8.1.1 批处理,例如，下面的SQL程序中，包含3个批处理。 CREATE TABLE 学院( 学院名称 CHAR(12) PRIMARY KEY , 院长 CHAR(8), 学院地址 CHAR(16), 办公电话 CHAR(8), 编制人数 SMALLINT) GO INSERT INTO 学院 VALUES(计算机学院,刘海洋, 一号楼604, 85905878,50) INSERT INTO 学院 VALUES(商学院

4、,江山, 五号楼301, 85908851,60) GO SELECT * FROM 学院 GO,6,8.1 SQL编程基础 8.1.1 批处理,说明： （1）如果一个批处理的所有语句正确执行，则整个批处理顺利提交；否则批处理中，只要有一个语句错误，则整个批处理都不提交。 （2）有些语句不能组合在同一批处理中，例如，ALTER TABLE修改表和UPDATE 更新表中的新列放在一个批处理中，将会出现错误。,7,8.1 SQL编程基础 8.1.1 批处理,将包含多个批处理的一个程序存放在以后缀为“.SQL”的文件中，该文件称为脚本。,8,8.1 SQL编程基础 8.1.2 变量,1变量的类型 在

5、SQL Server2005中，变量可分为局部变量和全局变量。局部变量是用户定义的变量，全局变量是由系统定义的变量。 局部变量的名称以“”开头，全局变量的名称以“”开头。局部变量和全局变量的主要区别是作用域不同，局部变量的作用域是在声明变量的批处理、存储过程或者语句组中，而全局变量的作用域是则全局。,9,8.1 SQL编程基础 8.1.2 变量,2. 变量的声明 变量声明语句格式： DECLARE 变量名称 数据类型 ，变量名称 数据类型 例8.1 声明Name 、Sex 和 Age三个局部变量。 DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT,

6、10,8.1 SQL编程基础 8.1.2 变量,3. 变量的赋值 可以用SET或SELECT语句给变量赋值。,11,8.1 SQL编程基础 8.1.2 变量,【格式一】 SET 变量名称=表达式 例8.2 给Name 、Sex 和 Age三个局部变量赋值。 DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT SET Name=李华 SET Sex=女 SET Age=20 GO,12,8.1 SQL编程基础 8.1.2 变量,【格式二】 SELECT 变量名称=表达式 例8.3 给局部变量Name赋值。 DECLARE Name CHAR(8) SE

7、LECT Name=李华,13,8.1 SQL编程基础 8.1.2 变量,【格式三】 将查询结果指定给变量 （查询结果必须是单一值）。语句格式： SELECT 变量名称=字段（或函数） FROM 表名 WHERE 条件表达式,14,8.1 SQL编程基础 8.1.2 变量,例8.4 有学生关系 R（学号，姓名，性别，年龄），检索学号为200490111的学生姓名、性别和年龄，并将查询结果传递给局部变量Name、Sex和Age。 DECLARE Name CHAR(8), Sex CHAR(2), Age SMALLINT SELECT Name=姓名，Sex=性别，Age=年龄 FROM 学生

8、 WHERE 学号=200490111 GO,15,8.1 SQL编程基础 8.1.3 SQL的控制流语句,控制流语句可以控制SQL语句的执行顺序，在存储过程、触发器和批处理中很有用。,16,8.1 SQL编程基础 8.1.3 SQL的控制流语句,1. BEGIN.END 功能：将一组SQL语句作为一个语句块。 语法格式： BEGIN END 说明:BEGIN.END语句的作用实际上相当于C语言中的作用，其间的语句序列作为一个块执行。允许BEGIN.END 语句块嵌套。通常BEGIN.END 语句与IF.ELSE或WHILE等语句控制流语句一起使用。,17,8.1 SQL编程基础 8.1.3

9、SQL的控制流语句,2. IF.ELSE语句 功能：条件分支语句，若条件为真，则执行语句序列1；否则执行语句序列2。 语法格式： IF ELSE ,18,8.1 SQL编程基础 8.1.3 SQL的控制流语句,例8.5 在图书馆数据库中，有图书关系R（书号，书名，类别，出版社，定价）。如果计算机类图书的平均定价大于30，则显示“平均价格超过30元”，反之显示“平均价格不超过30元”，并列出所有计算机类的书名。程序流程如图8.1所示。,19,8.1 SQL编程基础 8.1.3 SQL的控制流语句,图8.1 例8.5的程序流程图,开始,查询,均价30,打印,列出书名,结束,N,Y,20,8.1 S

10、QL编程基础 8.1.3 SQL的控制流语句,USE 图书馆 GO IF (SELECT AVG(定价) FROM R WHERE 类别= 计算机) 30 PRINT 平均定价高于30元 ELSE BEGIN PRINT 平均定价不超过30元 SELECT 书名 FROM R WHERE类别= 计算机 END,21,8.1 SQL编程基础 8.1.3 SQL的控制流语句,例8.6依据例8.5中的关系R。如果计算机类图书的平均定价小于30元，则列出所有计算机类的书名；如果计算机类图书的平均价格高40元，则显示“价格昂贵”。程序流程如图8.2所示。,22,8.1 SQL编程基础 8.1.3 SQL

11、的控制流语句,图8.2 例8.6的程序流程图,开始,查询,均价30,打印昂贵,列出书名,结束,均价40,打印适中,N,Y,N,Y,23,8.1 SQL编程基础 8.1.3 SQL的控制流语句,USE 图书馆 GO IF (SELECT AVG(定价) FROM R WHERE 类别= 计算机) 40 PRINT 图书价格昂贵 ELSE PRINT 图书价格适中 GO,24,8.1 SQL编程基础 8.1.3 SQL的控制流语句,3.CASE CASE用于多分支选择。 格式一： CASE WHEN THEN 结果表达式1 WHEN THEN 结果表达式2 ELSE 结果表达式n END,25,8

12、.1 SQL编程基础 8.1.3 SQL的控制流语句,功能：按顺序对每个WHEN子句的条件表达式进行计算，若计算第一个WHEN的条件表达式结果为TRUE，则得到THEN后的结果表达式；若计算结果不为TRUE，则计算第二个WHEN的条件表达式，以此类推，当最后一个WHEN的条件表达式结果不为TRUE时，则得到ELSE后面的结果表达式。 注意：CASE每次只执行一个分支。,26,8.1 SQL编程基础 8.1.3 SQL的控制流语句,格式二 CASE input_expression WHEN when_expression THEN result_expression n ELSE else_r

13、esult_expression END 功能：按顺序扫描每个WHEN子句，若其when_expression与input_expression值相同，则执行result_expression，之后执行END后的其他T-SQL语句，若没有符合条件的WHEN子句，则在指定了ELSE子句时，执行else_result_expression，否则返回NULL值。,27,8.1 SQL编程基础 8.1.3 SQL的控制流语句,CASE的使用 （1）将CASE用于SELECT语句 实验5中第4题，按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：性别为男显示为男 生，性别为

14、女显示为女 生，其他显示为“条件不明”；,28,8.1 SQL编程基础 8.1.3 SQL的控制流语句,实验5中第13题，显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；,29,8.1 SQL编程基础 8.1.3 SQL的控制流语句,（2）将CASE用于UPDATE语句 SELECT * INTO Albumback FROM Album UPDATE Albumback SET Alb

15、umLanguage= CASE WHEN AlbumLanguage=汉语普通话 THEN Chinese WHEN AlbumLanguage=英语 THEN English WHEN AlbumLanguage=日语 THEN Japanese ELSE Other END,30,8.1 SQL编程基础 8.1.3 SQL的控制流语句,(3)将CASE用于INSERT语句 DECLARE AlbumID int, SumQuantity int SET AlbumID =1 SELECT SumQuantity =SUM(Quantity) FROM sale WHERE AlbumID

16、= AlbumID INSERT INTO AlbumEvaluate VALUES(AlbumID, CASE WHTN SumQuantity=10000 THEN 畅销专辑 WHTN SumQuantity=5000 THEN 较畅销专辑 WHTN SumQuantity=100 THEN 普通专辑 ELSE 滞销专辑 END,31,8.1 SQL编程基础 8.1.3 SQL的控制流语句,4. 循环语句 功能：重复执行某个SQL语句或语句块。当循环条件为真时，执行这些语句，直到循环条件是假为止。,32,8.1 SQL编程基础 8.1.3 SQL的控制流语句,语法格式： WHILE BRE

17、AK CONTINUE 说明： （1）BREAK 从最内层的 WHILE 循环中退出来，结束循环。 （2）CONTINUE 重新开始执行下一次循环，忽略CONTINUE 关键字后的任何语句。,33,8.1 SQL编程基础 8.1.3 SQL的控制流语句,例8.7 依据例8.5中的关系R。如图8.3所示，如果图书的平均价格少于30元，则执行循环，将价格加倍，然后选择最高价格。如果最高价50元，则继续执行循环，再次将价格加倍，直到最高价格超过50元退出循环，或平均价格30元结束循环。,34,8.1 SQL编程基础 8.1.3 SQL的控制流语句,开始,查询,均价 30,取最高价,价格加倍,结束,最

18、高价50,N,Y,N,Y,图8.3 例8.7的 程序流程图,35,8.1 SQL编程基础 8.1.3 SQL的控制流语句,USE图书馆 GO WHILE (SELECT AVG(定价) FROM R ) 50 BREAK ELSE CONTINUE END,36,8.1 SQL编程基础 8.1.3 SQL的控制流语句,4. RETURN语句 功能： 无条件退出所在的批处理、存储过程和触发器。可以返回状态信息（整数）。 语法格式： RETURN 整数 /* */ 说明：所有系统存储过程返回 0 值表示成功，返回非零值则表示失败。当用于存储过程时，RETURN 不能返回空值。如果过程试图返回空值（

19、例如，使用 RETURN status 且 status 是 NULL），将生成警告信息并返回 0 值。,37,8.1 SQL编程基础 8.1.4 EXECUTE 语句,功能：执行函数、存储过程。 语法格式：EXECUTE OUTPUT 说明： （1）参数需要在CREATE PROCEDURE 语句中定义。参数可以是变量或常量。 （2）OUTPUT指定存储过程必须返回一个参数。该参数必须用关键字OUTPUT创建。,38,8.1 SQL编程基础 8.1.5 注释,注释是为SQL语句加上注解，说明代码的含义，增强程序的可读性。注释是非执行语句，不做任何操作。 （1）/*/ 这种方法可以注释多行。

20、（2）-（两个减号）用于单行的注释。,39,8.1 SQL编程基础 8.1.6 SQL程序设计举例,例8.8 假设转帐业务规定，如果A账户的余额大于等于100元，则从A账户上支出100元，转到B账户上。否则不执行转帐操作。编写转帐业务的程序。,40,8.1 SQL编程基础 8.1.6 SQL程序设计举例,BEGIN TRANSACTION IF (SELECT余额 FROM 帐户 WHERE 帐号 =A) 100) BEGIN PRINT 金额不够，转帐失败！ ROLLBACK TRANSACTION END ELSE BEGIN UPDATE 帐户 SET 余额 =余额-100 WHERE

21、帐号 =A UPDATE 帐户 SET 余额 = 余额+100 WHERE 帐号 =B PRINT 转帐成功！ COMMIT TRANSACTION END,41,8.2 游标及游标的应用 8.2.1 游标的概念,绝大多数SQL语句是以集合的方式操作数据，操作的对象和结果都是一个元组的集合（关系）。例如，SELECT 语句返回满足WHERE 子句中条件的所有行。然而，在应用程序中常常需要对单个行或部分行进行操作，游标就是用于协调SQL的集合处理方式与单记录处理方式的机制。 游标（CUSOR）相当于一个临时表，如图8.4所示，在表中存放查询的结果，为了逐个地取出这个表中的元组，设置一个指针，指示

22、已取元组的位置；每取一个元组，指针向前推进一个位置。利用游标（CUSOR）机制，可以将集合操作转换成单记录的处理方式。,42,8.2 游标及游标的应用 8.2.1 游标的概念,游标 游标的指针 图8.4 游标是一个存储查询结果的临时表,43,8.2 游标及游标的应用 8.2.2 游标的用法,游标的使用方法如图8.5所示，包括如下5个步骤。 图8.5 与游标相关的语句,44,8.2 游标及游标的应用 8.2.2 游标的用法,例8.9 已知学生关系如图8.6所示。定义一个STU_Cursor游标，查询学号以4开头的学生姓名和性别，然后打开游标，提取数据并传递给两个局部变量，最后关闭游标和释放游标。

23、 学生,45,8.2 游标及游标的应用 8.2.2 游标的用法,DECLARE STU_Cursor CURSOR FOR /* 声明一个游标 */ SELECT 姓名, 性别 FROM 学生 WHERE 学号 LIKE 4% DECLARE Name CHAR(8), Sex CHAR(2) /*声明2个局部变量*/ OPEN STU_Cursor /* 打开游标 */ FETCH NEXT FROM STU_Cursor INTO Name, Sex /* 第一次推进游标*/ /* 测试全局变量FETCH_STATUS，判断游标是否能够向前推进。*/ WHILE FETCH_STATUS

24、= 0 BEGIN PRINT FETCH_STATUS PRINT Name+Sex FETCH NEXT FROM STU_Cursor INTO Name,Sex /* 向下推进游标指针 */ PRINT FETCH_STATUS END CLOSE STU_Cursor /* 关闭游标 */ DEALLOCATE STU_Cursor /* 释放游标 */,46,8.3 存储过程,存储过程（Stored Procedure）是存储在数据库服务器上的SQL程序。,47,8.3 存储过程 8.3.1 存储过程的概念,为了改善数据库系统的性能，当前许多数据库产品都提供了存储过程功能，允许用户

25、将常用的访问数据库的SQL程序，作为一个过程进行编译并存储在数据库中，供用户调用。,48,8.3 存储过程 8.3.1 存储过程的概念,使用存储过程主要有四个优点。 创建存储过程以后，可以多次调用而不必重写程序。修改存储过程不影响调用的程序，提高程序的可移植性。 存储过程不仅可用SQL语句，还可以使用一些控制程序流程的语句，例如 BEGIN/END、IF/THEN/ELSE、RETURN、WHILE/ END_WHILE 等语句，可说明局部变量和用SET语句赋值，并可以调用以宿主语言编写的函数和过程。 存储过程以编译后的形式存储在数据库中，在调用时不必再进行语法分析以及查询处理和优化，所以使用

26、存储过程能够提高系统的执行速度。 储过程能够减少网络流量。客户端调用存储过程时，只需要与数据库服务器端传递参数、结果和少量必要的消息，从而减少了网上的信息流量。,49,8.6存储过程 8.6.1存储过程的概述,存储过程使用时请注意： （1）不能将 CREATE PROCEDURE 语句与其它 SQL 语句组合到单个批处理中。 （2）创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 （3）存储过程是数据库对象，其名称必须遵守标识符规则。 （4）只能在当前数据库中创建存储过程。,50,8.6存储过程 8.6.2存储过程类型,通常来说，SQL Server 2005中有六种类

27、型的存储过程，它们分别是系统存储过程、本地存储过程、临时存储过程、远程存储过程、扩展存储过程、用户自定义存储过程。 我们需要重点关注用户自定义存储过程，当然系统本身提供的存储过程也为我们今后的数据处理带来了极大的方便之处，大家应该适时学习并使用，这样可以大大提供工作的效率。,51,8.6存储过程 8.6.2存储过程类型,系统存储过程：由系统提供的存储过程，可以执行各种操作，存储在教务管理数据库中，其前缀为sp_，分为管理存储过程、辅助存储过程、配置存储过程和监控存储过程。 本地存储过程：用户自己的数据库中创建的存储过程。主要用于应用程序来完成特定的任务。 临时存储过程：也属于本地存储过程。名称

28、前面有一个“#”或两个“#”分别表示局部和全局临时存储过程。 远程存储过程：用于远程调用的存储过程。 扩展存储过程：扩展存储过程其前缀为xp_允许用户使用动态连接库（DLL）访问SQLsever。 用户自定义存储过程：根据不同的业务要求，用户可以自己创建一些存储过程来完成特殊的任务，这些任务通常无法依靠系统本身提供的存储过程来实现。,52,8.3 存储过程 8.3.2 创建存储过程,存储过程是一个经过编译和存储在数据库中的SQL程序。在不同的DBMS系统中创建存储过程的方法有所区别，但基本方法和思路大同小异。本文以SQL Server2005用SQL语言创建存储过程的方法为例，介绍创建存储过程

29、的基本方法。,53,8.3 存储过程 8.3.2 创建存储过程,1. 创建存储过程的SQL语句 语法格式： CREATE PROCEDURE 数据类型 OUTPUT AS 功能：创建存储过程。,54,8.6存储过程 8.6.3存储过程的使用,1创建存储过程P203 创建存储过程的SQL语句语法格式： CREATE PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR R

30、EPLICATION AS sql_statement .n ,55,8.3 存储过程 8.3.2 创建存储过程,说明： 可以在CREATE PROCEDURE语句中声明多个参数。参数可以将数据传入存储过程，也可以将存储过程计算的结果返回调用的过程。如果希望参数能够返回数据，则可以用选项OUTPUT指定参数是输出型的。通过参数传递的方法，可以实现用户与存储过程之间的数据交互。当用户调用存储过程时，必须按参数声明的顺序和类型提供实参（常量或变量）。 只能在当前数据库中创建存储过程，因为执行CREATE PROCEDURE语句后，经过编译的存储过程就成为数据库的对象存储在数据库中。存储过程不同于一

31、般的应用程序，它是只能存储在数据库中的程序，即存储在数据库服务器端。 通常DBMS提供多种形式的存储过程。DBMS为了使用户更方便，针对常见的数据库操作设计了大量系统存储过程，供用户调用。用户也可以根据某些特殊业务操作，创建自己的存储过程存储在数据库中。存储过程不仅能够简化数据库操作，而且有利于安全性控制。,56,8.3 存储过程 8.3.2 创建存储过程,实际上，这里所涉及的许多概念并不新鲜，如变量说明、局部变量、全局变量、参数传递等都是高级程序设计语言中所必要的知识，存储过程类似于C语言的函数，系统存储过程类似于C语言的函数库，但也有一些不同之处。,57,8.3 存储过程 8.3.2 创建

32、存储过程,2. 删除存储过程 删除存储过程的SQL语句格式： DROP PROCEDURE ,58,8.3 存储过程 8.3.2 创建存储过程,例如，要删除存储过程PROA，可以直接使用删除语句，写成： DROP PROCEDURE PROA 在程序设计中更完善的删除方法是打开存储这个存储过程的数据库，然后用IF语句判断系统表（sysobjects）中是否存在这个存储过程，如果存在就删除，否则不执行删除操作。,59,8.3 存储过程 8.3.2 创建存储过程,在程序设计中常用的方法是写成： USE MIS /* 打开数据库*/ IF EXISTS (SELECT name FROM sysob

33、jects /* 测试系统表*/ WHERE name = XYZ AND type = P) DROP PROCEDURE PROA /*若存在就删除*/ GO,60,8.6存储过程 8.6.3存储过程的使用,2修改存储过程P205 用ALTER PROCEDURE更改先前通过执行 CREATE PROCEDURE 语句创建的过程，但不会更改权限，其基本语法如下： ALTER PROC EDURE procedure_name ; number parameter data_type VARYING = default OUTPUT ,.n WITH RECOMPILE | ENCRYPTI

34、ON | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n ,61,8.3 存储过程 8.3.2 创建存储过程,3. 创建存储过程的实例 例8.10 有三个关系模式如下： S（SNO，SNAME，SEX，AGE） 属性依次是学号、姓名、性别、年龄 C（CNO，CNAME） 属性依次是课程号、课程名 SC（SNO，CNO，GRADE） 属性依次是学号、课程号、分数 创建一个存储过程，检索某位学生的成绩单，包括学生姓名、课程名称、分数。存储过程名为SC_GRADE，参数是学号。,62,8.3 存储过程 8.3.2 创建存储过程,U

35、SE MIS IF EXISTS (SELECT name FROM sysobjects /* 判断存储过程是否存在*/ WHERE name = SC_GRADE AND type = P) DROP PROCEDURE SC_GRADE /* 如果已存在，则删除。*/ GO CREATE PROCEDURE SC_GRADE SNO CHAR(6) /*创建带参数的存储过程*/ AS SELECT SNAME, CNAME, GRADE FROM S，SC，C WHERE S.SNO=SC.SNO AND SC.CNO=C.CNO AND SNO= SNO GO,63,8.6存储过程 8

36、.6.3存储过程的使用,3执行存储过程P206 当需要执行存储过程时，用 EXEC(UTE) 语句,当存储过程是批处理中的第一条语句，可以不用 EXECUTE ,也可以执行该存储过程。 执行存储过程语法: EXEC UTE return_status = procedure_name ;number | procedure_name_var parameter = value | variable OUTPUT | DEFAULT ,.n WITH RECOMPILE ,64,8.6存储过程 8.6.3存储过程的使用,4存储过程的参数P206 用例子的方式来调用存储过程的传递参数。 【例8-1

37、2】使用 EXECUTE 传递单个参数，创建一个名称为showind的存储过程，其含有一个输入参数tabname，其执行方式有隐式传递 EXECUTE showind titles 在执行过程中变量可以显式传递参数值： EXECUTE showind tabname = titles 一般来说可以将EXECUTE 简写成前面四个字符EXEC,如果该语句处于批处理中第一个语句，则 EXEC 语句还可以进一步省略，直接写成：showind titles 或showind tabname = titles 就可以了。,65,8.3 存储过程 8.3.2 创建存储过程,可以利用EXECUTE语句，执行

38、存储过程SC_GRADE。 EXECUTE SC_GRADE 40092 或者 EXECUTE SC_GRADE SNO=40092,66,8.6存储过程 8.6.3存储过程的使用,例8-10：创建一个储存过程，使其带有输入参数。创建一个带有两个输入参数的存储过程，这两个参数分别对应学号和课程号，要求实现course表与score表的等值连接，连接条件是两个表的课程号相同，连接后显示学生的姓名，学号，课程名和成绩，并用学号和课程号检验。,67,8.6存储过程 8.6.3存储过程的使用,CREATE PROCEDURE stu_info STU_NUM varchar(40), CLASS_NU

39、M varchar(20) AS SELECT STU.姓名, SC.学号,C.课程名, SC.成绩 FROM COURSE AS C INNER JOIN SCORE SC ON C.课程号=SC.课程号INNER JOIN STUDENT AS STU ON SC.学号= STU.学号 WHERE SC.学号= STU_NUM AND SC.课程号= CLASS_NUM GO 执行 EXEC stu_info 08002,c4后的效果图P203,68,8.6存储过程 8.6.3存储过程的使用,例8-11：使用带有通配符参数的简单过程。在例8-10的功能基础上，要求如果不输入课程号也能够输出

40、查询结果。 USE 教务管理 IF EXISTS (SELECT name FROM sysobjects WHERE name = au_info AND type = P) DROP PROCEDURE au_info2 GO CREATE PROCEDURE stu_info STU_NUM varchar(40)=0_00_, CLASS_NUM varchar(20) =c% AS SELECT STU.姓名, SC.学号,C.课程名, SC.成绩 FROM COURSE AS C INNER JOIN SCORE SC ON C.课程号=SC.课程号INNER JOIN STUDE

41、NT AS STU ON SC.学号= STU.学号 WHERE SC.学号LIKE STU_NUM AND SC.课程号LIKE CLASS_NUM GO 检验存储过程 EXEC stu_info 08002,c4 再用另一种方式检验存储过程 EXEC stu_info 08001,69,8.6存储过程 8.6.3存储过程的使用,5存储过程的返回P207 存储过程通过下列方法之一向用户返回数据： 1）返回整数值形式的代码。 2）输出参数。 3）一个可以在存储过程外引用的游标。 4）从任何包含在存储过程中的select语句中返回的结果集。 存储过程可返回一个称为返回代码的整型值，以表明过程的执

42、行状态，如P208表8-1。如果执行成功返回0。 【例8-16】 DECLARE result int EXECUTE result = test_proc,70,8.6存储过程 8.6.3存储过程的使用,例8-12创建存储过程，实现按学号查询学生的平均成绩。下面的示例创建一个存储过程 (titles_sum)，并使用一个可选的输入参数和一个输出参数。 USE 教务管理 GO IF EXISTS(SELECT name FROM sysobjects WHERE name = titles_sum AND type = P) DROP PROCEDURE titles_sum GO USE 教

43、务管理 GO CREATE PROCEDURE titles_sum STU_NUM varchar(40) = %, SUM int OUTPUT AS SELECT 学生姓名=姓名FROM STUDENT WHERE 学号LIKE STU_NUM SELECT SUM = AVG(成绩) FROM SCORE WHERE 学号LIKE STU_NUM -执行语句 DECLARE SUM INT EXEC titles_sum 08002,SUM OUTPUT; select 平均分：,SUM,?,71,8.6存储过程 8.6.3存储过程的使用,6存储过程自动执行P208 如果有需要定期执行

44、的操作，或者有作为后台进程运行的存储过程，并希望该存储过程在所有时间都处于运行状态，此种方法非常有用。 语法：sp_procoption ProcName = procedure , OptionName = option , OptionValue = value 参数说明ProcName = procedure是要为其设置或查看选项的过程名； OptionName = option要设置的选项的名称；option 的唯一值是 startup，该值设置存储过程的自动执行状态； , OptionValue = value 表示选项是设置为开（true 或 on）还是关（false 或 off）

45、。 【例8-16】Sp_procoption 教务管理_testproc,startup,on。 注意：执行自动权限默认授予 sysadmin 固定服务器角色的成员。启动过程必须属于 教务管理 数据库的所有者。,72,8.4 数据库触发器 8.4.1 触发器的概念,触发器是一种特殊类型的存储过程,事实上它们都是一组SQL语句。当使用UPDATE、INSERT 或 DELETE的一种或多种数据修改操作在指定表中对数据进行修改时，触发器会生效。,73,8.4 数据库触发器 8.4.1 触发器的概念,1. 触发器的特点 触发器是数据库的一个对象，必须创建在一个特定的表/视图上，并存储在数据库中。 如

46、果对一个表/视图上的某种操作（如插入、更新或删除）定义了触发器，则该操作发生时，触发器将自动触发。 与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。 触发器和激活它的SQL语句构成一个事务，可以在触发器中包含ROLLBACK TRANSACTION语句，根据触发器运行的状态回滚事务，撤消所有操作。,74,8.4 数据库触发器 8.4.1 触发器的概念,2. 触发器的优点 利用触发器能够实现相关表的级联操作。 触发器具有比CHECK子句更强和更复杂的完整性约束定义功能。 利用触发器可以比较数据修改前后的状态，并可根据差异而采取不同的对策。 应用触发器能够简化复杂业务的实现方法，用简单

47、的方法定义复杂的业务规则和完整性约束条件。 由于触发器是一种特殊的存储过程，它具备存储过程的优点。,75,8.4 数据库触发器 8.4.1 触发器的概念,SQL Server2005 包括两大类触发器：DML 触发器和 DDL 触发器。 （1）DML 触发器在数据库中发生数据操作语言 (DML) 事件时将启用。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个

48、事务即自动回滚。 （2）DDL 触发器是 SQL Server 2005 的新增功能。当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用这些触发器。,76,8.4 数据库触发器 8.4.1 触发器的概念,当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。从而确保对数据的处理必须符合由这些SQL语句所定义的规则。 DML 触发器的主要优点如下： （1）DML 触发器可通过数据库中的相关表实现级联更改。例如，可以在 titles 表的 title_id 列上写入一个删除触发器，以使其他表中的各匹配行采取删除操作。该触发器用 title_id 列作为惟一键，在 titl

49、eauthor、sales 及 roysched 表中对各匹配行进行定位。 （2）DML 触发器可以防止恶意或错误的 INSERT、UPDATE 以及 DELETE 操作，并强制执行比 CHECK 约束定义的限制更为复杂的其他限制。与 CHECK 约束不同，DML 触发器可以引用其他表中的列。 （3）DML 触发器可以评估数据修改前后表的状态，并根据该差异采取措施。,77,8.4 数据库触发器 8.4.1 触发器的概念,DML 触发器又分为AFTER触发器、 INSTEAD OF触发器和CLR触发器。 AFTER触发器又称后触发器，它将在执行了相应的DML语句操作之后被触发。可以对变动的数据进

50、行检查，如果发现错误，将拒绝接受或回滚变动的数据。AFTER触发器只能在表上定义（即不能在视图上定义 AFTER 触发器）。在同一个表上，可以创建多个AFTER 触发器。 INSTEAD OF触发器又称前触发器，它在数据变动前被触发，并取代变动数据的操作（ INSERT、UPDATE 和 DELETE操作），而去执行触发器定义的操作。 INSTEAD OF触发器可以在表或视图上定义。在表或视图上，每个 INSERT、UPDATE 或 DELETE 语句最多可以定义一个 INSTEAD OF 触发器。,78,8.4 数据库触发器 8.4.1 触发器的概念,INSTEAD OF触发器工作原理 IN

51、STEAD OF触发器的含义是当某个事件发生时，事件本身的操作不执行，而是执行触发器中指定的SQL语句序列。即当在表或视图上创建了由INSERT、DELETE、UPDATE中某一个操作激活的INSTEAD OF触发器时，这个操作本身并不执行，取而代之的是执行触发器中的SQL语句。,79,8.4 数据库触发器 8.4.1 触发器的概念,CLR触发器 CLR触发器可以是AFTER触发器或INSTEAD OF触发器， CLR触发器还可以是DDL触发器。 CLR触发器将执行在托管代码（在.NET Framework中创建并在SQL Server中上载的程序集的成员）中编写的方法，而不用执行T-SQL语

52、句。,80,8.4 数据库触发器 8.4.1 触发器的概念,DML 触发器使用 deleted 和 inserted 逻辑表。它们在结构上和触发器所在的表的结构相同，SQL Server会自动创建和管理这些表。可以使用这两个临时的驻留内存的表测试某些数据修改的效果及设置触发器操作的条件。 Deleted表用于存储delete，update语句所影响的行的副本。在执行delete或update语句时，行从触发器表中删除，并传输到deleted表中。 Inserted表用于存储Insert或update语句所影响的行的副本，在一个插入或更新事务处理中，新建的行被同时添加到Inserted表和触发器

53、表中。Inserted表中的行是触发器表中新行的副本。,81,8.4 数据库触发器 8.4.2 创建DML触发器,使用SQL Server管理平台创建触发器的过程如下： 在SQL Server管理平台中，展开指定的服务器和数据库项，然后展开表，选择并展开要在其上创建触发器的表，如图1所示，右击触发器选项，从弹出的快捷菜单中选择“新建触发器”选项，则会出现触发器创建窗口，如图2所示。最后，单击“执行”按钮，即可成功创建触发器。,82,83,84,8.4 数据库触发器 8.4.2 创建DML触发器,使用CREATE TRIGGER命令创建DML触发器的语法形式如下： CREATE TRIGGER

54、trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n ,85,8.4 数据库

55、触发器 8.4.2 创建DML触发器,trigger_name是触发器的名称。 Table | view是在其上执行触发器的表或视图，有时称为触发器表或触发器视图。 WITH ENCRYPTION 加密 syscomments 表中包含 CREATE TRIGGER 语句文本的条目。 FOR | AFTER | INSTEAD OF指定触发器的类型，默认是 AFTER 触发器。 DELETE , INSERT , UPDATE 是指定在表或视图上执行哪些数据修改语句时将激活触发器的关键字。,86,8.4 数据库触发器 8.4.2 创建DML触发器,WITH APPEND 指定应该添加现有类型的

56、其它触发器。 NOT FOR REPLICATION表示当复制进程更改触发器所涉及的表时，不应执行该触发器。 AS 是触发器要执行的操作。 sql_statement 是触发器的条件和操作。 IF UPDATE (column) 测试在指定的列上进行的 INSERT 或 UPDATE 操作，不能用于 DELETE 操作。 column是要测试 INSERT 或 UPDATE 操作的列名。 IF (COLUMNS_UPDATED() 测试是否插入或更新了提及的列，仅用于 INSERT 或 UPDATE 触发器中。 bitwise_operator 是用于比较运算的位运算符。 updated_bi

57、tmask 是整型位掩码，表示实际更新或插入的列。 comparison_operator是比较运算符。 column_bitmask是要检查的列的整型位掩码，用来检查是否已更新或插入了这些列,87,8.4 数据库触发器 8.4.2 创建DML触发器,例8.4-1 ：示例说明inserted，deleted表的作用。执行结果如右图。 程序清单如下： create table sc (sno char(10), cno char(2), score real) Go CREATE TRIGGER tr1 ON sc FOR INSERT, UPDATE, DELETE AS PRINT inse

58、rted表： Select * from inserted PRINT deleted表： Select * from deleted Go,88,8.4 数据库触发器 8.4.2 创建DML触发器,例8.4-2创建一个触发器，在 s 表上创建一个插入、更新类型的触发器。 程序清单如下： CREATE TRIGGER tr_s ON s FOR INSERT, UPDATE AS Begin DECLARE bh varchar(6) SELECT bh =inserted.sno FROM inserted /*获取插入或更新操作时的新值（学号）*/ End,89,8.4 数据库触发器 8.

59、4.3 INSERT触发器的工作原理,1. INSERT触发器工作原理 INSERT触发器的工作原理如下图所示，触发器监视INSERT操作，一旦INSERT操作事件发生，将激活触发器，在被作用表中插入数据，并将插入的副本存入Inserted临时表。然后，执行触发器中定义的SQL语句。,90,8.4 数据库触发器 8.4.3 INSERT触发器的工作原理,图 INSERT触发器工作原理,监视INSERT语句,将数据插入创建了触发器的表中,生成临时表Inserted,执行触发器中定义的SQL语句,91,8.4 数据库触发器 8.4.3 INSERT触发器的工作原理,例8.4-3 有学生和贷款关系模式： 学生（学号，姓