ORA-20000

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

Сначала создадим коллекции, основанные на на простых типах varchar2, integer и на основе записи (record). Тестирование выполняем в схеме SCOTT.

SQL> create or replace type array_varchar as table of varchar2(4000);
2  /

Type created

SQL> create or replace type array_integer as table of integer;
2  /

Type created

SQL>
SQL> create or replace type param as object
2  (
3    param_varchar  varchar2(4000),
4    param_integer  integer
5  );
6  /

Type created

SQL> create or replace type array_param as table of param;
2  /

Type created

SQL> 

Рассмотрим сначала самый простой, но некрасивый и медленный способ.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- декларируем переменные для коллекции
3    --   и сразу же их инициалзируем
4    v_array_varchar array_varchar := array_varchar();
5    v_array_integer array_integer := array_integer();
6    v_array_param array_param := array_param();
7  begin
8    for vCur in (
9                 select emp.empno, emp.ename
10                 from   emp
11                ) loop
12      -- заполняем коллекцию строк
13      v_array_varchar.extend();
14      v_array_varchar(v_array_varchar.count) := vCur.ename;
15      -- заполняем коллекцию чисел
16      v_array_integer.extend();
17      v_array_integer(v_array_integer.count) := vCur.empno;
18      -- заполняем коллекцию записей
19      v_array_param.extend();
20      v_array_param(v_array_param.count) := param(vCur.ename, vCur.empno);
21    end loop;
22    -- посмотрим количесто записей
23    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
24    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
25    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
26  end;
27  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL> 

Правильный способ – это использовать BULK COLLECT INTO.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- Инициализировать уже не надо
3    v_array_varchar array_varchar;
4    v_array_integer array_integer;
5    v_array_param array_param;
6  begin
7    select emp.ename, emp.empno , param(emp.ename, emp.empno)
8    bulk collect into v_array_varchar, v_array_integer, v_array_param
9    from   emp;
10    -- посмотрим количесто записей
11    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
12    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
13    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
14  end;
15  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL> 

Еще один способ использовать функцию COLLECT.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- Инициализировать уже не надо
3    v_array_varchar array_varchar;
4    v_array_integer array_integer;
5    v_array_param array_param;
6  begin
7    select cast(collect(emp.ename) as array_varchar),
8           cast(collect(emp.empno) as array_integer),
9           cast(collect(param(emp.ename, emp.empno)) as array_param)
10    into   v_array_varchar, v_array_integer, v_array_param
11    from   emp;
12    -- посмотрим количесто записей
13    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
14    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
15    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
16  end;
17  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL> 

И еще один способ использовать функцию MULTISET.

SQL> set serveroutput ON
SQL>
SQL> declare
2    -- Инициализировать уже не надо
3    v_array_varchar array_varchar;
4    v_array_integer array_integer;
5    v_array_param array_param;
6  begin
7    select cast(multiset(select emp.ename from emp) as array_varchar),
8           cast(multiset(select emp.empno from emp) as array_integer),
9           cast(multiset(select param(emp.ename, emp.empno) from emp) as array_param)
10    into   v_array_varchar, v_array_integer, v_array_param
11    from   dual;
12
13    -- посмотрим количесто записей
14    dbms_output.put_line('v_array_varchar.count=' || v_array_varchar.count);
15    dbms_output.put_line('v_array_integer.count=' || v_array_integer.count);
16    dbms_output.put_line('v_array_param.count=' || v_array_param.count);
17  end;
18  /

v_array_varchar.count=14
v_array_integer.count=14
v_array_param.count=14

PL/SQL procedure successfully completed

SQL>

Читать полностью