PL SQL Tutorial

---

PL SQL LOOPs Tutorial

      LOOPS allows you to execute sequence of statements repeatedly.

There are 3 types of LOOPs

• Simple LOOP

    Simple Loop is an infitinite Loop, it should be terminated using EXIT [When Condition], This type of loops are used to execute the code,atleast once;

• FOR LOOP

    Numeric for loop has accepts range of values with optional step value.

• WHILE LOOP

    Numeric WHILE LOOP, First condition should be checked, if condition is met, executes the WHILE LOOP Body, otherwise skips While code block

Simple LOOP

Syntx:

			LOOP
			      PL-SQL Statements;
			END LOOP;
		

FOR LOOP

Syntax:

			FOR index [REVERSE] start..end.step
			LOOP
				PL-SQL Statements;
			END LOOP;
		

WHILE LOOP

Syntax:

			WHILE Condition(s)
			LOOP
			
				PL-SQL Statements;
			END LOOP;
		
		

PL/SQL Simple Loop Example

DECLARE 

 C NUMBER DEFAULT 0;

BEGIN

	LOOP
		EXIT WHEN C = 5;
		C:=C+1;
		DBMS_OUTPUT.PUT_LINE(C);
	END LOOP;
END;		
		

OUTPUT:

1
2
3
4
5

PL/SQL FOR Loop Example

Display Numbers using for Loop,
here index variable is idx
start number is 1
end number is 10,
Auto incremented by 1(default)
index idx is assigned to start value, then auto incremented by 1,checks internally incremented value is less than the end value, if TRUE loop continues prints incremented value,otherwise breaks the LOOP.
You must have atlease one statement between LOOP and END LOOP; if no statements specify NULL; statement

		set serveroutput on;
		
		begin
			for idx in  1..10
			loop
			    dbms_output.put_line(idx);
			end loop;

		end;

		-- display elements in reverse order using REVERSE keyword.
		
		begin
			for idx in  REVERSE 1..10
			loop
			    dbms_output.put_line(idx);
			end loop;

		end;

		

PL SQL Program to Display All Dates in a MONTH

   Months Always starts by 1, end of the month can calculated using LAST_DAY(sysdate)-TRUNC(SYSDATE,'mm'), LAST_DAY gives LAST date of the month TRUNC(sysdate,'mm') gives first date of the Month. difference between lastdate-firstdateof the month, gives total numbers of days in a month.
Current day can be calculated using EXTRACT(DAY from SYSDATE).
Add number of days to SYSDATE based on today's date. month_start set to 1, month_end set to total day's in a month.

set serveroutput on;
------------------------------------------------	

declare 
   month_start number(2):=1;
   month_end   number(2) default round(last_day(sysdate)-trunc(sysdate,'mm'));
   today number(2) default extract(day from sysdate);
   ret number(2):=0;
begin


for idx in month_start..month_end
loop
        ret:=-today+idx;

    dbms_output.put_line(sysdate+ret);
end loop;

end;			
		
		

Pl/SQL WHILE LOOP Example

set serveroutput on

		DECLARE
		    N NUMBER(2) NOT NULL DEFAULT 1;
		BEGIN
		
			WHILE  N <=5 LOOP
			
			DBMS_OUTPUT.PUT_LINE(N);
			N:=N+1;
			END LOOP;
		
		END;
		

OUTPUT:

		1
		2
		3
		4
		5
		

PL/SQL Program to check a Number is a Prime Number or Not?

    Prime Number is a Number, which is not divisble by any other number other that 1 and that Number(N).
i.e. Number should not be divisble by 2..N-1. ,because any number is divisible by 1 and that number, gives remainder zero.

--This program checks given number is a Prime Number or NOT
DECLARE
   N NUMBER(5);
   B BOOLEAN := TRUE;
BEGIN
    N:=11;
    FOR I IN 2..N/2
    LOOP
        IF MOD(N,I) = 0 THEN 
        B := FALSE;
        DBMS_OUTPUT.PUT_LINE(N||' IS NOT A PRIME NUMBER');
        EXIT;
        END IF;
       
    END LOOP;
       IF B THEN
         DBMS_OUTPUT.PUT_LINE(N||' IS A PRIME NUMBER');
       END IF;

END;
		
		

OUTPUT:

4 IS NOT A PRIME NUMBER

PL/SQL procedure successfully completed.

3 IS A PRIME NUMBER

7 IS A PRIME NUMBER

9 IS NOT A PRIME NUMBER