Oracle Tutorial

  • Home
  • Start Here
  • Basics
  • Advanced
    • Oracle View
    • Oracle Index
    • Oracle Synonym
    • Oracle Sequence
    • Oracle Administration
  • PL/SQL
  • Functions
    • Aggregate Functions
    • Analytic Functions
    • Comparison Functions
    • Date Functions
    • String Functions
  • API
    • Python Oracle
Home / PL/SQL Tutorial / PL/SQL Cursor with Parameters

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.

  • Was this tutorial helpful?
  • YesNo
Previous PL/SQL Cursor FOR LOOP
Next PL/SQL Cursor Variables with REF CURSOR

PL/SQL Getting Started

  • What is PL/SQL
  • PL/SQL Anonymous Block
  • PL/SQL Data Types
  • PL/SQL Variables
  • PL/SQL Comments
  • PL/SQL Constants

PL/SQL Conditional Control

  • PL/SQL IF THEN
  • PL/SQL CASE
  • PL/SQL GOTO
  • PL/SQL NULL Statement

PL/SQL Loops

  • PL/SQL LOOP
  • PL/SQL FOR LOOP
  • PL/SQL WHILE Loop
  • PL/SQL CONTINUE

PL/SQL Select Into

  • PL/SQL SELECT INTO

PL/SQL Exception Handlers

  • PL/SQL Exception
  • PL/SQL Exception Propagation
  • PL/SQL RAISE Exceptions
  • RAISE_APPLICATION_ERROR

PL/SQL Records

  • PL/SQL Record

PL/SQL Cursors

  • PL/SQL Cursor
  • PL/SQL Cursor FOR LOOP
  • PL/SQL Cursor with Parameters
  • PL/SQL Updatable Cursor

PL/SQL Procedures & Functions

  • PL/SQL Procedure
  • PL/SQL Function
  • PL/SQL Cursor Variables

PL/SQL Packages

  • PL/SQL Package
  • PL/SQL Package Specification
  • PL/SQL Package Body

PL/SQL Triggers

  • PL/SQL Triggers
  • PL/SQL Statement-level Triggers
  • PL/SQL Row-level Triggers
  • PL/SQL INSTEAD OF Triggers
  • PL/SQL Disable Triggers
  • PL/SQL Enable Triggers
  • PL/SQL Drop Triggers
  • Oracle Mutating Table Error

About Oracle Tutorial

OracleTututorial.com website provides Developers and Database Administrators with the updated Oracle tutorials, scripts, and tips.

Search

Recent Tutorials

  • Oracle Implicit Statement Results
  • Calling PL/SQL Stored Functions in Python
  • Calling PL/SQL Procedures in Python
  • Managing Transaction in Python
  • Deleting Data From Oracle Database in Python

Site Links

  • Oracle Books
  • About
  • Contact
  • Privacy Policy
  • Terms of Use

Copyright © 2021 Oracle Tutorial. All Rights Reserved.