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;

Can a select statement fire a trigger?

653

---

i have some prob lem to tell me about my self in interview first round ...

1700

---

What is the purpose of a sql?

519

---

What are the operators used in select statements?

568

---

what are the join types in tsql? : Transact sql

569

---

---

What are the usages of sql?

560

---

Why are sql stored procedures used?

611

---

What is trigger in pl sql?

555

---

what is top in tsql? : Transact sql

537

---

What do you know by pl/sql cursors?

559

---

what are the properties and different types of sub-queries? : Sql dba

502

---

Explain how can you save or place your msg in a table?

593

---

Explain the difference between rename and alias?

543

---

What is the purpose of cursors in pl/sql?

631

---

What is raw datatype in sql?

546

---