SQL Server 2005 实用教程

1. SQL Server 2005 实用教程 电子工业出版社出版

2. 第10章 程序设计

3. 第10章 SQL Server 中的程序设计 • 10.1 程序中的批、脚本、注释 • 10.2 SQL Server变量 • 10.3 程序中的流程控制 • 10.4 SQL SEVER函数 • 10.5 程序中的事务 • 10.6游标的使用 • 10.7案例应用举例

4. 10.1 程序中的批、脚本、注释 1 .批处理 批处理就是一个或多个Transact-SQL语句的集合，从应用程序—次性发送到SQL Server并由SQL Server编译成—个可执行单元，此单元称为执行计划。建立批处理时，使用GO语句作为批处理的结束标记。

5. 10.1 程序中的批、脚本、注释 建立批处理时，应当注意以下几点： • CREATE DEFAULT，CREATE PROCEDURE，CREATE RULE，CREATE TRIGGER及CREATE VIEW语句不能与其他语句放在一个批处理中。 • 不能在删除一个对象之后，在同一批处理中再次引用这个对象。 • 不能在一个批处理中引用其他批处理中所定义的变量。 • 不能把规则和默认值绑定到表字段或用户自定义数据类型之后，立即在同—个批处理中使用它们。 • 不能定义一个CHECK约束之后，立即在同一个批处理中使用该约束。 • 不能在修改表中的一个字段名之后，立即在同一个批处理中引用新字段名。 • 如果一个批处理中的第一个语句是执行某个存储过程的EXECUTE语句，则EXECUTE关键字可以省略；如果该语句不是第一个语句，则必须使用EXECUTE关键字，或者省写为“EXEC”。

6. 10.1 程序中的批、脚本、注释 2 .脚本 脚本是存储在文件中的一系列SQL语句，即一系列按顺序提交的批处理。 SQL脚本中可以包含一个或多个批处理。使用脚本可以将创建和维护数据库时的操作步骤保存为一个磁盘文件。将Transact-SQL语句保存为脚本文件，不仅可以建立起可再用的模块化代码，还可以在不同计算机之间传送Transact-SQL语句，使两台计算机执行同样的操作。脚本可以在查询分析器中执行，也可以在isql或osql实用程序中执行。

7. 10.1 程序中的批、脚本、注释 3.注释 注释是程序代码中不执行的文本字符串（也称为注解）。在SQL Server中，可以使用两种类型的注释字符：一种用于单行注释的注释符“--”，另一种是块注释，注释符号为“/* */”。

8. 10.2 SQL Server变量 变量是SQL Server用来在语句之间传递数据的方式之一。SQL 中有两种形式的变量，一种是系统提供的全局变量，另外一种是用户自己定义的局部变量。

9. 1.全局变量 全局变量是SQL Server系统提供并赋值的变量。用户不能建立全局变量，也不能修改全局变量。在SQL Server 2000中，全局变量是—组特殊的函数，它们的名称是以@@开始，而且不需要任何参数，在调用时无需在函数名后面加上—对圆括号，这些函数也称为无参函数。

10. 2.局部变量 局部变量是指在批处理或脚本中用来保存数据值的对象。局部变量常用于作为计数器计算循环执行的次数或控制循环执行的次数，也可以用于保存由存储过程代码返回的数据值。此外，还可以使用Table数据类型的局部变量来代替临时表。

11. 2.局部变量 1）声明局部变量语法格式为： DECLARE @局部变量数据类型[,…n] 2）局部变量赋值语句，语法格式如下： ①SET @局部变量=表达式[,…n] ② SELECT @局部变量=表达式[,…n] 3）局部变量的作用域 局部变量的作用域从声明它们的地方开始到声明 它们的批处理或存储过程的结尾。

12. 10.3 程序中的流程控制 流程控制语句是用来控制程序执行和流程分支的命令，这些命令包括条件控制语句，无条件转移语句和循环语句。使用这些命令，可以使程序具有结构性和逻辑性，并可完成较复杂的操作。

