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