十三、SQLSERVER编程

1、DECLARE,SET,PRINT 语句

1)、局部变量 和 全局变量

局部变量（local variable，以 @为变量名称开头）

(1)局部变量定义

语法：DECLARE @VariableName Data_Type
示例：

declare @name varchar(50)

DECLARE @TempTable TABLE([ID] INT)

(2)局部变量赋值

赋值格式一：

语法：SET 变量名 = 表达式

示例：SET  @name = 'micro'

赋值格式二：
语法：SELECT  变量名 = 表达式或 SELECT子句
示例：SELECT   @name = 'micro'

全局变量（global variable,@@为名称头）

     

2)、DECLARE语句

DECLARE语句是数据声明语句，主要作用是声明局部变量、函数、存储过程或游标变量等；

语法： DECLARE { { @local_variable [AS] data_type } | [= value] } [,...n]

示例：

?

DECLARE @username VARCHAR(20)='micro'

　　

3)、SET语句

SET语句为数据赋值语句，可以对局部变量进行赋值

语法：SET { @local_variable = value | expression }

示例：

?

DECLARE @username VARCHAR(20)

SET @username = 'micro'

　　

4)、PRINT语句

PRINT语句为数据输出语句，可以向客户端返回用户定义的信息

语法：PRINT msg_str | @local_varible | string_expr

示例：

?

1 2 3

DECLARE @username VARCHAR(20) SELECT @username = 'micro' PRINT @username

　　

2、定义语句块 BEGIN...END

示例：

?

If Exists(SELECT * from course where cno='2')

    Begin

        Delete course where cno='2'

        Print '课程号为2的课程已删除!'

    End

Else

    Print '课程号为2的课程不存在!'

　　

3、循环 WHILE、BREAK、CONTINUE

示例：

?

Declare @i int, @sum int

Set @i=0

set @sum=0

While @i>=0

   Begin

      set @i=@i+1

      if @i<=100

         if (@i%2)=0

            Continue

         else

            set @sum=@sum+@i

      else

         Begin

           print '1到100之間的奇數和＝'+str(@sum)

           break

         End

    End

　　

4、条件语句 IF…ELSE

示例：

?

Select @var=(select sum(salary) from employees)

IF @var>10000

    If @var<20000

        Print '工资总和在10000到20000之间'

    else

        print '工资总和大于20000'

Else

    print '工资总和小于10000'

　　

5、条件分支 case when then

示例：

?

CASE sex

         WHEN '1' THEN '男'

         WHEN '2' THEN '女'

ELSE '其他' END

　　

6、批处理结束 GO

示例：

?

USE [databaseName]

GO

　　

7、返回值 out | return

示例：

?

declare @count int

declare @query nvarchar(max)

set @query = 'select @count = COUNT(*) from LoadTest2010.dbo.LoadTestReport'

EXEC sp_executesql @query,

                   N'@count int OUTPUT',

                   @count OUTPUT

select @count

　　

8、AS的用法

(1) 定义变量类型

示例：

?

DECLARE @UserName AS VARCHAR(50)='micro'

　　

(2)取别名

示例：

?

1	SELECT OrderID AS '订单ID',CustID AS '顾客ID',OrderDate AS '订单日期' FROM Orders

　　

(3)转换类型

?

SELECT CAST('110' AS INT)  --110

　　

(4)创建视图，表示基表来源

?

CREATE VIEW ViewDemo

AS

SELECT * FROM Orders

　　

(5)WITH AS

WITH AS短语，也叫做子查询部分，可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。

语法

?

1 2 3 4 5

[ WITH <common_table_expression> [ ,n ] ] <common_table_expression>::= expression_name [ ( column_name [ ,n ] ) ] AS ( CTE_query_definition )

 示例

?

1 2 3

with cte1 as (select * from table1 where name like 'a%'), cte2 as(select * from table2 where id > 1)<br>select a.* from cte1 a, cte2 b

 

9、使用数据库 USE tableName

 示例：

?

1	USE [databaseName]

　　

10、SET ANSI_NULLS {ON | OFF}

指定在对空值使用等于 (=) 和不等于 (<>) 比较运算符时，这些运算符的 SQL-92 遵从行为。

 

11、SET QUOTED_IDENTIFIER ON

表示使用  引用标识符，标识符可以用双引号分隔，但是，文字必须用单引号分隔。

 

12、SET NOCOUNT { ON | OFF }

使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。

 

12、等待 Waitfor

?

waitfor time '23:08:00' --等到晚上11点零8分后才执行select语句

select * from employee

　　

13、事务

?

1 2 3 4 5 6 7 8 9 10 11

BEGIN TRANSACTION   T-SQL   IF @@ERROR <> 0 BEGIN    ROLLBACK TRANSACTION --回滚    RETURN END   COMMIT TRANSACTION

示例：

?

1 2 3 4 5 6 7 8 9

Begin Try     Begin TransAction         Insert Into Tb1(Id,Name)Values('1','Name1')         Insert Into Tb2(Id,Name)Values('2','Name2')     Commit TransAction End Try Begin Catch     Rollback TransAction End Catch

 　　

14、@@ROWCOUNT

@@ROWCOUNT返回受上一语句影响的行数，返回值类型为 int 整型

示例：

?

1 2 3 4 5

INSERT INTO @temp select id  from tb WHILE @@rowcount > 0 BEGIN T-SQL END

 14、@@ERROR

@@ERROR返回上一语句的错误，整数 0为正常

示例：

?

1 2 3 4 5 6 7

EXEC uspProcedure  @Name IF @@ERROR <> 0 BEGIN     --事务处理回滚     ROLLBACK TRANSACTION; RETURN END

 15、RAISERROR

RAISERROR的作用就和asp.NET中的throw new Exception一样，用于抛出一个异常或错误

语法：

?

1 2 3 4

RAISERROR ( { msg_id | msg_str | @local_variable }  { ,severity ,state }  [ ,argument [ ,...n ] ] )  [ WITH option [ ,...n ] ]

 示例：

?

1 2 3 4 5

IF EXISTS(SELECT 1 FROM [dbo].[TB1] WHERE Name = @Name) BEGIN         RAISERROR('Messages.RecordExists', 12, 1)         RETURN END