Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / MERGE и RETURNING INTO / 19 сообщений из 19, страница 1 из 1
20.09.2005, 11:50
    #33278303
Trampler
Trampler
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
Вопрос вроде бы простой.. Есть одна специфическая таблица, в которую оптимальным решением было бы делать MERGE, однако есть необходимость возвратить значение одного из столбцов после dml-операции. Однако RETURNING INTO в MERGE насколько я понимаю не поддерживается, в итоге все бонусы использования MERGE улетучиваются т.к. придется делать еще один запрос после операции чтобы выяснить значение столбца. С другой стороны, убирать MERGE, и делать вместо него первоначальный запрос с целью определения следующей операции (INSERT, UPDATE) тоже не хочется. Можно ли найти какой-то компромисс, или придется смириться с неизбежностью ввиду отсутствия подходящего синтаксиса? =) Версия 9.2.0.6
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 12:21
    #33278418
Gluk (Kazan)
Gluk (Kazan)
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
Скажите, а КАКОЕ множество строк Вы хотели бы видеть в RETURNING
Проапдейченное или проинсерченное ???
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 12:24
    #33278433
AlexOI
AlexOI
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
Может сделать триггер и заполнять пакетные переменные.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 12:24
    #33278439
Oleg Afanasiev
Oleg Afanasiev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
заполнить в триггере массив?
Код: plaintext
1.
2.
3.
4.
-----------------------
Вечны налоги,
Смерть и потеря данных.
Что на этот раз?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 12:27
    #33278450
Trampler
Trampler
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
2Gluk(Kazan)
Имелась в виду фраза типа

MERGE...
...
WHEN MATCHED THEN UPDATE ... RETURNING ... INTO ...
WHEN NOT MATCHED THEN INSERT ... RETURNING ... INTO ...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 12:42
    #33278526
mcureenab
mcureenab
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
Попробуй триггер

create table t (
n number
);

-- Глобальный пакет для управления функцией и сбора результатов
create package pkg as
enable_log boolean := false; -- Сбор изменений отключен
log_rowid dbms_sql.urowid_table;
log_op dbms_sql.varchar2_table;
end;
/

create trigger t_t
after insert or update
on t
for each row
declare
i binary_integer;
begin
if pkg.enable_log then
i := nvl(pkg.log_rowid.last+1, 1);
pkg.log_rowid(i) := :new.rowid;
if inserting then
pkg.log_op(i) := 'I';
else
pkg.log_op(i) := 'U';
end if;
end if;
end;
/

exec pkg.enable_log := true
insert into t values (1);
update t set n = n+1;

-- Получаем список rowid изменённых строк
exec for i in 1..pkg.log_rowid.last loop dbms_output.put_line(pkg.log_rowid(i)); end loop;

Jl1rADmAAAijWAAA
Jl1rADmAAAijWAAA

-- Получаем список операций
exec for i in 1..pkg.log_rowid.last loop dbms_output.put_line(pkg.log_op(i)); end loop;

I
U

-- Убираем за собой
exec pkg.enable_log := false
exec pkg.log_rowid.delete
exec pkg.log_op.delete
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 12:57
    #33278574
Oleg Afanasiev
Oleg Afanasiev
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
mcureenab
-- Глобальный пакет

Я бы сказал ГАЛЛАКТИЧЕСКИЙ :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 15:28
    #33279162
Trampler
Trampler
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
Спасибо за триггерно-пакетную реализацию, но так делать не хотелось бы. Таблица и так в режиме мультимастер-репликации находится, содержит данные с нескольких узлов (кстати, поэтому делать DML по ROWID не совсем корректно, т.к. уникальность ROWID гарантируется лишь на уровне экземпляра БД а не всей системы баз, связанных репликацией). Так что лишнего усложнения программной логики таблицы не хотелось бы.. Похоже придется остановиться на простом варианте с IF THEN INSERT ELSE UPDATE END IF.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 19:41
    #33279915
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
TramplerСпасибо за триггерно-пакетную реализацию, но так делать не хотелось бы. [...] Похоже придется остановиться на простом варианте с IF THEN INSERT ELSE UPDATE END IF.

На один проход меньше, чем в варианте "select... if-then-insert-else-update":

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
SQL> create table ane_test(Id number, Val number);
Table created.

