Le procedure memorizzate aiutano con l'ottimizzazione del database e nel miglioramento della riusabilità. Invece di dover eseguire la stessa query, puoi creare l'attività come una procedura memorizzata che chiamerai quando richiesto. Imparerai tutto sulle procedure memorizzate entro la fine di questo post.
Lavorare con le procedure memorizzate in PostgreSQL
Come utente PostgreSQL, potresti aver notato che le funzioni PostgreSQL non eseguono transazioni. Sebbene sia possibile creare una transazione, non è possibile confermarla o riportarla allo stato precedente. Tuttavia, queste limitazioni vengono aggirate utilizzando le procedure memorizzate.
Ecco la sintassi di base per creare una procedura memorizzata in PostgreSQL:
CREA O SOSTITUISCI PROCEDURA nome_procedura(
parametro[i] tipo_dati
)
LINGUA plpsql;
COME $$
DICHIARARE
variabili_se_qualsiasi tipo_dati
INIZIO
logica
FINE;
$$
Gli elementi chiave da notare dalla sintassi data sono il 'procedure_name', che è il nome che utilizzerai per la procedura memorizzata, i parametri che desideri includere e i relativi tipi di dati, e la logica che sono principalmente le istruzioni SQL.
Diamo tre esempi per aiutarti a capire come creare le procedure memorizzate in PostgreSQL.
Esempio 1: una procedura memorizzata per calcolare il quadrato di un numero
Per il nostro primo esempio, creiamo una procedura memorizzata che utilizza l'istruzione 'RAISE NOTICE' come modo per stampare l'output sul terminale. La procedura memorizzata prende il valore intero che le dai quando la chiami e calcola il suo quadrato.
Ecco come creiamo la procedura memorizzata:
Chiamiamo il nostro parametro come 'num1' ed è un numero intero. Per la parte logica, definiamo come ottiene il quadrato di “num1” e lo memorizza come variabile quadrata. Quando eseguiamo il comando, otteniamo l'output 'CREATE PROCEDURE' che conferma che siamo riusciti a creare con successo la procedura memorizzata.
Il compito successivo è chiamare la procedura e fornirle l'argomento previsto.
CALL nome_procedura(argomenti);Otterrai l'output CALL che mostra che la procedura memorizzata è stata eseguita e stiamo ottenendo l'output previsto che, in questo caso, è il quadrato dell'argomento che abbiamo aggiunto.
Esempio 2: una procedura memorizzata per inserire i valori in una voce di tabella
I due esempi seguenti mostrano come creare una procedura memorizzata che funzioni con una tabella di database. Creiamo rapidamente la tabella “studenti” con cui lavoreremo.
Per questo esempio creiamo una procedura memorizzata che consente all'utente di inserire i valori nella tabella appena creata. Nota come specifichiamo i parametri che prevediamo vengano aggiunti come argomenti quando chiamiamo la procedura memorizzata. Inoltre, definiamo la logica che accetta gli argomenti aggiunti ed esegue un'istruzione INSERT SQL sulla tabella 'studenti'.
Possiamo verificare le procedure memorizzate disponibili eseguendo il seguente comando:
\dfLa prima procedura memorizzata che possiamo vedere dal seguente output è la “add_student” che abbiamo creato in precedenza.
Ora chiamiamo la procedura memorizzata per eseguirla. L'immagine seguente mostra come abbiamo una tabella vuota, ma abbiamo chiamato la procedura memorizzata per aggiungere il primo studente:
Se elenchiamo i valori nella nostra tabella, notiamo come gli argomenti che abbiamo aggiunto con il comando di chiamata della procedura siano i valori per il nostro primo studente nella nostra tabella. È così che crei una procedura memorizzata per inserire i valori in una tabella.
Tieni presente che durante la creazione della procedura memorizzata, i parametri specificati devono corrispondere a quanto previsto nella tabella per evitare errori. Inoltre, il tipo di dati deve corrispondere.
Esempio 3: una procedura memorizzata per aggiornare una voce di tabella
Andando avanti, creiamo un'altra procedura memorizzata che aggiorna una voce della tabella. Se desideri avere un modo rapido per aggiornare i valori nella nostra tabella, puoi creare una procedura memorizzata di aggiornamento come segue:
Specifica quale colonna desideri aggiornare utilizzando la parola chiave WHERE e il nuovo valore utilizzando la parola chiave SET. È quindi necessario aggiungere la parola chiave COMMIT per rendere persistenti le modifiche.
Chiamiamo la procedura memorizzata di aggiornamento e aggiungiamo gli argomenti previsti: 'student_id' e il nuovo corso.
Se elenchiamo le voci nella nostra tabella, possiamo verificare di avere il corso aggiornato per lo studente specifico che abbiamo preso di mira. Ecco come funziona una procedura memorizzata di aggiornamento.
Conclusione
Puoi creare qualsiasi procedura memorizzata in PostgreSQL. Devi solo comprendere la sintassi da seguire e quindi definire la logica per la procedura memorizzata. Da lì, chiama la procedura memorizzata e verifica che sia stata eseguita come previsto. Questo post spiega le procedure memorizzate in PostgreSQL e fornisce esempi su come crearle.