Oracle

Script que gera um “esqueleto” para uma Procedure

Postado em Atualizado em

VAR VTAB VARCHAR2(100);
EXECUTE :VTAB := ‘NOME_TABELA’
SET SERVEROUTPUT ON;
SET SERVEROUTPUT ON SIZE 99999;

BEGIN

DECLARE CURSOR C IS
SELECT * from col where tname = :VTAB order by colno;

CURSOR CPK IS
SELECT * from col where COLNO = 1 AND tname = :VTAB ;

CURSOR CSPK IS
SELECT * from col where COLNO 1 AND tname = :VTAB ;

BEGIN
DBMS_OUTPUT.PUT_LINE(‘CREATE OR REPLACE PROCEDURE _GRAVA_’||:VTAB||’ (‘);
FOR R IN CPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ P_’||RPAD(R.CNAME,33,’ ‘)||’IN OUT ‘||R.TNAME||’.’||R.CNAME||’%TYPE,’);
END LOOP;
FOR R IN CSPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ P_’||RPAD(R.CNAME,40,’ ‘)||R.TNAME||’.’||R.CNAME||’%TYPE,’);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘ P_’||RPAD(‘USERNAME’,40,’ ‘)||’VARCHAR2’);
DBMS_OUTPUT.PUT_LINE(‘) –PRIMARYKEY = IN OUT’);
DBMS_OUTPUT.PUT_LINE(‘IS’);
DBMS_OUTPUT.PUT_LINE(‘ V_PEF_COD_REG ROGERIO.PEDCIR.PEF_COD_REG%TYPE;’);
DBMS_OUTPUT.PUT_LINE(‘BEGIN’);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘ SELECT PEF_COD INTO V_PEF_COD_REG FROM ROGERIO.USUARIOS_WEB WHERE USERNAME LIKE ”USU_”||P_USERNAME;’);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
FOR R IN CPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ IF NVL(P_’||R.CNAME||’,0) = 0 THEN’);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘ ‘);
FOR R IN CPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ SELECT NVL(‘||R.CNAME||’,0)+1 INTO P_’||R.CNAME||’ FROM ‘||R.TNAME||’;’);
END LOOP;
FOR R IN CPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ OR SELECT SEQ_’||R.TNAME||’.NEXTVAL INTO P_’||R.CNAME||’ FROM DUAL;’);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘ INSERT INTO ‘||:VTAB);
DBMS_OUTPUT.PUT_LINE(‘ (‘);
FOR R IN C LOOP
DBMS_OUTPUT.PUT_LINE(‘ ‘||R.CNAME||’, ‘);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘ )’);
DBMS_OUTPUT.PUT_LINE(‘ VALUES’);
DBMS_OUTPUT.PUT_LINE(‘ (‘);
FOR R IN C LOOP
DBMS_OUTPUT.PUT_LINE(‘ P_’||R.CNAME||’, ‘);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘ );’);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘ ELSE’);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘ UPDATE ‘||:VTAB);
DBMS_OUTPUT.PUT_LINE(‘ SET ‘);
FOR R IN CSPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ ‘||RPAD(R.CNAME,20,’ ‘) || ‘ = P_’ || R.CNAME || ‘,’);
END LOOP;
FOR R IN CPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ WHERE ‘||RPAD(R.CNAME,18,’ ‘)||’ = P_’||R.CNAME||’;’);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘  ‘);
FOR R IN CPK LOOP
DBMS_OUTPUT.PUT_LINE(‘ DELETE FROM ‘||:VTAB||’_ITENS WHERE ‘||RPAD(R.CNAME,20,’ ‘)||’ = P_’||R.CNAME||’;’);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘ END IF;’);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘ COMMIT; ‘);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘ ‘);
DBMS_OUTPUT.PUT_LINE(‘END _GRAVA_’||:VTAB||’;’);
DBMS_OUTPUT.PUT_LINE(‘/’);
DBMS_OUTPUT.PUT_LINE(‘SHOW ERRORS;’);
DBMS_OUTPUT.PUT_LINE(‘/’);
END;
END;

Anúncios

Minhas referências para utilização do ORACLE

Postado em Atualizado em

Tabelas

  1. ALL_CONSTRAINTS: Utilizo-a para encontrar as FK e PK de uma table;
  2. COL: Grande utilidade para encontrar tabelas, utilizando comandos como TNAME like ‘%ABC%’  para procurar pelo nome de uma tabela e CNAME like ‘%ABC%’ para procurar pelos campos da tabela. Exemplo: SELECT UNIQUE TNAME FROM COL WHERE CNAME LIKE ‘%COD%’;
  3. ALL_SOURCE: Utiliza-a bastante para recuperar o texto de uma PROCEDURE/FUNCTION. Exemplo: SELECT UNIQUE TEXT FROM ALL_SOURCE WHERE NAME = ‘NOME_FUNCAO’;

Comandos que utilizo com frequencia em PROCEDURES/FUNCTION:

  1. Comando utilizado para abortar e/ou para paralizar uma procedure/function. Examplo: RAISE_APPLICATION_ERROR(-20010,’MSG DE ERRO’);
  2. Comando utilizado para abortar e/ou para paralizar uma procedure/function e concatenar com uma variavel. Examplo : RAISE_APPLICATION_ERROR(-20010,’ exibe paramêtro = ‘||TO_CHAR(CODIGO));
  3. Comando para verificar se alguma linha foi alterada após alguma operação: Exemplo Após um UPDATE verifico se alguma linha foi alterada e caso negativo faço o INSERT. Linha de comando: (UPDATE;) … IF SQL%ROWCOUNT = 0  THEN …(INSERT;) ;
  4. Verificar se um SELECT retornou alguma linha: Exemplo:  … BEGIN (SELECT) EXCEPTION WHEN NO_DATA_FOUND THEN (REGRA) END;