Cursors in Oracle

Cursors in pl/sql:-

Scenario:-
 

If you want to select data from a table or tables, and based on some condition you need to manipulate that data. You can achieve this by using cursors. It will store data fetch by sql queries in a private area and that data can then manipulate accordingly. For this, you must declare the cursor, open that cursor, fetch the records and finally you should close the cursor.

What is pl/sql Cursors?

A cursor is a temporary work area, created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it. This temporary work area is used to store the data retrieved from the database, and manipulate this data in executable part of the block. A cursor can hold more than one row, but can process only one row at a time. The set of records cursor’s currently holds is called as active set.

There are two types of Cursors:

1)    Implicit Cursor

2)    Explicit Cursor

Implicit Cursors:

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Attributes of Implicit Cursors:

1)    SQL%FOUND

2)    SQL%NOTFOUND

3)    SQL%ROWCOUNT
 

Explicit Cursors:
 

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.

1)    CURSOR_NAME%FOUND

2)    CURSOR_NAME %NOTFOUND

3)    CURSOR_NAME %ROWCOUNT

4)    CURSOR_NAME %ISOPEN

Steps while accessing cursors:
 

1)    DECLARE

2)    OPEN

3)    FETCH

4)    CLOSE
 

Let’s have an implicit cursor Example:-

When you query data on the table implicitly cursor are declare implicitly.

Let’s have an explicit cursor example:-

What is cursor with parameter?

We can pass parameter in the cursor and can use them in the query. We need to pass only datatype and not length of the parameter.

Syntax of the cursor with parameter is:-

CURSOR CURSOR_NAME (PARAMETER_LIST)

IS

 SELECT_STATEMENT;

Let’s have an example:-

DECLARE

CURSOR CUR_1 (C_EMPNO NUMBER)

IS

  SELECT EMPNO

  FROM EMPLOYEE

WHERE EMPNO=10;

V_EMPNO NUMBER;

V_EMPNO_1 NUMBER;

BEGIN

OPEN CUR_1(V_EMPNO);

FETCH CUR_1 INTO V_EMPNO_1;

DBMS_OUTPUT.PUT_LINE('EMPLOYEE NUMBER '||V_EMPNO_1);

END;

Read Also:-