第8章 T-SQL编程基础与存储过程

1、第第1010章章 存储过程存储过程nSQLSQL语言是关系数据库的标准语言。语言是关系数据库的标准语言。nSQL Server2008SQL Server2008数据库系统的编程语言是数据库系统的编程语言是Transact-SQLTransact-SQL语言。语言。10.1 Transact-SQL基础知识 Transact-SQL Transact-SQL是是SQLSQL数据库查询语言的一个强大实现，是数据库查询语言的一个强大实现，是一种一种数据定义数据定义、数据操作数据操作和和控制语言控制语言，是，是SQL ServerSQL Server中的重中的重要组成元素。要组成元素。 SQLSQL

2、可以分为以下三类。可以分为以下三类。(1) (1) 数据定义语句数据定义语句 实现对基本表、视图以及索引的定义、修改和删除等。实现对基本表、视图以及索引的定义、修改和删除等。(2) (2) 数据操作语句数据操作语句 数据查询、统计、分组、排序、检索、插入、删除、修改等。数据查询、统计、分组、排序、检索、插入、删除、修改等。(3) (3) 数据控制语句数据控制语句 对数据库用户的授权和收权命，以保证数据库的安全性。对数据库用户的授权和收权命，以保证数据库的安全性。 10.1 Transact-SQL基础知识 nSQLSQL语言是关系数据库系统的标准语言，标语言是关系数据库系统的标准语言，标准的准

3、的SQLSQL语句几乎可以语句几乎可以在所有的关系数据库系统中在所有的关系数据库系统中使用使用，如，如OracleOracle、SQL ServerSQL Server、SybaseSybase等数等数据库系统。据库系统。nTransact-SQLTransact-SQL（简称（简称T-SQLT-SQL）语言正是微软）语言正是微软在其在其SQL ServerSQL Server系列关系数据库系统中的实系列关系数据库系统中的实现，主要包括现，主要包括常量和变量、运算符、表达式、常量和变量、运算符、表达式、流程控制语句、注释流程控制语句、注释等。等。10.1.2 T-SQL10.1.2 T-SQL

4、语法格式语法格式在在T-SQLT-SQL语句中常会用到一些符号，语句中常会用到一些符号，T-SQLT-SQL语句的语法格式约定如下：语句的语法格式约定如下：(1) (1) 大写字母大写字母代表代表Transact-SQLTransact-SQL中保留的关键字，如中保留的关键字，如CREATECREATE、SELECTSELECT、UPDATEUPDATE、DELETEDELETE等。等。(2) (2) 小写字母小写字母代表表达式、标识符等。代表表达式、标识符等。(3) (3) 竖线竖线“| |”表示参数之间是表示参数之间是“或或”的关系，用户可以从其中选择使用。的关系，用户可以从其中选择使用。

5、(4) (4) 尖括号尖括号“”表示其中的内容为实际语义。表示其中的内容为实际语义。(5) (5) 大括号大括号“”大括号中的内容为必选项，其中可以包含多个选项，各个选项之间用大括号中的内容为必选项，其中可以包含多个选项，各个选项之间用竖线分隔，用户必须从选项中选择其中一项，大括号不必键入。竖线分隔，用户必须从选项中选择其中一项，大括号不必键入。(6) (6) 方括号方括号“ ”方括号内所列出的项为可选项，用户可以根据需要选择使用。方括号内所列出的项为可选项，用户可以根据需要选择使用。(7) (7) 省略号省略号“,.n ”,.n ”表示前面的项可重复表示前面的项可重复n n次，每项由逗号分隔

6、。次，每项由逗号分隔。(8) (8) 省略号省略号“.n”.n” 表示前面的项可以重复表示前面的项可以重复n n次，每项由空格分隔。次，每项由空格分隔。10.2 批处理 n编写批处理时，编写批处理时，GOGO语句是批处理命令的结束标志，从程序开头或从某一语句是批处理命令的结束标志，从程序开头或从某一个个GOGO语句开始到下一个语句开始到下一个GOGO语句或程序结束为一个批处理。语句或程序结束为一个批处理。n当编译器读取到当编译器读取到GOGO语句时，会把语句时，会把GOGO语句前的所有语句作为一个批处理，语句前的所有语句作为一个批处理，并将这些语句并将这些语句打包发送打包发送给数据库服务器。给

7、数据库服务器。 使用批处理的规则：使用批处理的规则：1 1）CREATE DEFAULTCREATE DEFAULT、CREATE FUNCTIONCREATE FUNCTION、CREATE PROCEDURECREATE PROCEDURE、CREATECREATE RULE RULE、CREATE TRIGGERCREATE TRIGGER、GREATE VIEWGREATE VIEW语句不能在批处理中与其他语句语句不能在批处理中与其他语句 组合使用。组合使用。2) 2) 如果如果EXECUTEEXECUTE语句是批处理中的第一句，则语句是批处理中的第一句，则EXECUTEEXECUTE

