Dizer a quantidade de linhas a serem atualizadas

facc · Sáb, 06 Jun 2009 9:45 am

Tenho uma procedure que faz um update em uma tabela com 100.000 registros, quero atualizar apenas algumas linhas (não sei quais serão essas linhas!) mas irei dizer a quantidade a ser atualizada.

Ou alguém pode me dar uma luz de como bloquear uma certa quantidade de registros a serem usados apenas para quem pediu esse bloqueio?

Hoje estou fazendo assim, mas não sei como, mas outra loja conseguiu reservar o mesmo número

Selecionar tudo

CREATE OR REPLACE PROCEDURE CYBELAR_RES_NRSORTE(P_LOJA     IN varchar2,
                                                P_PDV      IN VARCHAR2,
                                                P_QTD      IN NUMBER,
                                                P_NROSORTE OUT varchar2,
                                                P_CONFIRMA OUT NUMBER) is
  /***********************************************************
  * PROCEDURE : CYBELAR_RES_NRSORTE                          *
  * OBJETIVO  : RESERVAR O NRSORTE P/ A LOJA E "ESCONDER"    *
                DAS DEMAIS LOJAS E OUTRAS RESERVAS           *
  * CRIACAO   : 08/05/2009                                   *
  * VERSAO    : 1.0                                          *
  * AUTOR     : FABIO A. CAMPOS CRUZ -*
  ***********************************************************/

  retorno_nrsorte varchar2(5);
  ListaNrsorte varchar2(1000);
  RETORNOCONFIRMA NUMBER;
  erro_int        varchar2(1000);
  você_dir_log      varchar2(100);
  você_id_log       varchar2(7) := 'CYBELAR';
  você_arq_log      varchar2(15) := 'NRSORTE.LOG';
  você_nome_pgm     varchar2(50) := 'PDV_NRSORTE';
  vu_file         utl_file.file_type;

  VN_QTREG NUMBER := 0;

  cursor cur_nrosorte is
    select rowid, nrosorte
      from cybelar_nrosorte
     where flguso = 0
     and rownum < P_QTD + 1 ;

  res cur_nrosorte%rowtype;
begin
  BEGIN
    SELECT PINT_NM_DIRETORIO_LOG
      INTO você_DIR_LOG 
      FROM GEMCO_PARAMETRO_INTERFACE PINT, GEMCO_SISTEMA SIST
     WHERE PINT.PINT_CD_SISTEMA = SIST.SIST_CD_SISTEMA
       AND SIST.SIST_DS_SISTEMA = você_nome_pgm;
  EXCEPTION
    -- SE não EXISTIR INFORMAR O DIRETORIO ONDE DEVERA SER
    -- GERADO O LOG DE OCORRENCIAS
    WHEN NO_DATA_FOUND THEN
      você_DIR_LOG := '/integra/Log';
    WHEN TOO_MANY_ROWS THEN
      você_DIR_LOG := '/integra/Log';
    WHEN OTHERS THEN
      você_DIR_LOG := '/integra/Log';
  END;

  begin
    vu_file := utl_file.fopen(você_dir_log, você_arq_log, 'r');
    utl_file.fclose(vu_file);
  exception
    when others then
      sp_int_gemco_gera_log('INICIO DO LOG',
                            você_dir_log,
                            você_id_log,
                            você_arq_log,
                            sysdate,
                            null,
                            você_nome_pgm,
                            0,
                            0,
                            0);
  end;

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        'INICIO DO LOG',
                        você_nome_pgm,
                        0,
                        0,
                        0);

  open cur_nrosorte;
  loop
    fetch cur_nrosorte
      into res;
    if cur_nrosorte%notfound then
      ERRO_INT := NULL;
      ERRO_INT := 'ERRO AO RESERVAR O NRSORTE PARA A LOJA ' || P_LOJA || '.';
      RETORNOCONFIRMA := 1;
      exit;
    ELSE
      UPDATE CYBELAR_NROSORTE
         SET FLGUSO = 1, LOJA = P_LOJA, PDV_CAIXA = P_PDV, DATANRSORTE = SYSDATE
       WHERE NROSORTE = RES.NROSORTE
      RETURNING NROSORTE INTO retorno_nrsorte;
    
      COMMIT;
      ListaNrsorte := retorno_nrsorte || '|' || ListaNrsorte;
      RETORNOCONFIRMA := 0;
    end if;
    VN_QTREG := VN_QTREG + 1;
  end loop;

  IF VN_QTREG = 0 THEN
    ERRO_INT := 'ERRO AO RESERVAR O NRSORTE PARA A LOJA ' || P_LOJA || '.';
    RETORNOCONFIRMA := 1;
  ELSE
    ERRO_INT := 'RESERVADO COM SUCESSO.';
    RETORNOCONFIRMA := 0;
  END IF;

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        erro_int,
                        você_nome_pgm,
                        0,
                        0,
                        0);

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        'FIM DO LOG',
                        você_nome_pgm,
                        0,
                        0,
                        0);
  sp_int_gemco_gera_log('FIM LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        null,
                        você_nome_pgm,
                        0,
                        0,
                        0);
  close cur_nrosorte;
  COMMIT;
  
  P_CONFIRMA := RETORNOCONFIRMA;
  P_NROSORTE := ListaNrsorte;
  
