Using Explicit Cursors  «Prev 

Modes for Cursor Parameters in PL/SQL

There are two modes for cursor parameters, in and out.
  1. With the in mode, the parameter value is passed in to the cursor.
  2. With the out mode, the parameter value is passed out of the cursor.
However, you cannot use the in out mode for cursor parameters.
When a parameter is defined as incoming, its values are passed in when the cursor is defined. When a parameter is defined as outgoing, as the cursor execution is completed, the value of the parameter is sent out of the cursor. This happens when a cursor exists within a package. You will learn more about packages in the third course in this series.

Cursor Parameter Modes

The syntax for cursor parameters is very similar to that of procedures and functions, with the restriction that a cursor parameter can be an IN parameter only. You cannot specify OUT or IN OUT modes for cursor parameters.
The IN and IN OUT modes are used to pass values out of a procedure through that parameter. This does not make sense for a cursor. Values cannot be passed back out of a cursor through the parameter list. Information is retrieved from a cursor only by fetching a record and copying values from the column list with an INTO clause.

Oracle PL/SQL Programming


Your program stores values in variables and constants. As the program executes, the values of variables can change, but the values of constants cannot. You can declare variables and constants in the declarative part of any PL/SQL block, subprogram, or package.


  1. allocate storage space for a value,
  2. specify its datatype, and
  3. name the storage location so that you can reference it.
Some examples follow:

birthday DATE;
emp_count SMALLINT := 0;

The first declaration names a variable of type DATE. The second declaration names a variable of type SMALLINT and uses the assignment operator to assign an initial value of zero to the variable. The next examples show that the expression following the assignment operator can be arbitrarily complex and can refer to previously initialized variables:
pi REAL := 3.14159;
radius REAL := 1;
area REAL := pi * radius**2;

By default, variables are initialized to NULL, so it is redundant to include ":= NULL" in a variable declaration.