Sl No | Condition | Example |
---|---|---|
1 | IF - THEN statement |
DECLARE a number(2) := 10; BEGIN a:= 10; -- check the boolean condition using if statement IF( a < 20 ) THEN -- if condition is true then print the following dbms_output.put_line('a is less than 20 ' ); END IF; dbms_output.put_line('value of a is : ' || a); END; / |
2 | IF-THEN-ELSE statement |
DECLARE a number(3) := 100; BEGIN -- check the boolean condition using if statement IF( a < 20 ) THEN -- if condition is true then print the following dbms_output.put_line('a is less than 20 ' ); ELSE dbms_output.put_line('a is not less than 20 ' ); END IF; dbms_output.put_line('value of a is : ' || a); END; / |
3 | IF-THEN-ELSIF statement |
DECLARE a number(3) := 100; BEGIN IF ( a = 10 ) THEN dbms_output.put_line('Value of a is 10' ); ELSIF ( a = 20 ) THEN dbms_output.put_line('Value of a is 20' ); ELSIF ( a = 30 ) THEN dbms_output.put_line('Value of a is 30' ); ELSE dbms_output.put_line('None of the values is matching'); END IF; dbms_output.put_line('Exact value of a is: '|| a ); END; / |
4 | Case statement |
DECLARE grade char(1) := 'A'; BEGIN CASE grade when 'A' then dbms_output.put_line('Excellent'); when 'B' then dbms_output.put_line('Very good'); when 'C' then dbms_output.put_line('Well done'); when 'D' then dbms_output.put_line('You passed'); when 'F' then dbms_output.put_line('Better try again'); else dbms_output.put_line('No such grade'); END CASE; END; / |
5 | Searched CASE statement |
DECLARE grade char(1) := 'B'; BEGIN case when grade = 'A' then dbms_output.put_line('Excellent'); when grade = 'B' then dbms_output.put_line('Very good'); when grade = 'C' then dbms_output.put_line('Well done'); when grade = 'D' then dbms_output.put_line('You passed'); when grade = 'F' then dbms_output.put_line('Better try again'); else dbms_output.put_line('No such grade'); end case; END; / |
6 | nested IF-THEN-ELSE |
DECLARE a number(3) := 100; b number(3) := 200; BEGIN -- check the boolean condition IF( a = 100 ) THEN -- if condition is true then check the following IF( b = 200 ) THEN -- if condition is true then print the following dbms_output.put_line('Value of a is 100 and b is 200' ); END IF; END IF; dbms_output.put_line('Exact value of a is : ' || a ); dbms_output.put_line('Exact value of b is : ' || b ); END; / |
Sl No | Condition | Example |
---|---|---|
1 | PL/SQL Basic LOOP |
DECLARE x number := 10; BEGIN LOOP dbms_output.put_line(x); x := x + 10; IF x > 50 THEN exit; END IF; END LOOP; -- after exit, control resumes here dbms_output.put_line('After Exit x is: ' || x); END; / DECLARE x number := 10; BEGIN LOOP dbms_output.put_line(x); x := x + 10; exit WHEN x > 50; END LOOP; -- after exit, control resumes here dbms_output.put_line('After Exit x is: ' || x); END; / |
2 | PL/SQL WHILE LOOP |
DECLARE a number(2) := 10; BEGIN WHILE a < 20 LOOP dbms_output.put_line('value of a: ' || a); a := a + 1; END LOOP; END; / |
3 | PL/SQL FOR LOOP |
DECLARE a number(2); BEGIN FOR a in 10 .. 20 LOOP dbms_output.put_line('value of a: ' || a); END LOOP; END; / DECLARE a number(2) ; BEGIN FOR a IN REVERSE 10 .. 20 LOOP dbms_output.put_line('value of a: ' || a); END LOOP; END; / |
4 | Nested loops in PL/SQL |
DECLARE i number(3); j number(3); BEGIN i := 2; LOOP j:= 2; LOOP exit WHEN ((mod(i, j) = 0) or (j = i)); j := j +1; END LOOP; IF (j = i ) THEN dbms_output.put_line(i || ' is prime'); END IF; i := i + 1; exit WHEN i = 50; END LOOP; END; / |
5 | Labeling a PL/SQL Loop |
DECLARE i number(1); j number(1); BEGIN << outer_loop >> FOR i IN 1..3 LOOP << inner_loop >> FOR j IN 1..3 LOOP dbms_output.put_line('i is: '|| i || ' and j is: ' || j); END loop inner_loop; END loop outer_loop; END; / |
6 | PL/SQL EXIT |
DECLARE a number(2) := 10; BEGIN -- while loop execution WHILE a < 20 LOOP dbms_output.put_line ('value of a: ' || a); a := a + 1; IF a > 15 THEN -- terminate the loop using the exit statement EXIT; END IF; END LOOP; END; / |
6 | PL/SQL CONTINUE |
DECLARE a number(2) := 10; BEGIN -- while loop execution WHILE a < 20 LOOP dbms_output.put_line ('value of a: ' || a); a := a + 1; IF a = 15 THEN -- skip the loop using the CONTINUE statement a := a + 1; CONTINUE; END IF; END LOOP; END; / |
7 | PL/SQL GOTO |
DECLARE a number(2) := 10; BEGIN < A GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement or sub-block. A GOTO statement cannot branch from one IF statement clause to another or from one CASE statement WHEN clause to another. A GOTO statement cannot branch from an outer block into a sub-block (i.e., an inner BEGIN-END block). A GOTO statement cannot branch out of a subprogram. To end a subprogram early, either use the RETURN statement or have GOTO branch to a place right before the end of the subprogram. A GOTO statement cannot branch from an exception handler back into the current BEGIN-END block. However, a GOTO statement can branch from an exception handler into an enclosing block. |
7 | PL/SQL FORALL (use on collection for update data ) |
SET SERVEROUTPUT ON; DECLARE --Source collection TYPE My_NestedTable IS TABLE OF NUMBER; source_col My_NestedTable := My_NestedTable (9,18,27,36,45,54,63,72,81,90); --Indexing collection TYPE My_Array IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; index_col My_Array; BEGIN --Initializing indexing collection with the index numbers. index_col (1) := 3; index_col (5) := 7; index_col (12):= 8; index_col (28):= 10; --FORALL statement FORALL idx IN VALUES OF index_col INSERT INTO tut_79 VALUES (source_col (idx)); END; / |
Total : 27273
Today :9
Today Visit Country :