Lagrede procedurer hjælper med databaseoptimering og med at forbedre genbrugbarheden. I stedet for at skulle udføre den samme forespørgsel, kan du oprette opgaven som en lagret procedure, som du vil kalde, når det kræves. Du vil lære alt om lagrede procedurer i slutningen af dette indlæg.
Arbejde med lagrede procedurer i PostgreSQL
Som PostgreSQL-bruger har du måske bemærket, at PostgreSQL-funktioner ikke udfører transaktioner. Selvom det er muligt at oprette en transaktion, er det ikke muligt at forpligte den eller rulle den tilbage til den tidligere tilstand. Disse begrænsninger omgås dog ved hjælp af lagrede procedurer.
Her er den grundlæggende syntaks til at oprette en lagret procedure i PostgreSQL:
OPRET ELLER ERSTAT PROCEDURE procedurenavn(
parameter[s] data_type
)
SPROG plpsql;
SOM $$
ERKLÆRE
variables_if_any data_type
BEGYNDE
logik
ENDE;
$$
De vigtigste ting at bemærke fra den givne syntaks er 'procedurenavn', som er det navn, du vil bruge til den lagrede procedure, de parametre, du ønsker at inkludere og deres datatyper, og logikken, som hovedsageligt er SQL-sætningerne.
Lad os give tre eksempler for at hjælpe dig med at forstå, hvordan du opretter de lagrede procedurer i PostgreSQL.
Eksempel 1: En lagret procedure til at beregne kvadratet af et tal
For vores første eksempel opretter vi en lagret procedure, der bruger 'RAISE NOTICE'-sætningen som en måde at udskrive output til terminalen på. Den lagrede procedure tager den heltalsværdi, du giver den, når du kalder den, og beregner dens kvadrat.
Sådan opretter vi den lagrede procedure:
Vi navngiver vores parameter som 'num1', og det er et heltal. For den logiske del definerer vi, hvordan den får kvadratet af 'num1' og gemmer det som kvadratvariabelen. Når vi udfører kommandoen, får vi outputtet 'CREATE PROCEDURE', som bekræfter, at det lykkedes os at oprette den lagrede procedure.
Den næste opgave er at kalde proceduren og give den det forventede argument.
CALL procedure_name(argumenter);Du vil få CALL-outputtet, der viser, at den lagrede procedure er blevet udført, og vi får det forventede output, som i dette tilfælde er kvadratet af argumentet, som vi tilføjede.
Eksempel 2: En lagret procedure til at indsætte værdierne i en tabelpost
De følgende to eksempler viser, hvordan man opretter en lagret procedure, der fungerer med en databasetabel. Lad os hurtigt oprette 'studerende'-bordet, som vi vil arbejde med.
Til dette eksempel opretter vi en lagret procedure, der tillader en bruger at indsætte værdierne i den nyoprettede tabel. Bemærk, hvordan vi angiver de parametre, som vi forventer at blive tilføjet som argumenter, når vi kalder den lagrede procedure. Desuden definerer vi logikken, der tager de tilføjede argumenter og udfører en INSERT SQL-sætning til 'studerende'-tabellen.
Vi kan kontrollere de tilgængelige lagrede procedurer ved at køre følgende kommando:
\dfDen første lagrede procedure, som vi kan se fra følgende output, er 'add_student', som vi tidligere har oprettet.
Lad os nu kalde den lagrede procedure for at udføre den. Følgende billede viser, hvordan vi har en tom tabel, men vi kaldte den lagrede procedure for at tilføje den første elev:
Hvis vi angiver værdierne i vores tabel, så læg mærke til, hvordan de argumenter, som vi tilføjede med kommandoen opkaldsprocedure, er værdierne for vores første elev i vores tabel. Det er sådan, du opretter en lagret procedure for at indsætte værdierne i en tabel.
Bemærk, at når du opretter den lagrede procedure, skal de parametre, du angiver, matche, hvad der forventes i din tabel for at undgå fejl. Desuden skal datatypen matche.
Eksempel 3: En lagret procedure til opdatering af en tabelpost
Fortsæt, lad os oprette en anden lagret procedure, der opdaterer en tabelpost. Hvis du vil have en hurtig måde at opdatere værdierne i vores tabel på, kan du oprette en lagret opdateringsprocedure som følger:
Angiv, hvilken kolonne du vil opdatere ved hjælp af nøgleordet WHERE og den nye værdi ved hjælp af nøgleordet SET. Du skal derefter tilføje nøgleordet COMMIT for at fortsætte ændringerne.
Lad os kalde opdateringens lagrede procedure og tilføje de forventede argumenter: 'student_id' og det nye kursus.
Hvis vi angiver posterne i vores tabel, kan vi bekræfte, at vi har det opdaterede kursus for den pågældende studerende, som vi målrettede mod. Det er sådan en opdatering lagret procedure fungerer.
Konklusion
Du kan oprette enhver lagret procedure i PostgreSQL. Du behøver kun at forstå den syntaks, der skal følges, og derefter definere din logik for den lagrede procedure. Derfra skal du kalde den lagrede procedure og kontrollere, at den er udført som forventet. Dette indlæg forklarede om de lagrede procedurer i PostgreSQL og gav eksempler på, hvordan man opretter dem.