13. 10.3 程序中的流程控制 • 10.3.1 BEGIN…END语句块 • 10.3.2 IF…ElSE语句 • 10.3.3 CASE表达式 • 10.3.4 WAITFOR语句 • 10.3.5 WHILE语句 • 10.3.6 PRINT语句 • 10.3.7RETURN语句

14. 10.3.1 BEGIN…END语句块 在条件和循环等流程控制语句中，要执行两个或两个以上的Transact-SQL语句时，就需要使用BEGIN…END语句，这些语句可以作为一个单元来执行。其语法形式为： BEGIN 语句I 语句2 … END

15. 10.3.1 BEGINN…END语句块 BEGIN…END语句通常用于下列情况： • WHILE循环需要包含语句块。 • CASE语句的元素需要包含语句块。 • IF或ELSE子句需要包含语句块。

16. 10.3.2 IF…ElSE语句 在程序中，经常需要根据特定条件指示SQL Server执行不同的操作和运算，进行流程控制。SQL Server利用IF…ELSE命令使程序有不同的条件分支，从而完成各种不同条件环境下的操作。其语法形式为： IF 布尔表达式 语句1 [ELSE 语句2]

17. 10.3.2 IF…ElSE语句 案例：使用IF…ELSE语句实现以下功能：如果存在职称为副教授或教授的教师，那么输出这些教师的姓名、学历、职称，否则输出“没有满足条件的教师”信息。代码如下： USE student GO IF EXISTS (SELECT * FROM 教师 WHERE 职称='副教授' OR 职称='教授') BEGIN PRINT '以下教师是具有高级职称的' SELECT 姓名,学历,职称 FROM 教师 WHERE 职称='副教授' OR 职称='教授' END ELSE BEGIN PRINT '没有满足条件的教师' END GO

18. 10.3.3 CASE表达式 CASE表达式是一个特殊的Transact-SQL表达式，它能够实现多重选择的情况。CASE表达式不同于—个普通的Transact-SQL语句，它不能单独执行，而只能作为—个可以单独执行的语句的一部分来使用。CASE表达式分为简单CASE表达式和搜索CASE表达式两种类型。

19. 10.3.3 CASE表达式 1．简单CASE表达式 简单CASE表达式将一个测试表达式与一组简单表达式进行比较，如果某个简单表达式与测试表达式的值相等，则返回相应结果表达式的值。简单CASE表达式的语法格式为： CASE测试表达式 WHEN测试值1 THEN结果表达式1 [WHEN测试值2THEN结果表达式2 [… ]] [ELSE 结果表达式n] END

20. 10.3.3 CASE表达式 案例：使用简单CASE表达式实现以下功能：分别输出课程号和课程名称，而且在课程名称后添加备注。代码如下： USE student GO SELECT 课程号,课程名称,备注= CASE 课程名称 WHEN 'SQL Server 2005 ' THEN '数据库应用技术' WHEN 'ASP.NET程序设计' THEN 'WEB程序设计' WHEN '计算机基础' THEN '计算机导论' WHEN '网络营销' THEN '电子商务' END FROM 课程 GO

21. 2．搜索CASE表达式 与简单CASE表达式相比较，在搜索CASE表达式中，CASE关键字后面不跟任何表达式，各个WHEN子句后都是布尔表达式。搜索CASE表达式的语法格式为： CASE WHEN布尔表达式1 THEN结果表达式1 WHEN布尔表达式2 THEN结果表达式2 […n ]] [ELSE 结果表达式n] END

22. 2．搜索CASE表达式 案例：使用搜索CASE表达式实现以下功能：分别输出班级代码、班级名称，并根据班级代码判别年级。代码如下： USE student GO SELECT 班级代码,班级名称,年级= CASE WHEN LEFT(班级名称,2)='04' THEN '三年级' WHEN LEFT(班级名称,2)='05' THEN '二年级' WHEN LEFT(班级名称,2)='06' THEN '一年级' END FROM 班级 GO