end cybelar_res_nrsorte;

Sáb, 06 Jun 2009 2:13 pm

Brow, Já testou o select for update??

Olha esse topico aqui...

http://glufke.net/oracle/viewtopic.php?t=690
http://www.techonthenet.com/oracle/curs ... update.php

Espero ter lhe ajudado!

facc · Seg, 08 Jun 2009 10:41 am

já tentei... dá o erro

Selecionar tudo

ORA-01002 - fetch out of sequence

Lembrando que preciso dizer qntas linhas bloquear!!! 67 lojas acessam ao mesmo tempo essa procedure

Segue a procedure com a alteração

Selecionar tudo

CREATE OR REPLACE PROCEDURE CYBELAR_RES_NRSORTE(P_LOJA     IN varchar2,
                                                P_PDV      IN VARCHAR2,
                                                P_QTD      IN NUMBER,
                                                P_NROSORTE OUT varchar2,
                                                P_CONFIRMA OUT NUMBER) is
  /***********************************************************
  * PROCEDURE : CYBELAR_RES_NRSORTE                          *
  * OBJETIVO  : RESERVAR O NRSORTE P/ A LOJA E "ESCONDER"    *
                DAS DEMAIS LOJAS E OUTRAS RESERVAS           *
  * CRIACAO   : 08/05/2009                                   *
  * VERSAO    : 1.0                                          *
  * AUTOR     : FABIO A. CAMPOS CRUZ - fabioc@cybelar.com.br *
  ***********************************************************/
  
  pragma autonomous_transaction;

  retorno_nrsorte varchar2(5);
  ListaNrsorte varchar2(1000);
  RETORNOCONFIRMA NUMBER;
  erro_int        varchar2(1000);
  você_dir_log      varchar2(100);
  você_id_log       varchar2(7) := 'CYBELAR';
  você_arq_log      varchar2(15) := 'NRSORTE.LOG';
  você_nome_pgm     varchar2(50) := 'PDV_NRSORTE';
  vu_file         utl_file.file_type;

  VN_QTREG NUMBER := 0;

  cursor cur_nrosorte is
    SELECT ROWID, NROSORTE, FLGUSO
      FROM CYBELAR_NROSORTE
     WHERE FLGUSO = 0 
     AND LOJA IS NULL
     AND ROWNUM < P_QTD + 1
     FOR UPDATE OF FLGUSO;
  
/*    select rowid, nrosorte
      from cybelar_nrosorte
     where flguso = 0
     and rownum < P_QTD + 1 ;
*/
  res cur_nrosorte%rowtype;
  
