PL/SQL Parameterized Cursors

PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features. With PL/SQL, you can fetch data from the table, add data to the table, make decisions, perform repetitive tasks, and handle errors.PL/SQL supports SQL queries. PL/SQL contains declaration block, begin block, exception block, and end block. Declare and exception blocks are optional.

In this article, we will explore PL/SQL Cursors and their parameters, providing insights into the declaration of explicit cursors and the step-by-step process involved. The focus will then shift to the dynamic capabilities of PL/SQL Cursors with Parameters, demonstrating how to create adaptable SQL queries.

What Are PL/SQL Cursors?

Oracle uses a special memory space called a context area for storing and retrieving information. The context area contains all the details related to the database. The cursor is the virtual pointer to the context area, in the database. The cursor helps you to process through the rows one by one. There are two types of cursors Implicit cursor and Explicit cursor.

• Implicit Cursor: In the implicit cursor SELECT INTO, INSERT, UPDATE, and DELETE queries are used, without declaring the cursor with a cursor name in the declaration block.
• Explicit Cursor: In explicit cursor, the cursor is declared with a particular name to fetch or add data through the rows one by one. It is declared in the declaration section of the PL/SQL block.

How to Declare an Explicit Cursor in PL/SQL

To use an explicit cursor, follow these steps:

1. Declaration of cursor: The Cursor is declared in the declaration section of the PL/SQL block.

DECLARECURSOR cursor_name IS select_statement;

• cursor_name: Name assigned to the cursor.
• select_statement: The SQL query used to fetch data.

2. Open the cursor: It helps to allocate the memory for the cursor from the context area. It is declared in the BEGIN section .

BEGINOPEN cursor_name;

3. Fetch the cursor: It is used to retrieve the data from the table into a variable. It helps to access rows at a time. It is also declared in the BEGIN section.

BEGINFETCH cursor_name INTO variable_list;

4. Close the cursor: As work associated with a cursor is completed, memory allocated is released.

CLOSE cursor_name;

PL/SQL Cursors with Parameters

The cursor can be declared with the parameter or without the parameter. It can have any number of parameters as per requirement.Cursors with Parameters are used to work with particular data. Parameters are used to create reusable and adaptable code. Explicit cursors may be declared with parameters. The parameter contains a variable and its datatype. The parameter can have a default value associated with a variable.

Syntax:

DECLARE
declare variables;
              --create a cursor with parameter;
 BEGIN OPEN cursor; 
FETCH cursor;
              -- process the rows
 CLOSE cursor; 
END;

Example of a Parameterized Cursor

GFG cursor is initialized with a parameter to retrieve the Id, name, and rank of Geek from the Geeks Table. The requested data must satisfy the specified condition.

SET SERVEROUTPUT ON;
DECLARE
   CURSOR GFG (Min_rank NUMBER) IS
      SELECT Id, name, rank
      FROM Geeks
      WHERE rank > Min_rank;
--  Declare variables 
   cur_id Geeks.Id%TYPE;
   cur_name Geeks.name%TYPE;
   cur_rank Geeks.rank%TYPE;
BEGIN
 -- Open and fetch data using the cursor
   OPEN GFG(951);
   LOOP
      FETCH GFG INTO cur_id, cur_name, cur_rank;
      EXIT WHEN GFG%NOTFOUND;
-- Process fetched data
      DBMS_OUTPUT.PUT_LINE('ID: ' || cur_id || ', Name: ' || cur_name || ', Rank: ' || cur_rank);
-- Close the loop
   END LOOP;
-- Close the cursor
   CLOSE GFG;
END;

Output:

Table: Establishing a table named Geeks and adding data to it.

Output:

Explanation:

SET SERVEROUTPUT ON is used to display output from DBMS_OPTPUT.PUT_LINE. GFG cursor and variables are declared in the declaration block. The parameter indicates the minimum required rank. The BEGIN keyword is used to start the execution of code. The cursor is opened using the OPEN keyword and data is fetched repeatedly from the table using the LOOP keyword.

Data from the table is checked against the condition mentioned in the cursor. DBMS_OUTPUT.PUT_LINE to display the data that satisfies the condition. END LOOP breaks the loop and the cursor is closed using the CLOSE keyword. The END keyword is used to end the execution.

PL/SQL Parameterized Cursor with Default Value

Default values can be passed in the parameterized cursor. If default values are passed in the parameterized cursor in the DECLARE block, the cursor can be called without argument in the BEGIN block if default values are to be used. If arguments are mentioned then they overwrite the default value.

Syntax:

DECLARE
declare variables;
       create a cursor with default value for parameter;
 BEGIN OPEN cursor; 
FETCH cursor;
       process the rows;
 CLOSE cursor; 
END;

Example of PL/SQL Parameterized Cursor with Default Value

GFG cursor is initialized with a default value for the parameter to retrieve the Id, name, and rank of Geek from the Geeks Table. The requested data must satisfy the specified condition mentioned in the cursor.

SET SERVEROUTPUT ON;
DECLARE
--default value for the parameter
   CURSOR GFG (Min_rank NUMBER :=951) IS
      SELECT Id, name, rank
      FROM Geeks
      WHERE rank > Min_rank;
--    Declare variables 
   cur_id Geeks.Id%TYPE;
   cur_name Geeks.name%TYPE;
   cur_rank Geeks.rank%TYPE;
BEGIN
    DBMS_OUTPUT.PUT_LINE('PL/SQL parameterized cursor with default value');
   -- Open and fetch data using the cursor with no argument
   OPEN GFG;
   LOOP
      FETCH GFG INTO cur_id, cur_name, cur_rank;
      EXIT WHEN GFG%NOTFOUND;
      -- Process fetched data
      DBMS_OUTPUT.PUT_LINE('ID: ' || cur_id || ', Name: ' || cur_name || ', Rank: ' || cur_rank);
-- Close the loop
   END LOOP;
   -- Close the cursor
   CLOSE GFG;
END;

Output of Parameterized Cursor with Default Value:

Explanation: The example mentioned is the same as the one used earlier. In this example, default values are used in the parameterized cursor. In DECLARE block cursor is defined with default value for the parameter . The cursor is called without argument in the BEGIN section.

Important Points About PL/SQL Parameterized Cursors

• Explicitly close cursors to free up memory resources.
• Use the %NOTFOUND attribute to exit loops when no more rows are available.
• PL/SQL Cursors with Parameters allows for specific actions to be taken based on parameterized cursor outcomes.
• You can adapt the cursor's behavior based on different input values.