Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
Пожалуста помогите может кто-то подoбное делал логика задачи простая но не хватает опыта для её реализации (и желательно без явных курсоров чтоб в коде не было слов CURSOR)
Есть две таблицы одна с приходящими данными SA_MB другая хранилище WH_MB
поля в первой (code_mb(varchar2),sum_mb(number),dt_mb(date),id_file(number))
CREATE TABLE sa_mb
(code_mb VARCHAR2(4),
sum_mb NUMBER(15,2),
dt_mb DATE,
id_file NUMBER)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

вторая таблица хранилище WH_MB с такими же полями и +поле is_ective(number)

CREATE TABLE wh_mb
(code_mb VARCHAR2(4) NOT NULL,
sum_mb NUMBER(15,2),
dt_mb DATE NOT NULL,
id_file NUMBER NOT NULL,
is_ective NUMBER)
PCTFREE 10
INITRANS 1
MAXTRANS 255
TABLESPACE users
STORAGE (
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
)
/

Необхобимо написать процедуру(ры) которая бы вставляла новые строчки которые появились в SA_MB в хранилище WH_MB (в WH_MB строчки повторяться не должны )это можно сделать с помощью
(select b.code_mb,b.sum_mb,b.dt_mb,b.id_file from sa_mb b
minus
select w.code_mb,w.sum_mb,w.dt_mb,w.id_file from wh_mb w)
это не проблема
Но проблема в том что когда вставляются новые строчки необходимо чтоб параметр is_ective=1
если у той строки которая по времени последняя а страя становилась при этом 0
Надеюсь понятно изложил !
Если кто что то подобное делал подскажите -можно просто кинуть свой код я его адаптирую!!
25 янв 07, 18:50    [3695647]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116217
Хм. Вроде такой несколько хитрый MERGE делает с оговорками то, что Вам надо...
Можно еще постараться подкручивать, чтобы гарантированно апдейтилась только одна строка.

На всякий случай это Oracle 10g

SQL> 
SQL> drop table scott.emp_new
  2  /

Table dropped

SQL> 
SQL> create table scott.emp_new
  2  as select * from scott.emp where rownum <= 5
  3  /

Table created

SQL> select * from scott.emp_new
  2  /

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
 7369 SMITH      CLERK      7902 17.12.1980     800,00               20
 7499 ALLEN      SALESMAN   7698 20.02.1981    1600,00    300,00     30
 7521 WARD       SALESMAN   7698 22.02.1981    1250,00    500,00     30
 7566 JONES      MANAGER    7839 02.04.1981    2975,00               20
 7654 MARTIN     SALESMAN   7698 28.09.1981    1250,00   1400,00     30

SQL> 
SQL> alter table scott.emp_new add indicator number
  2  /

Table altered

SQL> 
SQL> merge INTO scott.emp_new en using scott.emp e
  2  on (e.empno = en.empno)
  3  when matched
  4  THEN update SET indicator = 0 where hiredate = (select max(hiredate) from scott.emp_new)
  5  WHEN NOT matched
  6  THEN INSERT (empno, ename, deptno, job, mgr, sal, comm, indicator)
  7  VALUES(e.empno, e.ename, e.deptno, e.job, e.mgr, e.sal, e.comm, 1)
  8  /

Done

SQL> select * from scott.emp_new
  2  /

EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO  INDICATOR
----- ---------- --------- ----- ----------- --------- --------- ------ ----------
 7369 SMITH      CLERK      7902 17.12.1980     800,00               20 
 7499 ALLEN      SALESMAN   7698 20.02.1981    1600,00    300,00     30 
 7521 WARD       SALESMAN   7698 22.02.1981    1250,00    500,00     30 
 7566 JONES      MANAGER    7839 02.04.1981    2975,00               20 
 7654 MARTIN     SALESMAN   7698 28.09.1981    1250,00   1400,00     30          0
 7902 FORD       ANALYST    7566               3000,00               20          1
 7900 JAMES      CLERK      7698                950,00               30          1
 7782 CLARK      MANAGER    7839               2450,00               10          1
 7844 TURNER     SALESMAN   7698               1500,00      0,00     30          1
 7698 BLAKE      MANAGER    7839               2850,00               30          1
 7934 MILLER     CLERK      7782               1300,00               10          1
 7839 KING       PRESIDENT                     5000,00               10          1
 7788 SCOTT      ANALYST    7566               3000,00               20          1
  222 ss         ww          222                222,00               40          1
 7876 ADAMS      CLERK      7788               1100,00               20          1

