PL SQL Tutorial

---

PL SQL LOOPs Tutorial

      LOOPS allows you to execute sequence of statements repeatedly.

There are 3 types of LOOPs

• Simple LOOP
• FOR LOOP
• WHILE LOOP

Simple LOOP

Syntx:

			LOOP
			      PL-SQL Statements;
			END LOOP;
		

FOR LOOP

Syntax:

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

WHILE LOOP

Syntax:

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

FOR Loop

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;