PL SQL Tutorial
LOOPS allows you to execute sequence of statements repeatedly.
There are 3 types of LOOPsSimple 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;
Numeric for loop has accepts range of values with optional step value.
Numeric WHILE LOOP, First condition should be checked, if condition is met, executes the WHILE LOOP Body, otherwise skips While code block
LOOP PL-SQL Statements; END LOOP;
FOR index [REVERSE] start..end.step LOOP PL-SQL Statements; END LOOP;
WHILE Condition(s) LOOP PL-SQL Statements; END LOOP;
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
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;
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
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
ADS