How to Create Cursor in SQL
In this article I am going to explain how to create a cursor in SQL.
Introduction
A cursor is a database object that points to a result set. We use cursor to fetch any specific row from result set. Most of time cursor is used by application programmer.
Implementation of cursor in SQL
- We can implement cursor through standard database APIs.
- We can implement cursor through Transact-SQL
SQL Statement used in cursor
- DECLARE : It is used to define a new cursor.
- OPEN : It is used to open a cursor
- FETCH : It is used to retrieve a row from a cursor.
- CLOSE : It is used to close a cursor.
- DEALLOCATE : It is used to delete a cursor and releases all resources used by cursor.
We use DECLARE statement to declare a cursor. A cursor can be local or global to identify the scope of cursor. You can create dynamic, static and forward only cursor. Dynamic and forward only cursor is sensitive to all changes to source data. Static cursor is not sensitive to all changes to source data.
A Statement that is used to create a mcninvoices table
|
create table mcninvoices
(
invoiceid int not null identity primary key,
vendorid int not null,
invoiceno varchar(15),
invoicetotal money,
paymenttotal money,
creadittotal money
)
|
A Statement that is used to insert data in mcninvoices table
|
insert into mcninvoices values (20,'e001',100,100,0.00)
insert into mcninvoices values (21,'e002',200,200,0.00)
insert into mcninvoices values (22,'e003',500,0.00,100)
insert into mcninvoices values (23,'e004',1000,100,100)
insert into mcninvoices values (24,'e005',1200,200,500)
|
A Statement that show data in mcninvoices table
A Statement that is used to create a mcnvendors table
|
create table mcnvendors
(
vendorid int,
vendorname varchar(15),
vendorcity varchar(15),
vendorstate varchar(15)
)
|
A Statement that is used to insert data in mcnvendors table
|
insert into mcnvendors values (20,'vipendra','noida','up')
insert into mcnvendors values (21,'deepak','lucknow','up')
insert into mcnvendors values (22,'rahul','kanpur','up')
insert into mcnvendors values (23,'malay','delhi','delhi')
insert into mcnvendors values (24,'mayank','noida','up')
|
A Statement that show data in mcnvendors table
A Statement that is used to create a cursor
|
declare cur_invinfo cursor
global static
for
select vendorname,vendorcity,invoicetotal
from mcnvendors join mcninvoices
on mcnvendors.vendorid=mcninvoices.vendorid
|