通常业务系统不推荐使用游标,但是又要实现游标逐行处理功能,在mssql中实现方法如下:
1.导入临时表,建立行ID逐行搜索或者每次TOP(1)查找
2.导入表变量,建立行ID逐行搜索
至于临时表和表变量主要区别在于大数量搜索时的查找时间长短,临时表是存贮在磁盘,表变量是在内存
临时表导入数据时需要两次IO读写,表变量只有一次,临时表可以建立索引,表变量不能,所以在大数据复杂操作时,临时表利用索引查找的速度能提供不少
实现脚本如下:
--准备数据
IF OBJECT_ID('tb_a') IS NOT NULL
DROP TABLE tb_a
CREATE TABLE tb_a
(
ph VARCHAR(20),
tdate VARCHAR(6),
qty NUMERIC(18,0)
)
INSERT INTO tb_a
select 'aaaaaaaaa','201101','100' union all
select 'aaaaaaaaa','201102','200' union all
select 'aaaaaaaaa','201103','0' union all
select 'aaaaaaaaa','201104','100' union all
select 'bbbbbbb','201101','100' union all
select 'bbbbbbb','201102','0' union all
select 'bbbbbbb','201103','200' union all
select 'bbbbbbb','201104','100' union all
select 'bbbbbbb','201105','400'
SELECT * FROM tb_a
GO
--方法一:表变量
DECLARE @t TABLE (
ph VARCHAR(20),
tdate VARCHAR(6),
qty NUMERIC(18),
rownum INT
)
DECLARE @i INT,@rownum int
INSERT INTO @t
SELECT ph,tdate,qty,ROW_NUMBER()OVER(ORDER BY ph) FROM tb_a
SELECT @rownum=@@ROWCOUNT,@i=1
WHILE @i<=@rownum
BEGIN
SELECT * FROM @t WHERE rownum=@i
SET @i=@i+1
END
--方法一:临时表
IF OBJECT_ID('tempdb..#tb_a') IS NOT NULL
DROP TABLE #tb_a
CREATE TABLE #tb_a
(
ph VARCHAR(20),
tdate VARCHAR(6),
qty NUMERIC(18)
)
INSERT INTO #tb_a
SELECT * FROM tb_a
--表变量与临时区别之一
CREATE NONCLUSTERED INDEX ix_ph ON #tb_a(ph)
DECLARE @j INT
SELECT @j=COUNT(1) FROM #tb_a
WHILE @j>0
BEGIN
SELECT TOP 1 * FROM #tb_a
--逻辑处理...
DELETE TOP(1) FROM #tb_a
SET @j=@j-1
END