Een inleiding tot opgeslagen procedures in MySQL 5

MySQL 5 introduceerde een overvloed aan nieuwe functies - opgeslagen procedures zijn een van de belangrijkste. In deze zelfstudie concentreren we ons op wat ze zijn en hoe ze uw leven gemakkelijker kunnen maken.

Als u veel met MySQL werkt, wilt u misschien het bereik van MySQL codescripts en plug-ins op Envato Market bekijken.

Invoering

? Een opgeslagen routine is een set SQL-instructies die op de server kunnen worden opgeslagen.?

Een opgeslagen procedure is een methode om repetitieve taken in te kapselen. Ze maken variabele declaraties, flow control en andere handige programmeertechnieken mogelijk.

De? Academicus? standpunt hierover is vrij duidelijk en ondersteunt het uitgebreide gebruik van opgeslagen procedures. Aan de andere kant, als je kijkt naar de meningen van diegenen die dag in dag uit met hen werken, zul je merken dat reacties variëren van complete, onwrikbare ondersteuning tot extreme haat. Houd dit in gedachten.

Pros

Deel logica met andere applicaties. Opgeslagen procedures inkapselen functionaliteit; dit zorgt ervoor dat gegevenstoegang en -manipulatie coherent zijn tussen verschillende toepassingen.
Isoleren gebruikers van gegevenstabellen. Dit geeft u de mogelijkheid om toegang te verlenen tot de opgeslagen procedures die de gegevens manipuleren, maar niet rechtstreeks naar de tabellen.
Verschaffen veiligheid mechanisme. Gezien het voorgaande item, als u alleen toegang hebt tot de gegevens met behulp van de gedefinieerde opgeslagen procedures, kan niemand anders a uitvoeren DELETE SQL-instructie en wis uw gegevens.
Naar prestatie verbeteren omdat het netwerkverkeer vermindert. Met een opgeslagen procedure kunnen meerdere oproepen worden samengevoegd tot één.

Cons

Verhoogde belasting op de databaseserver - het grootste deel van het werk gebeurt aan de serverzijde en minder aan de clientzijde.
Er is een fatsoenlijk leercurve. U moet de syntaxis van MySQL-instructies leren om opgeslagen procedures te schrijven.
je bent de logica herhalen van uw toepassing op twee verschillende plaatsen: uw servercode en de code voor opgeslagen procedures, waardoor dingen een beetje moeilijker te onderhouden zijn.
migreren naar een ander databasebeheersysteem (DB2, SQL Server, enz.) kan mogelijk moeilijker zijn.

De tool waarmee ik in deze tutorial, MySQL Query Browser, werk, is vrij standaard voor database-interacties. Het opdrachtregelprogramma MySQL is een andere uitstekende keuze. Ik noteer dit omdat de populaire phpMyAdmin geen ondersteuning biedt voor de uitvoering van opgeslagen procedures.

Bovendien zal ik zeer rudimentaire tafelstructuren gebruiken, uitsluitend om de uitleg te vergemakkelijken. Ik laat opgeslagen procedures zien en ze zijn complex genoeg zonder me zorgen te maken over grote tafels.

Stap 1 - Een scheidingsteken kiezen

Het scheidingsteken is het teken of de reeks tekens die u gebruikt om de mySQL-client te vertellen dat u klaar bent met het typen van een SQL-instructie. Al eeuwenlang is het scheidingsteken altijd een puntkomma geweest. Dat levert echter problemen op, omdat je in een opgeslagen procedure veel uitspraken kunt doen en elk moet eindigen met een puntkomma. In deze tutorial gebruik ik? //?

Stap 2 - Hoe te werken met een opgeslagen procedure

Een opgeslagen procedure maken

DELIMITER // CREATE PROCEDURE 'p2' () TAAL SQL DETERMINISTIC SQL BEVEILIGING DEFINER COMMENTAAR 'A procedure' BEGIN SELECT 'Hallo wereld!'; EINDE//

Het eerste deel van de verklaring creëert de procedure. De volgende clausules definiëren de optionele kenmerken van de procedure. Dan heb je de naam en als laatste de body of routine code.

Opgeslagen procedurenamen zijn niet hoofdlettergevoelig en u kunt geen procedures met dezelfde naam maken. In een procedure-instantie kunt u geen databasemanipulatie-uitspraken doen.

De vier kenmerken van een procedure zijn:

Taal : Voor portabiliteitsdoeleinden; de standaardwaarde is SQL.
deterministische : Als de procedure altijd dezelfde resultaten oplevert, met dezelfde invoer. Dit is voor doeleinden van replicatie en logging. De standaardwaarde is NIET DETERMINISTISCH.
SQL-beveiliging : Controleer tijdens de beltijd de rechten van de gebruiker. Invoker is de gebruiker die de procedure oproept. DEFINER is de maker van de procedure. De standaardwaarde is DEFINER.
Commentaar : Voor documentatiedoeleinden; de standaardwaarde is ""

