for each in sql query

Guri Bee

2 min read

·

21-10-2024

1

0

Share

Unlocking the Power of "FOR EACH" in SQL Queries

The FOR EACH construct in SQL, often overlooked, provides a powerful way to iterate through rows in a table and perform specific actions on each one. While not universally supported by all SQL dialects, its presence in databases like Oracle and PostgreSQL opens up exciting possibilities for data manipulation and automation.

What does "FOR EACH" do?

Imagine you need to update multiple rows in a table based on a condition. Traditionally, you would use a WHERE clause to identify the target rows and apply your changes. However, what if you need to perform different actions on each matching row? This is where FOR EACH shines.

In essence, FOR EACH allows you to "loop" through selected rows and execute a sequence of commands on each iteration. This enables you to:

• Dynamically update data: Apply unique modifications to each row based on its content.
• Generate custom output: Create a report or summary for each individual row.
• Execute procedures or functions: Call specific routines based on the row's values.

Let's illustrate with examples:

Example 1: Updating Customer Discounts in Oracle

DECLARE
    v_discount_amount NUMBER;
BEGIN
    FOR rec IN (SELECT customer_id, order_total FROM orders WHERE order_date < SYSDATE - 90)
    LOOP
        IF rec.order_total > 1000 THEN
            v_discount_amount := rec.order_total * 0.1;
        ELSE
            v_discount_amount := rec.order_total * 0.05;
        END IF;
        UPDATE customers
        SET discount = v_discount_amount
        WHERE customer_id = rec.customer_id;
    END LOOP;
END;
/

Explanation:

• This Oracle PL/SQL block uses a FOR EACH loop to iterate through orders older than 90 days.
• For each order, it calculates a discount based on the order total.
• Then, it updates the discount field in the customers table for the corresponding customer ID.

Example 2: Generating Reports in PostgreSQL

DO $
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT * FROM employees WHERE department = 'Sales'
    LOOP
        RAISE NOTICE 'Employee % has salary %', rec.employee_name, rec.salary;
    END LOOP;
END $;

Explanation:

• This PostgreSQL function uses FOR EACH to loop through employees in the 'Sales' department.
• Inside the loop, it uses RAISE NOTICE to print a message about each employee's name and salary.

Key Advantages of "FOR EACH":

• Flexibility: Customize actions based on individual row data.
• Efficiency: Avoids repetitive queries for each row modification.
• Data integrity: Ensures consistent updates and avoids inconsistencies.

Points to Remember:

• Not all SQL dialects support FOR EACH. Check your database documentation for specific implementations.
• Proper syntax and usage are crucial for accurate execution.
• Consider performance implications for large datasets.

Beyond "FOR EACH":

While FOR EACH offers a powerful solution, other techniques like cursor-based loops might be more suitable for complex scenarios. Consider exploring different approaches to find the best fit for your needs.

By utilizing "FOR EACH" effectively, you can unlock greater control and flexibility in manipulating your data and automating tasks. Explore its potential, and see how it can enhance your SQL querying capabilities.