Ajuda com procedure Type Pipilined [message #678101] |
Mon, 11 November 2019 04:10 |
|
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 #678103 is a reply to message #678102] |
Mon, 11 November 2019 08:03 |
|
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 #678105 is a reply to message #678104] |
Mon, 11 November 2019 09:11 |
cookiemonster
Messages: 13925 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 #678107 is a reply to message #678106] |
Mon, 11 November 2019 09:33 |
|
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 |
cookiemonster
Messages: 13925 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 #678121 is a reply to message #678112] |
Tue, 12 November 2019 05:24 |
cookiemonster
Messages: 13925 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 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
cristine_katia wrote on Mon, 11 November 2019 16:28My 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?
|
|
|