I have one requirement as shown below :
Suppose we have EMP table with 5 rows having deptno = 20 --Here i will get deptno i.e.20 from input paramter
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------------------------------------------------------------------------
7566 JONES MANAGER 7839 04/02/1981 2975 - 20
7788 SCOTT ANALYST 7566 12/09/1982 3000 - 20
7902 FORD ANALYST 7566 12/03/1981 3000 - 20
7369 SMITH CLERK 7902 12/17/1980 800 - 20
7876 ADAMS CLERK 7788 01/12/1983 1100 - 20
Now I want to write one UPDATE query in procedure which will update EMP Table set JOB = 'MANAGER' where empno in (7788,7902); -- Here empno i.e. 7788 and 7902 i will get from input parameter
My update query Should update JOB = 'MANAGER' for 2 rows having deptno = 20 and for rest of 3 rows i want to update will Null values.
So that final output will be as shown below :
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------------------------------------------------------------------------------
7566 JONES 7839 04/02/1981 2975 - 20
7788 SCOTT MANAGER 7566 12/09/1982 3000 - 20
7902 FORD MANAGER 7566 12/03/1981 3000 - 20
7369 SMITH 7902 12/17/1980 800 - 20
7876 ADAMS 7788 01/12/1983 1100 - 20
So what will be the best way to write this query
Try:
UPDATE emp
SET job = (CASE empno
WHEN 7788
THEN 'MANAGER'
WHEN 7902
THEN 'MANAGER'
ELSE NULL
END)
WHERE deptno = 20;
Or
UPDATE emp
SET job = (CASE
WHEN empno IN (7788, 7902)
THEN 'MANAGER'
ELSE NULL
END)
WHERE deptno = 20;
Hope this is what you are after...
EDIT: After your comments about the input coming from a number table type something like this should work:
CREATE TYPE number_tab
AS TABLE OF NUMBER
/
Type Created.
CREATE OR REPLACE
PROCEDURE upd_emp (
p_deptno IN emp.deptno%TYPE,
p_empno_tab IN number_tab
)
IS
BEGIN
UPDATE emp e
SET e.job = (SELECT (CASE
WHEN t.column_value IS NULL
THEN NULL
ELSE 'MANAGER'
END)
FROM TABLE(p_empno_tab) t
WHERE t.column_value(+) = e.empno)
WHERE deptno = p_deptno;
EXCEPTION
WHEN others
THEN
...Exception handling code
END upd_emp;
/