Function retornando mais de um valor

[ygor.anjos 23]

Eai galera, tudo bem com vocês?

Estou com uma dificuldade para criar uma fuction que retorna varios valores, vi algumas na internet mas não consegui implementar.

Tenho uma tabela LANCAMENTO:

CREATE
TABLE LANCAMENTO
(
LANCAMENTO_ID NUMBER (3) NOT NULL ,
ALTITUDE_INICIAL FLOAT (5) NOT NULL ,
ALTITUDE_APOGEU FLOAT (5) NOT NULL,
TEMPERATURA NUMBER (3) NOT NULL ,
PRESSAO NUMBER (3) NOT NULL ,
DISTANCIA NUMBER (3) NOT NULL ,
GRUPO_GRUPO_ID NUMBER (3) NOT NULL
) ;
ALTER TABLE LANCAMENTO ADD CONSTRAINT LANCAMENTO_PK PRIMARY KEY ( LANCAMENTO_ID
) ;

eu preciso de uma fuction que retorne a altura(altitude_apogeu - altitude_inicial ), temperatura, pressao, distancia e nome do grupo

CREATE
TABLE GRUPO
(
GRUPO_ID NUMBER (3) NOT NULL ,
NOME_GRUPO VARCHAR2 (100) NOT NULL ,
FOGUETE_FOGUETE_ID NUMBER (3) NOT NULL
) ;
ALTER TABLE GRUPO ADD CONSTRAINT GRUPO_PK PRIMARY KEY ( GRUPO_ID ) ;

O que eu sei até agora:

sei que precisa criar um novo tipo pra retorna os dados, porem n sei se esta correto:

CREATE OR REPLACE TYPE DADOS_TYPE AS OBJECT(
    ALTITUDE_INICIAL FLOAT (5) ,
ALTITUDE_APOGEU FLOAT (5) ,
TEMPERATURA NUMBER (3) ,
PRESSAO NUMBER (3) ,
DISTANCIA NUMBER (3) ,
GRUPO_GRUPO_ID NUMBER (3) )
    
CREATE OR REPLACE TYPE DADOS_TYPE AS TABLE OF DADOS_TYPE;

oque eu não sei é como usar ele na fuction:

CREATE FUNCTION getDADOS(v_grupo IN NUMBER)
RETURN NUMBER
IS DADOS_TYPE AS ???????? ;
BEGIN
SELECT ALTITUDE_INICIAL,ALTITUDE_APOGEU, TEMPERATURA,PRESSAO,DISTANCIA,GRUPO_GRUPO_ID
INTO ????????
FROM LANCAMENTO
WHERE GRUPO_GRUPO_ID = v_grupo;
RETURN(DADOS_TYPE);
END;
/

se alguém puder me ajudar eu agradeço.

PS: sei que preciso fazer join pra pegar o nome do grupo.

[spernega 4]

Boa tarde Ygor,

A única fora de uma função retornar varias linha é com o tipo de retorno "sys_refcursor".

Mas depende de como você vai utilizar isso.

Você não poderia, dentro da função, alimentar uma outra tabela?

[ygor.anjos 23]

Boa tarde Ygor,

A única fora de uma função retornar varias linha é com o tipo de retorno "sys_refcursor".

Mas depende de como você vai utilizar isso.

Você não poderia, dentro da função, alimentar uma outra tabela?

Não precisa retorna varias linhas, preciso retorna varios campos apenas.

[spernega 4]

Ygor,

Veja se este exemplo ajuda

CREATE OR REPLACE PACKAGE test AS

TYPE measure_record IS RECORD(

l4_id VARCHAR2(50),

l6_id VARCHAR2(50),

l8_id VARCHAR2(50),

year NUMBER,

period NUMBER,

VALUE NUMBER);

TYPE measure_table IS TABLE OF measure_record;

FUNCTION get_ups(foo NUMBER)

RETURN measure_table

PIPELINED;

END;

/

CREATE OR REPLACE PACKAGE BODY test AS

FUNCTION get_ups(foo number)

RETURN measure_table

PIPELINED IS

rec measure_record;

BEGIN

SELECT 'foo', 'bar', 'baz', 2010, 5, 13

INTO rec

FROM DUAL;

-- you would usually have a cursor and a loop here

PIPE ROW (rec);

RETURN;

END get_ups;

END;

/

SELECT * FROM table(test.get_ups(0));

[ygor.anjos 23]

tenho que criar uma fuction, nesse exemplo você cria package.

eu to sem BD pra testar mas vendo o exemplo acho que ficaria assim a fuction:

CREATE OR REPLACE TYPE DADOS_TYPE AS OBJECT(
    ALTITUDE_INICIAL FLOAT (5) ,
ALTITUDE_APOGEU FLOAT (5) ,
TEMPERATURA NUMBER (3) ,
PRESSAO NUMBER (3) ,
DISTANCIA NUMBER (3) ,
GRUPO_GRUPO_ID NUMBER (3) )
    
