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;

What is sp_helptext?

1007

---

What is dense_rank in sql?

918

---

How can I delete duplicate rows?

966

---

How to download oracle sql developer?

1058

---

What is assignment operator in pl sql?

970

---

What is basic structure of pl sql?

992

---

Are null values same as that of zero or a blank space?

971

---

what is single byte over head in oracle..?

2346

---

How do you declare a variable in pl sql?

967

---

Is it possible to include an insert statement on the same table to which the trigger is assigned?

961

---

Why do we need a foreign key?

1025

---

What is the difference between sql and mysql?

1055

---

Can we use distinct and group by together?

995

---

What does truncate mean in sql?

964

---

What is t sql in sql server?

1003

---