23. 10.3.4 WAITFOR语句 WAITFOR语句可以暂停执行程序—段时间之后再继续执行，也可以暂停执行程序到所指定的时间后再继续执行。WAITFOR语句的语法格式为： WAITFOR DELAY ‘时间’ |TIME ‘时间’

24. 10.3.4 WAITFOR语句 案例：设置在输出系部代码为“01”的班级信息后5秒钟输出系部代码为“02”的班级信息。代码如下： USE student GO SELECT * FROM 班级 WHERE 系部代码='01' GO WAITFOR DELAY '00:00:05' SELECT * FROM 班级 WHERE 系部代码='02' GO

25. 10.3.5 WHILE语句 在程序中当需要多次重复处理某项工作时，就需使用WHILE循环语句。WHILE语句的语法格式为： WHILE布尔表达式BEGIN 语句序列1 [BREAK] 语句序列2 [CONTINUE] 语句序列3 END

26. 10.3.5 WHILE语句 案例：使用WHILE语句实现以下功能：求2~300之间的所有素数。 DECLARE @I INT, @J INT SET @I=2 WHILE @I<=300 BEGIN SET @J=2 WHILE @J<=@I-1

27. 10.3.5 WHILE语句 BEGIN IF @I%@J=0 BREAK ELSE SET @J=@J+1 END IF @I=@J PRINT CONVERT(VARCHAR,@I)+'是素数！' SET @I=@I+1 END

28. 10.3.6 PRINT语句 SQL Server向客户程序返回信息的方法除了使用SELECT语句外，还可以使用PRINT语句，它的语法格式为： PRINT ‘字符串’|局部变量|全局变量 例11.6 PRINT语句举例。 USE STUDENT GO DECLARE @STR CHAR(20) SET @STR=”欢迎使用PRINT语句” PRINT @STR GO

29. 10.3.7 RETURN语句 RETURN的作用是无条件终止查询、存储过程或批处理等。可以在任意位置使用RETURN语句，以便从语句块中退出。使用RETURN语句时要注意，该语句后面存在的语句都不会被系统执行。当RETURN语句用于存储过程时，它不能返回空值。如果某个过程试图返回空值，则将生成警告消息并返回0值。

30. 10.4 SQL Server 函数 为了使用户对数据库进行查询和修改时更加方便，SQL server在T-SQL语言中提供了许多内部函数以供调用。用户也可以根据自己的需要创建函数。系统提供的函数称为内置函数；用户创建的函数称为用户自定义函数 。

31. 10.4 SQL Server 函数 • 10.4.1 常用系统函数 • 10.4.2 创建自定义函数 • 10.4.3 修改和删除自定义函数

32. 10.4.1 常用系统函数 系统函数为三类：标量函数、聚合函数和行集函数。 • 标量函数：对单一值操作，返回单一值。 • 聚合函数：对一组值进行计算后，向调用者返回单一的值。 • 行集函数：返回对象，该对象可在 SQL 语句中用作表引用。 对于每一个系统函数需要掌握两点： • 函数功能 • 调用形式<参数个数及其作用>

33. 1标量函数 • 数学函数 1、求绝对值函数 调用形式：abs(数值型表达式) 功能：返回表达式 的绝对值，其值的数据类型与参数一致。 2、求ASCLL码函数 调用形式：ascii(字符) 功能：返回字符的ASCLL码值，值的类型为整型。

34. 1标量函数 • 数学函数 3、ceiling(数值型表达式) 功能：返回最小的大于或等于给定数值型表达式的整数值，值的类型和给定的值相同。 4、floor(数值型表达式) 功能：返回最大的小于或等于给定数值型表达式的整数值。

35. 1标量函数 • 数学函数 5、power(表达式1，表达式2) 功能：进行乘方运算，求表达式的表达式2的次方，值的数据类型 和第一个参数的数据类型相同。 6、PI( ) 功能：求圆周率。 7、sqrt(表达式) 功能：求表达式的平方根。 8、sign(数值型表达式) 功能：判断数值的正负属性，+1表示正数；-1表示负数。

