SQL lagrede prosedyrer: opprette og bruk

SQL-lagrede prosedyrer er kjørbar programvare modul som kan lagres i databasen i form av ulike objekter. Med andre ord, det er et objekt som inneholder SQL-setning. Disse lagrede prosedyrer kan utføres i klientprogrammet for å få et godt resultat. I tillegg er slike anlegg ofte kalles fra andre scripts eller fra noen annen del.

introduksjon

Mange tror at de ligner på prosedyrene i de ulike høyt nivå programmeringsspråk (henholdsvis, med unntak av MS SQL). Kanskje dette er sant. De har lignende parametre, de kan utstede en tilsvarende verdi. Videre, i noen tilfeller, de kommer i kontakt med. For eksempel, er de sammen med databaser av DDL og DML data, så vel som brukerfunksjoner (kodenavn - UDF).

Egentlig lagrede SQL-prosedyrer har en rekke fordeler som skiller dem blant slike prosesser. Sikkerhet, variabilitet programmering produktiviteten - alt dette tiltrekker seg brukere som jobber med databaser, mer og mer. Toppen av popularitet kom prosedyrer for årene 2005-2010, da jeg fikk programmet fra "Microsoft" under navnet «SQL Server Management Studio». Med sin hjelp, har arbeidet med databaser blitt mye enklere, mer praktisk og mer praktisk. År etter år, denne metoden for overføring av informasjon vunnet popularitet i programmeringsmiljøet. I dag, MS SQL Server er helt vanlig program, som for brukerne å "kommunisere" med databaser, sto på linje med "Excel".

Når du ringer prosedyren, er det umiddelbart behandlet av serveren uten unødvendige prosesser og brukermedvirkning. Du kan deretter utføre noen handling med informasjon: sletting, utførelse, endring. Over alt dette er i DDL-operatør, som egenhendig utføre komplekse handlinger på disse stedene. Og alt skjer veldig raskt, og serveren faktisk ikke lastes. Denne hastigheten og produktivitet kan du raskt overføre store mengder informasjon fra brukeren til serveren, og vice versa.

For å gjennomføre dette arbeidet med informasjon, er det flere programmeringsspråk teknologier. Dette inkluderer for eksempel PL / SQL fra database management system Oracle, psql i Interbase og Firebird systemer, så vel som den klassiske "maykrosoftovskih» Transact-SQL. Alle av dem er utformet for å opprette og kjøre lagrede prosedyrer, slik at du kan bruke sine egne algoritmer på en stor database handlers. Det er nødvendig og for å sikre at de som utfører behandling av slik informasjon, kan beskytte alle objekter fra uautoriserte tredjeparter, og derfor etablering, endring eller sletting av visse data.

produktivitet

Disse databaseobjekter kan programmeres på forskjellige måter. Dette tillater brukere å velge type prosess som ville være mest hensiktsmessig, noe som sparer tid og energi. I tillegg er selve inngrepet behandlet, for derved å unngå stor tid brukt på kommunikasjonen mellom tjeneren og brukeren. Modulen kan omprogrammeres og endres i riktig retning i absolutt enhver tid. Spesielt verdt å merke seg den hastigheten som lanseringen av SQL lagret prosedyre skjer: prosessen er raskere andre som ligner på det, noe som er praktisk og allsidig.

sikkerhet

Denne type av informasjonsbehandling skiller seg fra lignende prosesser, ved at det gir forbedret sikkerhet. Dette sikres ved det faktum at andre brukere får tilgang til fremgangsmåter kan elimineres helt. Dette vil gi administrator for å utføre operasjoner med dem hver for seg, uten frykt for avlytting eller uautorisert tilgang til databasen.

dataoverføring

Kommunikasjon mellom en lagret SQL prosedyre og klientapplikasjonen er å bruke parameterne og returnerer verdier. Sistnevnte er ikke nødvendig å sende dataene til et lagret prosedyre, men informasjonen (hovedsakelig på brukerens forespørsel), og bearbeidet for SQL. Etterat de lagrede prosedyren har avsluttet sitt arbeid, sender datapakkene tilbake (men igjen, hvis det er ønskelig) til den anropende applikasjonen, ved hjelp av en rekke metoder som kan implementeres som en samtale til den lagrede prosedyren blir SQL, og retur, for eksempel:

- dataoverføring via Utgangsparametertype;

- dataoverføring via retur setningen;

- Dataoverføring med prefiks.

Og nå se på hvordan denne prosessen ser det samme inne.

1. Lag EXEC-lagret i SQL-prosedyre

Du kan lage en prosedyre i MS SQL (Managment Studio). Etter inngrepet er opprettet, føres det opp på en programmerbar database node, i hvilket fremgangsmåten utføres av operatøren av oppretting. For å utføre SQL-lagrede prosedyrer ved hjelp EXEC-en prosess som inneholder objektnavnet.