begin
  BEGIN
    SELECT PINT_NM_DIRETORIO_LOG
      INTO você_DIR_LOG 
      FROM GEMCO_PARAMETRO_INTERFACE PINT, GEMCO_SISTEMA SIST
     WHERE PINT.PINT_CD_SISTEMA = SIST.SIST_CD_SISTEMA
       AND SIST.SIST_DS_SISTEMA = você_nome_pgm;
  EXCEPTION
    -- SE não EXISTIR INFORMAR O DIRETORIO ONDE DEVERA SER
    -- GERADO O LOG DE OCORRENCIAS
    WHEN NO_DATA_FOUND THEN
      você_DIR_LOG := '/integra/Log';
    WHEN TOO_MANY_ROWS THEN
      você_DIR_LOG := '/integra/Log';
    WHEN OTHERS THEN
      você_DIR_LOG := '/integra/Log';
  END;

  begin
    vu_file := utl_file.fopen(você_dir_log, você_arq_log, 'r');
    utl_file.fclose(vu_file);
  exception
    when others then
      sp_int_gemco_gera_log('INICIO DO LOG',
                            você_dir_log,
                            você_id_log,
                            você_arq_log,
                            sysdate,
                            null,
                            você_nome_pgm,
                            0,
                            0,
                            0);
  end;

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        'INICIO DO LOG',
                        você_nome_pgm,
                        0,
                        0,
                        0);

  open cur_nrosorte;
  loop
    fetch cur_nrosorte
      into res;
    if cur_nrosorte%notfound then
      ERRO_INT := NULL;
      ERRO_INT := 'ERRO AO RESERVAR O NRSORTE PARA A LOJA ' || P_LOJA || '.';
      RETORNOCONFIRMA := 1;
      exit;
    ELSE
      
    
      UPDATE CYBELAR_NROSORTE
         SET FLGUSO = 1, LOJA = P_LOJA, PDV_CAIXA = P_PDV, DATANRSORTE = SYSDATE
       WHERE NROSORTE = RES.NROSORTE
      RETURNING NROSORTE INTO retorno_nrsorte;
    
      COMMIT;
      ListaNrsorte := retorno_nrsorte || '|' || ListaNrsorte;
      RETORNOCONFIRMA := 0;
    end if;
    VN_QTREG := VN_QTREG + 1;
  end loop;

  IF VN_QTREG = 0 THEN
    ERRO_INT := 'ERRO AO RESERVAR O NRSORTE PARA A LOJA ' || P_LOJA || '.';
    RETORNOCONFIRMA := 1;
  ELSE
    ERRO_INT := 'RESERVADO COM SUCESSO.';
    RETORNOCONFIRMA := 0;
  END IF;

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        erro_int,
                        você_nome_pgm,
                        0,
                        0,
                        0);

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        'FIM DO LOG',
                        você_nome_pgm,
                        0,
                        0,
                        0);
  sp_int_gemco_gera_log('FIM LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        null,
                        você_nome_pgm,
                        0,
                        0,
                        0);
  close cur_nrosorte;
  COMMIT;
  
  P_CONFIRMA := RETORNOCONFIRMA;
  P_NROSORTE := ListaNrsorte;
  
end cybelar_res_nrsorte;

alguém pode me dar uma luz de como resolver?

facc · Seg, 08 Jun 2009 12:37 pm

Deixa eu tentar explicar melhor.

Tenho uma tabela com 100.000 registros que contem alguns numeros para sorteio. Esses numeros são únicos (chave primaria) para as 67 lojas (ou seja, não pode se repetir para ninguém!)

quero algo assim:

1) Uma loja entre e diga que quer reservar 5 numeros (isso já está feito) e que se "esconda" para as demais lojas

mas na procedure que estou fazendo, não sei porque, mas em alguns casos está repetindo essa numeração.

alguém pode me dar uma luz para resolver esse problemão?

facc · Ter, 09 Jun 2009 11:28 am

Fiz algumas alterações, mas ainda está repetindo os números

Procedure

Selecionar tudo

