数据库实验报告八(游标的使用)

 

实验内容

1、 游标的应用-定义 打开 关闭 删除

存储过程 –定义 使用 删除

 

实验过程

1、游标的应用

--use spj

/*
declare 游标名称 cursor
[local | clobal]
[forward_only|scroll]
[static|keyset|dynamic|fast_forward]
[read_only|scroll_locks|optimistic]
[type_warning]
for select 语句
[for update[for 字段名][,...n]]]
根据sql帮助理解各项含义
*/

--定义游标
declare spj_cursor cursor
scroll               

--指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用

for
select * from spj

--打开游标

open spj_cursor

--从游标中提取一行记录
--如果未指定scroll选项，fetch next是唯一可用的可提取选项

--用其他选项出现错误fetch: 提取类型 prior 不能用于只进游标。

--依次运行以下语句
fetch next from spj_cursor   --向后一行取得第一条数据 新定义的游标指向第一条数据的上一条
fetch next from spj_cursor
fetch next from spj_cursor
fetch next from spj_cursor
fetch next from spj_cursor
fetch prior from spj_cursor    --向前一行
fetch first from spj_cursor    --游标移到第一行

fetch last from spj_cursor     --游标移到最后一行
fetch next from spj_cursor

--如果当前行为最后一行 fetch next from spj_cursor 取的是空值

fetch absolute 3 from spj_cursor

--从开始移3行（取得的是第3行的数据 因为游标开始指向第一行的前一行） 如果3是负数则向上数3行

fetch absolute -3 from spj_cursor –取得倒数第3行的数据
fetch relative -1 from spj_cursor -从当前行开始移1行 //正数向上 负数向下

--如果当前行为最后一行 fetch relative 1 from spj_cursor取的是空值

 

 

--察看有几行
--if @@cursor_rows>0   //@@代表系统变量 cursor_rows为整数类型
print '共有'+convert(varchar,@@cursor_rows) //转换为字符串类型

返回值	描述
-m	游标被异步填充。返回值 (-m) 是键集中当前的行数。
-1	游标为动态。因为动态游标可反映所有更改，所以符合游标的行数不断变化。因而永远不能确定地说所有符合条件的行均已检索到。
0	没有被打开的游标，没有符合最后打开的游标的行，或最后打开的游标已被关闭或被释放。
n	游标已完全填充。返回值 (n) 是在游标中的总行数。

--关闭游标
close cj_cursor
--删除游标
deallocate cj_cursor

2、存储过程（相当于其他语言中的函数）

--建立一个带有参数存储过程

 

use Student

if exists(select name from sysobjects

             where name = 'xsxxcx' and type='p')

drop procedure xsxxcx   --如果有此过程(学生信息查询)就删除后再新建

go

-- @xm 姓名

-- @xb 性别

create procedure xsxxcx  

@xm varchar(8),@xb varchar(2)

as

select Sname 姓名,Ssex 性别,Sno 学号,Sage 年龄,Sdept 所在系

from Student

where Sname=@xm and Ssex=@xb

--go必须得加
go

if exists(select name from sysobjects

             where name = 'stu_scr' and type='p')

drop procedure stu_scr

go

--建立不带参数的存储过程 查询所在系为CS且姓李的同学的信息

create procedure stu_scr

as

select Sname 姓名,Ssex 性别,Sno 学号,Sage 年龄,Sdept 所在系

from student

where Sdept='CS' and Sname like '李%'

order by Sno

go

--执行存储过程
execute stu_scr
go

2.测试带参数的存储过程用

execute xsxxcx '李勇','男'   --执行自定义的过程（确保已使用此数据库）

 

 

实验中的问题的排除与总结：

1、新建一个查询需要重新定义游标，

GOTO

将执行流变更到标签处。跳过 GOTO 之后的 Transact-SQL 语句，在标签处继续处理。GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。GOTO 语句可嵌套使用。

语法

定义标签：

     label :

改变执行：

     GOTO label

参数

label

若有 GOTO 语句指向此标签，则其为处理的起点。标签必须符合标识符规则。不论是否使用 GOTO 语句，标签均可作为注释方法使用。

注释

GOTO 可用在条件控制流语句、语句块或过程中,但不可跳转到批处理之外的标签处。GOTO 分支可跳转到定义在 GOTO 之前或之后的标签处。