8、关键字可以省略。关键字可以省略。如果如果EXECUTEEXECUTE语句不是批处理中的第一条语句，则需要语句不是批处理中的第一条语句，则需要EXECUTEEXECUTE关键字。关键字。3) 3) 不能在删除一个对象之后，由在同一批处理中再次引用这个对象。不能在删除一个对象之后，由在同一批处理中再次引用这个对象。4) 4) 不能在定义一个不能在定义一个CHECKCHECK约束之后，立即在同一个批处理中使用。约束之后，立即在同一个批处理中使用。5) 5) 不能把规则和默认值绑定到表字段上之后，立即在同一批处理中使用。不能把规则和默认值绑定到表字段上之后，立即在同一批处理中使用。6) 6) 不能在修

9、改表中一个字段之后，立即在同一个批处理中引用这个字段。不能在修改表中一个字段之后，立即在同一个批处理中引用这个字段。7) 7) 局部变量的作用域限制在一个批处理中，不能在局部变量的作用域限制在一个批处理中，不能在GOGO语句之后再次引用语句之后再次引用该变量。该变量。10.3 常量和变量 n常量是在程序运行过程中保持不变的量；常量是在程序运行过程中保持不变的量；n变量是在程序运行过程中，值可以发生变化的量，通常用变量是在程序运行过程中，值可以发生变化的量，通常用来保存程序运行过程中的录入数据、中间结果和最终结果。来保存程序运行过程中的录入数据、中间结果和最终结果。n两种类型的变量：两种类型的变

10、量：全局变量全局变量局部变量局部变量10.3.1 10.3.1 常量常量 常量，是表示一个特定值的符号，常量的类型取决于它所表示的值常量，是表示一个特定值的符号，常量的类型取决于它所表示的值的数据类型，可以是日期型、数值型、字符串型等。对于的数据类型，可以是日期型、数值型、字符串型等。对于日期型和字日期型和字符串型常量，使用的时候要用单引号括起来符串型常量，使用的时候要用单引号括起来。需要注意的是，需要注意的是，UnicodeUnicode字符串常量与字符串常量与ASCIIASCII字符串常量相似，但它前面字符串常量相似，但它前面有一个有一个N N标识符（标识符（N N代表代表SQL-92SQ

11、L-92标准中的国际语言（标准中的国际语言（national national languagelanguage）。）。N N前缀必须大写，前缀必须大写，UnicodeUnicode数据中的每个字符用两个字节数据中的每个字符用两个字节存储，而每个存储，而每个ASCIIASCII字符用一个字节存储。字符用一个字节存储。 10.3.2 10.3.2 全局变量全局变量 全局变量是由全局变量是由SQL Server2008SQL Server2008系统定义并使用系统定义并使用的变量，的变量，用户不能定义全局变量，只能使用全局用户不能定义全局变量，只能使用全局变量变量。全局变量通常存储一些。全局变量通

12、常存储一些SQL SQL Server2008Server2008的配置设置值和性能统计数据，用户可的配置设置值和性能统计数据，用户可在程序中用全局变量来测试系统的设定值或在程序中用全局变量来测试系统的设定值或Transact-SQLTransact-SQL命令执行后的状态值。引用全局变命令执行后的状态值。引用全局变量时，量时，全局变量的名字前面要使用两个标记符全局变量的名字前面要使用两个标记符。【例例8-18-1】 使用全局变量使用全局变量VERSIONVERSION查看当前查看当前数据库的版本。数据库的版本。 PRINT VERSIONPRINT VERSION10.3.3 10.3.3

13、局部变量局部变量 局部变量是用户自定义的变量，作用范围仅在程序内部，一般用于局部变量是用户自定义的变量，作用范围仅在程序内部，一般用于临时存储各种类型的数据，以便在临时存储各种类型的数据，以便在SQLSQL语句之间传递。语句之间传递。n在在Transact-SQLTransact-SQL语法中使用的局部变量必须以语法中使用的局部变量必须以 开头，如开头，如xx。n局部变量一定是定义后才能使用，语法如下。局部变量一定是定义后才能使用，语法如下。 DECLARE DECLARE 变量名变量名 数据类型数据类型 ( ( 长度长度 ) ,.n ) ,.n 2) 2) 局部变量在定义之后的局部变量在定义

