sreemon

• Jan 4th, 2012

 

Every SQL statement executed by Oracle has a Private SQL area that contains info about SQL statement and the set of data returned.

In PL/SQL, a cursor is a name assigned to a specific
private SQL area of a specific SQL Statement.
There can be either, Static Cursor whose SQL statements is determined at compile time, or Dynamic Cursor, whose SQL statement is determined at runtime.

Note: Dynamic Cursors are implemented using Oracle built in package DBMS_SQL.

Implicit Cursors:
----------------
Any SQL statement that is executed directly or in an PL/SQL block i.e. execution section or in exception section, during which it is associated with a work area in memory of oracle (SGA). This is done using implicit cursor by Oracle.

We do not need to declare implicit cursor hence not be
opened, fetched and closed.

Explicit Cursors:
-----------------

They are the SELECT statement that is declared explicitly in the declaration section of current block or in a package
specification.

Further we can use open, fetch and close in execution section or exception section of the block or program to utilize declared cursor.

To use an explicit cursor we need to declare it in
declaration section of block or package specification.

There are three sub types of explicit cursors:

* Simple Cursor (without parameter)

CURSOR emp_cur IS
SELECT emp_id
FROM emp;

* Parameterized Cursor - That accepts arguments

CURSOR emp_cur (dept_in [IN] NUMBER) IS
SELECT emp_id
FROM emp
WHERE dept = dept_in ;
* Returning Cursor - A cursor header that contains
a return clause

CURSOR emp_cur (dept_in [IN] NUMBER) IS
RETURN emp%ROWTYPE
SELECT *
FROM emp;


Hope so, above paragraphs explain cursors very well and in details.

Amol Kulkarni

• Jun 13th, 2012

 

A cursor is a private sql area used to stored intermediate data from the sql statements.whenever an sql statement is fired an cursor get open indirectly which is called as implicit cursor and whenever an user want to process data based on some sql statements result he has to explicitly define a cursor known as explicit cursor, A simple cursor is where no parameters are used where as a parametrized cursor is a cursor where the query result are based explicitly on the parameters passed to the cursor

Code
DECLARE


 CURSOR C1(ENO NUMBER) IS


   SELECT * FROM EMP


   WHERE EMPNO=ENO;


BEGIN


 


  FOR I IN C1(&ENO) LOOP


   DBMS_OUTPUT.PUT_LINE(ENAME);


 END LOOP;


END;


 


 


 

Srinivasan Konar

• Aug 23rd, 2012

 

The difference between simple and parameterized cursor is as follows:

Simple Cursor - A cursor having a simple or a complex SQL query and which will have conditions based on given variables or hard-coded.

Parameterized Cursor - such types of cursors will accept parameters i.e. values of the where clause in the cursor query will depend on the passed parameters.

abinay

• Oct 5th, 2012

 

Cursors having some private area.which is used for store the data from sql statements.Normal cursors we don't have use any parameters.but parametrized cursors are used the parameters,which is executed based upon the parameter.

amala

• Jun 25th, 2015

 

Cursor is a private SQL area which is used to retrieve multiple rows from one or more tables. The cursor which uses parameter is called parameterised cursor. Based on the given parameter value
the cursor will execute.

Akash Mohan singh

• Dec 26th, 2017

 

A parameterized cursor are static cursor that can accept passed in parameter values when they are opened. Parameterized can only reference to its own parameter parameterized cursor cannot reference to local variable.

sandeep

• Feb 1st, 2018

 

In real time we are using more than one cursor in PL/SQL block. so in this we are sending one cursor values into another cursor so the receiving cursor must be parameterised cursor