CURSOR AND VIEW

What is a cursor N Details

Question : What is a cursor?

Oracle uses work area to execute SQL statements and store processing information PL/SQL construct called a cursor lets you name a work area and access its stored information A cursor is a mechanism used to fetch more than one row in a Pl/SQl block.

---

Cursor is a variable.
it is similar to a 2D array .
used for processing multiple rows.
used for storing data temporarily.

cursors is a private sql area and are 2 types implict cursor and explicit.implict cursor are predefined and explicit cursor defined by the programmer.

Question : What is use of a cursor variable? How it is defined?

A cursor variable is associated with different statements at run time, which can hold different values at run time. Static cursors can only be associated with one run time query. Acursor variable is reference type (like a pointer in C).
Declaring a cursor variable:
TYPE type_name IS REF CURSOR RETURN return_type type_name is the name of the reference type,return_type is a record type indicating the types of the select list
that will eventually be returned by the cursor variable

Question : Difference between an implicit & an explicit cursor.

     PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL
statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

---

The implicit cursor is used to process INSERT, UPDATE,DELETE, and SELECT INTO statements. During the processing ofan implicit cursor,Oracle automatically performs the OPEN,FETCH, and CLOSE operations.

Where as in explicit cursors,the process of its working isdone in 4 steps namely DECLARE a cursor,OPEN a cursor,FETCH from cursor and CLOSE a cursor.

IMPLICT CURSOR:- Automatically porvide by oracle which perform DML statements. queries returns only one row.

EXPLICT CURSOR:- Defined by user. queries returns more than rows.

Explicit Cursor:-We are not able to Handle NO_DATA_FOUND Exception.

Implicit Cursor:-We are able to Handle NO_DATA_FOUND Exception.

According to pl/sql where the data is temporarily stored is called cursor.
The Implicit cursor is created by Oracle engine itself while Explicit cursor is created by programmer by mean of programming statement.
The Exception can be handled in the Implicit cursor whereas Explicit cursor is unable to handle Exceptions.

There are properties of cursor

%open - check cursor is open or not.
%found - Row found out or not.
%not found - yes if no row found.
%row count - Rows affected by statement.

There are 4 steps of working of cursor named DECLARE-cursor is declared, OPEN- cursor is opened, FETCH- data fetched, CLOSE- close the cursor.

What is a view?

Question : What is a view?

                           View is a logical table which based on one or more than one table or anather view. The table(s) on which view creats are called as base table(s). It does not have it's own data rather it shows the data from the base table(s).

                           View is a virtual table based on the result set of an SQL statement.It contains rows and columns just like real table.