Oracle Procedure memorizzate e funzioni PL/SQL con esempi

In questo tutorial vedrai la descrizione dettagliata su come creare ed eseguire i blocchi nominati (procedure e funzioni).

Procedure e Funzioni sono i sottoprogrammi che possono essere creati e salvati nel database come oggetti di database. Possono essere chiamati o riferiti anche all'interno degli altri blocchi.

Oltre a questo, tratteremo le principali differenze tra questi due sottoprogrammi. Inoltre, discuteremo di Oracle funzioni integrate.

Terminologie nei sottoprogrammi PL/SQL

Prima di conoscere i sottoprogrammi PL/SQL, discuteremo le varie terminologie che fanno parte di questi sottoprogrammi. Di seguito sono riportate le terminologie di cui parleremo.

Parametro

Il parametro è variabile o segnaposto di qualsiasi valore valido Tipo di dati PL/SQL attraverso il quale il sottoprogramma PL/SQL scambia i valori con il codice principale. Questo parametro consente di fornire input ai sottoprogrammi e di esportaretract da questi sottoprogrammi.

• Questi parametri dovrebbero essere definiti insieme ai sottoprogrammi al momento della creazione.
• Questi parametri sono inclusi nell'istruzione di chiamata di questi sottoprogrammi per interagire i valori con i sottoprogrammi.
• Il tipo di dati del parametro nel sottoprogramma e l'istruzione chiamante dovrebbero essere gli stessi.
• La dimensione del tipo di dati non dovrebbe essere menzionata al momento della dichiarazione del parametro, poiché la dimensione è dinamica per questo tipo.

In base al loro scopo i parametri sono classificati come

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

Parametro IN

• Questo parametro viene utilizzato per fornire input ai sottoprogrammi.
• È una variabile di sola lettura all'interno dei sottoprogrammi. I loro valori non possono essere modificati all'interno del sottoprogramma.
• Nell'istruzione chiamante, questi parametri possono essere una variabile o un valore letterale o un'espressione, ad esempio potrebbe essere l'espressione aritmetica come '5*8' o 'a/b' dove 'a' e 'b' sono variabili .
• Di default i parametri sono di tipo IN.

Parametro OUT

• Questo parametro viene utilizzato per ottenere l'output dai sottoprogrammi.
• È una variabile di lettura-scrittura all'interno dei sottoprogrammi. I loro valori possono essere modificati all'interno dei sottoprogrammi.
• Nell'istruzione chiamante, questi parametri dovrebbero sempre essere una variabile per contenere il valore dei sottoprogrammi correnti.

Parametro IN OUT

• Questo parametro viene utilizzato sia per fornire input che per ottenere output dai sottoprogrammi.
• È una variabile di lettura-scrittura all'interno dei sottoprogrammi. I loro valori possono essere modificati all'interno dei sottoprogrammi.
• Nell'istruzione chiamante, questi parametri dovrebbero sempre essere una variabile per contenere il valore dei sottoprogrammi.

Questi tipi di parametri dovrebbero essere menzionati al momento della creazione dei sottoprogrammi.

RITORNO

RETURN è la parola chiave che indica al compilatore di trasferire il controllo dal sottoprogramma all'istruzione chiamante. Nel sottoprogramma RETURN significa semplicemente che il controllo deve uscire dal sottoprogramma. Una volta che il controller trova la parola chiave RETURN nel sottoprogramma, il codice successivo verrà saltato.

Normalmente, il blocco genitore o principale chiamerà i sottoprogrammi, quindi il controllo passerà da quel blocco genitore ai sottoprogrammi chiamati. RETURN nel sottoprogramma restituirà il controllo al blocco genitore. Nel caso delle funzioni RETURN anche l'istruzione restituisce il valore. Il tipo di dati di questo valore viene sempre menzionato al momento della dichiarazione della funzione. Il tipo di dati può essere qualsiasi tipo di dati PL/SQL valido.

