Transact-SQL介绍.ppt

第 12 章 Transact-SQL介绍,本章学习目标,l了解Transact-SQL语言起源、组成l掌握Transact-SQL编程基础知识l综合运用变量、表达式、函数及流控语句等编写应用程序代码,12.1 Transact-SQL语言概论12.1.1 Transact-SQL语言简介SQL语言能够针对数据库完成定义、查询、操纵和控制功能，是关系型数据库领域中的标准化查询语言。微软公司在SQL语言的基础上对其进行了大幅度的扩充，并将其应用于SQL Server服务器技术中，从而将SQL Server所采用的SQL语言称为Transact-SQL语言。目前SQL语言的最新标准为SQL-92，由美国国家标准局制定，包含了语法标准以及对SQL关键字的定义。,SQL语言由三部分组成：数据定义语言（Data Definition Language，简称DDL）、数据操纵语言（Data Manipularion Language，简称DML）、数据控制语言（Data Control Language，简称DCL）。对上述三种语言的描述如表12-1所示。表12-1 SQL语言的组成,2 批处理 批处理是多条Transact-SQL语句构成的集合。SQL Server将批处理语句进行编译形成一个可执行单元，称为执行计划。执行计划中的语句一次执行每一条语句，如果在编译过程中出现语法错误，那么批处理中所有的语句均无法正常执行。如果在运行阶段出现错误时，一般都会中断当前以及其后语句的执行，只有在少数情况下，如违反约束时，仅中断当前出错的语句而继续执行其他语句。如果在事务中含有批处理语句，那么在运行阶段一旦出现错误时，都将会对已经执行的操作结果进行回滚。假定在事务中包含10条语句，那么如果第五条语句出现错误，则不会执行后续的语句，并且取消之前完成的操作。,在建立批处理时，应该遵循以下规则：l不能在批处理中引用其他批处理中所定义的变量。lCREATE DEFAULT、CREATE PROCEDURE、CREATE RULE、CREATE TRIGGER 和 CREATE VIEW 语句不能在批处理中与其它语句组合使用。l不允许在一个批处理中更改表结构、并引用新的字段。l如果 EXECUTE 语句是批处理中的第一句，则不需要 EXECUTE 关键字。如果 EXECUTE 语句不是批处理中的第一条语句，则需要 EXECUTE 关键字。l 一个完整的批处理需要使用GO语句作为结束标记。,批处理命令以批处理的方式处理单个或多个Transact-SQL语句。有两种基本方法将批处理传送给SQL Server。（1）go 向SQL Server 发送当前Transact-SQL语句的批处理的一种信号。（2）EXEC用于执行用户定义函数、系统过程、用户定义存储过程或扩展的存储过程。EXEC可以传递参数，并且可以赋值给返回状态变量,【例1】执行批处理程序，依次查询系部表、系部总数、班级表、班级总数。USE XSCJGOSELECT*FROM 系部表SELECT COUNT(*)FROM 系部表SELECT*FROM 班级表SELECT COUNT(*)FROM 班级表GO运行结果如图12-18所示。,图12-18 执行批处理程序结果,Transact-SQL语法要素,注释语句（1）单行注释-（2）多行注释/*/变量 局部变量 全局变量，只读变量的声明 DECLARE local_variable data_typen,例如，定义一个整数类型的变量score以及字符串类型的变量name，可以表示为：DECLARE score INTDECLARE name CHAR(12),SET语句一次只能给一个局部变量赋值，SELECT语句则可以同时给一个或多个变量赋值。局部变量通常用于以下几种情况：作为计数器计算循环执行的次数或控制循环执行的次数。保存数据值以供流控语句测试。保存由存储过程返回代码的数据值。变量的作用域指从申明变量的开始位置到含有该变量的批处理或存储过程的结束位置。,表12-2 Transact-SQL的语法规则,变量的赋值方法：SET local variable=expressionSELECT local variable=expression,n,打印输出变量,PRINT any ASCII text|local_variable|FUNCTION|string_expr例：If exists(select*from s where sno=s1)print 学生表S中存在S1Else print 学生表S中不存在S1,2数据库对象的引用规则,一般情况下，数据库对象的引用都由以下四部分所组成:l server_name 用于指定所连结的本地服务器或远程服务器的名称。l database_name 用于确定在服务器中当前状态下所操作的数据库名称l object_name 在数据库中被引用的数据库对象名称l owner_name 表示数据库对象的所有者所以一个完整的数据对象引用的表示方法应该为：server_name.database_name.owner_name.object_name 其中，服务器名称、数据库名称以及所有者都可以省略,例如：.班级表、.课程信息表。,12.1.3 Transact-SQL语法元素在大部分Transact-SQL语句中，都包含诸如标识符、数据类型、函数、表达式、运算符、注释以及保留关键字等语法元素。1 标识符 用于标识数据库对象的名称，这些对象包括服务器、数据库及相关对象（如表、视图、列、索引、触发器、过程、约束、规则等）。标识符在定义对象时进行命名，当需要使用某个对象时可以通过引用该对象的名称来完成。标识符可划分为常规标识符与分隔标识符两类，其中常规标识符的命名规则如下：l 第一个字符必须由字母a-z、A-Z，以及来自其他语言的字母字符或者下划线_、#构成，其中表示局部变量或参数，以#开头的标识符表示全局临时对象，以开始的标识符表示全局变量，也称为配置函数。l在定义标识符时，不能占用Transact-SQL 的保留字，例如不能将Table、View、Index等定义为一个标识符。l 在标识符中不能含有空格，并且标识符中的字符数量不能超过128个。如果定义的标识符不符合上述规则时，即被称为分隔标识符，需要使用双引号”或方括号对其进行分割。例如：SELECT*FROM my table,1 数据类型Transact-SQL中的基本数据类型如表12-3表所示：表12-3 Transact-SQL基本数据类型,（1）整型数据 整数数据类型是最常用的数据类型之一，由正整数和负整数所组成，使用 bigint、int、smallint和tinyint数据类型进行存储。bigint 数据类型可存储的数字范围比 int 数据类型广。int 数据类型比 smallint 数据类型的存储范围大，而 smallint 的数值范围又比 tinyint 类型大。,lbigint：可以存储-263到263-1之间的数字，占据8个字节存储空间。lint：可以存储从-231到231-1范围之间的所有整数，占据4字节存储空间。lsmallint：可以存储从-215到215-1范围之间的所有整数，占据2字节存储空间。ltinyint：可以存储从0到255范围之间的所有正整数。,（2）浮点数据类型主要包括Real、Float、Decimal和numeric四种类型。,Real：用于存储7位小数的十进制数据，所能够表示的范围为-3.40E+38到1.79E+38。Float：可以精确到第15位小数，数据范围为-1.79E-308到1.79E+308。Decimal：提供小数所需要的实际存储空间，可以存储2到17个字节的从-1038-1到1038-1之间的数值。numeric：与Decimal数据类型几乎完全相同，区别是在表格中，只有numeric型的数据可以带有identity关键字的列。,（3）字符数据类型SQL Server提供了三种字符数据类型，分别是Char、Varchar和Text。,lChar：最长可以容纳8000个字符，并且每个字符占用一个字节的存储空间。使用Char数据类型定义变量时，需要指定数据的最大长度。如果实际数据的字符长度小于指定长度时，剩余的字节用空格来填充。如果实际数据的长度超过了指定的长度，则超出部分将会被删除。在表示字符串常量时，需要使用一对单引号将其括起来。lvarchar：该数据类型的使用方式与Char数据类型类似。Char 数据类型不同的是，Varchar数据类型所占用的存储空间由字符数据所占据的实际长度来确定。ltext：该数据类型所能表示的最大长度为 231-1 即2,147,483,647个字符，当需要表示的数据类型长度超过了8000时，可以采用text来处理可变长度的字符数据。,（4）日期/时间数据类型日期/时间数据类型可以分为datetime和smalldatetime两类。,lDatetime：范围从1753年1月1日到9999年12月31日，可以精确到千分之一秒，此类型的数据占用8个字节的存储空间。lSmalldatetime：数据范围从1900年1月1日到2079年6月6日，可以精确到分，此类型的数据占4个字节的存储空间。,（5）货币数据类型SQL Server提供了Money和Smallmoney两种货币数据类型。,lMoney：占据8字节存储空间。每4字节分别用于表示货币值的整数部分及小数部分。Money的取值的范围为-263到263-1，并且可以精确到万分之一货币单位。lSmallmoney：占据4字节存储空间。每2字节分别用于表示货币值的整数部分以及小数部分。smallmoney的取值范围为-214,748.3648到+214,748.3647，可以精确到万分之一货币单位。,（6）二进制数据类型用于存储二进制数据，有binary、varbinary两种。,l Binary:用于存储固定长度的二进制数据，表示数据的长度取值为1到8000个字节。在输入数据时必须在数据前加上字符0X作为二进制标识。如要输入abc则应输入0 xabc。若输入的数据过长将会截掉其超出部分，若输入的数据位数为奇数则会在起始符号0X后添加一个0。如上述的0 xabc会被系统自动变为0 x0abc。lVarbinary:具有可变长度的特性，表示数据的长度也为1到8000个字节，若输入的数据过长将会截掉其超出部分。当binary数据类型允许NULL值时将被视为varbinary数据类型。,（7）逻辑数据类型 bit数据类型占用1个字节的存储空间，其值为0或1，如果输入0或1以外的值将被视为1。bit类型不能定义为NULL值（所谓NULL值是指空值或无意义的值）。（8）文本和图形数据类型 这类数据类型用于存储大量的字符或二进制数据。,lText：用于存储大量文本数据，其容量理论上为1-231-1（2,147,483,647）个字节，在实际应用时需要视硬盘的存储空间而定。lntext：与text类型相似，不同的是ntext类型采用Unicode标准字符集(Character Set),因此其理论容量为230-1(1,073,741,823)个字节。l image:用于存储大量的二进制数据。其理论容量为231-1(2,147,483,647)个字节。通常用来存储图形等（OLE Object Linking and Embedding，对象连接和嵌入）对象。在输入数据时同binary数据类型一样，必须在数据前加上字符“0X”作为二进制标识。,（9）特定数据类型SQLServer提供的特殊数据类型有Timestamp、Uniqueidentifier2种。,lTimestamp：用于表示SQL Server 活动的先后顺序，以二进投影的格式表示。Timestamp 数据与插入数据或者日期和时间没有关系。l Uniqueidentifier：由 16 字节的十六进制数字组成，此数字称为GUID（GloballyUniqueIdentifier即全球惟一鉴别号）。此数字由SQLServer的NEWID函数产生全球惟一的编码。在全球各地的计算机经由此函数产生的数字不会相同。,（10）用户定义的数据类型 用户定义的数据类型基于在 Microsoft SQL Server 中提供的数据类型。当几个表中必须存储同一种数据类型时，并且为保证这些列有相同的数据类型、长度和可空性时，可以使用用户定义的数据类型。例如，可定义一种称为postal_code 的数据类型，它基于 Char 数据类型。当创建用户定义的数据类型时，必须提供三个数：数据类型的名称、所基于的系统数据类型和数据类型的可空性。（11）新数据类型 SQLServer2000中增加了3种数据类型bigint、SQL_variant和table。其中bigint数据类型已在整数类型中介绍。,l SQL_variant：可以存储除文本图形数据text、ntext、image、timestamp类型数据外其它任何合法的SQLServer数据。此数据类型大大方便了SQLServer的开发工作。l Table:用于存储对表或视图处理后的结果集。这一新类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便快。,【例12-1】在数据库XSCJ中定义一个长度为12的字符串类型变量student，对该变量进行赋值，并且查询出“学生基本信息表“中该变量所指定姓名的学生信息。在查询分析器中运行如下命令：USE XSCJGODECLARE student CHAR(12)SET student=王倩倩SELECT*FROM 学生基本信息表 WHERE 姓名=student运行结果如图12-1所示。,应用举例：,图12-1 定义一个长度为12的字符串类型变量,【例12-2】在数据库XSCJ中定义两个日期时间类型的变量max_csrq、min_csrq，分别用于查询“学生基本信息表”中“出生日期”的最大值、最小值。可在查询分析器中运行如下命令：USE XSCJGODECLARE max_csrq DATETIME,min_csrq DATETIMESELECT max_csrq=MAX(出生日期),min_csrq=MIN(出生日期)FROM 学生基本信息表PRINT max_csrqPRINT min_csrq运行结果如图12-2所示：,图12-2定义日期时间类型变量运行结果,12.2.4 常用函数,在SQL Server 2000中提供了大量的系统函数，通过使用这些函数，用户可以根据需要完成特定的操作。常用的系统函数有：聚合函数、数学函数、字符串函数、日期时间函数、数据类型转换函数等。,1聚合函数聚合函数也称为统计函数，它对一组值进行计算并返回一个数值。聚合函数经常与SELECT语句一起使用。常用聚合函数如表12-5所示。表12-5 SQL Server聚合函数及其功能,【例12-3】计算XSCJ数据库中“学生基本信息表”的总行数。在查询分析器中运行如下命令：USE XSCJGOSELECT COUNT(*)AS 学生总人数 FROM 学生基本信息表GO运行结果如图12-3所示。,表12-3 计算学生基本信息表的总人数,【例12-4】计算XSCJ数据库中课程编号为003的课程总成绩和平均成绩。在查询分析器中运行如下命令：USE XSCJGOSELECT SUM(成绩)AS 课程总分,AVG(成绩)AS 课程平均分 FROM 成绩表 WHERE 课程编号=003GO运行结果如图12-4所示。,图12-4 计算课程编号为003的课程总分和平均分,【例12-5】在成绩表中查询课程编号为003的课程的最高分和最低分。在查询分析器中运行如下命令：USE XSCJGOSELECT MAX(成绩)AS 最高分,MIN(成绩)AS 最低分FROM 成绩表WHERE 课程编号=003GO运行结果如图12-5所示。,图12-5 查询课程编号为003的课程最高分和最低分,2数学函数数学函数用来对数值型数据进行数学运算。常用数学函数如表12-6所示。表12-6 常用数学函数,【例12-6】分别输出2的3次幂、-1的绝对值、2的平方、3.14的整数部分。在查询分析器中运行如下命令：PRINT POWER(2,3)PRINT ABS(-1)PRINT SQUARE(2)PRINT FLOOR(3.14)GO运行结果为：8、1、4、3。,3字符串函数,字符串函数可以对char、nchar、varchar、nvchar等类型的参数执行操作，并返回相应的结果，返回值一般为字符串或数字。SQL Server 2000中所包含的字符串函数如表12-7所示。表12-7 常用字符串函数,【例12-7】使用LEN函数显示字符串常量以及字符串变量的长度。提示：LEN函数用于计算字符串中所包含的字符个数，如果字符串尾部含有空格则会被忽略。在查询分析器中运行如下命令：PRINT LEN(computer department)DECLARE s1 char(10)DECLARE s2 char(10)SET s1=welcomeSET s2=hellow PRINT LEN(s1)PRINT LEN(s2)运行结果为：19，7，6。,【例12-8】给定一个字符串have a good time，判断字符g在整个字符串中的位置。提示：CHARINDEX函数用于在规定字符串中对子字符串进行查询。当返回值大于零时表示子字符串的起始位置，返回值为0时表明没有查询结果。在查询分析器中运行如下命令：DECLARE s CHAR(20)SET s=have a good timePRINT CHARINDEX(g,s)运行结果为：8,4日期时间函数日期时间函数可以对日期时间类型的参数进行运算、处理，并返回一个字符串、数字或日期和时间类型的值。SQL Server 2000中提供的日期时间函数如表12-7所示。表12-7常用日期时间函数,在查询分析器中运行如下命令：DECLARE xtsj DATETIMESET xtsj=GETDATE()SELECT YEAR(xtsj)SELECT MONTH(xtsj)SELECT DAY(xtsj)运行结果如图12-6所示。,图12-6 计算时期时间信息,【例12-9】获取系统时间信息，在查询分析器中分别显示系统时间中的年份、月份以及日期。提示：GETDATE函数用于返回当前的系统时间，YEAR，MONTH，DAY函数可以取得时间中的年、月、日的数值。,【例12-10】通过对“学生基本信息表”中的“出生日期”字段进行计算，查询每一位学生的年龄。提示：利用DATEDIFF函数可以计算出两个日期之间的距离，该函数含有三个参数，第一个参数通常可以为yy（年）或mm（月）或dd（日），若第一个参数为yy时，该函数返回值为后两个日期参数之间年份的差距。在本例中，当前的日期由GETDATE函数获得后，计算与每一位学生的出生日期之间年份的差距，从而获得学生的年龄并在查询结果中显示。在查询分析器中运行如下命令：SELECT 学号,姓名,DATEDIFF(yy,出生日期,GETDATE()from 学生基本信息表运行结果如图12-7所示。,图12-7查询每一位学生的年龄,Getdate()函数：按datetime值的格式返回当前系统日期和时间。,在学生基本信息表中添加入学时间字段，并设其默认值为添加记录的时间：alter table 学生基本信息表 add 入学日期 datetime default getdate()insert into 学生基本信息表(学号,姓名)values(002,John),5数据类型转换函数 在不同的数据类型之间进行运算时，需要将其转换为相同的数据类型。在SQL Server中，某些数据类型可以由系统自动完成转换，当系统不能够自动执行不同类型表达式的转换时，可以通过CAST和CONVERT函数对数据进行转换。,CAST(expression AS data_type)CONVERT(data_type(length),expressoin,style),数据转换函数应用举例：,declare a char(12)set a=convert(char(10),getdate(),102)print a,declare a char(12)set a=cast(getdate()as char(10)print a,【例12-11】查询“学生基本信息表”中的学号、姓名、年龄，并且将这三个字段通过“+”运算符进行连结显示在查询结果中。提示：由于计算学生年龄的结果为整数，而学号、姓名均为字符串类型的值，因而在运算之前,需要将年龄的计算结果转化为字符串，即CAST(DATEDIFF(yy,出生日期,GETDATE()AS CHAR(2)。在查询分析器中运行如下命令：SELECT 学号+姓名+年龄：+CAST(DATEDIFF(yy,出生日期,GETDATE()AS CHAR(2)FROM 学生基本信息表运行结果如图12-8所示。,图12-8 查询“学生基本信息表”中的学号、姓名、年龄,【例12-12】将常量3.14分别转换为整数类型以及字符串类型并且输出结果。在查询分析器中运行如下命令：PRINT CONVERT(INTEGER,3.14)PRINT CONVERT(CHAR(4),3.14)结果显示为：3、3.14。,DB_ID、DB_NAME 分别返回当前数据库的编号以及名称HOST_ID、HOST_NAME 分别返回主机编号以及名称OBJECT_ID、OBJECT_NAME 分别返回对象的编号以及名称SUSER_ID、SUSER_NAME 分别返回SQL Server 身份验证模式下，当前登录用户的编号以及名称 USER_ID、USER_NAME 返回系统用户编号、名称，通常该用户为dbo 例如：SELECT DB_ID()表示调用DB_ID 函数来获得当前数据库在系统中的编号。与字段有关的系统函数包括：COL_NAME 返回列名。COL_LENGTH 返回列长度。INDEX_COL 返回索引列名称。例如：SELECT COL_LENGTH(课程信息表,课程名称)可以取得“课程信息表”中“课程名称”字段的长度。,6系统函数系统函数可以使得用户在不直接访问系统表的情况下就可获得系统表的信息。针对于数据库、主机、对象、登录以及用户的系统函数包括：,12.6.4 流控语句流控制语句采用了与程序设计语言相似的机制，使其能够产生控制程序执行及流程分支的作用。通过使用流程控制语句，用户可以完成功能较为复杂的操作，并且使得程序获得更好的逻辑性和结构性。1BEGIN.END语句用于将一系列的SQL语句合并为一组语句，当需要同时执行两条以上的语句时，可以使用BEGINEND语句将这些语句包含在内形成一个语句块，作为一个整体来执行。通常该语句可以嵌套在其他语句中，如条件分支语句、循环语句中。BEGINEND语法格式为：BEGIN 语句1 语句2 语句nEND,2IFELSE语句该语句用于设计条件分支流程，根据给定的条件，程序可以执行不同的操作和运算，从而使程序的功能更加完善。该语句的格式为：IF 条件表达式 语句块1ELSE语句块2其中：条件表达式为一个布尔表达式，结果为真或假。当条件成立时，执行语句块1，否则执行语句块2。当需要判断多个条件时，可以对IF.ELSE进行嵌套。,【例12-23】在“学生基本信息表”中查询班级编号为20041001的班级中是否有党员。要求：如果有党员则显示党员的人数，否则，提示该班级没有党员。,在查询分析器中运行如下命令：USE XSCJGOIF(SELECT COUNT(*)FROM 学生基本信息表 WHERE 班级编号=20041001 AND 政治面貌=党员)0BEGIN DECLARE dy INTEGER SELECT dy=COUNT(*)FROM 学生基本信息表 WHERE 班级编号=20041001 AND 政治面貌=党员 PRINT 党员的人数为：PRINT dyENDELSE PRINT 该班级没有党员,上述程序中利用COUNT函数计算出满足条件的学生人数，并且根据人数进行判断，当人数大于零时，将人数赋值给dy变量并输出结果，程序运行结果如图12-19所示。,图12-19 在“学生基本信息表”中查询班级编号为20041001的班级中是否有党员,3CASE语句CASE语句用于计算多个条件并为每个条件返回单个值，以简化SQL语句格式。CASE语句不同于其他SQL语句，不能作为独立的语句来执行，而是需要作为其他语句的一部分来执行。CASE语句有两种格式：简单CASE表达式以及搜索CASE表达式。（1）简单CASE表达式语法格式为：CASE 表达式WHEN 条件1 THEN 结果表达式1WHEN 条件2 THEN 结果表达式2ELSE 结果表达式nEND在简单表达式中，将表达式与每一个条件依次进行比较，如果遇到表达式与条件相匹配时，停止比较，并且返回满足条件的WHEN子句所对应的结果表达式。如果表达式与所有的条件都不匹配时，则返回ELSE子句中的结果表达式，如果不存在ELSE子句，则返回NULL值。如果表达式与多个条件匹配时，CASE函数返回第一次满足条件时的WHEN子句所对应的结果表达式。,【例12-24】根据系统时间判断当前日期所对应的星期值并且输出结果。在查询分析器中运行如下命令：DECLARE dt DATETIMESET dt=DATEPART(w,GETDATE()SELECT CASE dt WHEN 1 THEN 星期天 WHEN 2 THEN 星期一 WHEN 3 THEN 星期二 WHEN 4 THEN 星期三 WHEN 5 THEN 星期四 WHEN 6 THEN 星期五 WHEN 7 THEN 星期六END该实例中，首先通过DATEPART函数获得当前时间所对应的星期数，范围为1至7，其中1代表星期天，7代表星期六，构造一个条件分支。然后将系统的星期数值通过CASE函数转换为相应的字符串信息并显示结果。运行结果如图12-20所示。,图12-20 根据系统时间判断当前日期所对应的星期值并且输出结果,（2）搜索CASE表达式搜索CASE表达式与简单CASE表达式的功能类似，但能够实现的条件分支要比简单CASE表达式更为复杂。其语法格式为：CASE WHEN 布尔表达式 THEN 结果表达式 WHEN 布尔表达式2 THEN 结果表达式2 ELSE 结果表达式nEND【例12-25】取得系统时间，并且判断当前时间在一天中所处的时间段，并且在查询分析器中输出提示信息。在查询分析器中运行如下命令：DECLARE sj DATETIMESET sj=DATEPART(hh,GETDATE(),SELECT CASE WHEN sj=20 and sj=14 and sj=12 and sj=10 and sj=0 and sj10 THEN 临晨 END,本例中首先通过DATEPART函数取得当前时间中的小时数，并保存在sj变量中，然后构造CASE函数，用于确定不同的条件分支。将变量sj分别与CASE函数中的条件依次进行比较，直到满足条件时输出结果。运行结果如图12-21所示：,图12-21取得系统时间并判断当前时间所处的时间段,4WHILE语句WHILE语句可以在满足条件的情况下重复执行在循环体内的语句。在循环体内部可以使用CONTINUE、BREAK语句对循环进行控制。WHILE语句的语法格式为：WHILE 条件表达式BEGIN 语句块1 BREAK 语句块2 CONTINUE 语句块3END 当条件表达式的值为真时，循环将重复执行，直到条件表达式的值为假时，退出循环。CONTINUE命令可以忽略其后的语句而继续进入下一次循环。而BREAK命令可以提前终止整个循环过程。,【例12-26】利用循环计算1+2+3+99+100的值。在查询分析器中运行如下命令：DECLARE sum int,i intSET sum=0SET i=1WHILE i=100BEGIN SET sum=sum+i SET i=i+1ENDPRINT sum运行结果为：5050。【例12-27】利用WHILE循环计算当前时间距离2012年1月1日之间的天数。在查询分析器中运行如下命令：DECLARE num int,sj DATETIMESET sj=GETDATE()PRINT 当前时间为：+CAST(sj AS CHAR(20)SET num=0WHILE 1=1BEGIN,SET sj=DATEADD(day,1,sj)SET num=num+1 IF DATEPART(yy,sj)=2012 BREAKEND PRINT 距离2012年1月1日还有+CAST(num AS VARCHAR(10)+天本实例中，首先由sj变量获取系统时间，其后在while的每次循环中，都对sj对应的日期加1。当日期增长至2006年时，使用BREAK语句退出循环，用num变量累计循环的次数，退出循环时num值为最终的结果。运行结果如图12-22所示。,图12-12 利用WHILE循环计算当前时间距离2007年1月1日之间的天数,12.7 存储过程1 存储过程概述存储过程是一组编译在单个执行计划中的Transact-SQL语句，分为系统存储过程和用户自定义存储过程两种。SQL Server包含了多种多样的系统存储过程，用户可以通过调用这些系统存储过程对系统进行管理以及对数据信息进行操作。系统存储过程以“sp_”开头，并且保存在Master数据库中，部分存储过程只能由系统管理员进行调用，普通用户可以经过授权后使用其它的系统存储过程。,存储过程的作用,通过创建存储过程以及在ASP或其它程序设计语言中调用存储过程，就可以避免将SQL语句同ASP代码混杂在一起。这样做的好处至少有三个：第一、大大提高效率。存储过程本身的执行速度非常快，而且，调用存储过程可以大大减少同数据库的交互次数。第二、提高安全性。假如将SQL语句混合在ASP代码中，一旦代码失密，同时也就意味着库结构失密。第三、有利于SQL语句的重用。,2 建立存储过程,使用CREATE PROCEDURE创建一个存储过程,CREATE PROC EDURE procedure_name;number parameter data_type VARYING=default OUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement.n,procedure_name新存储过程的名称。过程名必须符合标识符规则，且对于数据库及其所有者必须唯一。要创建局部临时过程，可以在 procedure_name 前面加一个编号符(#procedure_name)，要创建全局临时过程，可以在 procedure_name 前面加两个编号符(#procedure_name)。完整的名称（包括#或#）不能超过 128 个字符。指定过程所有者的名称是可选的。;number是可选的整数，用来对同名的过程分组，以便用一条 DROP PROCEDURE 语句即可将同组的过程一起除去。例如，名为 orders 的应用程序使用的过程可以命名为 orderproc;1、orderproc;2 等。DROP PROCEDURE orderproc 语句将除去整个组。如果名称中包含定界标识符，则数字不应包含在标识符中，只应在 procedure_name 前后使用适当的定界符。parameter过程中的参数。在 CREATE PROCEDURE 语句中可以声明一个或多个参数。用户必须在执行过程时提供每个所声明参数的值（除非定义了该参数的默认值）。存储过程最多可以有 2.100 个参数。使用 符号作为第一个字符来指定参数名称。参数名称必须符合标识符的规则。每个过程的参数仅用于该过程本身；相同的参数名称可以用在其它过程中。默认情况下，参数只能代替常量，而不能用于代替表名、列名或其它数据库对象的名称。有关更多信息。data_type参数的数据类型。所有数据类型（包括 text、ntext 和 image）均可以用作存储过程的参数。不过，cursor 数据类型只能用于 OUTPUT 参数。如果指定的数据类型为 cursor，也必须同时指定 VARYING 和 OUTPUT 关键字。VARYING指定作为输出参数支持的结果集（由存储过程动态构造，内容可以变化）。仅适用于游标参数。,default参数的默认值。如果定义了默认值，不必指定该参数的值即可执行过程。默认值必须是常量或 NULL。如果过程将对该参数使用 LIKE 关键字，那么默认值中可以包含通配符（%、_、和）。OUTPUT表明参数是返回参数。该选项的值可以返回给 EXECUTE。使用 OUTPUT 参数可将信息返回给调用过程。FOR REPLICATION指定不能在订阅服务器上执行为复制创建的存储过程。.使用 FOR REPLICATION 选项创建的存储过程可用作存储过程筛选，且只能在复制过程中执行。本选项不能和 WITH RECOMPILE 选项一起使用。AS指定过程要执行的操作。sql_statement过程中要包含的任意数目和类型的 Transact-SQL 语句。但有一些限制。n是表示此过程可以包含多条 Transact-SQL 语句的占位符。,只返回单一记录集的存储过程,假设有以下存储过程 CREATE PROCEDURE dbo.getUserList as set nocount on begin select*from dbo.userinfoend go,以上存储过程取得userinfo表中的所有记录，返回一个记录集。,通过command对象调用该存储过程的ASP代码如下,DIM MyComm,MyRst Set MyComm=Server.CreateObject(ADODB.Command)MyComm.ActiveConnection=MyConStr MyConStr是数据库连接字串 MyComm.CommandText=getUserList“指定存储过程名 MyComm.CommandType=4 表明这是一个存储过程 MyComm.Prepared=true 要求将SQL命令先行编译 Set MyRst=MyComm.ExecuteSet MyComm=Nothing 存储过程取得的记录集赋给MyRst，接下来，可以对MyRst进行操作。,在以上代码中，CommandType属性表明请求的类型，取值及说明如下：-1 表明CommandText参数的类型无法确定 1 表明CommandText是一般的命令类型 2 表明CommandText参数是一个存在的表名称 4 表明CommandText参数是一个存储过程的名称,例：建立存储过程(xfjs),根据提供学分单价和学生选课情况计算应交学费,create procedure xfjs price floatas declare sno char(8)declare fees floatdeclare aa cursor for select 学号,sum(学分)*price from 成绩表,课程信息表 where 成绩表.课程编号=课程信息表.课程编号 group by 学号;open aa;fetch aa into sno,fees;while fetch_status=0begin update 学生基本信息表 set 应交学费=fees where 学号=sno;fetch aa into sno,fees;endclose aa;deallocate aa;go