CREATE OR REPLACE PROCEDURE CYBELAR_RES_NRSORTE(P_LOJA     IN varchar2,
                                                P_PDV      IN VARCHAR2,
                                                P_QTD      IN NUMBER,
                                                P_NROSORTE OUT varchar2,
                                                P_CONFIRMA OUT NUMBER) is
  /***********************************************************
  * PROCEDURE : CYBELAR_RES_NRSORTE                          *
  * OBJETIVO  : RESERVAR O NRSORTE P/ A LOJA E "ESCONDER"    *
                DAS DEMAIS LOJAS E OUTRAS RESERVAS           *
  * CRIACAO   : 08/05/2009                                   *
  * VERSAO    : 1.0                                          *
  * AUTOR     : FABIO A. CAMPOS CRUZ - fabioc@cybelar.com.br *
  ***********************************************************/

  pragma autonomous_transaction;

  retorno_nrsorte varchar2(5);
  ListaNrsorte    varchar2(1000);
  RETORNOCONFIRMA NUMBER;
  erro_int        varchar2(1000);
  você_dir_log      varchar2(100);
  você_id_log       varchar2(7) := 'CYBELAR';
  você_arq_log      varchar2(60) := 'NRSORTE_' || to_char(sysdate, 'DDMMYY') ||
                                  '.LOG';
  você_nome_pgm     varchar2(50) := 'PDV_NRSORTE';
  vu_file         utl_file.file_type;

  VN_QTREG NUMBER := 0;

  cursor cur_nrosorte is
  --cursor pega numeros "livres"
    select nrosorte
      from cybelar_nrosorte
     where flguso = 0
       AND (LOJA IS NULL OR LOJA = '')
       and rownum <= P_QTD;

  res cur_nrosorte%rowtype;

begin
  BEGIN
    SELECT PINT_NM_DIRETORIO_LOG
      INTO você_DIR_LOG
      FROM GEMCO_PARAMETRO_INTERFACE PINT, GEMCO_SISTEMA SIST
     WHERE PINT.PINT_CD_SISTEMA = SIST.SIST_CD_SISTEMA
       AND SIST.SIST_DS_SISTEMA = você_nome_pgm;
  EXCEPTION
    -- SE não EXISTIR INFORMAR O DIRETORIO ONDE DEVERA SER
    -- GERADO O LOG DE OCORRENCIAS
    WHEN NO_DATA_FOUND THEN
      você_DIR_LOG := '/integra/Log';
    WHEN TOO_MANY_ROWS THEN
      você_DIR_LOG := '/integra/Log';
    WHEN OTHERS THEN
      você_DIR_LOG := '/integra/Log';
  END;

  begin
    vu_file := utl_file.fopen(você_dir_log, você_arq_log, 'r');
    utl_file.fclose(vu_file);
  exception
    when others then
      sp_int_gemco_gera_log('INICIO DO LOG',
                            você_dir_log,
                            você_id_log,
                            você_arq_log,
                            sysdate,
                            null,
                            você_nome_pgm,
                            0,
                            0,
                            0);
  end;

  FOR rNrSorte IN cur_nrosorte loop  
    if CYBELAR_LCK_REGISTRO(rNrSorte.nrosorte) > 0 then
    
      UPDATE CYBELAR_NROSORTE
         SET FLGUSO      = 1,
             LOJA        = P_LOJA,
             PDV_CAIXA   = P_PDV,
             DATANRSORTE = SYSDATE
       WHERE NROSORTE = rNrSorte.nrosorte
      RETURNING NROSORTE INTO retorno_nrsorte;
      COMMIT;
    
      ListaNrsorte    := retorno_nrsorte || '|' || ListaNrsorte;
      RETORNOCONFIRMA := 0;
    end if;
    VN_QTREG := VN_QTREG + 1;
  end loop;
  --close cur_nrosorte;

  IF VN_QTREG = 0 THEN
    ERRO_INT        := 'ERRO AO RESERVAR O NRSORTE (' || P_NROSORTE ||
                       ') PARA A LOJA ' || P_LOJA || '.';
    RETORNOCONFIRMA := 1;
  ELSE
    ERRO_INT        := 'RESERVADO COM SUCESSO (' || ListaNrsorte ||
                       '). LOJA ' || P_LOJA;
    RETORNOCONFIRMA := 0;
  END IF;

  sp_int_gemco_gera_log('GERA LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        erro_int,
                        você_nome_pgm,
                        0,
                        0,
                        0);

  sp_int_gemco_gera_log('FIM LOG',
                        você_dir_log,
                        você_id_log,
                        você_arq_log,
                        sysdate,
                        null,
                        você_nome_pgm,
                        0,
                        0,
                        0);
  COMMIT;

  P_CONFIRMA := RETORNOCONFIRMA;
  P_NROSORTE := ListaNrsorte;

