《数据库原理及应用》课件第6章

第6章数据库编程6.1Transact-SQL语言概述6.2Transact-SQL基础知识6.3流程控制语句6.4批处理6.5函数6.6编程技术应用

【技能目标】

学会使用Transact-SQL语言进行数据库应用编程，具备基本的数据库应用系统的开发能力。

【知识目标】

掌握SQLServer中变量的用法；掌握SQLServer中各类运算符的用法；掌握Transact-SQL基本流程控制语句；掌握批处理的概念；掌握SQLServer中常用函数的用法；掌握Transact-SQL编程技术的应用。

Transact-SQL语言是微软公司在MicrosoftSQLServer系统中使用的语言，是对SQL语言的一种扩展。Transact-SQL语言具有SQL的主要特点，同时增加了变量、运算符、函数、流程控制和注释等语言元素，使得其功能更加强大。6.1Transact-SQL语言概述使用Transact-SQL编写应用程序可以完成所有的数据库管理工作。任何应用程序，只要目的是向SQLServer的数据库管理系统发出命令以获得数据库管理系统的响应，最终都体现为以Transact-SQL语言为表现形式的指令。对用户来说，Transact-SQL是唯一可以和SQLServer2005数据库管理系统进行交互的语言。

Transact-SQL语言分为如下四类：

(1)数据定义语言DDL(DataDefinitionLanguage)：用来定义和管理数据库以及数据库中的各种对象，这些语句包括CREATE、ALTER和DROP等。如CREATETABLE、CREATEVIEW、DROPTABLE等。

(2)数据操纵语言DML(DataManipulationLanguage)：用来操纵数据库中的数据的语句，如SELECT、INSERT、UPDATE等。

(3)数据控制语言DCL(DataControlLanguage)：用来控制数据库组件的存取许可存取权限等的语句，如GRANT、REVOKE等。

(4)事务管理语言和附加的语言元素：用来处理事务操作方面的命令，如COMMIT、ROLLBACK等。

作为一种语言，Transact-SQL语言提供了有关变量、标识符、数据类型、表达式及流程控制语句等语言元素。这些语言元素被称为附加的语言元素。

上述分类中，数据定义语言DDL、数据操纵语言DML、数据控制语言DCL和事务管理语言分布在各章讲述。本章将以SCMS数据库编程应用为例重点讨论Transact-SQL语言中附加的语言元素。

6.2.1Transact-SQL语法约定

Transact-SQL语法约定如表6-1所示。6.2Transact-SQL基础知识表6-1Transact-SQL语法约定6.2.2数据库对象的引用规则

一般情况下，数据库对象的引用都由以下四部分组成：

●

server_name：用于指定所连接的本地服务器或远程服务器的名称。

●

database_name：用于确定在服务器中当前状态下所操作的数据库名称。

●

schema_name：数据库中指定包含对象的架构的名称。

●

object：对象的名称。一个完整的数据对象引用的表示方法为

[server_name.][database_name.][schema_name.]object_name

其中，服务器名称、数据库名称以及架构名称都可以省略。若省略中间级节点，则使用句点来表示这些位置。

说明SQLServer2000中完整的数据对象名称也包含四个标识符：服务器名称、数据库名称、所有者名称和对象名称。格式如下：

[server_name.][database_name.][owner_name.]object_name

SQLServer2005中使用对象的架构名称代替了对象的所有者名称，这样做的好处是用户规划数据库对象命名时更具有弹性。表6-2列出了对象名的有效格式。表6-2对象名的有效格式6.2.3标识符

标识符用于标识数据库对象的名称。这些对象包括服务器、数据库及相关对象(如表、视图、列、索引、触发器、过程、约束、规则等)。标识符用于对象命名，当需要使用某个对象时可以通过引用对象的名称来完成。

标识符可划分为常规标识符与分隔标识符两类。其中常规标识符的命名规则如下：

(1)标识符的第一个字符必须由字母a～z、A～Z以及来自其他语言的字母字符或_(下划线)、@、#构成。

(2)标识符不能是Transact-SQL的保留字。例如不能将TABLE、VIEW、INDEX等定义为一个标识符。

(3)标识符中不能包含空格。

(4)包含的字符数必须在1～128之间。

如果定义的标识符不符合上述规则，即被称为分隔标识符，需要使用双引号(“”)或方括号([])对其进行分隔。例如：SELECT*FROM[mytable]，其中mytable标识符含有空格，不符合标识符命名规则，要使用[]对其进行分隔。