SQL> insert into ane_test values( 1 , 10 );
 1  row created.

SQL> insert into ane_test values( 2 , 20 );
 1  row created.

SQL> insert into ane_test values( 3 , 30 );
 1  row created.

SQL> insert into ane_test values( 5 , 50 );
 1  row created.

SQL> commit;
Commit complete.

SQL> select * from ane_test;
        ID        VAL                                                           
---------- ----------                                                           
          1           10                                                            
          2           20                                                            
          3           30                                                            
          5           50                                                            

 4  rows selected.

SQL> declare
   2     block_size binary_integer :=  1000 ;
   3     src_id dbms_sql.number_table;
   4     src_val dbms_sql.number_table;
   5   
   6     ptr binary_integer;
   7     src_ins_id dbms_sql.number_table;
   8     src_ins_val dbms_sql.number_table;
   9   
  10     cursor src_data is select id+ 1  Id,val+ 10  Val from ane_test;
  11   begin
  12     open src_data;
  13     loop
  14   	 fetch src_data bulk collect into src_Id, src_Val limit block_size;
  15     exit when src_Id.count =  0 ;
  16   	 if src_Id.count> 0  then
  17   	   forall i in src_Id.first..src_Id.last
  18   	     update ane_test set val=src_Val(i) where id=src_Id(i);
  19   	   ptr :=  0 ;
  20   	   src_ins_id.delete;
  21   	   for i in src_Id.first..src_Id.last loop
  22   	     if sql%bulk_rowcount(i)= 0  then
  23   	       ptr := ptr+ 1 ;
  24   	       src_ins_id(ptr) := src_Id(i);
  25   	       src_ins_val(ptr) := src_Val(i);
  26   	     end if;
  27   	   end loop;
  28   	   if src_ins_id.count> 0  then
  29   	     forall i in src_ins_id.first..src_ins_id.last
  30   	       insert into ane_test (id, val) values(src_ins_id(i),src_ins_val(i));
  31   	   end if;
  32   	 end if;
  33     exit when src_Id.count < block_size;
  34     end loop;
  35     src_Id.delete;  src_ins_Id.delete;
  36     src_Val.delete; src_ins_Val.delete;
  37     close src_data;
  38   end;
  39   /

PL/SQL procedure successfully completed.

SQL> select * from ane_test;
        ID        VAL                                                           
---------- ----------                                                           
          1           10                                                            
          2           20                                                            
          3           30                                                            
          5           50                                                            
          4           40                                                            
          6           60                                                            

 6  rows selected.

SQL> drop table ane_test;
Table dropped.[SRC oracle]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.09.2005, 19:44
    #33279921
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
Да, забыл сказать - вариант для 9i. В 10g цикл "сжатия" промежуточного массива - лишний. Как и массивы src_ins_*
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
24.07.2007, 18:35
    #34681316
bgn
bgn
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
andrey_anonymousДа, забыл сказать - вариант для 9i. В 10g цикл "сжатия" промежуточного массива - лишний. Как и массивы src_ins_*
как?
через save exception но там (все равно нужен массив)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2007, 14:49
    #34683211
bgn
bgn
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
up
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2007, 14:52
    #34683219
dmidek
dmidek
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
bgn andrey_anonymousДа, забыл сказать - вариант для 9i. В 10g цикл "сжатия" промежуточного массива - лишний. Как и массивы src_ins_*
как?
через save exception но там (все равно нужен массив)?

Пусть andrey_anonymous меня поправит (погрузился в тему не до дна),
но мне кажется , что он имеет в виду
новую вариацию FORALL

FORALL Statement: INDICES OF- Clause
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2007, 16:28
    #34683587
barrabas
barrabas
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
   procedure SetRouteListFromArray
   -- =================================================================
   -- Добавляем/изменяем трассу из массива
   -- =================================================================
   is
      aindex pls_integer;
   begin
      begin
         forall i in aRoute.RouteID.first..aRoute.RouteID.last save exceptions
            insert into ROUTE_LIST (ROUTE_ID, ROUTE_SHORT, ROUTE_FULL)
            values (aRoute.RouteID(i), aRoute.RouteShort(i), aRoute.RouteFull(i));
      exception
         when OTHERS then
            for i in  1 ..sql%bulk_exceptions.count
            loop
               aindex := sql%bulk_exceptions(i).error_index;
               aRoute.RouteID.delete(aindex); --Удаляем вставленные записи
               aRoute.RouteShort.delete(aindex);
               aRoute.RouteFull.delete(aindex); 
            end loop;
            forall i in indices of aRoute.RouteID --оставшиеся обновляем
               update ROUTE_LIST
                  set ROUTE_SHORT = aRoute.RouteShort(i),
                      ROUTE_FULL = aRoute.RouteFull(i)
                where ROUTE_ID = aRoute.RouteID(i);

            aRoute.RouteID.delete;
            aRoute.RouteShort.delete;
            aRoute.RouteFull.delete;

      end;
   end;
