Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Returning, bulk collect, forall. DML нужных значений  [new]
IvaSteel
Member

Откуда: Киев
Сообщений: 244
Здравствуйте!

OS: Windows 2008 R2 SP1
DB: Oracle 10.2.0.5, Oracle 11.2.0.3

Входные данные:
CREATE table iva_test
(
id varchar2(10);
fam varchar2(10);
);

insert into iva_test values ('1', 'ИВЧИК');
insert into iva_test values ('2', 'ОСЕННИКОВ');
insert into iva_test values ('3', 'ПЕТРОВ');
insert into iva_test values ('4', 'МУЗЫКА');
insert into iva_test values ('5', 'ОСЕННИКОВ');
insert into iva_test values ('6', 'ИВЧИК');
insert into iva_test values ('7', 'СЫРОВАТКА');

CREATE table iva_test_1 as select * from iva_test where 1=0;


Удаление данных из табл. iva_test. Передача удаленных данных в коллекцию. Вставка ВСЕХ значений из коллекции в табл. iva_test_1:
declare
Type v_all Is Table Of ibd.iva_test%ROWTYPE Index By Pls_Integer;
v_all_array v_all;
begin
delete from iva_test returning id, fam bulk collect into v_all_array;
  forall idx in v_all_array.first .. v_all_array.last  
  insert into iva_test_dvoyniki_1 values v_all_array(idx);
  --commit;
end;


Как вставить, например только столбик ID использую RETURNING?
Хотелось бы что-то типа такого:

declare
Type v_all Is Table Of ibd.iva_test%ROWTYPE Index By Pls_Integer;
v_all_array v_all;
begin
delete from iva_test returning id, fam bulk collect into v_all_array;
  forall idx in v_all_array.first .. v_all_array.last  
  insert into iva_test_dvoyniki_1(id) values (v_all_array(idx).id);
   --commit;
end;


Да, можно без returning с помощью объектного типа и treat (для Oracle 10), но хотелось бы красиво :)
Спасибо :)
2 мар 12, 16:47    [12184978]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
Elic
Member

Откуда:
Сообщений: 29987
IvaSteel
11.2.0.3
  forall idx in v_all_array.first .. v_all_array.last  
    insert into iva_test_dvoyniki_1(id) values (v_all_array(idx).id);
но хотелось бы красиво
Надо не хотеть, а читать документацию и пробовать - и приятно удивляться.
2 мар 12, 17:12    [12185220]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
IvaSteel
Member

Откуда: Киев
Сообщений: 244
Elic спасибо за ответ. Еще раз специально полез http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/tuning.htm#autoId24.
Получилось :)

Все работает, если указывать поля так:
Type v_all Is Table Of ibd.iva_test%TYPE;
v_all_id v_all;


Но, если мне надо, например 50 полей вставлять. Не объявлять же 50 типов.
А с помощью ROWTYPE не хочет :) Доку специально еще раз почитал. Есть какой-то культурный выход?
2 мар 12, 18:20    [12185619]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
IvaSteel
Member

Откуда: Киев
Сообщений: 244
IvaSteel
Все работает, если указывать поля так:
Type v_all Is Table Of ibd.iva_test%TYPE;
v_all_id v_all;

Очепятка
Type v_all Is Table Of ibd.iva_test.id%TYPE;
2 мар 12, 18:22    [12185636]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
Elic
Member

Откуда:
Сообщений: 29987
IvaSteel
Все работает, если указывать поля так:
Никаких если. И так работает.
IvaSteel
Но, если мне надо, например 50 полей вставлять. Не объявлять же 50 типов.
А с помощью ROWTYPE не хочет
50 из скольких? RTFM
2 мар 12, 18:34    [12185697]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
IvaSteel
Member

Откуда: Киев
Сообщений: 244
Elic
drop table iva_test purge;
drop table iva_test_1 purge;

-- создаем таблицу из 97 столбцов
CREATE table iva_test as select * from v$session where rownum <= 10; 

