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

Identify the 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're 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.

Cursors With Parameters

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