Home » SQL & PL/SQL » SQL & PL/SQL » Return TYpe (tablet) as output parameters. (Oracle)
Return TYpe (tablet) as output parameters. [message #678160] Wed, 13 November 2019 15:28 Go to next message
cristine_katia
Messages: 23
Registered: November 2013
Location: SÃO PAULO
Junior Member
Ret


Good afternoon people!

I have a procedure in Oracle with various functions of calculations, simulator type. So you want to assemble a type with output parameters reading this data and bring the results of these functions in real time, because you want to display as output lines on a front (web screen). I have already created the object and table types. However, I have a hard time assembling because I don't know Type well. Can someone help me? Thanks
Re: Return TYpe (tablet) as output parameters. [message #678161 is a reply to message #678160] Wed, 13 November 2019 19:33 Go to previous messageGo to next message
BlackSwan
Messages: 26642
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Why MUST the solution include TYPE datatype?
Re: Return TYpe (tablet) as output parameters. [message #678162 is a reply to message #678161] Wed, 13 November 2019 19:53 Go to previous messageGo to next message
cristine_katia
Messages: 23
Registered: November 2013
Location: SÃO PAULO
Junior Member
Because the mass of data is very large at the time of calculation, so the need for a type, a dynamic form.
Here's a little example of the structure. From there I will play these calculations in type.

Each column refers to the result of a function


CREATE TYPE typ1 AS OBJECT( Valor_Previsto Number,
Valor_simulado Number,
Qtd_simulado Number,
Valor_Iss Number);

[Updated on: Wed, 13 November 2019 19:56]

Report message to a moderator

Re: Return TYpe (tablet) as output parameters. [message #678171 is a reply to message #678162] Thu, 14 November 2019 06:30 Go to previous messageGo to next message
Bill B
Messages: 1917
Registered: December 2004
Senior Member
This can just as easily be brought in as a select row without resorting to type.
Re: Return TYpe (tablet) as output parameters. [message #678186 is a reply to message #678171] Thu, 14 November 2019 22:03 Go to previous messageGo to next message
cristine_katia
Messages: 23
Registered: November 2013
Location: SÃO PAULO
Junior Member

But I do need the type. One for exit and another for entry. My code went like this. It's working, but I need to add a type of parameter input. Does this code have a way to improve or is it good? Follow my code as an example.
thanks

create or replace PROCEDURE proc_motor ( p_ISS IN number,
p_COFINS IN number,
P_IR IN number,
P_IMPOSTO IN number,
P_TAXA IN number ) AS

--Retorno (Saida Funções Calculadas)
v_num1 NUMBER := 0;
v_num2 NUMBER := 0;
v_num3 NUMBER := 0;

-- type que receberá o retorno (Saida Funções Calculadas)
type tret_motor is record ( v_num1 number,
v_num2 number,
v_num3 number);
vretorno tret_motor;

-- função que retorna os valores (Saida Funções Calculadas)
function fun_ret_motor
return tret_motor
is
vret_motor tret_motor;
begin

--Retorno das funções-----------
--FUNCTION 1
v_num1 := fn_ISS( p_iss , p_COFINS );

--FUNCTION 2
v_num2 := fn_ir( P_IR, P_IMPOSTO);

--FUNCTION 3
v_num3 := fn_taxa( P_TAXA, P_IMPOSTO, p_ISS );

--Agora alimentamos o v_row a ser retornado
vret_motor.v_num1 := v_num1;
vret_motor.v_num2 := v_num2;
vret_motor.v_num3 := v_num3;
--
return vret_motor;
end fun_ret_motor;
--
begin
-- Chamada da função
vretorno := fun_ret_motor;
-- Print dos dados
dbms_output.put_line('FUNCTION 1: '||vretorno.v_num1);
dbms_output.put_line('FUNCTION 2: '||vretorno.v_num2);
dbms_output.put_line('FUNCTION 3: '||vretorno.v_num3);

end;
Re: Return TYpe (tablet) as output parameters. [message #678189 is a reply to message #678186] Fri, 15 November 2019 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 66720
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
BlackSwan wrote on Thu, 14 November 2019 02:33
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Why MUST the solution include TYPE datatype?
Re: Return TYpe (tablet) as output parameters. [message #678195 is a reply to message #678189] Fri, 15 November 2019 10:32 Go to previous messageGo to next message
Bill B
Messages: 1917
Registered: December 2004
Senior Member
Types are a neat idea, but over the years I have not seen them really used very often. a daughter table is much easier to use and is MUCH easier to manipulate and query.
Re: Return TYpe (tablet) as output parameters. [message #678201 is a reply to message #678195] Fri, 15 November 2019 16:58 Go to previous message
BlackSwan
Messages: 26642
Registered: January 2009
Location: SoCal
Senior Member
Bill B wrote on Fri, 15 November 2019 08:32
Types are a neat idea, but over the years I have not seen them really used very often. a daughter table is much easier to use and is MUCH easier to manipulate and query.

+10
Previous Topic: who called my procedure through a database link
Next Topic: Query performance condundrum.
Goto Forum:
  


Current Time: Tue Dec 10 00:41:27 CST 2019