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.

PL/SQL Cursor

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.

Declaration of Explicit Cursor

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

DECLARE

CURSOR cursor_name IS select_statement from the table.

CURSOR is used to declare the cursor name like a function is used in JavaScript to declare a function.

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

BEGIN

OPEN cursor_name;

• 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 .

BEGIN

FETCH cursor_name INTO variable_list;

• 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 PL/SQL Cursors with Parameters

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:

Cursor with Parameter

Table:

Establishing a table named Geeks and adding data to it.

Table

Output:

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;

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.

Output of Parameterized Cursor with Default Value:

Output of Parameterized Cursor with default value

Conclusion

In conclusion, parameterized PL/SQL cursors offer a strong way to improve the adaptability and reuse of database operations. Developers can create dynamic and flexible SQL queries that facilitate efficient data retrieval and manipulation by utilizing parameters inside cursors. This feature is especially useful when working with different conditions or when different sets of data require the application of the same cursor logic.