DBMS LAB : PL/SQL Programs

1. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN NUMBER IS STRONG OR NOT.

SQL> Declare n number:=&n; n1 number:=n; fact number; sum1 number; r number; i number; Begin sum1:=0; while(n>0) loop r:=mod(n,10); n:=trunc(n/10); i:=1; fact:=1; while(i<=r)loop fact:=fact*i; i:=i+1; end loop; sum1:=sum1+fact; end loop; if(sum1=n1) then dbms_output.put_line(n1||' is strong number'); else dbms_output.put_line(n1||' is not a strong number'); end if; end; /

Output: Enter value for n: 145 old 9: n:=&n; new 9: n:=145; 145 is strong number PL/SQL procedure successfully completed.

2. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN STRING IS PALINDROME OR NOT.

SQL> Declare len number; s1 varchar2(20) := '&s1'; s2 varchar2(20); Begin len := length(s1); for i in REVERSE 1..len loop s2 := s2||substr(s1,i,1); end loop; if s2= s1 then dbms_output.put_line('Given String '||s1||' is a PALINDROME'); else dbms_output.put_line('Given String '||s1||' is not a PALINDROME'); end if; end; /

Output: Enter value for s1: madam old 3: s1 varchar2(20) := '&s1'; new 3: s1 varchar2(20) := 'madam'; Given String madam is a PALINDROME PL/SQL procedure successfully completed.

3. WRITE A PL/SQL PROGRAM TO SWAP TWO NUMBERS WITHOUT USING THIRD VARIABLE.

SQL>Declare a number:=&a; b number:=&b; Begin dbms_output.put_line('Before Swapping :'||a||' ' ||b); a:=a+b; b:=a-b; a:=a-b; dbms_output.put_line('After Swapping:'||a||' '||b); end; /

Output: Enter value for a: 10 old 2: a number:=&a; new 2: a number:=10; Enter value for b: 20 old 3: b number:=&b; new 3: b number:=20; Before Swapping :10 20 After Swapping : 20 10

4. WRITE A PL/SQL PROGRAM TO GENERATE MULTIPLICATION TABLES FOR 2,4,6

SQL>Declare i number:=2; Begin dbms_output.put_line('- - - - - - - - - -'); while (i<=6) loop dbms_output.put_line(i||' Multiplication table'); dbms_output.put_line('- - - - - - - - - -'); for j in 1 .. 10 loop dbms_output.put_line(i||'*'||j||'='||(i*j)); end loop; i:=i+2; dbms_output.put_line('- - - - - - - - - -'); end loop; end; /

Output: - - - - - - - - - - 2 Multiplication table - - - - - - - - - - 2*1=2 2*2=4 2*3=6 2*4=8 2*5=10 2*6=12 2*7=14 2*8=16 2*9=18 2*10=20 - - - - - - - - - - 4 Multiplication table - - - - - - - - - - 4*1=4 4*2=8 4*3=12 4*4=16 4*5=20 4*6=24 4*7=28 4*8=32 4*9=36 4*10=40 - - - - - - - - - - 6 Multiplication table - - - - - - - - - - 6*1=6 6*2=12 6*3=18 6*4=24 6*5=30 6*6=36 6*7=42 6*8=48 6*9=54 6*10=60 - - - - - - - - - - PL/SQL procedure successfully completed.

5. WRITE A PL/SQL PROGRAM TO DISPLAY SUM OF EVEN NUMBERS AND SUM OF ODD NUMBERS IN THE GIVEN RANGE.

SQL>Declare n number; i number; evensum number; oddsum number; Begin dbms_output.put_line('Enter a number to find even sum and odd sum'); n:=&n; i:=1; oddsum:=0; evensum:=0; while i<=n loop if(mod(i,2)=0) then evensum:=evensum+i; else oddsum:=oddsum+i; end if; i:=i+1; end loop; dbms_output.put_line('Even sum= '||evensum); dbms_output.put_line('odd sum= '||oddsum); end; /

Output: Enter value for n: 5 old 8: n:=&n; new 8: n:=5; Enter a number to find even sum and odd sum Even sum= 6 odd sum= 9 PL/SQL procedure successfully completed.

6. WRITE A PL/SQL PROGRAM TO CHECK THE GIVEN NUMBER IS PALLINDROME OR NOT.

SQL>Declare n number(3); i number(3); sum1 number(3); k number(3); Begin sum1:=0; n:=&n; k:=n; while (n>0) loop i:=mod(n,10); sum1:=(sum1*10)+i; n:=trunc(n/10); end loop; if(k=sum1) then dbms_output.put_line('Given Number is a Palindrome Number'); else dbms_output.put_line('Given Number is not a Palindrome Number'); end if; end; /

Output: Enter value for n: 121 old 8: n:=&n; new 8: n:=121; Given Number is a Palindrome Number PL/SQL procedure successfully completed.

7. WRITE A PL/SQL PROGRAM TO DISPLAY TOP 10 ROWS IN EMP TABLE BASED ON THEIR JOB AND SALARY.

Declare cursor c is select * from emp e where 10> (select count(distinct sal) from emp a where a.sal>e.sal); Begin for rec in c loop dbms_output.put_line(rec.empno||' '||rec.ename||' '||rec.sal); end loop; END;

Output: 7839 king 6250 7698 blake 3562.5 7782 clark 3062.5 7566 jones 3718.75 7654 martin 1562.5 7499 allen 2000 7844 turner 1875 7521 ward 1562.5 7902 ford 3750 7788 scott 3750 7876 adams 1375 7934 miller 1625 PL/SQL procedure successfully completed.