Cursor in oracle database

[Ainsley]

Sponsored Links

Hello sir,

I have taken an option in the oracle database course the DBA option. This option is not so related to developing application.The DBA is not responsible for these thing it's the task of development team.But I need to know few things about the CURSOR concept in oracle database.At some points, The DBA also need to know but not necessary. So please let me know about the cursor in database.

Thanks.

[Modifier]

Cursor in oracle database :

To execute the SQL statement,some reserve area is allocated .The PL/SQL allows you for naming. A cursor in the context area acts as a handle or pointer. The PL/SQL program are designed to control over the context area using Cursor. Cursor shows a structure in the memory and is slightly unique from cursor variable.

During the creation and declaration of a cursor,a pointer variable is assigned to you,which doesn't point for any object.When the cursor is being opened the memory is assigned and the cursor structure is created.Then after cursor variable points to the cursor.

[MindSpace]

The implicit cursors :

The declaration of implicit cursors performed during SQL queries returning single row from the PL/SQL block.The implicit cursors are constructed through the simple SELECT statements which resides between the BEGIN and END section of PL/SQL Block.It is easy to perform the code.

The cursor are capable to retrieve exactly one row at a time.The commonly occurred exceptions regarding the implicit cursor areNO_DATA_FOUND or TOO_MANY_ROWS. The syntax would be as follows :

SQL> SELECT emp_name, salary  INTO Enm, Esal FROM EMP WHERE EMPNO = 4439;

Where the Emp_name and salary are columns of the EMP table and Enm and Esal are variables being used to store the result fetched by the query.

[opaper]

Explicit Cursors :

The explicit cursor are used when a query capable to return more than one rows for a query.A set of rows is fetched by the query is treated as active set.The space occupied by the active set is dependent on search criteria defined by SELECT statement.

The syntax would be as follows :

CURSOR <cursor-name> IS <select statement>

And the sample code to construct the Explicit Cursor would be as explained below:

DECLARE
CURSOR e_cursor IS SELECT emp_name FROM EMPLOYEE;
BEGIN
----
---
END;

[Reegan]

Attributes of Explicit Cursor :

The Cursor defined by the user has at least four attributes.There are some useful attributes are being used to define and investigating on cursor.The attributes are explained below :

• %NOTFOUND - Its follow the Boolean attribute in true or false,when there are no rows left in the cursor to fetch it activates.
• %FOUND - It activates the Boolean variable and evaluates to true if the last fetch comes succeeded.
• %ROWCOUNT - It’s a numeric attribute and counts the number of rows which is fetched by the cursor earlier.
• %ISOPEN - which is a boolean variable and shows the status of the cursor.

[Zecho]

Using Cursor For Loop :

It is being used to process multiple records. there are 2 benefits with this type of cursor.

1 - It implicitly declares a %ROWTYPE variable and implement it as LOOP index.
2- It performs the opening and closing automatically when records are being fetched and closes when fetching comes to complete.

SQL> Declare 
       Cursor cur1 IS select Emp_name,salary from EMP;
BEGIN 
For emp_rec IN cur1 
LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name||' ' ||emp_rec.salary);
END LOOP;
END;