using cursors salesman get bonus as 10% of their salary ,
managers get 20% , analalist get 30%. generae a report
showing the employee name, salary , bonus.
- 4 Answers
- 10978 Views
- Fidelity, I also Faced
- E-Mail Answers
Answer Posted / ajit
CREATE OR REPLACE package SCOTT.Insr_sal
IS
TYPE Emp_rec IS RECORD ( V_Empno Emp.Empno%type,
V_Ename EMP.ENAME%type,
V_sal EMP.SAL%TYPE,
V_comm EMP.COMM%TYPE,
V_hiredate EMP.HIREDATE%TYPE,
V_job EMP.JOB%TYPE,
V_Total_sal EMP.Sal%TYPE
);
TYPE Emp_ref_cur IS REF CURSOR
RETURN Emp_rec;
FUNCTION Emp_Sal_hike ( P_job varchar2 )
RETURN Emp_ref_cur;
END;
CREATE OR REPLACE package BODY SCOTT.Insr_sal
IS
FUNCTION Emp_Sal_hike ( P_job varchar2 )
RETURN Emp_ref_cur
IS
C_emp_ref Insr_sal.Emp_ref_cur;
V_raise_sal NUMBER;
BEGIN
CASE
WHEN P_job = 'CLERK' THEN
V_raise_sal := 1.4;
WHEN P_job = 'SALESMAN' THEN
V_raise_sal := 1.6;
WHEN P_job = 'MANAGER' THEN
V_raise_sal := 1.8;
WHEN P_job = 'ANALYST' THEN
V_raise_sal := 1.3;
WHEN P_job = 'PRESIDENT' THEN
V_raise_sal := 2.5;
ELSE
NULL;
END CASE;
OPEN C_emp_ref FOR
SELECT Empno,Ename, Sal, Comm, Hiredate, Job, V_raise_sal * ( Sal + NVL ( Comm, 0 ) ) V_Total_sal
FROM Emp
WHERE job = P_job;
RETURN C_emp_ref;
CLOSE C_emp_ref;
END;
END Insr_sal;
| Is This Answer Correct ? | 1 Yes | 0 No |
Post New Answer View All Answers
what is the difference between a web-garden and a web-farm? : Sql dba
What is the starting oracle error number?
What are types of exception?
Can we change the table name in sql?
Can we group by two columns in sql?
Define a temp table?
How to write a query to show the details of a student from students table whose
What is difference between mysql and postgresql?
Can pl sql procedure have a return statement?
What is error ora-01000: maximum open cursors exceeded
What are the query optimization techniques?
Can sql developer connect to db2?
Why stored procedures are faster than query?
What is join view in sql?
What are procedures used for?