说明最好不要使用@@或##来命名一个变量。因为这是SQLServer系统用于声明全局变量和全局临时变量的关键字符，所以使用它们命名一个变量时很容易引起混乱。可以使用存储过程sp_validname来判断一个标识符是否正确。其调用的语法格式是：sp_validname'标识符名'，当返回值为0时表示该标识符不合法，返回值大于0时表示该标识符是合法的。

例如：

DECLARE@countASint

EXECsp_validname'@count'6.2.4注释

注释是程序代码中不执行的文本字符串。SQLServer中有单行和多行两种类型的注释。

(1)单行注释：使用两个连在一起的减号“--”作为注释符。

(2)多行注释：使用“/*…*/”作为注释符。

【例6-1】使用单行注释。

INSERTINTOstudent(sno,sname,classno)

VALUES('2010091022','王萍萍','09011031')--向表中插入一条数据记录

服务器不执行“--”注释符后的文本。

【例6-2】使用多行注释。

/*向student表插入一条记录

其中sno和classno字段值不能为空*/

INSERTINTOstudent(sno,sname,classno)

VALUES('200901101190','张扬','09011031')

服务器不执行“/*”和“*/”注释符之间的文本。书写时在/和*之间不能有空格。6.2.5常量

常量是指在程序运行过程中其值不变的量。常量分为字符串常量、二进制常量、日期时间常量、整型常量、实数常量、货币常量和唯一标识常量。6.2.6变量

变量是指在程序运行过程中其值可以改变的量。Transact-SQL中变量分为局部变量和全局变量。局部变量由用户定义和使用，全局变量由系统定义并维护。

1.局部变量

用户自己定义的变量称为局部变量。它的作用范围仅在程序内部，在程序中通常用来存储从表中查询到的数据，或在程序执行过程中临时保存数据。局部变量必须以@开头，而且必须先定义后使用。局部变量定义的语法格式如下：

DECLARE{{@变量名数据类型}|{@游标变量名CURSOR}}[,...n]

参数说明如下：

●

@变量名：是变量的名称。局部变量名必须符合标识符规则。

●数据类型：是任何由系统支持的或用户自定义的数据类型，但不能是text、ntext或image数据类型。

●

@游标变量名：是游标变量的名称。

●

CURSOR：指定变量是局部游标变量。

【例6-3】定义一个整型变量count。

DECLARE@countint

说明局部变量没有被赋值之前，其值为NULL，如果要在程序体中引用，必须先为其赋值。

在Transact-SQL中不能像在一般的程序语言中一样使用“变量

=

变量值”格式来给变量赋值，必须使用SELECT或SET语句来给变量赋值。

SELECT和SET语句给变量赋值的语法格式分别如下：

SELECT@局部变量

=

变量值

SET@局部变量

=

变量值

SELECT命令可以将表达式的值赋给局部变量，也可以将SELECT查询的结果赋给局部变量。局部变量的输出可以通过SELECT语句或PRINT语句来实现。

【例6-4】定义变量@sname，并为变量赋初值。

DECLARE@snamechar(10)

SET@sname='王萍萍'

或者

DECLARE@snamechar(10)

SELECT@sname='王萍萍'

【例6-5】定义多个变量，并为多个变量赋初值。

DECLARE@iint,@jint

SET@i=5

SET@j=7

或者

DECLARE@iint,@jint

SELECT@i=5,@j=7

【例6-6】定义多个变量，并将查询结果赋给变量。

DECLARE@snamechar(10),@classchar(14)

SELECT@class='09031012'--通过SELECT赋值，也可以用SET赋值

SELECT@sname=sname--将查询结果赋给变量

FROMstudent

WHEREclassno=@class

说明使用SET赋值时一次只能给一个变量赋值，如果要给多个变量赋值，则要多次使用SET语句，而SELECT可以一次给多个变量赋值。由于SET语句功能更强且更严密，因此SQLServer推荐使用SET命令来给变量赋值。

【例6-7】定义一个整型变量和一个可变长字符型变量，为变量赋值并输出变量值。

DECLARE@variable_intint,@variable_charnvarchar(20)

SELECT@variable_int=40

SET@variable_char='WelcometoChina'

SELECT@variable_int

PRINT@variable_char

2.全局变量

全局变量是SQLServer系统定义和维护的变量。全局变量通常存储一些SQLServer的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统设定值或者Transact-SQL语句执行后的状态值。引用全局变量时必须以@@开头。局部变量的名称不能与全局变量相同，否则会在应用程序中出现不可预测的结果。

以下是几个常用的全局变量及其功能。●

@@CONNECTIONS：返回自上次启动SQLSERVER以来连接或试图连接的次数。

●

@@ROWCOUNT：返回上一条Transact-SQL语句影响到的数据行数。