так? я правильно понял, оптимальнее нельзя?

у меня, с клиента в цикле вставляются записи в запись массивов
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
   procedure SetRouteListArray
      (vRouteID    in   number,          -- ID трассы
       vRouteShort in   varchar2,        -- короткое название
       vRouteFull  in   varchar2)        -- полное название
   -- =================================================================
   -- Добавляем трассу в массив
   -- =================================================================
   is
      block_size pls_integer :=  200 ;
      aindex pls_integer;
   begin
      if InitArrayRouteList(forse => false) then --проверяем инициализированы ли массивы
         aRoute.RouteID.extend;
         aRoute.RouteShort.extend;
         aRoute.RouteFull.extend;
         aindex := aRoute.RouteID.last;
         aRoute.RouteID(aindex) := vRouteID;
         aRoute.RouteShort(aindex) := vRouteShort;
         aRoute.RouteFull(aindex) := vRouteFull;
         if aindex >= block_size
         then
            SetRouteListFromArray; --переносим данные из массива в БД
         end if;
      end if;
   end;
и когда они накапливаются то заносятся в БД, ну в в самом конце опять вызывается SetRouteListFromArray с клиента чтобы занести последнюю партию.
По скорости с merge выйграл %30-40, но можно ли еще?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.07.2007, 18:47
    #34684051
barrabas
barrabas
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
блин касяк в не обновлялось (сам же удалял)

получилоси только так
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
   procedure SetRouteListFromArray
   -- =================================================================
   -- Добавляем/изменяем трассу из массива
   -- =================================================================
   is
      aindex tablePLSInteger;
   begin
      begin
         forall i in aRoute.RouteID.first..aRoute.RouteID.last save exceptions
            insert into ROUTE_LIST (ROUTE_ID, ROUTE_SHORT, ROUTE_FULL)
            values (aRoute.RouteID(i), aRoute.RouteShort(i), aRoute.RouteFull(i));
      exception
         when OTHERS then
            aindex := tablePLSInteger();
            aindex.extend(sql%bulk_exceptions.count);
            for i in  1 ..sql%bulk_exceptions.count
            loop
               aindex(i) := sql%bulk_exceptions(i).error_index;
            end loop;
            forall i in values of aindex --отбовляем 
               update ROUTE_LIST
                  set ROUTE_SHORT = aRoute.RouteShort(i),
                      ROUTE_FULL = aRoute.RouteFull(i)
                where ROUTE_ID = aRoute.RouteID(i);
      end;
      aRoute.RouteID.delete;
      aRoute.RouteShort.delete;
      aRoute.RouteFull.delete;
      aindex.delete;
   end;

как без дополнительного массива обойтись (можно конечно удалять те записи которых нет в sql%bulk_exceptions(i).error_index, но кажется это будет дольше, вложенные цыклы придется делать)?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
27.07.2007, 10:15
    #34687931
