第 8 章 T-SQL 编程基础

本章学习目标 掌握用户自定义数据类型的定义方法 了解并掌握 T-SQL 语言中创建默认和规则的方法 掌握变量的类型和局部变量的定义与使用 掌握常用的系统函数、系统存储过程，会自定义函数

掌握各种流程控制语句的用法

一、 T-SQL 基础知识 T-SQL 是 Microsoft 公司设计开发的一种结构化查询

语言（ Structure Query Language ， SQL ），它在关系

数据库管理系统（ Rational Database Management System ， RDBMS ）中实现数据的检索、操纵和添加功能。严格意义上说 T-SQL 并不是一种编程语言，而是结构化查询语言。

1 、用户自定义数据类型 添加用户自定义数据类型其基本语法格式如下：sp_addtype [@typename=]type, [@phystype=]system_data_type [,[@nulltype=]'null_type']参数说明：

@typename ：用户自定义的数据类型的名称。[@phystype=]system_data_type ：自定义类型所依 据的系统提供的数据类型。[[@nulltype=]‘null_type’] ：自定义数据类型处 理空值的方式

• 使用 T-SQL 语句创建用户自定义数据类型

Use CJGL

Go

Exec sp_addtype usernametype,'varchar(20)','not null'

Exec sp_addtype userpasswordtype,'varchar(20)','not null'

Exec sp_addtype userclasstype,'char(1)','not null'

创建名为 users 的数据表，包含三个字段， username， userpassword， usertype ，各字段的类型分别为刚创建的三个类型。

Use CJGL Go create table users (username usernametype, userpassword userpasswordtype, usertype userclasstype)

• 使用“对象资源管理器”创建用户自定义数据类型 用此方法创建自定义数据类型的主要截图如下。

删除用户自定义数据类型 删除用户自定义数据类型时要注意当该自定义类型

在使用时不能删除，必须要先停止使用，才能执行删除的操作。

删除用户自定义数据类型的基本语法为： sp_droptype [ @typename = ] 'type‘

如： use CJGL go exec sp_droptype usernametype

2 、规则和默认 规则（ RULE ） 规则是一种约束，用于执行一些与 CHECK 约束相同

的功能，但要注意规则和 CHECK 约束的区别：（ 1）使用 CHECK 约束是限制列取值的首选方案，

CHECK 约束比规则更简明。（ 2）一个列只能应用一个规则，但可以应用多个

CHECK 约束。（ 3） CHECK 约束被制定为“ CREATE TABLE” 语句的一部

分，而规则是作为一个单独的对象来创建，并由系统存储过程绑定到列上。

创建规则的基本语法为：CREATE RULE rule_nameAS condition_expression

参数说明：rule_name ：规则的名称。condition_expression ：定义规则的条件，条

件 里应该使用一个变量加以判断和传递。

绑定规则使用的是系统存储过程：sp_bindrule ‘rule_name‘, ‘[db.]table_name.column_name‘

取消绑定规则的系统存储过程为：sp_unbindrule ‘[db.]table_name.column_name’

删除规则的基本语法为：DROP RULE rule_name

【例 8.4 】为例 8.1 创建的 Users 表的 UserPassword列上加上一个约束规则：要求密码的长度不能少于 8位。

（ 1）在“新建查询”窗口中输入以下代码并执行，用以创

建规则：Use CJGLGoCREATE RULE rule_PasswordAsLen(@password)>=8

（ 2）将此规则绑定到 Users 表的 UserPassword 列上。输

入以下代码并执行： Use CJGL Go Exec sp_bindrule 'rule_Password','Users.UserPassword'

（ 3）测试规则 向 Users 表插入两条记录，其中一条记录中

UserPassword 值的长度小于 8位，一条记录的UserPassword 值大于 8位。 Use CJGL Go insert into users values('ssssss','123456','a') go insert into users values('admin','12345678','a') go

删除上例中创建的 rule_password （ 1）取消绑定规则 rule_password 。 Use CJGL

Go

Exec sp_unbindrule 'Users.UserPassword' （ 2）删除规则。 Use CJGL

Go

drop rule rule_password

默认（ DEFAULT ） 默认就是用户在创建表或向表中添加记录时，对

某些列预输入的默认值，比如在 CJGL 数据库中的Score 表中的某一门课程的成绩，在没有成绩时，系统的默认值是 null 。

创建默认的基本语法为：CREATE DEFAULT [db.]default_nameAS constant_expression

参数说明：db ：数据库的名称。default_name ：创建的默认的名称。constant_expression ：默认的表达式。这里

