PHPPamokos.lt


9. Kuriame savo STORED PROCEDURES

Kartais būna, kad reikia atlikti veiksmus keliomis SQL užklausomis. Sakykime, atfiltruoti vienus duomenis, o pagal jų rezultatus - dar kitus duomenis, ir to nesigauna padaryti vienoje užklausoje. Tada galime tą visą apiforminti kaip vientisą procedūrą, kas SQL kalboje vadinama stored procedure.

Tiesa, jeigu iki tol dauguma mūsų paliestų temų buvo aktualios SQL kalbai, nepriklausomai nuo duomenų bazės varikliuko (MySQL, PostgreSQL, Microsoft SQL ar dar kas nors), tai su procedūromis čia jau prasideda MySQL specifika - tad nebūtinai tai, kas veikia MySQL, veiks ir kitoje sistemoje.

Kuriame procedūrą per phpMyAdmin

PhpMyAdmin sistemoje procedūros yra vadinamos žodžiu Routines, tai meniu viršuje randame tokį punktą ir tada pasirenkamą Add routine. Pasirodys gan painus langas, iš kurio realiai mums reikia tik kelių laukų:

  • Pavadinimas - sakykime, getProducts ar getBestCustomer, čia jau kokį sugalvosite
  • Procedūros tekstas - čia tekstiniame lauke bus rašomi SQL sakiniai, iš kurių ir susideda procedūra
  • Parametrai (nebūtini) - jei norite procedūrai paduoti vieną ar daugiau parametrų, galima juos įvardinti čia, arba galima ištrinti jau esamus siūlomus parametrus

Pradžiai kaip pavyzdį sukurkime paprastą procedūrą iš vieno SQL sakinio:

Ir viskas - dabar turime procedūrą, kurią galima bet kada iškviesti iš už jos ribų - su komanda CALL. Sakykime, norime įvykdyti tokį sakinį:

CALL getExpensiveProducts();

Kaip matote, rezultatas panašus, kaip ir darant įprastą SQL užklausą.

Sukūrus procedūrą, ji lieka MySQL atmintyje, bet ją galima bet kada pašalinti - vėlgi arba per phpMyAdmin punktą routines, arba per SQL užklausą DROP PROCEDURE.

Kuriame procedūrą per SQL užklausas

Jei dėl kokios nors priežasties norite sukurti procedūrą rankiniu būdu užklausos pagalba - tai, be abejo, irgi įmanoma. Kodas atrodo daugmaž taip:

DELIMITER //

CREATE PROCEDURE `getExpensiveProducts`()
BEGIN
    SELECT * FROM products WHERE price > 100;
END

Turbūt dauguma žodžių kaip PROCEDURE yra savaime suprantami, o didžiausias klaustukas kyla - kas tas DELIMITER. Reikalas tas, kad MySQL kalboje - jei norite įvykdyti kelis sakinius vienu metu, sakiniai yra atskiriami kabliataškio simboliu. O kadangi kuriant procedūrą, mes turime atskyrinėti kabliataškiu sakinius procedūros viduje, tai reikia laikinai pakeisti tą atskyrimo požymį - jis ir vadinasi DELIMITER. Taigi laikinai jį pakeičiame į vieną ar kelis simbolius, kurių tikrai žinome kad nenaudosime pačioje procedūroje (šiuo atveju $$ bet jūs galite naudoti bet kokius kitus), o po mūsų procedūros sukūrimo užklausos įvykdymo - atstatome buvusį kabliataškį.

Procedūrų parametrai ir kintamieji

Kaip jau minėjau, procedūrai galime paduoti vieną ar daugiau parametrų. Tai atrodo daugmaž taip:

DELIMITER //

CREATE PROCEDURE `getExpensiveProducts`(IN param_price FLOAT)
BEGIN
  SELECT * FROM products WHERE price > param_price;
END

Galima sukurti ir tarpinį kintamąjį procedūros viduje:

CREATE PROCEDURE `getExpensiveProducts`()
BEGIN
  DECLARE param_price FLOAT(8,2) DEFAULT 0;
  SELECT * FROM products WHERE price > param_price;
END

Taip pat galime grąžinti kintamąjį kaip rezultatą.

CREATE PROCEDURE `getExpensiveProducts`(OUT max_price FLOAT)
BEGIN
  DECLARE result_price FLOAT(8,2);
  SELECT MAX(price) INTO result_price FROM products;
  SELECT result_price;
