数据库原理与应用07.Transact-SQL程序设计

数据库原理与应用07.Transact-SQL程序设计1注释2变量3运算符4函数5流程控制语句1注释 注释是程序代码中不执行的文本字符串〔也称为注解〕。在SQLServer中，可以使用两种类型的注释字符：一种是ANSI标准的注释符“--〞，它用于单行注释；另一种是与C语言相同的程序注释符号，即“/**/〞。2变量 变量是一种语言中必不可少的组成局部。Transact-SQL语言中有两种形式的变量，一种是用户自己定义的局部变量，另外一种是系统提供的全局变量。1.局部变量利用局部变量可以保存程序执行过程中的中间结果，保存由存储过程返回的数据值等。〔1〕局部变量的定义必须先用DECLARE命令定义后才可以使用。DECLAER{@local_variabledata_type}[…n]〔2〕局部变量的赋值方法SET{{@local_variable=expression}或者SELECT{@local_variable=expression}[,...n]2.全局变量全局变量是SQLServer系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQLServer的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。使用全局变量时应该注意以下几点：①全局变量不是由用户的程序定义的，它们是在效劳器级定义的。②用户只能使用预先定义的全局变量。③引用全局变量时，必须以标记符“@@〞开头。④局部变量的名称不能与全局变量的名称相同，否那么会在应用程序中出现不可预测的结果。常用的全局变量@@Error@@RowCount@@Identity3运算符运算符是一些符号，它们能够用来执行算术运算、字符串连接、赋值以及在字段、常量和变量之间进行比较。在SQLServer2000中，运算符主要有以下六大类：算术运算符、赋值运算符、位运算符、比较运算符、逻辑运算符以及字符串串联运算符。1．算术运算符算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是数字数据类型分类的任何数据类型。算术运算符包括加〔+〕、减〔—〕、乘〔*〕、除〔/〕和取模〔%〕。2．赋值运算符Transact-SQL中只有一个赋值运算符，即等号〔=〕。赋值运算符使我们能够将数据值指派给特定的对象。另外，还可以使用赋值运算符在列标题和为列定义值的表达式之间建立关系。3．位运算符位运算符使我们能够在整型数据或者二进制数据〔image数据类型除外〕之间执行位操作。此外，在位运算符左右两侧的操作数不能同时是二进制数据。

表2-1位运算符运

算

符

含

义&（按位AND）按位AND（两个操作数）。|（按位OR）按位OR（两个操作数）。^（按位互斥OR）按位互斥OR（两个操作数）。

4．比较运算符比较运算符用于比较两个表达式的大小或是否相同，其比较的结果是布尔值，即TRUE〔表示表达式的结果为真〕、FALSE〔表示表达式的结果为假〕以及UNKNOWN。除了text、ntext或image数据类型的表达式外，比较运算符可以用于所有的表达式。5．逻辑运算符逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括AND、OR和NOT等运算符。逻辑运算符和比较运算符一样，返回带有TRUE或FALSE值的布尔数据类型。6．字符串串联运算符字符串串联运算符允许通过加号(+)进行字符串串联，这个加号即被称为字符串串联运算符。例如对于语句SELECT‘abc’+’def’，其结果为abcdef。运算符的优先等级从高到低如下所示括号：〔〕；乘、除、求模运算符：*、/、%；加减运算符：+、-；比较运算符：=、>、<、>=、<=、<>、!=、!>、!<；位运算符：^、&、|；逻辑运算符：NOT；逻辑运算符：AND；逻辑运算符：OR。4函数在Transact-SQL语言中，函数被用来执行一些特殊的运算以支持SQLServer的标准命令。Transact-SQL编程语言提供了三种函数：

㈠行集函数：行集函数可以在Transact-SQL语句中当作表引用。

㈡聚合函数：聚合函数用于对一组值执行计算并返回一个单一的值。㈢标量函数：标量函数用于对传递给它的一个或者多个参数值进行处理和计算，并返回一个单一的值。SQLServer中最常用的几种函数字符串函数日期和时间函数数学函数转换函数系统函数聚合函数其它函数字符串函数字符串函数可以对二进制数据、字符串和表达式执行不同的运算，大多数字符串函数只能用于char和varchar数据类型以及明确转换成char和varchar的数据类型，少数几个字符串函数也可以用于binary和varbinary数据类型。此外，某些字符串函数还能够处理text、ntext、image数据类型的数据。字符串函数的分类：根本字符串函数：UPPER、LOWER、SPACE、REPLICATE、STUFF、REVERSE、LTRIM、RTRIM。字符串查找函数：CHARINDEX、PATINDEX。长度和分析函数：DATALENGTH、SUBSTRING、RIGHT。转换函数：ASCH、CHAR、STR、SOUNDEX、DIFFERENCE。2．日期和时间函数日期和时间函数用于对日期和时间数据进行各种不同的处理和运算，并返回一个字符串、数字值或日期和时间值。在SQLServer2000中，日期和时间函数的类型如表2-5所示；另外，表2-6列出了日期类型的名称、缩写形式以及可接受的值。表2-5日期和时间函数的类型函

数参

数DATEADD(datepart,number,date)DATEDIFF(datepart,date1,date2)DATENAME(datepart,date)DATEPART(datepart,date)DAY(date)GETDATE（）MONTH(date)YEAR(date)从GETDATE函数返回的日期中

提取月份数。SELECTDATEPART(month,GETDATE())AS'MonthNumber'运行结果为：MonthNumber------------4从日期04/12/2006中返回月份数、天数和年份数。SELECTMONTH('04/12/2006'),DAY('04/12/2006'),YEAR('04/12/2006')运行结果为：-----------------4

12

20063．数学函数数学函数用于对数字表达式进行数学运算并返回运算结果。数学函数可以对SQLServer提供的数字数据〔decimal、integer、float、real、money、smallmoney、smallint和tinyint〕进行处理。在同一表达式中使用CEILING〔〕、FLOOR〔〕、ROUND〔〕函数。selectceiling(13.4),floor(13.4),round(13.4567,3)运行结果为：-------------------------141313.45704．转换函数一般情况下，SQLServer会自动处理某些数据类型的转换。例如，如果比较char和datetime表达式、smallint和int表达式、或不同长度的char表达式，SQLServer可以将它们自动转换，这种转换被称为隐性转换。但是，无法由SQLServer自动转换的或者是SQLServer自动转换的结果不符合预期结果的，就需要使用转换函数做显示转换。转换函数有两个：CONVERT和CAST。CONVERT和CAST函数CAST(expressionASdata_type)CONVERT函数允许用户把表达式从一种数据类型转换成另一种数据类型，还允许把日期转换成不同的样式。其语法形式为：CONVERT(data_type[(length)],expression[,style])USEpubsSELECTtitle,ytd_salesFROMtitlesWHERECAST(ytd_salesASchar(20))LIKE'15%'ANDtype='trad_cook'运行结果为：Title

ytd_sales------------------------------FiftyYearsinBuckinghamPalaceKitchens

150965．系统函数系统函数用于返回有关SQLServer系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其它函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。返回Northwind数据库的Employees表中的首列的名称。USENorthwindSELECTCOL_NAME(OBJECT_ID('Employees'),1)运行结果为：EmployeeID6．聚合函数聚合函数可以返回整个或者几个列或者一个列的汇总数据，它常用来计算SELECT语句查询的统计值。聚合函数经常与SELECT语句的GROUPBY子句一同使用。用户自定义函数CREATEFUNCTION创立用户定义函数，它是返回值的已保存的Transact-SQL例程。用户定义函数不能用于执行一组修改全局数据库状态的操作。与系统函数一样，用户定义函数可以从查询中唤醒调用。也可以像存储过程一样，通过EXECUTE语句执行。用户定义函数用ALTERFUNCTION修改，用DROPFUNCTION除去。CREATEFUNCTION[owner_name.]function_name

([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,...n]])RETURNSscalar_return_data_type[WITH<function_option>[[,]...n]][AS]BEGIN

