Oracle PL/SQL is a powerful extension of SQL, specifically designed to provide procedural capabilities for Oracle databases. It allows developers to write complex programs that combine SQL queries with procedural constructs like loops, conditionals, and exception handling. Among these features, PL/SQL facilitates efficient data processing with cursors, which handle query results one row at a time. The PL/SQL Cursor FOR LOOP simplifies cursor management by automatically handling the fetching and looping through each row of the result set.
This article takes a deep dive into the FOR LOOP's intricacies, unraveling its syntax, usage, and the various benefits it offers. Through two examples, complete with code snippets and detailed output explanations, we'll showcase the remarkable versatility of this construct.
PL/SQL Cursor FOR LOOP
The PL/SQL FOR LOOP is a construct designed for repetitive execution, enabling developers to iterate over a specified range of values or through elements in collections. When used with cursors, the FOR LOOP can handle the processing of query results efficiently, automatically fetching rows and iterating over them without requiring explicit OPEN, FETCH, and CLOSE commands for the cursor
The FOR LOOP in PL/SQL is purpose-built for seamless iteration, whether traversing a range of values or cycling through collection elements. The fundamental syntax is elegantly straightforward:
---Standard FOR LOOP FOR loop_index IN [REVERSE] lower_bound..upper_bound LOOP -- Statements to be executed in each iteration END LOOP;
- loop_index: The loop index or counter variable.
- lower_bound and upper_bound: The range of values for the loop index.
- REVERSE (optional): Allows looping in reverse order.
Process:
- Initialize the loop index to the lower bound.
- Execute the statements within the loop.
- Increment or decrement the loop index.
- Repeat the process until the loop index reaches the upper bound.
Syntax:
For Standard FOR LOOP:
-- Basic FOR LOOP FOR loop_index IN lower_bound..upper_bound LOOP -- Statements to be executed in each iteration END LOOP;
For Reverse FOR LOOP:
-- FOR LOOP in Reverse FOR loop_index IN REVERSE lower_bound..upper_bound LOOP -- Statements to be executed in each iteration END LOOP;
Example of PL/SQL Cursor FOR LOOP
Through two illuminating examples, complete with code snippets and detailed output explanations, we'll showcase the remarkable versatility of this construct.
Example 1: Using Cursor FOR LOOP to Print Numbers
This example demonstrates how to print sequential numbers from 1 to 5 using a FOR LOOP.
-- Using FOR LOOP to Print Numbers DECLARE -- Loop index loop_index NUMBER := 1; BEGIN FOR loop_index IN 1..5 LOOP DBMS_OUTPUT.PUT_LINE('Number: ' || loop_index); END LOOP; END; /Output:
Number: 1 Number: 2 Number: 3 Number: 4 Number: 5
Explanation: In this example, the FOR LOOP effortlessly prints the numbers from 1 to 5 using the DBMS_OUTPUT.PUT_LINE statement. The loop index dynamically changes in each iteration, providing a concise and readable solution for printing sequential numbers.
Example 2: Using Cursor FOR LOOP to Update Records
the intricacies of how the loop index dynamically influences specific departments, showcasing the FOR LOOP's inherent prowess in efficiently managing targeted updates.
-- Using FOR LOOP to Update Records DECLARE -- Loop index loop_index NUMBER := 1; BEGIN FOR loop_index IN 1..3 LOOP UPDATE employees SET salary = salary * 1.1 WHERE department_id = loop_index; END LOOP; COMMIT; END; /
Explanation: This example updates employees' salaries in three different departments by multiplying their current salaries by 1.1. The loop index dynamically determines the specific departments being updated. The COMMIT statement finalizes the changes.
Important Points About PL/SQL Cursor FOR LOOP
- While Cursor FOR LOOPs are convenient, they may not be the best choice for processing very large datasets. In such cases, consider using bulk operations like BULK COLLECT for better performance.
- The cursor can be declared locally within the same block or as a parameterized cursor, depending on the use case.
- The loop terminates automatically when all rows in the cursor's result set have been processed, without needing an explicit exit condition.
- Cursor FOR LOOPs can only be used for read-only operations. If you need to update the fetched data, you should use a different approach or manage cursors manually.
Similar Reads
PL/SQL For Loop
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. To fetch records, process dat
4 min read
PL/SQL Loops
PL/SQL stands for Procedural Language Extension to the Structured Query Language and it is designed specifically for Oracle databases it extends Structured Query Language (SQL) capabilities by allowing the creation of stored procedures, functions, and triggers. It is a block-structured language that
5 min read
PL/SQL Cursor Update
PL/SQL stands for Procedural Language/Structured Query Language. It has block structure programming features. In Oracle PL/SQL, cursors play a vital role in managing and processing query results. Among the various types of cursors, updatable cursors stand out for their ability to fetch data and modi
5 min read
PostgreSQL - For Loops
In PostgreSQL, PL/pgSQL (Procedural Language/PostgreSQL) introduces control structures like FOR loops to simple complex data processing. The FOR loop allows developers to iterate over a specified range of integers or the results of a query and making repetitive tasks more manageable. This feature is
6 min read
PostgreSQL - Cursor
In the area of database management, effective data retrieval is essential particularly when handling large datasets. PostgreSQL offers the functionality of a cursor which allows for incremental data retrieval from extensive result sets. By using PostgreSQL cursor syntax, developers can manage memory
5 min read
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 b
5 min read
PL/SQL LIMIT Clause
The LIMIT clause in PL/SQL is a powerful tool designed to manage the amount of data retrieved from a query, making it particularly useful for handling large datasets. By specifying the maximum number of rows to be fetched, the LIMIT clause helps in optimizing both performance and memory usage. In th
6 min read
MySQL Cursors
A MySQL cursor is a powerful database object designed for retrieving, processing, and managing rows from a result set one at a time. Unlike standard SQL queries that handle sets of rows in bulk, cursors allow for detailed row-by-row operations. In this article, We will learn about MySQL Cursors in d
6 min read
Python SQLite - Cursor Object
A Cursor is an object used to execute SQL queries on an SQLite database. It acts as a middleware between the SQLite database connection and the SQL commands. It is created after establishing a connection to the SQLite database. Example: [GFGTABS] Python import sqlite3 conn = sqlite3.connect('exa
3 min read
What is Cursor in SQL ?
When working with SQL, most operations are performed on entire sets of data. But what if we need to process each row individually maybe to perform some custom logic or apply conditions row-by-row? Cursors come into play in such scenarios, providing a way to process each row individually. This articl
9 min read