课堂示范.txt

主要介绍了sql的基础教程

游标与自定义函数案例
一、课堂演示案例
例一：创建一个简单的游标
创建游标
declare TitleCursor CURSOR
scroll
for
select title_id,title,price,ytd_sales from titles
where type='psychology'
----------------------------------------------------------------------------------------------------------------------
打开游标
open titlecursor
----------------------------------------------------------------------------------------------------------------------
提取游标
fetch titlecursor
select @@fetch_status
----------------------------------------------------------------------------------------------------------------------
提取第一行
fetch first from titlecursor
----------------------------------------------------------------------------------------------------------------------
提取下一行
fetch next from titlecursor
----------------------------------------------------------------------------------------------------------------------
提取上一行
fetch prior from titlecursor
----------------------------------------------------------------------------------------------------------------------
提取最后一行
fetch last from titlecursor
----------------------------------------------------------------------------------------------------------------------
提取绝对位置的行
fetch absolute 2 from titlecursor
----------------------------------------------------------------------------------------------------------------------
提取相对位置的行
fetch relative 1 from titlecursor
----------------------------------------------------------------------------------------------------------------------
关闭、释放游标
close titlecursor
deallocate titlecursor
----------------------------------------------------------------------------------------------------------------------

例二：使用forward_only游标
declare JobsCursor cursor forward_only
for
select * from jobs where min_lvl=75
----------------------------------------------------------------------------------------------------------------------
open jobscursor
fetch jobscursor
update jobs set max_lvl=100
where current of jobscursor
----------------------------------------------------------------------------------------------------------------------
select * from jobs where min_lvl=75
----------------------------------------------------------------------------------------------------------------------
fetch relative -1 from jobscursor
----------------------------------------------------------------------------------------------------------------------
close jobscursor
deallocate jobscursor
----------------------------------------------------------------------------------------------------------------------

例三：使用read_only游标
declare PubInfoCursor cursor read_only
for
select * from pub_info
where pub_id between 1000 and 2000
----------------------------------------------------------------------------------------------------------------------
open pubinfocursor
fetch pubinfocursor
delete from pub_info
where current of pubinfocursor
----------------------------------------------------------------------------------------------------------------------
close pubinfocursor
deallocate pubinfocursor
----------------------------------------------------------------------------------------------------------------------

例四：通过游标循环修改数据
declare JobsCursor cursor scroll
for
select * from jobs where min_lvl=75
for update
----------------------------------------------------------------------------------------------------------------------
open jobscursor
fetch first from jobscursor
while @@fetch_status=0
begin
	update jobs set max_lvl=100
	where current of jobscursor
----------------------------------------------------------------------------------------------------------------------
	fetch next from jobscursor
end 
----------------------------------------------------------------------------------------------------------------------
select * from jobs where min_lvl=75
----------------------------------------------------------------------------------------------------------------------
close jobscursor
deallocate jobscursor