CREATE OR REPLACE TYPE DADOS_TYPE AS TABLE OF DADOS_TYPE;

CREATE FUNCTION getDADOS(v_grupo IN NUMBER)
RETURN NUMBER
IS DADOS_TYPE AS dados ;
BEGIN
SELECT ALTITUDE_INICIAL,ALTITUDE_APOGEU, TEMPERATURA,PRESSAO,DISTANCIA,GRUPO_GRUPO_ID
INTO dados 
FROM LANCAMENTO
WHERE GRUPO_GRUPO_ID = v_grupo;
RETURN(dados);
END;
/

acha que assim funcionaria??

[Motta 645]

http://stackoverflow.com/questions/14744976/how-can-i-return-multiple-values-in-record-type

Declarar e retornar um TYPE , não gosto da solução pois fica presa ao Oracle , não pode ser usada em uma aplicação por exemplo.

[spernega 4]

Fiz um teste aqui e adaptei para um formato que eu já uso.

drop TYPE DADOS_TYPE_table;

CREATE OR REPLACE TYPE DADOS_TYPE AS OBJECT(

id_linh          number(12), -- é um sequencial meio que obrigatório
ALTITUDE_INICIAL FLOAT (5) ,
ALTITUDE_APOGEU  FLOAT (5) ,
TEMPERATURA      NUMBER (3) ,
PRESSAO          NUMBER (3) ,
DISTANCIA        NUMBER (3) ,
GRUPO_GRUPO_ID   NUMBER (3) )
/

CREATE OR REPLACE TYPE DADOS_TYPE_table AS TABLE OF DADOS_TYPE
/

CREATE or Replace FUNCTION getDADOS RETURN DADOS_TYPE_table IS
 --
 TYPE tList IS REF CURSOR;
 sList    tList;
 vList    DADOS_TYPE       := DADOS_TYPE(0,0,0,0,0,0,0);
 ttList   DADOS_TYPE_table := DADOS_TYPE_table();
 --
 cursor c is
  SELECT ALTITUDE_INICIAL, ALTITUDE_APOGEU, TEMPERATURA, PRESSAO, DISTANCIA, GRUPO_GRUPO_ID
  FROM   LANCAMENTO;
  --
  -- SELECT 1 ALTITUDE_INICIAL, 2 ALTITUDE_APOGEU, 3 TEMPERATURA, 4 PRESSAO, 5 DISTANCIA, 6 GRUPO_GRUPO_ID
  -- FROM   dual;
  --
 c_r c%rowtype;
 --
 v_contador number := 1;
 --
BEGIN
  open c;
   loop
    fetch c into c_r;
     exit when c%notfound;
     --
     vlist.id_linh          := v_contador;
     vlist.ALTITUDE_INICIAL := c_r.ALTITUDE_INICIAL;
     vlist.ALTITUDE_APOGEU  := c_r.ALTITUDE_APOGEU;
     vlist.TEMPERATURA      := c_r.TEMPERATURA;
     vlist.PRESSAO          := c_r.PRESSAO;
     vlist.DISTANCIA        := c_r.DISTANCIA;
     vlist.GRUPO_GRUPO_ID   := c_r.GRUPO_GRUPO_ID;
     ttList.extend;
     ttList(vList.id_linh) := vList;  
     v_contador := v_contador + 1;
   end loop;
  close c;
  --
  RETURN(ttList);
END;
/

SELECT * FROM TABLE(getDADOS) where GRUPO_GRUPO_ID = 6;

funcionar funciona mas veja se veja se fica certo pra sua necessidade

Existem outras soluções menos nobres pra isso

[ygor.anjos 23]

http://stackoverflow.com/questions/14744976/how-can-i-return-multiple-values-in-record-type

Declarar e retornar um TYPE , não gosto da solução pois fica presa ao Oracle , não pode ser usada em uma aplicação por exemplo.

A solução que preciso é realmente para ficar presa ao Oracle :)

Fiz um teste aqui e adaptei para um formato que eu já uso.

drop TYPE DADOS_TYPE_table;

CREATE OR REPLACE TYPE DADOS_TYPE AS OBJECT(

id_linh number(12), -- é um sequencial meio que obrigatório

ALTITUDE_INICIAL FLOAT (5) ,

ALTITUDE_APOGEU FLOAT (5) ,

TEMPERATURA NUMBER (3) ,

PRESSAO NUMBER (3) ,

DISTANCIA NUMBER (3) ,

GRUPO_GRUPO_ID NUMBER (3) )

/

CREATE OR REPLACE TYPE DADOS_TYPE_table AS TABLE OF DADOS_TYPE

/

CREATE or Replace FUNCTION getDADOS RETURN DADOS_TYPE_table IS

--

TYPE tList IS REF CURSOR;

sList tList;

vList DADOS_TYPE := DADOS_TYPE(0,0,0,0,0,0,0);

ttList DADOS_TYPE_table := DADOS_TYPE_table();

--

cursor c is

SELECT ALTITUDE_INICIAL, ALTITUDE_APOGEU, TEMPERATURA, PRESSAO, DISTANCIA, GRUPO_GRUPO_ID

FROM LANCAMENTO;

--

-- SELECT 1 ALTITUDE_INICIAL, 2 ALTITUDE_APOGEU, 3 TEMPERATURA, 4 PRESSAO, 5 DISTANCIA, 6 GRUPO_GRUPO_ID

-- FROM dual;

--

c_r c%rowtype;

--

v_contador number := 1;

--

BEGIN

open c;

loop

fetch c into c_r;

exit when c%notfound;

--

vlist.id_linh := v_contador;

vlist.ALTITUDE_INICIAL := c_r.ALTITUDE_INICIAL;

vlist.ALTITUDE_APOGEU := c_r.ALTITUDE_APOGEU;

vlist.TEMPERATURA := c_r.TEMPERATURA;

vlist.PRESSAO := c_r.PRESSAO;

vlist.DISTANCIA := c_r.DISTANCIA;

vlist.GRUPO_GRUPO_ID := c_r.GRUPO_GRUPO_ID;

ttList.extend;

ttList(vList.id_linh) := vList;

v_contador := v_contador + 1;

end loop;

close c;

--

RETURN(ttList);

END;

/

SELECT * FROM TABLE(getDADOS) where GRUPO_GRUPO_ID = 6;

funcionar funciona mas veja se veja se fica certo pra sua necessidade

Existem outras soluções menos nobres pra isso

vou fazer o teste quando eu estiver com acesso ao banco de dados

[ygor.anjos 23]

@spernega

eu precisava sem o cursor, não conseguir tirar. consegue me ajudar?

Fiz um teste aqui e adaptei para um formato que eu já uso.

drop TYPE DADOS_TYPE_table;

CREATE OR REPLACE TYPE DADOS_TYPE AS OBJECT(

id_linh number(12), -- é um sequencial meio que obrigatório

ALTITUDE_INICIAL FLOAT (5) ,

ALTITUDE_APOGEU FLOAT (5) ,

TEMPERATURA NUMBER (3) ,

PRESSAO NUMBER (3) ,

DISTANCIA NUMBER (3) ,

GRUPO_GRUPO_ID NUMBER (3) )

/

CREATE OR REPLACE TYPE DADOS_TYPE_table AS TABLE OF DADOS_TYPE

/

CREATE or Replace FUNCTION getDADOS RETURN DADOS_TYPE_table IS

--

TYPE tList IS REF CURSOR;

sList tList;

vList DADOS_TYPE := DADOS_TYPE(0,0,0,0,0,0,0);

ttList DADOS_TYPE_table := DADOS_TYPE_table();

--

cursor c is

SELECT ALTITUDE_INICIAL, ALTITUDE_APOGEU, TEMPERATURA, PRESSAO, DISTANCIA, GRUPO_GRUPO_ID

FROM LANCAMENTO;

--

-- SELECT 1 ALTITUDE_INICIAL, 2 ALTITUDE_APOGEU, 3 TEMPERATURA, 4 PRESSAO, 5 DISTANCIA, 6 GRUPO_GRUPO_ID

-- FROM dual;

--

c_r c%rowtype;

--

v_contador number := 1;

--

BEGIN

open c;

loop

fetch c into c_r;

exit when c%notfound;

--

vlist.id_linh := v_contador;

vlist.ALTITUDE_INICIAL := c_r.ALTITUDE_INICIAL;

vlist.ALTITUDE_APOGEU := c_r.ALTITUDE_APOGEU;

vlist.TEMPERATURA := c_r.TEMPERATURA;

vlist.PRESSAO := c_r.PRESSAO;

vlist.DISTANCIA := c_r.DISTANCIA;

vlist.GRUPO_GRUPO_ID := c_r.GRUPO_GRUPO_ID;

ttList.extend;

ttList(vList.id_linh) := vList;

v_contador := v_contador + 1;

end loop;

close c;

--

RETURN(ttList);

END;

/

SELECT * FROM TABLE(getDADOS) where GRUPO_GRUPO_ID = 6;

funcionar funciona mas veja se veja se fica certo pra sua necessidade

Existem outras soluções menos nobres pra isso

[spernega 4]

Veja se assim daria certo.

CREATE or Replace 

Function  getDADOS  (p_GRUPO_GRUPO_ID In number,
                     P_LTITUDE_INICIAL In Out Number,
                     P_ALTITUDE_APOGEU In Out Number,
                     P_TEMPERATURA In Out Number,
                     P_PRESSAO In Out Number,
                     P_DISTANCIA In Out Number) Return varchar2 is
