游标中使用的SQL语句

• DECLARE ：它用于定义一个新的游标。
• OPEN ：它用于打开游标
• FETCH ：它用于从游标检索行。
• CLOSE：它用于关闭游标。
• DEALLOCATE：它用于删除游标并释放游标使用的所有资源。

fetch语句用于从游标检索特定行。

示例

创建billtbl表的语句

create table billtbl
(

billid int not null identity primary key,
vendorid int not null,
billno varchar(15),
billtotal money,

paymenttotal money,
creadittotal money
)

用于在billtbl表中插入数据的语句

insert into billtbl values (20,'e001',100,100,0.00)
insert into billtbl values (21,'e002',200,200,0.00)
insert into billtbl values (22,'e003',500,0.00,100)
insert into billtbl values (23,'e004',1000,100,100)

insert into billtbl values (24,'e005',1200,200,500)

在billtbl表中显示数据的语句

用于创建vendors表的语句

create table vendors
(
vendorid int,
vendorname varchar(15),

vendorcity varchar(15),
vendorstate varchar(15)
)

用于在vendors表中插入数据的语句

insert into vendors values (20,'vipendra','noida','up')
insert into vendors values (21,'jack','lucknow','up')
insert into vendors values (22,'rahul','kanpur','up')
insert into vendors values (23,'malay','Amsterdam','Amsterdam')

insert into vendors values (24,'mayank','noida','up')

在vendors表中显示数据的语句

select * from vendors

创建游标的语句

declare cur_bill cursor

global static

for

select vendorname,vendorcity,billtotal

from vendors join billtbl

on vendors.vendorid=billtbl.vendorid

打开游标语句

open cur_bill

从游标检索下一行的语句

fetch from cur_bill

从游标检索下一行的语句

fetch next from cur_bill

从游标检索第一行的语句

fetch first from cur_bill

从游标检索最后一行的语句

fetch last from cur_bill

从游标检索前一行的语句

fetch prior from cur_bill

从游标检索第五行的语句

fetch absolute 5 from cur_bill

从当前游标行检索第三行的语句

fetch relative 3 from cur_bill