Условный запрос обновления Oracle

У меня есть одно требование, как показано ниже:

Предположим, у нас есть таблица EMP с 5 строками, имеющими deptno = 20. Здесь я получу deptno, т.е. 20, из входного параметра.

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

Теперь я хочу написать один запрос UPDATE в процедуре, которая обновит набор таблиц EMP JOB = 'MANAGER', где empno in (7788,7902); -- Здесь empno т.е. 7788 и 7902 я получу из входного параметра

Мой запрос на обновление должен обновить JOB = 'MANAGER' для 2 строк, имеющих deptno = 20, а для остальных 3 строк, которые я хочу обновить, будут нулевые значения.

Таким образом, окончательный результат будет таким, как показано ниже:

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

Итак, как лучше всего написать этот запрос


oracle procedure
person user1017936    schedule 30.11.2011    source источник
comment
Вы говорите, что получите emno в качестве входного параметра. Какой формат имеет ваш входной параметр? Это список CSV? Таблица PL/SQL? IOW, как вы принимаете несколько значений emno?   -  person DCookie    schedule 30.11.2011

Ответы (2)


arrow_upward
6
arrow_downward

Пытаться:

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;

Надеюсь, это то, что вам нужно...

РЕДАКТИРОВАТЬ: после ваших комментариев о вводе, поступающем из типа таблицы чисел, должно работать что-то вроде этого:

CREATE TYPE number_tab
AS TABLE OF NUMBER
/

Тип Создан.

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;
/
person Ollie    schedule 30.11.2011
comment
Извините, я забыл упомянуть одну вещь в примере, что я получаю свой входной параметр в виде коллекции. Итак, в этом сценарии я не могу написать простой оператор case. Поэтому я должен использовать некоторый цикл вместо прямого использования случая, когда оператор, который создает проблему - person user1017936; 30.11.2011
comment
Хорошо, какая коллекция является вашим входным параметром? - person Ollie; 30.11.2011
comment
Его объявленный пользователем тип таблицы чисел - person user1017936; 01.12.2011
comment
ОК, см. новое редактирование моего ответа. Вам придется поиграть с ним для вашего точного сценария, но он должен быть довольно близким. - person Ollie; 01.12.2011

arrow_upward
1
arrow_downward

Для ясности я бы предпочел решить эту проблему с помощью двух операторов обновления.

UPDATE emp SET job = null
WHERE deptno = 20
AND empno NOT IN (7788, 7902);

UPDATE emp SET job = 'MANAGER'
WHERE deptno = 20
AND empno IN (7788, 7902);

Если вы хотите поместить его в одно обновление, вы можете сделать это следующим образом:

UPDATE emp SET job = DECODE(empno, 7788, 'MANAGER', 7902, 'MANAGER', null)
WHERE deptno = 20;
person bpgergo    schedule 30.11.2011
comment
Том Кайт рекомендует использовать CASE, а не декодирование — декодирование несколько неясно — CASE очень и очень ясен. То, что легко сделать в декодировании, легко сделать в CASE, то, что сложно или почти невозможно сделать с помощью декодирования, легко сделать в CASE. СЛУЧАЙ, логика мудрая, побеждает безоговорочно. - person Ollie; 30.11.2011
comment
Ага, case предпочтительнее, чем decode, с этим согласен. Однако я хочу сказать, что в некоторых случаях, если у вас нет соображений производительности, оба варианта обескуражены. Особенно, когда ваш SQL генерируется приложением промежуточного программного обеспечения, проще сделать это с помощью двух отдельных обновлений. - person bpgergo; 30.11.2011
comment
Я бы все же предпочел сделать это в одном обновлении, меньше работы, меньше переключений контекста, один оператор для поддержки и т. д. - person Ollie; 30.11.2011