Opgeslagen procedures helpen bij het optimaliseren van de database en bij het vergroten van de herbruikbaarheid. In plaats van dezelfde query uit te moeten voeren, kunt u de taak maken als een opgeslagen procedure die u kunt oproepen wanneer dat nodig is. Aan het einde van dit bericht leert u alles over opgeslagen procedures.
Werken met opgeslagen procedures in PostgreSQL
Als PostgreSQL-gebruiker is het je misschien opgevallen dat PostgreSQL-functies geen transacties uitvoeren. Hoewel het mogelijk is om een transactie te creëren, is het niet mogelijk om deze vast te leggen of terug te zetten naar de vorige staat. Deze beperkingen worden echter omzeild met behulp van opgeslagen procedures.
Hier is de basissyntaxis om een opgeslagen procedure in PostgreSQL te maken:
PROCEDURE MAKEN OF VERVANGEN procedurenaam(
parameter[s] gegevenstype
)
TAAL plpsql;
ALS $$
VERKLAREN
variabelen_als_een gegevenstype
BEGINNEN
logica
EINDE;
$$
De belangrijkste dingen om op te merken uit de gegeven syntaxis zijn de “procedurenaam”, de naam die u gaat gebruiken voor de opgeslagen procedure, de parameters die u wilt opnemen en hun gegevenstypen, en de logica die voornamelijk uit de SQL-instructies bestaat.
Laten we drie voorbeelden geven om u te helpen begrijpen hoe u de opgeslagen procedures in PostgreSQL kunt maken.
Voorbeeld 1: Een opgeslagen procedure om het kwadraat van een getal te berekenen
Voor ons eerste voorbeeld maken we een opgeslagen procedure die de instructie “RAISE NOTICE” gebruikt als een manier om de uitvoer naar de terminal af te drukken. De opgeslagen procedure neemt de gehele waarde die u eraan geeft bij het aanroepen ervan en berekent het kwadraat ervan.
Zo maken we de opgeslagen procedure:
We noemen onze parameter 'num1' en het is een geheel getal. Voor het logische gedeelte definiëren we hoe het het kwadraat van “num1” krijgt en slaat dit op als de vierkante variabele. Wanneer we de opdracht uitvoeren, krijgen we de uitvoer 'CREATE PROCEDURE', die bevestigt dat we erin zijn geslaagd de opgeslagen procedure met succes te maken.
De volgende taak is om de procedure aan te roepen en deze het verwachte argument te geven.
CALL procedurenaam(argumenten);U krijgt de CALL-uitvoer die aangeeft dat de opgeslagen procedure is uitgevoerd, en we krijgen de verwachte uitvoer die in dit geval het kwadraat is van het argument dat we hebben toegevoegd.
Voorbeeld 2: Een opgeslagen procedure om de waarden in een tabelinvoer in te voegen
De volgende twee voorbeelden laten zien hoe u een opgeslagen procedure maakt die werkt met een databasetabel. Laten we snel de tabel ‘studenten’ maken waarmee we gaan werken.
Voor dit voorbeeld maken we een opgeslagen procedure waarmee een gebruiker de waarden in de nieuw gemaakte tabel kan invoegen. Merk op hoe we de parameters specificeren waarvan we verwachten dat ze als argumenten worden toegevoegd wanneer we de opgeslagen procedure aanroepen. Bovendien definiëren we de logica die de toegevoegde argumenten gebruikt en een INSERT SQL-instructie uitvoert naar de tabel 'studenten'.
We kunnen de beschikbare opgeslagen procedures controleren door de volgende opdracht uit te voeren:
\dfDe eerste opgeslagen procedure die we uit de volgende uitvoer kunnen zien, is de “add_student” die we eerder hebben gemaakt.
Laten we nu de opgeslagen procedure oproepen om deze uit te voeren. De volgende afbeelding laat zien hoe we een lege tabel hebben, maar we hebben de opgeslagen procedure aangeroepen om de eerste leerling toe te voegen:
Als we de waarden in onze tabel opsommen, zie dan hoe de argumenten die we hebben toegevoegd met het commando call procedure de waarden zijn voor onze eerste leerling in onze tabel. Zo maakt u een opgeslagen procedure om de waarden in een tabel in te voegen.
Houd er rekening mee dat bij het maken van de opgeslagen procedure de parameters die u opgeeft, moeten overeenkomen met wat er in uw tabel wordt verwacht om fouten te voorkomen. Bovendien moet het gegevenstype overeenkomen.
Voorbeeld 3: Een opgeslagen procedure om een tabelinvoer bij te werken
Laten we verder gaan en nog een opgeslagen procedure maken die een tabelinvoer bijwerkt. Als u de waarden in onze tabel snel wilt bijwerken, kunt u als volgt een opgeslagen updateprocedure maken:
Geef op welke kolom u wilt bijwerken met het sleutelwoord WHERE en de nieuwe waarde met het sleutelwoord SET. Vervolgens moet u het trefwoord COMMIT toevoegen om de wijzigingen te behouden.
Laten we de opgeslagen updateprocedure aanroepen en de verwachte argumenten toevoegen: “student_id” en de nieuwe cursus.
Als we de vermeldingen in onze tabel vermelden, kunnen we verifiëren dat we de bijgewerkte cursus hebben voor de specifieke student waarop we ons richten. Dat is hoe een opgeslagen procedure voor updates werkt.
Conclusie
U kunt elke opgeslagen procedure in PostgreSQL maken. U hoeft alleen de te volgen syntaxis te begrijpen en vervolgens uw logica voor de opgeslagen procedure te definiëren. Roep vanaf daar de opgeslagen procedure aan en controleer of deze is uitgevoerd zoals verwacht. In dit bericht werd uitleg gegeven over de opgeslagen procedures in PostgreSQL en werden voorbeelden gegeven van hoe u deze kunt maken.