权限

GOTO 语句的权限默认情况下授予任何有效用户。

DECLARE CURSOR

定义 Transact-SQL 服务器游标的特性，例如游标的滚动行为和用于生成游标对其进行操作的结果集的查询。DECLARE CURSOR 接受基于 SQL-92 标准的语法和使用一组 Transact-SQL 扩展的语法。

SQL-92 语法

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
FOR select_statement
[ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]

Transact-SQL 扩展语法

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

SQL-92 参数

cursor_name

是所定义的 Transact-SQL 服务器游标名称。cursor_name 必须遵从标识符规则。有关标识符规则的更多信息，请参见使用标识符。

INSENSITIVE

定义一个游标，以创建将由该游标使用的数据的临时复本。对游标的所有请求都从 tempdb 中的该临时表中得到应答；因此，在对该游标进行提取操作时返回的数据中不反映对基表所做的修改，并且该游标不允许修改。使用 SQL-92 语法时，如果省略 INSENSITIVE，（任何用户）对基表提交的删除和更新都反映在后面的提取中。

SCROLL

指定所有的提取选项（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。如果在 SQL-92 DECLARE CURSOR 中未指定 SCROLL，则 NEXT 是唯一支持的提取选项。如果指定 SCROLL，则不能也指定 FAST_FORWARD。

select_statement

是定义游标结果集的标准 SELECT 语句。在游标声明的 select_statement 内不允许使用关键字 COMPUTE、COMPUTE BY、FOR BROWSE 和 INTO。

如果 select_statement 中的子句与所请求的游标类型的功能发生冲突，则 Microsoft® SQL Server™ 隐性地将游标转换为另一种类型。有关更多信息，请参见隐性游标转换。

READ ONLY

Prevents updates made through this cursor.在 UPDATE 或 DELETE 语句的 WHERE CURRENT OF 子句中不能引用游标。该选项替代要更新的游标的默认功能。

UPDATE [OF column_name [,...n]]

定义游标内可更新的列。如果指定 OF column_name [,...n] 参数，则只允许修改所列出的列。如果在 UPDATE 中未指定列的列表，则可以更新所有列。

Transact-SQL 扩展参数

cursor_name

是所定义的 Transact-SQL 服务器游标名称。cursor_name 必须遵从标识符规则。有关标识符规则的更多信息，请参见使用标识符。

LOCAL

指定该游标的作用域对在其中创建它的批处理、存储过程或触发器是局部的。该游标名称仅在这个作用域内有效。在批处理、存储过程、触发器或存储过程 OUTPUT 参数中，该游标可由局部游标变量引用。OUTPUT 参数用于将局部游标传递回调用批处理、存储过程或触发器，它们可在存储过程终止后给游标变量指派参数使其引用游标。除非 OUTPUT 参数将游标传递回来，否则游标将在批处理、存储过程或触发器终止时隐性释放。如果 OUTPUT 参数将游标传递回来，游标在最后引用它的变量释放或离开作用域时释放。

GLOBAL

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

 

说明  如果 GLOBAL 和 LOCAL 参数都未指定，则默认值由 default to local cursor 数据库选项的设置控制。在 SQL Server 7.0 版中，该选项默认为 FALSE 以与 SQL Server 早期版本相匹配，在 SQL Server 早期版本中所有游标都是全局的。该选项的默认值在以后的 SQL Server 版本中可能会更改。有关更多信息，请参见设置数据库选项。

FORWARD_ONLY

指定游标只能从第一行滚动到最后一行。FETCH NEXT 是唯一受支持的提取选项。如果在指定 FORWARD_ONLY 时不指定 STATIC、KEYSET 和 DYNAMIC 关键字，则游标作为 DYNAMIC 游标进行操作。如果 FORWARD_ONLY 和 SCROLL 均未指定，除非指定 STATIC、KEYSET 或 DYNAMIC 关键字，否则默认为 FORWARD_ONLY。STATIC、KEYSET 和 DYNAMIC 游标默认为 SCROLL。与 ODBC 和 ADO这类数据库 API 不同，STATIC、KEYSET 和 DYNAMIC Transact-SQL 游标支持 FORWARD_ONLY。FAST_FORWARD 和 FORWARD_ONLY 是互斥的；如果指定一个，则不能指定另一个。

STATIC

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