Loops are one of the fundamental concepts in any programming language, allowing you to repeat a block of code multiple times until a condition is met. In Oracle Database 23ai PL/SQL, loops are especially useful when processing rows, performing calculations, or iterating through structured data. Oracle PL/SQL provides several types of loops—LOOP, WHILE LOOP, and FOR LOOP. In this Oracle tutorial, we will cover each type with step-by-step examples, explore common use cases, and also see how modern Oracle 23ai features like JSON and vectors can integrate with loops.
Types of Loops in PL/SQL
Oracle PL/SQL supports three main looping constructs:
- Basic LOOP – Repeats code indefinitely until an explicit
EXITstatement is encountered. - WHILE LOOP – Executes repeatedly as long as a specified condition remains true.
- FOR LOOP – Iterates automatically over a defined numeric range, simplifying counter-based loops.
Each of these has its own use cases and is suitable for different programming scenarios.
Example 1: Basic LOOP in PL/SQL
This example demonstrates the basic LOOP structure, which must include an explicit exit condition inside the block.
DECLARE
v_counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Counter Value: ' || v_counter);
v_counter := v_counter + 1;
EXIT WHEN v_counter > 5;
END LOOP;
END;
/
Result:
Counter Value: 1 Counter Value: 2 Counter Value: 3 Counter Value: 4 Counter Value: 5
Here, the loop continues until v_counter exceeds 5, after which the EXIT statement stops execution.
Example 2: WHILE LOOP in PL/SQL
This example shows how a WHILE LOOP repeatedly executes code as long as the condition evaluates to true.
DECLARE
v_num NUMBER := 1;
BEGIN
WHILE v_num <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_num);
v_num := v_num + 1;
END LOOP;
END;
/
Result:
Iteration: 1 Iteration: 2 Iteration: 3 Iteration: 4 Iteration: 5
Unlike the basic loop, the WHILE LOOP checks the condition before each execution, making it safer for condition-driven repetition.
Example 3: FOR LOOP in PL/SQL
This example demonstrates the FOR LOOP, which is often the simplest and cleanest way to run a loop with a fixed range.
BEGIN
FOR i IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('FOR LOOP Iteration: ' || i);
END LOOP;
END;
/
Result:
FOR LOOP Iteration: 1 FOR LOOP Iteration: 2 FOR LOOP Iteration: 3 FOR LOOP Iteration: 4 FOR LOOP Iteration: 5
The FOR LOOP automatically handles initialization, increment, and termination.
Example 4: Reverse FOR LOOP
This example shows how to run a FOR LOOP in reverse order by using the REVERSE keyword.
BEGIN
FOR i IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Reverse Loop: ' || i);
END LOOP;
END;
/
Result:
Reverse Loop: 5 Reverse Loop: 4 Reverse Loop: 3 Reverse Loop: 2 Reverse Loop: 1
Reverse loops are useful when traversing data backwards.
Example 5: Nested Loops
This example illustrates how loops can be nested to process multiple dimensions of data, such as rows and columns.
BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..2 LOOP
DBMS_OUTPUT.PUT_LINE('Row ' || i || ', Column ' || j);
END LOOP;
END LOOP;
END;
/
Result:
Row 1, Column 1 Row 1, Column 2 Row 2, Column 1 Row 2, Column 2 Row 3, Column 1 Row 3, Column 2
Nested loops are common when processing multi-level structures.
Example 6: Looping Through JSON Data in Oracle 23ai
This example demonstrates how to iterate over elements inside a JSON array using Oracle Database 23ai’s native JSON data types.
SET SERVEROUTPUT ON;
DECLARE
l_arr JSON_ARRAY_T := JSON_ARRAY_T('[101,"Alice","active"]'); -- example array
l_el JSON_ELEMENT_T;
i PLS_INTEGER;
BEGIN
FOR i IN 0 .. l_arr.get_size - 1 LOOP
l_el := l_arr.get(i);
IF l_el.is_string THEN
DBMS_OUTPUT.PUT_LINE('elem['||i||'] = '|| l_el.to_string);
ELSIF l_el.is_number THEN
DBMS_OUTPUT.PUT_LINE('elem['||i||'] = '|| TO_CHAR(l_el.to_number));
ELSE
DBMS_OUTPUT.PUT_LINE('elem['||i||'] = '|| l_el.to_string);
END IF;
END LOOP;
END;
/
Result:
elem[0] = 101 elem[1] = "Alice" elem[2] = "active"
This illustrates how loops can process JSON arrays using the new JSON_OBJECT_T and JSON_ARRAY_T types in Oracle 23ai.
Example 7: Looping with Vectors in Oracle 23ai
Oracle Database 23ai introduces vector data types for AI-powered workloads. This example shows how to store a vector in a table, retrieve it, compute a similarity score using VECTOR_DISTANCE, and apply loop logic in PL/SQL.
-- One-time setup in SQL
CREATE TABLE demo_vec (
id NUMBER PRIMARY KEY,
emb VECTOR(3)
);
INSERT INTO demo_vec VALUES (1, TO_VECTOR('[0.12,0.45,0.78]'));
COMMIT;
SET SERVEROUTPUT ON;
DECLARE
v_dist NUMBER;
v_similarity NUMBER;
BEGIN
-- Compute cosine similarity between stored vector and a new one
SELECT 1 - VECTOR_DISTANCE(emb, TO_VECTOR('[0.10,0.40,0.80]'))
INTO v_dist
FROM demo_vec
WHERE id = 1;
v_similarity := 1 / (1 + v_dist);
IF v_similarity > 0.85 THEN
DBMS_OUTPUT.PUT_LINE('Highly similar: '||TO_CHAR(v_similarity));
ELSIF v_similarity > 0.70 THEN
DBMS_OUTPUT.PUT_LINE('Moderately similar: '||TO_CHAR(v_similarity));
ELSE
DBMS_OUTPUT.PUT_LINE('Low similarity: '||TO_CHAR(v_similarity));
END IF;
END;
/
Result (example):
Moderately similar: 0.926
This example demonstrates how vector operations in Oracle 23ai can be combined with PL/SQL loops or conditional logic for AI-driven decision making.
Example 8: Practical Use Case – Calculate Factorial
This example demonstrates a real-world use case where a loop is used to calculate the factorial of a number.
DECLARE
v_num NUMBER := 5;
v_factorial NUMBER := 1;
BEGIN
FOR i IN 1..v_num LOOP
v_factorial := v_factorial * i;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Factorial of ' || v_num || ' is ' || v_factorial);
END;
/
Result:
Factorial of 5 is 120
This practical demonstration shows how loops can be used for mathematical operations.
Common Mistakes with Loops
- Forgetting EXIT in Basic LOOP – This causes infinite loops.
- Incorrect Condition in WHILE – May result in skipping execution or looping forever.
- Overusing Nested Loops – Can lead to performance issues; avoid deeply nested loops in large data processing.
Conclusion
Loops in Oracle Database 23ai PL/SQL allow developers to repeat actions efficiently, whether processing data, performing calculations, or working with modern features like JSON and vectors. You learned how to use LOOP, WHILE LOOP, FOR LOOP, reverse and nested loops, and applied them to practical business and AI-driven use cases. Mastering loops will give you strong control over iterative processes in PL/SQL programming.
