Pl/Sql Programs
3
Section-B(PL/SQL)
16. Write a Pl/Sql program to raise the employee salary by 30%, who have completed their 40 years of service. declare cursor c3 is select * from emp where extract(year from sysdate)-extract(year from hiredate)>40 for update; e emp%rowtype; begin open c3; loop fetch c3 into e; exit when c3%notfound; update emp set sal=e.sal+(30/100)*e.sal where current of c3; end loop; close c3; end; / 17. Write a Pl/Sql program to check the given number is Armstrong ‘or’ not. declare n number(3); s number(3):=0; t number(3); begin n:=&n; t:=n; while t>0 loop s:=s+power((t mod 10),3); t:=trunc(t/10); end loop; if(s=n) then dbms_output.put_line('The given number ' || n || 'is an armstrong number'); else dbms_output.put_line('The given number ' || n || 'is not an armstrong number'); end if; end; / 18. Write a Pl/Sql program to display top 10 rows in emp table based on their job and salary. declare cursor c1 is select * from emp order by sal desc; e emp%rowtype; begin
IIMC Prashanth Kuma r K (Head-Dept of Compute rs)
4
open c1; loop fetch c1 into e; exit when c1%rowcount=11 or c1%notfound; dbms_output.put_line('top ::'||c1%rowcount||' employee'); dbms_output.put_line('employee no:'||e.empno); dbms_output.put_line('employee name'||e.ename); dbms_output.put_line('employee job is '||e.job); dbms_output.put_line('employee salary is '||e.sal); dbms_output.put_line('*-*-*-*-*-*-*-*-*-*-*-*-*-*'); end loop; end; / 19. Write a Pl/Sql program to swap two numbers without using third variable. declare a number(3); b number(3); begin a:=&a; b:=&b; dbms_output.put_line('before swapping a= '||a||' and b= '||b); a:=a+b; b:=a-b; a:=a-b; dbms_output.put_line('after swapping a= '||a||' and b= '||b); end; / 20. The hrd manager has decided to raise the employee salary by 20%. Write a Pl/Sql block to accept the employee number and update the salary of that employee. Display appropriate message based on the existence of the record in emp table. declare e emp%rowtype; no
Section-B(PL/SQL)
16. Write a Pl/Sql program to raise the employee salary by 30%, who have completed their 40 years of service. declare cursor c3 is select * from emp where extract(year from sysdate)-extract(year from hiredate)>40 for update; e emp%rowtype; begin open c3; loop fetch c3 into e; exit when c3%notfound; update emp set sal=e.sal+(30/100)*e.sal where current of c3; end loop; close c3; end; / 17. Write a Pl/Sql program to check the given number is Armstrong ‘or’ not. declare n number(3); s number(3):=0; t number(3); begin n:=&n; t:=n; while t>0 loop s:=s+power((t mod 10),3); t:=trunc(t/10); end loop; if(s=n) then dbms_output.put_line('The given number ' || n || 'is an armstrong number'); else dbms_output.put_line('The given number ' || n || 'is not an armstrong number'); end if; end; / 18. Write a Pl/Sql program to display top 10 rows in emp table based on their job and salary. declare cursor c1 is select * from emp order by sal desc; e emp%rowtype; begin
IIMC Prashanth Kuma r K (Head-Dept of Compute rs)
4
open c1; loop fetch c1 into e; exit when c1%rowcount=11 or c1%notfound; dbms_output.put_line('top ::'||c1%rowcount||' employee'); dbms_output.put_line('employee no:'||e.empno); dbms_output.put_line('employee name'||e.ename); dbms_output.put_line('employee job is '||e.job); dbms_output.put_line('employee salary is '||e.sal); dbms_output.put_line('*-*-*-*-*-*-*-*-*-*-*-*-*-*'); end loop; end; / 19. Write a Pl/Sql program to swap two numbers without using third variable. declare a number(3); b number(3); begin a:=&a; b:=&b; dbms_output.put_line('before swapping a= '||a||' and b= '||b); a:=a+b; b:=a-b; a:=a-b; dbms_output.put_line('after swapping a= '||a||' and b= '||b); end; / 20. The hrd manager has decided to raise the employee salary by 20%. Write a Pl/Sql block to accept the employee number and update the salary of that employee. Display appropriate message based on the existence of the record in emp table. declare e emp%rowtype; no