Pl/Sql Programs

Topics: SQL, Relational model, Computer Pages: 5 (1293 words) Published: August 22, 2013
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 emp.empno %type; sa emp.sal%type; begin no:=&no; select * into e from emp where empno=no; sa:=e.sal+(15/100)*e.sal; update emp set sal=sa where empno=no; dbms_output.put_line('employee record is modified'); dbms_output.put_line('employee number is '||e.empno); dbms_output.put_line('employee name is '||e.ename); dbms_output.put_line('employee job is '||e.job); dbms_output.put_line('employee sal is '||sa); end; / IIMC Prashanth Kuma r K (Head-Dept of Compute rs)

5

21. Write a Pl/Sql program to generate multiplication tables for 3 & 7. declare i number(3); begin i:=1; for i in 1..10 loop dbms_output.put_line(3||' * '||i||' = '||3*i); end loop; dbms_output.put_line('***********************'); for i in 1..10 loop dbms_output.put_line(7||' * '||i||' = '||7*i); end loop; end; / 22. Write a Pl/Sql program to display the given number is prime or not? DECLARE n number:=&n; j number:=2; counter number:=0; BEGIN WHILE(j10000) then raise_application_error(-20000,'Not allowed to update'); end if; IIMC Prashanth Kuma r K (Head-Dept of Compute rs)

6

end; SQL> update emp Set sal=15000; 24. Write a Pl/Sql program to raise the employee salary by 10%, for department number 30 people and also maintain the raised details in the raise table. create table raise_emp (empid number(4) primary key, name varchar2(10), desig varchar2(9), mgr number(4), doj date, salary number(7,2), comm number(7,2), dno number(2)); declare cursor c2 is select * from emp where deptno=30 for update; e emp%rowtype; begin open c2; loop fetch c2 into e; exit when c2%notfound; update emp set sal=e.sal+(10/100)*e.sal where current of c2; end loop; dbms_output.put_line('employee details are stored in the emp_raise table'); insert into raise_emp(select * from emp where deptno=30); close c2; end; / 25. Write a procedure to update the salary of employee, who are not getting commission 9%. create or replace procedure raise_comm as begin update emp set sal=sal+(10/100)*sal where comm is null or comm=0; end; / 26....
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Pl/Sql Excersizes Essay
  • SQL vs. Oracle Essay
  • sql notes Research Paper
  • Program Essay
  • Program Essay
  • Essay on Program
  • Programs Essay
  • Sql Essay

Become a StudyMode Member

Sign Up - It's Free