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

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more