/*游标*/
--游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制
/*游标的作用*/
--允许定位到结果集中的特定行。
--从结果集的当前位置检索一行或多行数据。
--支持对结果集中当前位置的行进行修改
/*创建游标*/
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
/*cursor_name
是所定义的 Transact-SQL 游标名称。
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、和 INTO。
*/
/*游标操作*/
[cpp] view plaincopyprint?declare cr cursor
for
select * from authors
--定义游标
--该游标未指定scroll，只可用next
open cr
--打开游标
fetch next from cr
--推进游标
close cr
--关闭游标
deallocate cr
--删除游标
create table emp
(
eid int primary key,
ename varchar(10),
sal money
)
insert into emp select 1001,'rose',1234
union select 1002,'jack',2564
union select 1003,'will',245
union select 1004,'lecky',456
--首次推进的结果为第一条
/*@@fetch_status*/
--返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
-- 0 FETCH 语句成功。
-- -1 FETCH 语句失败或此行不在结果集中。(无法fetch的时候)
-- -2 被提取的行不存在
fetch next from cr
while @@fetch_status=0
begin
fetch next from cr
end
--如不推进，则全局变量的值对于循环是否能够启动没有参考价值
--最后一行fetch_status值为0
--下一条为-1
/*滚动游标*/
declare cr cursor scroll for select * from emp
--需用scroll关键字
open cr
fetch next from cr
close cr
deallocate cr
declare cr cursor
for
select * from authors
--定义游标
--该游标未指定scroll，只可用next
open cr
--打开游标
fetch next from cr
--推进游标
close cr
--关闭游标
deallocate cr
--删除游标
create table emp
(
eid int primary key,
ename varchar(10),
sal money
)
insert into emp select 1001,'rose',1234
union select 1002,'jack',2564
union select 1003,'will',245
union select 1004,'lecky',456
--首次推进的结果为第一条
/*@@fetch_status*/
--返回被 FETCH 语句执行的最后游标的状态，而不是任何当前被连接打开的游标的状态。
-- 0 FETCH 语句成功。
-- -1 FETCH 语句失败或此行不在结果集中。(无法fetch的时候)
-- -2 被提取的行不存在
fetch next from cr
while @@fetch_status=0
begin
fetch next from cr
end
--如不推进，则全局变量的值对于循环是否能够启动没有参考价值
--最后一行fetch_status值为0
--下一条为-1
/*滚动游标*/
declare cr cursor scroll for select * from emp
--需用scroll关键字
open cr
fetch next from cr
close cr
deallocate cr
--FETCH FIRST：提取游标的第一行。
--FETCH NEXT：提取上次提取的行的下一行。
--FETCH PRIOR：提取上次提取的行的前一行。
--FETCH LAST：提取游标中的最后一行。
--FETCH ABSOLUTE n：
-- 如果n 为正整数，则提取 游标中的第n行
-- 如果n为负整数，则提取游标最后一行之前的第n行
-- 如果n 为0，则不提取任何行
--FETCH RELATIVE n ：
-- 如果n为正，则提取上次提取的行之后的第n行。
-- 如果n为负，则提取上提取的行之前的第n行。
-- 如果n为0，则再次提取同一行
/*@@cursor_rows*/
--返回当前打开的游标中符合条件的行的数目
/*游标实例（更新）*/
begin tran tr
declare cr cursor for select * from emp for update of ename
--通过该游标只能更新ename列
--多个列逗号分隔
open cr
fetch next from cr
update emp set ename='log' where current of cr
--current of cr通过游标
--只更新一行
close cr
deallocate cr
rollback tran tr
/*删除游标指向的行*/
delete from emp where current of cr
/*存储过程*/
/*取记录*/
create proc p1 @no int
as select * from emp where eid=@no
--as必须
exec p1 2004
--执行存储过程
/*加法*/
create proc p1 @a int,@b int
as return @a+@b
declare @res int
set @res=exec p1 20,30
print @res
--drop proc p1
--带有返回值的存储过程的执行
/*按地址传递输出*/
create proc p1 @a int output,@b int output
as
begin
declare @temp int
set @temp=@a
set @a=@b
set @b=@temp
end
declare @a int,@b int
set @a=90
set @b=890
exec p1 @a output,@b output
print @a
print @b
SQL之随机函数及游标应用示例
[c-sharp] view plaincopyprint?create table emp
(
eid varchar(10)
)
GO --注意这里一定要加go
--drop table emp
create proc prand
as
begin
declare @i int
set @i=0
while @i<100
begin
insert into emp select floor(rand()*100000)
--rand（）*100000的取值范围为1－－99999
set @i=@i+1
--循环插入100条随机数
end
declare crl scroll cursor for select * from emp
--定义游标
open crl
--打开游标
--fetch first from crl
declare @max int,@min int,@temp int
--@max最大值,@min最小值,@temp 临时变量
fetch next from crl into @max
--首次推进游标，'into @max'是把其推进结果赋值给@max，关于into子句的用法建议参看联机丛书了解一下，帅的很...
set @min=@max
--将此初值也赋给最小值
while @@fetch_status=0
begin
fetch next from crl into @temp
if @temp>@max
begin
set @max=@temp
end
if @temp<@min
begin
set @min=@temp
end
end
print '最大值为'+convert(varchar,@max)
print '最小值为'+convert(varchar,@min)
--输出结果，需要强制转换
close crl
--关闭游标
deallocate crl
--删除游标
end
--drop proc prand
exec prand
create table emp
(
eid varchar(10)
)
GO --注意这里一定要加go
--drop table emp
create proc prand
as
begin
declare @i int
set @i=0
while @i<100
begin
insert into emp select floor(rand()*100000)
--rand（）*100000的取值范围为1－－99999
set @i=@i+1
--循环插入100条随机数
end
declare crl scroll cursor for select * from emp
--定义游标
open crl
--打开游标
--fetch first from crl
declare @max int,@min int,@temp int
--@max最大值,@min最小值,@temp 临时变量
fetch next from crl into @max
--首次推进游标，'into @max'是把其推进结果赋值给@max，关于into子句的用法建议参看联机丛书了解一下，帅的很...
set @min=@max
--将此初值也赋给最小值
while @@fetch_status=0
begin
fetch next from crl into @temp
if @temp>@max
begin
set @max=@temp
end
if @temp<@min
begin
set @min=@temp
end
end
print '最大值为'+convert(varchar,@max)
print '最小值为'+convert(varchar,@min)
--输出结果，需要强制转换
close crl
--关闭游标
deallocate crl
--删除游标
end
--drop proc prand
exec prand
GO表示一批T-SQL语句结束，GO之后的T-SQL语句属于另一个批处理范围，在T-SQL所有语句的最后都要默认有一个GO。但是，GO不是T-SQL语句，而只是一个能被SQL Server实用工具识别的命令
USE [北风贸易]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[testProcedure]
@身份证号码 NVARCHAR(18)
AS
SELECT*FROM dbo.成绩 WHERE 身份证号码=@身份证号码
上面示例将发生错误：'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。
应该改成
USE [北风贸易]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testProcedure]
@身份证号码 NVARCHAR(18)
AS
SELECT*FROM dbo.成绩 WHERE 身份证号码=@身份证号码