En introduksjon til lagrede prosedyrer i MySQL 5

MySQL 5 introduserte en mengde nye funksjoner - lagrede prosedyrer er en av de viktigste. I denne opplæringen vil vi fokusere på hva de er, og hvordan de kan gjøre livet enklere.

Hvis du jobber med MySQL mye, vil du kanskje sjekke ut utvalg av MySQL-kodeskript og plugins på Envato Market.

Introduksjon

? En lagret rutine er et sett med SQL-setninger som kan lagres i serveren.?

En lagret prosedyre er en metode for å inkapslere gjentatte oppgaver. De tillater variabeldeklarasjoner, flytkontroll og andre nyttige programmeringsteknikker.

Den? Akademiske? stillingen på dette er ganske tydelig og støtter den omfattende bruken av lagrede prosedyrer. På den annen side, når du vurderer meninger fra de som jobber med dem dag inn, dag ut, vil du legge merke til at reaksjonene varierer fra fullstendig, ubøyelig støtte til fullstendig hat. Husk dette.

Pros

Del logikk med andre applikasjoner. Lagrede prosedyrer inkapsler funksjonalitet; Dette sikrer at dataadgang og manipulering er sammenhengende mellom ulike applikasjoner.
Isolere brukere fra datatabeller. Dette gir deg muligheten til å gi tilgang til de lagrede prosedyrene som manipulerer dataene, men ikke direkte til tabellene.
Gi en sikkerhet mekanisme. Tatt i betraktning det forrige elementet, hvis du bare kan få tilgang til dataene med de lagrede prosedyrene som er definert, kan ingen andre utføre en SLETT SQL-setning og slette dataene dine.
Til forbedre ytelsen fordi det reduserer nettverkstrafikken. Med en lagret prosedyre kan flere samtaler meldes inn i en.

Ulemper

Økt belastning på databaseserveren - det meste av arbeidet er gjort på server siden, og mindre på klientsiden.
Det er en anstendig læringskurve. Du må lære syntaksen av MySQL-setningene for å skrive lagrede prosedyrer.
Du er gjenta logikken av søknaden din på to forskjellige steder: serverkoden og den lagrede prosedyrekoden, noe som gjør ting litt vanskeligere å vedlikeholde.
Migrering til et annet databasebehandlingssystem (DB2, SQL Server, etc) kan potensielt være vanskeligere.

Verktøyet som jeg jobber med i denne opplæringen, MySQL Query Browser, er ganske standard for databasevirkninger. MySQL kommandolinjeverktøyet er et annet utmerket valg. Jeg gjør oppmerksom på dette fordi den populære phpMyAdmin ikke støtter lagret prosedyreutførelse.

I tillegg bruker jeg veldig rudimentære bordstrukturer, for å lette forklaringen. Jeg viser bort lagrede prosedyrer, og de er komplekse nok uten å bekymre deg for store tabeller.

Trinn 1 - Plukker en avgrenser

Avgrenseren er tegnet eller strengen av tegn som du vil bruke til å fortelle mySQL-klienten at du er ferdig med å skrive inn en SQL-setning. I årevis har avgrenseren alltid vært en semikolon. Det forårsaker imidlertid problemer, for i en lagret prosedyre kan man ha mange uttalelser, og hver må ende med et semikolon. I denne opplæringen vil jeg bruke? //?

Trinn 2 - Hvordan jobbe med en lagret prosedyre

Opprette en lagret prosedyre

DELIMITER // CREATE PROCEDURE 'p2' () LANGUAGE SQL DETERMINISTISK SQL SECURITY DEFINER COMMENT 'En prosedyre' BEGIN SELECT 'Hello World!'; SLUTT//

Den første delen av setningen skaper prosedyren. De neste klausulene definerer de valgfrie egenskapene til prosedyren. Da har du navnet og til slutt kroppen eller rutinekoden.

Lagrede prosedyrenavn er sakfølsomme, og du kan ikke opprette prosedyrer med samme navn. Inne i en prosedyre kroppen, kan du ikke sette databasen-manipulering uttalelser.

De fire egenskapene til en prosedyre er:

Språk : For portabilitet formål; Standardverdien er SQL.
determinis : Hvis prosedyren alltid returnerer de samme resultatene, gitt samme inngang. Dette er for replikering og logging formål. Standardverdien er IKKE DETERMINISK.
SQL-sikkerhet : På anropstid, kontroller brukerens privilegier. Invoker er brukeren som ringer prosedyren. definer er skaperen av prosedyren. Standardverdien er definer.
Kommentar : Til dokumentasjonsformål; standardverdien er ""