function_body

RETURNscalar_expression

END5流程控制语句流程控制语句是指那些用来控制程序执行和流程分支的命令，在SQLServer2000中，流程控制语句主要用来控制SQL语句、语句块或者存储过程的执行流程。使用BEGIN…END可以将多条T-SQL语句封装起来，构成一个独立的语句块。BEGIN关键字表示语句块的开始，END关键字表示语句块的结束，它们必须成对出现。BEGIN…END的语法格式如下：BEGIN{sql_statement|statement_block}ENDBegin…EndIF…ELSE

IF…ELSE语句可以控制程序按条件执行。当IF关键字后的条件满足〔布尔表达式返回TRUE时〕，那么在执行IF关键字及其条件之后的T-SQL语句。否那么，就执行ELSE关键字后的T-SQL语句〔假设ELSE局部存在〕。IF…ELSE语句的语法格式如下：IFBoolean_expression{sql_statement|statement_block}[ELSE{sql_statement|statement_block}]查询课程号为“105237〞的课程的平均分是否超过了85分，假设超过，那么输出考出了高分的信息。USEEducationalGODECLARE@Course_IDvarchar(10)SELECT@Course_ID='105237'/*查询的课程号*/IF(SELECTAVG(Grade)FROMGradeWHERECourseID=@Course_ID)>85 /*平均分大于85分*/BEGINSELECT@Course_ID=CourseName/*从课程信息表中取课程名*/FROMCourseWHERECourseID=@Course_IDPRINT@Course_ID+'课程'PRINT'考出了高分'ENDELSEPRINT'考的一般'GOTO使用GOTO语句可以使程序的流程无条件地转移到指定的标签处继续执行。GOTO语句的语法格式如下：GOTOlabel其中label就是需指向的标签。标签必须符合标识符规那么，且必须存在。求1+2+3+…+100的累加和。DECLARE@iINT,@sumINTSET@i=1SET@sum=0LabelLoop:SET@sum=@sum+@iSET@i=@i+1IF(@i<=100)G