Implementing Cursors with PL/SQL
This article includes types of cursors and you will also learn about implementation of cursors using PL/SQL.
The primary goal of the Oracle PL/SQL language is to make querying and modifying the contents of tables in a database as easy and efficient as possible. Of course, you’ll need to use the SQL language to access the table, and each time you’ll use a cursor to do your work. A cursor is a pointer to a private SQL area that stores information about processing a SELECT or data manipulation language (DML) statement (INSERT, UPDATE, DELETE, or MERGE). Cursor management for DML statements is handled by Oracle Database, but PL/SQL provides several ways to define and manipulate cursors to execute SELECT statements.
Table of contents
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
WHAT IS A CURSOR?
A cursor is a control structure in SQL that enables traversal over the rows in a result set. It is a way to retrieve data from a database and process it one row at a time, rather than retrieving all the data at once and then processing it.
When a SELECT statement is executed, the result set is stored in a cursor, which can then be used to fetch and process the rows one at a time. The Cursor acts as a pointer to the current row in the result set, allowing the developer to move forward and backwards through the result set.
In SQL, there are two types of cursors:
- Implicit cursors: These are automatically created by the database management system (DBMS) when a SELECT statement is executed without an explicit cursor.
- Explicit cursors: The developer creates these using the DECLARE CURSOR statement. They are used to retrieve and process data more fine-grained way and can be parameterized.
Cursors are commonly used in PL/SQL and T-SQL to fetch and process data from the database, but they can also be used in other languages with an SQL interface.
In SQL, a cursor is a mechanism that allows for the traversal and manipulation of the rows in a result set returned by a SELECT statement. A cursor can be used to retrieve data row-by-row and perform various operations on each row, such as updating or deleting the data. Cursors can also be used to process large result sets in smaller chunks rather than loading the entire result set into memory at once. Cursors are typically used in stored procedures and functions in SQL.
Read Later
Must Check: SQL Online Course and Certifications
You can also explore: SQL RIGHT JOIN – Example and Syntax
You can also explore: How to use GROUP BY in SQL?
TYPES OF CURSORS
1. IMPLICIT CURSORS: An implicit cursor is a cursor automatically created and managed by the database management system (DBMS) when certain SQL statements are executed. These statements include SELECT INTO, INSERT, UPDATE, and DELETE. The DBMS uses an implicit cursor to handle the rows affected by these statements, and it automatically opens, fetches, and closes the Cursor as needed.
Following is an example where an SQL statement creates an implicit cursor to handle the rows affected by the update statement:
UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = 'IT';
The implicit Cursor is used to locate the rows in the Employees table that have a Department value of ‘IT’ and update their Salary values by increasing them by 10%. The DBMS automatically opens the Cursor, fetches the rows that match the WHERE clause condition, performs the update operation, and then closes the Cursor.
It’s important to note that implicit cursors are created and managed by the DBMS, not directly by the user. Therefore, the user does not have direct control over the Cursor and cannot perform operations like FETCH and CLOSE. Also, It’s more efficient than an explicit cursor since you don’t need to declare and manage it.
More examples of implicit cursors in SQL are stated below:
1. SELECT INTO Statement:
SELECT COUNT(*) INTO @total_employees FROM Employees;
In the above example, the SELECT INTO statement creates an implicit cursor to handle the result set returned by the SELECT COUNT(*) statement. The Cursor retrieves the count of rows in the Employees table and stores it in the variable @total_employees.
2. INSERT Statement:
INSERT INTO Orders(OrderID, CustomerID, OrderDate) VALUES(123, 'C001', GETDATE());
The statement above creates an implicit cursor to handle the row that will be inserted into the Orders table. The Cursor inserts the new row with the specified values for OrderID, CustomerID, and OrderDate.
3. UPDATE Statement:
UPDATE Employees SET Salary = Salary * 1.1 WHERE Department = 'IT';
This statement creates an implicit cursor to handle the rows that will be updated in the Employees table. The Cursor is used to locate the rows with a Department value of ‘IT’, and update their Salary values by increasing them by 10%.
4. DELETE Statement:
DELETE FROM Customers WHERE Country = 'USA'
This statement creates an implicit cursor to handle the rows that will be deleted from the Customers table. The Cursor is used to locate the rows with a Country value of ‘USA’ and delete them.
In the examples above, the DBMS automatically opens the Cursor, performs the operation on the rows, and closes the Cursor. The user does not have to declare or manage the Cursor explicitly.
5. EXPLICIT CURSORS: An explicit cursor in SQL is a programming construct that allows for retrieving and manipulating rows from a result set one at a time. It is defined by the programmer and can be used to perform operations such as fetching, updating, or deleting rows.
Explicit cursors are created using the DECLARE statement and are typically used when the result set is too large to be handled efficiently with a SELECT statement or when the programmer needs to perform operations on each row in the result set in a specific order.
An explicit cursor has four basic operations:
- Open: The Cursor is opened, and the result set is populated.
- Fetch: The Cursor retrieves a single row from the result set and makes it available for processing.
- Close: The Cursor is closed, and the result set is no longer available for processing.
- Deallocate: The Cursor is deallocated, and the memory it uses is freed up.
Explicit cursors can be useful for performing complex data processing tasks but can also be a performance bottleneck. In most cases, it’s recommended to use a set-based operation like a join or a subquery instead of a cursor.
For more information you can also explore: How to use HAVING clause in SQL
You can also explore: Cross Join in SQL
Examples of explicit cursors in SQL are stated below
1.Declare a cursor and retrieve all rows from a table:
DECLARE cur CURSOR FOR SELECT * FROM Customers;
2. Open a cursor and fetch the first row:
OPEN cur;FETCH NEXT FROM cur;
3. Loop through all rows in the Cursor:
WHILE @@FETCH_STATUS = 0BEGIN FETCH NEXT FROM cur;END
3. Close the Cursor and deallocate memory:
CLOSE cur;DEALLOCATE cur;
The Cursor can be resource-intensive and slow down the performance of the database, so it should be used only when necessary.
IMPLEMENTATION OF CURSORS IN PL/SQL
Implementations of cursors in PL or Implementation in SQL, both of them are the same; only a few additional features are being added, which is equal to equivalent. Below are the implementation steps:
- DECLARE THE CURSOR: The Cursor is declared using the DECLARE statement, specifying the SELECT statement, which will be used to populate the result set.
DECLARE cursor cursor_name IS SELECT column_name FROM table_name;
- OPEN THE CURSOR: The Cursor is opened using the OPEN statement, which populates the result set and makes it available for processing.
OPEN cursor_name;
- FETCH ROWS FROM THE CURSOR: Rows are retrieved from the Cursor using the FETCH statement, which moves the Cursor to the next row in the result set and makes the row’s data available for processing.
FETCH cursor_name INTO variable_name;
- PROCESS THE FETCHED ROW: The data of the fetched row can be processed using the variable_name.
- CLOSE THE CURSOR: When all rows in the result set have been processed, the Cursor is closed using the CLOSE statement. This releases the resources associated with the Cursor.
<strong>CLOSE cursor_name;</strong>
- DEALLOCATE THE CURSOR: The Cursor is deallocated using the DEALLOCATE statement, which releases the memory used by the Cursor.
DEALLOCATE cursor;
It’s also possible to use a loop like “WHILE” or “FOR” to fetch all rows from the Cursor, and it’s important to close and deallocate the Cursor after the process is finished to avoid resource leaks.
PL/SQL allows you also to use Implicit Cursor, which is automatically created and managed by Oracle Database to process the SQL statement.
It’s worth noting that explicit cursors are resource-intensive and can slow down the performance of the database, so it’s good practice to use them only when it’s truly necessary and in an optimized way.
Also, PL/SQL provides some cursor attributes, such as %FOUND, %NOTFOUND, and %ROWCOUNT, that can be used to check the status of the Cursor after each fetched statement.
A detailed example of implementing cursors in PL/SQL is illustrated below-
DECLARE cursor emp_cursor IS SELECT * FROM employees; emp_record employees%ROWTYPE;BEGIN OPEN emp_cursor; LOOP FETCH emp_cursor INTO emp_record; EXIT WHEN emp_cursor%NOTFOUND; --process the fetched row dbms_output.put_line(emp_record.first_name || ' ' || emp_record.last_name); END LOOP; CLOSE emp_cursor;END;
In this example, we first declare a cursor named “emp_cursor” that retrieves all rows from the “employees” table. We also declare a variable “emp_record” of the “employees” table’s row type, which will store the fetched row.
Then, we open the Cursor using the OPEN statement. We use a LOOP statement to fetch each row from the Cursor using the FETCH statement and store it in the “emp_record” variable. The loop exits when the Cursor reaches the end of the result set, indicated by the NOTFOUND attribute. Inside the loop, we process the fetched row by displaying the first name and last name of the employee.
Finally, we close the Cursor using the CLOSE statement. Closing the Cursor after it’s finished processing is important to release the associated resources.
In this example, the Cursor is not deallocated after closing, but it’s also possible to deallocate a cursor using DEALLOCATE statement, and it’s good practice to do so.
This is an example of how cursors can be used in PL/SQL, and you can use cursors to perform various other operations, such as updating or deleting rows or even using the Cursor as a parameter in a stored procedure.
This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio
Top Picks & New Arrivals
