SQL Serverカーソルの例
1934 ワード
-カーソルの宣言
declare my_cursor cursor keyset for select * from info
--カーソルリソースの削除
deallocate my_cursor
--カーソルを開きます.カーソルが閉じるか削除するまで有効です.
open my_cursor
--カーソルを閉じる
close my_cursor
--カーソルの解放
deallocate my_cursor
--ローカル変数の宣言
declare @id int,@name varchar(20),@address varchar(20)
--指定された場所に配置されたレコード
fetch absolute 56488 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--現在のレコードの相対位置レコードにナビゲート
fetch relative -88 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--現在のレコードの前に移動
fetch prior from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--現在のレコードの後に配置
fetch next from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--先頭レコードにナビゲート
fetch first from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--末尾レコードにナビゲート
fetch last from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
例:
use database1
declare my_cursor cursor keyset for select * from info
--カーソルリソースの削除
deallocate my_cursor
--カーソルを開きます.カーソルが閉じるか削除するまで有効です.
open my_cursor
--カーソルを閉じる
close my_cursor
--カーソルの解放
deallocate my_cursor
--ローカル変数の宣言
declare @id int,@name varchar(20),@address varchar(20)
--指定された場所に配置されたレコード
fetch absolute 56488 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--現在のレコードの相対位置レコードにナビゲート
fetch relative -88 from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--現在のレコードの前に移動
fetch prior from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--現在のレコードの後に配置
fetch next from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--先頭レコードにナビゲート
fetch first from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
--末尾レコードにナビゲート
fetch last from my_cursor into @id,@name,@address
select @id as id,@name as name,@address as address
例:
use database1
declare my_cursor cursor scroll dynamic
/**//*scroll ( ),dynamic ( )*/
for
select productname from product
open my_cursor
declare @pname sysname
fetch next from my_cursor into @pname
while(@@fetch_status=0)
begin
print 'Product Name: ' + @pname
fetch next from my_cursor into @pname
end
fetch first from my_cursor into @pname
print @pname
/**//*update product set productname='zzg' where current of my_cursor */
/**//*delete from product where current of my_cursor */
close my_cursor
deallocate my_cursor