Funcao Return several lines?

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
joabel
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Wed, 18 May 2005 10:37 am
Location: Chapecó - SC

I have to do a role that returns several rows (equal to a SELECT) to create a report.

Do you have any example or do you know where I can find it?
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

joabel
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 7
Joined: Wed, 18 May 2005 10:37 am
Location: Chapecó - SC

I was able to find a function that returns several lines, it is very simple.

I will post it because it is simple and can be very useful and some cases.
I hope it helps more algem.

Select all

CREATE OR REPLACE TYPE obj_rel_Tab_preço_row AS OBJECT ( 
preço      number(16,6),   
comissao   number(5,2) ,  
preço_2    number(16,6) 
); 
/ 
 
CREATE OR REPLACE TYPE obj_rel_Tab_preço_table AS TABLE OF obj_rel_Tab_preço_row; 
/ 
 
create or replace function F_REL_TABELA_preço 
( 
<parametros> 
) 
  RETURN obj_rel_tab_preço_table AS 
  v_tab obj_rel_tab_preço_table := obj_rel_tab_preço_table(); 
BEGIN 
    -- 
    loop 
      -- 
     . 
      . 
      . 
      -- 
      v_tab.extend; 
      v_tab(v_tab.last) := obj_rel_tab_preço_row( 1, 1.2 ,2 ); 
      -- 
      exit when <condicao de saida> 
    end loop; 
  end if; 
  -- 
  RETURN v_tab; 
  --   
end F_REL_TABELA_preço; 
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 20 guests