PL/SQL Parameters   «Prev  Next»

Lesson 4 Using parameters with cursors
ObjectiveIdentify correct syntax for using parameters with cursors.

Using Parameters with Cursors

You can specify parameters for cursors in a similar way that you define them for a procedure.
This allows you to pass parameters to a cursor when you open the cursor.
  • PL/SQL Parameter Syntax Modes
    Parameter syntax 
    CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name {IN | OUT | IN OUT} datatype
  [{:= | DEFAULT} value ...) ]
  BEGIN
    Procedure body
  END;

    Each location in the table below is designated by a rectangle in the diagram above.
    Location 1 Choose one of these three modes: IN, OUT, or IN OUT.
    Location 2 Specify a datatype, such as NUMBER, VARCHAR2, DATE, and BOOLEAN. Do not place a length on the datatype.
    Location 3 Optional default value can be coded: = 'value' or DEFAULT 'value'
    Location 4 Any incoming parameter that has a default value can be omitted from the call. To use positional method, however, you must list parameters with default values last, so that values listed in the call are assigned to the first (non-default) parameters.
    Location 5 Example of a procedure that has two parameters: one IN, and one OUT.
    Location 6 Example of a PL/SQL block that calls the above procedure. Two local variables are declared first.

Examine the following series of images to see how the syntax works and learn how to open a cursor that uses parameters in PL/SQL.

Cursors with Parameters in Oracle PL/SQL

1) Here is the syntax of a cursor containing parameters 1 
CURSOR cursor_name
[(parameter_name {IN} datatype [ {:= | DEFAULT} VALUE]...)]
IS
  QUERY
1) Here is the syntax of a cursor containing parameters. The primary difference between defining parameters for a cursor and parameters for procedures is that cursors can only use IN parameters.

2) Here is an example of a cursor that has two parameters. 
DECLARE
.....
CURSOR READ_CUSTOMER
(I_CITY IN NUBMER,
I_STATE VARCHAR2 := 'CA')
AS
SELECT FIRSTNAME, LASTNAME
FROM CUSTOMER 
WHERE CUST_ID = I_CITY
AND STATE = I_STATE
...
BEGIN
...
END;
END;

2) Here is an example of a cursor that has two parameters. The second parameter has a default value.
The two parameters are used in the query in the cursor.

3) Continuing with the example, this screen shows you how to open the cursor while specifying values for the parameters. 
DECLARE
.....
CURSOR READ_CUSTOMER
(I_CITY IN NUMBER,
I_STATE VARCHAR2 := 'CA')
AS
SELECT FIRSTNAME, LASTNAME
FROM CUSTOMER 
WHERE CUST_ID = I_CITY
AND STATE = I_STATE
...
BEGIN
...
END;
END;
3) Continuing with the example, this screen shows you how to open the cursor while specifying values for the parameters. (The parameters could have been specified with variables, but I used literal values just to simplify the example) The same three methods of specifying parameters (position, assignment, and mixture) you saw demonstrated for procedure parameters can be used for cursor parameters. This screen uses the first (most common) method

4) This screen show how you can omit the last parameter if you desire. 
DECLARE
.....
CURSOR READ_CUSTOMER
(I_CITY IN NUMBER,
I_STATE VARCHAR2 := 'CA')
AS
SELECT FIRSTNAME, LASTNAME
FROM CUSTOMER 
WHERE CUST_ID =I_CITY 
AND STATE = I_STATE;
.....
BEGIN
...
OPEN READ_CUSTOMER('San Francisco');
4) This screen shows how you can omit the last parameter if you desire. This is possible because the second parameter has a default value defined. Refer to the Slide Show in the previous lesson if you want to see the other two methods for specifying parameters.

Generalizing Cursors with Parameters

I do not want to write a separate cursor for each category, that is definitely not a data-driven approach to programming. Instead, I would much rather be able to change the joke cursor so that it can accept different categories and return the appropriate rows. The best (though not the only) way to do this is with a cursor parameter 
PROCEDURE explain_joke (main_category_in IN joke_category.category_id%TYPE)
IS
/*
|| Cursor with parameter list consisting of a single
|| string parameter.
*/
CURSOR joke_cur (category_in IN VARCHAR2)
IS
SELECT name, category, last_used_date
FROM joke
WHERE category = UPPER (category_in);
joke_rec joke_cur%ROWTYPE;
BEGIN
/* Now when I open the cursor, I also pass the argument */
OPEN joke_cur (main_category_in);
FETCH joke_cur INTO joke_rec;

I added a parameter list after the cursor name and before the IS keyword. I took out the hardcoded "HUSBAND" and replaced it with "UPPER (category_in)" so that I could enter "HUSBAND”, “husband”, or "HuSbAnD" and the cursor would still work. Now when I open the cursor, I specify the value I want to pass as the category by including that value (which can be a literal, a constant, or an expression) inside parentheses. At the moment the cursor is opened, the SELECT statement is parsed and bound using the specified value for category_in. The result set is identified, and the cursor is ready for fetching.
The key reason for using parameters in a cursor is to help you write clear code. It is obvious to the casual reader of your code that you are passing variables X, Y, and Z to the cursor if the OPEN command contains those three variables as parameters.
The next lesson concludes this module.

Procedures Functions Cursors Parameters - Quiz

Click the Quiz link below to answer a few questions about parameters in procedures, functions, and cursors.
Procedures Functions Cursors Parameters - Quiz

SEMrush Software