Temporary table SQL SERVER to CURSOR in PL-SQL

0

Greetings

How could you transform the use of a temporary table to make an update in SQL server to an equivalent code in PLSQL with a cursor?

            ALTER proc [dbo].[sp_lista] (@pcFI datetime, @pcFF datetime, @vSuma tinyint) as
    begin tran

    Declare @crLista Table (
          NEnt int,
          NEje int,
          NExp char(17),
          NAcu char(17),
          NSen char(17)
     )

     if @vSuma = 0
                  begin
                       insert into @crLista 
                       select tablax.num_entrada, tablax.ejercicio,                                               tablax.num_expediente, tablax.num_acuerdo, tablax.num_sentencia,    tablax.descripcion
from tablax inner join tablaz on
tablaz.num_expediente = tablax.num_expediente
where tablax.est_suma = 0 and (EXISTS
  (SELECT 1
  FROM tablay
  WHERE tablay.cve_usuario = SESSION_USER
  and tablay.cve_region = tablaz.cve_region
  and tablay.cve_sala = tablaz.cve_sala
  and tablay.cve_mag = tablaz.cve_mag
  and tablay.cve_srio = tablaz.cve_srio))


    update tablax 
     set notificado = 1,
     fec_notificado = getdate()
     from @crLista MLista
     where MLista.NEnt = tablax.num_entrada and
     MLista.NEje = tablax.ejercicio and tablax.est_suma = @vSuma        

In PL-SQL I have something like the following:

    create or replace PROCEDURE sp_lista(
           v_pcFI IN DATE,
           v_pcFF   IN DATE,
           v_vSuma  IN NUMBER
    )
    AS

    vcMLista SYS_REFCURSOR;

    BEGIN

     IF v_vSuma = 0 THEN
         BEGIN

               open vcMLista for

                    SELECT tablax.num_entrada ,
                      tablax.ejercicio ,
                      tablax.num_expediente ,
                      tablax.num_acuerdo ,
                      tablax.num_sentencia
                    FROM tablax
                    JOIN tablaz
                         ON tablaz.num_expediente = tablax.num_expediente
                            WHERE tablax.est_suma          = 0
                    AND ( EXISTS
                           (SELECT 1
                                 FROM tablay
                                 WHERE tablay.cve_usuario = v_vcUsr_actual
                                 AND tablay.cve_region    =  tablaz.cve_region
                                 AND tablay.cve_sala      = tablaz.cve_sala
                                 AND tablay.cve_mag       = tablaz.cve_mag
                                 AND tablay.cve_srio      = tablaz.cve_srio
                            ))
                            FOR UPDATE;

Later in SQL SERVER a temporary table is used and the following is done:

     update tablax 
     set notificado = 1,
     fec_notificado = getdate()
     from @crLista MLista
     where MLista.NEnt = tablax.num_entrada and
     MLista.NEje = tablax.ejercicio and tablax.est_suma = @vSuma

     update tablay set fmod =getdate()
        where ndoc in 
        (select distinct NExp from @crLista)

How can I do the equivalent in PLSQL with a cursor? I tried the following:

      OPEN crLista;
         FETCH crLista INTO reg;

         WHILE crLista%FOUND LOOP
           UPDATE actm_lista SET  
               notificado = 1,
               FEC_NOTIFICADO = sysdate
               WHERE CURRENT OF crLista;
         FETCH crLista INTO reg;
         END LOOP;

     OPEN crLista;
        FETCH crLista INTO reg;
         WHILE crLista%FOUND LOOP  
             UPDATE tablax
                 SET fmod = SYSDATE
             WHERE ne  IN
                   (SELECT DISTINCT reg.ne FROM crLista);
         FETCH crLista INTO reg;
     END LOOP;



     UPDATE tablay
            SET fmod = SYSDATE
            WHERE ndoc IN
            (SELECT DISTINCT ndoc FROM crLista) ;

But when trying to do the from crLista I get the error the table or view does not exist

I hope you can support me, thanks in advance

    
asked by user3421888 21.02.2017 в 18:47
source

0 answers