●

@@ERROR：返回上一条Transact-SQL语句执行后的错误号。

●

@@PROCID：返回当前存储过程的ID标识。

●

@@REMSERVER：返回登录记录中远程服务器的名字。●

@@SPID：返回当前服务器进程的ID标识。

●

@@VERSION：返回当前SQLServer服务器的版本。

【例6-8】使用全局变量@@ROWCOUNT查询命令影响的行数。

SELECT*FROMstudent

SELECT@@ROWCOUNTAS'查询涉及行数为：'

执行结果如图6-1所示。

图6-1显示查询影响行数6.2.7运算符

与其他程序语言一样，Transact-SQL语言也有自己的运算符和运算规则。SQLServer2005运算符主要分为算术运算符、赋值运算符、位运算符、关系运算符、一元运算符、逻辑运算符以及字符串连接运算符。

1.算术运算符

算术运算符可以在两个表达式上执行数学运算，这两个表达式可以是任何数字数据类型。算术运算符如表6-3所示。表6-3算术运算符加和减运算符也可以对datetime和smalldatetime值进行运算。其使用格式如下：

日期+整数或者日期-整数

运算结果返回“日期”向前推或向后推“整数”天数的日期。

例如：

SELECTCONVERT(smalldatetime,'2010/5/21')-10

执行结果如图6-2所示。

图6-2算术运算符示例

2.赋值运算符

赋值运算符将数据值指派给特定的对象。Transact-SQL赋值运算符是等号(=)。

3.位运算符

位运算符在整型数据或者二进制数据之间执行位操作。Transact-SQL首先把整数数据转换为二进制数据，然后再对二进制数据进行按位运算。位运算符如表6-4所示。表6-4位运算符例如：

DECLARE@aint,@bint

SELECT@a=10,@b=3

SELECT@a&@bAS'a&b',@a|@bAS'a|b',~@aAS'~a',@a^@bAS'a^b'

执行结果如图6-3所示。

图6-3位运算符示例

4.关系运算符

关系运算符用于比较两个表达式的大小或是否相等。

比较的结果是布尔值，即TRUE(真)或FALSE(假)以及UNKNOWN。除了text、ntext或image数据类型的表达式外，关系运算符可以用于所有的表达式。关系运算符如表6-5所示。表6-5关系运算符5.一元运算符

一元运算符只对一个表达式执行操作。该表达式可以是numeric数据类型类别中的任何一种数据类型。一元运算符如表6-6所示。表6-6一元运算符6.逻辑运算符

逻辑运算符可以把多个逻辑表达式连接起来。逻辑运算符包括AND、OR和NOT等运算符。运算结果为TRUE或FALSE。逻辑运算符如表6-7所示。表6-7逻辑运算符7.字符串连接运算符

加号(+)用于连接两个或两个以上的字符串，将一个字符串连接到另一个的末尾。例如：

DECLARE@ichar(10)

SELECT@i=sname

FROMstudent

WHEREsno='200903101107'

PRINT'200903101107号的学生姓名是：'+@i

执行结果如图6-4所示。

图6-4字符串连接运算符示例6.2.8运算符优先级

当一个复杂的表达式有多个运算符时，运算符优先级决定运算执行的先后次序。运算符的优先级别如表6-8所示。数字越小，级别越高。运算时按照级别由高到低的次序进行。当一个表达式中的两个运算符有相同的运算符优先级别时，将按照它们在表达式中出现的位置从左到右进行求值。使用括号可以提高运算符的优先级。表6-8运算符优先级

1. BEGIN…END语句

BEGIN…END用来定义语句块。在BEGIN…END内的多条SQL语句相当于一个单一的语句。BEGIN和END必须成对出现。BEGIN…END中可嵌套另一BEGIN…END。其语法格式如下：

BEGIN

语句16.3流程控制语句语句2

…

END

BEGIN…END常用于下列几种情况：

(1)

WHILE循环需要包含多条语句。

(2)

CASE函数的元素需要包含多条语句。

(3)

IF…ELSE语句中需要包含多条语句。

2. IF…ELSE语句

IF…ELSE语句用来判断当某一条件成立时执行一个SQL语句块，条件不成立时执行另一个SQL语句块。IF…ELSE结构可以嵌套。

其语法格式如下：

IF<条件表达式>

<SQL语句块>

[ELSE[条件表达式]

<SQL语句块>]

其中<条件表达式>可以是各种表达式的组合。表达式的返回值为逻辑值，即真或假。ELSE子句是可选的。

【例6-9】查询学号为200903101105的学生的平均成绩，如果大于85显示“成绩优秀，再接再厉！”，否则显示“成绩一般，继续努力！”。