36. 1标量函数 • 数学函数 9、rand(整型表达式) 功能：返回一个位于0和1之间的随机数，在单个查询中反复调用 rand( )将产生相同的值。 10、round(数值表达式，整数) 功能：将数值四舍五入成整数指定的精度形式。整数为正表示 要进行的运算位置在小数点后，为负表示在小数点前。

37. 1标量函数 • 字符串函数 1、 len(字符串表达式) 功能：返回给定字符串表达式的字符个数，其中不包含尾随空格 ，注意不是字节个数，返回类行为int. 2、datalength(表达式) 功能：返回该表达式所占用的字节数，返回类型为int。对varchar ,text,image,nvarchar,ntext数据类型特别有用，因为这些数 据类型可以存储可变长数据。

38. 1标量函数 • 字符串函数 3、char(整形表达式) 功能：将给定的整形表达式的值按照ASCLL码转换为字符型。整 形表达式的值介于0~255之间，否则返回null。 4、substring(字符串，star，length) 功能：从字符串的star位置返回长度为length的字符串。 5、left(字符型表达式，整形表达式) 功能：返回从字符串左边开始指定个数的字符。 right(字符型表达式，整形表达式) 功能：返回从字符串右边开始指定个数的字符。

39. 1标量函数 • 字符串函数 6、upper(字符型表达式) 功能：将字符型表达式全部转化为大写形式。 lower(字符型表达式) 功能：将字符型表达式全部转化为大写形式。 7、ltrim(字符型表达式) 功能：删除左端空白。 rtrim(字符型表达式) 功能：删除右端空白。

