Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

plsql

Attributes of CURSOR in Oracle PLSQL

by

The attributes of cursor in Oracle PLSQL helps us to determine the state or status of a cursor.

Below is the list of attributes of cursors that we can use in Oracle PLSQL to determine the status of cursor.

Attribute Explanation
%ISOPEN If the cursor is open the %ISOPEN returns TRUE, else returns FALSE.
%FOUND Returns INVALID_CURSOR if the cursor is declared but has been closed or not opened.

Returns NULL if no fetch has been executed after the opening of cursor.

Returns TRUE if fetch executed has been successful.

Returns FALSE if there is no row has been returned.
%NOTFOUND Returns INVALID_CURSOR if the cursor is declared but has been closed or not opened.

Returns NULL if no fetch has been executed after the opening of cursor.

Returns FALSE if fetch executed has been successful.

Returns TRUE if there is no row has been returned.
%ROWCOUNT Returns INVALID_CURSOR if the cursor is declared but has been closed or not opened.

Returns the number of rows fetched.

Let’s understand, how to use cursor attributes from the help of the below PLSQL function:

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

The statement IF cur_salary%NOTFOUND; shows us the use of %NOTFOUND attribute of cursor cur_salary.

CLOSE CURSOR in Oracle PLSQL

by

The CLOSE CURSOR in Oracle PLSQL is used when we have finished processing the records of the CURSOR.

Syntax to use CLOSE CURSOR in Oracle SQL / PLSQL is:

CLOSE cursor_name;

Let’s understand, how to CLOSE CURSOR from the help of the below Oracle PLSQL function

CREATE OR REPLACE FUNCTION GetSalary
IS
cur_sal NUMBER;
CURSOR cur_salary
IS
  SELECT salary
  FROM employee;

BEGIN
FETCH STATEMENT cur_salary;
FETCH cur_salary IN cur_sal;
IF cur_salary%NOTFOUND THEN
      cur_sal := 100000;
END IF;

CLOSE cur_salary;
END;

The statement CLOSE cur_salary; will be used to close the cursor cur_salary.

FETCH Statement for CURSOR in Oracle PLSQL

by

FETCH statement in Oracle PLSQL is used to access the records from the CURSOR which has been previously opened.

Oracle PSQL Syntax to use FETCH CURSOR statement is:

FETCH cursor_name INTO ;

Let’s understand, how to use FETCH Statement in a cursor from the help of the below PLSQL function:

CREATE OR REPLACE FUNCTION GetSalary
  IS
  cur_sal NUMBER;

CURSOR cur_salary
IS
  SELECT salary
  FROM employee;

BEGIN
  FETCH STATEMENT cur_salary;
  FETCH cur_salary IN cur_sal;
  IF cur_salary%NOTFOUND THEN
        cur_sal := 100000;
  END IF;

CLOSE cur_salary;
END;

The line FETCH cur_salary IN cur_sal is used to FETCH the records in the cursor.

Open CURSOR in Oracle PLSQL

by

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
  SELECT salary
  FROM employee;
BEGIN
OPEN cur_salary;
FETCH cur_salary IN cur_sal;
IF cur_salary%NOTFOUND THEN
      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.

Declare CURSOR in Oracle PLSQL

by

Oracle PLSQL terms a CURSOR as a memory area, which contains the records or results fetched by an SQL SELECT Statement.

Oracle PLSQL syntaxes to declare or Create CURSOR are:

1. Creating PLSQL Cursor Without Parameters:

CURSOR cursor_name
IS
SELECT_Statement;

2. Creating Cursor With Parameter:

CURSOR cursor_name(parameter_list)
IS
SELECT_Statement;

3. Creating Cursor in Oracle PLSQL With Return Type:

CURSOR cursor_name
RETURN field%ROWTYPE
IS
SELECT_Statement;

Let’s understand each type of cursor with example and usage.

Exmaple 1. Creating Oracle PLSQL Cursor without parameter. 

CURSOR cur_salary
IS
  SELECT salary
  FROM employee;

The above PLSQL CURSOR will SELECT all the records / rows of the ‘salary’ column from the ‘employee’ table.

Using cursor without parameter in an Oracle PLSQL Function: 

CREATE OR REPLACE FUNCTION GetSalary
IS
cur_sal NUMBER;

CURSOR cur_salary
IS
  SELECT salary
  FROM employee;

BEGIN
  OPEN cur_salary;
  FETCH cur_salary IN cur_sal;

  IF cur_salary%NOTFOUND THEN
    cur_sal := 100000;
  END IF;

  CLOSE cur_salary;

END;

Example 2. Creating Oracle PLSQL Cursor With Parameters 

CURSOR cur_salary(emp_id IN NUMBER)
IS
  SELECT salary
  FROM employee
  WHERE employee_id = emp_id;

The above PLSQL CURSOR will SELECT the record / row of the ‘salary’ column from the ‘employee’ table based on the passed parameter ‘employee_id’.

Using PLSQL Cursor with parameter in an Oracle PLSQL Function: 

CREATE OR REPLACE FUNCTION GetSalary
IS
cur_sal NUMBER;

CURSOR cur_salary(emp_id IN NUMBER)
IS
  SELECT salary
  FROM employee
  WHERE employee_id = emp_id;

  BEGIN
  OPEN cur_salary;
  FETCH cur_salary IN cur_sal;

  IF cur_salary%NOTFOUND THEN
    cur_sal := 100000;
  END IF;

  CLOSE cur_salary;
END;

The above PLSQL CURSOR will SELECT the record / row of the ‘salary’ column from the ‘employee’ table based on the passed parameter ‘employee_id’.

Example 3. Creating Oracle PLSQL Cursor with Return Type: 

CURSOR cur_language
RETURN lang_table%ROWTYPE
IS
  SELECT *
  FROM lang_table
  WHERE language = 'English';