IF(SELECTAVG(result)FROMscWHEREsno='200903101105')>85

BEGIN

PRINT'200903101105'

PRINT'成绩优秀，再接再厉！！'

END

ELSE

BEGIN

PRINT'200903101105'

PRINT'成绩一般，继续努力！！'

END

执行结果如图6-5所示。

图6-5IF语句示例

3. CASE语句

CASE语句用于多条件分支选择。虽然在这种情况下也可使用IF…ELSE语句实现，但CASE语句可使程序结构显得更加精炼、清晰。

CASE语句有简单CASE和搜索型CASE两种。

1)简单CASE

简单CASE语句的语法格式如下：

CASE<输入表达式>

WHEN<条件表达式>THEN<结果表达式>

[...n]

[ELSE<结果表达式>]

END

【例6-10】在Transact-SQL中使用简单CASE语句。

SELECTclassname,'所属系部'=

CASEdno

WHEN'01'THEN'机械工程系'

WHEN'02'THEN'电气工程系'

WHEN'03'THEN'计算机工程系'

ELSE''

END

FROMclass

ORDERBYdno

执行结果如图6-6所示。

图6-6简单CASE语句示例简单CASE语句的执行过程是：将CASE后表达式的值与WHEN子句中表达式的值进行比较，如果相等，则返回THEN后的表达式，然后跳出CASE语句，否则返回ELSE子句中的表达式。ELSE子句是可选项，当CASE语句中不包含ELSE子句时，如果未能在WHEN子句中找到匹配项，则返回NULL。

2)搜索型CASE

搜索型CASE语句的语法格式如下：

CASE

WHEN<条件表达式>THEN<结果表达式>

[...n]

[ELSE<结果表达式>]

END

【例6-11】在Transact-SQL中使用搜索型CASE语句。

SELECTsno,cno,'成绩等级'=

CASE

WHEN(result<60)THEN'不及格'

WHEN(result>=60andresult<70)THEN'及格'

WHEN(result>=70andresult<80)THEN'中'

WHEN(result>=80andresult<90)THEN'良好'

ELSE'优秀'

END

FROMsc

执行结果如图6-7所示。

图6-7搜索型CASE语句示例搜索型CASE语句的执行过程是：首先测试WHEN后的布尔表达式，如果其值为真，则返回THEN后的表达式；否则测试下一个WHEN子句中的布尔表达式，如果所有布尔表达式的值都为假，则返回ELSE后的表达式。ELSE子句是可选项。

搜索型CASE语句与简单CASE语句的区别是：搜索型CASE运行执行各种比较操作和多条件测试(通过AND、OR实现)，而简单CASE表达式只能进行相等比较。

4. WHILE、CONTINUE和BREAK语句

WHILE语句用于实现循环控制结构。只要指定的条件为真，就重复执行SQL语句块。可以使用BREAK和CONTINUE语句在循环内部控制WHILE循环中语句的执行。WHILE语句也可以嵌套。其语法格式如下：

WHILE<条件表达式>

BEGIN

<SQL语句块>

[BREAK]

[CONTINUE]

[SQL语句块]

END

其中，BREAK语句退出WHILE循环，CONTINUE语句跳过其后SQL语句块开始下一次循环。

【例6-12】在Transact-SQL中使用WHILE语句。

DECLARE@rint,@sint,@tint

SELECT@r=2,@s=3

WHILE@r<5

BEGIN

PRINT@r

WHILE@s<4

BEGIN

SELECT@t=100*@r+@s

PRINT@t

SELECT@s=@s+1

END

SELECT@r=@r+2

SELECT@s=1

END

执行结果如图6-8所示。

图6-8WHILE语句示例

5. WAITFOR语句

WAITFOR语句用于指定触发语句块、存储过程或事务执行的时间、时间间隔或事件。其语法格式如下：

WAITFOR{DELAY'time'|TIME'time'}

参数说明如下：

●

DELAY：指示SQLServer一直等到指定的时间过去，最长可达24小时。

●

TIME：指示SQLServer等待到指定时间。

●

time：要等待的时间。不能指定日期，因此，在time值中不允许有日期部分。

【例6-13】等待1小时15分钟后执行SELECT语句。

WAITFORDELAY'01:15:00'

SELECT*FROMstudent

【例6-14】在晚上10:20执行存储过程update_all_stats。

WAITFORTIME'22:20'

EXECUTEupdate_all_stats

6. GOTO

GOTO语句用来改变程序执行的流程，使程序跳到标签指定的程序行再继续往下执行。作为跳转目标的标签可以是数字与字符的组合。其语法格式如下：

