Sunday, August 12, 2012

PL/SQL Programs



LOOPING

1-Simple Loop:
--e.g. Simple Loop

declare
                a number(2):=1;
begin
loop
                dbms_output.put_line(a);
                a:=a+1;
                exit when (a>10);  
end loop;
end;
/
DECLARE
                x int:=10;
BEGIN
                Loop
                     dbms_output.put_line('X value is => '||x);
                     x:=x-1;
                     --exit when x<0;
                     if(x<=0)then
                            exit;
                     end if;
                End Loop;
END;
/
2-While
DECLARE
                i int:=1;
BEGIN
                While(i<=10)
                Loop
                     dbms_output.put_line('X value is => '||i);
                     i:=i+1;
                End Loop;
END;
/
2-2 Sum ODDEVEN
DECLARE
    co int:=0;
    ce int:=0;
    so int:=0;
    se int:=0;
    i int:=&StartNo;
    j int:=&EndNo;
BEGIN
   While(i<=j)
   Loop
     if(i mod 2=0) then
                ce:= ce+1;
                se:= se + i;
     else
                co:=co+1;
                so:=so+i;
     end if;
                i:=i+1;
   End Loop;

dbms_output.put_line('Count of Odd Nos.: '||co);
dbms_output.put_line('Sum of Odd Nos.: '||so);

dbms_output.put_line(chr(10)||'Count of EVEN Nos.: '||ce);
dbms_output.put_line('Sum of EVEN Nos.: '||se);
END;
/
2-CountEven_Odd
DECLARE
                i int:=1;
                n int:=&n;
                so int:=0;
                se int:=0;
BEGIN
                dbms_output.put_line('Odd Numbers between 1 and ' || n);
                While(i<=n)          
                Loop
                   dbms_output.put_line(i);
                   so:=so+i;
                   i:=i+2;
                End Loop;
                dbms_output.put_line('And their Sum is: '||so);

                i:=2;
                dbms_output.put_line('EVEN Numbers between i and ' || n);
                While(i<=n)          
                Loop
                   dbms_output.put_line(i);
                   se:=se+i;
                   i:=i+2;
                End Loop;
                dbms_output.put_line('And their Sum is: '||se);
END;
/
3-Goto
--E.g for Goto Statement

DECLARE
                no number:=0;
BEGIN
   While(no<=15)
   Loop
      no:=no+1;
      if no=10 then
                goto xyz;
      end if;
      dbms_output.put_line(no||' - In while');
   End Loop;
<<xyz>>
                dbms_output.put_line(no||chr(9)||'Out while');
END;
/

--To findout Armstrong or not - using While Loop
declare
                n number(5);
                n1 number(5);     
                r number(5);
                result number(5):=0;
begin
                n:=&no;--n=153
                n1:=n;   --n1=153
while(n>0)
loop
                --select mod(n,10) into r from dual;
        r:=n mod 10;
                result:=result+(r**3);
                n:=floor(n/10);
end loop;

if n1=result then
                dbms_output.put_line('Armstrong Number');
else
                dbms_output.put_line('Not an Armstrong Number');
end if;
end;
/
------------------------------------------------------------------------------
PIRME
DECLARE
    n int:=&n;
    cnt int:=0;
    i int:=1;
BEGIN
    while (i<=n)
    Loop
          if(n mod i)=0 then
                   cnt:=cnt+1;
          end if;

        i:=i+1;
        if(cnt>2) then
             exit;
        end if;
            
    End Loop;
if cnt=2 then
   dbms_output.put_line('The Given Number is a Prime');
else
   dbms_output.put_line('The Given Number NOT is a Prime Number');
end if;

END;
/
------------------------------------------------------------------------------
DECLARE
a number(2):=1;
i number(2):=1;
begin
   WHILE(a<=5)
   LOOP
                While(i<=10)
                loop                                       
                   dbms_output.put_line(i||' * '|| a ||'='||(i*a));
                   i:=i+1;
                End Loop;
   dbms_output.put_line('***********');
   i:=1;
   a:=a+1;
   END LOOP;
END;
/
------------------------------------------------------------------------------
 3-FOR
BEGIN
for i in 1..10
Loop
                dbms_output.put_line(i);
End Loop;
END;
/
------------------------------------------------------------------------------
BEGIN
                for i in reverse 20..40
                loop
                       dbms_output.put_line('i value : '||i);
                end loop;
END;
/
------------------------------------------------------------------------------
--To count no. of words and Chars
DECLARE
     l number;
     words number:=0;
     chars number:=0;
     line varchar2(50);
BEGIN
     line:='&line';--This is a test line
     line:=trim(line);
     l:=length(line);
for i in 1..l
Loop
                if substr(line,i,1) = ' ' and substr(line,i+1,1)<>' ' then
                   words:=words+1;
                elsif substr(line,i,1) <>' ' then
                   chars:=chars+1; --chars=6
                end if;
End Loop;

words:=words+1;
dbms_output.put_line('no. of words: ' || words);
dbms_output.put_line('no. of chars: ' || chars);
end;
/

-- To Count ODD numbers and EVEN numbers
-----------------------------------------
DECLARE
    a int;
    b int;
    co int:=0;
    ce int:=0;
BEGIN
    a:=&Starting_No;
    b:=&Ending_No;
    for i in a..b
    Loop
                if(i mod 2 = 0) then             
                    ce:=ce+1;
                else
                    co:=co+1;
                end if;
    End Loop;
dbms_output.put_line('Count of EVEN numbers is: '||ce);
    dbms_output.put_line('Count of ODD numbers is: '||co);
END;
/

BEGIN
    dbms_output.put_line('Even Numbers from 1 to 10...');         
    for i in 1..10
    Loop
                if(i mod 2 = 0) then             
                    dbms_output.put_line(i);
                end if;
    End Loop;
END;
/

No comments:

Post a Comment

Thank you :
- kareem