paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

Struttura di una procedura Pl/sql

Iniziamo con i settaggi dell'output

set doc off
set echo off
set term off
set ver  off
set feed off
set head off
def v_contleng=12
def v_flagdebu=S
def v_spoofile=&1\FILE_DI_LOG.log

clear columns
col data noprint new_value v_data

select to_char (sysdate, 'DD/MM/YYYY" ore "hh24:mi:ss') data
from dual;

spool &&v_spoofile

Mettiamo un prompt, sempre importante per le successive modifiche o visualizzazioni del codice


set term on
PROMPT*****************************************
PROMPT Descrizione dettagliata della procedura
PROMPT*****************************************
PROMPT
PROMPT .Elaborazione del &&v_data
PROMPT

set term on
set serveroutput on size 1000000

Dichiaro il cursose sulla tabella ANAGAZIE

declare

 cursor CURS_ANAGAZIE is
  select
	upper(ltrim (rtrim (CODI_AZIE)))  codiazie,
	upper(ltrim (rtrim (DESC_AZIE)))  descazie,
	upper(ltrim (rtrim (NUME_DIPE)))  numedipe
  from ANAGAZIE ;

Dichiarazione Variabili interne al modulo



 v_auxx                varchar2 (255);
 v_debu                varchar2 (800)  := null;
 v_de01                varchar2 (320)  := null;
 v_dataaggi            date            := sysdate;
 v_flaginse            varchar2(1)     := null;
 vc_user_prop        varchar2(10)     :='&2';


Dichiarazione Contatori che inserisco alla fine come output di resoconto



v_contreadreco 	      	 number (&&v_contleng) := 0;
v_contscarreco		     number (&&v_contleng) := 0;
v_continsereco   		 number (&&v_contleng) := 0;
v_continserecoscar		 number (&&v_contleng) := 0;
v_contcomminte           number (&&v_contleng) := 0;
v_contcodiazienull		 number (&&v_contleng) := 0;
v_contdatafattnull		 number (&&v_contleng) := 0;
v_contcodiazieerra		 number (&&v_contleng) := 0;
v_contdatinull		 	 number (&&v_contleng) := 0;
v_contcodifatterra		 number (&&v_contleng) := 0;
v_contcodiservnull		 number (&&v_contleng) := 0;
v_contcodiserverra		 number (&&v_contleng) := 0;
v_continserecoscarserv	 number (&&v_contleng) := 0;
v_continserecoserv		 number (&&v_contleng) := 0;


Dichiarazione variabili da utilizzare come ritorno delle istruzioni di Select


VN_CODIAZIE       ANAGAZIE.CODI_AZIE%TYPE          := null;
VN_DESCAZIE       ANAGAZIE.DESC_AZIE%TYPE          := null;
VN_NUMEDIPE       ANAGAZIE.NUME_DIPE%TYPE          :=NULL;

Inizio Definizione Funzione per display Debug


procedure DispDebu (p_debu IN     varchar2) is
   v_ch10po01 number (3)      := null;
   v_ch10po02 number (3)      := null;
  Begin

   if rtrim (p_debu) is null
   then
     return;
   end if;

   dbms_output.put_line ('');
   dbms_output.put_line ('..Debug: ');

   v_ch10po01 := 1;

   loop
     v_ch10po02 := nvl (instr (p_debu, chr(10), v_ch10po01),0);

     if v_ch10po02 = 0
     then
       dbms_output.put_line ('...'||substr (v_debu, v_ch10po01) );
       exit;
     else
       dbms_output.put_line ('...'||substr (v_debu, v_ch10po01, v_ch10po02 - v_ch10po01) );
     end if;

    v_ch10po01 := v_ch10po02+1;
   end loop;
  end;

Fine Definizione Funzione per display Debug e inizio del main del codice


BEGIN
    v_debu  := 'Lock tabella'||chr(10);
	lock table ANAGAZIE in exclusive mode nowait;

Apertura e ciclo di lettura sul cursore


	FOR VR_ANAGAZIE in CURS_ANAGAZIE loop
    	v_debu  := 'Lettura record n.'|| CURS_ANAGAZIEE%rowcount||' - Codice AZIENDA='||VR_ANAGAZIE.codiazie;
      	v_de01  := v_debu||chr(10);

    	v_contreadreco := v_contreadreco + 1;

Si possono effettuare varie operazioni, come controllare i dati su un altra tabella considerando ad esempio il codiazie oppure operazioni di altro tipo.

