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;

How many types of normalization are there?

969

---

Is mariadb a nosql database?

1057

---

How to call a javascript function from pl sql?

1145

---

Is left join faster than join?

1058

---

What is pl sql and why it is used for?

1114

---

How do you run a query?

995

---

What is cursor in pl sql with examples?

1036

---

How many subqueries can be nested in a statement?

992

---

what is the difference between primary key and unique key? : Sql dba

1042

---

what are the limitations of mysql in comparison of oracle? Mysql vs. Oracle. : Sql dba

963

---

Is keyword pl sql?

1012

---

Can I call a procedure inside a function?

1016

---

Is it possible to link two groups inside a cross products after the cross products group has been created?

1006

---

What is prepared statement in sql?

1056

---

What is the advantage of index in sql?

975

---