Consider the following Entities and their Relationships Department (deptno, deptname, location) Employee (empno, empname, salary, commission, designation) Relationship between Department and Employee is one-to-many.Constraints : Primary Key Create a RDB in 3NF & write queries in Oracle 8i for following.
a) Create or Replace a PL/SQL Procedure to increase the salary of a given employee by 5% & display updated salary.
b) Write a Cursor to display details of all Employees of all Departments.
declare cursor c1 is select deptno,sum(sal) tot from emp group by deptno; –rec c1%rowtype; sal emp.sal%type; begin for rec in c1 loop dbms_output.put_line(‘Deptno : ‘||rec.deptno||’ sum of salary : ‘||rec.tot); dbms_output.put_line(‘ ‘); end loop; select sum(sal) into sal from emp; dbms_output.put_line(‘total salary of all the department : ‘||sal); end; //* WAB which accept city from user and display empno and name employees living in that city.
*/ declare cursor c1(xcity varchar2) is select * from emp where city=xcity order by city; rec c1%rowtype; tcity emp.city%type; begin tcity:=’&city’; for rec in c1(tcity) loop dbms_output.put_line(’empno : ‘||rec.empno||’ empname : ‘|| rec.empname||’ city : ‘||rec.city); dbms_output.put_line(‘ ‘); end loop; end; //* Use of parameterized cursor.
SOLUTION 2: a] create or replace procedure p4 (p_no in number, p_sal in int) as v_name emp.ename%type; v_sal emp.sal%type; begin select ename,sal into v_name,v_sal from emp where empno=p_no; update emp set sal=sal p_sal*0.1 where empno=p_no; v_sal:=v_sal v_sal*0.1; dbms_output.put_line(‘modified salary of ‘||v_name||’is ‘||v_sal); end p4; Procedure created.’ ‘