GOTO标签

【例6-15】使用IF语句求1～100之间的累加和并输出结果。

DECLARE@sumint,@countint

SET@sum=0

SET@count=1

label:

SELECT@sum=@sum+@count

SELECT@count=@count+1

IF@count<=100

GOTOlabel

SELECT'1到100的和为：',@sum

执行结果如图6-9所示。

图6-9GOTO语句示例

7. RETURN

RETURN语句的功能是无条件退出所在的批处理、存储过程和触发器。退出时可以返回状态信息。RETURN语句后面的任何语句不被执行。其语法格式如下：

RETURN([整数])

【例6-16】RETURN语句练习。

CREATEPROCEDUREp_ex

AS

DECLARE@iint,@jint

SELECT@i=5

SELECT@j=6

IF@i>@j

RETURN1

ELSE

RETURN2

说明如果用于存储过程，RETURN不能返回空值。如果某个过程试图返回空值(例如，使用RETURN@status，而@status为NULL)，则将生成警告消息并返回0值。

8.其他语句

1)

PRINT语句

PRINT语句向客户端返回一个用户自定义的信息即显示一个字符串(最长为255个字符)、局部变量或全局变量。如果变量值不是字符串，必须先用数据类型转换函数将其转换为字符串。其语法格式如下：

PRINT'文本'|@局部变量|@@全局变量|字符串表达式

【例6-17】PRINT语句练习。

DECLARE@xchar(4),@ychar(10)

SELECT@x='学习',@y='SQLServer'

PRINT'科技'

PRINT@x+@y

执行结果如图6-10所示。

图6-10PRINT语句示例

2)

RAISERROR语句

RAISERROR语句用于在SQLServer2005系统返回错误信息时，同时返回用户指定的信息。RAISERROR语句可以自动记录全局变量@@ERROR中指定的错误号，并且把错误号、严重性、错误状态以及错误消息的文本传送到客户的应用程序中。

与PRINT相比，RAISERROR在把消息返回给应用程序方面功能更强大，因此，如果用户需要在程序中调用SQLServer数据库系统错误，需要使用RAISERROR命令。

RAISERROR语句的语法格式如下：

RAISERROR(消息字符串，严重度，状态)

两个GO语句之间的SQL语句作为一个批处理。在一个批处理中可以包含一条或多条Transact-SQL语句，成为一个语句组。这样的语句组从应用程序一次性地发送到SQLServer服务器进行执行。SQLServer服务器将批处理编译成一个可执行单元，称为执行计划。这样处理可以节省系统开销。如果批处理中的某一条语句发生编译错误，执行计划就无法编译，从而导致批处理中的任何语句都无法执行。6.4批处理

【例6-18】批处理实例分析。

CREATEVIEWv_s

AS

SELECTsno,sname,ssex

FROMstudent

WHEREssex='女'

GO

SELECT*FROMv_s

GO分析：在该例中，不能将创建视图的批处理语句和SELECT查询语句之间的GO语句去掉。原因是如果去掉GO语句，则创建视图语句和查询语句是一个批处理语句，同时发送到服务器端执行，查询语句中的来源v_s还未生成，系统会报错。又如：

DECLARE@iint

GO

SET@i=10

GO

执行代码，系统报错，错误提示如图6-11所示。

图6-11批处理执行错误

函数是Transact-SQL中实现各种功能的有力工具。SQLServer2005提供两大类函数：内部函数和用户自定义函数。内部函数即系统定义的函数或系统内置函数。Transact-SQL中提供了丰富的系统内置函数，方便用户实现各种功能。同时用户可根据实际应用的特殊需要自行定义函数，用户自己定义的函数就是用户自定义函数。6.5函数6.5.1内部函数

SQLServer提供的内部函数包括聚合函数、字符串函数、数学函数、日期时间函数、数据类型转换函数、系统函数和元数据函数。

1.聚合函数

聚合函数也称为统计函数，它对一组值进行统计计算并返回一个数值。聚合函数经常与SELECT语句的子句一起使用。聚合函数的详细内容请参阅本书第5章的相关部分。

2.字符串函数

表6-9列出了常用字符串函数及其功能。

表6-9常用字符串函数及其功能【例6-19】LEN函数练习。求字符串“2010年上海世博会”的长度。

DECLARE@svarchar(20)

SET@s='2010年上海世博会'

PRINTLEN(@s)

执行结果如图6-12所示。

图6-12LEN函数示例

【例6-20】LEFT函数、RIGHT函数和SBUSTRING函数练习。求字符串的一部分。

DECLARE@svarchar(30)

SET@s='SQLServer2005数据库'

PRINTLEFT(@s,10)

PRINTRIGHT(@s,7)

PRINTSUBSTRING(@s,5,6)

执行结果如图6-13所示。

图6-13LEFT、RIGHT和SUBSTRING函数示例

【例6-21】STUFF函数练习。将字符串的一部分用其他字符替换。

SELECTSTUFF('我喜欢学习SQLServer2000',17,4,'2005'),STUFF('informate',8,2,'tion')

执行结果如图6-14所示。

图6-14STUFF函数示例

【例6-22】REVERSE函数练习。

SELECTREVERSE('123'),REVERSE('赤橙黄绿青蓝紫')

执行结果如图6-15所示。

图6-15REVERSE函数示例

【例6-23】LTRIM和RTRIM函数练习。去掉字符串的前导和后导空格。

DECLARE@ichar(20),@jchar(20)

SET@i='SQLServer2005'

SET@j='我喜欢学习！'

PRINT@j+@i

PRINTRTRIM(@j)+LTRIM(@i)

执行结果如图6-16所示。

图6-16LTRIM和RTRIM函数示例

3.数学函数

数学函数用来对数值型数据进行数学运算。表6-10列出了常用数学函数及其功能。表6-10常用数学函数及其功能【例6-24】SIGN和ABS函数练习。

SELECTSIGN(25),SIGN(-3),SIGN(0)

SELECTABS(2),ABS(0),ABS(-3)

执行结果如图6-17所示。

图6-17SIGN和ABS函数示例

【例6-25】CEILING和FLOOR函数练习。

SELECTCEILING(123),CEILING(12.3),CEILING(-12.3)

SELECTFLOOR(123),FLOOR(12.3),FLOOR(-12.3)

执行结果如图6-18所示。

图6-18CEILING和FLOOR函数示例

【例6-26】ROUND函数练习。

SELECTROUND(123.346,1),ROUND(12.346,2),ROUND(-12.346,2),ROUND(12.346,-2)

执行结果如图6-19所示。

图6-19ROUND函数示例

4.日期时间函数

日期时间函数用来操作datetime和smalldatetime类型的数据，执行算术运算。与其他函数一样，可以在SELECT语句的SELECT和WHERE子句以及表达式中使用日期函数。表6-11列出了常用日期时间函数及其功能。表6-11常用日期时间函数及其功能【例6-27】YEAR函数、MONTH函数和DAY函数练习。

SELECTYEAR('12/3/2009'),MONTH('12/3/2009'),DAY('12/3/2009')

执行结果如图6-20所示。

图6-20YEAR、MONTH和DAY函数示例

【例6-28】DATEADD函数练习。

SELECTDATEADD(DAY,12,'12/3/2009'),DATEADD(MONTH,12,'12/3/2009'),DATEADD(YEAR,12,'12/3/2009')

执行结果如图6-21所示。

图6-21DATEADD函数示例

【例6-29】GETDATE和DATEPART函数练习。

SELECTGETDATE(),DATEPART(YEAR,GETDATE())

执行结果如图6-22所示。

图6-22GETDATE和DATEPART函数示例

5.数据类型转换函数

一般情况下，SQLServer会自动完成数据类型的转换。例如，可以直接将字符数据类型或表达式与DATATIME数据类型或表达式比较。当表达式中用了int、smallint或tinyint时，SQLServer也可将int数据类型或表达式转换为smallint数据类型或表达式，这称为隐式转换。如果不能确定SQLServer是否能完成隐式转换或者使用了不能隐式转换的其他数据类型，就需要使用数据类型转换函数做显式转换。表6-12列出了数据类型转换函数及其功能。表6-12数据类型转换函数及其功能用CONVERT函数的转换格式选项能以不同的格式显示日期和时间。转换格式是将datetime和smalldatetime数据转换为字符串时所选用的由SQLServer系统提供的转换样式编号，不同的样式编号有不同的输出格式。

【例6-30】CONVERT函数练习。将数字类型数据转换为字符串类型。

DECLARE@cjint

SELECT@cj=result

FROMsc

WHEREsno='200903101105'ANDcno='03208'

PRINT'该学生课程成绩为：'+CONVERT(VARCHAR(2),@cj)

执行结果如图6-23所示。

图6-23CONVERT函数示例

【例6-31】CAST函数练习。

SELECTCAST(12345ASchar(12)),CAST('321.5'ASfloat),CAST(12.345ASbit)

执行结果如图6-24所示。

图6-24CAST函数示例

6.系统函数