Ringer en lagret prosedyre

For å ringe en prosedyre, trenger du bare å skrive inn ordet ANROP, etterfulgt av prosedyrens navn, og deretter parentesene, inkludert alle parametrene mellom dem (variabler eller verdier). Parenteser er obligatoriske.

CALL stored_procedure_name (param1, param2 ,?) CALL procedure1 (10, 'streng parameter', @parameter_var);

Endre en lagret prosedyre

MySQL gir en ALTER PROCEDURE uttalelse om å endre en rutine, men tillater bare muligheten til å endre visse egenskaper. Hvis du må endre kroppen eller parametrene, må du slippe og gjenopprette prosedyren.

Slett en lagret prosedyre

DROP PROCEDURE OM EXISTS P2;

Dette er en enkel kommando. De HVIS EXISTER Klausulen hindrer en feil hvis prosedyren ikke eksisterer.

Trinn 3 - Parametere

La oss undersøke hvordan du kan definere parametere i en lagret prosedyre.

Lag prosedyre proc1 () : Parameterlisten er tom
CREATE PROCEDURE proc1 (IN varname DATA-TYPE) : En inngangsparameter. Ordet I er valgfritt fordi parametere er I (input) som standard.
CREATE PROCEDURE proc1 (UT varnavn DATA-TYPE) : Én utgangsparameter.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE) : En parameter som både er inngang og utgang.

Selvfølgelig kan du definere flere parametere definert med forskjellige typer.

I eksempel

 DELIMITER // CREATE PROCEDURE 'proc_IN' (IN var1 INT) BEGIN SELECT var1 + 2 AS resultat; SLUTT//

UT eksempel

DELIMITER // CREATE PROCEDURE 'proc_OUT' (UT var1 VARCHAR (100)) BEGIN SET var1 = 'Dette er en test'; SLUTT //

INOUT eksempel

DELIMITER // CREATE PROCEDURE 'proc_INOUT' (UT var1 INT) BEGIN SET var1 = var1 * 2; SLUTT //

Trinn 4 - Variabler

Følgende trinn vil lære deg hvordan du definerer variabler, og lagrer verdier i en prosedyre. Du må deklarere dem eksplisitt ved starten av BEGIN / END blokkere, sammen med deres datatyper. Når du har erklært en variabel, kan du bruke den hvor som helst du kan bruke en øktvariabel, eller bokstavelig eller kolonneavn.

Erklære en variabel ved å bruke følgende syntaks:

DECLARE varnavn DATA-TYPE DEFAULT standardverdier;

La oss erklære noen få variabler:

DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); ERKLAR I dag TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT;

Arbeide med variabler

Når variablene er erklært, kan du tildele dem verdier ved hjelp av SETT eller Å VELGE kommando:

DELIMITER // CREATE PROCEDURE 'var_proc' (IN paramstr VARCHAR (20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); ERKLAR I dag TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INSERT TIL tabell1 verdier (a); SET str = 'Jeg er en streng'; VELG KONCAT (str, paramstr), i dag FRA tabell2 hvor b> = 5; SLUTT //

Trinn 5 - Flow Control Structures

MySQL støtter IF, CASE, ITERATE, LEAVE LOOP, WHILE og GJENTA konstruerer for flytkontroll i lagrede programmer. Vi skal vurdere hvordan du bruker HVIS, SAK og SAMTIDIG SOM spesielt, siden de tilfeldigvis er de mest brukte setningene i rutiner.

`HVIS` uttalelse

Med HVIS uttalelse, vi kan takle oppgaver som involverer forhold:

DELIMITER // CREATE PROCEDURE 'proc_IF' (IN param1 INT) BEGIN DECLARE variabel1 INT; SET variable1 = param1 + 1; IF variable1 = 0 THEN SELECT variable1; SLUTT OM; HVIS param1 = 0 SELV 'Parameterverdi = 0'; ELSE SELECT 'Parameter verdi <> 0'; SLUTT OM; SLUTT //

`SAK` uttalelse

De SAK erklæring er en annen måte å sjekke forholdene på og ta den riktige banen. Det er en utmerket måte å erstatte flere HVIS uttalelser. Erklæringen kan skrives på to forskjellige måter, og gir stor fleksibilitet til å håndtere flere forhold.

