28、SQL Server T-SQL编程

T-SQL编程

Transact-SQL（事务-结构化查询语言）

 由一条或多条T-SQL语句组成。

一、T-SQL基础

1、结束批处理

默认情况下，批处理分隔关键字为go

注：go 必须独占一行，不可包括其他任何字符 （包括注释）

不过也可在选项中自定义批处理分隔符，结束批处理时，将销毁该批处理的所有局部

变量，临时表和游标

2、切换数据库

use 数据库名

3、执行批处理

F5

4、执行存储过程

Exec 存储过程名 参数

二、格式化T-SQL代码

1、结束语句

ANSI SQL标准规定在每个命令末尾使用分号来结束它，但分号是可选的。

但有一些硬性规则

 a、try and 后不可加分号

 b、if条件后不可加分号

 c、CTE之前必须添加分号

2、续行

可以使用换行、空格

3、注释

单行：--

多行：/*   */ 使用这种注释的特点：可选择注释中的SQL语句执行，测试。

而无需首先取消注释。

4、调试T-SQL

使用select print 

waitfor  delay  ‘00:00:02’暂停2秒执行

三、变量

T-SQL变量是使用命令declare创建的

：

declare 变量名1 数据类型，变量名2 数据类型

注：可用的数据类型包括创建表时可用的数据类型以及数据类型table和SQLVariant。

1、变量的默认值和作用域

变量的作用域（可用范围和生命周期）为当前批处理。

新声明的变量的默认值为null，在使用前需初始化。

go用来分隔不同的批处理。

2、变量的赋值

使用set命令和select命令

set命令和select命令都可以将表达式的值赋给变量

主要区别：

select可以从数据源（如表，子查询或视图）检索数据，

set只能获取表达式的值。

注：如果select语句检索多行，那么只有最后一行的数据将被赋给变量，而不会

导致错误。

如：

declare @tempid int,@name varchar(20)set @tempid=20select @name=truename from yuangong where id=3select @name,@tempid

注：如果select 语句没有返回任何行，变量的值将不会受影响。

3、在SQL查询中，使用变量

在SQL查询中，可以使用表达式的地方都可以使用变量。

如：

declare @tempid intset @tempid=1select * from yuangong where id=@tempid

四、流程控制

1、if条件语句

格式：

if condintion

statement;

注：只可控制单个命令（一条语句）

1.1 begin/end

相当于程序中的 {  }

可以让多条命令在if的控制下执行。

格式：

if condition

begin

multiple lines;

end

1.2 if Exists()

在括号中使用select语句查询时，选择特定列，还是全部列，没有本质的不同

，但选择全部列时SQL Server能够根据索引选择最佳的列。

1.3if  else

if condition

...

else

...

2、while

while condition

begin

...

end

中间可使用break和continue

3、goto

label1 :

...

   goto label1

五、使用代码获取SQL Server信息

1、sp_help

sp_help及其变体返回有关服务器、数据库、对象、连接等方面的信息

如：

sp_help 表名

返回表的结构等具体信息：

如:

sp_help yuangong

结果：

Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation

id int no 4 10    0     no (n/a) (n/a) NULL

bmID int no 4 10    0     no (n/a) (n/a) NULL

zwID int no 4 10    0     no (n/a) (n/a) NULL

username varchar no 50             no no no Chinese_PRC_CI_AS

pwd varchar no 50             no no no Chinese_PRC_CI_AS

truename varchar no 50             yes no yes Chinese_PRC_CI_AS

sex varchar no 10             yes no yes Chinese_PRC_CI_AS

birthday datetime no 8             yes (n/a) (n/a) NULL

tele varchar no 50             yes no yes Chinese_PRC_CI_AS

note text no 16             yes (n/a) (n/a) Chinese_PRC_CI_AS

rzsj datetime no 8             yes (n/a) (n/a) NULL

lzsj datetime no 8             yes (n/a) (n/a) NULL

isdelete int no 4 10    0     yes (n/a) (n/a) NULL

2、全局变量

以@@打头

全局变量
全局变量是SQL Server系统内部使用的变量，其作用范围并不仅仅局限于某一程序，而是任何程序均可以随时调用。全局变量通常存储一些SQL Server的配置设定值和统计数据。用户可以在程序中用全局变量来测试系统的设定值或者是Transact-SQL命令执行后的状态值。

