对SQL视图进行增加操作及游标的使用

　　　　下面这个实例　http://www.cnblogs.com/lucc/archive/2011/03/20/1989218.html　

使用户有Name,Remark两项信息，但是没有存放在同一张表中，而是分开存储在两个表UserBase(ID, Name)，UserExtent(ID, Remark)中。

create table UserBase

(

　　 ID int identity(1,1) not null primary key,

　　 Name varchar(20) not null

) go

create table UserExtent

(

　　ID int not null constraint FK_ID references UserBase(ID) ,

　　Remark varchar(50) not null

)

为使用方便，建立一个视图Users，用于表示用户的完整信息，其定义如下：

CREATE VIEW [dbo].[Users]
as
SELECT b.ID as ID, b.Name as Name, e.Remark as Remark 
FROM UserBase b, UserExtent e 
WHERE b.ID = e.ID;

现在，我们希望通过Users视图进行增删改实现对UserBase，UserExtent表进行修改。显然，如果对Users直接执行insert，update，delete是不可能的，执行时会发生以下错误：

在SQL Server中，对视图增删改可以通过触发器来实现，例如我们可以创建一个INSERT触发器，当在视图Users上执行INSERT时，在触发器中实现对UserBase，UserExtent的INSERT操作。在触发器中，可以通过名称为inserted的表，获取到新插入的行，具体代码如下：

CREATE TRIGGER [dbo].[Users_Insert] ON [dbo].[Users] INSTEAD OF INSERT
as
declare @name nvarchar(32), @remark nvarchar(32)
declare ins_cursor cursor
for
select Name, Remark from inserted
open ins_cursor
fetch next from ins_cursor into @name, @remark;
while(@@fetch_status = 0)
begin
    --读取所有行，并插入
    insert into UserBase (Name) values (@name);
    insert into UserExtent(ID, Remark) values (@@identity, @remark);
    fetch next from ins_cursor into @name, @remark;
end
close ins_cursor

下面我们通过插入两行数据测试触发器：

--清空所有数据
delete from UserExtent;
delete from UserBase;

create table #temp(
    name nvarchar(32),
    remark nvarchar(32)
)
insert #temp (name,remark) values (N'user1', N'1');
insert #temp (name,remark) values (N'user2', N'2');

--插入两行数据
insert Users(name, remark)
select name,remark from #temp

drop table #temp

select * from Users;
select * from UserBase;
select * from UserExtent;

执行结果如下：

在定义这个触发器中使用到游标，　

下面为游标的实例：

你要找出以a 为分组条件的前一笔资料
例如
SELECT * FROM YB1

4         9
1         3
1         5
3         0
4         8
4         0

declare mycur cursor for select DISTINCT T1 from YB1---- 找出定义的栏位‘这里以t1为条件’
declare @T2 CHAR---------上面的select语句查询几个栏位定义几个变数

declare @t table (T3 CHAR ,T4 CHAR)-----------查询的内容存放的表单
open mycur
fetch next from mycur into @T2
while @@fetch_status=0
begin
insert into @t----将根据条件查询出的内容放入到表@t中
select TOP 1* from YB1----查询出内容
where T1=@T2----定义几个变数这里就要等於几个阐述
ORDER BY T2
fetch next from mycur into @T2----根据定义的参数进行回圈
end
---关闭游标
close mycur
deallocate mycur

select * from @t
1 3
3 0
4 0

游标语句的核心是定义了一个游标标识名，并把游标标识名和一个查询语句关联起来。

declare语句用于声明游标，它通过SELECT查询定义游标存储的数据集合。

语句格式为：（SQL-92语法格式）

DECLARE 游标名称 [INSENSITIVE] [SCROLL] CURSOR FOR select语句 [FOR{READ ONLY|UPDATE[OF 列名字表]}]

注：INSENSITIVE关键字：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的临时表中得到应答；

　　　　　　　　　　　　　因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。

　　　　　　　　　　　　　使用SQL-92语法时，如果省略INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中

　　SCROLL关键字：指定该游标可用所有的游标数据定位方法提取数据，游标定位方法包括（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。

　　FIRST：取第一行数据。

　 LAST：取最后一行数据。

　 PRIOR：取前一行数据。

　 NEXT：取后一行数据。　　　　　　　如果未指定SCROLL，则NEXT是惟一支持的提取选项。

　 RELATIVE：按相对位置取数据。

　 ABSOLUTE：按绝对位置取数据。

　　在游标声明语句中，有下列条件之一时，系统自动把游标定义为INSENSITIVE游标： SELECT语句中使用了DISTINCT、UNION、 GROUP BY或HAVING等关键字；

　　任一个游标基表中不存在唯一索引

　 READ ONLY：表明不允许游标内的数据被更新，尽管在默认状态下游标是允许更新的。在UPDATE或DELETE语句的WHERE CURRENT
　　OF子句中不允许引用游标。

