UPDATE COM PROCEDURE

[agregorio]

beleza pessoal?

Atualmente estou estudando procedures e funções, ver se vocês podem dar uma ajuda.
Estou querendo fazer uma procedure de UPDATE com condição, porem não estou conseguindo.

Quero atualizar a tabela FUNCIONARIO. Na hora de atualizar, se NÃO existir o codigo do funcionário ele tem que ir direto pro INSERT. To me perdendo na condição IF.

segue o codigo:

Selecionar tudo

CREATE OR REPLACE PROCEDURE UPDT_FUNCIONARIO
   (V_COD_FUNCIONARIO  IN  FUNCIONARIO.COD_FUNCIONARIO%TYPE,
    V_CPF              IN  FUNCIONARIO.NOME_FUNCIONARIO%TYPE,
    V_SALARIO          IN  FUNCIONARIO.SALARIO%TYPE,
    V_DEPARTAMENTO     IN  FUNCIONARIO.DEPARTAMENTO%TYPE)

    IS
    BEGIN
      IF (SELECT * FROM FUNCIONARIO WHERE NO EXISTS

        (SELECT COD_FUNCIONARIO FROM FUNCIONARIO
         WHERE (COD_FUNCIONARIO = V_COD_FUNCIONARIO)))THEN

       UPDATE FUNCIONARIO
       SET COD_FUNCIONARIO = V_COD_FUNCIONARIO,
           CPF             = V_CPF            ,
           SALARIO         = V_SALARIO        ,
           DEPARTAMENTO    = V_DEPARTAMENTO
        WHERE COD_FUNCIONARIO = V_COD_FUNCIONARIO;
        ELSE
          INSERT INTO FUNCIONARIO VALUES
          (V_COD_FUNCIONARIO,V_CPF,V_SALARIO,V_DEPARTAMENTO);
        END IF;

        END UPDT_FUNCIONARIO;

[stcoutinho]

agregorio,

Você poderia usar SQL%ROWCOUNT para resolver seu problema.

Esta variável mostra para o usuário quantas linhas foram "afetadas"/"acessadas" por um comando DELETE, SELECT, UPDATE, INSERT.

Você poderia fazer uma lógica como esta:

Selecionar tudo

CREATE OR REPLACE PROCEDURE nome_procedure
IS
  declaracoes;
BEGIN
   ...
   --- UPDATE de tabela
   UPDATE tabela 
         SET coluna=valor
    WHERE condicao;
 
    --- Se UPDATE não atualizou nenhuma linha então faca INSERT
    IF SQL%ROWCOUNT = 0 THEN 
        INSERT
           INTO tabela
                   (colunas)
        VALUES 
                   (valores);
    END IF;
    ...
END;

Esta sugestão não é original neste forum, Você pode também consultar a URL abaixo:
http://glufke.net/oracle/viewtopic.php?f=3&t=1329

Pode ser que os foristas tenham outras sugestões para seu problema.

Abraços e boa sorte,

Sergio Coutinho

[agregorio]

Sergio
Boa noite

Vlw cara deu certo usando o SQL%ROWCOUNT.

obrigado

Antonio Gregorio

[agregorio]

Sergio, só uma duvida:

Por que SQL%ROWCOUNT = 0 ?

vlw

Antonio Gregorio

[stcoutinho]

Olá agregorio,

Que bom que conseguiu resolver o seu problema.

Vamos lá: por que SQL%ROWCOUNT = 0 ?

Bem, toda a vez que você faz na base um comando de DELETE, INSERT, UPDATE (e acho que SELECT), a variável SQL%ROWCOUNT é atualizada com o número de registros afetados pelo comando de INSERT, DELETE, etc que foi executado imediatamente antes.

No caso da sua procedure, note que você está verificando o valor de SQL%ROWCOUNT imediatamente depois da execução do comando UPDATE. Não existe nenhum comando PL/SQL entre o UPDATE e o IF SQL%ROWCOUNT = 0 THEN ....

Se o seu comando UPDATE conseguiu atualiza um ou mais registros existentes na tabela, OBRIGATORIAMENTE este SQL%ROWCOUNT apresentará valor MAIOR do que ZERO.

Se nenhum registro for atualizado por seu comando UPDATE, então o SQL%ROWCOUNT apresentará valor ZERO.

Um outro PONTO: desconfio que toda esta lógica de ROWCOUNT poderia ser substituída pelo comando MERGE, que "automatiza" todas estas tarefas (a lógica que discutimos aquí sobre o UPDATE ... senão INSERT).

Performaticamente falando, creio que este comando seria melhor de se adotar do que o ROWCOUNT. Se tiver tempo, valeria a pena você pesquisar sobre este comando e ver se pode implementar ele no seu caso.

Pode ser que algum forista aquí possa dar uma opinião sobre o uso do comando MERGE.

Espero ter conseguido sanar sua dúvida. Se ainda precisar de esclarecimentos, poste aquí, ok?

Abraços,

Sergio Coutinho

[agregorio]

Sergio
Bom dia Passeiro. Agora entendi porque ROWCOUNT = 0.
Obrigado pela paciencia em explicar. Vou pesquisar sobre o MERGE e tentar implementar o meu bloco PL/SQL com ele. Valeu mesmo cara. Ah, achei tambem outra forma de resolver tambem, essa é simples:

Selecionar tudo

CREATE OR REPLACE PROCEDURE UPDT_FUNCIONARIO
   (V_COD_FUNCIONARIO   IN  FUNCIONARIO.COD_FUNCIONARIO%TYPE,
    V_CPF               IN  FUNCIONARIO.CPF%TYPE,
    V_NOME              IN  FUNCIONARIO.NOME_FUNCIONARIO%TYPE,
    V_SALARIO           IN  FUNCIONARIO.SALARIO%TYPE,
    V_DEPARTAMENTO      IN  FUNCIONARIO.DEPARTAMENTO%TYPE) IS
    VAR_COD_FUNCIONARIO     FUNCIONARIO.COD_FUNCIONARIO%TYPE;
BEGIN
   BEGIN
      SELECT COD_FUNCIONARIO
         INTO VAR_COD_FUNCIONARIO
       FROM  FUNCIONARIO
       WHERE COD_FUNCIONARIO = V_COD_FUNCIONARIO;

     
      UPDATE FUNCIONARIO
       SET COD_FUNCIONARIO    = V_COD_FUNCIONARIO,
           CPF                = V_CPF            ,
           NOME_FUNCIONARIO   = V_NOME           ,
           SALARIO            = V_SALARIO        ,
           DEPARTAMENTO       = V_DEPARTAMENTO
        WHERE COD_FUNCIONARIO = V_COD_FUNCIONARIO;

          
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
       
          INSERT INTO FUNCIONARIO VALUES
          (V_COD_FUNCIONARIO,V_CPF,V_NOME,V_SALARIO,V_DEPARTAMENTO);
   END;
END;

Obrigado

Antonio Gregorio

[diegolenhardt]

outra forma é

Selecionar tudo

begin;
  insert into x ...
exception
  when dup_val_on_index then
    update...
end;

para seu conhecimento...

[agregorio]

diegolenhardt

Obrigado por essa outra forma de resolver meu problema.

Obrigado

Antonio Gregorio