Nota: Il sottoprogramma non è altro che una procedura e deve essere creato manualmente secondo i requisiti. Una volta creati verranno archiviati come oggetti del database.

Di seguito sono riportate le caratteristiche dell'unità del sottoprogramma Procedura in PL/SQL:

• Le procedure sono blocchi autonomi di un programma che possono essere archiviati nel file banca dati.
• La chiamata a queste procedure PLSQL può essere effettuata facendo riferimento al loro nome, per eseguire le istruzioni PL/SQL.
• Viene utilizzato principalmente per eseguire un processo in PL/SQL.
• Può avere blocchi annidati oppure può essere definito e annidato all'interno di altri blocchi o pacchetti.
• Contiene la parte di dichiarazione (facoltativa), la parte di esecuzione, la parte di gestione delle eccezioni (facoltativa).
• I valori possono essere passati Oracle procedura o recuperato dalla procedura tramite parametri.
• Questi parametri dovrebbero essere inclusi nell'istruzione di chiamata.
• Una procedura in SQL può avere un'istruzione RETURN per restituire il controllo al blocco chiamante, ma non può restituire alcun valore tramite l'istruzione RETURN.
• Le procedure non possono essere chiamate direttamente dalle istruzioni SELECT. Possono essere richiamati da un altro blocco o tramite la parola chiave EXEC.

Sintassi

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

• CREATE PROCEDURE ordina al compilatore di creare una nuova procedura in Oracle. La parola chiave 'OR REPLACE' indica alla compilazione di sostituire la procedura esistente (se presente) con quella corrente.
• Il nome della procedura deve essere univoco.
• Verrà utilizzata la parola chiave "IS" quando la procedura memorizzata sarà inserita Oracle è nidificato in alcuni altri blocchi. Se la procedura è autonoma, verrà utilizzato "AS". A parte questo standard di codifica, entrambi hanno lo stesso significato.

Esempio 1: creazione di una procedura e chiamata tramite EXEC

In questo esempio, creeremo un file Oracle procedura che prende il nome come input e stampa il messaggio di benvenuto come output. Utilizzeremo il comando EXEC per chiamare la procedura.

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 Linea 1: Creazione della procedura con nome 'welcome_msg' e con un parametro 'p_name' di tipo 'IN'.
• Code Linea 4: stampa del messaggio di benvenuto concatenando il nome immesso.
• La procedura è stata compilata correttamente.
• Code Linea 7: Chiamata della procedura tramite il comando EXEC con il parametro 'Guru99'. La procedura viene eseguita e il messaggio viene stampato come "Benvenuto Guru99 ".

Che cos'è la funzione?

Funzioni è un sottoprogramma PL/SQL autonomo. Come la procedura PL/SQL, le funzioni hanno un nome univoco con cui possono essere riferite. Questi vengono archiviati come oggetti di database PL/SQL. Di seguito sono riportate alcune caratteristiche delle funzioni.

• Le funzioni sono un blocco autonomo utilizzato principalmente a scopo di calcolo.
• La funzione utilizza la parola chiave RETURN per restituire il valore e il tipo di dati di questo è definito al momento della creazione.
• Una funzione dovrebbe restituire un valore o sollevare un'eccezione, ovvero il ritorno è obbligatorio nelle funzioni.
• La funzione senza istruzioni DML può essere chiamata direttamente nella query SELECT mentre la funzione con operazione DML può essere chiamata solo da altri blocchi PL/SQL.
• Può avere blocchi annidati oppure può essere definito e annidato all'interno di altri blocchi o pacchetti.
• Contiene la parte di dichiarazione (facoltativa), la parte di esecuzione, la parte di gestione delle eccezioni (facoltativa).
• I valori possono essere passati alla funzione o recuperati dalla procedura tramite i parametri.
• Questi parametri dovrebbero essere inclusi nell'istruzione di chiamata.
• Una funzione PLSQL può anche restituire il valore tramite parametri OUT diversi dall'utilizzo di RETURN.
• Poiché restituirà sempre il valore, nell'istruzione di chiamata accompagna sempre l'operatore di assegnazione per popolare le variabili.

Sintassi

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

• CREATE FUNCTION ordina al compilatore di creare una nuova funzione. La parola chiave "OR REPLACE" indica al compilatore di sostituire la funzione esistente (se presente) con quella corrente.
• Il nome della funzione deve essere univoco.
• Il tipo di dati RETURN dovrebbe essere menzionato.
• Verrà utilizzata la parola chiave "IS" quando la procedura è annidata in altri blocchi. Se la procedura è autonoma, verrà utilizzato "AS". A parte questo standard di codifica, entrambi hanno lo stesso significato.

Esempio 1: creazione di una funzione e chiamata tramite blocco anonimo

In questo programma creeremo una funzione che prende il nome come input e restituisce il messaggio di benvenuto come output. Utilizzeremo il blocco anonimo e l'istruzione select per chiamare la funzione.

CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2
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 Linea 1: Creazione del Oracle funzione con nome 'welcome_msg_func' e con un parametro 'p_name' di tipo 'IN'.
• Code Linea 2: dichiarando il tipo restituito come VARCHAR2
• Code Linea 5: Restituisce il valore concatenato 'Benvenuto' e il valore del parametro.
• Code Linea 8: Blocco anonimo per chiamare la funzione precedente.
• Code Linea 9: Dichiarare la variabile con tipo di dati uguale al tipo di dati restituito dalla funzione.
• Code Linea 11: Chiamare la funzione e popolare il valore restituito nella variabile 'lv_msg'.
• Code Linea 12: Stampa del valore della variabile. L'output che otterrai qui è "Benvenuto Guru99 "
• Code Linea 14: Chiamare la stessa funzione tramite l'istruzione SELECT. Il valore restituito viene indirizzato direttamente allo standard output.

Somiglianze tra procedura e funzione

• Entrambi possono essere richiamati da altri blocchi PL/SQL.
• Se l'eccezione sollevata nel sottoprogramma non viene gestita nel sottoprogramma la gestione delle eccezioni sezione, quindi si propagherà 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

Procedura	Funzione
Utilizzato principalmente per eseguire determinati processi	Utilizzato principalmente per eseguire alcuni calcoli
Impossibile chiamare nell'istruzione SELECT	Una funzione che non contiene istruzioni DML può essere chiamata nell'istruzione SELECT
Utilizzare il parametro OUT per restituire il valore	Utilizzare RETURN per restituire il valore
Non è obbligatorio restituire il valore	È obbligatorio restituire il valore
RETURN uscirà semplicemente dal controllo dal sottoprogramma.	RETURN uscirà dal controllo dal sottoprogramma e restituirà anche il valore
Il tipo di dati restituito non verrà specificato al momento della creazione	Il tipo di dati restituito è obbligatorio al momento della creazione

Funzioni integrate in PL/SQL

PL / SQL contiene varie funzioni integrate per lavorare con stringhe e tipo di dati di data. Qui vedremo le funzioni comunemente utilizzate e il loro utilizzo.

Funzioni di conversione

Queste funzioni integrate vengono utilizzate per convertire un tipo di dati in un altro tipo di dati.