　　　UPDATE [OF 列名字表]选项：定义游标可修改的列。如果使用OF 列名字表选项，说明只允许修改所指定的列，否则，所有列均可修改。

　语句格式为：（SQL Server扩展格式）

　　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 ] ] ]

　　注：LOCAL：定义游标的作用域仅限在其所在的批处理、存储过程或触发器中。当建立游标在存储过程执行结束后，游标会被自动释放。

　　　　 GLOBAL：指定该游标的作用域对连接是全局的。在由连接执行的任何存储过程或批处理中，都可以引用该游标名称。该游标仅在脱接时隐性释放。

　　　　 FORWARD_ONLY：指定游标只能从第一行滚动到最后一行。FETCH NEXT是惟一受支持的提取选项非指定STATIC、KEYSET或DYNAMIC关键字，

　　　　否则默认为FORWARD_ONLY。STATIC、KEYSET和DYNAMIC游标默认为SCROLL。与ODBC和ADO这类数据库API不同，STATIC、KEYSET和DYNAMICTransact-　　　　　　　SQL游标支持FORWARD_ONLY。FAST_FORWARD和FORWARD_ONLY是互斥的；如果指定一个，则不能指定另一个。

　　　　STATIC：定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从tempdb中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的　　数据中不反映对基表所做的修改，并且该游标不允许修改。

　　　
　　　　KEYSET：指定当游标打开时，游标中行的成员资格和顺序已经固定。对行进行惟一标识的键集内置在tempdb内一个称为keyset的表中。对基表中的非键值所做的更改　　（由游标所有者更改或由其他用户提交）在用户滚动游标时是可视的。其他用户进行的插入是不可视的（不能通过Transact-SQL服务器游标进行插入）。如果某行已删除，则对该行的提取操作将返回@@FETCH_STATUS值-2。从游标外更新键值类似于删除旧行后接着插入新行的操作。含有新值的行不可视，对含有旧值的行的提取操作将返回@@FETCH_STATUS值-2。如果通过指定WHERE 　CURRENT OF子句用游标完成更新，则新值可视。

　　　　
　　　　DYNAMIC：定义一个游标，以反映在滚动游标时对结果集内的行所做的所有数据的更改。行的数据值、顺序和成员在每次提取时都会更改。

　　　　动态游标不支持ABSOLUTE提取选项。

　　　　FAST_FORWARD：指明一个FORWARD_ONLY、READ_ONLY型游标。

　　　　SCROLL_LOCKS：指定确保通过游标完成的定位更新或定位删除可以成功。将行读入游标以确保它们可用于以后的修改时，SQL
Server会锁定这些行。如果还指定了FAST_FORWARD，则不能指定SCROLL_LOCKS。

　　　　OPTIMISTIC：指明在数据被读入游标后，如果游标中某行数据已发生变化，那么对游标数据进行更新或删除可能会导致失败。

　　　　TYPE_WARNING：指定如果游标从所请求的类型隐性转换为另一种类型，则给客户端发送警告消息。

　　　　使用DECLARE CURSOR语句创建以下几种形式的游标。

　　　eg: 标准游标

　　　　DECLARE MyCursor CURSOR FOR

　　　　SELECT * FROM 操作员信息表

只读游标

　　　　DECLARE MyCursor_01 CURSOR FOR

　　　　SELECT * FROM 操作员信息表

　　　　FOR READ ONLY --只读游标

　　　　　　更新游标

　　　　DECLARE MyCursor_02 CURSOR FOR

　　　　SELECT 操作员编号,操作员姓名,操作员年龄 FROM 操作员信息表

　　　　FOR UPDATE --更新游标

　　　　打开游标 打开一个声明的游标使用OPEN命令。

　　　　语法：

　　　　OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }

　　　　注：global 为全局，不标为局部游标

　　　　　　cursor_variable_name：游标变量的名称，该名称引用一个游标。

　　　　　　如果使用INSENSITIV或STATIC选项声明了游标，那么OPEN将创建一个临时表以保留结果集。如果结果集中任意行的大小超过SQL
　　　　　　Server表的最大行大小，OPEN将失败。如果使用KEYSET选项声明了游标，那么OPEN将创建一个临时表以保留键集。临时表存储在tempdb中。

　　　　　从游标中读取数据 当打开一个游标之后，就可以读取游标中的数据了。可以使用FETCH命令读取游标中的某一行数据。

　　　　　　语法：　　

　　　FETCH

　　 [ [ NEXT | PRIOR | FIRST | LAST

　　 | ABSOLUTE { n | @nvar }

　　 | RELATIVE { n | @nvar }

　　 ]

　　 FROM

　　 ]

　　　　{ { [ GLOBAL ] cursor_name } | @cursor_variable_name }

　　　　INTO @variable_name [ ,...n ] ]

　　　　注：NEXT：返回紧跟当前行之后的结果行，并且当前行递增为结果行。如果FETCH 　NEXT　为对游标的第一次提取操作，　　

