Блог

Пытаюсь преобразовать коллекцию типов SQL в тип PLSQL.

 -- create an SQL type
create or replace type arrayforvarchar as table of varchar2(30);
/
  

Проверьте ниже анонимный блок:

 declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar := arrayforvarchar();
begin
    select cola
    bulk collect into var_plsql_array
    FROM (
        select 'X' as cola from dual
        union all
        select 'Y' as cola from dual
        );
end;
/
  

Как можно присваивать значения var_plsql_array to var_sql_array , кроме использования циклов?

В любом случае простое назначение не работает.

Не то, что я могу найти:

Использование простого назначения не работает, поскольку массивы имеют разные типы:

 declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    var_sql_array := var_plsql_array;
end;
/
  

Выводит:

 ORA-06550: line 8, column 22:
PLS-00382: expression is of wrong type
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored
  

Попытка использовать CAST не работает в области PL / SQL, поскольку она работает только в области SQL:

 declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    var_sql_array := CAST( var_plsql_array AS arrayforvarchar );
end;
/
  

Выводит:

 ORA-06550: line 8, column 22:
PLS-00204: function or pseudo-column '' may be used inside a SQL statement only
ORA-06550: line 8, column 5:
PL/SQL: Statement ignored
  

Попытка передать коллекцию PL / SQL в область SQL, которую можно использовать CAST , не работает, поскольку коллекции PL / SQL нельзя использовать в области SQL:

 declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    SELECT CAST( var_plsql_array AS arrayforvarchar )
    INTO   var_sql_array
    FROM   DUAL;
end;
/
  

Выводит:

 ORA-06550: line 8, column 18:
PLS-00642: local collection types not allowed in SQL statements
  

Попытка передать коллекцию PL / SQL в выражение коллекции таблиц в области SQL и использование BULK COLLECT не работает по той же причине, что и предыдущее:

 declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    SELECT COLUMN_VALUE
    BULK COLLECT INTO var_sql_array
    FROM   TABLE( var_plsql_array );
end;
/
  

Выводит:

 ORA-06550: line 10, column 19:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 10, column 12:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 8, column 5:
PL/SQL: SQL Statement ignored
  

Однако использование циклов и присвоение значений по отдельности работает:

 declare
    type arrayforvarcharplsql is table of varchar2(30);
    var_plsql_array arrayforvarcharplsql;
    var_sql_array arrayforvarchar;
begin
    var_plsql_array := arrayforvarcharplsql( 'X', 'Y' );
    
    var_sql_array := arrayforvarchar();
    var_sql_array.EXTEND( var_plsql_array.COUNT );
    FOR i IN 1 .. var_plsql_array.COUNT LOOP
      var_sql_array(i) := var_plsql_array(i);
    END LOOP;
end;
/
  

db<> скрипка здесь

Нет, вы не можете присвоить значения одного типа коллекции другому типу коллекции без использования циклов. См. раздел Присвоение значений переменным коллекции:

Совместимость типов данных
Вы можете назначить коллекцию переменной коллекции, только если они имеют один и тот же тип данных.
Одного и того же типа элемента недостаточно.

Даже если любая из этих коллекций объявлена в PL / SQL, она не будет работать:

 declare 
    type t1 is table of int;
    type t2 is table of int;
    a t1 := t1 (1);
    b t2;
begin b := a;
end;
/
ORA-06550: line 6, column 12:
PLS-00382: expression is of wrong type
  

Нет такой внутренней функции, как CAST преобразование коллекции одного типа в коллекцию другого типа. Хотя вы можете использовать свою собственную функцию, чтобы скрыть цикл. Воспроизводимый пример:

 create or replace type arrsql as table of varchar2 (30);
/
var rc refcursor
declare
    type arrpls is table of varchar2(30);
    source arrpls := arrpls ('aaa','bbb','ccc');
    target arrsql;
    function cast (s arrpls, astypeof arrsql) return arrsql is
        t arrsql := arrsql ();
    begin
        t.extend (s.count); 
        for i in 1..s.count loop t(i) := s(i); end loop;
        return t;
    end; 
begin
    target := cast (source, astypeof=>target);
    open :rc for select * from table (target);
end;
/

Result Sequence
------------------------------
aaa
bbb
ccc