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