Menu de navegação

[Andre SilvaParticipante]

Boa tarde,

Por gentileza, me auxiliem com um PL SQL recursivo, pois preciso atualizar o valor de uma coluna com base no valor calculado da coluna de outra linha.

Eu testei o CTE Oracle e gerou o valor até a segunda linha.

with e

as (

SELECT placonta, pernumero, saldoanterior,saldo,mov
— saldo= saldoanterior+mov
from testesaldo
where pernumero = 1

union all

SELECT T2.placonta, T2.pernumero, T2.saldoanterior, T1.saldo+T2.MOV,T2.MOV
from testesaldo2 T1
join testesaldo T2
on T1.placonta = T2.placonta and T1.pernumero = T2.pernumero -1
)
select * from e;

 

 

[José Laurindo ChiappaModerador]

Parece ser um caso um tanto complexo : pra gente poder palpitar PLZ manda os CREATE TABLE necessários, uns INSERTs pra popular as tabelas E explica detalhadamente as regras lógicas para vc fazer o JOIN entre as tabelas….

Abraços,

Chiappa

[Andre SilvaParticipante]

Boa tarde,

Eu preciso atualizar o saldo do período 2 através da conta do período 1 (SALDO da linha anterior + MOV do período da linha 2 (e assim por diante)).

Abaixo seguem os scripts:

CREATE TABLE CM.SALDOTESTE
(
PLACONTA VARCHAR2(18 BYTE),
PEREXERCICIO NUMBER,
PERNUMERO NUMBER,
IDPESSOA NUMBER,
MOV NUMBER,
SALDOANTERIOR NUMBER,
SALDO NUMBER
)

 

 

Insert into CM.SALDOTESTE
(PLACONTA, PEREXERCICIO, PERNUMERO, IDPESSOA, MOV, SALDOANTERIOR, SALDO)
Values
(‘215103202 ‘, 2019, 1, 541,
68426.25, -161601.31, -93175.06);
Insert into CM.SALDOTESTE
(PLACONTA, PEREXERCICIO, PERNUMERO, IDPESSOA, MOV, SALDOANTERIOR, SALDO)
Values
(‘215103202 ‘, 2019, 2, 541,
-40965.24, NULL, NULL);
Insert into CM.SALDOTESTE
(PLACONTA, PEREXERCICIO, PERNUMERO, IDPESSOA, MOV, SALDOANTERIOR, SALDO)
Values
(‘215103202 ‘, 2019, 3, 541,
19639.5, NULL, NULL);
Insert into CM.SALDOTESTE
(PLACONTA, PEREXERCICIO, PERNUMERO, IDPESSOA, MOV, SALDOANTERIOR, SALDO)
Values
(‘215103202 ‘, 2019, 4, 541,
-45379.32, NULL, NULL);
Insert into CM.SALDOTESTE
(PLACONTA, PEREXERCICIO, PERNUMERO, IDPESSOA, MOV, SALDOANTERIOR, SALDO)
Values
(‘215103202 ‘, 2019, 5, 541,
42082.43, NULL, NULL);

 

[José Laurindo ChiappaModerador]

Vou ASSUMIR que essa tabela SALDO2 é uma construção artificial, o DESEJADO é nessa mesma única tabela simplesmente atualizar a linha 2 com o valor da linha 1, a linha 3 com o valor da linha 2, a linha 4 com o valor da linha 3, assim por diante…

ORA, na linguagem SQL pra vc poder referenciar o valor ANTERIOR de um SELECT vc ** ABSOLUTAMENTE NÃO PRECISA *** dessa traquitana TODA que vc montou – acessar o valor da linha anterior é a função analítica LAG, ela foi CRIADA para isso , entre outras coisas do tipo… Vou escrever um exemplo em cima dos seus dados mas me parece que ESSA é a alternativa melhor, mais simples (por ser mais curta) E mais rápida (por NÃO envolver JOIN de duas tabelas e/ou CTE)….

Abraços,

 

Chiappa

[José Laurindo ChiappaModerador]

Blz ? Então, no SEU caso específico, vi que a coluna SALDO está nula nos registros abaixo do primeiro :

PLACONTA           PEREXERCICIO  PERNUMERO   IDPESSOA        MOV SALDOANTERIOR      SALDO
------------------ ------------ ---------- ---------- ---------- ------------- ----------
215103202                  2019          1        541   68426,25    -161601,31  -93175,06
215103202                  2019          2        541  -40965,24                         
215103202                  2019          3        541    19639,5                         
215103202                  2019          4        541  -45379,32                         
215103202                  2019          5        541   42082,43                         

5 linhas selecionadas.