Een opgeslagen procedure oproepen

Om een procedure te bellen, hoeft u alleen het woord in te voeren CALL, gevolgd door de naam van de procedure en vervolgens de haakjes, inclusief alle parameters ertussen (variabelen of waarden). Haakjes zijn verplicht.

CALL stored_procedure_naam (param1, param2 ,?) CALL-procedure1 (10, 'stringparameter', @parameter_var);

Wijzig een opgeslagen procedure

MySQL biedt een ALTER PROCEDURE verklaring om een routine te wijzigen, maar alleen voor de mogelijkheid om bepaalde kenmerken te wijzigen. Als u de hoofdtekst of de parameters moet wijzigen, moet u de procedure laten vallen en opnieuw maken.

Een opgeslagen procedure verwijderen

DROP PROCEDURE ALS BESTAAT p2;

Dit is een eenvoudige opdracht. De ALS BESTAAT clausule voorkomt een fout in het geval de procedure niet bestaat.

Stap 3 - Parameters

Laten we eens kijken hoe u parameters kunt definiëren binnen een opgeslagen procedure.

CREËER PROCEDURE proc1 () : Parameterlijst is leeg
CREËER PROCEDURE proc1 (IN varname DATA-TYPE) : Eén invoerparameter. Het woord IN is optioneel omdat parameters dat zijn IN (invoer) standaard.
CREËER PROCEDURE proc1 (OUT varname DATA-TYPE) : Eén uitgangsparameter.
CREËER PROCEDURE proc1 (INOUT varname DATA-TYPE) : Eén parameter die zowel invoer als uitvoer is.

Natuurlijk kunt u meerdere parameters definiëren die met verschillende types zijn gedefinieerd.

Bijvoorbeeld

 DELIMITER // CREËER PROCEDURE 'proc_IN' (IN var1 INT) BEGIN SELECT var1 + 2 AS-resultaat; EINDE//

OUT voorbeeld

DELIMITER // CREËER PROCEDURE 'proc_OUT' (OUT var1 VARCHAR (100)) BEGIN SET var1 = 'Dit is een test'; END //

INOUT voorbeeld

DELIMITER // CREËER PROCEDURE 'proc_INOUT' (UIT var1 INT) BEGIN SET var1 = var1 * 2; END //

Stap 4 - Variabelen

In de volgende stap leert u hoe u variabelen definieert en waarden opslaat in een procedure. U moet ze expliciet aan het begin van de BEGIN / END blokkeren, samen met hun gegevenstypen. Nadat u een variabele hebt gedeclareerd, kunt u deze overal gebruiken waar u een sessievariabele of letterlijke of kolomnaam kunt gebruiken.

Een variabele declareren met behulp van de volgende syntaxis:

DECLARE varname DATA-TYPE DEFAULT standaardwaarde;

Laten we een paar variabelen verklaren:

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

Werken met variabelen

Nadat de variabelen zijn gedeclareerd, kunt u deze waarden toewijzen met behulp van de SET of SELECT commando:

DELIMITER // CREËER PROCEDURE 'var_proc' (IN paramstr VARCHAR (20)) BEGIN DECLARE a, b INT DEFAULT 5; DECLARE str VARCHAR (50); DECLARE vandaag TIMESTAMP DEFAULT CURRENT_DATE; DECLARE v1, v2, v3 TINYINT; INVOEGEN IN tabel 1 WAARDEN (a); SET str = 'Ik ben een string'; SELECT CONCAT (str, paramstr), vandaag FROM table2 WHERE b> = 5; END //

Stap 5 - Flow Control-structuren

MySQL ondersteunt de INDIEN, CASE, ITERATE, VERLOF LOOP, TERWIJL en HERHALING constructies voor flow control binnen opgeslagen programma's. We gaan bekijken hoe te gebruiken ALS, GEVAL en TERWIJL vooral omdat ze de meest gebruikte uitspraken zijn in routines.

`ALS` uitspraak

Met de ALS verklaring, we kunnen taken aan met voorwaarden:

DELIMITER // CREËR PROCEDURE 'proc_IF' (IN param1 INT) BEGIN DECLARE variable1 INT; SET variabele1 = param1 + 1; ALS variabele1 = 0 DAN SELECTEER variabele1; STOP ALS; IF param1 = 0 DAN SELECTEER 'Parameterwaarde = 0'; ELSE SELECTEER 'Parameterwaarde <> 0'; STOP ALS; END //

`GEVAL` uitspraak

