PL/SQL Control Statement

By ukmodak | June 6th 2022 12:23:03 PM | viewed 195 times

IF statement:

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; 
			/ 
	   

Loop Statement:

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 
			<> 
			-- while loop execution  
			WHILE a < 20 LOOP
			dbms_output.put_line ('value of a: ' || a); 
			a := a + 1; 
			IF a = 15 THEN 
			a := a + 1; 
			GOTO loopstart; 
			END IF; 
			END LOOP; 
			END; 
			/
	   

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;
			/
	   
bONEandALL
Visitor

Total : 18980

Today :9

Today Visit Country :

  • Germany
  • Singapore
  • United States
  • Russia