What is View and Cursor in SQL Server?
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;
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
A cursor is opened for storing data retrieved from the result set.
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
The cursor should be closed explicitly after data manipulation.
Cursors should be deallocated to delete cursor definition and release all the system resources associated with the cursor.
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