CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqluser-defined-types
atf01
atf01

Reputation: 55

How can I insert into a nested table from the resultset of a select statement?

I have two tables with nested tables of the same type, the type is:

CREATE OR REPLACE TYPE tipo_valor AS OBJECT (
ano        DATE,   --year
cantidad   INTEGER  --ammount of exported wine
) ;

CREATE OR REPLACE TYPE hist_export AS OBJECT (
   nombre_pais   VARCHAR2(100),  --name of importer country
   cantidad      tipo_valor --type referenced above
 );

the nested table:

CREATE OR REPLACE TYPE nt_hist_exp IS
TABLE OF hist_export;

And my two tables are:

CREATE TABLE bodega (  --winery
    id_bod                   INTEGER NOT NULL,
    exp_an_bod               nt_hist_exp ,
)

CREATE TABLE marca (  --wine
id_marca                INTEGER NOT NULL,
exp_an_marca            nt_hist_exp 
)

I have procedure with a select statement that collects the export ammounts from the wine table on a certain year and orders it by country,

PROCEDURE exp_bod ( p_ano DATE,
                      p_bod_nom VARCHAR2)IS
    sumatoria INTEGER;
    p_idbod INTEGER;
    BEGIN
      SELECT id_bod INTO p_idbod
      FROM bodega 
      WHERE nombre_bod = p_bod_nom;
      DBMS_OUTPUT.PUT_LINE(to_char(p_idbod));
      SELECT nt.nombre_pais,sum(nt.cantidad.cantidad)
      INTO sumatoria
      FROM bodega b
      JOIN presentacion p on p.bodega_fk = b.id_bod
      JOIN marca m on m.id_marca = p.marca_fk
      CROSS JOIN TABLE(m.exp_an_marca) nt
      WHERE b.id_bod = p_idbod
      AND nt.cantidad.ano = p_ano
      group by nt.nombre_pais
      order by nt.nombre_pais;
      );

end exp_bod;

the second select in this procedure successfully returns what I need which is a resultset with two columns,one with the country names and the second one with the export ammounts all summed up and ordered, what I want is to insert the rows from that resultset into the nested table in the winery table including the year which is received as an argument by the function

Upvotes: 2

Views: 644

Answers (2)

Alex Poole
Alex Poole

Reputation: 191580

You could use insert as select, creating an instance of your object type as part of the query:

INSERT INTO TABLE (SELECT exp_an_bod FROM bodega b WHERE b.nombre_bod = p_bod_nom)
SELECT hist_export(nt.nombre_pais, tipo_valor(nt.cantidad.ano, sum(nt.cantidad.cantidad)))
FROM bodega b
JOIN presentacion p on p.bodega_fk = b.id_bod
JOIN marca m on m.id_marca = p.marca_fk
CROSS JOIN TABLE(m.exp_an_marca) nt
WHERE b.nombre_bod = p_bod_nom
AND nt.cantidad.ano = p_ano
GROUP BY nt.nombre_pais, nt.cantidad.ano;

I'm assuming nombre_bod is a column on bodega, though you haven't shown that in the table definition, which means you don't really need a separate look-up for that.

This also assumes that exp_an_bod is not null; it can be empty though. It also doesn't make any allowance for an existing row for the country, but it's not very clear from your data model whether than can exist or what should happen if it does. You can update en existing entry using the same mechanism though, as long as you can identify it.

Upvotes: 2

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59652

You can do it in PL/SQL like this:

declare
   hist_exp nt_hist_exp;
begin
   select exp_an_bod
   into hist_exp
   from bodega 
   where id_bod = 123;

   hist_exp.extend;
   hist_exp(hist_exp.LAST) := hist_export('xyz', 456);

   update bodega 
   set exp_an_bod = hist_exp
   where id_bod = 123;

end;

If you like to UPDATE rather then INSERT you can also use

UPDATE (select nombre_pais, cantida, id_bod FROM bodega CROSS JOIN TABLE(exp_an_bod))  
SET nombre_pais = 'abc'
WHERE id_bod = 123
   and cantida = 456;

You may also try 

INSERT INTO (select nombre_pais, cantida, id_bod FROM bodega CROSS JOIN TABLE(exp_an_bod)) ...

but I don't think this is possible - I never tried.

Upvotes: 1

Related Questions