Селект в коллекцию с пользовательским типом данных

#sql #oracle #plsql

Как сделать селект в в коллекцию с пользовательским типом данных?

Например, есть таблица:

create table emp (  
  empno    number(4,0),  
  ename    varchar2(10),  
  job      varchar2(9),  
  mgr      number(4,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(2,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
)


Cоздаю пользовательский тип:

CREATE OR REPLACE TYPE typ_emp as OBJECT (
  name VARCHAR2(20),
  deptno VARCHAR2(20)
);

CREATE OR REPLACE TYPE emp_tbl AS TABLE OF typ_emp;


Пишу функцию:

CREATE OR REPLACE FUNCTION getEmpl
  RETURN emp_tbl
  AS
  tbl emp_tbl ;
  BEGIN
    SELECT e.ENAME, e.DEPTNO INTO tbl FROM EMP e;
  RETURN tbl;
  END;


Компиляция с ошибками:


  PL/SQL: ORA-00947: not enough values
  PL/SQL: SQL Statement ignored

    

Ответы

Ответ 1

Вам надо обернуть результат запроса в нужный тип и извлекать через bulk collect into.

CREATE OR REPLACE FUNCTION getEmpl
  RETURN emp_tbl
  AS
  tbl emp_tbl ;
  BEGIN
    SELECT typ_emp(e.ENAME, e.DEPTNO)
    BULK   COLLECT INTO tbl 
    FROM   EMP e;
  END;


Чуть больше примеров на en-so.

И сейчас ваша функция не возвращает никакого результат, это странно.


Ответ 2

Так можно обойтись только с INTO:

create or replace function getEmpl return emp_tbl as
    ret emp_tbl;
begin
    select cast (multiset (select e.name, e.deptno from emp e) as emp_tbl) 
    into ret
    from dual
    ;
    return ret;
end;
/


Не рекомендуется для больших коллекций, т.к. создаются временные объекты в sql контехте
и поэтому уступит по быстродействию bulk collect. И кроме того, fetch ... limit ,
если потребуется, в данном случае не возможен.