系统函数用于获取有关计算机系统、用户、数据库和数据库对象的信息。系统函数可以让用户在得到信息后，使用条件语句，根据返回的信息进行不同的操作。与其他函数一样可以在SELECT语句的SELECT和WHERE子句以及表达式中使用系统函数。表6-13列出了常用系统函数及其功能。表6-13常用系统函数及其功能【例6-32】APP_NAME函数练习。求SQLServer中当前运行的应用程序名称。

DECLARE@currentApplicationvarchar(50)

SET@currentApplication=APP_NAME()

PRINT'当前运行的应用程序名称为：'+@currentApplication

执行结果如图6-25所示。

图6-25APP_NAME函数示例

【例6-33】DATALENGTH函数练习。求student表中saddress字段的实际长度。

SELECTsaddress,DATALENGTH(saddress)长度

FROMstudent

WHEREsaddress!=''

执行结果如图6-26所示。

图6-26DATALENGTH函数示例

【例6-34】HOST_NAME函数练习。求主机名称。

SELECTHOST_NAME()

执行结果如图6-27所示。

图6-27HOST_NAME函数示例

【例6-35】ISNULL函数练习。查询sc表中的数据，当result字段值为NULL时，替换为0。

SELECTsno,cno,ISNULL(result,0)

FROMsc

执行结果如图6-28所示。

图6-28ISNULL函数示例

7.元数据函数

元数据函数返回有关数据库和数据库对象的信息，所以元数据函数都具有不确定性。常用元数据函数及其功能如表6-14所示。表6-14常用元数据函数及其功能【例6-36】COL_LENGTH函数练习。求student表中sname字段长度。

SELECTCOL_LENGTH('student','sname')as姓名字段长度

执行结果如图6-29所示。

图6-29COL_LENGTH函数示例

【例6-37】DB_ID函数练习。求SCMS数据库的DB_ID号。

USEmaster

SELECTname,DB_ID(name)ASdb_id

FROMsysdatabases

WHEREname='scms'

执行结果如图6-30所示。

图6-30DB_ID函数示例6.5.2用户自定义函数

SQLServer2005中使用CREATEFUNCTION语句编写用户自定义的函数，以满足特殊需要。用户自定义函数中可以使用0个或多个参数。用户自定义函数可以像系统函数一样在程序中使用，也可以像存储过程一样通过EXECUTE语句来执行。用户自定义函数中存储了一个Transact-SQL例程，可以返回一定的值。

1.创建用户自定义函数

根据函数返回值形式的不同，将用户自定义函数分为标量型、内嵌表值型和多语句表值型三种类型。

1)标量型函数

标量型函数返回一个确定类型的标量值。返回值类型为除了text、ntext、image、cursor，timestampt和table类型外的其他数据类型。函数体定义在BEGIN…END语句内，其中包含了可以返回值的Transact-SQL语句。其语法格式如下：

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

RETURN[scalar_expression]

END参数说明如下：

●

owner_name：指定用户自定义函数的所有者。

●

function_name：指定用户自定义函数的名称。函数名称必须符合标识符的规则。

●

@parameter_name：定义参数的名称，参数前用@标明，参数作用范围是整个函数。

●

scalar_parameter_data_type：指定标量参数的数据类型。

●

scalar_return_data_type：指定标量返回值的数据类型。●

function_option：有两个可选值，即{encryption|schemabinding}。其中，encryption为加密选项，schemabinding为计划绑定选项。

●

scalar_expression：指定标量型用户自定义函数返回的标量值表达式。

●

function_body：指定一系列的Transact_SQL语句，它们决定了函数的返回值。

【例6-38】创建计算长方体体积的函数。

CREATEFUNCTIONTiJi

(@cdecimal(4,1),@kdecimal(4,1),@gdecimal(4,1))

RETURNSdecimal(12,3)

BEGIN

RETURN(@c*@k*@g)

END

SELECTDBO.TiJi(2,3,4)AS体积为--调用函数

执行结果如图6-31所示。

图6-31标量型函数示例

2)内嵌表值型函数

内嵌表值型函数以表的形式返回一个值，即它返回的是一个表。内嵌表值型函数没有由BEGIN…END语句定义的函数体。其返回的表由一个位于RETURN子句中的SELECT语句从数据库中筛选出来。

其语法格式如下：

CREATEFUNCTION[owner_name]function_name

([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,…n]])

RETURNSTABLE

[WITH<function_option>[,…n]]

[AS]

RETURN[(select_statement)]

参数说明如下：

●

TABLE：指定返回值为一个表。

●

select_statement：单个select语句，确定返回的表的数据。

【例6-39】创建返回SCMS数据库中指定班级学生信息的函数。

--创建函数，参数classno表示班级编号

