Home » SQL & PL/SQL » SQL & PL/SQL » Ajuda com procedure Type Pipilined (Oracle)
Ajuda com procedure Type Pipilined [message #678101] Mon, 11 November 2019 04:10 Go to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member
Bom dia pessoal.

Sou nova em BI e estou desenvolvendo uma procedure com chamada de várias funções. Como faço para trazer estas informações da procedure executadas e o retorno das mesmas trazer dentro de um TYPE PIPELINED? Esta informação tipo consulta ,sera uma consulta em front. Não estou conseguindo montar este Pl-sql.
Estou enviando um simples exemplo como quero.

Agradeço pela ajuda.

--Procedure

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

v_num1 NUMBER := 0;
v_num2 NUMBER := 0;
v_num3 NUMBER := 0;

BEGIN

--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 );



END;




---PIPELINED

create or replace procedure RETORNO

FUNCTION BUSCA_RETORNO( v_num1,
v_num2,
v_num3 )

RETURN RETORNO_TYPES.t_simulador_tab PIPELINED

IS

v_row t_simulador_row;

v_tab t_simulador_tab := t_simulador_tab();

TYPE t_row_inicial IS RECORD( v_num1,
v_num2,
v_num3);

v_row_inicial t_row_inicial;

TYPE t_inicial IS TABLE OF t_row_inicial;

v_tab_inicial t_inicial := t_inicial();


BEGIN


FOR i IN v_tab_inicial.first..v_tab_inicial.last LOOP


--Agora alimentamos o v_row a ser retornado

v_row.v_num1 := v_tab_inicial(i).v_num1;
v_row.v_num2 := v_tab_inicial(i).v_num2;
v_row.v_num3 := v_tab_inicial(i).v_num3;

PIPE ROW(v_row);

END LOOP;

END BUSCA_RETORNO;

