criar procedure

Dúvidas, dicas e truques de PL/SQL. Aqui também vão assuntos relacionados a pacotes, triggers, funções, Java-Stored Procedures, etc
Responder
fjralberto
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qui, 16 Set 2010 10:38 am
Localização: Santo André - SP

Pessoal, bom dia.

Alguém poderia me ajudar a criar uma procedure onde esta deve receberá como parâmetro valores de campos de uma tabela.

Ex:
E estes valores são CHAR.

Variaveis

Selecionar tudo

PCodigo      NUMBER
PEndereco  CHAR
PNumero     CHAR
Só que em especial, a variável PNumero embora ela aceite CHAR eu preciso com que obrigatóriamente passe a fazer uma validação antes de gravar na tabela, pois não pode ir para a tabela valores que não sejam números.
:?:
Alguém pode me dar uma mão?

Obrigado.
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

no que especificamente você esta com dificuldades??

Selecionar tudo

CREATE OR REPLACE PROCEDURE sua_proc
(p_codigo IN NUMBER ,
 p_endereco IN CHAR,
 p_numero IN CHAR)
IS
BEGIN
...
END sua_proc;
/
Só que em especial, a variável PNumero embora ela aceite CHAR eu preciso com que obrigatóriamente passe a fazer uma validação antes de gravar na tabela, pois não pode ir para a tabela valores que não sejam números.
essa parte ai eu não entendi.. pra que então o tipo dele e char r não number??
fjralberto
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qui, 16 Set 2010 10:38 am
Localização: Santo André - SP

A dificuldade está em fazer com que a PNumero ao receber um CHAR ela antes de ser gravada na tabela tem que ser um NUMBER
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

da um to_number dentro de um bloco begin end.. dai e so capturar a exceção lançada

Selecionar tudo

ORA-01722  - INVALID_NUMBER  	

It isn't a number even though you are treating it like one to trying to turn it into one
fjralberto
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qui, 16 Set 2010 10:38 am
Localização: Santo André - SP

Então... aí ficaria assim

Selecionar tudo

CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR
(
    PTGPR_SQPROCESSO                    IN OUT NUMBER,
    PTGPR_NUREGRECUPERADOS     IN     NUMBER,
    PTGPR_NUREGPROCESSADOS     IN     NUMBER,
    PTGPR_NMARQUIVO                      IN     CHAR,
    PTGPR_PATHARQUIVO                 IN     CHAR,
    PTGPR_DSHEADERARQUIVO        IN     CHAR,      -> Eu preciso que essa variavel aqui quando trazer uma informação seja validado como número...
    PTGPR_SISTEMA                             IN     CHAR
)
AS

  vBuscaInt  NUMBER; -> pra i sso, eu criei essa variável que é do tipo numerico

  BEGIN

    vBuscaInt := NVL(vDSHEADERHARQUIVO,0);  --> caso ele receba um valor que não seja numerico... ele vai cair na excecao lá em baixo... ÍSSO QUE EU FIZ ESTÁ CERTO?
    
    SELECT GERAL.INTEGRA_SQ_GERATITULO.NEXTVAL
     INTO PTGPR_SQPROCESSO
     FROM DUAL;

    INSERT INTO GERAL.TI_GERATITULOS_TGPR(
    TGPR_SQPROCESSO        ,
    TGPR_NUREGRECUPERADOS  ,
    TGPR_NUREGPROCESSADOS  ,
    TGPR_NMARQUIVO         ,
    TGPR_PATHARQUIVO       ,
    TGPR_DSHEADERARQUIVO   ,
    TGPR_DTGERACAO         ,
    TGPR_USUARIOGERACAO    ,
    TGPR_SISTEMA
)
      VALUES(
        PTGPR_SQPROCESSO        ,
        PTGPR_NUREGRECUPERADOS  ,
        PTGPR_NUREGPROCESSADOS  ,
        PTGPR_NMARQUIVO         ,
        PTGPR_PATHARQUIVO       ,
        PTGPR_DSHEADERARQUIVO    ,
        SYSDATE,
        USER,
        PTGPR_SISTEMA);
   
    EXCEPTION     
        WHEN OTHERS THEN   
  
  END;
/
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

Selecionar tudo

CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR
(
    PTGPR_SQPROCESSO           IN OUT NUMBER,
    PTGPR_NUREGRECUPERADOS     IN     NUMBER,
    PTGPR_NUREGPROCESSADOS     IN     NUMBER,
    PTGPR_NMARQUIVO            IN     CHAR,
    PTGPR_PATHARQUIVO          IN     CHAR,
    PTGPR_DSHEADERARQUIVO      IN     CHAR,
    PTGPR_SISTEMA              IN     CHAR
)
AS

  vBuscaInt  NUMBER;

  BEGIN
    BEGIN          
        select to_number(PTGPR_DSHEADERARQUIVO) into vBuscaInt from dual;   
   EXCEPTION     
        WHEN INVALID_NUMBER THEN 
        PTGPR_DSHEADERARQUIVO := 0;
    END;
   
    SELECT GERAL.INTEGRA_SQ_GERATITULO.NEXTVAL
     INTO PTGPR_SQPROCESSO
     FROM DUAL;

    INSERT INTO GERAL.TI_GERATITULOS_TGPR(
    TGPR_SQPROCESSO        ,
    TGPR_NUREGRECUPERADOS  ,
    TGPR_NUREGPROCESSADOS  ,
    TGPR_NMARQUIVO         ,
    TGPR_PATHARQUIVO       ,
    TGPR_DSHEADERARQUIVO   ,
    TGPR_DTGERACAO         ,
    TGPR_USUARIOGERACAO    ,
    TGPR_SISTEMA
)
      VALUES(
        PTGPR_SQPROCESSO        ,
        PTGPR_NUREGRECUPERADOS  ,
        PTGPR_NUREGPROCESSADOS  ,
        PTGPR_NMARQUIVO         ,
        PTGPR_PATHARQUIVO       ,
        PTGPR_DSHEADERARQUIVO    ,
        SYSDATE,
        USER,
        PTGPR_SISTEMA);
   
    EXCEPTION     
        WHEN OTHERS THEN   
 
  END;
/ 
fjralberto
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qui, 16 Set 2010 10:38 am
Localização: Santo André - SP

Certo... Mas nesse caso você está mandando o campo como ZERO... Ficou show... mas em fez do valor ir como ZERO eu quero que o insert não seja feito... trazendo algum erro.

É possivel?
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

tu não quer fazer o insert caso venha algo diferente de numeros.. pensei que você queria tratar isso como havia dito la em cima..

e so tirar esse begin exception end ai po.. deixa o select into q dai vai direto para o teu exception de baixo..


uma dica... others e meio fo*a porque tu nunca vai saber o que ta acontencendo.. ou então printa o erro code ai.. faz alguma coisa no teu others pois ele tem o uso "desaconselhavel "
fjralberto
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qui, 16 Set 2010 10:38 am
Localização: Santo André - SP

Ok...

Então ficaria assim!!! Estou entendendo que ele só vai gravar se for número se não for ele não vai gravar.

Selecionar tudo

CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR 
( 
    PTGPR_SQPROCESSO           IN OUT NUMBER, 
    PTGPR_NUREGRECUPERADOS     IN     NUMBER, 
    PTGPR_NUREGPROCESSADOS     IN     NUMBER, 
    PTGPR_NMARQUIVO            IN     CHAR, 
    PTGPR_PATHARQUIVO          IN     CHAR, 
    PTGPR_DSHEADERARQUIVO      IN     CHAR, 
    PTGPR_SISTEMA              IN     CHAR 
) 
AS 

  vBuscaInt  NUMBER; 

  BEGIN 
      
    SELECT TO_NUMBER(PTGPR_DSHEADERARQUIVO)
     INTO vBuscaInt
     FROM DUAL;    
    
    SELECT GERAL.INTEGRA_SQ_GERATITULO.NEXTVAL 
     INTO PTGPR_SQPROCESSO 
     FROM DUAL; 

    INSERT INTO GERAL.TI_GERATITULOS_TGPR( 
    TGPR_SQPROCESSO        , 
    TGPR_NUREGRECUPERADOS  , 
    TGPR_NUREGPROCESSADOS  , 
    TGPR_NMARQUIVO         , 
    TGPR_PATHARQUIVO       , 
    TGPR_DSHEADERARQUIVO   , 
    TGPR_DTGERACAO         , 
    TGPR_USUARIOGERACAO    , 
    TGPR_SISTEMA 
) 
      VALUES( 
        PTGPR_SQPROCESSO        , 
        PTGPR_NUREGRECUPERADOS  , 
        PTGPR_NUREGPROCESSADOS  , 
        PTGPR_NMARQUIVO         , 
        PTGPR_PATHARQUIVO       , 
        PTGPR_DSHEADERARQUIVO    , 
        SYSDATE, 
        USER, 
        PTGPR_SISTEMA); 
    
    EXCEPTION      
        WHEN OTHERS THEN    
  
  END; 
