SQL PLSQL SERVER PROGRAMMES

Writing PL/SQL block for checking armstrong number

To write a PL/SQL block to check whether given number is Armstrong or not.

INPUT

DECLARE

num number(5);

rem number(5);

s number(5):=0;

num1 number(5);

BEGIN

num:=&num;

num1:=num;

while(num>0)

loop

rem:=mod(num,10);

s:=s+power(rem,3);

num:=trunc(num/10);

End loop;

if (s=num1)then

dbms_RESULT.put_line(num1||' IS ARMSTRONG NUMBER ');

else

dbms_RESULT.put_line(num1||' IS NOT ARMSTRONG NUMBER ');

End if;

END;

RESULT:

SQL>@arm

Enter value for num: 153

old 7: num:=&num;

new 7: num:=153;

153 IS ARMSTRONG NUMBER

PL/SQL procedure successfully completed.

SQL> /

Enter value for num: 123

old 7: num:=&num;

new 7: num:=123;

123 IS NOT ARMSTRONG NUMBER

PL/SQL procedure successfully completed.

Writing a PL/SQL block for checking a number even or odd.

AIM: To write a PL/SQL block to check whether a given number is Even or Odd.

INPUT

DECLARE

num number(5);

rem number;

BEGIN

num:=&num;

rem:=mod(num,2);

if rem=0

then

dbms_RESULT.put_line(' Number '||num||' is Even');

else

dbms_RESULT.put_line(' Number '||num||' is Odd');

end if;

END;

RESULT:

SQL>start even

Enter value for num: 6

old 5: num:=&num;

new 5: num:=6;

Number 6 is Even

PL/SQL procedure successfully completed.

SQL> /

Enter value for num: 3

old 5: num:=&num;

new 5: num:=3;

Number 3 is Odd

PL/SQL procedure successfully completed.

Writing PL/SQL block to find sum of digits of a given number.

To write a PL/SQL block to find Sum of Digits of a given Number.

INPUT

DECLARE

num number(5);

rem number(5);

sm number(5):=0;

num1 number(5);

BEGIN

num:=&num;

num1:=num;

while(num>0) loop

rem:=mod(num,10);

sm:=sm+rem;

num:=trunc(num/10);

end loop;

dbms_RESULT.put_line('SUM OF DIGITS OF '||num1||' IS: '||sm);

end;

RESULT:

SQL> @sum

INPUT truncated to 2 characters

Enter value for num: 123

old 7: num:=&num;

new 7: num:=123;

SUM OF DIGITS OF 123 IS: 6

PL/SQL procedure successfully completed.

SQL> @sum

INPUT truncated to 2 characters

Enter value for num: 456

old 7: num:=&num;

new 7: num:=456;

SUM OF DIGITS OF 456 IS: 15

PL/SQL procedure successfully completed.

Writing PL/SQL block for generating Fibonacci series.

To write a PL/SQL block to Generate Fibonacci Series

INPUT

DECLARE

num number(5);

f1 number(5):=0;

f2 number(5):=1;

f3 number(5);

i number(5):=3;

BEGIN

num:=&num;

dbms_RESULT.put_line('THE FIBONACCI SERIES IS:');

dbms_RESULT.put_line(f1);

dbms_RESULT.put_line(f2);

while(i<=num) loop

f3:=f1+f2;

dbms_RESULT.put_line(f3);

f1:=f2;

f2:=f3;

i:=i+1;

end loop;

END;

RESULT:

SQL> start fib

Enter value for num: 10

old 8: num:=&num;

new 8: num:=10;

THE FIBONACCI SERIES IS:

PL/SQL procedure successfully completed.

Writing PL/SQL block for checking palendrome.

To write a PL/SQL block to Check the Given String is Palindrome or Not.

INPUT

DECLARE

name1 varchar2(20);

name2 varchar2(20);

l number(5);

BEGIN

name1:='&name1';

l:=length(name1);

while l>0 loop

name2:=name2||substr(name1,l,1);

l:=l-1;

end loop;

dbms_RESULT.put_line('REVERSE OF STRING IS:'||NAME2);

if(name1=name2) then

dbms_RESULT.put_line(name1||' IS PALINDROME ');

else

dbms_RESULT.put_line(name1||' IS NOT PALINDROME ');

end if;

END;

RESULT

Enter value for name1: LIRIL

old 6: name1:='&name1';

new 6: name1:='LIRIL';

REVERSE OF STRING IS:LIRIL

LIRIL IS PALINDROME

PL/SQL procedure successfully completed.

