Condition and Looping Statement

• IF 
• IF ELSE
• Multiple IF ELSE
• LOOP
• FOR LOOP
• WHILE LOOP

create table test_udt(id NUMBER, salary NUMBER);

BEGIN
INSERT INTO test_udt VALUES(001,23000);
INSERT INTO test_udt VALUES(002,43000);
INSERT INTO test_udt VALUES(003,78000);
INSERT INTO test_udt VALUES(004,25000);
INSERT INTO test_udt VALUES(005,26000);
INSERT INTO test_udt VALUES(006,90000);
Commit;
END;


select * from test_udt;


IF

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 1;
    
    IF v_salary > 20000 THEN
       UPDATE test_udt t
          SET t.salary = v_salary+10005
        WHERE t.id = v_id  ;
    END IF;
    
    commit;
END;
/

IF ELSE

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 2;
    
    IF v_salary < 20000 THEN
       UPDATE test_udt t
          SET t.salary = v_salary+10005
        WHERE t.id = v_id  ;
    ELSE    
        UPDATE test_udt t
          SET t.salary = v_salary+20007
        WHERE t.id = v_id  ;
    END IF;
    commit;
END;
/


--same example with less number of lines

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 2;
    
    IF v_salary < 20000 THEN
        v_salary := v_salary+10005;
    ELSE    
        v_salary := v_salary+20007;
    END IF;

       UPDATE test_udt t
          SET t.salary = v_salary
        WHERE t.id = v_id  ;
    commit;
END;
/


select * from test_udt;

Multiple IF ELSIF

DECLARE   
   v_salary NUMBER;
   v_id NUMBER;
BEGIN
   SELECT id,salary 
     INTO v_id,v_salary 
     FROM test_udt
    WHERE id = 3;
    
    IF v_id = 1 THEN
        v_salary := 1000;
        dbms_output.put_line('1 inside');
    ELSIF v_id = 2  THEN
        v_salary := 2000;
        dbms_output.put_line('2 inside');
    ELSIF v_id = 3 THEN
        v_salary := 3000;
        dbms_output.put_line('3 inside');
    ELSIF v_id = 4 THEN
        v_salary := 4000;
        dbms_output.put_line('4 inside');
    END IF;

       UPDATE test_udt t
          SET t.salary = v_salary
        WHERE t.id = v_id  ;
    commit;
END;
/

select * from test_udt;


LOOP

--syntax

LOOP
--statements
--increment 
--exit statement
END LOOP;


CREATE TABLE prod_details (pid VARCHAR2(3), pname VARCHAR2(30));

DECLARE 
    i NUMBER := 1;
    x VARCHAR2(3);
BEGIN
  LOOP
      x := lpad(i,3,'0');
      dbms_output.put_line('x value : '|| x);
      INSERT INTO prod_details VALUES(i, 'Product '||i);
      EXIT WHEN i >900;
      i := i+1;
      
      IF MOD(i,100) = 0 THEN
          commit;
          dbms_output.put_line('commit executed');
      END IF;
      
  END LOOP;
END;

SELECT * FROM prod_details ORDER BY 1;

DROP TABLE prod_details;

FOR LOOP

BEGIN
    FOR I in 1..5 
    LOOP
     Dbms_output.put_line('The value of i : '||i);
    END LOOP;
END;
/

FOR LOOP - Reverse

BEGIN
FOR I in REVERSE 14..50 
LOOP
Dbms_output.put_line('The value of i : '||i);
END LOOP;
END;
/

WHILE LOOP

DECLARE
i_val NUMBER:=0;
BEGIN
WHILE (i_val < 11) 
LOOP
dbms_output.put_line('The value of I is '||i_val);
i_val := i_val + 1;
END LOOP;
END;
/

sample output:

The value of I is 0
The value of I is 1
The value of I is 2
The value of I is 3
The value of I is 4
The value of I is 5
The value of I is 6
The value of I is 7
The value of I is 8
The value of I is 9
The value of I is 10