15 rows selected

SQL> 
25 янв 07, 19:07    [3695730]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
Очень близко к правде кажется но в вашем merge ставится условие равенства empno в обеих таблицах в моей же вероятно равенство полей code_mb, id_file Но в любом случае очень интересное и лёгкое решение для казалось бы сложной задачи!!
25 янв 07, 19:20    [3695781]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116217
alient
Очень близко к правде кажется но в вашем merge ставится условие равенства empno в обеих таблицах в моей же вероятно равенство полей code_mb, id_file Но в любом случае очень интересное и лёгкое решение для казалось бы сложной задачи!!


Насколько я понял Ваш пример, в роли empno у Вас выступают все колонки
из MINUS- оператора.

Еще раз подчеркну - решение для десятки.
Только там можно использовать WHERE - условия в UPDATE / INSERT - clauses.
25 янв 07, 19:25    [3695800]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 18363
dmidek
Еще раз подчеркну - решение для десятки.
Только там можно использовать WHERE - условия в UPDATE / INSERT - clauses.

IMHO это решение какой-то сосем другой задачи..
Если я правильно понял, то автору надо поддерживать признак "активности" для самой свежей записи в группе. И "продвигать" этот признак на новую запись при вставке.
25 янв 07, 23:55    [3696423]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
dmidek
Member

Откуда: Киев - Дортмунд
Сообщений: 116217
andrey_anonymous
dmidek
Еще раз подчеркну - решение для десятки.
Только там можно использовать WHERE - условия в UPDATE / INSERT - clauses.

IMHO это решение какой-то сосем другой задачи..
Если я правильно понял, то автору надо поддерживать признак "активности" для самой свежей записи в группе. И "продвигать" этот признак на новую запись при вставке.


Может быть, но автору вроде понравилось :-)
В решение показано ИМХО наглядно.
На всякий случай я озвучу "свою" задачу - передача новых записей с индикатором 1
и присваивание последней записи из уже существующих индикатора 0.
26 янв 07, 00:02    [3696432]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
JoeD
Member

Откуда: Москва
Сообщений: 266
Согласен с andrey_anonymous - по крайней мере я уже сталкивался с похожей постановкой :) - данные должны хранится для истории, последние должны быть помечены. Приложение использует код вида ".. and indicator = 1 .."
Можно использовать триггеры + процедуры. Можно разбить update на куски - сначала снимаем признак актуальности у строк которые будут сдублированы, потом вставляем записи. Надо смотреть на объемы и т.д.

После merge у "эмплоя" 7654 не остается актуальных данных вообще. Т.е. нужен мерge типа

WHEN MATCHED
THEN BEGIN update SET ind = 0; INSERT (...); end

но я так не умею сказать :)

Хотя у автора описан какой-то промежуточный вариант :) С одной стороны вставляются записи, которых еще не было (сравнение как я понял по уникальному ключу), а с другой : "страя становилась при этом 0". Что считать "страя" вот вопрос :)
26 янв 07, 01:13    [3696497]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
Владимир Бегун
Member