Re: Ajuda com procedure Type Pipilined [message #678102 is a reply to message #678101] Mon, 11 November 2019 07:57 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
This is an English language forum.

From Google Translate:

I am new to BI and I am developing a procedure with calling various functions. How do I bring this information of the procedure performed and the return of it bring inside a TYPE PIPELINED? This query type information will be a front query. I can't mount this Pl-sql.
I am sending a simple example as I want.
Re: Ajuda com procedure Type Pipilined [message #678103 is a reply to message #678102] Mon, 11 November 2019 08:03 Go to previous messageGo to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member
I am new to BI and I am developing a procedure with calling various functions. How do I bring this information of the procedure performed and the return of it bring inside a TYPE PIPELINED? This query type information will be a front query. I can't mount this Pl-sql.
I am sending a simple example as I want. Exatamente o que preciso! Pode me ajudar como colocar essas informações em um type pipelined

--Procedure

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

v_num1 NUMBER := 0;
v_num2 NUMBER := 0;
v_num3 NUMBER := 0;

BEGIN

--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 );



END;




---PIPELINED

create or replace procedure RETORNO

FUNCTION BUSCA_RETORNO( v_num1,
v_num2,
v_num3 )

RETURN RETORNO_TYPES.t_simulador_tab PIPELINED

IS

v_row t_simulador_row;

v_tab t_simulador_tab := t_simulador_tab();

TYPE t_row_inicial IS RECORD( v_num1,
v_num2,
v_num3);

v_row_inicial t_row_inicial;

TYPE t_inicial IS TABLE OF t_row_inicial;

v_tab_inicial t_inicial := t_inicial();


BEGIN


FOR i IN v_tab_inicial.first..v_tab_inicial.last LOOP


--Agora alimentamos o v_row a ser retornado

v_row.v_num1 := v_tab_inicial(i).v_num1;
v_row.v_num2 := v_tab_inicial(i).v_num2;
v_row.v_num3 := v_tab_inicial(i).v_num3;

PIPE ROW(v_row);

END LOOP;

END BUSCA_RETORNO;
Re: Ajuda com procedure Type Pipilined [message #678104 is a reply to message #678103] Mon, 11 November 2019 08:04 Go to previous messageGo to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member

Just what I need! Can you help me how to put this information into a pipelined type
Re: Ajuda com procedure Type Pipilined [message #678105 is a reply to message #678104] Mon, 11 November 2019 09:11 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It's not clear what you are having a problem with.
You've got a procedure that gets some numbers and does nothing with them and you've got a pipelined function.

So what is the problem?
What goes with what?
Re: Ajuda com procedure Type Pipilined [message #678106 is a reply to message #678105] Mon, 11 November 2019 09:28 Go to previous messageGo to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member
My problem is as follows: I have a procedure with 20 functions. What I need would be, when I execute this procedure and the data returned, I want to throw this result into a type pipilened, like a query. Would it be this.
Re: Ajuda com procedure Type Pipilined [message #678107 is a reply to message #678106] Mon, 11 November 2019 09:33 Go to previous messageGo to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member
EXAMPLE:

The result of this procedure, below I want to play inside a type, understood

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

v_num1 NUMBER: = 0;
v_num2 NUMBER: = 0;
v_num3 NUMBER: = 0;

BEGIN

--FUNCTION 1
v_num1: = fn_ISS (p_iss, p_COFINS);

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

--FUNCTION 3
v_num3: = fn_charge (P_TAXA, P_IMPOST, p_ISS);


END;
Re: Ajuda com procedure Type Pipilined [message #678108 is a reply to message #678107] Mon, 11 November 2019 10:30 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well that procedure doesn't have any results.
You're assigning local variables and doing nothing with them.

Why don't you just call those functions from the pipelined function.

Or make those variables out parameters in the procedure and call the procedure from the pipelined function.
Re: Ajuda com procedure Type Pipilined [message #678109 is a reply to message #678108] Mon, 11 November 2019 10:49 Go to previous messageGo to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member
cookiemonster,Thank you so much for the return.

Do you have any simple examples to help me in constructing this procedure because I don't know the pipelined function well
Re: Ajuda com procedure Type Pipilined [message #678110 is a reply to message #678109] Mon, 11 November 2019 16:31 Go to previous messageGo to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member
Can someone help me?
Re: Ajuda com procedure Type Pipilined [message #678111 is a reply to message #678106] Mon, 11 November 2019 18:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
cristine_katia wrote on Mon, 11 November 2019 07:28
My problem is as follows: I have a procedure with 20 functions.

Do all 20 functions return the same datatype for all 20?

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



Re: Ajuda com procedure Type Pipilined [message #678112 is a reply to message #678111] Mon, 11 November 2019 19:51 Go to previous messageGo to next message
cristine_katia
Messages: 24
Registered: November 2013
Location: SÃO PAULO
Junior Member
Thanks BlackSwan
Re: Ajuda com procedure Type Pipilined [message #678121 is a reply to message #678112] Tue, 12 November 2019 05:24 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you're letting the fact that you've got a pipelined function put you off.
A pipelined function simply returns a rowtype in a way that allows you to call the function as thought it was a table.
Getting data to put in the rowtype is no different to getting data to put in any variable.

If you want the rowtype populated with the result of function calls then just call the functions in the pipelined function.
Re: Ajuda com procedure Type Pipilined [message #678122 is a reply to message #678106] Tue, 12 November 2019 05:45 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
cristine_katia wrote on Mon, 11 November 2019 16:28
My problem is as follows: I have a procedure with 20 functions. What I need would be, when I execute this procedure and the data returned, I want to throw this result into a type pipilened, like a query. Would it be this.
Maybe, before trying to code anything, you should study its syntax instead of inventing yours.
Here is the example of definition and call of pipelined function for the latest Oracle version (as you did not post your):
https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/plsql-optimization-and-tuning.html#GUID-58D850B2-A416-47EA-8803-8 936E2D6748A

Generally, the requirement is to achieve some functionality.
It is seldom required to use some language constructs like "put some information into a pipelined type".
So, what functionality is that "pipelined type" supposed to introduce?

After correcting syntax errors and with guess of its parameters (same as the procedure ones) and structure of data types without posted declaration (same as the new record type):
create or replace function busca_retorno
( 
  p_iss in number,
  p_cofins in number,
  p_ir in number,
  p_imposto in number,
  p_taxa in number
)
return retorno_types.t_simulador_tab pipelined is
  v_row retorno_types.t_simulador_row;
begin
  --function 1
  v_row.v_num1 := fn_iss( p_iss , p_cofins );
  -- use the respective field names of v_row structure 

  --function 2
  v_row.v_num2 := fn_ir( p_ir, p_imposto);

  --function 3
  v_row.v_num3 := fn_taxa( p_taxa, p_imposto, p_iss );

  pipe row(v_row)
end;
/
Note that, as the FN_* functions return scalar value (NUMBER), also the function will return single row.

In the end, the initial question is still in place: how is that function supposed to be called and what is its expected result? Can you state that information?
Previous Topic: AWR Report SQL with Bind Variables
Next Topic: problem to show result in join tables
Goto Forum:
  


Current Time: Thu Mar 28 06:06:42 CDT 2024