DELIMITER // CREATE PROCEDURE 'proc_CASE' (IN param1 INT) BEGIN DECLARE variabel1 INT; SET variable1 = param1 + 1; CASE variabel1 Når 0 da legges inn i tabell1 verdier (param1); Når 1 så legger inn i tabell1 verdier (variabel1); ANNET INSERT TIL TABELL1 VÆRDIER (99); Sluttfall SLUTT //

eller:

DELIMITER // CREATE PROCEDURE 'proc_CASE' (IN param1 INT) BEGIN DECLARE variabel1 INT; SET variable1 = param1 + 1; CASE WHEN variable1 = 0 INSERT IN I tabell1 VALUES (param1); Når variabel1 = 1 SEG INSERT TIL tabell1 VÆRDIER (variabel1); ANNET INSERT TIL TABELL1 VÆRDIER (99); Sluttfall SLUTT //

`SAMTIDIG SOM` uttalelse

Det er teknisk tre standardløkker: SAMTIDIG SOM løkker, LOOP sløyfer og GJENTA sløyfer. Du har også muligheten til å lage en løkke med "Darth Vader"? av programmeringsteknikker: GÅ TIL uttalelse. Sjekk ut dette eksempelet på en loop i aksjon:

DELIMITER // CREATE PROCEDURE 'proc_WHILE' (IN param1 INT) BEGIN DECLARE variabel1, variabel2 INT; SET variable1 = 0; Mens variabel1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Trinn 6 - Markører

markør brukes til å iterere gjennom et sett med rader returnert av en forespørsel og behandle hver rad.

MySQL støtter markør i lagrede prosedyrer. Her er en oppsummering av det essensielle syntaksen for å lage og bruke en markør.

DECLARE markørnavn KURSOR FOR VELG? ; / * Erklær og fyll markøren med en SELECT-setning * / DEKLAR FORTSATT HANDLER FOR IKKE FUNDET / * Angi hva du skal gjøre når det ikke finnes flere poster * / OPEN markørnavn; / * Åpne markør for bruk * / FETCH markørnavn INTO variabel [, variabel]; / * Tilordne variabler med de nåværende kolonnverdiene * / CLOSE cursor-name; / * Lukk markøren etter bruk * /

I dette eksemplet utfører vi noen enkle operasjoner ved hjelp av en markør:

DELIMITER // CREATE PROCEDURE 'proc_CURSOR' (UT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR FOR SELECT col1 FRA tabell1; DECLARE CONTINUE HANDLER FOR IKKE FUNKSJONER SET b = 1; ÅPEN cur1; SET b = 0; SET c = 0; HVOR b = 0 DO FETCH cur1 INTO a; IF b = 0 THEN SET c = c + a; SLUTT OM; Slutt mens; Lukk cur1; SET param1 = c; SLUTT //

Markøren har tre viktige egenskaper som du trenger å være kjent med for å unngå uventede resultater:

ASENSITIVE : Når en gang er åpen, vil markøren ikke gjenspeile endringer i kildetabellene. Faktisk garanterer MySQL ikke at markøren vil bli oppdatert, slik at du ikke kan stole på det.
Les bare : Markører er ikke oppdaterbare.
Ikke rullbar : Markører kan bare krysses i en retning fremover, og du kan ikke hoppe over poster fra å hente.

Konklusjon

I denne leksjonen dekket vi grunnlaget for lagrede prosedyrer og noen spesifikke egenskaper knyttet til dem. Selvfølgelig bør du fortsette studiene dine på områder som sikkerhet, SQL-setninger og ytelse før du kan mestre MySQL-rutiner.

Du må vurdere fordelene som lagrede prosedyrer kan bringe til dine applikasjoner, og deretter foreta en rimelig implementering som passer dine krav. Jeg bruker vanligvis prosedyrer; deres fordeler med hensyn til sikkerhet, kodevedlikehold og programvareutforming gjør dem verdig til bruk, etter min mening. I tillegg må du huske at prosedyrer i MySQL fortsatt er et pågående arbeid. Du bør fullt ut forvente forbedringer, når det gjelder funksjonalitet og ytelse i fremtiden

Ikke nøl med å kommentere og dele dine ideer og meninger. Og ta en titt på theяMySQL kode skript og pluginsяon Envato Market for å se om du finner noe for å hjelpe deg der.

Kode