Nome della funzione	Impiego	Esempio
TO_CHAR	Converte l'altro tipo di dati in tipo di dati carattere	TO_CHAR(123);
TO_DATE (stringa, formato)	Converte la stringa data fino ad oggi. La stringa dovrebbe corrispondere al formato.	TO_DATE('2015-GEN-15', 'AAAA-LUN-GG'); Uscita: 1 / 15 / 2015
TO_NUMBER (testo, formato)	Converte il testo nel tipo numerico del formato specificato. L'informazione '9' indica il numero di cifre	Seleziona TO_NUMBER('1234′,'9999') da doppio; Uscita: 1234 Seleziona TO_NUMBER('1,234.45′,'9,999.99') da doppio; Uscita: 1234

Funzioni di stringa

Queste sono le funzioni utilizzate sul tipo di dati carattere.

Nome della funzione	Impiego	Esempio
INSTR(testo, stringa, inizio, occorrenza)	Fornisce la posizione di un testo particolare nella stringa data. testo – Stringa principale stringa: testo che deve essere cercato start – posizione iniziale della ricerca (facoltativo) conformità – occorrenza della stringa cercata (facoltativo)	Selezionare INSTR('AEROPLANE','E',2,1) da dual Uscita: 2 Selezionare INSTR('AEROPLANE','E',2,2) da dual Uscita: 9 (2nd verificarsi di E)
SUBSTR (testo, inizio, lunghezza)	Fornisce il valore della sottostringa della stringa principale. testo – stringa principale partenza – posizione di partenza length – lunghezza da sottoincordare	select substr('aereo',1,7) from dual Uscita: aeropla
SUPERIORE (testo)	Restituisce la lettera maiuscola del testo fornito	Seleziona upper('guru99') da dual; Uscita:GURU99
INFERIORE (testo)	Restituisce la minuscola del testo fornito	Selezionare inferiore ("AerOpLane") da doppio; Uscita: aereo
INITCAP (testo)	Restituisce il testo specificato con la lettera iniziale maiuscola.	Seleziona ("guru99") da doppio Uscita: Guru99 Seleziona ('la mia storia') da doppio Uscita: La mia storia
LUNGHEZZA (testo)	Restituisce la lunghezza della stringa data	Seleziona LUNGHEZZA ('guru99') da doppio; Uscita: 6
LPAD (testo, lunghezza, pad_char)	Riempie la stringa nella parte sinistra per la lunghezza data (stringa totale) con il carattere dato	Seleziona LPAD('guru99', 10, '$') da dual; Uscita: $$$$guru99
RPAD (testo, lunghezza, pad_char)	Riempie la stringa nella parte destra per la lunghezza data (stringa totale) con il carattere dato	Seleziona RPAD('guru99′,10,'-') da duale Uscita:guru99—-
LTRIM (testo)	Taglia lo spazio bianco iniziale dal testo	Seleziona LTRIM(' Guru99') da duale; Uscita: Guru99
RTRIM (testo)	Taglia lo spazio bianco finale dal testo	Seleziona RTRIM('Guru99 ') da duale; Uscita; Guru99

Funzioni data

Queste sono funzioni utilizzate per manipolare le date.

Nome della funzione	Impiego	Esempio
ADD_MONTHS (data, n. di mesi)	Aggiunge i mesi indicati alla data	ADD_MONTH('2015-01-01',5); Uscita: 05 / 01 / 2015
SYSDATA	Restituisce la data e l'ora correnti del server	Seleziona SYSDATE da doppio; Uscita: 10/4/2015 2:11:43
TRUNC	Arrotonda la variabile della data al valore più basso possibile	seleziona sysdate, TRUNC(sysdate) da dual; Uscita: 10/4/2015 2:12:39 10/4/2015
ROTONDO	Arrotonda la data al limite più vicino, superiore o inferiore	Seleziona sysdate, ROUND(sysdate) da dual Uscita: 10/4/2015 2:14:34 10/5/2015
MESI_TRA	Restituisce il numero di mesi tra due date	Selezionare MONTHS_BETWEEN (sysdate+60, sysdate) da dual Uscita: 2

Sintesi

In questo capitolo abbiamo imparato quanto segue.

• Come creare una procedura e diversi modi per chiamarla
• Come creare una funzione e diversi modi per chiamarla
• Somiglianze e differenze tra Procedura e Funzione
• Parametri e RETURN terminologie comuni nei sottoprogrammi PL/SQL
• Funzioni integrate comuni in Oracle PL / SQL