40. 1标量函数 • 字符串函数 • 8、str(float型表达式，长度1，长度2） • 功能：将float型表达式转化为给定形式的字符串。长度1表示总 长度且包含小书店，长度2表示小数点后边的位数。 • 例：print str(123.45 , 6 , 1 ) • 转换为有六个位置的字符串，小数部分四舍五入。 • print str(123.45 , 2 , 2 ) • 当表达式超出指定长度时，返回* . *

41. 1标量函数 • 日期型函数 • 1、getdate( ) • 功能：返回当前的系统时间。 • 2、datepart(datepart , date) • 功能：以整数的形式返回给定的date型数据的指定日期部分。 • datename(datepart , date) • 功能：以字符串的形式返回给定的date型数据的指定日期部分。 • datepart的取值_____P305 • 3、day(date) 返回指定日期day部分的数值。 • month(date) 返回指定日期month部分的数值。 • year(date) 返回指定日期year部分的数值。

42. 2聚合函数 SQL Server提供了十几个聚合函数，下面我们将介绍几个最常用的聚合函数： lCOUNT(*)：用于计算所有行数 lMIN(数值表达式)：用于计算表达式的最小值 lMAX(数值表达式)：用于计算表达式的最大值 lSUM(数值表达式)：用于计算表达式的和 l AVG(数值表达式)：用于计算表达式的平均值

43. 2聚合函数 例： 使用聚合函数统计STUDENT数据库中学生的成 绩情况。其程序清单如下： USE STUDENT GO SELECT COUNT(*)AS 课程门数,MAX(成绩)AS 最高分 数,MIN(成绩)AS 最低分数, SUM(成绩)AS 总成绩 ,AVG(成绩) AS 平均成绩 FROM 课程注册 GROUP BY 学号 GO

44. 10.4.2用户自定义函数 在SQL Server中，用户不仅可以使用标准的内置函数，也可以使用自己定义的函数来实现一些特殊的功能。用户自定义函数可以在企业管理器中创建，也可以使用CREATE FUNCTION 语句创建。在创建时需要注意：函数名在数据库中必须唯一，其可以有参数，也可以没有参数，其参数只能是输入参数，最多可以有1024参数。 • 标量函数：返回单个数据值。 • 表值函数：返回值是一个记录集合——表。在此函数中，return语句包含一条单独的select语句。 • 多语句表值函数：返回值是由选择的结果构成的记录集。

45. 10.4.2用户自定义函数 1、使用CREATE FUNCTION语句创建用户自定义函数 使用CREATE FUNCTION创建用户自定义函数，其语法格式如下： CREATE FUNCTION [ owner_name.] function_name ( [ { @parameter_name [AS] scalar_parameter_data_type [ = default ] } [ ,...n ] ] ) RETURNS scalar_return_data_type [ AS ] BEGIN function_body RETURN scalar_expression END

46. 10.4.2用户自定义函数 • function_name：指用户自定义函数的名称。其名称必须符合标识符的命名规则，并且对其所有者来说，该名称在数据库中必须唯一。 • @parameter_name：用户自定义函数的参数，其可以是一个或多个。每个函数的参数仅用于该函数本身；相同的参数名称可以用在其它函数中。参数只能代替常量；而不能用于代替表名、列名或其它数据库对象的名称。函数执行时每个已声明参数的值必须由用户指定，除非该参数的默认值已经定义。如果函数的参数有默认值，在调用该函数时必须指定"default"关键字才能获得默认值。 • scalar_parameter_data_type：参数的数据类型。 • scalar_return_data_type：是用户定义函数的返回值。可以是 SQL Server 支持的任何标量数据类型（text、ntext、image 和 timestamp 除外）。 • function_body：位于begin和end之间的一系列 Transact-SQL 语句，其只用于标量函数和多语句表值函数。 • scalar_expression：用户自定义函数中返回值的表达式。

47. 1. 标量函数 例：在studenet库中创建一个用户自定义标量值函数xuefen，该函数通过输入成绩来判断是否取得学分，当成绩大于等于60时，返回取得学分，否则，返回未取得学分。代码如下： USE student GO CREATE FUNCTION xuefen(@inputxf int) RETURNS nvarchar(10) BEGIN declare @retrunstr nvarchar(10) if @inputxf >=60 set @retrunstr='取得学分' else set @retrunstr='未取得学分' return @retrunstr END GO

48. 1. 标量函数 使用刚才定义的xuefen函数来查看课程号为“0004”的课程，学生获得学分的情况。 在查询编辑器中输入如下代码： USE studenet GO SELECT 学号,成绩,dbo.xuefen(成绩) AS 学分情况 FROM 课程注册 WHERE 课程号='0004' GO

49. 2.表值函数 表值函数遵循的原则： • RETURNS子句仅包含关键字table。不必定义返回变量的格式，因为它由RETURN 子句中的 SELECT 语句的结果集的格式设置。 • function_body 不由BEGIN和END分隔。 • RETURN子句在括号中包含单个SELECT语句。SELECT语句的结果集构成函数所返回的表。内嵌表值函数中使用的SELECT语句受到与视图中使用的SELECT语句相同的限制。 例：在STUDENT库中创建一个内嵌表值函数XUESHENG，该函数可以根据输入的系部代码返回该系学生的基本信息。其代码如下： CREATE FUNCTION XUESHENG(@inputxbdm nvarchar(4)) RETURNS table AS RETURN ( SELECT 学号, 姓名, 入学时间 FROM 学生 WHERE 系部代码=@inputxbdm) GO 建立好该内嵌表值函数后，就可以象使用表或视图一样来使用它： SELECT * FROM DBO.XUESHENG('01') GO

50. 2.表值函数 在student库中创建一个多语句表值函数chengji，该函数可以根据输入的课程名称返回选修该课程的学生姓名和成绩。代码如下： USE student GO CREATE FUNCTION chengji( @inputkc as char(20) ) /*为chengji 函数定义的表结构，名称变量为@chji */ RETURNS @chji TABLE ( 课程名称 char(20), 姓名 char(8), 成绩 tinyint ) AS BEGIN INSERT @chji /*该变量是上面定义的表名称变量*/ SELECT c.课程名称,s.姓名 ,k.成绩 FROM 学生 as s INNER JOIN 课程注册 as k ON s.学号 =k.学号 INNER JOIN 课程 as c ON c.课程号=k.课程号 AND c.课程名称=@inputkc RETURN END GO