/ 
Referente ao OTHERS, o que você recomenda para ele trazer um erro de tipo para o usuário? ou log?
victorhugomuniz
Moderador
Moderador
Mensagens: 1396
Registrado em: Sex, 01 Fev 2008 2:06 pm
Localização: Rio de Janeiro - RJ
Contato:
:D

eu entendo da mesma forma que você...

o insert não sera executado caso gere alguma exception antes..

Selecionar tudo


    EXCEPTION
       WHEN OTHERS THEN
          err_code := SQLCODE;
          err_msg := substr(SQLERRM, 1, 200);

           DBMS_OUTPUT.PUT_LINE ('Exception, status=' || err_code || ' - mensagem: '|| err_msg);

    END;

Avatar do usuário
fsitja
Rank: OraSauro
Rank: OraSauro
Mensagens: 611
Registrado em: Seg, 19 Jan 2009 4:29 pm
Localização: Gaúcho no Rio de Janeiro - RJ
"The scars exist to remind us that the past was real"
Campanha: Como fazer uma pergunta e obter uma resposta.
http://tkyte.blogspot.com/2005/06/how-t ... tions.html

OCA & OCP Developer — OCE SQL Expert — OCS Data Warehousing Specialist

Acho que é mais fácil você simplesmente declarar o parâmetro como NUMBER... se o programa que chama a procedure enviar um caractere que não seja um número, ele vai dar erro na conversão implícita.

Selecionar tudo

CREATE OR REPLACE PROCEDURE GERAL.INSTI_GERATITULOS_TGPR
(
    PTGPR_SQPROCESSO                    IN OUT NUMBER,
    PTGPR_NUREGRECUPERADOS     IN     NUMBER,
    PTGPR_NUREGPROCESSADOS     IN     NUMBER,
    PTGPR_NMARQUIVO                      IN     CHAR,
    PTGPR_PATHARQUIVO                 IN     CHAR,
    PTGPR_DSHEADERARQUIVO        IN     NUMBER,      -> Eu preciso que essa variavel aqui quando trazer uma informação seja validado como número...
    PTGPR_SISTEMA                             IN     CHAR
)
AS 
Mas, mesmo assim... o melhor mesmo é mudar o tipo da coluna da tabela para number.

Se não tiver jeito mesmo... coloque um check constraint na coluna como abaixo:

Selecionar tudo

SQL> -- cria tabela de exemplo
SQL> create table ti_geratitulos_tgpr (tgpr_dsheaderarquivo varchar2(30));
 
Table created
SQL> -- criação da função de verificação
SQL> CREATE OR REPLACE FUNCTION testa_numero(p_char IN VARCHAR2) RETURN NUMBER IS
  2    v_temp NUMBER;
  3  BEGIN
  4    v_temp := to_number(p_char);
  5    RETURN 1;
  6  EXCEPTION
  7    WHEN invalid_number THEN
  8      RETURN 0;
  9  END;
 10  /
 
Function created
SQL> -- criação da check constraint
SQL> alter table ti_geratitulos_tgpr add constraint ck_header_is_number check (to_number(tgpr_dsheaderarquivo) = to_number(tgpr_dsheaderarquivo));
 
Table altered
SQL> -- testa inserção na tabela
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('123');
 
1 row inserted
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('123.45');
 
1 row inserted
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('1,5');
 
insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('1,5')
 
ORA-01722: invalid number
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('.2');
 
1 row inserted
SQL> insert into ti_geratitulos_tgpr (tgpr_dsheaderarquivo) values ('0.4');
 
1 row inserted
SQL> select tgpr_dsheaderarquivo from ti_geratitulos_tgpr;
 
TGPR_DSHEADERARQUIVO
------------------------------
123
123.45
.2
0.4
Novamente: o correto é mudar a coluna para number. É simples e é a coisa certa de se fazer.
fjralberto
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Mensagens: 6
Registrado em: Qui, 16 Set 2010 10:38 am
Localização: Santo André - SP

Hummm... Entendi... Vou testar e te falo...

Referente a alteração do tipo da coluna... Foi a primeira coisa que eu quis fazer... Mas eu não posso porque a tabela faz parte de um padrão!

Te retorno.

Abs
Responder
  • Informação