Open In App

Difference between View and Cursor in SQL

Improve
Improve
Like Article
Like
Save
Share
Report

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; 
 

Last Updated : 06 Jun, 2022
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads