T-SQL 编程技巧

Ø  T-SQL 编程是大多数程序员都会接触的，也是数据库编程必须掌握的技术。下面，是本人在工作或学习中积累的一些心得和技巧。主要包含以下内容：

1.   waitfor延时执行

2.   NOT 关键字的使用

3.   向变量赋值的 SELECT 语句，总是按照顺序赋值

4.   SET 与 SELECT 赋值的区别

5.   获取变量的数据类型

6.   一条 SQL 语句将一个字段的数据转为字符串

7.   根据起始和结束字符串，提取子字符串

8.   查询某数据库的所有存储过程中，是否包含指定的字符串

 

1.   waitfor延时执行，类似于定时器、休眠等

1)   延迟执行

WAITFOR DELAY '00:00:03';   --定时三秒后执行

PRINT '定时三秒后执行';

2)   定时执行

WAITFOR TIME '10:43';       --定时到10:43后执行

PRINT '到10:43才执行PRINT';

 

2.   NOT 关键字的使用

NOT 关键字用于将一个布尔值取反，类似于 C# 编程中(!)运算符，参考官方文档。

DECLARE @num1 int=25;

IF(NOT @num1 BETWEEN 50 AND 60)

    PRINT '25不在50~60之间';

结果输出：25不在50~60之间

 

3.   向变量赋值的 SELECT 语句，总是按照顺序赋值

DECLARE @Num1 int;

SELECT @Num1 = 5, @Num1 = @Num1 + 55;

PRINT @Num1;

结果输出：60

 

4.   SET 与 SELECT 赋值的区别

1)   SET 只能赋值一个变量，而 SELECT 可以赋值多个变量。

1.   SQL：

DECLARE @SetId int, @SelectId int, @SelectName varchar(20);

SET @SetId = (SELECT Id FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T);

SELECT @SelectId = Id, @SelectName = Name FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T;

SELECT @SetId AS SetId, @SelectId AS SelectId, @SelectName AS SelectName;

2.   结果：

 

2)   当返回多个值时，SET 不支持并且会报错；而 SELECT 不会，将赋值为最后一个值。

1.   SQL：

DECLARE @SetId int, @SelectId int, @SelectName varchar(20);

--SET @SetId = (SELECT Id FROM (SELECT 1 AS Id, 'zhangsan' AS Name UNION SELECT 2 AS Id, 'lisi' AS Name) AS T);

SELECT @SelectId = Id, @SelectName = Name FROM (SELECT 1 AS Id, 'zhangsan' AS Name UNION SELECT 2 AS Id, 'lisi' AS Name) AS T;

SELECT @SetId AS SetId, @SelectId AS SelectId, @SelectName AS SelectName;

2.   结果：

 

3)   没有记录返回时，SET 赋值为 NULL，而 SELECT 则保持原来的值。

1.   SQL：

DECLARE @SetId int = 10, @SelectId int = 10, @SelectName varchar(20) = 'original value';

SET @SetId = (SELECT Id FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T WHERE 1 <> 1);

SELECT @SelectId = Id, @SelectName = Name FROM (SELECT 1 AS Id, 'zhangsan' AS Name) AS T WHERE 1 <> 1;

SELECT @SetId AS SetId, @SelectId AS SelectId, @SelectName AS SelectName;

2.   结果：

 

5.   获取变量的数据类型

SELECT Sql_Variant_Property(20, 'BaseType') AS Column1, Sql_Variant_Property(0.1, 'BaseType') AS Column2,Sql_Variant_Property(20 / 0.1, 'BaseType') AS Column3;

 

6.   SQL 语句将一个字段的数据转为字符串

DECLARE @Table1 TABLE(Name varchar(20));

INSERT INTO @Table1 VALUES('a'),('b'),('c'),('d'),('e');

DECLARE @Result varchar(max);

SELECT @Result = (SELECT CAST(Name AS varchar(20)) + ',' FROM @Table1 FOR XML PATH('')), @Result = SUBSTRING(@Result, 1, LEN(@Result) - 1);

SELECT @Result AS 字符串;

 

7.   根据起始和结束字符串，提取子字符串，代码如下：

1)   首先，创建一张临时表并写入数据

IF(OBJECT_ID('tempdb.dbo.#Table1', 'U') IS NOT NULL)

    DROP TABLE #Table1;

CREATE TABLE #Table1(Id int, Value varchar(8000));

INSERT INTO #Table1 VALUES(1, '<id>1</id><name>爱变成</name><address>湖北</address>');

2)   检索位于起始字符与结束字符之间的字符串

DECLARE @start varchar(200) = '<name>', @end varchar(200) = '</name>';

SELECT SUBSTRING(Value, CHARINDEX(@start, Value) + LEN(@start), CHARINDEX(@end, Value) - (CHARINDEX(@start, Value) + LEN(@start))) AS Name, * FROM #Table1;

3)   执行结果

 

8.   查询某数据库的所有存储过程中，是否包含指定的字符串

--1. 申明变量

IF(OBJECT_ID('tempdb.dbo.#Temp_Proc', 'U') IS NOT NULL)

    DROP TABLE #Temp_Proc;

GO

CREATE TABLE #Temp_Proc(ProcName varchar(100), SqlText text);

DECLARE @Temp1 TABLE(SqlText text);

DECLARE @ProcName varchar(100);

DECLARE cursor_proc CURSOR FOR SELECT name FROM sys.procedures;

--2. 使用游标遍历所有存储过程

OPEN cursor_proc;

FETCH NEXT FROM cursor_proc INTO @ProcName;

WHILE(@@FETCH_STATUS = 0)

BEGIN

    --先写入表变量

    DELETE @Temp1;  --注意：这里最好不要 DECLARE @Temp1 TABLE(SqlText text); 这样效率极低，原因不详！

    INSERT INTO @Temp1 EXEC sp_helptext @ProcName;

    --再写入临时表

    INSERT INTO #Temp_Proc

    SELECT @ProcName, SqlText FROM @Temp1;

    FETCH NEXT FROM cursor_proc INTO @ProcName;

END

CLOSE cursor_proc;

DEALLOCATE cursor_proc;

--3. 查询临时表

DECLARE @PName varchar(100) = '', @PText varchar(200) = '%QueryText%';

SELECT * FROM #Temp_Proc WHERE 1=1

AND (LEN(@PName) = 0 OR ProcName = @PName)

AND SqlText LIKE @PText;