END

Sudėtingesnės struktūros procedūrų viduje

Vėlgi SQL nėra programavimo kalba, bet palaiko populiariausias kalbos konstrukcijas: IF-sakinius, CASE struktūrą ir pan. Tai šiam momentui turime pakankamai žinių, kad praplėstume viršuje esantį pavyzdį:

CREATE PROCEDURE `getExpensiveProducts`(IN price FLOAT)
BEGIN
  DECLARE result_price FLOAT(8,2);
  IF price > 100 THEN
    SELECT MAX(price) INTO result_price FROM products;
  ELSE
    SELECT MIN(price) INTO result_price FROM products;
  END IF;
  SELECT result_price;
END

Kodėl verta naudoti procedūras?

Koks apskritai poreikis kurti DB procedūras ir kokia iš jų nauda? Dažniausiai minimas yra toks pavyzdys, kai su ta pačia duomenų baze dirba kelios aplikacijos: sakykime, svetainė, mobilioji programėlė, dar koks nors išorinis įrenginys, administravimo panelė, trečiųjų šalių skriptai. Tada būtų šiek tiek nelogiška tą pačią procedūrą darbui su duomenimis atskirai rašyti kiekvienai tai skirtingai sistemai - geriau viską apjungti viename sluoksnyje, kas ir yra duomenų bazė. Visi tie įrenginiai kviestų tą pačią MySQL procedūrą, ir jeigu jos logikoje reikėtų kažką pakeisti, tai užtektų keisti tik viename DB sluoksnyje, o ne visur atskirai.

Taip pat dažnai minimas privalumas, kad DB procedūra veikia greičiau, nei pavienės užklausos atskirai. Tai iš dalies yra tiesa, bet iš tikrųjų viskas priklauso nuo to, kaip DB variklis saugo iškviečia procedūras. Tai šiuo atveju MySQL nepasižymi galingu kešavimo ar optimizavimo mechanizmu, tad jei spręsite naudoti DB procedūras vien dėl greičio, tai visumoje galite jo ir nepasiekti.

Kodėl neverta naudoti procedūrų?

Kaip turbūt supratote, procedūrų naudojimas bendrai yra architektūrinio lygio sprendimas - į kokias atskiras dalis suskirstytas jūsų projektas: ar apsimoka daugiau logikos sudėti į duomenų bazę, ar geriau į kodą, sakykime PHP kalba. Tai vienas iš argumentų, kodėl DB procedūros pasunkina gyvenimą programuotojams, tai kad jos turi ribotas galimybes teisingus kodo tvarkymo procesus organizuoti: su jomis sunku naudoti unit testus, versijū kontrolės įrankius, palaikyti senesnes versijas ar apskritai atlikti debuginimo procesą.

Kitas įvardinamas minusas - kad MySQL atveju procedūrų kūrimas dažnam vidutinio lygio programuotojui yra per sunkus uždavinys, tai jeigu vienas darbuotojas jūsų kompanijoje yra aukštesnio lygio ir tvarkosi su procedūromis gerai, tai jam pasitraukus galite nerasti tokio lygio specialisto. Nors čia, mano manymu, silpnas argumentas, bet kita vertus, procedūros kartais tikrai pasunkina gyvenimą naujiems programuotojams - pridedamas dar vienas papildomas sluoksnis palaikymui ir apdorojimui.

Taigi, klausimas "kada verta, kada neverta" neturi vienareikšmiško atsakymo. Tiesą pasakius, MySQL praktikoje realiuose projektuose procedūras tenka sutikti gana retai, dėl to pats turiu tokią nuomonę, kad jeigu įmanoma išsiversti be procedūrų ir nėra aiškaus poreikio jas naudoti, tai neapsimoka su jomis vargti. Apsimoka jas taikyti, kaip sakiau, tada kai norima atskirti duomenų bazės sluoksnį, su kuriuo dirbs skirtingos aplikacijos.

Apibendrinimas

Procedūros gali būti naudingas ir patogus įrankis, susisteminti kelias besikartojančias užklausas į vieną duomenų bazės sluoksnį, bet MySQL atveju reikia tvirtai pamąstyti, ar tai daryti apsimoka. Bet kuriuo atveju, dabar jūs žinote, su kuo tai valgoma, ir galbūt gyvenime prireiks.



(c) 2015-2018. Visais klausimais kreipkitės povilas@laraveldaily.com