==> Então de NADA adianta vc LER (com LAG, como indiquei) o conteúdo ATUAL da coluna SALDO dos registros posteriores, pois ele está NULL : na verdade o que vc precisa pelo que vejo é realmente fazer um UPDATE nas linha POSTERIORES ao registro 1 com o valor ATUALIZADO do registro anterior…. Talvez até desse pra montar uma query cheia de subqueries , ou (talvez) uma Função que pudesse ser chamada a partir do UPDATE, mas IMHO a maneira mais simples (melhor pra dar manutenção mais tarde) seria mesmo um Cursor Loop …. Tipo (em pseudo-código) :

declare
   v_registro_anterior SALDOTESTE%rowtype;
for r in (select * from SALDOTESTE order by PERNUMERO) loop
   if r.PERNUMERO > 1 then
      select * into v_registro_anterior from SALDOTESTE where pernumero = r.pernumero - 1;
      update SALDOTESTE set SALDO = v_registro_anterior.SALDO + r.mov where PERNUMERO = r.PERNUMERO;
   end if;
end loop;

==> veja, esta lógica FAZ o que vc quer (ie, atualizar do registro 2 em diante a coluna SALDO com o valor do SALDO DA LINHA ANTERIOR somando o valor da coluna MOV do registro atual) , e em MUITO menos linhas de código do que a tua traquitana anterior….
Isso NÃO seria a mais performática das Operações (mas modelagens desse tipo, onde a ORDEM dos registros tem significado numa tabela muitas vezes levam à isso), mas ACREDITO que com os índices corretos nem os n SELECTs pra ler o registro anterior e nem os n UPDATEs incorreriam em full table scan, seriam resolvidos por INDEX ACCESS direto (ou então por ROWID se vc incluir o ROWID no LOOP), o que deve ser bom o suficiente… teste lá….

[]s

Chiappa

[José Laurindo ChiappaModerador]

Fiz um teste simples e é isso aí :

SCOTT@xepdb1::CONTAINER=XEPDB1> declare
  2     v_registro_anterior SALDOTESTE%rowtype;
  3  BEGIN
  4     for r in (select * from SALDOTESTE order by PERNUMERO) loop
  5        if r.PERNUMERO > 1 then
  6           select * into v_registro_anterior from SALDOTESTE where pernumero = r.pernumero - 1;
  7           update SALDOTESTE set SALDO = v_registro_anterior.SALDO + r.mov where PERNUMERO = r.PERNUMERO;
  8        end if;
  9     end loop;
 10  END;
 11  /

Procedimento PL/SQL concluído com sucesso.

SCOTT@xepdb1::CONTAINER=XEPDB1> select placonta,PEREXERCICIO,PERNUMERO,IDPESSOA,MOV, SALDO FROM SALDOTESTE ORDER BY PERNUMERO;

PLACONTA           PEREXERCICIO  PERNUMERO   IDPESSOA        MOV      SALDO
------------------ ------------ ---------- ---------- ---------- ----------
215103202                  2019          1        541   68426,25  -93175,06
215103202                  2019          2        541  -40965,24  -134140,3
215103202                  2019          3        541    19639,5  -114500,8
215103202                  2019          4        541  -45379,32 -159880,12
215103202                  2019          5        541   42082,43 -117797,69

5 linhas selecionadas.

SCOTT@xepdb1::CONTAINER=XEPDB1>

==> fazendo as contas : o registro 1 foi pulado pelo IF, no registro 2 MOV = -40965,24 e o Saldo do Registro anterior (registro=1) é = -93175,06, somando ambos temos -134140,3 como resultado…. O registro 3 tem MOV = 19639,5 e o Saldo do registro anterior (registro 2) é -134140,3 aí somando ambos temos -114500,8 como resultado… O registro 4 tem MOV = -45379,32 e o Saldo do registro anterior (registro 3) é -114500,8 aí somando ambos temos -159880,12 como resultado…
Eu, como já disse muitas vezes antes, sou um ENTUSIASTA da opção de usar só e apenas SQL puro sempre que possível (entre outras coisas isso Evita context switch, permite que o Otimizador de SQLs – o CBO – do RDBMS Oracle monte o melhor plano possível), etc, MAS nESTE CASO que a lógica é complexa E que vc tem que fazer UPDATE na linha lida, A VANTAGEM de vc usar lógica procedural é que IMHO é MUITO mais simples de dar manutenção, se vc tiver que mudar as colunas ou implementar qquer lógica adicional via IF é mais simples….

[]s

Chiappa

[Andre SilvaParticipante]

Bom dia Chiappa,

Obrigado pelo retorno e solução.

 

[Autor]

Posts