Declare CURSOR in Oracle PLSQL

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';