1,Transact-SQL程序设计,在Transact-SQL 语言中标准的SQL 语句畅通无阻。 Transact-SQL 也有类似于SQL 语言的分类不过做了许多扩充。 本章中，我们介绍了标准SQL语言的语法及其基本使用方法，在此只介绍Transact-SQL 语言中的其它部分。 1变量 2流程控制命令 3其它命令 4常用函数,2,1 . 变量,Transact-SQL 中使用两种变量：局部变量和全局变量。 1局部变量 局部变量是用户可自定义的变量，它的作用范围仅在程序内部。 在程序中通常用来储存从表中查询到的数据，或当作程序执行过程中暂存变量使用。 局部变量必须以开头，而且必须先用DECLARE 命令说明后才可使用。其说明形式为： DECLARE 变量名 变量类型，变量名变量类型,3,1 . 变量,在Transact-SQL 中不能像在一般的程序语言中一样使用“变量=变量值”来给变量赋值，必须使用SELECT 或SET 命令来设定变量的值。其语法如下： SELECT 局部变量= 变量值 SET 局部变量量= 变量值 【例】声明一个长度为 8 个字符的变量id，并赋值。 declare id char（8） select id =10010001,5,3注释符,在Transact-SQL 中可使用两类注释符： 1ANSI 标准的注释符“- -”用于单行注释。 2与C 语言相同的程序注释符号，即“/*/”，/* 用于注释文字的开头，*/用于注释文字的结尾，可在程序中标识多行文字为注释。,6,2 . 流程控制命令,Transact-SQL 语言使用的流程控制命令主要有以下几种控制命令。 2.1BEGINEND 其语法如下： BEGIN END BEGINEND 用来设定一个程序块，将在BEGINEND 内的所有程序视为一个单元执行。 BEGINEND 经常在条件语句（如IFELSE）中使用。 在BEGINEND 中可嵌套另外的BEGINEND 来定义另一程序块。,7,2.2IF ELSE 其语法如下： IF ELSE 条件表达式式 其中: 可以是各种表达式的组合，但表达式的值必须是逻辑值“真”或“假”。 ELSE 子句是可选的，最简单的IF 语句没有ELSE 子句部分。 IFELSE 用来判断当某一条件成立时执行某段程序，条件不成立时执行另一段程序。 如果不使用程序块，IF 或ELSE 只能执行一条命令。 IF ELSE 可以进行嵌套，在Transact-SQL 中最多可嵌套32 级。,8,【例】从SC数据表中求出学号为S1同学的平均成绩，如果此平均成绩大于或等于60分，则输出“pass”信息。 if (select avg(score) from sc where sno=S1 group by sno)=60 begin print pass end,9,2.3CASE CASE 命令有两种语句格式： 格式1： CASE WHEN THEN WHEN THEN ELSE END 该语句的执行过程是： 将CASE后面表达式的值与各WHEN子句中的表达式的值进行比较， 如果二者相等，则返回THEN后的表达式的值，然后跳出CASE语句，否则返回ELSE子句中的表达式的值。 ELSE子句是可选项。当CASE语句中不包含ELSE子句时，如果所有比较失败时，CASE语句将返回NULL。,10,【例】从学生表S中，选取SNO，SEX，如果SEX为“男”则输出“M”，如果为“女”输出“F”。 SELECT SNO, SEX= CASE sex WHEN 男 THEN M WHEN 女 THEN F END FROM S,11,格式2：CASE WHEN THEN WHEN THEN ELSE END 该语句的执行过程是： 首先测试WHEN后的表达式的值 如果其值为真，则返回THEN后面的表达式的值，否则测试下一个WHEN子句中的表达式的值 如果所有WHEN子句后的表达式的值都为假，则返回ELSE后的表达式的值 如果在CASE语句中没有ELSE子句，则CASE表达式返回NULL。 注：CASE 命令可以嵌套到SQL 命令中。,12,【例】从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。 SELECT SNO,CNO, SCORE= CASE WHEN SCORE IS NULL THEN 未考 WHEN SCORE=60 AND SCORE=70 AND SCORE=90 THEN 优秀 END FROM SC,13,2.4WHILECONTINUEBREAK 其语法如下： WHILE BEGIN BREAK CONTINUE 命令行或程序块 END WHILE 命令在设定的条件成立时，会重复执行命令行或程序块。 CONTINUE 命令可以让程序跳过CONTINUE 命令之后的语句，回到WHILE 循环的第一行，继续进行下一次循环。 BREAK 命令则让程序完全跳出循环，结束WHILE 命令的执行。 WHILE 语句也可以嵌套。,14,如：以下程序计算1100之间所有能被3整除的数的个数及总和。 DECLARE S SMALLINT,I SMALLINT,NUMS SMALLINT SET S=0 SET I=1 SET NUMS=0 WHILE (I=100) BEGIN IF (I%3=0) BEGIN SET S=S+I SET NUMS=NUMS+1 END SET I=I+1 END PRINT S PRINT NUMS,15,2.5WAITFOR 其语法如下： WAITFOR DELAY | TIME | ERROREXIT | PROCESSEXIT | MIRROREXIT WAITFOR 命令用来暂时停止程序执行，直到所设定的等待时间已过或所设定的时间已到才继续往下执行。 其中时间必须为DATETIME 类型的数据，但不能包括日期。 各关键字含义如下： 1DELAY：用来设定等待的时间，最多可达24 小时 2TIME：用来设定等待结束的时间点 3ERROREXIT：直到处理非正常中断 4PROCESSEXIT：直到处理正常或非正常中断 5MIRROREXI： 直到镜像设备失败,16,【例】等待1 小时2 分零3 秒后才执行SELECT 语句。 waitfor delay 01:02:03 Select * from employee,17,2.6GOTO 语法如下： GOTO 标识符 GOTO 命令用来改变程序执行的流程，使程序跳到标有标识符的指定的程序行再继续往下执行。 作为跳转目标的标识符可为数字与字符的组合。但必须以“：”结尾。 在GOTO 命令行，标识符后不必跟“：”,18,如：求12310的总和。 DECLARE S SMALLINT,I SMALLINT SET I=1 SET S=0 BEG: IF (I=10) BEGIN SET S=S+I SET I=I+1 GOTO BEG END PRINT S,19,2.7RETURN 语法如下： RETURN （整数值） RETURN 命令用于结束当前程序的执行，返回到上一个调用它的程序或其它程序。 在括号内可指定一个返回值。 如果没有指定返回值，SQL Server 系统会根据程序执行的结果返回一个内定值，如：,0 程序执行成功 -1 找不到对象 -2 数据类型错误 -3 死锁 -4 违反权限原则 -5 语法错误 -6 用户造成的一般错误 -7 资源错误如磁盘空间不足 -8 非致命的内部错误 -9 已达到系统的极限 -10 -11 致命的内部不一致性错误 -12 表或指针破坏 -13 数据库破坏 -14 硬件错误,如果运行过程产生了多个错误，SQL Server 系统将返回绝对值最大的数值； 如果此时用户定义了返回值，则以返回用户定义的值。RETURN 语句不能返回NULL值。,20,28 SELECT SELECT 命令可用于给变量赋值其语法如下： SELECT local_variable = expression ，.n SELECT 命令可以一次给多个变量赋值。 当表达式expression 为列名时，SELECT 命令可利用其查询功能一次返回多个值，变量中保存的是其返回的最后一个值。 如果SELECT命令没有返回值，则变量值仍为其原来的值。 当表达式expression 是一个子查询时，如果子查询没有返回值，则变量被设为NULL。,21,12SET SET 命令有两种用法： （1）用于给局部变量赋值 在用DECLARE 命令声明之后，所有的变量都被赋予初值NULL。 需要用SET 命令来给变量赋值，但与SELECT 命令不同的是SET 命令一次只能给一个变量赋值。 不过由于SET 命令功能更强且更严密，因此，SQL Server 推荐使用SET 命令来给变量赋值。,22,（2）用于设定用户执行SQL 命令时，SQL Server 的处理选项设定。 有以下几种设定方式： SET：选项ON SET：选项OFF SET：选项值,23,15USE 语法如下： USE databasename USE 命令用于改变当前使用数据库为指定的数据库。 用户必须是目标数据库的用户成员或目标数据库建有GUEST 用户账号时，使用USE 命令才能成功切换到目标数据库。,24,3.存储过程和触发器,在大型数据库系统中，存储过程和触发器具有很重要的作用。 无论是存储过程还是触发器，都是SQL 语句和流程控制语句的集合。 就本质而言，触发器也是一种存储过程。 存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。 SQL Server 2005 不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。,25,3.1 存储过程概述,存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。 用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 在SQL Server 的系列版本中存储过程分为两类： 系统提供的存储过程 用户自定义存储过程,26,系统过程主要存储在master 数据库中并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQL Server 提供支持。 通过系统存储过程，SQL Server 中的许多管理性或信息性的活动（如了解数据库对象、数据库信息）都可以被顺利有效地完成。 尽管这些系统存储过程被放在master 数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名。 而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。 用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。,27,3.2 创建存储过程,在SQL Server 2000 中创建一个存储过程有两种方法： 一种是使用Transaction-SQL命令Create Procedure， 另一种是使用图形化管理工具Enterprise Manager。 用T-SQL 创建存储过程是一种较为快速的方法， 对初学者使用Enterprise Manager 更易理解，更为简单。 当创建存储过程时，需确定存储过程的三个组成部分： 1所有的输入参数以及传给调用者的输出参数 2被执行的针对数据库的操作语句包括调用其它存储过程的语句 3返回给调用者的状态值以指明调用是成功还是失败,28,2.1 用CREATE PROCEDURE 命令创建存储过程 在创建存储过程之前应该考虑到以下几个方面： 1在一个批处理中Create Procedure 语句不能与其它SQL 语句合并在一