Откуда: Redwood Shores, CA USA
Сообщений: 1707
alient
Но проблема в том что когда вставляются новые строчки необходимо чтоб параметр is_ective=1 если у той строки которая по времени последняя а страя становилась при этом 0
MERGE INTO wh_mb wh
USING (
  SELECT CASE
           WHEN COUNT(*) OVER () = 1
           THEN 1
           ELSE -1 + ROW_NUMBER() OVER (ORDER BY dt_mb)
         END is_active
       , code_mb
       , sum_mb
       , dt_mb
       , id_file
    FROM (
         SELECT code_mb, sum_mb, dt_mb, id_file
           FROM sa_mb d -- only one, newly inserted row has to be returned
          WHERE NOT EXISTS (
                  SELECT null
                    FROM wh_mb w
                   WHERE d.code_mb = w.code_mb
                     AND d.id_file = w.id_file
                     AND d.sum_mb = w.sum_mb
                     AND d.dt_mb = w.dt_mb
                )
         UNION ALL
         SELECT code_mb, sum_mb, dt_mb, id_file
           FROM wh_mb
          WHERE is_active = 1
         )
) n ON (
      n.code_mb = wh.code_mb
  AND n.id_file = wh.id_file
  AND n.sum_mb = wh.sum_mb
  AND n.dt_mb = wh.dt_mb
)
WHEN MATCHED THEN UPDATE SET wh.is_active = n.is_active
WHEN NOT MATCHED THEN INSERT VALUES (n.code_mb, n.sum_mb, n.dt_mb, n.id_file, n.is_active)
Посмотри подходит ли вот этот вариант. Дополнительно наверное можно добавить условия позволяющие избежать изменений текущей is_active записи -- нет смысла менять 1 на 1. И наверное всё-таки там не все значения проверяются т.е. в правильности оригинальной идеи с MINUS я не уверен.
26 янв 07, 08:20    [3696741]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
Владимир Бегун ваш запрос добавляет 1 к is_ective и в итого это поле принимает значение от 1 до 6 и более сколько записей а надо чтоб поле принимало 1 или 0 а минусом вставляются новые строки и надо только найти повтор по всем полям кроме даты а в дате взять последнюю!!
26 янв 07, 10:43    [3697483]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
Владимир Бегун
Member

Откуда: Redwood Shores, CA USA
Сообщений: 1707
alient
Владимир Бегун ваш запрос добавляет 1 к is_ective и в итого это поле принимает значение от 1 до 6 и более сколько записей а надо чтоб поле принимало 1 или 0 а минусом вставляются новые строки и надо только найти повтор по всем полям кроме даты а в дате взять последнюю!!
is_active, колонка должна занываться is_active -- что за мода колонки называть безграмотно? Он работает ровно с теми prerequisites которые я указал "FROM sa_mb d -- only one, newly inserted row has to be returned" -- предполагалось что ты его поменяешь для своих нужд.
26 янв 07, 10:52    [3697556]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
Владимир Бегун ваш запрос добавляет 1 к is_ective и в итого это поле принимает значение от 1 до 6 и более сколько записей а надо чтоб поле принимало 1 или 0 а минусом вставляются новые строки и надо только найти повтор по всем полям кроме даты а в дате взять последнюю!!
26 янв 07, 11:03    [3697665]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
Очень похоже на правду но в вашем merge отсутсвует ключевое слово where перед on
26 янв 07, 11:07    [3697697]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
Уважаемый didek в вашем merge отсуствует ключевое слово where перед on
иак говорит компилятор
26 янв 07, 11:22    [3697876]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
Jannny
Member

Откуда: Спб
Сообщений: 6424
alient
Уважаемый didek в вашем merge отсуствует
В Вашем тоже кое-чего :)

alient
в вашем merge отсуствует ключевое слово where перед on
иак говорит компилятор
Запускаем на 10ке, как оговаривалось?
26 янв 07, 11:48    [3698100]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
очень хочется чтоб и на 9 что то получилось!!!
26 янв 07, 12:00    [3698181]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
Правильный ответ по обновлению статуса за последнюю дату
Update wh_mb set is_ective=0
where id_file in (
select b.id_file from wh_mb a,wh_mb b
where a.code_mb=b.code_mb
and a.id_file=b.id_file
and a.dt_mb>b.dt_mb
)
and code_mb in (select b.code_mb from wh_mb a,wh_mb b
where a.code_mb=b.code_mb
and a.id_file=b.id_file
and a.dt_mb>b.dt_mb
)
and dt_mb in (select b.dt_mb from wh_mb a,wh_mb b
where a.code_mb=b.code_mb
and a.id_file=b.id_file
and a.dt_mb>b.dt_mb
)

Вставка записей

insert into wh_mb (code_mb,sum_mb,dt_mb,id_file,is_ective)
select m.code_mb,m.sum_mb,m.dt_mb,m.id_file,1 from
(select b.code_mb,b.sum_mb,b.dt_mb,b.id_file from sa_mb b
minus
select w.code_mb,w.sum_mb,w.dt_mb,w.id_file from wh_mb w) m
---------------------Всё гениальное просто!!
26 янв 07, 17:31    [3701012]     Ответить | Цитировать Сообщить модератору
 Re: Update AND Insert  [new]
alient
Member

Откуда:
Сообщений: 318
и commit посередине!!
29 янв 07, 13:23    [3707048]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить