DATA BASE - PL SQL - ORACLE - APPUNTI

NOTE ORACLE TIPS&TRICKS

- Dopo l'installazione di Oracle, se lo usate solo a livello client disabilitate TUTTI i servizi. (services.msc)

INTRODUZIONE AI DATA BASE

Un Database (base di dati) è una collezione organizzata di dati gestita ed organizzata da un software particolare chiamato DBMS ovvero Data Base Management System (Sistema di gestione di Data Base).
Grazie a questo potentissimo software, l’utente finale non accederà mai ai dati così come sono memorizzati effettivamente, cioè alla loro rappresentazione fisica, ma ne vedrà solamente una rappresentazione logica.
Le funzionalità principali che ci si deve aspettare da un DBMS sono quelle di:
- Consentire l’accesso ai dati attraverso uno schema concettuale, invece che attraverso uno schema fisico;
- Permettere la condivisione dei dati a più utenti;
- Controllare l’accesso ai dati adottando una opportuna politica di sicurezza e di integrità dei dati;
- Diminuire considerevolmente l’inconsistenza e la ridondanza dei dati.

I modelli più comuni di database sono:

1.Modello Gerarchico, in cui i dati sono organizzati in record connessi tra loro secondo strutture ad albero. Ogni record del database deve avere un unico padre. Possono esserci più record, su alberi diversi, che rappresentino la stessa informazione andando a creare però vari problemi come ad esempio la ridondanza dei dati e continui controlli di consistenza. La manutenzione di un database gerarchico è alquanto complessa poiché, ad esempio, la cancellazione di un record comporta l’eliminazione di tutti i record dipendenti da esso, l’aggiornamento di un dato richiede la modifica di altri record per assicurare la consistenza del database. Il modello gerarchico comporta la dipendenza dei programmi dalle strutture, quindi non possiamo modificare le strutture senza modificare i programmi.

2.Modello Reticolare, in cui i record sono legati tra loro con strutture ad anello che permettono all’utente finale di accedere ai dati in maniera più semplice, senza i vincoli rigidi della struttura gerarchica. Ogni nodo può essere un punto di partenza per raggiungere un campo. A differenza della struttura gerarchica, nel modello reticolare un record può avere uno o più record padre permettendo quindi i problemi relativi alla ridondanza dei dati.
Non si escludono tuttavia problemi relativi alla dipendenza dei programmi dalle strutture e soprattutto alla complessità delle strutture stesse che aumentano in proporzione alla crescita dei dati. Per modificare un database con questa struttura, bisogna cancellare il database e ricrearlo.

3.Modello Relazionale, in cui i dati sono organizzati in tabelle. A differenza delle altre strutture, il modello relazionale offre molta stabilità e flessibilità in modo da garantire la possibilità di sviluppare nuove ed impreviste applicazioni senza per questo dover riprogettare, o per lo meno ristrutturare, l’organizzazione dell’intero archivio dati (indipendenza logica), consentendo quindi un costante aggiornamento della sua struttura ed evitando la modifica di tutti i programmi applicativi che ne fanno uso.

DATABASE RELAZIONALE

Un database relazionale è un insieme di tabelle che rappresentano ogni tipo di informazione.
Per reperire i dati è sufficiente conoscere la loro struttura logica e non è necessario conoscere la loro locazione fisica.
Più campi formano un record
Più record formano una tabella
Più tabelle formano un database
Affinché un database possa essere definito relazionale, deve essere in grado di eseguire le tre operazioni relazionali di base:
la proiezione, la selezione e il join.
- La proiezione è la visualizzazione delle colonne;
- La selezione è la visualizzazione di alcune righe che soddisfano una determinata condizione;
- Il Join è l’unione di record che sono memorizzati su diverse tabelle.

Esistono diversi tipi di join:

1. Il Prodotto Cartesiano di due tabelle A, B è l’insieme di tutte le possibili coppie di record appartenenti alla tabella A con ogni record della tabella B. Non ha alcun senso fare il prodotto cartesiano di due tabelle poiché il risultato sarà la visualizzazione di tutti i campi delle rispettive tabelle senza alcun criterio di relazione;

