PL SQL Tutorial
LOOPS allows you to execute sequence of statements repeatedly.
There are 3 types of LOOPsLOOP PL-SQL Statements; END LOOP;
FOR index [REVERSE] start..end LOOP PL-SQL Statements; END LOOP;
WHILE Condition(s) LOOP PL-SQL Statements; END 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;
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;
ADS