Lesson 7	Using parameters with cursors
Objective	Identify correct syntax for using parameters with cursors.

Correct Syntax for Using Parameters with Cursors in Oracle PL/SQL

Oracle PL/SQL provides robust functionality to work with cursors, including the ability to pass parameters to them. Here is the authoritative syntax for using parameters with cursors in Oracle PL/SQL:

Declare the cursor: This is the first step, where you define the cursor and specify the parameters that it will accept. The syntax is as follows:
```
CURSOR cursor_name [(parameter_name datatype[, parameter_name datatype ...])] 
IS select_statement;
```
Here, 'cursor_name' is the name of your cursor, 'parameter_name' is the name of a parameter, 'datatype' is the data type of the parameter, and 'select_statement' is the SELECT statement that the cursor will execute.

Open the cursor: Once you have declared the cursor, you can open it using its parameters. Here is the syntax for that:

OPEN cursor_name [(argument[, argument ...])];

Here, 'cursor_name' is the name of your cursor, and 'argument' is a value that you are passing as a parameter.
To give you a concrete example, consider the following PL/SQL block:

DECLARE 
   CURSOR emp_cursor (p_department_id DEPARTMENTS.DEPARTMENT_ID%TYPE) IS 
      SELECT first_name, last_name 
      FROM employees 
      WHERE department_id = p_department_id;

   v_department_id DEPARTMENTS.DEPARTMENT_ID%TYPE := 30;
   v_first_name EMPLOYEES.FIRST_NAME%TYPE;
   v_last_name EMPLOYEES.LAST_NAME%TYPE;
BEGIN
   OPEN emp_cursor (v_department_id);

   LOOP
      FETCH emp_cursor INTO v_first_name, v_last_name;

      EXIT WHEN emp_cursor%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE (v_first_name || ' ' || v_last_name);
   END LOOP;

   CLOSE emp_cursor;
END;

In this PL/SQL block, 'emp_cursor' is a cursor that accepts one parameter 'p_department_id'. It's used to fetch the first and last names of employees from the 'employees' table where 'department_id' matches the value of 'p_department_id'. The 'OPEN' statement is used to open the cursor and pass the value of 'v_department_id' as an argument.
Remember, when you are finished with a cursor, you should always close it to free up the memory and resources that it's using.

Identify correct Syntax for PL/SQL Cursor Parameters

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. Look at the following Slideshow to see how the syntax works and learn how to open a cursor that uses parameters.

1) Here is the syntax of a cursor containing parameters.

2) Here is an example of a cursor that has two parameters.

3) Continuing with the example, this screen shows you how to open the cursor while specifying values for the parameters.

4) This screen shows how you can omit the last parameter if you desire.

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