ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 动态SQL开发基础和经验再总结

动态SQL开发基础和经验再总结

原创 Linux操作系统 作者:iSQlServer 时间:2010-12-07 13:50:24 0 删除 编辑

一、SQL语句直接处理非数字型列的“累加”问题

查询要求:取出Person表中所有人的FirstName,并以逗号隔开。

可直接通过SELECT @local_variable = expression的形式实现:

 
1 DECLARE @Result varchar(8000)
2 SET @Result=''
3 SELECT @Result=@Result + CASE WHEN @Result='' THEN FirstName ELSE ',' + FirstName 
4 END FROM Person ORDER BY Id
5 SELECT @Result AS Names

我们还有一种比较笨拙的方式进行查询,那就是通过游标:

 
01 --使用游标
02 DECLARE @Result varchar(8000)
03 DECLARE @SQL varchar(50)
04 DECLARE c CURSOR FORWARD_ONLY READ_ONLY FOR
05 SELECT FirstName FROM Person ORDER BY Id
06 SET @Result = ''
07 OPEN c
08 FETCH next FROM c INTO @SQL
09 WHILE @@fetch_status = 0
10 BEGIN
11    SET @Result = CASE WHEN @Result='' 
12    THEN @SQL 
13    ELSE @Result + ',' + @SQL 
14    END
15    FETCH next FROM c INTO @SQL
16 END
17 DEALLOCATE c
18 SELECT @Result AS Names

大部分SQL查询我们都会力争不用游标,因为不用考虑分配和释放游标,可大大节省数据库资源提升效率。

 

二、动态SQL语句基础

在一中,我们已经使用了动态SQL。这里再总结一下经常使用的动态SQL编程基础。

1、单引号,双引号

单引号:

 
1 SELECT '''' AS Result --单引号

那么双引号呢?8个单引号么?如果是真的8个单引号一起,实际上返回的是3个单引号。真正的双引号可以”含有“8个单引号,当然必须要像下面这样加起来:

 
1 SELECT ''''+'''' AS Result --双引号

但是更直接的写法是下面这样的:

 
1 SELECT '''''' AS Result --双引号

没错,就是6个单引号连写。

单引号和双引号也可以搞得这么烦?这也许正是SQL编程不如高级程序语言来得简单直白的地方。

2、定义变量,给变量赋值

来看一个简单的SQL语句:

 
1 DECLARE @a varchar(20),@b varchar(20)
2 SET @a='jeff' 
3 SET @b=' wong'
4 PRINT @a+@b

我们通过DECLARE定义变量,通过SET给变量赋值,也可以通过SELECT给变量赋值:

 
1 DECLARE @a varchar(20),@b varchar(20)
2 SELECT @a='jeff' 
3 SELECT @b=' wong'
4 PRINT @a+@b

SET和SELECT赋值的区别:据说SELECT 一次性赋值, 比用SET 逐个赋值效率好。

3、EXEC(@sql)

普通的SQL语句,可以直接通过EXEC执行

 
1 EXEC ('SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC')--两边的括号不可少

也可以通过定义变量,执行变量sql,但是必须加上括号:

 
1 DECLARE @Sql varchar(2000)
2 SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'
3 EXEC ( @Sql )

4、Exec sp_executesql

sp_executesql 支持对 Transact-SQL 字符串中指定的任何参数的参数值进行替换,但是 EXECUTE 语句不支持。因此,由 sp_executesql 生成的 Transact-SQL 字符串比由 EXECUTE 语句所生成的更相似。SQL Server 查询优化器可能将来自 sp_executesql 的 Transact-SQL 语句与以前所执行的语句的执行计划相匹配,以节约编译新的执行计划的开销。

对于普通的SQL语句,这个和EXEC直接执行SQL是一样的:

 
1 EXEC sp_executesql N'SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC' --必须加N

注意,那个大写的N必须加在要执行的sql语句前面,而且那个N也不是白来的,它还有重要的含义!

如果我们执行的sql语句定义成变量的形式,下面的sql是无法执行的:

 
1 DECLARE @Sql varchar(2000)
2 SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'
3 EXEC sp_executesql @Sql

搞怪的是,如果将@sql变量类型由varchar改成nvarchar,就可以执行了:

 
1 DECLARE @Sql nvarchar(2000)
2 SET @Sql='SELECT TOP 10 * FROM Person(NOLOCK) ORDER BY Id DESC'
3 EXEC sp_executesql @Sql

到这里哀乐响起,为什么为什么?偏偏你要折磨我?下面就来解惑吧:

(1)、EXEC sp_executesql N 这个倒贴过来的N表示后面的sql内容是unicode也是对应nvarchar类型;

(2)、将varchar改成nvarchar才能执行,就是为了执行sql时,将所有参数值转换为字符或 unicode 并使其成为 Transact-SQL 字符串的一部分。

5、将EXEC执行结果放入变量中

比如,我们需要查询Person表的所有记录数,可以像下面这样实现将执行结果放入变量@Num中:

 
1 /*将exec执行结果放入变量num中*/
2 DECLARE @Num int, @Sql nvarchar(4000)  
3 SET @Sql='SELECT @TotalCount=COUNT(0) FROM Person '  
4 EXEC sp_executesql @Sql,N'@TotalCount int output',@Num output  
5 SELECT @Num AS TotalCount

6、两个类型转换函数

下面示例将整数(int)转换成字符串(varchar):

 
1 /*CASTConvert函数*/
2 DECLARE @input int
3 SET @input=1234
4 SELECT CONVERT(varchar(50),@input)+' abc' AS result
5 SET  @input=@input+1000
6 SELECT CAST(@input AS varchar(50))+ ' xyz' AS result

这两个平时开发估计经常使用,大家应该不陌生。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16436858/viewspace-681072/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-10-17

  • 博文量
    1319
  • 访问量
    2080498