TRANSACTSQL编程应用.docx

TRANSACTSQL编程应用.docx

《TRANSACTSQL编程应用.docx》由会员分享，可在线阅读，更多相关《TRANSACTSQL编程应用.docx（52页珍藏版）》请在冰豆网上搜索。

TRANSACTSQL编程应用

SQLServer2000Transact-SQL

编程和应用

Transact-SQL（简记为T-SQL）是微软公司在数据库管理系统SQLServer上的SQL扩展，利用Transact-SQL不仅可以完成数据库上的各种操作，而且可以很容易地编制复杂的例行程序。

本章介绍T-SQL编程的基本知识、各种语句的语法及其应用、利用T-SQL创建其他数据对象（数据类型、存储过程、触发器等）以及这些数据对象的应用等内容。

7．1SQLServer2000Transact-SQL编程

Transact-SQL语言的主要特点如下：

（1）是一种交互式查询语言，功能强大，简单易学；

（2）既可以直接查询数据库，也可以嵌入到其它高级语言中执行；

（3）非过程化程度高，语句的操作执行由系统自动完成；

（4）所有的Transact-SQL命令都可以在查询分析器中完成。

Transact-SQL不仅支持所有的SQL语句，而且还提供了丰富的编程功能，允许使用变量、运算符、函数、流程控制语句等。

7.1.1用户定义的数据类型

第5章介绍了SQLServer提供的各种数据类型，这些数据类型虽然比较丰富，但由于实际应用情况的多样性，有时仍不能满足实际需求。

因此，SQLServer提供了用户自定义数据类型的功能（这里的用户是指数据库设计者）。

用户定义的数据类型可以实现以下功能：

·可以让不同表中重复出现的各列具有相同的特性，使相似的数据种类标准化。

·可以将规则和默认值捆绑到用户定义的数据类型上，以约束使用此数据类型的每个列。

用户定义的数据类型是在SQLServer的系统数据类型的基础上创建的。

用户定义的数据类型与系统数据类型一样，都是用来限制操作者输入数据的种类和长度，它可以用于使用系统数据类型的任何地方。

如果在model数据库中创建了用户自定义的数据类型，则它将出现在所有以后新建的数据库中。

但是定义在用户数据库中的用户自定义数据类型，则只会出现在定义它的数据库中。

用户定义的数据类型是与表、视图等并列的数据库对象，可以对它执行创建、修改、删除等操作。

1.创建用户自定义数据类型

可以使用企业管理器或T-SQL语句创建用户自定义数据类型。

●●       使用企业管理器创建用户自定义数据类型

步骤如下：

（1）

（1）在企业管理器中展开服务器组、服务器。

（2）

（2）展开【数据库】，选择要创建用户自定义数据类型的数据库。

右击【用户定义的数据类型】目录，在弹出的快捷菜单中选择【新建用户定义数据类型】命令。

（3）（3）打开的新建用户自定义数据类型对话框如图7-1所示。

在对话框的【名称】栏中输入用户自定义数据类型的名称，如Tel_No。

图7-1新建用户自定义数据类型对话框

（4）（4）在对话框的【数据类型】下拉列表框中，选择该用户自定义数据类型所基于的系统数据类型（此例选择varchar）。

（5）（5）如果选择的基类型是可以设定长度的（如，varchar、char等），则还须要在【长度】栏中设定数据类型的长度。

（6）（6）如果允许为空值，则选中【允许NULL值】复选框。

（7）（7）如果希望该数据类型与规则或默认值捆绑，则分别在【规则】和【默认值】下拉列表框中选择要捆绑的规则和默认值，否则选择“无”。

（8）（8）单击【确定】按钮，完成。

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

该方法通过调用系统存储过程sp_addtype实现，具体语法为：

sp_addtypetype[，systemdata_type][，‘null_type’]

其中，type：

为用户定义的数据类型名，这个名称在数据库中必须是惟一的。

systemdata_type：

为用户定义的数据类型所基于的系统数据类型，可以包括数据的长度、精度等。

当系统数据类型中包括标点符号（例如括号、逗号）时，应用引号括起来。

null_type：

指定该数据类型能否接受空值。

其值可以为‘NULL’、‘NOTNULL’或‘NONULL’。

例如，创建一个如图7-1所示的电话号码数据类型，可使用如下命令：

EXECsp_addtypeTel_No,'varchar（8）','NULL'

在查询分析器中执行上述语句，结果窗口显示：

（所影响的行数为1行）

类型已添加

2.查看用户自定义数据类型

可以使用sp_help系统存储过程来查看用户自定义数据类型的信息，包括它基于的系统数据类型、长度、精度、是否允许空值，以及在这一数据类型上捆绑的规则和默认值等。

使用查询分析器查看用户自定义数据类型Tel_NO，如图7-2所示。

图7-2查看用户自定义数据类型

3.删除用户自定义数据类型

可以使用T-SQL语句或企业管理器来删除用户自定义数据类型。

●●       使用T-SQL语句删除

使用系统存储过程sp_droptype来删除用户自定义数据类型。

语法格式为：

sp_droptypetype_name

其中，type_name为要删除的用户自定义数据类型名。

正被表或数据库使用的用户自定义数据类型不能被删除。

●●       使用企业管理器删除

使用企业管理器删除用户定义的数据类型的步骤如下：

（1）在企业管理器中展开服务器组、服务器。

图7-3删除用户定义数据类型对话框图7—4删除警告对话框

