|
Come implementare un campo counter in Oracle |
Contatore in Oracle: IntroduzioneNel porting di un database da MS Access ad Oracle spesso si incontra il problema dei Campi di tipo COUNTER, che, quasi sempre, rappresentano chiavi primarie. Questo tipo di dato, infatti, non esiste nella modalità nativa del DMBS Oracle (almeno fino alla versione 7.3), pertanto si è costretti ad utilizzare un campo di tipo NUMBER. Poi o si aggiornano tutte le query di inserimento della nostra applicazione (le quali necessitano prima di una routine di ricerca del successivo ID) o si scrive un TRIGGER (ovvero una funzione scritta in PL-SQL direttamente memorizzata e gestita dal DBMS) che si preoccupa di fare tutto ciò.
Questo tutorial analizza la seconda soluzione ed evidenzia l’utilizzo di un oggetto del DBMS sconosciuto a chi "proviene" da MS Access:
le "Sequence". |
|
La creazione della sequence counter_course in PL-SQLConsideriamo ad esempio una Tabella per gestire i corsi attivati da una Facoltà. Una possibile definizione può essere:
-- NOME FILE:
-- corso.sql
CREATE TABLE corso(
id_corso NUMBER(5),
nome_corso VARCHAR(35)
);
ALTER TABLE corso ADD PRIMARY KEY (id_corso);
CREATE SEQUENCE counter_course INCREMENT BY 1 START WITH 100 NOCACHE;
COMMIT;
-- FINE SQL
Il precedente listato crea la tabella "corso", che è costituita da due campi:
· il codice del corso (id_corso)
· il nome del corso.
Successivamente impostiamo il campo "id_corso" come chiave primaria (implicitamente tale campo sarà impostato come "NOT_NULL")e viene definita una Sequenza denominata "counter_course".
Questa Sequenza parte da 100 e si incrementà di 1. |
|
La store procedure per incrementare il contatore-- sp_CORSO.sql
-- DESCRIZIONE:
-- 1)Trigger per la simulazione del Contatore
id_corso della
-- Tabella CORSO. Utilizzo la SEQUENCE
counter_course.
-- VALORI di RITORNO:
-- Il prossimo CORSO.id_course utile.
-------------------------------------------------
set termout on
prompt Creazione del package sp_CORSO,...
CREATE OR REPLACE PACKAGE sp_CORSO AS
last_id_corso CORSO.id_corso%TYPE;
END sp_CORSO;
/
prompt -1-------------PACKAGE SP_CORSO creato!
CREATE TRIGGER TRG_CNT_CORSO
BEFORE INSERT OR UPDATE ON CORSO
FOR EACH ROW
DECLARE
iCounter CORSO.id_corso%TYPE;
CORSO_counter_except EXCEPTION;
BEGIN
IF INSERTING THEN
IF (:new.id_corso IS NULL) THEN
SELECT counter_course.NEXTVAL into iCounter
FROM dual;
:new.id_corso := iCounter;
-- Memorizzo ultimo valore utile nella variabile di pacchetto sp_CORSO.last_id_corso
sp_CORSO.last_id_corso := iCounter;
ELSE
RAISE CORSO_counter_except;
END IF;
END IF;
EXCEPTION
WHEN CORSO_counter_except THEN
raise_application_error(-20000, 'Errore:
[CORSO.id_corso]->COUNTER!, non deve comparire nella INSERT SQL!');
END;
/
----- EOF
|
|
Le variabili della store procedureLa store procedure genera un trigger per la tabella "corso" ed associa la sequenza "counter_course" alla chiave primaria "id_corso".
Lo script inizia con una direttiva (set termout on) al compilatore, la quale impone gli "echo" di eventuali messaggi utili per il controllo dell’esecuzione.
Si passa, quindi, alla creazione del package di variabili e funzioni (trigger e store procedure) disponibili per la tabella "corso" e si dichiara un'unica variabile (di pacchetto) che conterrà l’ultimo "id_corso" che è stato generato; quindi si passa al trigger (autoesplicativo) vero e proprio.
Il trigger utilizza due variabili locali: una per calcolare il prossimo "id_corso" utile (prelevandolo dalla sequenza counter_course.NextValue) e un’altra ( CORSO_counter_except) per il controllo delle eccezioni (generate, in questo caso, da una forzatura del campo "id_corso" erroneamente incluso nelle istruzioni SQL "INSERT INTO corso"). |
|
Testing della sequence counter_coursePer testare quanto detto è sufficiente copiare i due script esposti in due file di testo: corso.sql e sp_corso.sql; spostarli in una directory visibile da Oracle (per esempio: C:\ORAWINXX\DBS) ed eseguirli dalla console SQL Plus con i comandi:
sql> @corso.sql
sql> @sp_corso.sql
quindi provate ad eseguire delle INSERT, ad esempio:
sql> INSERT INTO corso (nome_corso) VALUES (‘Basi di dati’);
sql> INSERT INTO corso (nome_corso) VALUES (‘Ingegneria del Software’);
Poi provate ad eseguire una query di selezione su "corso" e verificate il risultato. Provate poi a rieseguire la prima INSERT e riceverete un errore (come è giusto che sia). |
|
|
|
|
