Difference between View and Cursor in SQL

Last Updated : 06 Jun, 2022

1. View :
A view is a virtual table that not actually exist in the database but it can be produced upon request by a particular user. A view is an object that gives the user a logical view of data from a base table we can restrict to what user can view by allowing them to see an only necessary column from the table and hide the other database details. View also permits users to access data according to their requirements, so the same data can be access by a different user in a different way according to their needs.

2. Cursor :
A cursor is a temporary work area created in memory for processing and storing the information related to an SQL statement when it is executed. The temporary work area is used to store the data retrieved from the database and manipulate data according to need. It contains all the necessary information on data access by the select statement. It can hold a set of rows called active set but can access only a single row at a time. There are two different types of cursors –
1. Implicit Cursor
2. Explicit Cursor

Difference between View and Cursor in SQL :

Sr.No.	Basis of Comparison	View	Cursor
1.	Terminology	A view is a virtual table that gives logical view of data from base table.	A CURSOR (CURrent Set of Records) is a temporary workstation created in the database server when the SQL statement is executed.
2.	Nature	Views are dynamic in nature which means any changes made in base table are immediately reflected in view.	Cursor can be static as well as dynamic in nature.
3.	Operations	We can perform CRUD operations on view like create, insert, delete and update.	There are some steps for creating a Explicit cursor – Declare the cursor in declaration section. Open the cursor in execution section. Fetch the cursor to retrieve data into PL/SQL variable. Close the cursor to release allocated memory.
4.	Data retrieval	Views are used to fetch or update data.	Using cursors, data retrieval from the resultset takes place row by row.
4.	Types	There are two types of view i.e. Simple View (created from the single table) and Complex View (created from multiple tables). In simple view, group functions like COUNT(), MIN(), can be used whereas in complex view group functions cannot be used.	Cursor has two types i.e. Implicit Cursor (pre-defined) and Explicit Cursor (user defined). Implicit cursor gets automatically created by Oracle whenever DML operations or SQL statement is executed whereas in explicit cursor user need to define them explicitly by giving a name.
5.	Usage	View is a database object similar to table so it can be used with both SQL and PL/SQL.	The cursor is defined and used within the block of stored procedure which means it can be only used with PL/SQL.
6.	Syntax	General Syntax of Creating View : CREATE VIEW “VIEW_NAME” AS “SQL Statement”;	General Syntax of Creating Explicit Cursor: CURSOR cursor_name IS select_statement;