BEgin
  --
  SELECT 1 ALTITUDE_INICIAL, 1 ALTITUDE_APOGEU, 1 TEMPERATURA, 1 PRESSAO, 1 DISTANCIA
  Into   P_LTITUDE_INICIAL,
         P_ALTITUDE_APOGEU,
         P_TEMPERATURA,
         P_PRESSAO,
         P_DISTANCIA
  FROM LANCAMENTO
  WHERE GRUPO_GRUPO_ID = p_GRUPO_GRUPO_ID;
  --
  return 'OK';
Exception
 when others then
  P_LTITUDE_INICIAL := 0;
  P_ALTITUDE_APOGEU := 0;
  P_TEMPERATURA := 0;
  P_PRESSAO := 0;
  P_DISTANCIA := 0;
  return 'ERRO';
End;
/

[spernega 4]

Pra testar

variable P_LTITUDE_INICIAL Number

variable P_ALTITUDE_APOGEU Number
variable P_TEMPERATURA Number
variable P_PRESSAO Number
variable P_DISTANCIA Number
variable erro varchar2(1000)
exec :erro := getDADOS  (10, _LTITUDE_INICIAL, _ALTITUDE_APOGEU, _TEMPERATURA, _PRESSAO, _DISTANCIA)
print P_LTITUDE_INICIAL
print P_ALTITUDE_APOGEU
print P_TEMPERATURA
print P_PRESSAO
print P_DISTANCIA
print erro

[spernega 4]

@ = arroba...

[ygor.anjos 23]

@spernega

precisava chamar a função como você chamou antes: SELECT * FROM TABLE(getDADOS) where GRUPO_GRUPO_ID = 6;

como ela n vai retorna varias linhas n precisa do cursor, por isso queria tirar, pra ficar mais simples a fuction

Veja se assim daria certo.

CREATE or Replace 

Function  getDADOS  (p_GRUPO_GRUPO_ID In number,
                     P_LTITUDE_INICIAL In Out Number,
                     P_ALTITUDE_APOGEU In Out Number,
                     P_TEMPERATURA In Out Number,
                     P_PRESSAO In Out Number,
                     P_DISTANCIA In Out Number) Return varchar2 is
BEgin
  --
  SELECT 1 ALTITUDE_INICIAL, 1 ALTITUDE_APOGEU, 1 TEMPERATURA, 1 PRESSAO, 1 DISTANCIA
  Into   P_LTITUDE_INICIAL,
         P_ALTITUDE_APOGEU,
         P_TEMPERATURA,
         P_PRESSAO,
         P_DISTANCIA
  FROM LANCAMENTO
  WHERE GRUPO_GRUPO_ID = p_GRUPO_GRUPO_ID;
  --
  return 'OK';
Exception
 when others then
  P_LTITUDE_INICIAL := 0;
  P_ALTITUDE_APOGEU := 0;
  P_TEMPERATURA := 0;
  P_PRESSAO := 0;
  P_DISTANCIA := 0;
  return 'ERRO';
End;
/

[spernega 4]

Vamos tentar de novo

drop TYPE DADOS_TYPE_table;

CREATE OR REPLACE TYPE DADOS_TYPE AS OBJECT(

id_linh number(12), -- é um sequencial meio que obrigatório

ALTITUDE_INICIAL FLOAT (5) ,

ALTITUDE_APOGEU FLOAT (5) ,

TEMPERATURA NUMBER (3) ,

PRESSAO NUMBER (3) ,

DISTANCIA NUMBER (3) ,

GRUPO_GRUPO_ID NUMBER (3) )

/

CREATE OR REPLACE TYPE DADOS_TYPE_table AS TABLE OF DADOS_TYPE

/

CREATE or Replace FUNCTION getDADOS RETURN DADOS_TYPE_table IS

--

TYPE tList IS REF CURSOR;

sList tList;

vList DADOS_TYPE := DADOS_TYPE(0,0,0,0,0,0,0);

ttList DADOS_TYPE_table := DADOS_TYPE_table();

--

BEGIN

SELECT ALTITUDE_INICIAL, ALTITUDE_APOGEU, TEMPERATURA, PRESSAO, DISTANCIA, GRUPO_GRUPO_ID

Into vlist.ALTITUDE_INICIAL, vlist.ALTITUDE_APOGEU, vlist.TEMPERATURA, vlist.PRESSAO,

vlist.DISTANCIA, vlist.GRUPO_GRUPO_ID

FROM LANCAMENTO;

--

ttList.extend;

ttList(vList.id_linh) := vList;

--

RETURN(ttList);

END;

/

SELECT * FROM TABLE(getDADOS) where GRUPO_GRUPO_ID = 6;