`
tangleilei
  • 浏览: 13922 次
  • 来自: 上海
社区版块
存档分类
最新评论

sql 遍历表

 
阅读更多
•使用游标
•使用表变量
•使用临时表

  在数据库开发过程中,我们经常会碰到要遍历数据表的情形,一提到遍历表,我们第一印象可能就想到使用游标,使用游标虽然直观易懂,但是它不符合面向集合操作的原则,而且性能也比面向集合低。当然,从面向集合操作的角度出发,也有两种方法可以进行遍历表的操作,总结起来,遍历表有下面几种方法。
1.使用游标
2.使用表变量
3.使用临时表

我的需求是:针对HR.Employees表,新增一列fullname,并取值firstname+lastname。

-- 需求是,新增一列fullname,取值firstname+lastname
ALTER TABLE HR.Employees ADD fullname NVARCHAR(30) NULL;
GO

原始效果如下图。



这个需求本来可以一条sql语句搞定,如下代码所示。但是为了演示表的遍历,我还是使用了这三种方式来实现一下。

USE TSQLFundamentals2008;
GO

UPDATE HR.Employees SET fullname= firstname+' '+lastname;

回到顶部

使用游标

  使用游标的代码比较繁琐,概括起来主要有以下几个步骤,声明游标,打开游标,使用游标,关闭游标和释放游标。示例代码如下。


复制代码
-- 方法1:游标
-- 声明变量
DECLARE
    @empid AS INT,
    @firstname AS NVARCHAR(10),
    @lastname AS NVARCHAR(20);
   
-- 声明游标
DECLARE C_Employees CURSOR FAST_FORWARD FOR
    SELECT empid,firstname,lastname
    FROM HR.Employees
    ORDER BY empid;
   
OPEN C_Employees;

-- 取第一条记录
FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname;

WHILE @@FETCH_STATUS=0
BEGIN
    -- 操作
    UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;
   
    -- 取下一条记录
    FETCH NEXT FROM C_Employees INTO @empid,@firstname,@lastname;
END

-- 关闭游标
CLOSE C_Employees;

-- 释放游标
DEALLOCATE C_Employees;

复制代码

运行脚本,效果如下图。



可以看到,已经达到我们想要的效果了。

回到顶部

使用表变量

  因为使用游标存在性能和违背面向集合思想的问题,所以我们有必要用面向集合的思想去找到一种更好的解决方案,下面这种方法是使用表变量的方式实现的,代码如下。


复制代码
1 -- 方法2:使用表变量
2 -- 声明表变量
3 DECLARE @temp TABLE--类似 declare @a int;
4 (
5     empid INT,
6     firstname NVARCHAR(10),
7     lastname NVARCHAR(20)
8 );
9
10 -- 将源表中的数据插入到表变量中
11 INSERT INTO @temp(empid, firstname, lastname )
12 SELECT empid,firstname,lastname FROM HR.Employees
13 ORDER BY empid;
14
15 -- 声明变量
16 DECLARE
17     @empid AS INT,
18     @firstname AS NVARCHAR(10),
19     @lastname AS NVARCHAR(20);
20    
21 WHILE EXISTS(SELECT empid FROM @temp)
22 BEGIN
23     -- 也可以使用top 1
24     SET ROWCOUNT 1
25     SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM @temp;--查询
26     UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;--更新
27     SET ROWCOUNT 0
28    
29     DELETE FROM @temp WHERE empid=@empid; --删除
30 END
set rowcount * 使 SQL Server 在返回指定的行数之后停止处理查询。
要将此选项设置为 off 以便返回所有的行,请将 SET ROWCOUNT 指定为 0。
复制代码

回到顶部

使用临时表

  临时表也可以实现表变量的功能,所以我们也可以使用临时表来实现这个需求,代码如下。


复制代码
1 -- 方法3:使用临时表
2 -- 创建临时表
3 IF OBJECT_ID('tempdb.dbo.#tempemployees','U') IS NOT NULL DROP TABLE dbo.#tempemployees;
4 GO
5
6 SELECT empid,firstname,lastname
7 INTO dbo.#tempemployees
8 FROM HR.Employees
9 ORDER BY empid;
10
11 --SELECT * FROM dbo.#tempemployees;
12
13 -- 声明变量
14 DECLARE
15     @empid AS INT,
16     @firstname AS NVARCHAR(10),
17     @lastname AS NVARCHAR(20);
18    
19 WHILE EXISTS(SELECT empid FROM dbo.#tempemployees)
20 BEGIN
21     -- 也可以使用top 1
22     SET ROWCOUNT 1
23     SELECT @empid= empid, @firstname= firstname,@lastname= lastname FROM dbo.#tempemployees;
24     UPDATE HR.Employees SET fullname= @firstname+' '+@lastname WHERE empid=@empid;
25     SET ROWCOUNT 0
26    
27     DELETE FROM dbo.#tempemployees WHERE empid=@empid;
28 END

出处Sam Xiao
http://www.cnblogs.com/mcgrady/p/4182486.html#_label0
分享到:
评论

相关推荐

    SQL Server遍历表中记录的2种方法(使用表变量和游标)

    在SQL Server中,遍历表中的记录是数据库操作中常见的一种任务,特别是在处理逐行处理数据或执行复杂逻辑时。本文将深入探讨两种方法:使用表变量和使用游标。 首先,我们来看如何通过表变量来遍历记录。表变量在...

    SQL优化,1、使用索引来更快地遍历表。

    在本文中,我们将深入探讨如何通过使用索引来加快遍历表的速度,以及其他的SQL优化策略。 首先,索引是数据库管理系统中用于加速查询的关键工具。默认创建的索引是非群集索引,但在某些情况下,群集索引可能是更好...

    VC++中数据库及其相关技术&使用SQL查询、操纵数据库

    并添加记录、使用ADO在数据库中遍历、修改和删除记录、使用ADO Data和DataGrid控件实现遍历、修改、删除、添加、使用...SQL语句进行基本的条件查询、实例使用SQL语句进行时间条件检索、使用SQL语句对数据库进行通用操作...

    sqlserver 循环临时表插入数据到另一张表

    sqlserver 循环临时表插入数据到另一张表 -- 声明变量 DECLARE @SupCode as varchar(100), @ProdCode as varchar(50), @PackLayer as varchar(50), @CodeStatus as varchar(50), @ProductId as varchar(50), @...

    将表中数据生成SQL语句!

    在这个存储过程中,我们使用了多种技术来生成SQL语句,例如使用游标来遍历表中的每一列,使用变量来存储列名和数据类型,使用case语句来根据数据类型生成对应的SQL语句等。 通过使用这个存储过程,我们可以轻松地将...

    asp.net获取SQL所有数据库名、所有表名、所有字段名

    ASP.NET 中获取 SQL 服务器所有数据库名、所有表名、所有字段名 在 ASP.NET 中,获取 SQL 服务器中的数据库名、表名和字段名是非常有用的功能,下面我们将详细介绍如何获取这些信息。 获取所有数据库名 要获取...

    将SQL数据库表转换成XML文件输出(脚本)

    - **遍历表并生成XML**:通过游标遍历临时表中的表名,对于每个表: - 使用`bcp`命令生成格式文件,这一步是可选的,但有助于确保XML文件的结构与数据库表一致。 - 使用`FOR XML AUTO`将表数据转换为XML格式,并...

    sql语法,初学者的福音

    7. **遍历数据库.txt**:这可能涉及到了数据库的遍历,比如使用系统视图或存储过程来获取数据库信息,或遍历表结构,这对于数据库维护和审计非常关键。 8. **分割字符串.txt**:SQL提供了字符串处理函数,如...

    SQL 导出数据 insert 语句

    这些工具会遍历表中的每一行数据,并为每一行生成一个对应的INSERT语句。这样生成的脚本可以在新的环境中运行,将数据重新插入到相应的表中。 在标签中提到的"MSSQL"指的是Microsoft SQL Server,这是微软公司开发...

    生成提取表中数据的sql脚本

    - 使用游标遍历表的所有列,并构建插入语句所需的列名和数据值。 6. **生成SQL脚本**: - 最终将生成一个SQL脚本,其中包含一系列`INSERT`语句,用于将源表中的数据复制到另一个地方。 ### 总结 本文通过分析...

    SQLServer中如何将一个字段的多个记录值合在一行显示

    在函数体中,我们使用了一个 WHILE 循环来遍历表中的记录,并将每个记录的 `收费类别` 字段值追加到 `@str` 变量中。最后,我们使用 `RIGHT` 函数来删除最后一个逗号,并返回最终结果。 知识点 2:使用 CROSS APPLY...

    SQL查询动态字段

    接下来,文档提供了一种更为灵活的方法——动态构建SQL语句,通过遍历表中的所有不同标记来动态生成SQL语句,从而支持更多的标记处理。 ```sql DECLARE @sql VARCHAR(8000) SET @sql = 'SELECT ' SELECT @sql = @sql...

    SQL插入脚本生成器

    5. **生成过程**:生成器遍历表的所有列,对于非自增字段,它会构造如下的SQL语句模板:`INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...)`。然后,它会根据表中的数据填充这些值,生成完整的插入脚本。 6...

    plsql sql 练习题 答案

    2. 使用游标遍历表中所有记录并打印出来。 3. 创建一个存储过程,用于更新指定表中的一条记录。 4. 编写一个带有异常处理的PL/SQL块,处理可能的SQL错误。 5. 实现一个嵌套循环,生成指定范围内的所有数字对组合。 ...

    SQL Server如何通过创建临时表遍历更新数据详解

    在SQL Server中,遍历和更新大量数据时,通常有两种主要方法:游标和使用临时表。本篇文章将重点探讨如何通过创建临时表来遍历并更新数据,以避免使用游标带来的性能损耗和复杂性。 首先,游标虽然直观,但其缺点...

    sql server替换textntext类型字段的值

    在给定的代码中,我们使用了游标来遍历表中的所有行,然后使用 WHILE 循环来查找和替换文本字段中的值。首先,我们使用 PATINDEX 函数来查找文本字段中要替换的值的位置,然后使用 TEXTPTR 函数来获取文本字段的指针...

    Oracle PL/SQL程序设计(第5版)源代码

    压缩包可能包含使用游标进行数据操作的实例,例如遍历表中的记录并进行处理。 4. **触发器**:触发器是数据库自动执行的程序,可以在特定的数据更改事件(如INSERT、UPDATE或DELETE)发生时运行。你可以找到如何...

    DELPHI编程将MSSQL表、视图、存储过程转换为SQL语句..rar

    4. **转换表为SQL语句**: 要将表转换为创建表的SQL语句,需要遍历表的所有列,并构造CREATE TABLE语句。这涉及对TADOTable或TADODataset组件的Fields属性的迭代,获取每个字段的名称、类型、长度等信息。 5. **处理...

Global site tag (gtag.js) - Google Analytics