SQL> /

Enter value for name1: MADAM

old 6: name1:='&name1';

new 6: name1:='MADAM';

REVERSE OF STRING IS:MADAM

MADAM IS PALINDROME

PL/SQL procedure successfully completed.

Writing PL/SQL block to demonstrate Cursors.

To write a Cursor to display the list of Employees and Total Salary Department wise.

INPUT

DECLARE

cursor c1 is select * from dept;

cursor c2 is select * from emp;

s emp.sal%type;

BEGIN

for i in c1 loop

s:=0;

dbms_RESULT.put_line('----------------------------------------------'); dbms_RESULT.put_line('Department is :' || i.deptno ||' Department name is:' || i.dname);

dbms_RESULT.put_line('-------------------------------------------');

for j in c2 loop

if ( i.deptno=j.deptno) then

s:=s+j.sal;

dbms_RESULT.put_line(j.empno|| ' '|| j.ename || ' '|| j.sal );

end if;

end loop;

dbms_RESULT.put_line('----------------------------------------------');

dbms_RESULT.put_line('Total salary is: '|| s);

dbms_RESULT.put_line('----------------------------------------------');

end loop;

END;

RESULT:

SQL> @abc

------------------------------------------------------------------------------

Department is :10 Department name is : ACCOUNTING

------------------------------------------------------------------------------

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

-----------------------------------------------------------------------------

Total salary is: 8750

-----------------------------------------------------------------------------

Department is :20 Department name is:RESEARCH

------------------------------------------------------------------------------

7369 SMITH 800

7566 JONES 2975

7788 SCOTT 3000

7876 ADAMS 1100

7902 FORD 3000

-----------------------------------------------------------------------------

Total salary is: 10875

------------------------------------------------------------------------------

Department is :30 Department name is:SALES

------------------------------------------------------------------------------

7499 ALLEN 1600

7521 WARD 1250

7654 MARTIN 1250

7698 BLAKE 2850

7844 TURNER 1500

7900 JAMES 950

------------------------------------------------------------------------------

Total salary is: 9400

------------------------------------------------------------------------------

Department is :40 Department name is:OPERATIONS

------------------------------------------------------------------------------

Total salary is: 0

------------------------------------------------------------------------------

PL/SQL procedure successfully completed.

Writing PL/SQL CURSOR

To write a Cursor to display the list of employees who are Working as a Managers or Analyst.

INPUT

DECLARE

cursor c(jb varchar2) is select ename from emp where job=jb;

em emp.job%type;

BEGIN

open c('MANAGER');

dbms_RESULT.put_line(' EMPLOYEES WORKING AS MANAGERS ARE:');

loop

fetch c into em;

exit when c%notfound;

dbms_RESULT.put_line(em);

end loop;

close c;

open c('ANALYST');

dbms_RESULT.put_line(' EMPLOYEES WORKING AS ANALYST ARE:');

loop

fetch c into em;

exit when c%notfound;

dbms_RESULT.put_line(em);

end loop;

close c;

END;

RESULT:

EMPLOYEES WORKING AS MANAGERS ARE:

JONES

BLAKE

CLARK

EMPLOYEES WORKING AS ANALYST ARE:

SCOTT

FORD

PL/SQL procedure successfully completed.

Writing PL/SQL CURSOR

To write a Cursor to display List of Employees from Emp Table in PL/SQL block

INPUT

DECLARE

cursor c is select empno, ename, deptno, sal from emp ;

i emp.empno%type;

j emp.ename%type;

k emp.deptno%type;

l emp.sal%type;

BEGIN

open c;

dbms_RESULT.put_line('Empno, name, deptno, salary of employees are:= ');

loop

fetch c into i, j, k, l;

exit when c%notfound;

dbms_RESULT.put_line(i||' '||j||' '||k||' '||l);

end loop;

close c;

END;

RESULT:

SQL> @EMP

Empno,name,deptno,salary of employees are:=

7369 SMITH 20 800

7499 ALLEN 30 1600

7521 WARD 30 1250

7566 JONES 20 2975

7654 MARTIN 30 1250

7698 BLAKE 30 2850

7782 CLARK 10 2450

7788 SCOTT 20 3000

7839 KING 10 5000

7844 TURNER 30 1500

7876 ADAMS 20 1100

7900 JAMES 30 950

7902 FORD 20 3000

7934 MILLER 10 1300

PL/SQL procedure successfully completed.

DBMS LAB MANUAL

SQL PLSQL SERVER PROGRAMMES

RESULT

PLSQL C