Når du oppretter navnet kommer først prosedyren, og deretter gjort en eller flere parametere som er tildelt ham. Parametere kan være valgfritt. Etter at parameteren (e), det vil si legemet av prosedyren, som skal skrives, er det nødvendig å utføre noen nødvendige operasjoner.

Det faktum at kroppen kan ha lokale variabler, som ligger i det, og disse variablene er lokale, og i forhold til prosedyrene. Med andre ord, kan de anses kun innenfor kroppsbehandlinger Microsoft SQL Server. Lagrede prosedyrer i denne saken anses lokale.

Derfor, for å skape en prosess, trenger vi en fremgangsmåte navn og i det minste én parameter som en prosedyre legeme. Vær oppmerksom på at et utmerket alternativ i dette tilfellet er etablering og gjennomføring av prosedyrer med et skjemanavn i sorter.

Prosedyren legeme kan ha en hvilken som helst form for operatører SQL, for eksempel, for eksempel å lage et bord, å sette inn en eller flere rader i tabellen, etablere den database type og karakter, og så videre. Likevel begrenser inngrepet kroppen enkelte operasjoner i det. Noen av de viktigste begrensningene er listet nedenfor:

- kroppen ikke skaper noen annen lagret prosedyre;

- kroppen må ikke skape et falskt inntrykk om objektet;

- kroppen ikke skaper noen triggere.

2. Sett variable i kroppen av den prosedyre

Du kan lage variabler lokale til prosedyren av kroppen, og så vil de være innenfor en prosedyre kroppen. En god praksis er å skape en variabel ved begynnelsen av den lagrede prosedyren legeme. Men du kan også sette variabler hvor som helst i kroppen av objektet.

Noen ganger kan du legge merke til at noen variabler er satt i samme rad, og hver variabel atskilt med komma. Merk også at den variable er foran @. I kroppen av prosedyren, kan du sette en variabel som du vil. For eksempel, kan en variabel @ NAME1 bli annonsert mot slutten av prosedyren legeme. For å tilordne en verdi til en variabel deklarert ved hjelp av et sett av personopplysninger. I motsetning til situasjonen når mer enn én variabel deklarert i samme rad, bare ett sett av personopplysninger som brukes i denne situasjonen.

Brukerne ofte stiller spørsmålet: "Hvordan tildele flere verdier i en enkelt setning i kroppen av prosedyren 'Nå?. Interessant spørsmål, men dette er mye enklere enn du tror. Svaret: ved hjelp av slike par som «Velg Var = verdi". Man kan bruke disse par, adskilt av komma.

3. Lag en SQL lagret prosedyre

I en rekke eksempler på mennesker viser skape en enkel lagret prosedyre og utføre den. Imidlertid kan prosedyren ta slike parametre som kalle prosessen vil det ha en verdi nær det (men ikke alltid). Hvis de stemmer overens, starter deretter de tilsvarende prosesser i kroppen. For eksempel, hvis du lager en prosedyre som vil ta byen og regionen fra den som ringer og returnere data om hvor mye forfatterne refererer til tilsvarende byen og regionen. Prosedyren vil spørre en database tabell over forfattere, for eksempel, puber, for å utføre denne beregningen forfattere. For å oppnå disse databasene, for eksempel Google laster ned SQL-skript med SQL2005 side.

I forrige eksempel, tar prosedyren to parametre, som på engelsk ville bli kalt betinget @State og @City. Datatypen som svarer til den typen som er angitt i søknaden. Prosedyren legeme har innvendige variabler @TotalAuthors (alle forfattere), og denne variabel brukes til å vise deres antall. Deretter kommer en seksjon utvalg be om at alle punkter. Endelig er den beregnede verdien vises i output-vinduet med utskrifts uttalelse.

Hvordan du utfører en SQL lagret prosedyre

Det er to måter å utføre inngrepet. Den første måten er vist ved å føre parametre som en kommadelt liste utføres etter prosedyren navn. Anta at vi har to verdier (som i forrige eksempel). Disse verdier er samlet ved hjelp av variable og @State @City prosedyre. I denne fremgangsmåten viktige parametre til overførings orden. Denne metoden kalles overføringssekvensen av argumenter. I den andre metoden blir parametrene allerede er tildelt direkte, i hvilket tilfelle den rekkefølgen er ikke viktig. Denne andre metoden er kjent som overføring av navngitte argumenter.