CREATEFUNCTIONclassstu(@classnochar(8))

RETURNSTABLE

AS

RETURN(SELECT*

FROMstudents

WHEREs.classno=@classno)

--调用函数，查找班级编号为09031011班的学生信息

SELECT*

FROMdbo.classstu('09031011')

执行结果如图6-32所示。

图6-32返回09031011班级学生信息

3)多语句表值型函数

多语句表值型函数可以看做标量型和内嵌表值型函数的结合体。返回值是一个表，但它和标量型函数一样有一个用BEGIN...END语句定义的函数体。返回值表中的数据是由函数体中的语句插入的。由此可见，它可以进行多次查询，对数据进行多次筛选与合并，弥补了内嵌表值型函数的不足。

其语法格式如下：

CREATEFUNCTION[owner_name]function_name

([{@parameter_name[AS]scalar_parameter_data_type[=default]}[,…n]])

RETURNS@return_variableTABLE<table_type_definition>

[WITH<function_option>[,…n]]

[AS]

BEGIN

function_body

RETURN

END参数说明如下：

●

<table_type_definition>：表声明包含列定义和列约束(或表约束)。

●

@return_variable：一个table类型的变量，用于存储和累积返回的表中的数据行。

【例6-40】创建一个SCMS数据库中返回成绩高于指定学生成绩的学生信息的函数。

CREATEFUNCTIONscore_higher(@cnochar(5),@snochar(12))

RETURNS@score_higherTABLE(snochar(12),cnochar(5),resultint)

AS

BEGIN

INSERT@score_higher

SELECTsno,cno,result

FROMsc

WHEREsno!=@snoAND

result>(SELECTresult

FROMsc

WHEREsno=@snoANDcno=@cno)

RETURN

END

--调用函数，查找成绩高于学号200903101105的03201课程的学生的信息

SELECT*

FROMdbo.score_higher('03201','200903101105')

2.修改用户自定义函数

使用ALTERFUNCTION语句和SQLServerManagementStudio可以修改用户自定义

函数。

(1)使用ALTERFUNCTION语句修改用户自定义函数。此语句的语法与CREATEFUNCTION相同，因此使用ALTERFUNCTION语句修改用户自定义函数其实相当于重建一个同名的函数。其语法格式参数与创建函数相同。

(2)使用SQLServerManagementStudio修改用户自定义函数。其具体操作步骤如下：

①在“对象资源管理器”窗口中展开“数据库”节点，单击并展开SCMS节点。

②选择并展开“SCMS|可编程性|函数”节点。

③选择并展开要修改的函数类型。如果修改例6-38创建的TiJi函数，则选择“标量值函数”。

④展开“标量值函数”节点，则可以看到例6-38创建的TiJi函数。选择TiJi，点击右键，选择“修改”选项，打开函数脚本，便可修改，如图6-33所示。

图6-33修改用户自定义函数

3.删除用户自定义函数

使用DROPFUNCTION语句和SQLServerManagementStudio可以删除用户自定义

函数。

(1)使用DROPFUNCTION语句删除用户自定义函数。其语法格式如下：

DROPFUNCTION{[owner_name.]function_name}[,...n]

【例6-41】删除用户自定义函数。

DROPFUNCTIONTiJi

(2)使用SQLServerManagementStudio删除用户自定义函数。其具体操作步骤如下：

①在“对象资源管理器”窗口中展开“数据库”节点，单击并展开SCMS节点。

②选择并展开“SCMS|可编程性|函数”节点。

③选择要删除的函数，点击右键，选择“删除”。

④在弹出的“删除对象”对话框中单击“确定”按钮，即可将函数删除。

在数据库系统开发过程中会经常应用很多编程技术。例如：查询数据库的当前使用状态；根据用户要求显示相关信息；根据学生的成绩情况分类别显示学生成绩等级，等等。下面举例介绍编程技术的应用。

【例6-42】查询主机标识号、主机名、SCMS数据库的标识号、student表的标识号、当前用户标识号和当前用户名称等信息。6.6编程技术应用分析：可以通过系统函数来查询。

SELECTHOST_ID()AS主机标识号,HOST_NAME()AS主机名,DB_ID('SCMS')AS数据库标识号,OBJECT_ID('student')AS学生表标识号,USER_ID()AS当前用户标识号,USER_NAME()AS当前用户名

执行结果如图6-34所示。

图6-34显示主机、数据库信息

【例6-43】输出字符串“Hello”中每一个字符的ASCII值和字符。

分析：可以通过SUBSTRING函数读出每个字符。字符的ASCII值由ASCII函数实现。

DECLARE@positionint,@