In previous PL/SQL tutorial we saw how to create a simple explicit cursor. No doubt that explicit cursor has certain advantages over implicit cursor and can increase the efficiency of DML statements by giving more programmatic controls in user’s hands. Now let’s take a step ahead and learn how to create a parameterized explicit cursor a.k.a. cursor parameter.

Knowledge of cursor creation cycle and simple cursor creation is a must to understand the concept of cursor parameter thus I will highly suggest you to read the previous tutorial on Introduction to cursor and Simple Cursor Creation.

What is Parameterized cursor?

Unlike simple explicit cursor, parameterized cursors accept values as parameter. You specify the list of parameters separated by comma (,) while declaring the cursor and supply the corresponding argument for each parameter in the list while opening the cursor.

Definition:
Cursor parameter can be appropriately defined as an explicit cursor that accepts arguments from the user in the form of parameter.

Syntax of Parameterized Cursor in Oracle Database

CURSOR cur _ name (parameter list) IS SELECT statement;

Syntax of declaring a cursor parameter is pretty similar to that of the simple cursor except the addition of parameters enclosed in the parenthesis.

OPEN cur _ name (argument list)

You have to provide corresponding arguments for each parameter that are specified during the declaration. Rest of the steps are the same as that of the simple cursor.

There are few things which you have to take care of while specifying the parameters in your explicit cursor.

• In case of multiple parameters, always separate parameters and the corresponding arguments in the list from each other using comma (,).
• You can specify as many parameters as you need just make sure to include an argument in parameter list for each parameter when you open the cursor.
• While specifying a parameter during the declaration of the explicit cursor only specify the data type not the data width.

Some Wonderful Advantages of Parameterized Cursors

Makes the cursor more reusable

You can use a parameter and then pass different values to the WHERE clause each time a cursor is opened instead of hardcoding a value into the WHERE clause of a query to select particular information.

Avoids scoping problems

When you pass parameters instead of hardcoding the values, the result set for that cursor is not tied to a specific variable in a program or block. Therefore in case your program has nested blocks, you can define the cursor at a higher-level (enclosing) block and use it in any of the sub-blocks with variables defined in those local blocks.

When do we need a parameterized cursor?

You must be wondering when we need a cursor with parameters in our PL/SQL.

The simplest answer is whenever you need to use your cursor in more than one place with different values for the same WHERE clause of your SELECT statement.

If you can add something to this and have another idea for using a parameterized cursor. Then I am always open to listening to your thoughts do make sure to share it with me on my Facebook or twitter.

Example of Parameterized cursor.

SET SERVEROUTPUT ON;
DECLARE
  v_name VARCHAR2 (30);
  --Declare Cursor 
  CURSOR p_cur_RebellionRider (var_e_id VARCHAR2) IS 
  SELECT first_name FROM EMPLOYEES 
  WHERE employee_id < var_e_id;
BEGIN
  OPEN p_cur_RebellionRider (105); 
LOOP 
    FETCH p_cur_RebellionRider INTO v_name; 
    EXIT WHEN p_cur_RebellionRider%NOTFOUND; 
    DBMS_OUTPUT.PUT_LINE(v_name );  
  END LOOP;
  CLOSE p_cur_RebellionRider;
END;
/

You can watch the video tutorial on my YouTube channel for line by line explanation of the above code.

That’s it for this tutorial blog. If you want me to add something then do let me know on my social media.  Also make sure to share this blog on your social media. Thanks & have a great day!