Oracle Procedure memorizzate e funzioni PL/SQL con esempi

Che cosa sono i subprogrammi PL/SQL?

In questo tutorial, viene descritta in dettaglio la procedura per creare ed eseguire i blocchi, le procedure e le funzioni specificati.

Procedure e funzioni sono sottoprogrammi che possono essere creati e salvati nel database come oggetti di database. Possono essere richiamati o referenziati anche all'interno di altri blocchi.

Trattiamo anche le principali differenze tra questi due sottoprogrammi e ne discutiamo Oracle funzioni integrate.

Terminologie nei sottoprogrammi PL/SQL

Prima di approfondire i sottoprogrammi PL/SQL, esamineremo la terminologia specifica che li caratterizza.

Parametro

Un parametro è una variabile o un segnaposto di qualsiasi valore valido Tipo di dati PL/SQL tramite il quale il sottoprogramma PL/SQL scambia valori con il codice principale. Questo parametro consente l'input ai sottoprogrammi e l'extraczione di valori da loro.

• Questi parametri dovrebbero essere definiti insieme ai sottoprogrammi al momento della creazione.
• Sono inclusi nell'istruzione di chiamata per interagire con i sottoprogrammi.
• Il tipo di dati del parametro nella sottoprogramma e nell'istruzione chiamante deve essere lo stesso.
• La dimensione del tipo di dati non deve essere specificata al momento della dichiarazione del parametro, poiché la dimensione è dinamica.

In base al loro scopo, i parametri vengono classificati come:

1. Parametro IN
2. Parametro OUT
3. Parametro IN OUT

Parametro IN

• Utilizzato per fornire input ai sottoprogrammi.
• Si tratta di una variabile di sola lettura all'interno dei sottoprogrammi; il suo valore non può essere modificato all'interno del sottoprogramma.
• Nell'istruzione di chiamata, può trattarsi di una variabile, un valore letterale o un'espressione, come '5*8' o 'a/b'.
• Per impostazione predefinita, i parametri sono di tipo IN.

Parametro OUT

• Utilizzato per ottenere l'output dai sottoprogrammi.
• Si tratta di una variabile di lettura e scrittura all'interno dei sottoprogrammi; il suo valore può essere modificato al loro interno.
• Nell'istruzione di chiamata, dovrebbe sempre essere una variabile a contenere il valore proveniente dalla sottoprogramma.

Parametro IN OUT

• Utilizzato sia per fornire input che per ricevere output dai sottoprogrammi.
• Si tratta di una variabile di lettura e scrittura all'interno dei sottoprogrammi; il suo valore può essere modificato al loro interno.
• Nell'istruzione di chiamata, dovrebbe sempre essere una variabile a contenere il valore proveniente dalla sottoprogramma.

Il tipo di parametro deve essere specificato al momento della creazione dei sottoprogrammi.

RITORNO

RETURN è la parola chiave che indica al compilatore di passare il controllo dalla sottoprogramma all'istruzione chiamante. In una sottoprogramma, RETURN significa semplicemente che il controllo deve uscire dalla sottoprogramma; una volta che il controllore trova RETURN, il codice successivo viene saltato.

Normalmente, il blocco principale chiama i sottoprogrammi e il controllo passa dal blocco principale al sottoprogramma chiamato. L'istruzione RETURN all'interno del sottoprogramma restituisce il controllo al blocco principale. Nel caso delle funzioni, l'istruzione RETURN restituisce anche un valore, il cui tipo di dati è specificato al momento della dichiarazione della funzione.

Che cos'è una procedura in PL/SQL?

A Procedura In PL/SQL, una subprogram unit è costituita da un gruppo di istruzioni PL/SQL che possono essere chiamate per nome. Ogni procedura ha un nome univoco ed è memorizzata nel file Oracle database come oggetto database.

Nota: Un sottoprogramma non è altro che una procedura e deve essere creato manualmente in base alle esigenze. Una volta creato, viene memorizzato come oggetto del database.

Le caratteristiche di un'unità di sottoprogramma procedura in PL/SQL sono:

• Le procedure sono blocchi autonomi che possono essere memorizzati nel banca dati.
• Possono essere richiamati tramite il loro nome per eseguire le istruzioni PL/SQL.
• Vengono utilizzati principalmente per eseguire un processo.
• Possono contenere blocchi annidati, oppure essere annidati all'interno di altri blocchi o pacchetti.
• Contengono una parte di dichiarazione (facoltativa), una parte di esecuzione e una parte di gestione delle eccezioni (facoltativa).
• I valori possono essere passati a una procedura o recuperati da essa tramite parametri.
• Questi parametri dovrebbero essere inclusi nell'istruzione di chiamata.
• Una procedura può avere un'istruzione RETURN per restituire il controllo al blocco chiamante, ma non può restituire alcun valore tramite RETURN.
• Le procedure non possono essere richiamate direttamente dalle istruzioni SELECT; possono essere richiamate da un altro blocco o tramite la parola chiave EXEC.

Sintassi

CREATE OR REPLACE PROCEDURE
<procedure_name>
(
<parameter1 IN/OUT <datatype>
..
.
)
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• L'istruzione CREATE PROCEDURE indica al compilatore di creare una nuova procedura. La parola chiave 'OR REPLACE' indica di sostituire la procedura esistente (se presente) con quella corrente.
• Il nome della procedura deve essere univoco.
• La parola chiave 'IS' viene utilizzata quando la stored procedure è annidata all'interno di un altro blocco. Se la procedura è autonoma, si utilizza 'AS'. A parte questa convenzione di codifica, entrambe hanno lo stesso significato.

Esempio 1: Creazione di una procedura e sua chiamata tramite EXEC. In questo esempio creiamo un Oracle Procedura che accetta un nome come input e stampa un messaggio di benvenuto come output, utilizzando il comando EXEC per richiamarla.

CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)
IS
BEGIN
dbms_output.put_line ('Welcome '|| p_name);
END;
/
EXEC welcome_msg ('Guru99');

Code Spiegazione:

• Code riga 1: Creazione della procedura denominata 'welcome_msg' e con un parametro 'p_name' di tipo 'IN'.
• Code riga 4: Stampa il messaggio di benvenuto concatenando il nome inserito.
• La procedura è stata compilata correttamente.
• Code riga 7: Chiamata della procedura tramite EXEC con il parametro 'Guru99'. La procedura viene eseguita e stampa "Benvenuto Guru99 ".

Che cos'è una funzione?

Una funzione è un sottoprogramma PL/SQL autonomo. Come una procedura, una funzione ha un nome univoco e viene memorizzata come oggetto del database PL/SQL. Le sue caratteristiche sono:

• Le funzioni sono blocchi indipendenti utilizzati principalmente per i calcoli.
• Una funzione utilizza la parola chiave RETURN per restituire un valore, il cui tipo di dati è definito al momento della creazione.
• Una funzione deve restituire un valore o generare un'eccezione; il valore di ritorno è obbligatorio nelle funzioni.
• Una funzione senza istruzioni DML può essere chiamata direttamente in una query SELECT, mentre una funzione con istruzioni DML può essere chiamata solo da altri blocchi PL/SQL.
• Può contenere blocchi annidati, oppure essere annidato all'interno di altri blocchi o pacchetti.
• Contiene una parte di dichiarazione (facoltativa), una parte di esecuzione e una parte di gestione delle eccezioni (facoltativa).
• I valori possono essere passati alla funzione o recuperati da essa tramite parametri.
• Questi parametri dovrebbero essere inclusi nell'istruzione di chiamata.
• Una funzione può restituire un valore anche tramite i parametri OUT, oltre che tramite RETURN.
• Poiché restituisce sempre un valore, l'istruzione chiamante utilizza sempre un operatore di assegnazione per popolare una variabile.

Sintassi

CREATE OR REPLACE FUNCTION
<function_name>
(
<parameter1 IN/OUT <datatype>
)
RETURN <datatype>
[ IS | AS ]
<declaration_part>
BEGIN
<execution part>
EXCEPTION
<exception handling part>
END;

• CREATE FUNCTION indica al compilatore di creare una nuova funzione. OR REPLACE indica di sostituire la funzione esistente (se presente) con quella corrente.
• Il nome della funzione deve essere univoco.
• È necessario specificare il tipo di dati RETURN.
• La parola chiave 'IS' viene utilizzata quando la funzione è annidata all'interno di un altro blocco. Se la funzione è autonoma, si utilizza 'AS'.

Esempio 1: Creazione di una funzione e sua chiamata tramite un blocco anonimo. In questo programma, creiamo una funzione che accetta un nome come input e restituisce un messaggio di benvenuto, utilizzando un blocco anonimo e un'istruzione SELECT per richiamarla.

CREATE OR REPLACE FUNCTION welcome_msg_func ( p_name IN VARCHAR2) RETURN VARCHAR2
IS
BEGIN
RETURN ('Welcome '|| p_name);
END;
/
DECLARE
lv_msg VARCHAR2(250);
BEGIN
lv_msg := welcome_msg_func ('Guru99');
dbms_output.put_line(lv_msg);
END;
/
SELECT welcome_msg_func('Guru99') FROM DUAL;

Code Spiegazione:

• Code riga 1: Creazione della funzione denominata 'welcome_msg_func' e con un parametro 'p_name' di tipo 'IN'.
• Code riga 2: Dichiarazione del tipo di ritorno come VARCHAR2.
• Code riga 5: Restituisce il valore concatenato 'Welcome' e il valore del parametro.
• Code riga 8: Blocco anonimo per richiamare la funzione sopra descritta.
• Code riga 9: Dichiarazione della variabile con lo stesso tipo di dati del tipo di ritorno della funzione.
• Code riga 11: Chiamata della funzione e assegnazione del valore restituito alla variabile 'lv_msg'.
• Code riga 12: Stampa il valore della variabile. L'output è "Benvenuto Guru99 ".
• Code riga 14: Richiamando la stessa funzione tramite un'istruzione SELECT. Il valore restituito viene indirizzato all'output standard.

Somiglianze tra una procedura e una funzione

• Entrambi possono essere richiamati da altri blocchi PL/SQL.
• Se un'eccezione sollevata nel sottoprogramma non viene gestita nel suo gestione delle eccezioni sezione, si propaga al blocco chiamante.
• Entrambi possono avere tutti i parametri richiesti.
• Entrambi sono trattati come oggetti di database in PL/SQL.

Procedura vs. funzione: differenze chiave

Funzioni integrate in PL/SQL

PL / SQL Contiene diverse funzioni integrate per lavorare con i tipi di dati stringa e data. Qui vediamo le funzioni più comunemente utilizzate e il loro utilizzo.

Funzioni di conversione

Queste funzioni integrate convertono un tipo di dati in un altro.

Funzioni di stringa

Queste funzioni vengono utilizzate sul tipo di dati carattere.

Funzioni data

Queste funzioni vengono utilizzate per manipolare le date.

DOMANDE FREQUENTI