（2）展开【数据库】，展开用户自定义数据类型所在的数据库，选中【用户定义的数据类型】，右边的窗口中将显示数据库中所有用户自定义数据类型对象，右击要删除的用户自定义数据类型，在快捷菜单中选择【删除】命令。

（3）接着打开如图7—3所示的删除对话框。

（4）单击图7—3示中的【全部除去】按钮。

如果该数据类型正在被某些表使用，则会显示如图7—4所示的对话框，并撤销删除操作；否则，数据类型被删除。

7.1.2变量

任何一种编程语言中，变量都是不可缺少的，Transact-SQL也不例外。

Transact-SQL中的变量分为局部变量与全局变量。

下面先介绍Transact-SQL编程中批和脚本的概念，然后介绍变量。

1.1.   批和脚本

（1）批

一个批是由一条或多条T-SQL语句组成的语句集，这些语句一起提交并作为一个组来执行。

SQLServer将批中的语句作为一个整体编译为一个执行计划。

因为批中的语句是一起提交给服务器的，所以可以节省系统开销。

在查询分析器中，可以用GO命令标志一个批的结束。

GO不是一个执行语句，是通知查询分析器有多少语句要包含在当前的批中。

查询分析器将两个GO之间的语句组成一个字符串交给服务器去执行。

如，以下代码包含三个批：

USEMyDB

GO/*1批未*/

CREATEVIEWV_STUDENTS

AS

SELECT编号,姓名

FROMReaders

WHERE读者类型=’学生’

GO/*2批未*/

SELECT*

FROMV_STUDENTS

GO/*3批未*/

因为SQLServer为一个批生成一个单独的执行计划，所以一个批本身应该是完整的，不能在一个批中引用其它批定义的变量，也不能将注释从一个批开始，在另一个批中结束。

如果批中的语句出现编译错误，那么将不能生成执行计划，批中的任何一个语句都不会执行。

批的运行时期的错误，多数情况终止当前语句和批中后继语句的执行，少数运行时期错误（比如违反约束），只会影响当前造成错误的语句，而其后的语句仍可以正常执行。

不论是那种运行时期错误，出错之前的语句的执行结果不会受到影响，除非该批位于一个事务中，而且在出现错误之后明确地将事务回滚。

批有如下限制：

·CREATEDEFAULT、CREATEPROCEDURE、CREATERULE、CREATETRIGGER和CREATEVIEW语句不能与其它语句位于同一个批中。

·不能在一个批中修改一个表的结构，然后在同一个批中引用刚修改的新列。

·如果批的第一条语句是EXECUTE,则EXECUTE关键字可以省略。

否则，不能省略。

（2）脚本

脚本是一系列顺序提交的批。

脚本可以直接在查询分析器等工具中输入并执行，也可以保存在文件中，再由查询分析器等工具执行。

一个脚本可以包含一个或多个批，脚本中的GO命令标志一个批的结束，如果一个脚本没有包含任何GO命令，那么它被视为一个批。

脚本可用于：

·将服务器上创建一个数据库的步骤永久地记录在脚本文件中。

·将语句保存为脚本文件，从一台计算机传递到另一台计算机，这样可以方便地使两台计算机执行同样的操作。

2.局部变量

局部变量是用户自定义的变量。

使用范围是定义它的批、存储过程或触发器。

局部变量前面通常加上@标记。

用DECLARE对局部变量进行定义，并指明此变量的数据类型，用SET或SELECT命令对其赋值。

局部变量的数据类型可以是用户自定义的数据类型，也可以是系统数据类型，但不能将其定义为TEXT或IMAGE数据类型。

（1）定义局部变量的语法如下：

DECLARE@local_variabledata_type[,local_variabledata_type]…

可以看出，DECLARE命令可以定义多个局部变量，之间用逗号分隔。

（2）用SELECT为局部变量赋值的语法如下：

SELECT@variable_name=expressionselectstatement

[，@variable-name=expressionselectstatement]

[FROMlistoftables]

[WHEREexpression]

[GROUPBY..．]

[HAVING..．]

[ORDERBY]

说明：

SELECT命令可以将一个表达式的值赋给一个局部变量，也可以将一个SELECT查询的结果赋给一个局部变量。

SELECT命令通常返回一个值给局部变量。

但当返回多个值时也不会出现错误。

例如，expression为一个表列的名字时，SELECT命令可能会返回多个值，则变量的值为最后一个返回值。

如果SELECT命令没有返回值，则局部变量保持原值不变，如expression为—个分级查询且没有返回值时，变量被置为NULL。

【例7-1】数据库MyDb中readers表中的两列内容如下，执行下面脚本。

编号姓名

-------------------------

2004060001王晓奇

2004060002张刚

2004060003李亚茜

2004160426刘超

2002060328王立群

USEMyDb

DECLARE@varlvarchar（8）--声明局部变量

SELECT@var1='读者姓名'--为局部变量赋初值

SELECT@varl=姓名

FROMreaders

WHERE编号='2004160426'

SELECT@varlAS'读者姓名'--显示局部变量结果

执行结果为：

读者姓名

---------------

刘超

此例题中，打开数据库后，定义了—个变量varl为字符串类型，并且赋初值为“读者姓名”；然后SELECT命令从readers表中查询编号为“2004160426”的读者姓名，并将查询结果赋予varl变量。

由于在readers表中符合条件的记录存在且只有一条，所以varl变量的值为查询结果：

刘超。

【例7-2】多个返回值的赋值。

USEMyDb

DECLARE@varlvarchar（