Как я могу вернуть данные собственного типа в хранимой процедуре Oracle?

Question

Я создаю данные типа в оракуле для процедуры.

процедура:

create or replace TYPE ROW_USER as object 
(
    ID                  NUMBER(4),
    ROLES_ID            NUMBER(1),
    FIRST_NAME          VARCHAR2(60 BYTE),
    SECOND_NAME         VARCHAR2(60 BYTE),
    FIRST_LAST_NAME     VARCHAR2(60 BYTE),
    SECOND_LAST_NAME    VARCHAR2(60 BYTE),
    CC                  NUMBER(10,0),
    EMAIL               VARCHAR2(60 BYTE)
);

а также:

create or replace type NESTED_ROW_USER as table of ROW_USER;

когда я использую данные этого типа в анонимном блоке, и он работает.

set serveroutput on; 
DECLARE  
    ROWUSR NESTED_ROW_USER;
BEGIN
    select cast( multiset(select id, ROLES_ID, FIRST_NAME, SEGUNDO_NOMBRE, 
    FIRST_LAST_NAME, SECOND_LAST_NAME, CC, EMAIL from USERS where id =  1) as 
    NESTED_ROW_USER)
       into ROWUSR
    from users;  

DBMS_OUTPUT.PUT_LINE(ROWUSR(1).id||','||ROWUSR(1).ROLES_ID||','|| 
    ROWUSR(1).FIRST_NAME);
    END;

результат блока:

Process PL/SQL exited successfully.
1,1,DIEGO

Но когда я использую его в хранимой процедуре, чтобы вернуть его, я получаю сообщение об ошибке.

Процедура:

create or replace PROCEDURE outnested(ROWUSR OUT NESTED_ROW_USR)
AS
BEGIN
  select cast( multiset(select id, ROLES_ID, FIRST_NAME, SECOND_NAME, 
  FIRST_LAST_NAME, SECOND_LAST_NAME, CC, EMAIL from USERS where id =  1) as 
  NESTED_ROW_USER)
    into ROWUSR
    from users;     
END;

ошибка:

ORA-06550: line 5, column 16:
PLS-00302: component 'NESTED_ROW_USER' must be declared
ORA-06550: line 5, column 11:
PL/SQL: Item ignored
ORA-06550: line 14, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored

Почему он не возвращает значение?

Answer 1

Вы действительно пытаетесь создать коллекцию, которая имеет такое же количество строк, как usarios таблица с одинаковыми значениями объекта в каждом элементе коллекции? Это кажется странным.

Я думаю, что вы действительно хотите что-то подобное

create or replace PROCEDURE outnested(p_FILAUSU OUT NESTED_FILA_USUARIO)
AS
BEGIN
  select FILA_USUARIOr( id, 
                        ROLES_ID, 
                        PRIMER_NOMBRE, 
                        SEGUNDO_NOMBRE, 
                        PRIMER_APELLIDO, 
                        SEGUNDO_APELLIDO, 
                        CC, 
                        EMAIL )
    bulk collect into p_FILAUSU 
    from USUARIOS 
   where id =  1;
END;

Answer 2

Я запустил ваш DDL в базе данных Oracle 12c, заменив почтенный EMP стол для вашего usuario стол, и это, казалось, сработало.

Таблицы Oracle EMP и DEPT

Единственной потенциальной ошибкой, которую я заметил, было то, что from usuario дважды в вашем запросе: один раз для мультимножества и один раз как источник внешнего SELECT, Я подставил from dual за from usuario в конце запроса.

Я удалил WHERE пункт для удобства; Я предполагаю, что вы сохраните это.

Убедитесь, что NESTED_FILA_USUARIO Тип принадлежит той же схеме, что и outnested процедуры, или вам придется включить префикс имени схемы и убедиться, что есть прямой грант от владельца NESTED_FILA_USUARIO к схеме outnested (роли не работают в скомпилированном PL/SQL).

create or replace TYPE FILA_USUARIO as object 
(
  EMPNO NUMBER(4),
  ENAME VARCHAR2(10),
  JOB   VARCHAR2(9),
  MGR   NUMBER(4),
  HIREDATE DATE,
  SAL NUMBER(7,2),
  COMM NUMBER(7,2),
  DEPTNO NUMBER(2)
);
create or replace type NESTED_FILA_USUARIO as table of FILA_USUARIO;

Вот анонимный блок:

DECLARE  
    FILAUSU NESTED_FILA_USUARIO;
BEGIN
    select cast( multiset(select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from EMP) as 
    NESTED_FILA_USUARIO)
       into FILAUSU
    from dual;  

DBMS_OUTPUT.PUT_LINE(FILAUSU(1).EMPNO||','||FILAUSU(1).ENAME||','|| 
    FILAUSU(1).JOB);
END;

Вот хранимая процедура с тестом:

create or replace PROCEDURE outnested(FILAUSU OUT NESTED_FILA_USUARIO)
AS
BEGIN
  select cast( multiset(select EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO from EMP) as 
  NESTED_FILA_USUARIO)
    into FILAUSU
    from dual;     
END;
/
declare
  filausu NESTED_FILA_USUARIO;
begin
  outnested(filausu);
  DBMS_OUTPUT.PUT_LINE(FILAUSU(1).EMPNO||','||FILAUSU(1).ENAME||','|| 
    FILAUSU(1).JOB);
end;
/

Выход был такой:

7839, КОРОЛЬ, ПРЕЗИДЕНТ

И это ожидается для этого запроса.