14、之后的初始值是初始值是NULLNULL，给变量赋值使用，给变量赋值使用SETSET命令或命令或SELECTSELECT命令，语法如下。命令，语法如下。 SET SET 局部变量名局部变量名= =表达式表达式 SELECT SELECT 局部变量名局部变量名= =表达式表达式 , ,n n 3) 3) 使用使用PRINTPRINT、SELECTSELECT输出局部变量的值，语法格式是：输出局部变量的值，语法格式是： PRINT PRINT 表达式表达式 SELECT SELECT 表达式表达式1,1,表达式表达式2,2,PRINTPRINT，其值在查询后的，其值在查询后的“消息消息”窗口中窗口中

15、SELECT,SELECT,其结果在查询后的其结果在查询后的“网格网格”子窗口中显示；子窗口中显示； 4) 4) 局部变量的作用域是在一个批处理、一个存储过局部变量的作用域是在一个批处理、一个存储过程或一个触发器内，程或一个触发器内，其生命周期从定义开始其生命周期从定义开始到到它遇到的第一个它遇到的第一个GOGO语句或者语句或者到存储过程、触发器到存储过程、触发器的结尾结束，即的结尾结束，即局部变量只在当前的批处理、存储过局部变量只在当前的批处理、存储过程、触发器中有效。程、触发器中有效。 【例例10-210-2】声明两个变量声明两个变量x x和和d d，分别给其赋值后输出查看结果，分别给其赋

16、值后输出查看结果代码清单如下：代码清单如下：DECLARE x int,d datetimeDECLARE x int,d datetimeSET x=5SET x=5SELECT d=getdate()SELECT d=getdate()SELECT x,d SELECT x,d 10.6.1 BEGIN10.6.1 BEGINENDEND语句语句将多条将多条Transact-SQLTransact-SQL语句封装成一个语句块，作为一个整体处理。语句封装成一个语句块，作为一个整体处理。BEGINBEGIN SQL ENDEND注：注：BEGINBEGINENDEND语句可以嵌套使用。语句可以

17、嵌套使用。10.6.2 IF10.6.2 IFELSEELSE语句语句IF ELSE 注：除非使用语句块，否则注：除非使用语句块，否则IF或或ELSE条件只能影响一个条件只能影响一个SQL语句。语句。【例例10-310-3】根据现在的日期，显示月份上旬或下旬。根据现在的日期，显示月份上旬或下旬。 10.6.3 CASE10.6.3 CASEENDEND语句语句 CASECASEENDEND语句根据不同的条件返回不同的值，实现多分支判断。语句根据不同的条件返回不同的值，实现多分支判断。简单简单CASECASEENDEND：CASE CASE WHEN WHEN THEN THEN1 WHEN W

18、HEN THEN THEN 2 n n ELSE ELSE n END END 【例例10-410-4】按照正常习惯显示读者的性别。按照正常习惯显示读者的性别。搜索搜索CASECASEENDEND：CASECASE WHEN WHEN THEN THEN1 WHEN WHEN THEN THEN 2 n n ELSE ELSE n ENDEND 【例例10-510-5】按照月份划分所处季节。按照月份划分所处季节。10.6.4 WHILE10.6.4 WHILE语句语句语法格式如下：语法格式如下：WHILE WHILE BEGINBEGIN BREAK BREAK CONTINUE CONTIN

19、UE END END 【例例10-610-6】计算计算1+2+3+1+2+3+100+100的结果。的结果。10.6.5 WAITFOR10.6.5 WAITFOR语句语句语法如下：语法如下： WAITFOR DELAY WAITFOR DELAY | TIME | TIME 【例例8-78-7】比较下面两种表示暂停执行方式。比较下面两种表示暂停执行方式。WAITFOR DELAY 00:12:15WAITFOR DELAY 00:12:15表示距离当前时间延迟表示距离当前时间延迟1212分分1515秒后程序继续执行。秒后程序继续执行。WAITFOR TIME 00:12:15WAITFOR

20、TIME 00:12:15表示零点表示零点1212分分1515秒的时刻程序继续执行。秒的时刻程序继续执行。 10.6.6 10.6.6 注释注释SQL ServerSQL Server支持两种方式的注释：支持两种方式的注释：n“-”：行注释：行注释，以，以两个减号开始直到本行结束的全部内容是注释部分两个减号开始直到本行结束的全部内容是注释部分 。可以单独一行，也可以跟在可以单独一行，也可以跟在SQLSQL语句之后，允许嵌套使用。语句之后，允许嵌套使用。n“/ /* * */ /”：块注释，以：块注释，以“/ /* *”作为注释文字的开头，作为注释文字的开头，“* */ /”作为注释作为注释【例

