存储过程-使用游标、临时表、表变量、实现对表中数据进行操作

工作中经常会遇到批量处理一些数据，一般的方法可以使用代码开发相应的功能，在前端操作相应的功能，但是如果操作的数据不是特别的复杂，但是数据量特别的大，在客户端操作就会由于操作时间等待过长而导致客户体验度降低，这时我们就要考虑采用数据库中的存储过程进行操作，存储过程的优点我再这里就不再多介绍，详细的请参考的我的另一篇有关存储过程的介绍：https://www.cnblogs.com/mingqi-420/p/10664908.html。下面分别介绍游标和临时表两种方法对数据库中的表进行循环操作：

1、游标：

分类：

ms sql server 支持三种类型的游标：transact_sql 游标，api 服务器游标和客户游标。

（1） transact_sql 游标

transact_sql 游标是由declare cursor 语法定义、主要用在transact_sql 脚本、存储过程和触发器中。transact_sql 游标主要用在服务器上，由从客户端发送给服务器的transact_sql 语句或是批处理、存储过程、触发器中的transact_sql 进行管理。transact_sql 游标不支持提取块或多行。

（2） api 游标

api 游标支持在ole db， odbc 以及db_library 中使用游标函数，主要用在服务器上。每一次客户端应用程序调用api 游标函数，ms sql sever 的ole db 提供者、odbc驱动器或db_library 的动态链接库（dll） 都会将这些客户请求传送给服务器以对api游标进行处理。

（3） 客户游标

客户游标主要是当在客户机上缓存结果集时才使用。在客户游标中，有一个缺省的结果集被用来在客户机上缓存整个结果集。客户游标仅支持静态游标而非动态游标。由于服务器游标并不支持所有的transact-sql 语句或批处理，所以客户游标常常仅被用作服务器游标的辅助。因为在一般情况下，服务器游标能支持绝大多数的游标操作。

由于api 游标和transact-sql 游标使用在服务器端，所以被称为服务器游标，也被称为后台游标，而客户端游标被称为前台游标。

优点：

1）允许程序对由查询语句select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一个操作。

2）提供对基于游标位置的表中的行进行删除和更新的能力。

3）游标实际上作为面向集合的数据库管理系统（rdbms）和面向行的程序设计之间的桥梁，使这两种处理方式通过游标沟通起来。

缺点：

处理大数据量时，效率低下，占用内存大；
一般来说，能使用其他方式处理数据时，最好不要使用游标，除非是当你使用while循环,子查询，临时表，表变量,自建函数或其他方式都无法处理某种操作的时候，再考虑使用游标。

create procedure pro_mycursor
as
--声明1个变量
declare @name nvarchar(20)  
/*声明一个游标mycursor，select语句中参数的个数必须要和从游标取出的变量名相同*/
declare mycursor cursor for select name from student
--打开游标
open mycursor
--从游标里取出数据赋值到我们刚才声明的2个变量中
fetch next from mycursor into @name
 --判断游标的状态
--0 fetch语句成功    
-- -1 fetch语句失败或此行不在结果集中    
-- -2被提取的行不存在
while (@@fetch_status=0)
begin
--显示出我们每次用游标取出的值 或者进行一些其他的一些操作
   print '游标成功取出一条数据'
   print @name
--用游标去取下一条记录
   fetch next from mycursor into @name
end
--关闭游标
close mycursor
--撤销游标
deallocate mycursor

go

2、临时表

 1 create procedure pro_temptable
 2 as
 3     begin
 4 --临时表
 5         create table #t ( name nvarchar(50) );
 6         insert  into #t
 7                 ( name )
 8                 select  name
 9                 from    student;
10  --select * from #t
11  --drop table #t
12         declare @name int;
13         while exists ( select   [name]
14                        from     #t )
15             begin  
16                 select top 1
17                         @name = [name]
18                 from    #t; 
19               --打印名称  或者做其他的操作
20                 print @name;          
21                --删除临时表    
22                 delete  from #t
23                 where   name = @name;   
24         --select  * from    #t
25          --exec('drop table '+)
26             --print  @name  
27             end;
28     end;

 

3、表变量：

create proc pro_table
as
begin
declare @tb1 table
　　(
 　　name varchar(20)
　　)
　　insert into @tb1 select name from student
　   declare @name int;
        while exists ( select   name
                       from     @tb1 )
            begin  
                select top 1
                         @name = name
                from    student; 
             --打印名称  或者做其他的操作
                print @name;          
             end
 end

4、临时表和表变量的比较

（1）临时表是利用了硬盘(tempdb数据库) ，表名变量是占用内存，因此小数据量当然是内存中的表变量更快。当大数据量时，就不能用表变量了，太耗内存了。大数据量时适合用临时表。
（2）表变量缺省放在内存，速度快，所以在触发器，存储过程里如果数据量不大，应该用表变量。
临时表缺省使用硬盘，一般来说速度比较慢，那是不是就不用临时表呢？也不是，在数据量比较大的时候，如果使用表变量，会把内存耗尽，然后使用 tempdb的空间，这样主要还是使用硬盘空间，但同时把内存基本耗尽，增加了内存调入调出的机会，反而降低速度。这种情况建议先给tempdb一次分配合适的空间，然后使用临时表。
（3）临时表相对而言表变量主要是多了i/o时间，但少了对内存资源的占用。数据量较大的时候，由于对内存资源的消耗较少，使用临时表比表变量有更好的性能。
（4）建议：触发器、自定义函数用表变量；存储过程看情况，大部分用表变量；特殊的应用，大数据量的场合用临时表。
（5）表变量有明确的作用域，在定义表变量的函数、存储过程或批处理结束时，会自动清除表变量。
（6）在存储过程中使用表变量与使用临时表相比，减少了存储过程的重新编译量。
（7）涉及表变量的事务只在表变量更新期间存在。这样就减少了表变量对锁定和记录资源的需求。
（8）表变量需要事先知道表结构，普通临时表，只在当前会话中可用与表变量相同into一下就可以了,方便；全局临时表：可在多个会话中使用存在于temp中需显示的drop。（不知道表结构情况下临时表方便一些）
（9）全局临时表的功能是表变量没法达到的。
（10）表变量不必删除，也就不会有命名冲突，临时表特别是全局临时表用的时候必须解决命名冲突。
（11）应避免频繁创建和删除临时表，减少系统表资源的消耗。
（12）在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免log，提高速度；如果数据量不大，为了缓和系统表的资源，建议先create table，然后insert。
（13）如果临时表的数据量较大，需要建立索引，那么应该将创建临时表和建立索引的过程放在单独一个子存储过程中，这样才能保证系统能够很好的使用到该临时表的索引。
（14）如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。
（15）慎用大的临时表与其他大表的连接查询和修改，减低系统表负担，因为这种操作会在一条语句中多次使用tempdb的系统表。

大家可根据实际情况和这三者的优缺点合理的选择，如果有什么问题或者还有更好的方式，欢迎大家积极指正！！！