Prosedyren kan avvike litt fra den typiske. Alle de samme, som i forrige eksempel, men parametrene er flyttet her bare. Det er @City parameter lagres først og @State lagret ved siden av standardverdien. Standardinnstillingen er vanligvis tildelt separat. SQL lagrede prosedyrer er som enkle parametre. I dette tilfellet, gitt alternativet "UT 'erstatter standardverdien" CA ". I en annen utførelsesform passerer bare ett argument verdi for @City, og @State alternativ automatisk tilbake til "CA". Erfarne programmerere gjøres oppmerksom på at alle standard variablene er plassert nærmere slutten av parameterlisten. Ellers er utførelsen ikke er mulig, så må man jobbe med overføring av navngitte argumenter at lengre og mer komplisert.

4. lagrede prosedyrer SQL Server: forskjellige måter å returnere

Det er tre store måter å sende data som er lagret i kalles prosedyre. De er listet opp nedenfor:

- returverdiene lagret prosedyre;

- utdataparameteren lagrede prosedyrer;

- Velg en av lagrede prosedyrer.

4.1 Retur verdier av SQL-lagrede prosedyrer

I denne prosedyren, setter prosedyren verdien av lokal variabel og returnerer det. Fremgangsmåten kan også direkte returnere en konstant verdi. I følgende eksempel, har vi laget en prosedyre som returnerer det totale antallet sponsorer. Hvis vi sammenligner dette med forrige fremgangsmåte, kan du se at verdien av utskriften er erstattet av det motsatte.

Nå la oss se på hvordan du utfører prosedyren og vise verdien, returnere den. Utfører rutiner som kreves for å etablere og variable trykk, som utføres etter prosessen. Legg merke til at i stedet for å skrive operatøren kan bruke Select-operatør, for eksempel velge @RetValue, og OutputValue.

4.2 Utgang Parameter SQL lagrede prosedyrer

Responsen verdi kan brukes til å returnere en enkelt variabel som vi har sett i det foregående eksempel. Bruke Output lar prosedyre for å sende en eller flere variabler til den som ringer. Utdataparameteren betegnes som tiden dette stikkordet «utgang» når du oppretter prosedyre. Hvis parameteren er angitt som en utgangsparameter, må prosedyren gjenstand tilordne den en verdi. Lagrede prosedyrer SQL, og eksempler på disse kan sees nedenfor, i et slikt tilfelle å vende tilbake til den endelige informasjon.

I dette eksempelet, vil det være to helger navn: @TotalAuthors og @TotalNoContract. De er spesifisert i parameterlisten. Disse variablene er tildelt verdier innenfor prosedyre legeme. Når vi bruker utgangs parametere, kan den som ringer se verdien som er angitt i fremgangsmåten kroppen.

I tillegg, i den foregående situasjon, er to variable erklært å se verdiene som er installert lagrede prosedyrer, MS SQL Server som en utgangsparameter. Deretter prosedyren utføres ved å påføre den normale verdien «CA» parameter. Disse parameterne er utgang og, derfor, erklært variablene sendes på foreskreven måte. Vær oppmerksom på at når en variabel utgang søkeord også spesifisert her. Etter at prosedyren er fullført, blir verdien som returneres ved hjelp av utgangsparametrene som vises i displayet.

4.3 Valg av SQL-lagrede prosedyrer

Denne teknikken brukes til å returnere et sett i form tabell dataverdier (RecordSet) for å kalle et lagret prosedyre. I dette eksempel SQL lagret prosedyre med parametre @AuthID anmoder om bord "forfattere" ved å filtrere postene som returneres av denne parameteren @AuthId. Velg operatør bestemmer hva som skal returneres til å kalle en lagret prosedyre. Når en lagret prosedyre AUTHID sendt tilbake. En slik prosedyre er alltid returnerer bare én post eller ingen i det hele tatt. Men den lagrede prosedyren ikke har noen restriksjoner på avkastningen på mer enn én oppføring. Ofte eksempler finnes karakterisert ved at tilbakeførings utvalgte data ved hjelp av parametre med de beregnede variabler blir gjort ved å tilveiebringe en flerhet av totalverdier.

i konklusjonen

En lagret prosedyre er en ganske alvorlig program, returnert eller overføres, samt etablere de nødvendige variable grunn til klientapplikasjonen. Siden den lagrede prosedyren utføres på selve serveren, kan datautveksling i store volumer mellom server og klientapplikasjonen (for noen beregninger) unngås. Dette gjør det mulig å redusere belastningen på SQL server, noe som selvfølgelig er hånden av sine eiere. En av de underarter lagrede prosedyrer T SQL, men deres studier er nødvendig for de involverte i etableringen av en imponerende database. Det er også en stor, selv enorm mengde nyanser som kan være nyttig i studiet av lagrede prosedyrer, men dette behovet for de som planlegger å gjøre tett programmering, inkludert profesjonelt.