Lagrede prosedyrer hjelper med databaseoptimalisering og forbedrer gjenbrukbarhet. I stedet for å måtte utføre den samme spørringen, kan du opprette oppgaven som en lagret prosedyre som du vil ringe når det er nødvendig. Du vil lære alt om lagrede prosedyrer mot slutten av dette innlegget.
Arbeide med lagrede prosedyrer i PostgreSQL
Som PostgreSQL-bruker har du kanskje lagt merke til at PostgreSQL-funksjoner ikke utfører transaksjoner. Selv om det er mulig å opprette en transaksjon, er det ikke mulig å forplikte den eller rulle den tilbake til forrige tilstand. Imidlertid omgås disse begrensningene ved å bruke lagrede prosedyrer.
Her er den grunnleggende syntaksen for å lage en lagret prosedyre i PostgreSQL:
OPPRETT ELLER ERSTAT PROSEDYRE prosedyrenavn(
parameter[s] data_type
)
SPRÅK plpsql;
SOM $$
ERKLÆRE
variables_if_any data_type
BEGYNNE
logikk
SLUTT;
$$
De viktigste tingene å merke seg fra den gitte syntaksen er 'prosedyrenavn' som er navnet du vil bruke for den lagrede prosedyren, parametrene du ønsker å inkludere og deres datatyper, og logikken som hovedsakelig er SQL-setningene.
La oss gi tre eksempler for å hjelpe deg å forstå hvordan du lager de lagrede prosedyrene i PostgreSQL.
Eksempel 1: En lagret prosedyre for å beregne kvadratet til et tall
For vårt første eksempel lager vi en lagret prosedyre som bruker 'RAISE NOTICE'-setningen som en måte å skrive ut utdataene til terminalen på. Den lagrede prosedyren tar heltallsverdien du gir den når du kaller den og beregner kvadratet.
Slik lager vi den lagrede prosedyren:
Vi kaller parameteren vår som 'num1', og det er et heltall. For den logiske delen definerer vi hvordan den får kvadratet av 'num1' og lagrer den som kvadratvariabelen. Når vi utfører kommandoen, får vi 'CREATE PROCEDURE'-utgangen som bekrefter at vi klarte å lage den lagrede prosedyren.
Den neste oppgaven er å kalle prosedyren og gi den det forventede argumentet.
CALL prosedyrenavn(argumenter);Du vil få CALL-utgangen som viser at den lagrede prosedyren er utført, og vi får den forventede utgangen som i dette tilfellet er kvadratet på argumentet vi la til.
Eksempel 2: En lagret prosedyre for å sette inn verdiene i en tabelloppføring
De følgende to eksemplene viser hvordan du oppretter en lagret prosedyre som fungerer med en databasetabell. La oss raskt lage «studenter»-tabellen som vi skal jobbe med.
For dette eksemplet lager vi en lagret prosedyre som lar en bruker sette inn verdiene i den nyopprettede tabellen. Legg merke til hvordan vi spesifiserer parameterne som vi forventer skal legges til som argumenter når vi kaller den lagrede prosedyren. Dessuten definerer vi logikken som tar de tilføyde argumentene og utfører en INSERT SQL-setning til 'studenter'-tabellen.
Vi kan sjekke de tilgjengelige lagrede prosedyrene ved å kjøre følgende kommando:
\dfDen første lagrede prosedyren som vi kan se fra følgende utdata er 'add_student' som vi tidligere opprettet.
La oss nå kalle den lagrede prosedyren for å utføre den. Følgende bilde viser hvordan vi har en tom tabell, men vi kalte den lagrede prosedyren for å legge til den første studenten:
Hvis vi viser verdiene i tabellen vår, legg merke til hvordan argumentene som vi la til med kommandoen call prosedyre er verdiene for vår første elev i tabellen vår. Det er hvordan du oppretter en lagret prosedyre for å sette inn verdiene i en tabell.
Merk at når du oppretter den lagrede prosedyren, må parameterne du spesifiserer samsvare med det som forventes i tabellen for å unngå feil. Dessuten må datatypen samsvare.
Eksempel 3: En lagret prosedyre for å oppdatere en tabelloppføring
Gå videre, la oss lage en annen lagret prosedyre som oppdaterer en tabelloppføring. Hvis du vil ha en rask måte å oppdatere verdiene i tabellen vår på, kan du opprette en lagret oppdateringsprosedyre som følger:
Spesifiser hvilken kolonne du vil oppdatere ved hjelp av nøkkelordet WHERE og den nye verdien ved å bruke nøkkelordet SET. Du må deretter legge til nøkkelordet COMMIT for å fortsette endringene.
La oss kalle den lagrede oppdateringsprosedyren og legge til de forventede argumentene: 'student_id' og det nye kurset.
Hvis vi viser oppføringene i tabellen vår, kan vi bekrefte at vi har det oppdaterte kurset for den aktuelle studenten vi målrettet mot. Det er slik en oppdatering lagret prosedyre fungerer.
Konklusjon
Du kan opprette hvilken som helst lagret prosedyre i PostgreSQL. Du trenger bare å forstå syntaksen som skal følges og deretter definere logikken din for den lagrede prosedyren. Derfra ringer du opp den lagrede prosedyren og kontrollerer at den har utført som forventet. Dette innlegget forklarte de lagrede prosedyrene i PostgreSQL og ga eksempler på hvordan du oppretter dem.