Understanding Cursor in PL/SQL: Types, Importance, & More
Whether you’re a pro programmer or new to PL/SQL, grasping the concept of cursors is vital to maximizing the potential of your database applications. From performing complex operations to navigating through data rows with ease, cursors in PL/SQL provide a seamless gateway to operating data.
In this blog, we will explore cursor definitions in PL/SQL thoroughly, divulging their internal workings, and sharing practical knowledge that will empower you to confidently utilize these powerful tools.
Cursor Definition In PL/SQL
In PL/SQL, a cursor is a database object that allows you to retrieve and operate on the result set of a SELECT statement. Cursors provide a way to iterate through the rows returned by a query, one at a time, and perform various operations on the data.
The PL/SQL block utilizes a cursor to effectively control the context area’s functions. A cursor acts as a pointer to this memory zone, storing one or multiple rows returned by a given PL/SQL statement. Cursors can have names for reference purposes to be fetched in a program later on, and their rows are processed individually. To upskill your data management skills, consider taking an SQL training program to gain great command over SQL skills.
What is the Importance of Cursors in PL/SQL?
One of the essential tasks in any programming language is to perform actions by pointing to memory locations. In PL/SQL, this is achieved through cursors, which are crucial for executing various tasks such as naming and managing the memory area (context area), where SQL query results are saved. With cursors, we can access records individually, control them as needed, or display them on the console accordingly.
Explicit cursors in PL/SQL offer greater efficiency, better programmatic control, and lower vulnerability to data errors than Implicit ones, making them a valuable tool in PL/SQL programming.
Types of Cursors in PL/SQL
The PL/SQL programming language supports two kinds of cursors.
- Implicit Cursor
- Explicit Cursor
I. Implicit Cursors
An implicit cursor is a type of cursor that is generated by Oracle itself when DML statements such as INSERT, UPDATE, and DELETE are executed. Oracle offers some attributes through which programmers can perform operations on these cursors. Implicit cursors serve as an easy way for programmers to handle database transactions without requiring them to manually create cursors every time. The attributes offered by Oracle are as follows:
- %FOUND: This function returns a Boolean value of True if any DML statements, such as INSERT, UPDATE, and DELETE, affect single or multiple rows. Similarly, it returns True if any SELECT statement also returns single or multiple rows. If neither of these conditions are met, the function will return False.
- %NOTFOUND: The %NOTFOUND attribute operates in reverse to its counterpart, %FOUND. If a DML statement has not affected any rows or a SELECT statement does not return any results, then the %NOTFOUND attribute will be evaluated as True. Alternatively, it will return False when rows have been impacted by a DML statement or when the SELECT DML statement returns at least one result.
- %ISOPEN: Regarding Implicit cursors, Oracle shut off the cursor immediately following the SQL statement’s execution, resulting in a False return value.
- %ROWCOUNT: The function returns the rows affected by DML statements such as INSERT, UPDATE, and DELETE. It can also provide the count of rows returned from SELECT INTO statements in PL/SQL code.
Example of Implicit Cursors
Update the student Maths subject marks by 20 in the given table ‘student’ with column ‘subject.’
DECLARE
affected_rows number(4);
BEGIN
UPDATE students SET marks = marks+20 where subject = 'Maths';
IF sql%NOTFOUND THEN dbms_output.put_line ('No records of Maths subject are updated');
ELSIF sql%FOUND THEN affected rows: = affected rows%rowcount
dbms_output.put_line('Congrats ..Records Updated' || affected_rows);
END IF;
END;
/II. Explicit Cursor
An explicit cursor is a powerful tool that gives programmers greater control over context areas in PL/SQL programs. These cursors must be declared in a program’s declaration block and are particularly useful when working with SQL statements that produce multiple rows of results. To use an explicit cursor, many steps need to be implemented, including careful definition and initialization of the cursor within the program. If used correctly, explicit cursors can improve program performance and data accuracy.
1. Explicit Cursor Syntax
Cursor Cursor-Name
IS
Select-Statement;2. Steps to Utilize Explicit Cursors
Step 1: Cursor Declaration
This function aims to assign a name to the cursor or context area that will execute a select statement.
Syntax: CURSOR cursor_name IS SELECT statement;Step 2: Open Cursor
When opening the cursor, its memory is allocated, and then the cursor becomes accessible to retrieve records that are returned from the associated SQL statements.
Syntax: OPEN cursor_name;Step 3: Fetching Cursor
During this procedure, a single row is fetched sequentially. The statement SELECT is initiated and the retrieved rows are contained within the cursor or context area. These records are subsequently fetched and assigned to a pre-defined variable for further manipulation.
Syntax: FETCH cursor_name INTO variable;Step 4: Close Cursor
The final step in this process involves the closure of the cursor utilized earlier, freeing up memory for all rows that have been successfully retrieved.
Syntax: CLOSE cursor_name;Example of an Explicit Cursor
DECLARE variables;
records;
create a cursor;
BEGIN
OPEN cursor;
FETCH cursor;
process the records;
CLOSE cursor;
END;
Conclusion
The cursor in the PL/SQL programming language is a critical component, allowing developers to navigate and manipulate data within databases. An implicit cursor is automatically generated and suitable for straightforward operations, while explicit cursors offer more control for handling multiple rows. Becoming proficient in cursor usage enables developers to fine-tune their data manipulation techniques and improve the overall efficiency of their PL/SQL applications.