De GEVAL verklaring is een andere manier om de voorwaarden te controleren en het juiste pad te volgen. Het is een uitstekende manier om meerdere te vervangen ALS statements. De verklaring kan op twee verschillende manieren worden geschreven, waardoor er een grote flexibiliteit is om met meerdere omstandigheden om te gaan.

DELIMITER // CREËER PROCEDURE 'proc_CASE' (IN param1 INT) BEGIN DECLARE variable1 INT; SET variabele1 = param1 + 1; CASE variabele1 WANNEER 0 DAN IN TABEL1WAARDEN INVOEGEN (parameter 1); WANNEER DAN 1 IN TABEL1WAARDEN INVOEREN (variabele1); ELKE INSERT IN Table1 VALUES (99); EIND CASE; END //

of:

DELIMITER // CREËER PROCEDURE 'proc_CASE' (IN param1 INT) BEGIN DECLARE variable1 INT; SET variabele1 = param1 + 1; CASE WANNEER variabele1 = 0 DAN INVOEGEN IN tabel1 WAARDEN (param1); WANNEER variabele1 = 1 DAN INVOEGEN IN tabel1 WAARDEN (variabele1); ELKE INSERT IN Table1 VALUES (99); EIND CASE; END //

`TERWIJL` uitspraak

Er zijn technisch drie standaardlussen: TERWIJL loops, LUS loops en HERHALING loops. Je hebt ook de mogelijkheid om een lus te maken met de? Darth Vader? van programmeertechnieken: de GA NAAR uitspraak. Bekijk dit voorbeeld van een lus in actie:

DELIMITER // CREËR PROCEDURE 'proc_WHILE' (IN param1 INT) BEGIN DECLARE variabele1, variabele2 INT; SET variabele1 = 0; TERWIJL variabele1 < param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //

Stap 6 - Cursors

Cursor wordt gebruikt om een verzameling rijen door een query te doorlopen en elke rij te verwerken.

MySQL ondersteunt cursor in opgeslagen procedures. Hier volgt een samenvatting van de essentiële syntaxis om een cursor te maken en te gebruiken.

DECLARE cursornaam CURSOR VOOR SELECTEREN? ; / * Verklaar en vul de cursor met een SELECT-instructie * / VERKLARING DOORGAAN HANDLER NIET GEVONDEN / * Specificeer wat te doen als er geen records meer zijn gevonden * / OPEN cursornaam; / * Open cursor voor gebruik * / FETCH cursornaam INTO variabele [, variabele]; / * Ken variabelen toe met de huidige kolomwaarden * / CLOSE cursornaam; / * Sluit de cursor na gebruik * /

In dit voorbeeld voeren we enkele eenvoudige bewerkingen uit met behulp van een cursor:

DELIMITER // CREËER PROCEDURE 'proc_CURSOR' (OUT param1 INT) BEGIN DECLARE a, b, c INT; DECLARE cur1 CURSOR VOOR SELECT col1 FROM table1; VERKLAREN DOORGAAN HANDLER VOOR NIET GEVONDEN SET b = 1; OPEN cur1; SET b = 0; SET c = 0; TERWIJL b = 0 DO FETCH cur1 IN a; ALS b = 0 DAN SET c = c + a; STOP ALS; EINDE TERWIJL; SLUIT cur1; SET param1 = c; END //

Cursor heeft drie belangrijke eigenschappen die u moet kennen om onverwachte resultaten te voorkomen:

ASENSITIVE : Eenmaal geopend, geeft de cursor geen wijzigingen in de brontabellen weer. In feite garandeert MySQL niet dat de cursor wordt bijgewerkt, dus u kunt er niet op vertrouwen.
Alleen lezen : Cursors kunnen niet worden bijgewerkt.
Niet scrollable : Cursors kunnen slechts in één richting worden doorkruist, worden doorgestuurd en u kunt geen records overslaan voor ophalen.

Conclusie

In deze les hebben we de basisprincipes van opgeslagen procedures besproken en enkele specifieke eigenschappen die daarmee verband houden. Natuurlijk moet u doorgaan met uw studies op het gebied van beveiliging, SQL-instructies en prestaties voordat u MySQL-routines onder de knie kunt krijgen.

U moet de voordelen evalueren die opgeslagen procedures mogelijk voor uw toepassingen kunnen opleveren en vervolgens een redelijke implementatie maken die voldoet aan uw vereisten. Ik gebruik over het algemeen procedures; hun voordelen op het gebied van beveiliging, code-onderhoud en softwareontwerp maken ze naar mijn mening waardig. Bedenk bovendien dat de procedures in MySQL nog steeds in uitvoering zijn. U moet in de toekomst volledig verbeteringen verwachten in termen van functionaliteit en prestaties.я

Aarzel niet om commentaar te geven en deel uw ideeën en meningen. En bekijk de Samsung-codescripts en plug-ins op Envato Market om te zien of u iets vindt om u daar te helpen.

Code