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

Откуда:
Сообщений: 95
Дорого времени суток!

Подскажите, можно ли так писать? Или как иначе?
myCol точно содержит поля id и r. Будет ещё обработка Exception на отсутствие записи (а если оно внутри update - ошибка изменится?).
Сейчас дает не соответствие типов в подзапросе с table...

update (select t.v, t.r
                   from tab1 t inner join tab2 v on v.v = t.v
                  where t.id in (select id from table(myCol) where r = t.r)
              ) tt        
          set tt.v = null, tt.v = nvl(tt.v, 0) + 1;


Сообщение было отредактировано: 28 май 21, 11:50
28 май 21, 11:54    [22328394]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
Elic
Member

Откуда:
Сообщений: 30185
ARRay001
Сейчас дает не соответствие типов в подзапросе с table...
Никогда не пересказывай ошибки своими словами.
А код приводи более подробно, чтобы были видны типы переменных.
И указывай версию.

Сообщение было отредактировано: 28 май 21, 11:53
28 май 21, 12:01    [22328395]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Elic,
В pkgFunc:
type t_myCol is table of tab1%rowtype;

в Declare процедуры:
myCol pkgFunc.t_myCol:= pkgFunc.t_myCol();

Ошибка: invalid datatype

Oracle Database 18c Enterprise Edition Release 18.0.0.0.0

Сообщение было отредактировано: 28 май 21, 12:01
28 май 21, 12:05    [22328396]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
ARRay001
Member

Откуда:
Сообщений: 95
Вот так работало, с другой коллекцией:

select column_value from table(l_id_tab)


Возможно, синтаксис выше некорректен.

Цель: получить список id из коллекции в подзапросе, не прогоняя в цикле, по условию как выше.
28 май 21, 13:23    [22328413]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
Ты сообщения об ошибках читаешь также как и приводишь?

declare
    type t_myCol is table of dept%rowtype;
    v_tbl t_myCol;
    v_cnt number;
begin
    v_tbl(1).deptno := 10;
    v_tbl(1).dname := 'NEW YORK';
    v_tbl(1).loc := 'ACCOUNTING';
    select  count(*)
      into  v_cnt
      from  table(v_tbl);
end;
/
      from  table(v_tbl);
                  *
ERROR at line 11:
ORA-06550: line 11, column 19:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 11, column 13:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 9, column 5:
PL/SQL: SQL Statement ignored


SQL>


Используй SQL типы.

SY.
28 май 21, 13:31    [22328416]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
SY

Используй SQL типы.


Или пакетный тип (я лично предпочитаю не использовать ибо это все равно неявно создает системно-генерированные обьектный и коллекционные SQL типы):

create or replace
  package pkg1
    is
      type t_myCol is table of dept%rowtype;
end;
/
declare
    v_tbl pkg1.t_myCol := pkg1.t_myCol();
    v_cnt number;
begin
    v_tbl.extend;
    v_tbl(1).deptno := 10;
    v_tbl(1).dname := 'NEW YORK';
    v_tbl(1).loc := 'ACCOUNTING';
    select  count(*)
      into  v_cnt
      from  table(v_tbl);
end;
/

PL/SQL procedure successfully completed.

SQL>


SY.
28 май 21, 13:52    [22328424]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
ARRay001
Member

Откуда:
Сообщений: 95
SY,
а как же тогда select column_value from table(myCol) работало?

И коллекция же заполняется в другом пакете - в самой процедуре ничего не расширяется и не добавляется.

Заполнение делается select t.* bulk collect into

Сообщение было отредактировано: 28 май 21, 13:50
28 май 21, 13:54    [22328426]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
ARRay001

а как же тогда select column_value from table(myCol) работало?


22328424

SY.
28 май 21, 14:00    [22328432]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1705
Пакетные коллекции только для SELECT. Для non-query DML это не поддерживается.
SQL> create table tab1(
  2    id int,
  3    v int,
  4    r int);

Table created.