使用全局变量时应该注意以下几点：
①全局变量不是由用户的程序定义的，它们是在服务器级定义的。
②用户只能使用预先定义的全局变量。
③引用全局变量时，必须以标记符“@@”开头。
④局部变量的名称不能与全局变量的名称相同，否则会在应用程序中出现不可预测的结果。

select @@ERROR 返回最后执行的 Transact-SQL 语句的错误代码(integer) （0，表示

没错误；1，表示有错误）

select APP_NAME ( ) as w --当前会话的应用程序 

select @@IDENTITY   --返回最后插入的标识值 
select USER_NAME()    --返回用户数据库用户名 

SELECT @@CONNECTIONS  --返回自上次SQL启动以来连接或试图连接的次数。 
SELECT GETDATE() --当前时间 
SELECT @@CPU_BUSY/100  --返回自上次启动SQL 以来 CPU 的工作时间，单位为毫

秒

USE tempdb SELECT @@DBTS  as w  --为当前数据库返回当前 timestamp 数据类型

的值。这一 timestamp 值保证在数据库中是唯一的。 
select @@IDENTITY as w --返回最后插入的标识值 
SELECT @@IDLE  as w  --返回SQL自上次启动后闲置的时间，单位为毫秒 
SELECT @@IO_BUSY AS w   --返回SQL自上次启动后用于执行输入和输出操作的时间，

单位为毫秒 
SELECT @@LANGID AS w   --返回当前所使用语言的本地语言标识符(ID)。 
SELECT @@LANGUAGE AS w   --返回当前使用的语言名 
SELECT @@LOCK_TIMEOUT as w  --当前会话的当前锁超时设置，单位为毫秒。 
SELECT @@MAX_CONNECTIONS  as w  --返回SQL上允许的同时用户连接的最大数。

返回的数不必为当前配置的数值 
EXEC sp_configure  --显示当前服务器的全局配置设置 
SELECT @@MAX_PRECISION as w --返回 decimal 和 numeric 数据类型所用的精度

级别，即该服务器中当前设置的精度。默认最大精度38。 
select @@OPTIONS  as w  --返回当前 SET 选项的信息。 
SELECT @@PACK_RECEIVED as w  --返回SQL自启动后从网络上读取的输入数据包数

目。 
SELECT @@PACK_SENT as w  --返回SQ自上次启动后写到网络上的输出数据包数目。 

SELECT @@PACKET_ERRORS as w  --返回自SQL启动后，在SQL连接上发生的网络数

据包错误数。 
SELECT @@SERVERNAME as w --返回运行SQL服务器名称。 
SELECT @@SERVICENAME  as w --返回SQL正在其下运行的注册表键名 
SELECT @@TIMETICKS  as w --返回SQL服务器一刻度的微秒数 
SELECT @@TOTAL_ERRORS AS w  --返回 SQL服务器自启动后，所遇到的磁盘读/写错

误数。 
SELECT @@TOTAL_READ  as w  --返回 SQL服务器自启动后读取磁盘的次数。 
SELECT @@TOTAL_WRITE as w  --返回SQL服务器自启动后写入磁盘的次数。 
SELECT @@TRANCOUNT  as w  --返回当前连接的活动事务数。 
SELECT @@VERSION as w  --返回SQL服务器安装的日期、版本和处理器类型。 

六、临时表和表变量

1、局部临时表

以#开头

Create table #名称

(

数据列定义

)

注：临时表的生命周期很短，只在相应的创建中可见（在同一个新建查询中可见）

2、全局临时表

所有用户都可以引用全局临时表，仅当最后一个引用它的会话结束后才删除

以##开头，在别的新建查询中也可见，只要创建的那个一会话结束，就不可

再访问。

3、表变量

表变量的作用域和生命周期与局部变量相同，

对表变量有如下限制

a、不能使用语法select * into 或 insert into @tablename exec 来创建表变量

b、不能在函数中创建表变量

c、不可使用外键和check约束

d、不可有依据表变量的对象，如：触发器和外键作为变量来声明。

格式：

declare @表名 table

(

列定义

)

在同一个批处理中可对这个表变量操作

六、动态SQL

七、错误处理

7.1try ... catch

begin try

...放正常执行的代码

end try

begin catch

...如果发生错误执行这里的代码。

end  catch

错误函数：

error_message() 错误消息文本

error_number() 错误编号

error_procedure() 发生错误的存储过程或解发器的名称

error_severity() 错误的严重程度

error_state() 错误的状态