十三、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

示例：

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)取别名

示例：

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语句用到。

语法

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

 示例

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

 

9、使用数据库 USE tableName

 示例：

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、事务

BEGIN TRANSACTION

T-SQL

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

COMMIT TRANSACTION

示例：

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 整型

示例：

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

 14、@@ERROR

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

示例：

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

 15、RAISERROR

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

语法：

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

 示例：

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