• Skip to primary navigation
  • Skip to main content

Tech Honey

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

  • Home
  • HTML
    • Tags in HTML
    • HTML Attributes
  • OA Framework
    • Item in OAF
    • Regions in OAF
    • General OAF Topics
  • Oracle
    • statement
    • function
    • clause
    • plsql
    • sql
You are here: Home / Oracle / plsql / Declare CURSOR in Oracle PLSQL

Declare CURSOR in Oracle PLSQL

December 1, 2012 by techhoneyadmin

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

Filed Under: plsql Tagged With: CURSORPLSQL, how to create cursor in oracle plsql, oracle sql declare cursor syntax and example, oracle sql plsql declare cursor

Copyright © 2023 · Parallax Pro on Genesis Framework · WordPress · Log in