Procedure in Package receive an array (coming from a cursor / function) as input parameter - Oracle

Navigation
0

Good afternoon,

I already have a certain experience with PL / SQL but I have now started working with the Oracle database and packages.

I have a problem where my procedure in the package is getting two parameters and from there performing some tasks working with the bulk collect, forall and rowtype to fetch the information in several tables.

The question is: do I need these parameters to come from another function or cursor and from that return, execute my procedure inside the package. This cursor / function must be inside the same package as well.

This cursor / function will query an X table and if this table contains some results, they will search the procedure for the necessary information and insert them into a temporary table that will be consumed by another process.

Has anyone worked with this or do you know more or less the way I can accomplish this?

Thank you

    
asked by anonymous 04.12.2018 / 19:25

1 answer

0

It is possible to create a function with the return of the Type of your collection, in the procedure you want to receive this collection will need to pass by the same type parameter (If you need to manipulate the data of the collection will need to be an IN OUT parameter) / p>

Here is an example similar to what was requested (in the end it has a link to the creation of the tables and query used in the example): 

create or replace package PCK_TESTE is

  Type TPessoas is table of pessoas%Rowtype;

  Function fRetornaPessoas return TPessoas;

  Procedure PreencheTabelaTemporaria(aPessoas IN OUT TPessoas );

end PCK_TESTE;
/
create or replace package body PCK_TESTE is

  Function fRetornaPessoas return TPessoas is
    vRetorno TPessoas; 
    cursor TodasPessoas is
      select * from pessoas;
  begin
    -- Carrega coleção e retorna  
    open TodasPessoas;
    fetch TodasPessoas bulk collect into vRetorno;
    close TodasPessoas;

    return vRetorno;
  end;

  Procedure PreencheTabelaTemporaria( aPessoas IN OUT TPessoas ) is
  begin
    --Exemplo de regra de negócio 
    for i in aPessoas.first .. aPessoas.last loop
      aPessoas(i).nome := aPessoas(i).nome || ' Sobrenome não informado';
    end loop;
    --Efetivação na tabela temporária
    forall i in aPessoas.First .. aPessoas.Last
      insert into TMPpessoas (id, nome, Data_Nascimento) values (aPessoas(i).id, aPessoas(i).nome, aPessoas(i).Data_Nascimento);
  end;

end PCK_TESTE;
/

Method call: 

declare
  vPessoa PCK_TESTE.TPessoas;
begin
  vPessoa := pck_teste.fRetornaPessoas();
  pck_teste.preenchetabelatemporaria(vPessoa);
end;

Examples with data in SQL Fiddle: link

    
06.12.2018 / 19:04
Close div / popup when clicking back button SetInterval - Chrome Extension