Oracle – PL-SQL – For Loop

Oracle, Oracle-Tips and Tricks, Varinder Sandhu

A LOOP statement executes a sequence of statements multiple times. PL/SQL provides these loop statements:

Numeric FOR_LOOP loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP.

The range is evaluated when the FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by lower_boun..upper_bound. After each iteration, the loop index is incremented.

Example

-- CREATE TABLE 

CREATE TABLE EMPLOYEE
(
    ID                NUMBER NOT NULL,
    FIRST_NAME        VARCHAR2(10 BYTE),
    LAST_NAME         VARCHAR2(10 BYTE)
);

-- INSERT SOME VALUES INTO TABLES 

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (1,'VARINDER','SANDHU');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (2,'DINESH','SHARMA');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (3,'RANJOYT','SINGH');

INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (4,'VIKRAM','SINGH');

-- SEE THE VALUE IN THE TABLES 

SELECT * FROM EMPLOYEE;

-- LOOP EXAMPLE 

BEGIN
FOR EMP IN (
SELECT  FIRST_NAME FROM EMPLOYEE )
LOOP
    DBMS_OUTPUT.PUT_LINE('EMPLOYEE NAME : ' || EMP.FIRST_NAME);
   END LOOP;
 END;

Related Posts

,