只包 含常量值的表达式，不能使用用户自定义 函数。

绑定默认的系统存储过程为：sp_bindefault'default_name','[db.]table_name.column_name'

取消绑定默认的系统存储过程为：sp_undbindefault ' [db.]table_name.column_name'

删除默认的基本语法为：DROP DEFAULT default_name

3 、变量 SQL Server 2005中的变量有两种类型：

• 用户自定义的变量（局部变量）• 系统提供的变量（全局变量）

• 全局变量 全局变量是 SQL Server 2005系统内部使用的变

量，其作用范围是全局的，不局限于某一程序，用户不能定义全局变量。全局变量以 @@前缀开头，使用时，不必进行声明。

SQL Server 2005中的全局变量被当成了系统函数进行维护，其存放的位置和系统函数在一起。

下面通过一个例子看如何使用全局变量【例 8.6】要对 Student 表中的 StuNo 为 2009001 的学生的 StuNo改为 20090011111 ，用全局变量 @@ERROR 检

查某列的取值是否超过范围。题目分析：当字段长度超过规定长度，系统的错误代码为 8152；

当取值违反了约束，系统的错误代码 547 ；……。全局变量 @@ERROR 的返回值是一个 integer 类型。

在“新建查询”窗口中输入以下代码： use CJGL go update Student set StuNo='20090011111' where StuNo='2009001' if @@ERROR =8152 print '新值的长度超过了定义类型的取值范围 '

• 局部变量 局部变量是一个有特定数据类型的对象，它的作

用范围仅限于程序内部。局部变量前应加上 @符号，必须要先定义才能引用，定义局本部变量使用的关键字是 DECLARE 。

定义局部变量的基本语法如下： DECLARE @variable_name datatype[,@variable_name datatype…]

在 T-SQL 语句中，对局部变量赋值时，不能和其他程序设计语言一样直接对其赋值，必须使用 SET或SELECT命令来设置变量的值，其基本语法如下：

SET @variable_name=value 或

SELECT @variable_name=value

【例】对两个变量赋值，并且求它们相加的和。在“新建查询”窗口中输入以下代码，并执行：

declare @a int,@b int, @sum intset @a=2 --这里也可以使用 select赋值set @b=5set @sum=@a+@bprint @sum

二、函数 Server 2005中的函数分为两种类型：一种是系

统提供的内部函数（系统函数）；另一种是用户自定义函数。

1 、常用系统函数 利用系统函数，可以帮助用户获取系统的相关信

息、执行相关计算、实现数据转换，以及统计功能等，SQL Server 2005提供的系统函数包括聚合函数、配置函数、日期函数、数学函数、字符串函数等，如下图所示。

SQL Server 2005 中的系统函数

• 聚合函数

函数名称 描述Avg() 返回组中各值的平均值Count() 返回组中的项数Max() 返回表达式的最大值Min 返回表达式的最小值Stdev() 返回给定表达式中所有值的偏差Sum() 返回表达式中所有值的和

• 日期和时间函数

函数名称 说明Dateadd(datepart,日期 ) 返回 datepart加上日期产生的新日期Datediff(datepart,日期1,日期 2)

返回两个日期的差值，转化为指定 datepar的形式

Datename(datepart,日期 )

返回表示指定日期的指定日期部分字符串

Day(日期 ) “ ”返回一个整数，表示日期中 日 的部分Month(日期 ) “ ”返回一个整数，表示日期中 月 的部分Year(日期 ) “ ”返回一个整数，表示日期中 年 的部分Getdate() 返回服务器当前的系统日期和时间

分析输出结果：print Datediff(dd,Getdate(),'01/01/2012')

• 数学函数

• 字符串函数

【例 8.11 】向 Student 表中插入一条记录，检查当 StuNo

的长度大于 8时，提示不允许插入的信息，否则允许插入，并提示插入成功的信息。题目分析： 本例中使用了两个系统函数，一个是 len() ，一个是

convert() 。 len() 函数计算字符长度；而 convert() 函数则是将数据类型进行转换，因为这里的 len() 函数返回的是一个整型数据，而单引号里的是字符串类型，不能进行连接（ +）运算，所以需要将整型数据转换成字符型数据，再进行连接运算。如下：

use CJGLgodeclare @ls nvarchar(20)set @ls='0123'if len(@ls)<=8

