| by Achyut Kendre | No comments

What is View and Cursor in SQL Server?

PL SQL

In this article we will learn about the view and cursors in PL SQL using SQL server.

What is View?

In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.

You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.

A view is created with the CREATE VIEW statement.

CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;

Deleting View

DROP VIEW view_name;

What is Cursor?

A SQL cursor is a database object that is used to retrieve data from a result set one row at a time. A SQL cursor is used when the data needs to be updated row by row.

Cursor is a facility provided by PLSQL using which you can store multiple values or records fetched from the data base table in a single variable called cursor variable. A cursor is a symbolic name associated with a select statement. It consists of the following parts:

Cursor result set – the set (table) of rows resulting from the execution of a query that is associated with the cursor.
Cursor position – a pointer to one row within the cursor result set.
The cursor position indicates the current row of the cursor. You can explicitly modify or delete that row using update or delete statements with a clause naming the cursor.
You can change the current cursor position through an operation called a fetch. The fetch command moves the current cursor position one or more rows down the cursor result set.

SQL Cursor Life Cycle

Declaring and setting Cursor
A cursor is declared by defining the SQL statement.
declare @cursorname cursor
set @cursorname=cursor for select query
Opening Cursor
A cursor is opened for storing data retrieved from the result set.
open @cursorname

Fetching Cursor
When a cursor is opened, rows can be fetched from the cursor one by one or in a block to do data manipulation.
fetch next from @cursorname into variables
@@FETCH_STATUS this variable will have the value 0 if cursor able to fetch next record, if cursor failed to fetch the record it will have the value

Closing Cursor
The cursor should be closed explicitly after data manipulation.

close @cursorname

Deallocating Cursor
Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.

DEALLOCATE cursorname;

e.g. cursor example to display the customser name and address.

Begin
Declare @custcur Cursor
Declare @nm varchar(30)
Declare @ad varchar(30)
set @custcur=cursor for select cust_name,cust_address from custtbl
Open @custcur
Fetch next from @custcur into @nm,@ad
While(@@FETCH_STATUS=0)
  Begin
       print ' Name : ' + @nm +' has address : ' + @ad
       fetch next from @custcur into @nm,@ad
 End
Close @custcur
End

e. g. cusor example to print employeename from emptbl & grosssal from empsaltbl using formula bs + hr + t1 + d1 -de by using cursor.

Begin
Declare @custcur cursor
Declare @nm varchar(30)
Declare @bs int
Declare @hr int
Declare @t1 int
Declare @d1 int
Declare @de int
Declare @grosssal int
Set @custcur=cursor for select emp_name,basic_sal,hra,ta,da,deduc from emptbl inner join empsaltbl on emptbl.emp_id=empsaltbl.emp_id
Open @custcur
Fetch next from @custcur into @nm,@bs,@hr,@t1,@d1,@de
while(@@FETCH_STATUS=0)
 Begin
 Set @grosssal=(@bs+@hr+@t1+@d1)-@de
 Print 'empName : ' + @nm +' has grosssal : ' + convert(varchar,@grosssal)
 Fetch next from @custcur into @nm,@bs,@hr,@t1,@d1,@de
 End
Close @custcur
End
« Prev