　　　　　　则返回结果集中的第一行。NEXT为默认的游标提取选项。

　　　　　　 PRIOR：返回紧临当前行前面的结果行，并且当前行递减为结果行。如果FETCH PRIOR为对游标的第一次提取操作，则没有行返回并且游标置于第一行之前。

　　　　　　FIRST：返回游标中的第一行并将其作为当前行。

　　　　　　LAST：返回游标中的最后一行并将其作为当前行。

　　　　　　ABSOLUTE {n | @nvar}：如果n或@nvar为正数，返回从游标头开始的第n行，并将返回的行变成新的当前行。如果n或@nvar为负数，返回游标尾之前的第n行，　　　　　　　并将返回的行变成新的当前行。如果n或@nvar为0，则没有行返回。

　　　　　　RELATIVE {n | @nvar}：如果n或@nvar为正数，返回当前行之后的第n行，并将返回的行变成新的当前行。如果n或@nvar为负数，返回当前行之前的第n行，并将返回的行变成新的当前行。如果n或@nvar为0，返回当前行。如果对游标的第一次提取操作时将FETCHRELATIVE的n或@nvar指定为负数或0，则没有行返回。n必须为整型常量且@nvar必须为smallint、tinyint或int。

　　　　　　在前两个参数中，包含了n和@nvar其表示游标相对与作为基准的数据行所偏离的位置。

　　　　　　cursor_name：要从中进行提取的开放游标的名称。如果同时有以cursor_name作为名称的全局和局部游标存在，若指定为GLOBAL，则cursor_name对应于全局游标，未指定GLOBAL，则对应于局部游标。

　　　　　　@cursor_variable_name：游标变量名，引用要进行提取操作的打开的游标。

　　　　　　INTO @variable_name[,...n]：允许将提取操作的列数据放到局部变量中。列表中的各个变量从左到右与游标结果集中的相应列相关联。各变量的数据类型必须与相应的结果列的数据类型匹配或是结果列数据类型所支持的隐性转换。变量的数目必须与游标选择列表中的列的数目一致。

　　　　　　@@FETCH_STATUS：返回上次执行FETCH命令的状态。在每次用FETCH从游标中读取数据时，都应检查该变量，以确定上次FETCH操作是否成功，决定如何进行下一步处理。@@FETCH_STATUS变量有3个不同的返回值，说明如下：返回值为0：FETCH 语句成功；返回值为-1：FETCH 语句失败或此行不在结果集中；返回值为-2：被提取的行不存在。

　　　　　　当使用SQL-92语法来声明一个游标时，没有选择SCROLL选项，则只能使用FETCH NEXT命令来从游标中读取数据，即只能从结果集第一行按顺序地每次读取一行。由于不能使用FIRST、LAST、PRIOR，所以无法回滚读取以前的数据。如果选择了SCROLL选项，则可以使用所有的FETCH操作。

　　　　　　通常游标取数的操作与WHILE循环紧密结合，下面将使用@@FETCH_STATUS控制在一个WHILE循环中的游标活动。

　　　　　eg:

　　　　　　DECLARE ReadCursor CURSOR FOR --声明一个游标

　　　　　　SELECT 操作员编号,操作员姓名,操作员性别,操作员住址

　　　　　　FROM 操作员信息表

　　　　　　OPEN ReadCursor --打开游标

　　　　　　FETCH NEXT FROM ReadCursor --执行取数操作

　　　　　　WHILE @@FETCH_STATUS=0 --检查@@FETCH_STATUS，以确定是否还可以继续取数

　　　　　　　　BEGIN

　　　　　　　　　　FETCH NEXT FROM ReadCursor

　　　　　　　　END

关闭游标语法：

　　　　　　CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }　　　注：与打开游标类似

　　　　　　　eg:

　　　　　　　　DECLARE CloseCursor Cursor FOR --声明游标

　　　　　　　　SELECT * FROM 销售表

　　　　　　　　FOR READ ONLY

　　　　　　　　OPEN CloseCursor --打开游标

　　　　　　　　CLOSE CloseCursor --关闭游标

　　　　　　释放游标
　　　　　　　　当游标关闭之后，并没有在内存中释放所占用的系统资源，所以可以使用DEALLOCATE命令删除游标引用。当释放最后的游标引用时，组成该游标的数据结构由SQL Server释放。

　　　　DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }

eg:

　　　　　　DECLARE FreeCursor Cursor FOR --声明游标

　　　　　　SELECT * FROM 销售表

　　　　　　OPEN FreeCursor --打开游标

　　　　　　Close FreeCursor --关闭游标

　　　　　　DEALLOCATE FreeCursor --释放游标

posted @ 2012-11-22 00:10 skylionsea 阅读(762) 评论(0) 收藏举报

刷新页面返回顶部

对SQL视图进行增加操作及游标的使用

公告