 | Moderator | | Join Date: Dec 2006 Location: Bangalore ,India
Posts: 7,511
# 1
May 10 '07
| |
Sample PL/SQL programs
===================== To print EVEN numbers 1--100 -
BEGIN
-
FOR I IN 1..100 LOOP
-
--if the number is divisible by 2 then it is a even number.
-
IF MOD(I,2)=0 THEN
-
--display the number.
-
dbms_output.put_line(I);
-
END IF;
-
END LOOP;
-
END;
-
Sample program to REVERSE A NUMBER -
DECLARE
-
MNUM INTEGER;
-
MREV INTEGER:=0;
-
BEGIN
-
MNUM:=&MNUM;
-
WHILE(MNUM>0) LOOP
-
MREV:=MREV*10 +MOD(MNUM,10);
-
MNUM:=TRUNC(MNUM/10);
-
END LOOP;
-
DBMS_OUTPUT.PUT_LINE('THE REVERSE IS: '||MREV);
-
END;
-
Sample program showing use of XOR (Exclusive OR ) -
DECLARE
-
I BOOLEAN:=FALSE;
-
J BOOLEAN:=FALSE;
-
K BOOLEAN;
-
BEGIN
-
K:=XOR(I,J);
-
IF K=TRUE THEN
-
DBMS_OUTPUT.PUT_LINE('TRUE');
-
ELSE
-
DBMS_OUTPUT.PUT_LINE('FALSE');
-
END IF;
-
END;
-
Sample program showing use of EXECUTE IMMEDIATE -
declare
-
tablename varchar2(100):='&tablename';
-
rows_deleted number;
-
str varchar2(30);
-
begin
-
str := 'delete from ';
-
str :=str||tablename;
-
execute immediate str;
-
rows_deleted:=sql%rowcount;
-
dbms_output.put_line(to_char(rows_deleted));
-
end;
-
Sample program to REVERSE A STRING -
declare
-
i varchar2(20):='&i';
-
j varchar2(20);
-
k int;
-
l int;
-
begin
-
k:=length(i);
-
for l in reverse 1..k loop
-
j:=j||substr(i,l,1);
-
end loop;
-
dbms_output.put_line(j);
-
end;
-
SAMPLE PROGRAM TO PRINT ARMSTRONG NUMBER -
DECLARE
-
A NUMBER;
-
B NUMBER;
-
BEGIN
-
FOR I IN 1..500 LOOP
-
A:=I;
-
B:=0;
-
LOOP
-
EXIT WHEN A<=0;
-
B:=B+POWER(MOD(A,10),3);
-
A:=TRUNC(A/10);
-
END LOOP;
-
IF B=I THEN
-
DBMS_OUTPUT.PUT_LINE(I||' IS ARMSTRONG NUMBER');
-
END IF;
-
END LOOP;
-
END;
-
SOME SAMPLE CODE -
declare type nn is table of number index by pls_integer;
-
mm nn;
-
n number:=1;
-
s number:=1;
-
begin
-
for rec in (select empno from emp) loop
-
mm(n):=rec.empno;
-
n:=n+1;
-
end loop;
-
while s<n loop
-
dbms_output.put_line(mm(s));
-
s:=s+1;
-
end loop;
-
end ;
-
Last edited by debasisdas; Feb 11 '08 at 07:25 AM.
Reason: added inline comments.
|