-- создаем пустую таблицу из 97 столбцов
CREATE table iva_test_1 as select * from iva_test where 1=0;

-- вставка 1 поля в iva_test_1 
declare
Type v_all Is Table Of ibd.iva_test.sid%TYPE;
v_all_sid v_all;

begin
delete from iva_test returning sid bulk collect into v_all_sid;
 forall idx in v_all_sid.first .. v_all_sid.last
  insert into iva_test_1(sid) values (v_all_sid(idx));
--rollback; или commit; 
end;


rollback;

-- вставка 10 полей в iva_test_1
-- создаем 10 типов 
declare
Type v_all Is Table Of ibd.iva_test.sid%TYPE;
v_all_sid v_all;
Type v_all_serial Is Table Of ibd.iva_test.serial#%TYPE;
v_all_ser v_all_serial;
-- остальные типы...............................
Type v_all_server Is Table Of ibd.iva_test.server%TYPE;
v_all_serv v_all_server;
begin

-- перечисляем каждый тип
delete from iva_test returning sid, serial#, server bulk collect into v_all_sid, v_all_ser, v_all_serv;
 forall idx in v_all_sid.first .. v_all_sid.last
  insert into iva_test_1(sid, serial#, server) values (v_all_sid(idx), v_all_ser(idx), v_all_serv(idx));
--rollback; или commit; 
end;


Как обойтись только %ROWTYPE?:
declare
Type v_all Is Table Of ibd.iva_test%ROWTYPE;
v_all_sid v_all;

begin
-- перечисляем каждый тип
delete from iva_test returning sid, serial#, server bulk collect into v_all_sid(sid, serial#, server);
 forall idx in v_all_sid.first .. v_all_sid.last
  insert into iva_test_1(sid, serial#, server) values (v_all_sid(idx), v_all_ser(idx), v_all_serv(idx));
--rollback; или commit; 
end;

PLS-00316: PL/SQL TABLEs must use a single index


Спасибо за терпенье :)
2 мар 12, 19:27    [12185955]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
Elic
Member

Откуда:
Сообщений: 29987
IvaSteel
Спасибо за терпенье :)
Зря. RTFM
3 мар 12, 10:23    [12187316]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
IvaSteel
Member

Откуда: Киев
Сообщений: 244
Elic, ну хватит издеваться :)

Можно ли при удалении данных из таблицы ibd.iva_test возвратить значения некоторых столбцов, например sid, serial#, server в коллекцию v_all_sid объявленную, как %ROWTYPE на эту же таблицу ibd.iva_test?

При этом цель создать максимально быстрый и оптимизированный код, т.е, использовать returning ... bulk collect и forall.
declare
Type v_all Is Table Of ibd.iva_test%ROWTYPE index by pls_integer;
v_all_sid v_all;
begin
-- перечисляем каждый тип
delete from iva_test returning sid, serial#, server bulk collect into v_all_sid;
 forall idx in v_all_sid.first .. v_all_sid.last
   insert into iva_test_1 values v_all_sid(idx);
--rollback; или commit; 
end;

Разумеется, ругается на коллекцию v_all_sid, так как она содержит, кроме вставляемых 3 полей еще и остальные 94 из табл. iva_test.
ORA-06550: Строка 6, столбец 80:
PL/SQL: ORA-00913: слишком много значений
3 мар 12, 14:18    [12188030]     Ответить | Цитировать Сообщить модератору
 Re: Returning, bulk collect, forall. DML нужных значений  [new]
Elic
Member

Откуда:
Сообщений: 29987
IvaSteel
Elic, ну хватит издеваться :)
У тебя большое самомнение. Издеваешься ты, не желая парой слов описать задачу, вместо этого приводя кучу (говно?)кода.
IvaSteel
При этом цель создать максимально быстрый и оптимизированный код, т.е, использовать returning ... bulk collect и forall.
Я тебя уверяю, от перечисления необходимых столбцов в коде ни одна из обеих характеристик не пострадает.
3 мар 12, 14:42    [12188109]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить