Oracle PL SQL Tutorial day 10 - Loops
Loops are fundamental constructs in any programming language, and PL/SQL is no exception. Oracle PL/SQL provides various types of loops that allow developers to execute a block of code repeatedly. Whether you're fetching rows from a table or performing calculations, loops help you avoid redundant code and handle repetition effectively.
This article provides a comprehensive understanding of loops in PL/SQL with syntax, real-world examples using the HR schema, and best practices.
Why Use Loops in PL/SQL?
Loops are used when a set of statements needs to be executed multiple times. Some common use cases include:
- - Processing multiple rows returned by a query
- - Performing iterative calculations
- - Automating updates or inserts in bulk
- - Creating test or sample data
Types of Loops in PL/SQL
PL/SQL supports several types of loops:
1. Basic LOOP
2. WHILE LOOP
3. FOR LOOP
4. Cursor FOR LOOP
5. Nested Loops
6. EXIT and EXIT WHEN Statements
1. Basic LOOP
The most fundamental type. It repeats indefinitely unless you explicitly exit.
Syntax:
LOOP
-- statements
EXIT WHEN condition;
END LOOP;
Example: Print numbers 1 to 5
DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Value: ' || i);
i := i + 1;
EXIT WHEN i > 5;
END LOOP;
END;
2. WHILE LOOP
Executes as long as a condition is true.
Syntax:
WHILE condition LOOP
-- statements
END LOOP;
Example: Sum first 10 natural numbers
DECLARE
i NUMBER := 1;
total NUMBER := 0;
BEGIN
WHILE i <= 10 LOOP
total := total + i;
i := i + 1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Sum = ' || total);
END;
3. FOR LOOP
Best when you know how many times the loop should run. It automatically increments and handles boundary conditions.
Syntax:
FOR counter IN [REVERSE] lower..upper LOOP
-- statements
END LOOP;
Example: Display even numbers between 2 and 10
BEGIN
FOR i IN 2..10 LOOP
IF MOD(i, 2) = 0 THEN
DBMS_OUTPUT.PUT_LINE('Even: ' || i);
END IF;
END LOOP;
END;
Reverse Example:
BEGIN
FOR i IN REVERSE 5..1 LOOP
DBMS_OUTPUT.PUT_LINE('Countdown: ' || i);
END LOOP;
END;
4. Cursor FOR LOOP
This is used to process rows returned from a query. The loop automatically opens, fetches, and closes the cursor.
Syntax:
FOR record IN (SELECT ... FROM table) LOOP
-- use record.column_name
END LOOP;
Example: List employee names and salaries
BEGIN
FOR emp_rec IN (SELECT first_name, salary FROM employees WHERE department_id = 90) LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ': ' || emp_rec.salary);
END LOOP;
END;
5. Nested Loops
You can use one loop inside another to handle multi-level data operations.
Example: Combine departments and jobs
BEGIN
FOR dept_rec IN (SELECT department_id, department_name FROM departments WHERE ROWNUM <= 2) LOOP
DBMS_OUTPUT.PUT_LINE('Department: ' || dept_rec.department_name);
FOR job_rec IN (SELECT job_id, job_title FROM jobs WHERE ROWNUM <= 3) LOOP
DBMS_OUTPUT.PUT_LINE('-- Job: ' || job_rec.job_title);
END LOOP;
END LOOP;
END;
6. Using EXIT and EXIT WHEN
EXIT; — exits the loop unconditionally.
EXIT WHEN condition; — exits only if a condition is met.
Example: Exit when condition is met
DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('i = ' || i);
EXIT WHEN i = 3;
i := i + 1;
END LOOP;
END;
Real-World Examples Using HR Schema
Example 1: Give a 10% bonus to employees with salary < 5000
BEGIN
FOR emp IN (SELECT employee_id, salary FROM employees WHERE salary < 5000) LOOP
UPDATE employees
SET salary = salary * 1.10
WHERE employee_id = emp.employee_id;
DBMS_OUTPUT.PUT_LINE('Updated salary for Employee ' || emp.employee_id);
END LOOP;
COMMIT;
END;
Example 2: Insert sample data using FOR
loop
BEGIN
FOR i IN 1..5 LOOP
INSERT INTO departments (department_id, department_name, location_id)
VALUES (100 + i, 'Test Department ' || i, 1700);
DBMS_OUTPUT.PUT_LINE('Inserted Test Department ' || i);
END LOOP;
COMMIT;
END;
Example 3: Accumulate salary totals by department
DECLARE
total_salary NUMBER;
BEGIN
FOR dept IN (SELECT DISTINCT department_id FROM employees) LOOP
total_salary := 0;
FOR emp IN (SELECT salary FROM employees WHERE department_id = dept.department_id) LOOP
total_salary := total_salary + emp.salary;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Dept ' || dept.department_id || ' Total Salary: ' || total_salary);
END LOOP;
END;
Best Practices for Using Loops in PL/SQL
- Use EXIT WHEN early to prevent unnecessary iterations
- Prefer FOR LOOP for fixed-count iterations
- Use BULK COLLECT and FORALL for performance with large datasets
- Always test loop termination to avoid infinite loops
- Avoid loops for bulk updates when a single SQL statement is sufficient
- Comment nested loops for clarity
Performance Considerations
Loops are powerful, but they can degrade performance if misused:
- - Avoid loops for large-scale updates when SQL alone can do the job
- - Use BULK COLLECT and FORALL to reduce context switching
- - Use LIMIT clauses or paginated processing if dealing with large datasets
When to Avoid Loops
Prefer SQL set-based operations when possible.
Instead of:
FOR emp IN (SELECT * FROM employees WHERE ...) LOOP
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = emp.employee_id;
END LOOP;
Use:
UPDATE employees SET salary = salary * 1.05 WHERE ...;
Conclusion
Loops are indispensable in PL/SQL development. They help in writing flexible and dynamic code for data manipulation, reporting, automation, and more. By mastering the different types of loops—basic, while, for, and cursor loops—you gain control over complex logic and iteration in your PL/SQL programs.
Using examples from the HR schema, we've demonstrated how to apply loops in real-world scenarios and also covered best practices to ensure optimal performance.
Loops are a tool—use them wisely, and they'll help you write efficient, readable, and powerful PL/SQL code.
0 Comments