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 are different types of tables in sql?

1052

---

Why are cursors used?

1178

---

What is the most restrictive isolation level? : Transact sql

1128

---

How do you copy a table in sql?

1112

---

What does a pl/sql package consist of?

1019

---

How do you optimize a stored procedure in sql?

1019

---

Does oracle roll back the transaction on an error?

1070

---

How do you concatenate in sql?

1086

---

Explain the insert into statements in sql?

1054

---

what is cursor. write example of it. What are the attributes of cursor.

1467

---

What is sharding in sql?

1041

---

Why is a primary key important?

1105

---

What is a temporal data type?

1114

---

How can we find duplicate records in a table?

996

---

Is not equal in sql?

1064

---