实验内容
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 之后的 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 中的该临时表中得到应答;因此,在对该游标进行提取操作时返回的数据中不反映对基表所做的修改,并且该游标不允许修改。