PL/SQL Cursor with Parameters

Summary: in this tutorial, you will learn how to use the PL/SQL cursor with parameters to fetch data based on parameters.

An explicit cursor may accept a list of parameters. Each time you open the cursor, you can pass different arguments to the cursor, which results in different result sets.

The following shows the syntax of a declaring a cursor with parameters:

CURSOR cursor_name (parameter_list) 
IS
cursor_query;

In the cursor query, each parameter in the parameter list can be used anywhere which a constant is used. The cursor parameters cannot be referenced outside of the cursor query.

To open a cursor with parameters, you use the following syntax:

OPEN cursor_name (value_list);

In this syntax, you passed arguments corresponding to the parameters of the cursor.

Cursors with parameters are also known as parameterized cursors.

PL/SQL cursor with parameters example

The following example illustrates how to use a cursor with parameters:

DECLARE
    r_product products%rowtype;
    CURSOR c_product (low_price NUMBER, high_price NUMBER)
    IS
        SELECT *
        FROM products
        WHERE list_price BETWEEN low_price AND high_price;
BEGIN
    -- show mass products
    dbms_output.put_line('Mass products: ');
    OPEN c_product(50,100);
    LOOP
        FETCH c_product INTO r_product;
        EXIT WHEN c_product%notfound;
        dbms_output.put_line(r_product.product_name || ': ' ||r_product.list_price);
    END LOOP;
    CLOSE c_product;

    -- show luxury products
    dbms_output.put_line('Luxury products: ');
    OPEN c_product(800,1000);
    LOOP
        FETCH c_product INTO r_product;
        EXIT WHEN c_product%notfound;
        dbms_output.put_line(r_product.product_name || ': ' ||r_product.list_price);
    END LOOP;
    CLOSE c_product;

END;
/

In this example:

• First, declare a cursor that accepts two parameters low price and high price. The cursor retrieves products whose prices are between the low and high prices.
• Second, open the cursor and pass the low and high prices as 50 and 100 respectively. Then fetch each row in the cursor and show the product’s information, and close the cursor.
• Third, open the cursor for the second time but with different arguments, 800 for the low price and 100 for the high price. Then the rest is fetching data, printing out product’s information, and closing the cursor.

PL/SQL parameterized cursor with default values

A parameterized cursor can have default values for its parameters as shown below:

CURSOR cursor_name (
    parameter_name datatype := default_value, 
    parameter_name datatype := default_value, 
    ...
) IS 
    cursor_query;

If you open the parameterized cursor without passing any argument, the cursor will use the default values for its parameters.

The following example shows how to use a parameterized cursor with default values.

DECLARE
    CURSOR c_revenue (in_year NUMBER :=2017 , in_customer_id NUMBER := 1)
    IS
        SELECT SUM(quantity * unit_price) revenue
        FROM order_items
        INNER JOIN orders USING (order_id)
        WHERE status = 'Shipped' AND EXTRACT( YEAR FROM order_date) = in_year
        GROUP BY customer_id
        HAVING customer_id = in_customer_id;
        
    r_revenue c_revenue%rowtype;
BEGIN
    OPEN c_revenue;
    LOOP
        FETCH c_revenue INTO r_revenue;
        EXIT    WHEN c_revenue%notfound;
        -- show the revenue
        dbms_output.put_line(r_revenue.revenue);
    END LOOP;
    CLOSE c_revenue;
END;

In this example, we declared a parameterized cursor with default values. When we opened the cursor, we did not pass any arguments; therefore, the cursor used the default values, 2017 for  in_year and 1 for in_customer_id.

Now, you should know how to use a PL/SQL cursor with parameters to fetch data from the database tables.