2. Il Self Join ovvero l’interrogazione della tabella con se stessa (N.B. Sarà indispensabile creare due alias alla tabella);

3. L’Outer Join è un join tra due tabelle su una delle quali il record in join può anche mancare ma il record dell’altra tabella viene visualizzato comunque.

....................Ora è giunto il momento di entrare più in dettaglio:

RDBMS ORACLE

Oracle è un sistema complesso costituito da un software in grado di gestire un database relazionale in base a richieste effettuate nel linguaggio SQL (Structured Query Language).
In altre parole Oracle è l’insieme dei seguenti componenti:
- Un database relazionale;
- Un linguaggio SQL (un linguaggio di quarta generazione per l’accesso ai dati);
- Un insieme di programmi di gestione del database (Kernel e Tool).

Vediamo ora di spiegare in maniera più dettagliata questo linguaggio così potente che è l’SQL.
Il Linguaggio SQL (Structured Query Language) è un linguaggio non procedurale che è diventato uno standard tra i linguaggi per la gestione di database relazionali. Nel 1986 l'ANSI (American National Standards Institute) pubblicò il primo standard del linguaggio SQL, al quale seguì lo standard dell'ISO (International Standards Organization) pubblicato proprio l'anno seguente.

Il linguaggio SQL è in grado di elaborare gruppi di dati piuttosto che unità di programma grazie ad una serie di particolari comandi.

Questi comandi consentono di interrogare i dati memorizzati all'interno delle strutture del database, inserire, aggiornare e cancellare i record presenti nel database, creare, modificare ed eliminare gli oggetti dal database, controllare l'accesso ai database e ai loro oggetti e garantire la consistenza e l'integrità dei dati.
Possiamo dire quindi che le istruzioni SQL possono essere suddivise nelle seguenti categorie funzionali:

Istruzioni per l'interrogazione dei dati (Select)

Istruzioni DML (Data Manipulation Language)

Istruzioni CTL (Control Transaction Language)

Istruzioni DCL (Data Control Language)

Istruzioni DDL (Data Definition Language)

Occupiamoci adesso di spiegare in dettaglio l'istruzione più potente e più importante del linguaggio SQL.

IL COMANDO SELECT

Come abbiamo già detto, l'aspetto più importante del linguaggio SQL è quello di porre delle interrogazioni al database al fine di reperire i dati che cerchiamo.
Queste prestazioni vengono fornite dal comando SELECT, sicuramente un comando potentissimo ma allo stesso tempo molto semplice da usare (...spesso vi capiterà di farci a botte......quindi....occhi aperti e attenti a quello che scrivete).

La sintassi del comando è la seguente:

SELECT * | nome_colonna1, nome_colonna2, ... , nome_colonnan
FROM nome_tabella
questo comando vi permetterà di visualizzare tutti i dati memorizzati nella tabella compresi i valori duplicati.
SELECT * | nome_colonna1, nome_colonna2, ... , nome_colonnan
FROM nome_tabella WHERE condizione
questo comando vi permetterà di visualizzare tutti i dati memorizzati nella tabella che soddisferanno una determinata condizione.
SELECT DISTINCT * | nome_colonna1, nome_colonna2, ... , nome_colonnan
FROM nome_tabella
questo comando vi permetterà di visualizzare tutte i valori univoci presenti nella tabella
SELECT * | nome_colonna1, nome_colonna2, ... , nome_colonnan
FROM nome_tabella
ORDER BY [nome_colonna1, nome_colonna2, ... , nome_colonnan | 1, 2, ...,n] [ASC | DESC]
questo comando vi permetterà di visualizzare tutti i dati memorizzati nella tabella ordinati in modo ascendente in base a nome_colonna1, nome_colonna2, ... , nome_colonnan.Il tipo di ordinamento predefinito è ascendente, quindi dal valore più piccolo al più grande; per avere un ordinamento discendente, ovvero dal valore più grande al valore più piccolo, si dovrà esplicitamente precisare DESC .
SELECT SUM(nome_colonna1), nome_colonna2, ... , nome_colonnan
FROM nome_tabella
GROUP BY nome_colonna2.
Se usate una funzione di aggregazione come la SUM, nella successiva GROUP BY vanno elencate tutte le altre colonne che non partecipano nella funzione di raggruppamento (ricordatevi questa regola mi raccomando).