Tutte i blocchi di istruzione devono essere incluse in BEGIN...END e prima dell'END inserire EXCEPTION con l'azione da controllare. Normalmente si mette WHEN OTHERS ma si possono controllare solo alcuni errori o fare una sorta di switch sui codici di errore di ritorno, e per ognuno dei quali eseguire una operazione. Insomma... molta liberta'!

Determinazione codi_serv


    IF  VR_ANAGAZIE.codiazie IS not NULL
    THEN
      BEGIN
         SELECT COUNT(*) AS TOTAEMAI
           INTO VN_TOTAEMAI
           FROM ANAGEMAI
          WHERE CODI_EMAI = (SELECT CODI_EMAI
                               FROM RELAAZIEEMAI
                              WHERE CODI_AZIE = VR_ANAGAZIE.codiazie);

       EXCEPTION
         WHEN OTHERS THEN

In caso di errore si puo' in questo punto
1. inserire la chiamata ad una funzione per l'invio di una mail
2. inserire i dati in una tabella di log
3. scrivere un file di log

Esempio di chiamata ad una funzione

INSEANOM('SERVIZIO ERRATO','ANAGAZIE', VR_ANAGAZIE.descserv,'Servizio Errato: '||VR_ANAGAZIE.DESCRESP,VC_USER_PROP,VC_USER_PROP,'F');
    v_contcodiserverra:=v_contcodiserverra + 1;
    v_contscarreco :=v_contscarreco+1;
    v_flaginse :='N';
    END;

Controllo sul totale delle mail estratte ed inserisco una ipotetica tabella di Statistica

        IF VN_TOTAEMAI > 0
        THEN
        	BEGIN
                insert into STATEMAIAZIE (
                              CODI_AZIE ,
                              TOTA_EMAI,
                              ULTI_OGGE_EMAI,
                              DATA_INSE,
                              ....)
                         values(VN_CODIAZIE     ,
                                VN_TOTAEMAI    ,
                                TO_DATE('01/01/'||TO_CHAR(VN_ANNOEMAI,'YYYY'),'DD/MM/YYYY'),
                                SYSDATE);
            EXCEPTION
               WHEN OTHERS THEN

             ... Gestione errori ...

        	END;
        ELSE
        	BEGIN;
			 EXCEPTION
		         WHEN OTHERS THEN

             ... Gestione errori ...

        	END;
        END IF;
    END IF;

Pulisco le variabili per la prossima lettura del cursore


    VN_CODIAZIE     := NULL;
    ...

END LOOP;

commit;


Messaggi di Output, attraverso una funzione di Oracle

 dbms_output.put_line ('.Record letti                                          : '||lpad (v_contreadreco,     &&v_contleng, ' ')||' rec.');
 dbms_output.put_line ('.Record Inseriti                                       : '||lpad (v_continsereco,     &&v_contleng, ' ')||' rec.');
 dbms_output.put_line ('.Record scartati                                       : '||lpad (v_contscarreco,     &&v_contleng, ' ')||' rec.');
 dbms_output.put_line ('.Record scartati in inserimento                        : '||lpad (v_continserecoscar, &&v_contleng, ' ')||' rec.');
 dbms_output.put_line ('.Record scartati per codiceazienderrato                : '||lpad (v_contcodiserverra, &&v_contleng, ' ')||' rec.');
 dbms_output.put_line ('.Record scartati per errore inserimento servizio       : '||lpad (v_continserecoscarserv, &&v_contleng, ' ')||' rec.');
 dbms_output.put_line ('.Servizi Inseriti                                      : '||lpad (v_continserecoserv, &&v_contleng, ' ')||' rec.');

La chiamata alla EXCEPTION della procedura

 EXCEPTION
  WHEN OTHERS THEN
   ROLLBACK;
   DBMS_OUTPUT.PUT_LINE ('.ERRORE IN FASE DI CARICAMENTO:');
   DBMS_OUTPUT.PUT_LINE ('..'||SQLERRM);
   DBMS_OUTPUT.PUT_LINE ('..NON E'' STATO CARICATO ALCUN RECORD.');
   IF '&&V_FLAGDEBU' = 'S'
   THEN
    DISPDEBU (V_DEBU);
   END IF;
END;
/

Operazioni di Chiusura

set serveroutput off

select to_char (sysdate, 'DD/MM/YYYY" ore "hh24:mi:ss') data
  from dual;

PROMPT .Elaborazione terminata il &&v_data
PROMPT .File di log: &&v_spoofile
PROMPT


spool off
rem $ notepad &&v_spoofile

clear columns

undef v_data
undef v_spoofile
undef v_contleng
undef v_flagdebu

set term on
set doc off
set feed on
set head on

exit