Oracle Procedure memorizzate e funzioni PL/SQL con esempi

By: Fiona Marrone Fiona Marrone
Hours aggiornato

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 permette di dare input ai sottoprogrammi e di estrarre 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.

Cos'è la procedura in PL/SQL?

A Procedura in PL/SQL è un'unità di sottoprogramma costituita da un gruppo di istruzioni PL/SQL che possono essere chiamate per nome. Ogni procedura in PL/SQL ha il proprio nome univoco con cui è possibile fare riferimento e richiamarla. Questa unità di sottoprogramma nel Oracle il database viene archiviato come oggetto di database.

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’);

Spiegazione del codice:

  • Riga di codice 1: Creazione della procedura con nome 'welcome_msg' e con un parametro 'p_name' di tipo 'IN'.
  • Riga di codice 4: stampa del messaggio di benvenuto concatenando il nome immesso.
  • La procedura è stata compilata correttamente.
  • Riga di codice 7: Chiamare la procedura utilizzando il comando EXEC con il parametro 'Guru99'. La procedura viene eseguita e il messaggio viene stampato come "Welcome Guru99".

ARTICOLI CORRELATI

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.

Funzioni in PL/SQL

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.

Funzioni in PL/SQL

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;

Spiegazione del codice:

  • Riga di codice 1: Creazione del Oracle funzione con nome 'welcome_msg_func' e con un parametro 'p_name' di tipo 'IN'.
  • Riga di codice 2: dichiarando il tipo restituito come VARCHAR2
  • Riga di codice 5: Restituisce il valore concatenato 'Benvenuto' e il valore del parametro.
  • Riga di codice 8: Blocco anonimo per chiamare la funzione precedente.
  • Riga di codice 9: Dichiarare la variabile con tipo di dati uguale al tipo di dati restituito dalla funzione.
  • Riga di codice 11: Chiamare la funzione e popolare il valore restituito nella variabile 'lv_msg'.
  • Riga di codice 12: Stampa del valore della variabile. L'output che otterrai qui è "Welcome Guru99"
  • Riga di codice 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 dual;

Uscita: Guru99
RTRIM (testo) Taglia lo spazio bianco finale dal testo Seleziona RTRIM('Guru99 ') da dual;

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