SQL>
SQL> create or replace package pkgFunc
  2  is
  3    type t_myCol is table of tab1%rowtype;
  4  end;
  5  /

Package created.

SQL>
SQL> declare
  2    myCol pkgFunc.t_myCol := pkgFunc.t_myCol();
  3  begin
  4    myCol.extend(2);
  5    myCol(1).id := 100;
  6    myCol(2).id := 200;
  7    for c in (select * from table(myCol))
  8    loop
  9      dbms_output.put_line(c.id);
 10    end loop;
 11    update (select t.v, t.r
 12                   from tab1 t
 13             where t.id in (select id from table(myCol) where r = t.r)
 14            ) tt
 15       set tt.v = null, tt.v = nvl(tt.v, 0) + 1;
 16  end;
 17  /
100
200
declare
*
ERROR at line 1:
ORA-00902: invalid datatype
ORA-06512: at line 11
28 май 21, 14:01    [22328433]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
То же и с associative array:

create or replace
  package pkg1
    is
      type t_myCol is table of dept%rowtype index by binary_integer;
end;
/
declare
    v_tbl pkg1.t_myCol; -- := pkg1.t_myCol();
    v_cnt number;
begin
--    v_tbl.extend;
    v_tbl(1).deptno := 10;
    v_tbl(1).dname := 'NEW YORK';
    v_tbl(1).loc := 'ACCOUNTING';
    select  count(*)
      into  v_cnt
      from  table(v_tbl);
end;
/

PL/SQL procedure successfully completed.

SQL>


SY.
28 май 21, 14:04    [22328435]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
SeaGate
Пакетные коллекции только для SELECT. Для non-query DML это не поддерживается.


Спасибо, не знал (видимо потому что предпочитаю не использовать ).

SY,
28 май 21, 14:06    [22328436]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SeaGate
Member

Откуда: Новосибирск
Сообщений: 1705
с associative array это явно указано:
https://docs.oracle.com/database/121/LNPLS/release_changes.htm#GUID-57E439FB-B196-46CB-857C-0ADAB32D9EA0
автор
If the PL/SQL-only data type is an associative array, it cannot be used within a non-query DML statement (INSERT, UPDATE, DELETE, MERGE) nor in a subquery.
28 май 21, 14:08    [22328437]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
ARRay001
Member

Откуда:
Сообщений: 95
SY,
Понял - только для SELECT... ясно. Может какой-то другой способ есть сделать нечто подобное?

Сообщение было отредактировано: 28 май 21, 14:13
28 май 21, 14:15    [22328443]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10215
ARRay001

Не совсем понял, получается, если коллекция заполняется в другом пакете по вызову тамошней процедуры, то уже не сработает? Только если заполнять построчно в той же процедуре? Или есть какой-то иной способ для этого случая?


Где заполняется значения не имеет. Главное где определен тип - в пакете или нет.

SY.
28 май 21, 14:22    [22328448]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
ARRay001
Member

Откуда:
Сообщений: 95
SY,

Супер, только тогда смысла нет. Процедура заполнения всё равно в другом пакете и передавать между пакетами всё равно придётся...
28 май 21, 14:29    [22328456]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19237
Проблем с передачей коллекций между пакетами в пределах одной БД нет.
Передача между БД требует внимания, но тоже возможна.
В топике не понятно только одно - нафига вообще применять DML к коллекциям?
28 май 21, 14:48    [22328478]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
ARRay001
Member

Откуда:
Сообщений: 95
andrey_anonymous,
DML в примере применяется не к коллекции, коллекция служит только для выборки данных для DML.
28 май 21, 21:44    [22328690]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
andrey_anonymous
Member

Откуда: Москва
Сообщений: 19237
ARRay001,

Используйте forall
29 май 21, 02:05    [22328732]     Ответить | Цитировать Сообщить модератору
 Re: Работа с коллекциями  [new]
ARRay001
Member

Откуда:
Сообщений: 95
andrey_anonymous,
думаю тут он не поможет. Уже сделал обычным способом.
29 май 21, 10:26    [22328747]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить