Programmare in PL/SQL

OCA Oracle 10g
-
Programmare in PL/SQL
Distribuito tramite il sito www.manualioracle.it
Copyright © 2011 Assi Loris
Qualsiasi abuso sarà perseguito e punito secondo i termini di legge.
Version: 1.3.5
Questo non è un documento ufficiale della Oracle Corporation.
Alcuni termini usati sono trademarks registrati dei rispettivi proprietari.
Oracle 10g – OCA Programmare in PL/SQL
Sommario
Introduzione al Manuale ........................................................................................................................................ 4
Cap. 1 Introduzione ad Oracle, all’SQL e al PL/SQL .................................................................................. 5
Cap. 2 Introduzione ai PL/SQL Programs ................................................................................................... 6
2.1 Il PL/SQL Program Construct ........................................................................................................................ 6
2.2 I componenti di un PL/SQL block................................................................. Error! Bookmark not defined.
2.3 I benefits dei subprograms ............................................................................ Error! Bookmark not defined.
2.4 Come invocare una Procedura o Funzione .................................................... Error! Bookmark not defined.
Cap. 3 Creare le Procedure ............................................................................................................................ 7
3.1 Cosa è una stored procedure ........................................................................................................................... 7
3.2
Creare e droppare una procedura .............................................................. Error! Bookmark not defined.
3.3 Formal and Actual Parameters ...................................................................... Error! Bookmark not defined.
3.4 Procedure con Parametri e i tipi di parameter modes .................................... Error! Bookmark not defined.
3.5 Invocare una procedura con parametri .......................................................... Error! Bookmark not defined.
3.6 Il subprogram nella declarative section di una Procedura ............................. Error! Bookmark not defined.
3.7 Come le Exception sono propagate ............................................................... Error! Bookmark not defined.
Cap. 4 Creare le Funzioni ............................................................................................................................... 8
4.1 Cosa è una stored function ............................................................................................................................. 8
4.2 Creare, Invocare e rimuovere una Funzione .................................................. Error! Bookmark not defined.
4.3 Vantaggi e restrizioni delle chiamate dagli SQL statement ........................... Error! Bookmark not defined.
4.4 Dove le funzioni vengono chiamate negli SQL statement ............................ Error! Bookmark not defined.
4.5 Differenze fra procedure e funzioni .............................................................. Error! Bookmark not defined.
Cap. 5 Gestire i Subprograms ........................................................................... Error! Bookmark not defined.
5.1 System Privileges, Object Privileges e Grant Privileges ............................... Error! Bookmark not defined.
5.2 Differenze fra Invokers Rights e Definers Rights ......................................... Error! Bookmark not defined.
5.3 Le viste di dizionario per gestire gli stored objects ....................................... Error! Bookmark not defined.
Cap. 6 Creare i Package ..................................................................................... Error! Bookmark not defined.
6.1 Creare e Droppare Packages........................................................................... Error! Bookmark not defined.
6.2 Identificare un Package specification e un Package body ............................. Error! Bookmark not defined.
6.3 Usare la DESCRIBE per descrivere i package e i suoi componenti .............. Error! Bookmark not defined.
6.4 Disegnare un package construct come public o private ................................. Error! Bookmark not defined.
6.5 Invocare un package construct ...................................................................... Error! Bookmark not defined.
6.6 Usare un bodiless package ............................................................................ Error! Bookmark not defined.
6.7 I benefits dei Package .................................................................................... Error! Bookmark not defined.
Cap. 7 Altri Concetti relativi ai Package .......................................................... Error! Bookmark not defined.
7.1 Package che usano l’overloading .................................................................. Error! Bookmark not defined.
7.2 Usare la Forward Referencing ....................................................................... Error! Bookmark not defined.
7.3 Inizializzare variabili con una one-time-only procedure ............................... Error! Bookmark not defined.
7.4 Invocare le packaged function dagli SQL ..................................................... Error! Bookmark not defined.
7.5 Usare le PL/SQL tables e i records nei Package ........................................... Error! Bookmark not defined.
Cap. 8 Oracle Supplied Packages ...................................................................... Error! Bookmark not defined.
8.1 Native Dynamic SQL: i benefits dell’Execute Immediate rispetto al DBMS_SQL .... Error! Bookmark not
defined.
8.2 Identificare il flusso dell’esecuzione ............................................................. Error! Bookmark not defined.
8.3 Uso e applicazione di alcuni Oracle supplied Packages ................................ Error! Bookmark not defined.
Cap. 9 Gestire i Large Objects (LOBs) ............................................................. Error! Bookmark not defined.
9.1 Comparare i LONG con i Large Object (LOB) data type ............................. Error! Bookmark not defined.
9.2 Creare e gestire i LOB data type ................................................................... Error! Bookmark not defined.
9.3 Differenze fra Internal ed External LOBs ...................................................... Error! Bookmark not defined.
9.4 Identificare e Gestire i BFiles ........................................................................ Error! Bookmark not defined.
9.5 Migrare da LONG a LOB ............................................................................. Error! Bookmark not defined.
9.6 Usare il package DBMS_LOB ....................................................................... Error! Bookmark not defined.
9.7 Creare e popolare i LOB columns ................................................................. Error! Bookmark not defined.
9.8 Fare le SQL operations nei LOBS ................................................................. Error! Bookmark not defined.
9.9 Descrivere l’uso dei temporary LOBs ............................................................ Error! Bookmark not defined.
Cap. 10 Creare i db Trigger............................................................................ Error! Bookmark not defined.
10.1 Definizione di trigger e Tipi di trigger ........................................................ Error! Bookmark not defined.
10.2 Linee Guida per progettare i trigger ............................................................ Error! Bookmark not defined.
10.3 Creare un DML trigger ................................................................................ Error! Bookmark not defined.
2
www.manualioracle.it
Oracle 10g – OCA Programmare in PL/SQL
10.4 I componenti del DML trigger .................................................................... Error! Bookmark not defined.
10.5 I trigger row level, statement level e la loro firing sequence ....................... Error! Bookmark not defined.
10.6 I conditional predicated in un trigger .......................................................... Error! Bookmark not defined.
10.7 Usare i qualifiers OLD e NEW in un DML Trigger .................................... Error! Bookmark not defined.
10.8 Creare un INSTEAD OF trigger.................................................................. Error! Bookmark not defined.
10.9 Differenze fra stored procedures e trigger .................................................... Error! Bookmark not defined.
10.10 Il trigger execution model ......................................................................... Error! Bookmark not defined.
10.11 Cambiare un trigger status ......................................................................... Error! Bookmark not defined.
10.12 Rimuovere un trigger ................................................................................. Error! Bookmark not defined.
Cap. 11 Altri Concetti relativi ai Trigger ...................................................... Error! Bookmark not defined.
11.1 Eventi che fanno scattare il DB trigger ....................................................... Error! Bookmark not defined.
11.2 Creare un trigger per un DDL statement ...................................................... Error! Bookmark not defined.
11.3 Creare un trigger per un system event ......................................................... Error! Bookmark not defined.
11.4 Descrivere la funzionalità del CALL statement .......................................... Error! Bookmark not defined.
11.5 La mutating table ......................................................................................... Error! Bookmark not defined.
11.6 Per cosa possono essere implementati i trigger ........................................... Error! Bookmark not defined.
11.7 I privilegi associati ai trigger ....................................................................... Error! Bookmark not defined.
11.8 Le trigger information nelle dictionary views .............................................. Error! Bookmark not defined.
Cap. 12 Gestire le Dipendenze ........................................................................ Error! Bookmark not defined.
12.1 Tracciare le dipendenze procedurali ............................................................ Error! Bookmark not defined.
12.2 Descrivere gli oggetti dipendenti e gli oggetti referenziati.......................... Error! Bookmark not defined.
12.3 Definire le local dependencies ..................................................................... Error! Bookmark not defined.
12.4 Le view dependency information nelle dictionary views ............................ Error! Bookmark not defined.
12.5 Lo script UTLDTREE.sql e le procedure IDEPTREE e DEPTREE ......... Error! Bookmark not defined.
12.6 Descrivere una remote dependency ............................................................. Error! Bookmark not defined.
12.7 Listare come vengono gestite le remote dependencies ................................ Error! Bookmark not defined.
12.8 Ricompilazione di una Program Unit con e senza successo ........................ Error! Bookmark not defined.
12.9 Come minimizzare le dependency failures .................................................. Error! Bookmark not defined.
www.manualioracle.it
3
Oracle 10g – OCA Programmare in PL/SQL
Introduzione al Manuale
Contenuto
Il presente manuale tratta quasi tutti gli argomenti della certificazione Oracle “Oracle 10g: Program with PL/SQL”
(1Z0-147).
Audience
Il presente manuale è rivolto a chiunque voglia imparare le basi del linguaggio PL/SQL e le informazioni qui
contenute sono molto simili in ogni versione Oracle.
Particolarità
Il presente manuale è in italiano ma molti termini tecnici in esso contenuti sono in lingua inglese.
Abbiamo fatto tale scelta perché spesso a lavoro tali termini vengono detti in inglese e perché riteniamo più utile che
i lettori del manuale conoscano tali termini nella lingua usata nella documentazione ufficiale Oracle.
Molti argomenti sono schematizzati al fine di fornire una comprensione ed una memorizzazione superiore.
Principali Versioni
- 29.06.2011 : version 1.0
- 30.06.2011 : version 1.1 varie modifiche e rilascio al pubblico
Disclaimer
Non si fornisce alcuna garanzia relativamente al fatto che il presente documento sia privo di errori. Non si assume
nessuna responsabilità sugli eventuali errori o danni derivanti dall’uso delle informazioni qui contenute.
4
www.manualioracle.it
Oracle 10g – OCA Programmare in PL/SQL
Cap. 1 - Introduzione ad Oracle, all’SQL e al PL/SQL
Questo manuale si focalizzerà sul linguaggio PL/SQL.
Tuttavia, riteniamo utile fare una premessa sull’ambiente in cui si utilizza tale linguaggio e dunque qui forniamo una
breve definizione dei concetti più importanti.
DBMS (Database Management System)
Software progettato per creare e manipolare in modo efficiente un database (ovvero un insieme di dati strutturati)
solitamente da parte di più utenti.
Fra i DBMS più diffusi c’è l’Oracle database, prodotto dalla società americana Oracle Corporation.
RDBMS (Relational Database Management System)
Come si evince dal suo nome in inglese, è un sistema di gestione del database basato sul modello relazionale.
Il modello relazionale è un modello logico di rappresentazione dei dati.
Secondo tale modello, qualsiasi dato può essere rappresentato come relazione. E tale relazione può essere
rappresentata graficamente tramite una tabella.
Oracle Server
E’ costituito da 2 principali componenti:
-
Il database: è una raccolta di dati correlati. Ha una struttura logica (tabelle, indici, ecc...) e una fisica (i file
in cui sono immagazzinati i dati)
-
L’istanza: è costituita da una struttura di memoria e dai processi in background che gestiscono il db
Normalmente ogni db ha una singola istanza associata.
A volte c’è più di una istanza associata ad un singolo db e in questo caso di parla di Oracle Real Application Clusters
(RAC).
SQL*Plus
…………
…………
…………
www.manualioracle.it
5
Oracle 10g – OCA Programmare in PL/SQL
Cap. 2 - Introduzione ai PL/SQL Programs
2.1 Il PL/SQL Program Construct
Il construtto base del PL/SQL è il cosiddetto blocco PL/SQL.
Esso è strutturato così:
DECLARE
-- facoltativo
-- dichiarazioni delle variabili
…
BEGIN
-- obbligatorio
-- comandi di esecuzione
…
EXCEPTION
-- facoltativo
-- gestione delle eccezioni
…
END;
Nel prossimo paragrafo descriveremo nel dettaglio questi 3 componenti.
…………
…………
…………
6
www.manualioracle.it
Oracle 10g – OCA Programmare in PL/SQL
Cap. 3 - Creare le Procedure
3.1 Cosa è una stored procedure
Una stored procedure è uno stored PL/SQL subprogram e dunque fornisce:
-
modularità (modularity)
-
possibilità di essere riutilizzata (reusability)
Una procedura non è invocata come una funzione perché generalmente non ritorna un valore.
Per chiamare una procedura, usi l’EXECUTE statement e un valore può essere ritornato a seconda del mode dei
parametri: se hai parametri con mode OUT o IN OUT allora la procedura ritornerà un valore.
Una procedura può essere chiamata in vari modi:
1) tramite l’EXECUTE command: esso può essere usato in SQL*Plus così:
SQL> EXECUTE my_procedure
O anche:
SQL> EXEC my_procedure
…………
…………
…………
www.manualioracle.it
7
Oracle 10g – OCA Programmare in PL/SQL
Cap. 4 - Creare le Funzioni
4.1 Cosa è una stored function
Una stored function è uno stored PL/SQL subprogram e dunque fornisce:
-
modularità (modularity)
-
possibilità di essere riutilizzata (reusability).
Una funzione ha questi componenti:
-
l’header, incluso la RETURN clause e la keyword IS o AS
-
il body della funzione
-
opzionalmente, puoi anche includere le local declarations (dopo l’header) e una exception-handling section
(dopo l’executable section).
Es:
CREATE OR REPLACE FUNCTION fz_calc (in_old_salary IN NUMBER) RETURN
NUMBER IS
v_new_salary NUMBER;
BEGIN
v_new_salary:= in_old_salary+100;
RETURN v_new_salary;
END;
…………
…………
…………
8
www.manualioracle.it