end cybelar_res_nrsorte;

Função

Selecionar tudo

create or replace function CYBELAR_LCK_REGISTRO(P_QTD IN INTEGER) return PLS_INTEGER is
  iResult PLS_INTEGER := 0;
begin
  SELECT NROSORTE INTO iResult FROM CYBELAR_NROSORTE CYB WHERE CYB.NROSORTE = P_QTD FOR UPDATE NOWAIT;
  return(iResult);
EXCEPTION
  WHEN OTHERS THEN 
    RETURN(iResult);
end CYBELAR_LCK_REGISTRO;

alguém pode me auxiliar?

fsitja · Qua, 10 Jun 2009 1:36 pm

Beleza cara,
acho que você confundiu o esquema com o for update, mas é tranquilo.
A idéia é que você dê lock nos registros cuja chaves estão sendo selecionadas. O problema é que fazendo "sorteio", pelo menos da forma que eu considero mais adequada, não é possível fazer um lock exclusivo para as linhas.
Logo, eu optaria fazer um lock na tabela toda, o que vai significar que apenas uma loja vai rodar o programa num dado instante e a outra só consegue depois que acabar a anterior. É possível ler, mas não é possível obter outro lock para atualização em nenhuma linha da tabela, que é o que sua transação precisa garantir para que não haja duplicidade.
Dá uma olhada, fiz um "rascunho" de código pra você, se tiver alguma dúvida manda ver.

Selecionar tudo

declare
  cursor cur_sorteio(p_qtd in number) is
    select nrosorte
      from (select nrosorte
              from vw_nrosorte
             order by dbms_random.value)
     where rownum <= p_qtd;
  type t_sorteio is table of cur_sorteio%rowtype;
  v_sorteio t_sorteio;
begin
  lock table vw_nrosorte in exclusive mode nowait;
  open cur_sorteio(5);
  fetch cur_sorteio bulk collect
    into v_sorteio;
  for i in 1 .. v_sorteio.count
  loop
    update vw_nrosorte n
       set n.flguso = 1
     where n.nrosorte = v_sorteio(i).nrosorte;
  end loop;
  close cur_sorteio;
  -- commit;
end;

Só pra lembrar, o que solta o lock é um commit ou rollback, e o ideal é que eles fiquem fora da procedure que processa o negócio, na rotina chamadora, junto com os tratamentos de exceptions. Você vai precisar tratar o ORA-00054 (google nele), que vai disparar caso a tabela já esteja ocupada por outra pessoa rodando o mesmo procedimento.

Falou,
Francisco.

fsitja · Qua, 10 Jun 2009 1:38 pm

A estrutura que criei para testar o procediment foi a seguinte:

Selecionar tudo

create or replace view vw_nrosorte as
select nrosorte, loja, flguso, datanrosorte
from cybelar_nrosorte
where flguso = 0

create table CYBELAR_NROSORTE as
select level nrosorte, to_char(null, '0000000000') loja, 0 flguso, to_date(null) datanrosorte
from dual
connect by level <= 10;

facc · Seg, 15 Jun 2009 8:20 am

Consegui resolver da seguinte maneira.

Selecionar tudo

FOR rNrSorte IN 1 .. P_QTD loop
    BEGIN
      LOCK TABLE CYBELAR_NROSORTE IN EXCLUSIVE MODE;
    
      SELECT NRO.NROSORTE
        INTO retorno_nrsorte
        FROM CYBELAR_NROSORTE NRO
       WHERE LOJA IS NULL
         AND FLGUSO = 0
         AND ROWNUM <= 1
         FOR UPDATE NOWAIT;
    
      ListaNrsorte := retorno_nrsorte || '|' || ListaNrsorte;
    
      UPDATE CYBELAR_NROSORTE
         SET FLGUSO      = 1,
             LOJA        = P_LOJA,
             PDV_CAIXA   = P_PDV,
             DATANRSORTE = SYSDATE
       WHERE NROSORTE = retorno_nrsorte;
      COMMIT;
    EXCEPTION
      WHEN look_error THEN
        NULL;
    END;

Dizer a quantidade de linhas a serem atualizadas

Quem está online