LOOPS in PL/SQL


In PL/SQL, loops are used to execute a block of statements repeatedly. There are several types of loops available in PL/SQL:

Basic Loop
WHILE Loop
FOR Loop

1. Basic Loop

The basic loop executes the statements inside the loop body repeatedly until an EXIT condition is met.

Syntax:
LOOP
    -- statements
    EXIT WHEN condition;
END LOOP;

Example:
DECLARE
    i NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
        i := i + 1;
        EXIT WHEN i > 5;
    END LOOP;
END;

In this example, the loop runs five times, printing the iteration number each time.

2. WHILE Loop

The WHILE loop executes the statements as long as the condition is TRUE.

Syntax:
WHILE condition LOOP
    -- statements
END LOOP;

Example:
DECLARE
    i NUMBER := 1;
BEGIN
    WHILE i <= 5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
        i := i + 1;
    END LOOP;
END;

This WHILE loop works similarly to the basic loop example but uses a WHILE condition to control the loop.

3. FOR Loop

The FOR loop is used to execute a block of statements a fixed number of times. It automatically handles the loop variable initialization, increment, and termination.

Syntax:
FOR loop_variable IN [REVERSE] lower_bound..upper_bound LOOP
    -- statements
END LOOP;

Example:
BEGIN
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration: ' || i);
    END LOOP;
END;

In this example, the FOR loop iterates from 1 to 5 and prints the iteration number each time. The REVERSE keyword can be used to iterate in the reverse order.


Real time example with loops
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    salary NUMBER
);
DECLARE
    -- Loop variable
    emp_id INTEGER := 1;
    -- Employee record type
    TYPE emp_record_type IS RECORD (
        employee_id employees.employee_id%TYPE,
        first_name employees.first_name%TYPE,
        last_name employees.last_name%TYPE,
        salary employees.salary%TYPE
    );
    emp_record emp_record_type;
BEGIN
    -- Insert sample data
    INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (1, 'John', 'Doe', 50000);
    INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (2, 'Jane', 'Smith', 60000);
    INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (3, 'Jim', 'Brown', 55000);
    INSERT INTO employees (employee_id, first_name, last_name, salary) VALUES (4, 'Jill', 'White', 70000);
    
    COMMIT;
    
    -- Loop to update salaries with a 10% raise
    LOOP
        SELECT employee_id, first_name, last_name, salary INTO emp_record
        FROM employees
        WHERE employee_id = emp_id;
        
        emp_record.salary := emp_record.salary * 1.10;
        
        UPDATE employees
        SET salary = emp_record.salary
        WHERE employee_id = emp_id;
        
        emp_id := emp_id + 1;
        
        EXIT WHEN emp_id > 4;
    END LOOP;
    
    -- FOR loop to print updated employee details
    FOR emp IN (SELECT employee_id, first_name, last_name, salary FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp.employee_id || ', Name: ' || emp.first_name || ' ' || emp.last_name || ', Salary: ' || emp.salary);
    END LOOP;
END;

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

Procedures in PL/SQL

Procedures in PL/SQL (Procedural Language/Structured Query Language) are a way to encapsulate a set of SQL and PL/SQL statements that perform a specific task. They are stored in the database and can be executed as needed, providing a modular and reusable approach to database programming. Here’s an overview of creating and using procedures in PL/SQL: Creating a Procedure To create a procedure, you use the CREATE PROCEDURE statement followed by the procedure's name, parameters (if any), and the body of the procedure, which contains the executable code. Here’s a basic syntax: CREATE [OR REPLACE] PROCEDURE procedure_name     [ (parameter_name [IN | OUT | IN OUT] datatype [, ...]) ] IS    -- Declaration of variables BEGIN    -- Executable statements EXCEPTION    -- Exception handling END procedure_name; Example Here’s a simple example of a PL/SQL procedure that inserts a new employee record into an employees table: CREATE OR REPLACE PROCEDURE add_employee (     p_emp_id IN employees.emp
Read more

Cursors in PL/SQL

In PL/SQL, cursors are a mechanism for accessing and manipulating query result sets. A cursor allows you to fetch and process rows returned by a query one at a time. There are two main types of cursors in PL/SQL: implicit cursors and explicit cursors. Implicit Cursors PL/SQL automatically creates implicit cursors for SELECT INTO statements, and for INSERT, UPDATE, DELETE, and MERGE statements. Example of Implicit Cursor DECLARE     v_employee_name employees.first_name%TYPE; BEGIN     -- This SELECT INTO statement uses an implicit cursor     SELECT first_name     INTO v_employee_name     FROM employees     WHERE employee_id = 100;     DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_employee_name); END; In this example, PL/SQL creates an implicit cursor for the SELECT INTO statement and automatically fetches the result into the v_employee_name variable. Explicit Cursors Explicit cursors give you more control over the context area. You can open a cursor, fetch rows from the result s
Read more