Pekere

Direkte markørstøtte er nytt I pl / pgSQL versjon 7.2. Behandling av et resultatsett ved hjelp av en markør ligner på behandling av et resultatsett ved hjelp AV EN FOR loop, men markører tilbyr noen få klare fordeler som du vil se om et øyeblikk.

du kan tenke på en markør som et navn for et resultatsett. Du må deklarere en markørvariabel akkurat som du deklarerer en annen variabel. Følgende kodebit viser hvordan du kan deklarere en markørvariabel:

...DECLARE rental_cursor CURSOR FOR SELECT * FROM rentals;...

rental_cursor er erklært å være en markør for resultatsettet AV spørringen VELG * fra utleie. Når DU erklærer en variabel AV TYPEN MARKØR, må du ta med en spørring. Markørvariabelen sies å være bundet til denne spørringen, og variabelen er en bundet markørvariabel.

før du kan bruke en bundet markør, må du åpne markøren VED HJELP AV OPEN-setningen:

...DECLARE rental_cursor CURSOR FOR SELECT * FROM rentals;BEGIN OPEN rental_cursor;...

hvis du prøver Å ÅPNE en markør som allerede er åpen, får du en feilmelding (markøren «navn» allerede i bruk). Hvis DU prøver Å HENTE (se delen som følger) fra en markør som ikke er åpnet, får du en feilmelding(markøren «navn» er ugyldig). NÅR du bruker en markør, ERKLÆRER du den først, ÅPNER den, HENTER fra den og TIL SLUTT LUKKER den i den rekkefølgen. Du kan gjenta ÅPNE, HENTE, LUKKE syklusen hvis du vil behandle markørresultatene igjen.

HENT

etter at en bundet markør er åpnet, kan du hente resultatsettet (en rad om gangen) ved HJELP AV HENTESETNINGEN. Når du henter en rad fra en markør, må du gi en eller flere målvariabler SOM PL/pgSQL kan tømme resultatene inn i. Syntaksen FOR HENT-setningen er

FETCH cursor-name INTO destination ];

målet (eller målene) må samsvare med formen på en rad som returneres av markøren. Hvis markøren For eksempel Velger en rad fra leiebordet, er det tre mulige mål:

  • en variabel av typen utleie%ROWTYPE

  • Tre variabler: en av type utleie.tape_id%TYPE, en av type utleie.customer_id % TYPE, og den siste typen utleie.rental_date % TYPE

  • en variabel av typen POST

La oss se nærmere på hver av disse destinasjonstypene.

NÅR DU HENTER inn en variabel med noen % ROWTYPE, kan du referere til de enkelte kolonnene ved hjelp av den vanlige variabelen.kolonne notasjon. For eksempel:

Neste KAN JEG HENTE inn en kommaseparert liste over variabler. I det forrige eksemplet returnerer rental_cursor-markøren rader som hver inneholder tre kolonner. I stedet for å hente inn en % ROWTYPE-variabel, kan jeg deklarere tre separate variabler (av de aktuelle typene) og HENTE inn i dem i stedet:

Du er ikke pålagt å bruke variabler deklarert med %TYPE, Men dette er det perfekte stedet å gjøre det. Når du oppretter en funksjon, vet du vanligvis hvilke kolonner du vil være interessert i, og deklarere variabler med %TYPE vil gjøre funksjonene dine mye mindre skjøre i tilfeller der de refererte kolonnetypene kan endres.

du kan ikke kombinere sammensatte variabler og skalarvariabler i SAMME HENT-setning:

Dette virker som en feil for meg. Du kan kanskje kombinere kompositt-og skalarvariabler i en fremtidig utgave.

den tredje typen mål som du kan bruke MED EN HENT-setning, er en variabel med typepost. Du husker kanskje fra tidligere i dette kapitlet at EN REKORDVARIABEL er noe av en kameleon?den tilpasser seg hva slags data du legger inn i den. Følgende kodebit bruker for eksempel den samme POSTVARIABELEN til å holde to rader med forskjellig form:

etter at du har utført EN HENTESETNING, hvordan vet du om en rad faktisk ble hentet? HVIS DU HENTER etter å ha hentet hele resultatet, oppstår ingen feil. I stedet har hver pl / pgSQL-funksjon en automatisk deklarert variabel som heter FUNNET. FUNNET ER EN BOOLSK variabel som er satt AV PL / pgSQL tolk for å indikere ulike typer tilstandsinformasjon. Tabell 7.1 viser tidspunktene DER PL / pgSQL setter den FUNNET variabelen og de tilsvarende verdiene.

Tabell 7.1. Funnet Hendelser Og Verdier

Event

Verdi

Start av hver funksjon

FALSK

Start av et heltall – for loop

FALSK

Innenfor et heltall-for loop

SANT

Start av A FOR…VELG sløyfe

FALSK

Innenfor EN FOR…VELG sløyfe

SANT

FØR DU VELGER inn i setningen

FALSK

ETTER VELG i setningen

TRUE (HVIS rader returneres)

FØR HENTE uttalelse

FALSK

ETTER HENTE uttalelse

TRUE (HVIS en rad returneres)

Så, du kan se AT FUNNET er satt TIL TRUE hvis EN HENTESETNING returnerer en rad. La oss se hvordan du legger alle markørrelaterte setningene sammen i en enkelt pl / pgSQL-funksjon:

DET første du gjør I denne kodebiten, ER Å ÅPNE markøren. Deretter skriver DU INN EN LØKKE som vil behandle hver rad som returneres fra markøren. Inne I SLØYFEN HENTER DU en enkelt post, AVSLUTTER sløyfen hvis markøren er oppbrukt, og kaller en annen funksjon (process_rental())hvis ikke. Når sløyfen avsluttes, lukker du markøren VED HJELP AV CLOSE-setningen.

Så langt ser det ut til at en markørsløyfe er ganske mye det samme som EN FOR-I-select-sløyfe. Hva mer kan du gjøre med en markør?

Parameteriserte Markører

Du har sett at du må oppgi EN SELECT-setning når DU erklærer EN MARKØR. Ofte vil du oppdage at du ikke vet de nøyaktige verdiene som er involvert i spørringen når du skriver en funksjon. Du kan deklarere en parameterisert markør for å løse dette problemet.

en parameterisert markør er lik i konsept til en parameterisert funksjon. Når du definerer en funksjon, kan du deklarere et sett med parametere (disse kalles formelle parametere eller formelle argumenter); disse parametrene kan brukes i funksjonen for å endre resultatene av funksjonen. Hvis du definerer en funksjon uten parametere, returnerer funksjonen alltid de samme resultatene (med mindre det påvirkes av globale, eksterne data). Hvert språk pålegger begrensninger på hvor du kan bruke en parameter i en funksjon. Generelt kan funksjonsparametere brukes hvor som helst et verdiskapende uttrykk kan brukes. Når du ringer til en parameterisert funksjon, gir du en verdi for hver parameter: Verdiene du oppgir (disse kalles de faktiske parametrene, eller faktiske argumenter), erstattes inne i funksjonen der de formelle parameterne vises.

når du definerer en markør, kan du deklarere et sett med formelle parametere; disse parametrene kan brukes med markøren for å endre resultatsettet for spørringen. Hvis du definerer en markør uten parametere, returnerer spørringen alltid det samme resultatsettet, med mindre det påvirkes av eksterne data. PL / pgSQL begrenser stedene du kan bruke en parameter i en markørdefinisjon. En markørparameter kan brukes hvor som helst et verdiskapende uttrykk kan brukes. Når du åpner en markør, må du angi verdier for hver formell parameter. De faktiske parametrene er erstattet inne i markøren der de formelle parametrene vises.

la oss se på et eksempel:

Linjene 3, 4 og 5 erklærer en parameterisert markør. Denne markøren har en enkelt formell parameter; ET HELTALL kalt ID. Legg merke til (på slutten av linje 5), at jeg har brukt den formelle parameteren i markørdefinisjonen. Når jeg åpner denne markøren, gir jeg EN HELTALLSVERDI FOR ID-parameteren. Den faktiske parameteren som jeg gir vil bli erstattet i spørringen hvor den formelle parameteren brukes. Så, hvis target_customer er lik, si 42, vil markøren åpnet på linje 10 lese:

SELECT * FROM customers WHERE customer_id = 42;

den fulle syntaksen for en markørdeklarasjon er

variable-name CURSOR ) ] FOR select-query;

den fulle syntaksen for EN ÅPEN setning er

OPEN cursor-name ) ];

du vil parameterisere en markør av samme grunner som du vil parameterisere en funksjon: du vil at resultatene skal avhenge av de faktiske argumentene. Når du parameteriserer en markør, gjør du også markøren mer gjenbrukbar. For eksempel vil jeg kanskje behandle alle båndene i lageret mitt, men jeg vil behandle båndene en distributør om gangen. Hvis jeg ikke bruker en parameterisert markør, må jeg erklære en markør for hver av mine distributører(og jeg må vite settet av distributører når jeg skriver funksjonen). Ved hjelp av en parameterisert markør kan jeg erklære markøren en gang og gi forskjellige faktiske argumenter hver gang jeg åpner markøren:

Legg Merke til at du KAN ÅPNE og LUKKE en markør så ofte du vil. En markør må være lukket før den kan åpnes. Hver gang du åpner en parameterisert markør, kan du gi nye faktiske parametere.

Markørreferanser

la Oss nå være oppmerksom på et annet aspekt av markørstøtte I PL / pgSQL?markor referanser.

når du erklærer EN MARKØRVARIABEL, gir DU EN SELECT-setning som er bundet til markøren. Du kan ikke endre teksten i spørringen etter at markøren er erklært. Selvfølgelig kan du parameterisere spørringen for å endre resultatene, men formen på spørringen forblir den samme: Hvis spørringen returnerer rader fra tapes-tabellen, vil den alltid returnere rader fra tapes-tabellen.

I Stedet for å erklære EN MARKØR, kan du erklære en variabel FOR å VÆRE AV TYPEN REFCURSOR. EN REFCURSOR er egentlig ikke en markør, men en referanse til en markør. Syntaksen for å erklære EN REFCURSOR er

DECLARE ref-name REFCURSOR; ...

Legg Merke til at du ikke angir en spørring når du oppretter EN REFCURSOR. I stedet er en markør bundet til EN REFCURSOR under kjøring. Her er et enkelt eksempel:

i denne blokken har jeg erklært to markører og en markørreferanse. En av pekerne returnerer rader fra leiebordet, og den andre returnerer rader fra tapesbordet.

på linje 9 åpnes next_rentalmarkøren. På linje 10 gir jeg en verdi til next_row markørreferansen. Vi har nå to måter å få tilgang til next_rental-markøren: gjennom next_rental-markørvariabelen og gjennom next_row-markørreferansen. På dette punktet refererer next_row til next_rental-markøren. Du kan se (på linjene 11 og 12) at du KAN HENTE en rad ved hjelp av en variabel. Begge HENTEUTTALELSENE returnerer rad fra leiebordet.

på linje 14 peker next_row-markøren til en annen markør. NÅ, NÅR DU HENTER fra next_row, får du en rad fra kassettbordet. Legg merke til at du kan peke next_row til en markør som ennå ikke er åpnet. DU KAN LUKKE en markør ved hjelp av en markørreferanse, men DU KAN IKKE ÅPNE en markør ved hjelp av en markørreferanse.

Faktisk kan du åpne en markør ved hjelp AV EN REFCURSOR; du kan bare ikke åpne en navngitt markør. Når du erklærer EN MARKØRVARIABEL, oppretter du virkelig En PostgreSQL-markør hvis navn er det samme som navnet på variabelen. I det forrige eksemplet opprettet du en markør (ikke bare en markørvariabel) kalt next_rental og en markør kalt next_tape. PL / pgSQL lar deg lage anonyme pekere ved HJELP AV REFCURSOR variabler. En anonym markør er en markør som ikke har et navn. Du oppretter en anonym markør ved HJELP AV OPEN-setningen, EN REFCURSOR og EN SELECT-setning:

en anonym markør har faktisk et navn, Men PostgreSQL konstruerer navnet, og det er ikke veldig leservennlig. En anonym markør har et navn som <ikke navngitt markør 42>.

1 ...2 DECLARE3 next_row REFCURSOR;4 BEGIN5 OPEN next_row FOR SELECT * FROM customers;6 ...

på linje 5 oppretter du en anonym markør og binder den til next_row markørreferansen. Etter at en anonym markør er åpnet, kan du behandle den som en hvilken som helst annen markør. DU KAN HENTE fra den, LUKKE den og miste den. Den siste delen kan høres litt fishy, så la meg forklare nærmere. Ta en nærmere titt på følgende kodefragment:

denne funksjonen inneholder to løkker: en ytre sløyfe som leser gjennom kundetabellen og en indre sløyfe som leser hver utleie for en gitt kunde. Next_customer-markøren åpnes (på linje 10) før den ytre sløyfen begynner. Next_rentalmarkøren er bundet og åpnet (på linjene 15, 16 og 17) like før den indre sløyfen begynner. Etter at den indre sløyfen er fullført, setter jeg next_rental markørreferansen TIL NULL og fortsetter med den ytre sløyfen. Hva skjer med markøren som var bundet til next_rental? Jeg lukket ikke eksplisitt markøren, så den må forbli åpen. Etter å ha utført oppdragserklæringen på linje 29, har jeg ingen måte å få tilgang til markøren igjen??husk at det er en anonym markør, så jeg kan ikke referere til den ved navn. Denne situasjonen kalles en ressurslekkasje. En ressurslekkasje oppstår når du oppretter et objekt (i dette tilfellet en markør), og deretter mister du alle referanser til objektet. Hvis du ikke finner objektet igjen, kan du ikke frigjøre ressursen. Unngå ressurslekkasjer; de er ekkel og kan forårsake ytelsesproblemer. Ressurslekkasjer vil også føre til at koden mislykkes hvis du går tom for en ressurs (for eksempel minneplass). Vi kan unngå ressurslekkasjen som vises i dette eksemplet ved å lukke next_rental før du setter DEN TIL NULL.

Du har sett hva du ikke skal gjøre med en markørreferanse, men la oss se hvilke markørreferanser som er veldig gode for. Det fine med en markørreferanse er at du kan sende referansen til en annen funksjon, eller du kan returnere en referanse til den som ringer. Dette er kraftige funksjoner. Ved å dele markøren referanser mellom funksjoner, kan du faktor PL / pgSQL kode i gjenbrukbare stykker.

En av de mer effektive måtene å bruke markørreferanser er å skille koden som behandler en markør fra koden som lager markøren. Du kan for eksempel oppleve at vi trenger en funksjon for å beregne det totale beløpet vi har mottatt fra en gitt kunde over en gitt tidsperiode. Jeg kan begynne med å lage en enkelt funksjon som konstruerer en markør og behandler hver rad i den markøren:

Dette er en god start, Men det fungerer bare for et enkelt sett med forhold: en gitt kunde og et gitt par datoer. I stedet kan du faktor denne funksjonen i tre separate funksjoner.

den første funksjonen oppretter en markør som, når den åpnes, vil returnere alle leieposter for en gitt kunde innen en gitt periode; markøren returneres til den som ringer:

den andre funksjonen, gitt en markør som returnerer leieposter, beregner den totale verdien av leieposter som er tilgjengelig gjennom den markøren:

den siste funksjonen påkaller de to første:

fordelen med denne tilnærmingen er at du kan konstruere en markør ved hjelp av forskjellige utvalgskriterier og ring compute_total_value(). Du kan for eksempel beregne de totale verdiene for alle utleie av en gitt tape:

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert.