转载自:

T-SQL语法学习(一)

本篇介绍如何使用SSIS和作业完成自动更新目标数据任务。 

什么是游标                                                                                   

结果集,结果集就是select查询之后返回的所有行数据的集合。

游标则是处理结果集的一种机制吧,它可以定位到结果集中的某一行,多数据进行读写,也可以移动游标定位到你所需要的行中进行操作数据。

一般复杂的存储过程,都会有游标的出现,他的用处主要有:

  1. 定位到结果集中的某一行。
  2. 对当前位置的数据进行读写。
  3. 可以对结果集中的数据单独操作,而不是整行执行相同的操作。
  4. 是面向集合的数据库管理系统和面向行的程序设计之间的桥梁。

第一节 不常用语句

不常用语句-指的是一些不常用的查询语句,不针对业务数据查询

  • SET STATISTICS IO ON(用于查询逻辑读取次数,物理读取次数)
    图片
  • select @@version (查询当前实例的数据库版本)

 

游标的分类                                                                                    

根据游标检测结果集变化的能力和消耗资源的情况不同,SQL
Server支持的API服务器游标分为一下4种:

  • 静态游标: 静态游标的结果集,在游标打开的时候建立在TempDB中,不论你在操作游标的时候,如何操作数据库,游标中的数据集都不会变。例如你在游标打开的时候,对游标查询的数据表数据进行增删改,操作之后,静态游标中select的数据依旧显示的为没有操作之前的数据。如果想与操作之后的数据一致,则重新关闭打开游标即可。
  • 动态游标:这个则与静态游标相对,滚动游标时,动态游标反应结果集中的所有更改。结果集中的行数据值、顺序和成员在每次提取时都会变化。所有用户做的增删改语句通过游标均可见。如果使用API函数或T-SQL
    Where Current
    of子句通过游标进行更新,他们将立即可见。在游标外部所做的更新直到提交时才可见。
  • 只进游标:只进游标不支持滚动,只支持从头到尾顺序提取数据,数据库执行增删改,在提取时是可见的,但由于该游标只能进不能向后滚动,所以在行提取后对行做增删改是不可见的。
  • 键集驱动游标:打开键集驱动游标时,该有表中的各个成员身份和顺序是固定的。打开游标时,结果集这些行数据被一组唯一标识符标识,被标识的列做删改时,用户滚动游标是可见的,如果没被标识的列增该,则不可见,比如insert一条数据,是不可见的,若可见,须关闭重新打开游标。

静态游标在滚动时检测不到表数据变化,但消耗的资源相对很少。动态游标在滚动时能检测到所有表数据变化,但消耗的资源却较多。键集驱动游标则处于他们中间,所以根据需求建立适合自己的游标,避免资源浪费。

第二节 操作数据库管理工具注意事项

当我们在使用数据库图形化管理界面时,在执行update,insert,delete操作时,要是忘了加上条件,那么将会发生非常严重的问题,整个表的数据,乃至整个数据库都可能被你删除掉了,因此在做这些危险操作时,我建议加上事务,如下:

  begin tran hcx  --创建并命名事务
  update MCSMCB
  set MCB004=c.MYA013,MCB021=c.MYA003
  from
   MCSMCB b
   left join MCSMCA a on b.MCB001=a.MCA001
   left join EB_DuoMoCh.dbo.BASMYA c on c.MYA001=a.MCA002 and  RTRIM(LTRIM(c.MYA004))=b.MCB003 AND b.MCB015=c.MYA002

          --先使用select将要update的列,以及要update的值查询出来,做好对比,然后再执行update操作,并select出来,最后commit
   select MCB001,MCA002,c.MYA004,b.MCB003,c.MYA013,b.MCB004,c.MYA003,b.MCB021,b.MCB015,c.MYA002 from
   MCSMCB b
   left join MCSMCA a on b.MCB001=a.MCA001
   left join EB_DuoMoCh.dbo.BASMYA c on c.MYA001=a.MCA002 and  RTRIM(LTRIM(c.MYA004))=b.MCB003 AND b.MCB015=c.MYA002

  rollback tran hcx  --回滚事务
  COMMIT TRAN hcx

** 温馨提示:如需转载本文,请注明内容出处。**

游标的生命周期                                                                             

游标的生命周期包含有五个阶段:声明游标、打开游标、读取游标数据、关闭游标、释放游标。

 1.声明游标,语法

图片 1

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]

图片 2

参数说明:

  • cursor_name:游标名称。
  • Local:作用域为局部,只在定义它的批处理,存储过程或触发器中有效。
  • Global:作用域为全局,由连接执行的任何存储过程或批处理中,都可以引用该游标。
  • [Local | Global]:默认为local。
  • Forward_Only:指定游标智能从第一行滚到最后一行。Fetch
    Next是唯一支持的提取选项。如果在指定Forward_Only是不指定Static、KeySet、Dynamic关键字,默认为Dynamic游标。如果Forward_Only和Scroll没有指定,Static、KeySet、Dynamic游标默认为Scroll,Fast_Forward默认为Forward_Only
  • Static:静态游标
  • KeySet:键集游标
  • Dynamic:动态游标,不支持Absolute提取选项
  • Fast_Forward:指定启用了性能优化的Forward_Only、Read_Only游标。如果指定啦Scroll或For_Update,就不能指定他啦。
  • Read_Only:不能通过游标对数据进行删改。
  • Scroll_Locks:将行读入游标是,锁定这些行,确保删除或更新一定会成功。如果指定啦Fast_Forward或Static,就不能指定他啦。
  • Optimistic:指定如果行自读入游标以来已得到更新,则通过游标进行的定位更新或定位删除不成功。当将行读入游标时,sqlserver不锁定行,它改用timestamp列值的比较结果来确定行读入游标后是否发生了修改,如果表不行timestamp列,它改用校验和值进行确定。如果已修改改行,则尝试进行的定位更新或删除将失败。如果指定啦Fast_Forward,则不能指定他。
  • Type_Warning:指定将游标从所请求的类型隐式转换为另一种类型时向客户端发送警告信息。
  • For Update[of column_name ,….] :定义游标中可更新的列。

2.声明一个动态游标

declare orderNum_02_cursor cursor scroll
for select OrderId from bigorder where orderNum='ZEORD003402'

3.打开游标

--打开游标语法
open [ Global ] cursor_name | cursor_variable_name

cursor_name:游标名,cursor_variable_name:游标变量名称,该变量引用了一个游标。

--打开游标
open orderNum_02_cursor

4.提取数据

图片 3

--提取游标语法
Fetch
[ [Next|prior|Frist|Last|Absoute n|Relative n ]
from ]
[Global] cursor_name
[into @variable_name[,....]]

图片 4

参数说明:

  • Frist:结果集的第一行
  • Prior:当前位置的上一行
  • Next:当前位置的下一行
  • Last:最后一行
  • Absoute n:从游标的第一行开始数,第n行。
  • Relative n:从当前位置数,第n行。
  • Into @variable_name[,…] :
    将提取到的数据存放到变量variable_name中。

例子:

图片 5

--提取数据
fetch first from orderNum_02_cursor
fetch relative 3 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch absolute 4 from orderNum_02_cursor
fetch next from orderNum_02_cursor
fetch last from orderNum_02_cursor 
fetch prior from orderNum_02_cursor
select * from bigorder where orderNum='ZEORD003402'

图片 6

结果(对比一下,就明白啦):

图片 7

例子:

--提取数据赋值给变量
declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
select @OrderId as id
select * from bigorder where orderNum='ZEORD003402'

结果:

图片 8

通过检测全局变量@@Fetch_Status的值,获得提取状态信息,该状态用于判断Fetch语句返回数据的有效性。当执行一条Fetch语句之后,@@Fetch_Status可能出现3种值:0,Fetch语句成功。-1:Fetch语句失败或行不在结果集中。-2:提取的行不存在。

这个状态值可以帮你判断提取数据的成功与否。

图片 9

declare @OrderId int
fetch absolute 3 from orderNum_02_cursor into @OrderId
while @@fetch_status=0  --提取成功,进行下一条数据的提取操作
 begin
   select @OrderId as id
   fetch  next from orderNum_02_cursor into @OrderId  --移动游标
 end 

图片 10

5.利用游标更新删除数据 

--游标修改当前数据语法
Update 基表名 Set 列名=值[,...] Where Current of 游标名
--游标删除当前数据语法
Delete 基表名  Where Current of 游标名

图片 11

---游标更新删除当前数据
---1.声明游标
declare orderNum_03_cursor cursor scroll
for select OrderId ,userId from bigorder where orderNum='ZEORD003402'
--2.打开游标
open orderNum_03_cursor
--3.声明游标提取数据所要存放的变量
declare @OrderId int ,@userId varchar(15)
--4.定位游标到哪一行
fetch First from orderNum_03_cursor into @OrderId,@userId  --into的变量数量必须与游标查询结果集的列数相同
while @@fetch_status=0  --提取成功,进行下一条数据的提取操作 
 begin
   if @OrderId=122182
     begin
     Update bigorder Set UserId='123' Where Current of  orderNum_03_cursor  --修改当前行
     end
   if @OrderId=154074
      begin
      Delete bigorder Where Current of  orderNum_03_cursor  --删除当前行
      end
   fetch next from orderNum_03_cursor into @OrderId ,@userId  --移动游标
 end  

图片 12

6.关闭游标

 游标打开后,服务器会专门为游标分配一定的内存空间存放游标操作的数据结果集,同时使用游标也会对某些数据进行封锁。所以游标一旦用过,应及时关闭,避免服务器资源浪费。

--关闭游标语法
close [ Global ] cursor_name | cursor_variable_name
--关闭游标
close orderNum_03_cursor

7.删除游标

删除游标,释放资源

--释放游标语法
deallocate  [ Global ] cursor_name | cursor_variable_name
--释放游标
deallocate orderNum_03_cursor

本文链接:https://www.cnblogs.com/grom/p/9018978.html 

 

  笔者需要定期从服务器更新N家客户的远程服务器数据,上一篇的存储过程是其中一个更新方法,后来随着数据量逐渐增大,受网络环境的影响,批量远程插入消耗的时间已经无法承受,后来在导出数据时发现了保存SSIS选项,然后展开了测试开发,现将成果分享出来供大家参考,类似的资料不少,所以就简单写写操作步骤。

 

首先,尽量可能使用部署到环境的Windows账号,比如部署到服务器,就远程到服务器后使用Windows账号登录,可以避免很多权限上的麻烦。

另外,注意客户端的版本,SSMS一定不能低于SQL服务版本,不然会出异常。

 

微软官网文档:

 

导出导入数据,SSIS包部署在源库还是目标库均可,下面以导出数据为例。

 

右键导出数据的库 任务 导出数据

 

图片 13

 

选择导出数据库(数据源库)

图片 14

 

 选择目标库

图片 15

 

编辑要传输的表,有不同需求(如需要删除目标表内原数据实现全覆盖等)时,可在编辑映射里设置。

图片 16

 

 批量设置图片 17或单表设置图片 18

 

最后保存SSIS包,为了保证可移植性,笔者这里将保护级别设置成了密码保护

图片 19

(SSIS包保存方式有两种,一种是保存在SQL
Server内部,另一种是保存成.dtsx的文件

保存在SQL
Server里可以在系统数据库msdb里的sysssispackages表内看到

或者保存成文件图片 20 双击执行后可开启执行包的实用工具(前提是安装了)

图片 21

跟着向导走就可以执行包了,无论是包文件还是Sql
Server内的均可使用此工具执行。)

 

继续导出

图片 22

发表评论

电子邮件地址不会被公开。 必填项已用*标注