begininsert into Studentvalues(@ls, '李欢 ','男 ','网游 1班 ','成都 ','班

长 ','1988-3-5')print '此记录插入成功 '

endelseprint 'StunNo的长度为 '+convert(nvarchar,len(@ls))

+'位 ,超过了 8位 '

2 、自定义函数 在 SQL Server 2005中，用户自定义函数有两种，

分别是标量函数和表值函数。

标量函数创建标量函数的基本语法为：CREATE FUNCTION [owner_name.] function_name([{@parameter_name[AS]parameter_data_type[=def

ault]}][,…n])RERURNS return_data_type

[WITH<function_option>[,…n]][AS]BEGIN

function_bodyRETURN scalar_expression

END

参数说明：function_name ：用户自定义的函数名称@parameter_name ：用户自定义函数的参数，可以声

明一个或多个参数parameter_data_type ：参数的类型[=default] ：参数的默认值return_data_type ：用户自定义标量函数的返回值，

不能是 ext 、 ntext 、 image 、 cuisor和 timestamp 据类型function_body ：指定一系列定义函数值的 T-SQL 语

句，这些语句一起使用的计算结果为标量值scalar_expression ：指定标量函数返回的标量值

【例 8.12 】求某一科目的平均成绩。 题目分析： 求平均成绩，系统提供了平均值函数 Avg() ，但这里的科目名称未定， Where子句里应该是一个待定条件。这里就应该建立一个用户自定义标量函数方便用户求解。

use CJGL go create function avggrade(@kechengming nvarchar(20)) returns float as begin declare @pingjunfen float set @pingjunfen=(select avg(grade) from score where couno=(select couno from course where couname=@kechengming)) return @pingjunfen end

（ 1）创建自定义标量函数

（ 2）调用此函数 use cjgl

go

select dbo.avgGrade('高等数学 ') as ' ‘高等数学(3) 删除用户自定义函数use cjgl

go

Drop function avggrade

3 、常用系统存储过程 系统存储过程是一种特殊类型的系统函数，系统存

储过程主要存储在 master 数据库中，并以 sp_ 为前缀，在使用时用 EXEC调用。

如： EXEC sp_helptext avgGrade

三、批处理和流程控制语句1 、语句注释

注释语句为分：• 单行注释语句 “ --”• 多行注释语句 /*...*/

2 、批处理 批处理是包含一个或多个 T-SQL 语句的组，从应用

程序一次性地发送到 SQL Server 中执行。 SQL Server将

批处理语句编译成一个可执行的单元，此单元称为执行计划，执行计划中的语句每次执行一条。

一些 SQL 语句不可以放在一个批处理语句中处理，它们需遵守以下规则：大多数 CREATE命令要在单个命令

中执行，但除 CREATE DATABASE 、 CREATE TABLE 、CREATE INDEX例外。

T-SQL 语句中的批处理其实很简单，就是用 GO命令来通知 SQL Server 和 T-SQL 语句的结束。

【例8.14】在同一个查询窗口中查找 Student表中所有姓王的学生信息和在Score表中查找所有成绩大于 60分的信息，并将查询结果存放在名为 VV的视图中。

使用的命令如下： use cjgl go create view vv as select * from student where stuname like '王%' go select * from score where grade>='60' go

本例说明： 原本 create 语句应该是批处理中的第一条语句，但

这里为了指明在 CJGL 数据库下创建视图，在前面先使用

了 use CJGL ，所以应该用 go 语句将其分开。如果将 use

CJGL 和语句 create view vv 之间的 go 语句去掉，会有什

么结果呢？

3 、流程控制语句 IF…ELSE 语句 IF…ELSE 语句的基本格式为：

IF<条件表达式 > < 命令或程序块 >[ELSE[ 条件表达式 ] < 命令行或程序块 >]

BEGIN…END 语句BEGIN…END 语句的基本格式为：

BEGIN < 命令行或程序块 >END

CASE 语句CASE 语句的基本格式为： CASE <表达式或字段名>

WHEN<逻辑表达式1> THEN<结果表达式1> … WHEN< 逻辑表达式n> THEN<结果表达式n

> [ELSE <结果表达式>]END

例 8.15

WHILE…CONTINUE…BREAK 语句 WHILE…CONTINUE…BREAK 的基本格式为：

WHILE<条件表达式 >BEGIN< 命令行或程序块 >[BREAK][CONTINUE][命令行或程序块 ]

END例 8.16

四、本章小结 本章主要介绍了 T-SQL 的语言基础，通过示例介绍了全局变量、用户自定义变量、系统函数、用户自定义函数的用法，特别讲解了用户自定义数据类型的创建和应用，并在此基础上通过示例说明了如何创建规则和默认并绑定到相关数据表上，最后具体介绍了流程控制语句的用法。