Script que gera um “esqueleto” para uma Procedure

Posted on 2009/06/03 Updated on 2009/06/03

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;