21、例10-8】注释的使用。注释的使用。存储过程作用：存储过程作用：n高效、安全的访问数据库高效、安全的访问数据库n提高检索数据的速度提高检索数据的速度本章主要内容：本章主要内容：n创建存储过程创建存储过程n管理存储过程。管理存储过程。 10.1 存储过程概述存储过程概述10.1.1 存储过程的概念存储过程的概念存储过程是在存储过程是在数据库服务器数据库服务器执行的一组执行的一组T-SQL语句的集合语句的集合，经，经编译后存放在编译后存放在数据库服务器端数据库服务器端。10.1.1 存储过程的概念存储过程的概念SQL Server提供了三种类型的存储过程。提供了三种类型的存储过程。1) 用户存储过

22、程用户存储过程2) 系统存储过程：系统存储过程存储在系统存储过程：系统存储过程存储在源源数据库数据库中，并且带有中，并且带有 sp_ 前缀。前缀。3) 扩展存储过程：扩展存储过程是指扩展存储过程：扩展存储过程是指 Microsoft SQL Server 的实例可以动态加载的实例可以动态加载和运行的和运行的 DLL。 10.1.2 存储过程的优点存储过程的优点存储过程是一种独立的存储过程是一种独立的数据库对象数据库对象，它在，它在服务器服务器上上创建和运行。创建和运行。优点：优点：1. 模块化程序设计模块化程序设计2. 提高执行效率，改善系统系能提高执行效率，改善系统系能3. 减少网络通信流量

23、减少网络通信流量4. 强制应用程序的安全性强制应用程序的安全性10.2 创建存储过程创建存储过程10.2.1 创建存储过程创建存储过程1. 使用使用SQL Server Management Studio创创建于执行建于执行存储过程存储过程10.2.1 创建存储过程创建存储过程2. 使用使用CREATE PROCEDURE语句创建存储语句创建存储过程过程语法如下：语法如下：CREATE PROCEDURE 存储过程名存储过程名 ；分组号；分组号 形参变量形参变量1 数据类型数据类型 VARYING = 默认值默认值 OUTPUT 形参变量形参变量n 数据类型数据类型 VARYING = 默认值

24、默认值 OUTPUT WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATIONASSQL 语句系列语句系列 10.2.2 执行存储过程执行存储过程2. 使用使用EXECUTE语句执行存储过程语句执行存储过程 EXEC UTE 状态值状态值 = 存储过程名存储过程名 ；分组号；分组号 | 存储过程变量存储过程变量 参数参数1 = 参量值参量值 | 变量变量 OUTPUT |DEFAULT 参数参数n = 参量值参量值 | 变量变量 OUTPUT | DEFAULT WITH RECOMPILE 10.2.1 创建存储

25、过程创建存储过程【例例10-2】创建存储过程创建存储过程ProSeBook，查询所有图书的信息。，查询所有图书的信息。 10.2.1 创建存储过程创建存储过程【例例10-3】创建带有输入参数的存储过程创建带有输入参数的存储过程ProIdBook，查询指定读者编号的，查询指定读者编号的读者的借书记录。输入参数用于接收读者编号值，设有默认值读者的借书记录。输入参数用于接收读者编号值，设有默认值“G001”。10.2.1 创建存储过程创建存储过程【例例10-4】创建一个带有输入参数和输出参数的存储过程创建一个带有输入参数和输出参数的存储过程ProcBookCate，返回指定读者所借图书的图书类别。返

26、回指定读者所借图书的图书类别。实例实例n表book的内容如下：n编号编号 书名书名 价格价格001 C语言入门语言入门 $30002 PowerBuilder开发开发 $52 10.3 管理存储过程管理存储过程10.3.1 查看存储过程查看存储过程1. 使用使用SQL Server Management Studio查看存储过程查看存储过程【例例10-9】使用使用SQL Server Management Studio来查看来查看【例例10-1】创建的存储过程创建的存储过程ProAvgPrice。10.3.1 查看存储过程查看存储过程2. 使用命令方式查看存储过程使用命令方式查看存储过程 (1

27、) sp_help用于显示存储过程的信息，如存储过程的参数、用于显示存储过程的信息，如存储过程的参数、创建日期等。创建日期等。EXECUTE sp_help 存储过程名存储过程名(2) sp_helptext用于显示存储过程的源代码。用于显示存储过程的源代码。EXECUTE sp_helptext 存储过程名存储过程名(3) sp_depends 用于显示和存储相关的数据库对象。用于显示和存储相关的数据库对象。EXECUTE sp_ depends 存储过程名存储过程名 10.3.1 查看存储过程查看存储过程【例例10-10】使用相关系统存储过程查询使用相关系统存储过程查询【例例10-4】创建的存储过程创建的存储过程ProcBookCate的所有者、创建时间和各个参数的信息。的所有者、创建时间和各个参数的信息。10.3.1 查看存储过程查看存储过程【例例10-11】使用相关系统存储过程查询使用相关系统存储过程查询【例例10-4】创建的存储过程创建的存储过程ProcBookCate