ORACLE/PLSQL Tips & Tricks

[dino]

Apro questo topic non perché io sia un espertissimo di database Oracle, ma semplicemente perché mi sto facendo un po' di esperienza. Condividerò dunque volentieri tutte le cose che scopro nel mentre.

- Estrarre l'anno da un campo data
- Creare uno script con TOAD per le operazioni di ALTER TABLE
- Gerarchia nelle chiamate degli oggetti da query
- Ricerca per stringhe evitando maiuscole e minuscole
- Come scoprire quali righe contengono più occorrenze di un certo campo
- Come inserire un campo data con una INSERT?
- La gestione dei NULL
- Campo sequenza come fare update con valori sequenziali
- Utilizzare una sequence in una operazione di INSERT
- Lavorare con le date

[dino]

Estrarre l'anno da un campo data

Usando la funzione EXTRACT è possibile estrarre da una data un qualunque valore. Per prendere solo l'anno:

Codice: Seleziona tutto

EXTRACT (YEAR FROM DATE '2012-01-22') 

Provate ad eseguire: SELECT EXTRACT (YEAR FROM DATE '2012-01-22') FROM DUAL;

Il risultato sarà 2012.

Ovviamente sostituite DATE '2012-01-22' con il campo della tabella dal quale volete estrarre l'anno.

[dino]

Creare uno script con TOAD per le operazioni di ALTER TABLE

Quando si lavora con Oracle (come con qualunque altro DBMS) è necessario adottare degli strumenti grafici per la modifica, la creazione e la gestione delle strutture così come per la gestione dei dati. TOAD è certamente uno dei tool maggiormente utilizzati in questo ambito.

Vediamo dunque come creare uno script da utilizzare per le operazioni di ALTER TABLE. Queste solitamente vanno eseguite su tabelle in essere, dunque non è possibile cancellare la tabella e ricrearla da capo. Ovviamente potete anche scrivere questi comandi manualmente in SQL, ma la comodità di un editor ci risparmia di ricordare i tipi, il formato e via discorrendo.

Per creare lo script cliccate con il tasto destro del mouse sulla tabella che volete modificare. Selezionate la voce "Alter table".

uno.png

Nella maschera che vi compare effettuate tutte le modifiche, aggiungete colonne o indici, modificate il tipo, droppate ciò che non vi serve. Al termine di tutte le operazioni cliccate il tasto "Show SQL" e troverete lo script SQL per la replicazione delle modifiche effettuate su qualunque altro schema.

due.png

[dino]

Gerarchia nelle chiamate degli oggetti da query

Quando si lavora con più schemi di dati, Oracle permette di accedere agli oggetti di un altro database grazie all'uso dei sinonimi. Quando avviene una chiamata ad un oggetto tramite una query, la gerarchia degli oggetti interrogati è la seguente (supponiamo di aver una query "SELECT * FROM utente"):

1. Tabella "utente" locale. Se non esiste,
2. sinonimo "utente" locale. Se non esiste,
3. sinonimo "utente" globale.

[dino]

Ricerca per stringhe evitando maiuscole e minuscole

Quando si deve cercare una stringa all'interno di un campo, nonostante la disponibilità del carattere jolly (% nella fattispecie) potrebbe essere difficoltoso per via del fatto che non sappiamo come la stringa sia stata memorizzata (tutta in minuscolo, tutta in maiuscolo, o con lettere maiuscole e minuscole).

Per ovviare a questo è possibile confrontare il valore del campo da cercare con una stringa tutta maiuscola (ma andrebbe bene anche tutta minuscola) ma solo dopo aver trasformato il campo stesso in maiuscolo (minuscolo). Per farlo potete usare la funzione "upper":

Codice: Seleziona tutto

SELECT * FROM notizie WHERE upper(TITOLO) LIKE '%BITS%';

[dino]

Come scoprire quali righe contengono più occorrenze di un certo campo

Quando si deve fare manutenzione di un DB può capitare di trovarsi di fronte a tabelle per le quali non siano state definite le chiavi o solo semplicemente dei vincoli UNIQUE. Il problema è che se la tabella contiene già dei dati potrebbero esserci alcune righe che non rispettino il vincolo che vogliamo imporre. Come scoprire quali sono le righe incriminate?

Codice: Seleziona tutto

SELECT nome_campo FROM nome_tabella GROUP BY nome_campo HAVING COUNT(*) > 1

[dino]

Come inserire un campo data con una INSERT?

Per inserire una data (o time) in un campo di tipo date di Oracle usando una istruzione di INSERT INTO è necessario far ricorso alla funzione to_date.

Codice: Seleziona tutto

INSERT INTO nome_tabella (nome_campo_data) VALUES (to_date('15/03/2012 20:05:30', 'dd/mm/yyyy hh24:mi:ss'));

Maggiori informazioni sulla funzione to_date sono reperibili a questo indirizzo.

[dino]

La gestione dei NULL
In Oracle ci sono delle funzioni specifiche per gestire i valori null. Ad esempio:

Codice: Seleziona tutto

NVL(stringa_da_controllare, valore_da_sostituire_al_null)

Se dunque si esegue:

Codice: Seleziona tutto

SELECT NVL(indirizzo, 'Indirizzo non fornito') FROM fornitori;

il risultato sarà l'indirizzo immesso se nel campo indirizzo c'è un valore; altrimenti se c'è NULL avrò come risultato 'Indirizzo non fornito'.

[dino]

Campo sequenza come fare update con valori sequenziali

Nel caso in cui in una tabella venga aggiunto un campo sequenza in un secondo momento quando al suo interno ci sono già dei dati, è necessario inserire dei valori sequenziali al suo interno. Il modo più semplice per farlo è con un semplice UPDATE prendendo i valori dal numero di riga:

Codice: Seleziona tutto

UPDATE nome_tabella SET campo_sequenza = rownum;

[dino]

Utilizzare una sequence in una operazione di INSERT

Capita molto spesso di creare delle tabelle che, come chiave primaria, utilizzano una sequenza. Ecco come usarla correttamente in una operazione di inserimento di una riga nella tabella.

Supponiamo di avere la seguente tabella di esempio:

Codice: Seleziona tutto

CREATE TABLE TABELLA_DI_ESEMPIO
(
  ID  INTEGER             NOT NULL,
  LABEL             VARCHAR2(50)        NOT NULL,
  DESCRIZIONE       VARCHAR2(200)  
)

ALTER TABLE TABELLA_DI_ESEMPIO ADD (
  CONSTRAINT PTABELLA_DI_ESEMPIO_PK
 PRIMARY KEY
 (ID));

In questo modo creiamo la sequence:

Codice: Seleziona tutto

CREATE SEQUENCE TABELLA_DI_ESEMPIO_SEQ
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  NOCACHE
  NOORDER;

Ed ecco come deve essere utilizzata:

Codice: Seleziona tutto

INSERT INTO TABELLA_DI_ESEMPIO (ID, LABEL, DESCRIZIONE) 
VALUES (TABELLA_DI_ESEMPIO_SEQ.nextVal, 'T1', 'Test 1');