Open CURSOR in Oracle PLSQL

OPEN CURSOR in Oracle PLSQL is used to open a cursor before starting to access the records or results fetched. Oracle OPEN Cursor statement allows us to use the records fetched by the cursor in a PLSQL Function, package or procedure.

Oracle PLSQL Syntax of OPEN CURSOR is:

OPEN cursor_name;

Let’s understand, how to use OPEN Cursor with the help of the below Oracle PLSQL function code:

CREATE OR REPLACE FUNCTION GetSalary
IS
cur_sal NUMBER;
CURSOR cur_salary
IS
<%%KEEPWHITESPACE%%>  SELECT salary
<%%KEEPWHITESPACE%%>  FROM employee;
BEGIN
OPEN cur_salary;
FETCH cur_salary IN cur_sal;
IF cur_salary%NOTFOUND THEN
<%%KEEPWHITESPACE%%>      cur_sal := 100000;
END IF;
CLOSE cur_salary;
END;

The code OPEN cur_salary is used to open a cursor in plsql.

We must also make sure that once we open a cursor we have to close the cursor using CLOSE CURSOR statement.

Tagged , , , , , , , , , , , , . Bookmark the permalink.