Un'altra istruzione potente che usa il comando Select è rappresentata dalle SUBQUERY o Query Innestate

Una subquery è un'istruzione select annidata all'interno di un'altra istruzione generalmente usata per finalità di comparazione tra dati. Le subquery devono restituire sempre un unico valore se sono impiegate con l'operatore "=" altrimenti possono restituire più valori se usati con l'operatore "IN":

SELECT nome_colonna1, nome_colonna2
FROM nome_tabella WHERE nome_colonna1 = (SELECT nome_colonna1 FROM nome_altra_tabella WHERE condizione)
SELECT nome_colonna1 FROM nome_tabella WHERE nome_colonna1 IN (SELECT nome_colonna1 FROM nome_tabella2 WHERE condizione)

PRODOTTO CARTESIANO E JOIN

Quando nella clausola FROM di una istruzione SELECT viene specificata più di una tabella senza impiegare la clausola WHERE allora viene effettuato un prodotto cartesiano che restituisce un set di risultati (recordset) uguale al prodotto di tutte le possibili combinazioni di righe in ciascuna tabella (es. fra due tabelle una di 20 righe ed una di 10 avremo un set composto dall’unione delle colonne di entrambe le tabelle e da 200 righe). Per evitare la voluminosità eccessiva dei prodotti cartesiani in genere si introducono dei filtri mediante opportune condizioni specificate nella clausola WHERE.
Queste condizioni o predicati sono delle tipiche espressioni booleane che coinvolgono le colonne delle tabelle interessate dalla select e vengono definiti “condizioni di join”, fornendo come risultato un prodotto cartesiano chiamato più brevemente “join”. Il numero minimo di join necessarie a mettere in relazione un set di tabelle è uguale a n-1 (dove n indica il numero delle tabelle che partecipano alla select).
Nel lavoro quotidiano vi capiterà spessisime volte ti dover fare delle join tra tabelle e sarà molto importante avere chiaro il concetto. Ci sono tre tipi di join: Equi Join, Self Join e Outer Join.

L’equijoin viene generalmenteimpiegato per visualizzare i dati di due o più tabelle che hanno valori comuni nelle corrispondenti colonne.Esso è così chiamato poiché impiega uno degli operatori di uguaglianza o disuguaglianza stabilendo una relazione tra le tabelle sulla base delle colonne comuni.Naturalmente i tipi di dato di queste colonne devono essere uguali o comunque convertibili. SELECT nome_tab1_colonna1, nome_tab2_colonna1 FROM nome_tab1, nome_tab2 WHERE nome_tab1_colonna1 = nome_tab2_colonna1;

Il self-join è usato quando è necessario mettere una tabella in relazione con se stessa. In questo caso per occorre impiegare un alias di tabella:

SELECT alias_tab1.nome_colonna1, alias_tab2.nome_colonna2 FROM nome_tabella alias_tab1, nome_tabella alias_tab2;

L’outer-join viene impiegato quando per una delle tabelle che partecipano al join mancano record correlati nell’altra tabella e si vuole che la SELECT restituisca comunque tutti i record.In tal caso, anche se una determinata riga della tabella non soddisfa la condizione di join per nessuna riga dell’altra tabella, essa viene ugualmente restituita (pur contenendo valori nulli).Il join esterno si ottiene indicando il simbolo (+) dalla parte dell’attributo della tabella che fornisce meno valori di quanti ne siano presenti nel corrispondente attributo dell’altra tabella.
Esistono delle limitazioni:
- l’outer join può apparire soltanto nella clausola WHERE ed è valido soltanto se riferito a colonne di tabelle/viste (sono escluse le espressioni);
- una colonna utilizzata in outer-join non può essere comparata con una query innestata;
- una condizione di outer-join non può essere usata in una condizione IN né può essere collegata con un OR logico ad un’altra condizione;

