In questo tutorial, vedrai la descrizione dettagliata su come creare ed eseguire i blocchi nominati (procedure e funzioni).
Le procedure e le 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 delle funzioni integrate di Oracle.
In questo tutorial Oracle Stored Procedure imparerai:
- Terminologie nei sottoprogrammi PL / SQL
- Cos'è la procedura in PL / SQL?
- Cos'è la funzione?
- Somiglianze tra procedura e funzione
- Procedura vs. Funzione: differenze chiave
- Funzioni integrate in PL / SQL
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 un qualsiasi tipo di dato PL / SQL valido 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 deve 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
- Parametro IN
- Parametro OUT
- 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 .
- Per impostazione predefinita, 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.
Questo tipo di parametro dovrebbe essere menzionato al momento della creazione dei sottoprogrammi.
RITORNO
RETURN è la parola chiave che indica al compilatore di passare 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à ignorato.
Normalmente, il blocco padre o principale chiamerà i sottoprogrammi, quindi il controllo si sposterà da quei blocchi padre ai sottoprogrammi chiamati. RETURN nel sottoprogramma restituirà il controllo al blocco padre. Nel caso delle funzioni anche l'istruzione RETURN restituisce il valore. Il tipo di dati di questo valore è sempre menzionato al momento della dichiarazione della funzione. Il tipo di dati può essere di qualsiasi tipo di dati PL / SQL valido.
Cos'è la procedura in PL / SQL?
Una procedura in PL / SQL è un'unità di sottoprogramma che consiste in un gruppo di istruzioni PL / SQL che possono essere chiamate per nome. Ogni procedura in PL / SQL ha il proprio nome univoco con il quale può essere richiamata e chiamata. Questa unità di sottoprogramma nel database Oracle viene memorizzata come oggetto database.
Nota: il sottoprogramma non è altro che una procedura e deve essere creato manualmente secondo il requisito. Una volta creati, verranno archiviati come oggetti di database.
Di seguito sono riportate le caratteristiche dell'unità di sottoprogramma di procedura in PL / SQL:
- Le procedure sono blocchi indipendenti di un programma che possono essere memorizzati nel database.
- È possibile chiamare queste procedure PLSQL 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 o può essere definito e annidato all'interno di altri blocchi o pacchetti.
- Contiene parte dichiarazione (opzionale), parte esecuzione, parte gestione eccezioni (opzionale).
- I valori possono essere passati alla procedura Oracle o recuperati dalla procedura tramite parametri.
- Questi parametri dovrebbero essere inclusi nell'istruzione chiamante.
- Una routine 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( … )[ IS | AS ] BEGIN EXCEPTION END;
- CREATE PROCEDURE indica 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 dovrebbe essere univoco.
- La parola chiave "IS" verrà utilizzata quando la procedura memorizzata in Oracle è annidata in altri blocchi. Se la procedura è autonoma, verrà utilizzato "AS". Oltre a questo standard di codifica, entrambi hanno lo stesso significato.
Esempio 1: creazione di una procedura e chiamata tramite EXEC
In questo esempio, creeremo una procedura Oracle che prende il nome come input e stampa il messaggio di benvenuto come output. Useremo il comando EXEC per chiamare la procedura.
CREATE OR REPLACE PROCEDURE welcome_msg (p_name IN VARCHAR2)ISBEGINdbms_output.put_line (‘Welcome '|| p_name);END;/EXEC welcome_msg (‘Guru99’);
Spiegazione del codice:
- Riga di codice 1 : creazione della procedura con il nome "welcome_msg" e con un parametro "p_name" di tipo "IN".
- Riga di codice 4 : stampa del messaggio di benvenuto concatenando il nome di input.
- La procedura è stata compilata correttamente.
- Riga di codice 7 : Richiamo della procedura utilizzando il comando EXEC con il parametro "Guru99". La procedura viene eseguita e il messaggio viene stampato come "Welcome Guru99".
Cos'è la funzione?
Functions è un sottoprogramma PL / SQL autonomo. Come la procedura PL / SQL, le funzioni hanno un nome univoco con cui è possibile fare riferimento. Questi vengono memorizzati come oggetti di database PL / SQL. Di seguito sono riportate alcune delle 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 l'eccezione, ovvero return è 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 o può essere definito e annidato all'interno di altri blocchi o pacchetti.
- Contiene parte dichiarazione (opzionale), parte esecuzione, parte gestione eccezioni (opzionale).
- I valori possono essere passati alla funzione o recuperati dalla procedura tramite i parametri.
- Questi parametri dovrebbero essere inclusi nell'istruzione chiamante.
- Una funzione PLSQL può anche restituire il valore tramite parametri OUT diversi dall'utilizzo di RETURN.
- Dato che restituirà sempre il valore, nell'istruzione di chiamata si accompagna sempre con l'operatore di assegnazione per popolare le variabili.
Sintassi
CREATE OR REPLACE FUNCTION( )RETURN [ IS | AS ] BEGIN EXCEPTION END;
- CREATE FUNCTION indica 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 dovrebbe essere univoco.
- Il tipo di dati RETURN dovrebbe essere menzionato.
- La parola chiave "IS" verrà utilizzata quando la procedura è annidata in altri blocchi. Se la procedura è autonoma, verrà utilizzato "AS". Oltre a 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. Useremo il blocco anonimo e l'istruzione select per chiamare la funzione.
CREATE OR REPLACE FUNCTION welcome_msgJune ( p_name IN VARCHAR2) RETURN VAR.CHAR2ISBEGINRETURN (‘Welcome ‘|| p_name);END;/DECLARElv_msg VARCHAR2(250);BEGINlv_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 della funzione Oracle con nome "welcome_msg_func" e con un parametro "p_name" di tipo "IN".
- Riga di codice 2 : dichiarazione del tipo restituito come VARCHAR2
- Riga di codice 5 : restituzione del valore concatenato "Benvenuto" e del valore del parametro.
- Riga di codice 8 : blocco anonimo per chiamare la funzione precedente.
- Riga di codice 9 : dichiarazione della variabile con tipo di dati uguale al tipo di dati di ritorno della funzione.
- Riga di codice 11 : chiamata della funzione e inserimento del 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 : chiamata della 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 nella sezione relativa alla gestione delle eccezioni del sottoprogramma, verrà propagata al blocco chiamante.
- Entrambi possono avere tutti i parametri richiesti.
- Entrambi vengono trattati come oggetti di database in PL / SQL.
Procedura vs. Funzione: differenze chiave
| Procedura | Funzione |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
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 di uso comune e il loro utilizzo.
Funzioni di conversione
Queste funzioni incorporate vengono utilizzate per convertire un tipo di dati in un altro tipo di dati.
| Nome funzione | Utilizzo | Esempio |
|---|---|---|
| TO_CHAR | Converte l'altro tipo di dati in un tipo di dati carattere | TO_CHAR (123); |
| TO_DATE (stringa, formato) | Converte la stringa data fino alla data. La stringa dovrebbe corrispondere al formato. | TO_DATE ("2015-GEN-15", "AAAA-LUN-GG"); Uscita: 15/1/2015 |
| TO_NUMBER (testo, formato) | Converte il testo in un tipo di numero del formato specificato. Informat "9" denota il numero di cifre | Seleziona TO_NUMBER ("1234", "9999") da doppio; Output: 1234 Seleziona TO_NUMBER ('1,234.45', '9,999.99') da dual; Uscita: 1234 |
Funzioni di stringa
Queste sono le funzioni che vengono utilizzate sul tipo di dati del carattere.
| Nome funzione | Utilizzo | Esempio |
|---|---|---|
| INSTR (testo, stringa, inizio, occorrenza) | Fornisce la posizione di un particolare testo nella stringa data.
| Seleziona INSTR ('AIRPLANE', 'E', 2,1) dalla doppia uscita : 2 Seleziona INSTR ('AIRPLANE', 'E', 2,2) dalla doppia uscita: 9 (2 ° occorrenza di E) |
| SUBSTR (testo, inizio, lunghezza) | Fornisce il valore della sottostringa della stringa principale.
| seleziona substr ('aeroplane', 1,7) dal doppio Output : aeropla |
| UPPER (testo) | Restituisce le maiuscole del testo fornito | Seleziona upper ('guru99') da dual; Uscita : GURU99 |
| INFERIORE (testo) | Restituisce la minuscola del testo fornito | Selezionare inferiore ('aeroplano') da doppio; Uscita : aereo |
| INITCAP (testo) | Restituisce il testo specificato con la lettera iniziale in maiuscolo. | Seleziona ('guru99') dalla doppia uscita : Guru99 Seleziona ('la mia storia') dalla doppia uscita : La mia storia |
| LUNGHEZZA (testo) | Restituisce la lunghezza della stringa data | Selezionare LENGTH ('guru99') da dual; Uscita : 6 |
| LPAD (testo, lunghezza, pad_char) | Riempie la stringa sul lato sinistro per la lunghezza data (stringa totale) con il carattere dato | Seleziona LPAD ('guru99', 10, '$') da dual; Risultato : $$$$ guru99 |
| RPAD (testo, lunghezza, pad_char) | Riempie la stringa sul lato destro per la lunghezza data (stringa totale) con il carattere dato | Seleziona RPAD ('guru99', 10, '-') dalla doppia uscita : guru99 ---- |
| LTRIM (testo) | Ritaglia 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 di data
Queste sono funzioni utilizzate per manipolare le date.
| Nome funzione | Utilizzo | Esempio |
|---|---|---|
| ADD_MONTHS (data, numero di mesi) | Aggiunge i mesi indicati alla data | ADD_MONTH ("01-01-2015", 5); Uscita : 05/01/2015 |
| SYSDATE | Restituisce la data e l'ora correnti del server | Seleziona SYSDATE da dual; Uscita : 10/4/2015 2:11:43 PM |
| TRUNC | Arrotondare la variabile della data al valore più basso possibile | selezionare sysdate, TRUNC (sysdate) da dual; Uscita : 10/4/2015 2:12:39 PM 10/4/2015 |
| IL GIRO | Arrotonda la data al limite più vicino superiore o inferiore | Seleziona sysdate, ROUND (sysdate) dalla doppia uscita : 10/4/2015 2:14:34 PM 10/5/2015 |
| MONTHS_BETWEEN | Restituisce il numero di mesi tra due date | Seleziona MONTHS_BETWEEN (sysdate + 60, sysdate) dalla doppia uscita : 2 |
Sommario
In questo capitolo abbiamo imparato quanto segue.
- Come creare Procedure 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