barrabas
barrabas
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
УРА, вот, что получилось
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
   procedure SetRouteListFromArray2
   -- =================================================================
   -- Добавляем/изменяем трассу из массива
   -- =================================================================
   is
      aindex tablePLSInteger; --массив индексов для insert
      f boolean;
   begin
      if aRoute.RouteID.count !=  0 
      then
         aindex := tablePLSInteger();
         --Обновляем
         forall i in aRoute.RouteID.first..aRoute.RouteID.last
            update ROUTE_LIST1
               set ROUTE_SHORT = aRoute.RouteShort(i),
                   ROUTE_FULL = aRoute.RouteFull(i)
             where ROUTE_ID = aRoute.RouteID(i);

         --ищем элементы массива которые обновили 0 строк в таблице
         for i in aRoute.RouteID.first..aRoute.RouteID.last
         loop
            if sql%bulk_rowcount(i) =  0 
            then
               f := false;
               --формируем массив индексов массива для корректной вставки
               for j in  1 ..aindex.count 
               loop
                  --проверяем PK, чтобы не не было дубликатов,
                  if aRoute.RouteID(i) = aRoute.RouteID(aindex(j))
                  then
                     aindex(j) := i; --если находим, заменяем индекс на старший
                     f := true;
                     exit;
                  end if;
               end loop;
               if not f --не нашли, добавим
               then
                  aindex.extend;
                  aindex(aindex.last) := i;
               end if;
            end if;
         end loop;
         
         if aindex.count !=  0 
         then
            --вставим
            forall i in values of aindex
               insert into ROUTE_LIST1 (ROUTE_ID, ROUTE_SHORT, ROUTE_FULL)
               values (aRoute.RouteID(i), aRoute.RouteShort(i), aRoute.RouteFull(i));
         end if;
      end if;
      --Убираем за собой
      aRoute.RouteID.delete;
      aRoute.RouteShort.delete;
      aRoute.RouteFull.delete;
      aindex.delete;
   end;

тест
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
declare 
   vRouteID       number;          -- ID трассы (PK)
   vRouteShort    varchar2( 50 );        -- короткое название
   vRouteFull     varchar2( 50 );        -- полное название
   f number;
begin
  
   for i in  1 .. 5000 
   loop
      vRouteID    := i* 1 ;
      vRouteShort := to_char(i);
      vRouteFull  := to_char(i);
--      GS_WEB_SYNCHRONIZATION.SetRouteListArray(vRouteID,vRouteShort,vRouteFull);
      GS_WEB_SYNCHRONIZATION.SetRouteListArray2(vRouteID,vRouteShort,vRouteFull);
   end loop;

   for i in  1 .. 5000 
   loop
      vRouteID    := i* 2 ;
      vRouteShort := to_char(i);
      vRouteFull  := to_char(i);
--      GS_WEB_SYNCHRONIZATION.SetRouteListArray(vRouteID,vRouteShort,vRouteFull);
      GS_WEB_SYNCHRONIZATION.SetRouteListArray2(vRouteID,vRouteShort,vRouteFull);
   end loop;

   for i in  1 .. 5000 
   loop
      vRouteID    := i* 3 ;
      vRouteShort := to_char(i);
      vRouteFull  := to_char(i);
--      GS_WEB_SYNCHRONIZATION.SetRouteListArray(vRouteID,vRouteShort,vRouteFull);
      GS_WEB_SYNCHRONIZATION.SetRouteListArray2(vRouteID,vRouteShort,vRouteFull);
   end loop;
   --Последняя порция трасс
--   GS_WEB_SYNCHRONIZATION.SetRouteListFromArray;
   GS_WEB_SYNCHRONIZATION.SetRouteListFromArray2; 

end;

на разных клонах таблицы ROUTE_LIST (со всеми ключами и индексами)
вариант с save exceptions(SetRouteListFromArray), раз в 7 проиграл SetRouteListFromArray2, а про простой merge или if .. insert .. update вобще молчу.
Заливаются записи теперь со свистом
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
10.09.2020, 14:46
    #39997302
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
коллеги

ничего не поменялось? не появился returning в merge?

 
ms сделал уже
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
declare  @v1         table(id int, v varchar(100))
declare  @v_inserted table(id int, v varchar(100))

;
insert into @v1 values(1,1), (2,2), (3,3)
merge into @v1 v using (select 4 id, 4 v) t on (t.id = v.id)
when not matched then insert(id, v) values(t.id, t.v)
output inserted.id, inserted.v into @v_inserted(id, v)
;

select * from @v_inserted

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
10.09.2020, 14:56
    #39997312
env
env
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
andreymx,

У ms конструкция output с давних времен, как и служебные таблицы inserted, deleted
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
14.09.2020, 16:17
    #39998388
merch
merch
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MERGE и RETURNING INTO
envУ ms конструкция output с давних времен, как и служебные таблицы inserted, deleted

Подай знак, если ты в плену..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / MERGE и RETURNING INTO / 19 сообщений из 19, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 161ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1880904]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 59ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 2ms
others: 11ms
total:  161ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]