OPERATORI

OPERATORE	DESCRIZIONE
(+) e (-)	operatori unari che indicano il segno algebrico di un numero
(), (/), (+), (-)*	operatori binari di moltiplicazione e divisione
(\|\|)	operatore binario di concatenazione di stringhe
NOT, AND, OR	operatori logici
(=)	operatore di uguaglianza
(!=), (^=),(<>)	operatori di disuguaglianza
(>),(>=),(<),(<=)	operatori di confronto
IN, (=ANY), (=SOME)	operatore di appartenenza ad una lista
NOT IN, (!=ALL)	operatore di non appartenenza
ANY, SOME	operatori di confronto con almeno un valore di una lista (devono essere preceduti da un operatore di uguaglianza, disuguaglianza o confronto)
ALL	operatore di confronto con tutti i valori di una lista (deve essere preceduto da un operatore di uguaglianza, disuguaglianza o confronto)
[NOT] BETWEEN x AND y	[Non] maggiore di o uguale a x e minore di o uguale a y
EXISTS	TRUE se la subquery restituisce almeno una riga
x [NOT] LIKE y [ESCAPE ‘z’]	TRUE se la stringa x contiene la stringa y. All’interno di y il carattere speciale ‘%’ indica una stringa di zero o più caratteri mentre il carattere speciale ‘_’ indica un qualsiasi carattere. I caratteri speciali ‘%’ e ‘_’ preceduti dal carattere che segue la clausola ESCAPE perdono la loro peculiarità per riassumere il loro valore letterale.
IS [NOT] NULL	Verifica se un valore è nullo. Il valore NULL è indefinito e come tale non è uguale a nessun altro valore, neanche a un altro NULL.
UNION	unisce i risultati di due SELECT e scarta le righe duplicate
UNION ALL	unisce i risultati di due SELECT lasciando i duplicati
INTERSECT	mostra solo le righe comuni alle due SELECT scartando i duplicati
MINUS	restituisce tutte le righe distinte della prima select che non appartengono all’insieme

Questi ultimi quattro operatori sono i comuni operatori insiemistici usati quando è necessario unire il risultato di due query (ad esmpio per produrre un report da più tabelle).
Per usare questi operatori è necessario che le colonne delle tabelle interessate dalle varie SELECT abbiano lo stesso tipo di dati mentre il nome e la lunghezza possono essere anche diversi. L’ordine delle select unite non è importante per gli operatori UNION e UNION ALL mentre lo è per INTERSECT e MINUS.

TIPI DI DATO IN ORACLE

Ogni colonna di una tabella in Oracle ha un suo tipo di dato che definisce il dominio dei dati possibile per quella determinata colonna.
I tipi di dato usati più di frequente in Oracle sono riportati nella tabella seguente

TIPO DI DATO	DESCRIZIONE
char(dim)	carattere, dimensione max 2000 bytes
varchar2(dim)	carattere di lunghezza variabile fino a 4000 bytes
number	(precisione, scala) numerico
long	carattere fino a 2 gigabytes
long raw	binario fino a 2 gigabytes
raw(dim)	binario, dimensione max 2000 bytes
date	data, dal 1/1/4712 B.C al 12/31/4712 A.C
blob	binario fino a 4 gigabytes
clob	carattere fino a 4 gigabytes
bfile	puntatore
rowid	interno

Una colonna di tipo char occupa sempre la dimensione specificata a prescindere dal suo riempimento effettivo mentre il varchar2 occupa sempre la dimensione effettiva (fino al raggiungimento del valore max).
Il formato standard di visualizzazione delle date in Oracle è (10-APR-99).
E’ ammesso un solo campo di tipo long e long raw all’interno di una tabella (viceversa ci possono essere diversi campi di tipo BLOB).
Il tipo di dato bfile rappresenta in realtà una sorta di puntatore ad un file, cioè il dato effettivamente memorizzato da una colonna di questo tipo è la locazione del file all’interno del file-system e non il suo contenuto reale.
Il rowid è un tipo di dato interno, non definibile dall’utente, usato per identificare in modo univoco ciascun record di una tabella.

ISTRUZIONI DML - DATA MANIPULATION LANGUAGE

Questa categoria comprende le istruzioni per inserire,cancellare o modificare i dati memorizzati nelle tabelle, in particolare:

COMANDO	DESCRIZIONE
INSERT	inserisce nuovi record in una tabella
UPDATE	modifica i records esistenti in una tabella
DELETE	cancella i records esistenti in una tabella

MOLTO IMPORTANTE:
Le modifiche apportate da questi comandi hanno bisogno del COMMIT per confermarle oppure del ROLLBACK per annullarle

FAQ

1) SCRIPT PER ESEGUIRE UNA DELETE:
#######################################################################################################
SET SERVEROUTPUT ON
SET VERIFY ON
SPOOL delrec_sap_serviz_account.log
PROMPT
SELECT TO_CHAR (SYSDATE, 'DAY, DD/MM/YYYY HH24:MI:SS') inizio
FROM DUAL;
PROMPT
SET TRANSACTION USE ROLLBACK SEGMENT bigroll;
PROMPT sap_serviz_account
DECLARE
conta NUMBER;
BEGIN
DBMS_OUTPUT.ENABLE (100000);
conta := 0;
--###########################################
delete sap_serviz_account s
WHERE 0 <(SELECT COUNT (*)
FROM view_sap_serviz_account vs
WHERE s.ute = vs.ute
AND NVL (s.puntopresa, -9999) = NVL (vs.puntopresa, -9999)
AND s.app_oldkey_sap = vs.app_oldkey_sap
AND NVL (s.flag_caricamento, '?') =NVL (vs.flag_caricamento, '?'));
--------------------------------------------
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT bigroll;
DBMS_APPLICATION_INFO.set_action ('Cancellati: '||SQL%ROWCOUNT||' sap_serviz_account');
DBMS_OUTPUT.put_line ('Cancellate '||SQL%ROWCOUNT||' righe');
conta := SQL%ROWCOUNT;
WHILE SQL%NOTFOUND = FALSE
LOOP
--###########################################
delete sap_serviz_account s
WHERE 0 <(SELECT COUNT (*)
FROM view_sap_serviz_account vs
WHERE s.ute = vs.ute
AND NVL (s.puntopresa, -9999) = NVL (vs.puntopresa, -9999)
AND s.app_oldkey_sap = vs.app_oldkey_sap
AND NVL (s.flag_caricamento, '?') =NVL (vs.flag_caricamento, '?'));
--------------------------------------------
COMMIT;
SET TRANSACTION USE ROLLBACK SEGMENT bigroll;
DBMS_OUTPUT.put_line ('Cancellate ' || SQL%ROWCOUNT || ' righe');
conta := conta + SQL%ROWCOUNT;
DBMS_APPLICATION_INFO.set_action ('Cancellati: '|| TO_CHAR (conta)|| ' sap_serviz_account');
END LOOP;

DBMS_OUTPUT.put_line ('Cancellati IN TOTALE: ' || TO_CHAR (conta) || ' sap_serviz_account');
END;
/
PROMPT
SELECT TO_CHAR (SYSDATE, 'DAY, DD/MM/YYYY HH24:MI:SS') fine FROM DUAL;
PROMPT
SPOOL OFF;
EXIT
###################################################################################################

Salvare lo script in un file nomefile.scr UNIX e lanciarlo in questo modo (non c'è bisogno di CHMOD 777)

nohup sqlplus test/test @nomefile.scr &

2)COME VERIFICARE TNS
EX: tnsping80 10.91.40.108

questa pagina è stata realizzata da Luca Ramaglia