Início / dba / Perguntas / 312438
Accepted
user062
Asked: 2022-05-22 13:30:42 +0800 CST

inserção em uma tabela de objetos com tabela aninhada no oracle

  • 772

Estou com um problema ao inserir em uma tabela aninhada no oracle

Estes são os tipos e tabelas relevantes;

create type movies_type as Table of ref movie_type;

create type actor_type under person_type

(

starring movies_type

) Final;

create table actor of actor_type

NESTED TABLE starring STORE AS starring_nt;

foi assim que tentei inserir

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type(select ref(m) from movie m where movie_id in (7, 8, 9))));

isso não funciona, dá

SQL Error: ORA-00936: missing expression

o que não é muito útil.

eu também tentei aninhar a instrução select entre parênteses porque pensei que poderia ter sido um erro de sintaxe

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type((select ref(m) from movie m where movie_id in (7, 8, 9)))));

mas disse

SQL ERROR ORA-01427: single-row subquery returns more than one row

então eu mudei para isso

insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YY'),TO_DATE('27/12/2016', 'DD/MM/YY'),'USA', movies_type((select ref(m) from movie m where movie_id=7))));

que funcionou, mas não é o que eu quero, pois não me permite ter vários valores em

movies_type

eu não entendo qual é o problema exatamente e as mensagens de erro não são úteis

por que diz expressão ausente?

e por que no segundo caso dá sub-consulta de linha única retorna mais de uma linha?

Muito obrigado.

Atualização: aqui está o tipo movie_type e table movie:

create type movie_type as Object
(
  MOVIE_ID NUMBER(15),
  TITLE VARCHAR(50) ,
  GENRE  VARCHAR(30),
  RELEASE_DATE DATE,
  RUNNING_TIME NUMBER,
  BUDGET NUMBER
) Final;

criação da tabela:

create table MOVIE of movie_type;

ALTER TABLE MOVIE
  ADD CONSTRAINT PK_MOVIE_ID PRIMARY KEY (MOVIE_ID);

ALTER TABLE MOVIE modify TITLE not null;

inserções relevantes no filme:

INSERT INTO MOVIE (MOVIE_ID, TITLE, GENDER, RELEASE_DATE, RUNNING_TIME, BUDGET) VALUES (7,'Star Wars','epic space opera',TO_DATE('25/05/1977', 'DD/MM/YY'),121,11000000);

INSERT INTO MOVIE (MOVIE_ID, TITLE, GENDER, RELEASE_DATE, RUNNING_TIME, BUDGET) VALUES (8,'The Empire Strikes Back','epic space opera',TO_DATE('17/05/1980', 'DD/MM/YY'),124,18000000);

INSERT INTO MOVIE (MOVIE_ID, TITLE, GENDER, RELEASE_DATE, RUNNING_TIME, BUDGET) VALUES (9,'Return of the Jedi','epic space opera',TO_DATE('25/05/1983', 'DD/MM/YY'),132,32500000);
oracle oracle-11g-r2

1 respostas

  1. Best Answer

    movies_type()é a FUNÇÃO CONSTRUTORA paramovies_type

    Requer 1 ou mais instâncias de movie_type. Cada um separado por uma vírgula.

    Você está fazendo apenas uma única instância em seu SQL.

    O encantamento mágico que você procura é:

    Cast(multiset( select... ) as movies_type)

    Como funciona

    movies_typeé um tipo de tabela aninhada.

    Você pode construir um NT de cardinalidade 1 especificando uma única instrução SQL escalar. (isso é o que você tem na sua 3ª tentativa)

    insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA'
    ,movies_type(
        (select ref(m) from movie m where movie_id = 7) -- single element
    )
));

    Além disso, você pode construir um NT de cardinalidade n especificando n elementos. Neste exemplo, forneço 3x instruções SQL escalares.

    insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA'
    ,movies_type(
        (select ref(m) from movie m where movie_id = 7), -- element 1
        (select ref(m) from movie m where movie_id = 8), -- element 2
        (select ref(m) from movie m where movie_id = 9)  -- element 3
    )
));

    Mas, não é isso que você quer.

    Para transformar uma SELECTdeclaração em um Array de elementos, você precisa usar cast(multiset() as data_type)assim:

    insert into actor values
(actor_type(29,'Carrie','Fisher',TO_DATE('21/10/1956', 'DD/MM/YYYY'),TO_DATE('27/12/2016', 'DD/MM/YYYY'),'USA'
    ,cast(multiset(
                    select ref(m) from movie m where movie_id in (7,8,9)
                 ) as movies_type)
));
    • 0

relate perguntas