Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle Declare Cursor

In Oracle, declare a cursor is a simple SELECT statement that is defined within the declaration section of your PLSQL code.

Oracle Cursor without Parameters Syntax

To declare a cursor without parameters in Oracle, use the following syntax:

Syntax

CURSOR cursor_name
IS
select_statement;

Parameters:

cursor_name: The name of cursor.

select_statement: The select statement.

Oracle Cursor without Parameters example

The following Oracle example shows how to declare cursor without parameters:

Example

CURSOR cur1
IS
  SELECT firstName
  FROM tblCustomerLoan
  WHERE working_hover = 6;

Note:

In the above example, the result set of this cursor is all firstName whose working_hover is equal to 6.

Oracle Cursor with parameters Syntax

To declare a cursor with parameters in Oracle, use the following syntax:

Syntax

CURSOR cursor_name (parameter_list)
IS
select_statement;

Parameters:

cursor_name: The name of cursor.

parameter_list: List of parameters.

select_statement: The select statement.

Oracle Cursor with Parameters example

The following Oracle example shows how to declare cursor with parameters:

Example

CURSOR cur1 (working_h IN varchar2)
IS
  SELECT firstName
  FROM tblCustomerLoan
  WHERE working_hover = working_h;

Note:

In the above example, the result set of this cursor is all firstName whose working_hover is equal to 6, passed to the cursor via the parameter.

Oracle Cursor with return Clause Syntax

To declare a cursor with a return clause in Oracle/PLSQL, use the following syntax:

Syntax

CURSOR cursor_name
RETURN field%ROWTYPE
IS
select_statement;

Parameters:

cursor_name: The name of cursor.

RETURN: The return value.

select_statement: The select statement.

Oracle Cursor with return Clause example

The following Oracle example shows how to declare cursor with return clause:

Example

CURSOR cur1
RETURN tblCustomerLoan%ROWTYPE
IS
  SELECT firstName
  FROM tblCustomerLoan
  WHERE working_hover = 6;

Note:

In the above example, the result set of this cursor is all columns from the "tblCustomerLoan" where the working_hover is 6.