Procedura e ruajtur MySQL kundrejt funksionit që do të përdor kur
Procedurat e ruajtura në MySQL
Procedurat e ruajtura - çfarë është ajo?
Procedurat e ruajtura u shfaqën duke filluar nga versioni 5 i MySQL. Ato ju lejojnë të automatizoni proceset komplekse në nivelin MySQL, në vend që të përdorni skriptet e jashtme për këtë. Kjo na jep shpejtësinë më të lartë të ekzekutimit, sepse... Ne nuk dërgojmë një numër të madh kërkesash, por thërrasim këtë apo atë procedurë (ose funksion) vetëm një herë.
Çfarë nevojitet për këtë? Instaloni versionin 5 të serverit MySQL ose më të lartë (dev.mysql.com/downloads). Procedurat mund të krijohen si pyetje, për shembull përmes linjës së komandës MySQL, por për lehtësi ju këshilloj të shkarkoni MySQL GUI Tools (dev.mysql.com/downloads/gui-tools). Kjo paketë përfshin tre programe - MySQL Administrator, MySQL Query Browser dhe MySQL Migration Toolkit. Do të na duhen dy të parat. (Edhe pse mund t'ia dilni me vetëm MySQL Query Browser, të gjitha ato $$ në procedurat e ruajtura ndonjëherë mund të jenë konfuze).
Procedura e parë e ruajtur
Pra, hapni MySQL Administrator, lidheni me serverin MySQL dhe krijoni një skemë të re (bazë të dhënash): klikoni Katalogët, zgjidhni Krijo Skemë të Re në zonën Skemat (Ctrl+N). Emërtoni atë diçka (për shembull db). Hapni skemën që sapo keni krijuar, zgjidhni skedën Procedurat e ruajtura dhe klikoni butonin Create Stored Proc. Emërtoni procedurën tuaj të procedurës1. Në trupin e procedurës (midis FILLIMIT dhe FUNDIT) shkruani sa vijon:
SELECT "Kjo është procedura ime e ruajtur" ;
Dhe klikoni Ekzekutoni SQL - procedura është krijuar. Hapni MySQL Query Browser, zgjidhni skemën tuaj (db) dhe shkruani pyetjen e mëposhtme:
Procedura e Thirrjes1() ;
Voila! urime.
Variablat në MySQL
Për të përfituar nga procedurat e ruajtura në MySQL, do t'ju duhet të punoni me variabla. Meqenëse kjo nuk është në objektin e këtij artikulli, unë do të tregoj vetëm disa shembuj.
Variabla të thjeshta
DEKLAROJE iVar INT DEFAULT 0; SELECT COUNT(*) INTO iVar FROM `data` ;
Variablat e Sistemit
SET @iVar = 5 ; SELECT @iVar;
Dallimi midis variablave të thjeshta dhe atyre të sistemit është se variablat e sistemit aksesohen nga jashtë procedurës së ruajtur. Kjo do të thotë, për të nxjerrë disa të dhëna, duhet të përdorni ato të sistemit, dhe variablat që nevojiten vetëm brenda procedurës duhet të jenë të thjeshta.
Parametrat në procedurat e ruajtura
Gjithçka këtu është gjithashtu mjaft e thjeshtë. Ne ndryshojmë rreshtin e parë duke deklaruar vetë procedurën:
KRIJO PROCEDURË 'procedurë1' (Në iInput1 INT, NË iInput2 INT)
Këtu, fjala kyçe IN tregon se parametri është vetëm për lexim. Më pas, ne punojmë me këtë parametër si me një ndryshore të rregullt brenda një procedure:
Kushtet, ciklet. NËSE PASTAJ TJETËR, NDERSA
Ju patjetër do të keni nevojë për kushte dhe sythe kur shkruani procedura komplekse të ruajtura, por unë nuk do të ndalem në këtë temë. Unë mendoj se ju keni të paktën disa aftësi programuese, kështu që unë do t'ju tregoj vetëm sintaksën.
NËSE kushti APO veprimi; veprim TJETER; FUND NËSE ;
NDERSA kushti DO veprim; FUND DERI;
Shembull i thjeshtë
Një rast i mirë përdorimi për procedurat e ruajtura është kur ju duhet të kombinoni kërkesa të shumta në një, të tillë si shtimi i një teme në një forum dhe rritja e numrit total të temave. Le të themi tabelën e fijeve
KRIJO 'fije' TABELARE ( `id` INT NOT NULL AUTO_INCREMENT , `titulli` VARCHAR(255 ) NOT NULL , `etiketa` VARCHAR(255 ) NOT NULL , KEY PRIMARY ( `id` ) ) MOTORI = MYIS
Këtu titulli do të jetë titulli i temës së re. Epo, një tabelë, për shembull, me variabla të ndryshëm statistikorë të faqes, duke përfshirë numrin e përgjithshëm të temave në formë.
CREATE TABLE `variablas` ( `id` INT NOT NULL AUTO_INCREMENT , `emri` VARCHAR(255 ) NOT NULL , `value` INT NOT NULL DEFAULT 0, PRIMARY CEY ( `id` ) ) ENGINE = MYISAM;
Gjithçka duket e qartë këtu, le të themi se kemi një hyrje me emër = threads dhe vlerë = 0. Le të krijojmë një procedurë të re të ruajtur të procedurës2.
Nuk ka asgjë të veçantë për të shpjeguar, vetëm dy kërkesa u bashkuan në një. Tani mund ta quajmë këtë procedurë si kjo:
Procedura CALL2("Fija ime e re");
Kështu, në vend që të kalojmë dy ose më shumë kërkesa (për shembull përmes php), ne mund të kalojmë një - optimizim, kod të pastër dhe mund të ndryshohet në çdo kohë pa ndikuar në skriptet e tjera.
Kursorët (Kursorët MySQL)
Kursorët ju lejojnë të përsërisni të gjitha rezultatet e një pyetjeje. Është e vështirë të shpjegohet në teori, por unë do t'ju tregoj në praktikë. Le të shtojmë një tabelë tjetër në bazën tonë të të dhënave - hitet:
KRIJO 'etiketat' TABELARE ( `id` INT NOT NULL AUTO_INCREMENT , `etiketë` VARCHAR(255 ) NOT NULL , ÇELËSI PRIMAR ( `id` ) ) MOTORI = MYISAM
Këtu do të regjistrojmë të gjitha etiketat nga të gjitha temat. Procedura e ruajtur do të duket diçka si kjo:
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | KRIJO PROCEDURË `procedurë3` () FILLO DEKLAROJE të kryer NË PARAKTORIM 0; DECLARE sTag VARCHAR(255) ; DEKLAROJE iNumërimin NË PARALAKTUAR 0; DEKLAROJ KURSORIN rKursorin PËR ZGJEDHJE `etiketën` NGA `threads` WHERE 1 ; DEKLARONI VAZHDIM TRAJTUESIN PËR SQLSTATE "02000" SET done= 1 ; HAPUR rkursorin; SHKONI rKursorin NË STAG; WHILE done = 0 ZGJIDH NUMRIN(*) NË iCount FROM `tags` WHERE `tag` = sTag; NËSE iCount = 0 ATHEQE INSERT NË `etiketat` (`tag` ) VLERAT (sTag) ; FUND NËSE ; SHKONI rKursorin NË STAG; FUND DERI; MBYLL rKursorin; FUND |
Detajet. Procedura do të kalojë në secilën temë, çdo etiketë do të kalojë në tabelën e etiketave dhe nëse kjo etiketë mungon, do ta shtojë atë.
Kursori për një pyetje SELECT që do të zgjedhë etiketat nga të gjitha temat (KU 1). Pas kursorit ne deklarojmë diçka si një përjashtim - çfarë të bëjmë kur rezultatet mbarojnë (SQLSTATE '02000' nënkupton këtë fund). Në këtë rast, ne do të shkruajmë 1 në ndryshoren e bërë në mënyrë që të dalim më pas nga cikli.
Ne hapim kursorin dhe marrim rekordin e parë. Tjetra në ciklin - Zgjidhni numrin e ndeshjeve nga tabela e etiketave për etiketën aktuale dhe vendosni rezultatin në ndryshoren iCount. Nëse nuk ka rezultate, atëherë përdorni pyetjen INSERT për të futur një etiketë të re.
Në fund mbyllim kursorin dhe dalim nga procedura. OK tani ka mbaruar.
Nxjerrja e të dhënave
Le të kujtojmë variablat e sistemit dhe të shqyrtojmë një manipulim tjetër të tabelave tona - marrjen e numrit total të etiketave dhe temave. Le të kalojmë direkt në procedurë:
| 1 2 3 4 5 6 7 8 9 10 | KRIJO PROCEDURË `procedurë4` () FILLO DEKLARONI iTags NË PARAKTORIM 0; DEKLARONI iThreads INT DEFAULT 0; SELECT COUNT(*) INTO iTags FROM `tags` ; SELECT COUNT(*) INTO iThreads FROM `threads` ; SET @tags = iTags, @threads = iThreads; FUND |
Ne deklarojmë dy variabla - iTags - numrin e etiketave dhe iThreads - numrin total të temave.
Më tej janë dy pyetje të thjeshta të përzgjedhjes, duke plotësuar variablat tona. Epo, në fund ne caktojmë vlerat e ndryshoreve të thjeshta aktuale në variablat e sistemit. Kur thirret, kjo procedurë nuk kthen asgjë, por pasi ta thërrasim, mund të lexojmë vlerat e kërkuara nga variablat e sistemit:
Procedura CALL4() ; ZGJIDHni @tags, @threads;
Dallimi më i zakonshëm midis procedurave dhe funksioneve është se ato quhen ndryshe dhe për qëllime të ndryshme:
- Procedura nuk kthen një vlerë. Në vend të kësaj, thirret me një deklaratë CALL për të kryer një operacion të tillë si modifikimi i një tabele ose përpunimi i të dhënave të marra.
- Funksioni thirret brenda një shprehjeje dhe i kthen një vlerë të vetme thirrësit të menjëhershëm, e cila duhet të përdoret në shprehje.
- Ju nuk mund të thërrisni një funksion me një deklaratë CALL dhe nuk mund të thërrisni një procedurë në një shprehje.
Sintaksa për krijimin rutinë është paksa e ndryshme për procedurat dhe funksionet:
- Parametrat e procedurës mund të specifikohen si hyrje, dalje ose të dyja. Kjo do të thotë që procedura mund t'i kalojë vlerat përsëri telefonuesit duke përdorur parametrat e daljes. Këto vlera mund të aksesohen në deklaratat që pasojnë deklaratën CALL. Funksionet kanë vetëm parametra hyrës. Si rezultat, edhe pse të dy procedurat dhe funksionet mund të kenë parametra, përcaktimi i një parametri të procedurës është i ndryshëm nga përcaktimi i funksioneve për funksionet.
Për të thirrur një procedurë të ruajtur, përdorni një deklaratë CALL. Për të thirrur një funksion të ruajtur, referojuni atij në një shprehje. Funksioni kthen një vlerë gjatë vlerësimit të shprehjes.
Një procedurë thirret duke përdorur një deklaratë CALL dhe mund të kthejë vlera vetëm duke përdorur variablat e daljes. Një funksion mund të thirret nga brenda një deklarate ashtu si çdo funksion tjetër (d.m.th., duke thirrur emrin e funksionit) dhe mund të kthejë një vlerë skalare.
Cilësimi IN, OUT ose INOUT është i vlefshëm vetëm për PROCEDURE. Për funksionin FUNCTION, parametrat konsiderohen gjithmonë në parametrat IN.
Një funksion kthen një vlerë, kështu që përkufizimi i funksionit duhet të ketë një klauzolë RETURNS për të treguar llojin e të dhënave të vlerës së kthyer. Për më tepër, trupi i funksionit duhet të përmbajë të paktën një shprehje RETURN për t'i kthyer një vlerë thirrësit. KTHIMI dhe KTHIMI nuk shfaqen në përkufizimet e procedurës.
Nëse një fjalë kyçe nuk është specifikuar përpara emrit të një parametri, ajo vendoset në një parametër IN. Parametrat për funksionet e ruajtura nuk paraprihen nga IN, OUT ose INOUT. Të gjithë parametrat e funksionit trajtohen si parametra IN.
Për të përcaktuar një procedurë ose funksion të ruajtur, përdorni përkatësisht CREATE PROCEDURE ose CREATE FUNCTION:
CREATE PROCEDURE proc_name () rutine_body CREATE FUNCTION func_name () RETURNS data_type // diffrent rutine_body
Një shtrirje MySQL në një procedurë të ruajtur (jo funksione) është që një procedurë mund të gjenerojë një grup rezultatesh, apo edhe grupe të shumta rezultatesh, të cilat thirrësi i ekzekuton në të njëjtën mënyrë si rezultati i një deklarate SELECT. Megjithatë, përmbajtja e grupeve të tilla të rezultateve nuk mund të përdoret drejtpërdrejt në një shprehje.
Procedurat e ruajtura(duke iu referuar si procedurave të ruajtura ashtu edhe funksioneve të ruajtura) janë të lidhura me një bazë të dhënash specifike, si tabela ose pamje. Kur lëshoni një bazë të dhënash, çdo procedurë e ruajtur në bazën e të dhënave gjithashtu hidhet poshtë.
Procedurat dhe funksionet e ruajtura nuk përdorin të njëjtën hapësirë emri. Ndoshta ka një procedurë dhe funksion me të njëjtin emër në bazën e të dhënave.
Ju mund të përdorni SQL dinamike në procedurat e ruajtura, por jo në funksione ose aktivizues.
Deklaratat e konservuara SQL (PREPARE, EXECUTE, DEALLOCATE PREPARE) mund të përdoren në procedurat e ruajtura, por jo në funksionet ose aktivizuesit e ruajtur. Pra, funksionet dhe nxitësit e ruajtur nuk mund të përdorin Dynamic SQL (ku i ndërtoni deklaratat si vargje dhe më pas i ekzekutoni ato). (SQL dinamike në procedurat e ruajtura të MySQL)
Disa ndryshime më interesante midis FUNKSIONIT dhe PROCEDURËS STORED:
Funksionet zakonisht përdoren për llogaritjet, ku si procedura zakonisht përdoren për të kryer logjikën e biznesit.
Funksionet nuk mund të ndikojnë në gjendjen e bazës së të dhënave (Deklaratat që bëjnë kryerjen ose rikthimin e qartë ose të nënkuptuar janë të ndaluara në një funksion) Ndërsa Procedurat e ruajtura mund të ndikojnë në gjendjen e bazës së të dhënave duke përdorur commit etj.
referencë: J.1. Kufizimet në procedurat e ruajtura dhe nxitësit
Funksionet nuk mund të përdorin FLUSH, ndërsa Procedurat e ruajtura munden.
Brenda një funksioni ose shkasjeje të ruajtur, nuk ju lejohet të modifikoni një tabelë që është tashmë në përdorim (për lexim ose shkrim) nga deklarata që thërret funksionin ose këmbëzën. Shembull i mirë: Si të përditësoni të njëjtën tabelë kur fshihet në MYSQL?
shënim: Megjithëse disa kufizime zbatohen përgjithësisht për funksionet dhe aktivizuesit e ruajtur, por jo për procedurat e ruajtura, këto kufizime zbatohen për procedurat e ruajtura nëse ato thirren nga një funksion ose aktivizues i ruajtur. Për shembull, edhe pse mund të përdorni FLUSH në një procedurë të ruajtur, një procedurë e tillë e ruajtur nuk mund të thirret nga një funksion ose aktivizues i ruajtur.
E thënë thjesht, procedurat e ruajtura (“SP”) janë procedura të ruajtura në një bazë të dhënash (të shkruara duke përdorur SQL dhe deklarata të tjera kontrolli) që mund të ekzekutohen nga një motor i bazës së të dhënave dhe të thirren nga kodi i programit që funksionon me atë motor. """ Lexo plotësisht
Procedurat e ruajtura në MySQL dhe PHP. Pjesa 2
Taylor Wren (Taylor Ren), 03.01.2014
Krijimi i një procedure të ruajtur në MySQL
Meqenëse HP-të ruhen në server, rekomandohet krijimi i tyre direkt në server, d.m.th. Ju nuk duhet të përdorni PHP ose gjuhë të tjera programimi për të ekzekutuar komandat SQL për të krijuar procedura të ruajtura.
Le të shohim se si të krijojmë një HP në një server MySQL, si të krijojmë një përdorues për të dhe si t'i caktojmë atij privilegje për të drejtuar HP-në tonë. Pastaj do të kontrollojmë korrektësinë e rezultatit. Për këtë unë do të përdor MySQL Workbench. Ju gjithashtu mund të përdorni programe të tjera (për shembull, PHPMyAdmin). Ju mund të zgjidhni paketën e veglave që ju përshtatet më së miri.
Le të themi se tabela jonë duket si kjo:
KRIJO TABELA `paga` (`empid` int(11) NOT NULL, `sal` int(11) DEFAULT NULL, ÇELËSI KRYESOR (`empid`)) ENGINE=InnoDB KARKESË E DEFAULTUR=utf8;
Për punonjësin tonë që ka nevojë për informacion statistikor mbi pagat (mesatare, maksimale, minimale, etj.) nga kjo tabelë, ne do të krijojmë një përdorues "tr" si më poshtë:
KRIJO PERDORUES "tr"@"localhost" I IDENTIFIKUAR NGA "mypass";
Tani le t'i caktojmë këtij përdoruesi të vetmin privilegj EXECUTE në skemën ku ndodhet tabela e pagave:
Granto ekzekutimin në hris.* në tr@`%`
Ne mund të verifikojmë që kemi caktuar privilegjin e saktë duke hapur "Përdoruesit dhe privilegjet" në MySQL Bench:
Tani le të krijojmë vetë HP-në si më poshtë:
DELIMITER $$ KRIJO PROCEDURE `avg_sal` (nga mesatarja dhjetore) FILLO zgjidhni mesataren (sal) në mesatare nga paga; FUND
Pas ekzekutimit të kësaj komande në MySQL Workbench, do të krijohet një avg_sal HP gati për t'u përdorur. E kthen pagën mesatare nga tabela e pagave.
Për të kontrolluar nëse përdoruesi tr mund të ekzekutojë vërtet HP dhe nuk ka akses në tabelën e pagave, duhet të rilidhemi me serverin MySQL, duke u identifikuar si tr. Në MySQL Workbench, kjo mund të bëhet duke krijuar një lidhje tjetër dhe duke specifikuar përdoruesin e dëshiruar dhe fjalëkalimin e tij.
Pas lidhjes nga nën tr, gjëja e parë që vërejmë është se përdoruesi nuk sheh fare tabela, ai sheh vetëm HP:
Natyrisht, përdoruesi tr nuk mund të hyjë në asnjë nga tabelat (dhe për këtë arsye nuk mund të shohë informacion të detajuar rreth pagave nga tabela e pagave), por ai mund të drejtojë HP-në që kemi krijuar, e cila do t'i kthejë atij pagën mesatare për kompaninë:
Thirrni avg_sal(@out); zgjidhni @out;
Do të shfaqet paga mesatare.
Pra, ne kemi bërë të gjithë punën përgatitore: kemi krijuar një përdorues, i kemi caktuar privilegje, kemi krijuar një HP dhe e kemi testuar atë. Tani le të shohim se si ta quajmë këtë HP nga PHP.
Thirrja e një procedure të ruajtur nga PHP
Kur përdorni PDO, thirrja e HP-së është mjaft e thjeshtë. Këtu është kodi përkatës PHP:
$dbms = "mysql"; // Zëvendësoni parametrat e mëposhtëm të lidhjes me ato të përshtatshme për mjedisin tuaj: $host = "192.168.1.8"; $db = "hris"; $user = "tr"; $pass = "mypass"; $dsn = "$dbms:host=$host;dbname=$db"; $cn=PDO e re ($dsn, $user, $pass); $q=$cn->exec("thirrni avg_sal(@jashtë)"); $res=$cn->query("select @out")->fetchAll(); print_r($res);
Variabli $res përmban pagën mesatare nga tabela e pagave. Tani përdoruesi mund të kryejë përpunim të mëtejshëm të daljes duke përdorur PHP.
konkluzionet
Në këtë artikull, ne shikuam një komponent të harruar prej kohësh të bazave të të dhënave MySQL: procedurat e ruajtura. Përfitimet e përdorimit të HP janë të dukshme, por më lejoni t'ju kujtoj: Procedurat e ruajtura na lejojnë të aplikojmë kontroll më të rreptë të aksesit në të dhëna të caktuara kur logjika e biznesit e kërkon atë.
Përveç kësaj, ne demonstruam hapat bazë në krijimin e procedurave të ruajtura, përdoruesve dhe caktimin e privilegjeve të duhura dhe treguam se si thirret HP nga PHP.
Ky artikull nuk mbulon të gjithë temën e procedurave të ruajtura. Disa aspekte të rëndësishme si parametrat I/O, deklaratat e kontrollit, kursorët, sintaksa e plotë etj. nuk u trajtuan në këtë artikull të shkurtër.
Nëse jeni të interesuar, ju lutemi lini një koment këtu. Nëse është e nevojshme, ne do të jemi të lumtur të ofrojmë artikuj më të thelluar mbi aspektin e dobishëm dhe të fuqishëm të procedurave të ruajtura të MySQL.
Taylor Wren
Taylor është një zhvillues i pavarur i aplikacioneve në internet dhe desktop me bazë në Suzhou në Kinën lindore. Filloi me mjetet e zhvillimit Borland (C++Builder, Delphi), botoi një libër në InterBase. Që nga viti 2003 ai është ekspert i certifikuar i Borland. Pastaj kalova në zhvillimin e uebit në një konfigurim tipik LAMP. Më vonë fillova të punoj me jQuery, Symfony, Bootstrap, Dart etj.
Publikimet e mëparshme:
MySQL 5 ka shumë veçori të reja, një nga më të rëndësishmet prej të cilave është krijimi i procedurave të ruajtura. Në këtë tutorial, unë do të flas se cilat janë ato dhe si mund ta bëjnë jetën tuaj më të lehtë.
Prezantimi
Një procedurë e ruajtur është një mënyrë për të përmbledhur veprimet e përsëritura. Procedurat e ruajtura mund të deklarojnë variabla, të manipulojnë rrjedhën e të dhënave dhe të përdorin teknika të tjera programimi.
Arsyeja e krijimit të tyre është e qartë dhe konfirmohet nga përdorimi i shpeshtë. Nga ana tjetër, nëse flisni me ata që punojnë me ta në mënyrë të parregullt, mendimet do të ndahen në dy krahë krejtësisht të kundërt. Mos harroni këtë.
Mbrapa
- Ndarja e logjikës me aplikacione të tjera. Procedurat e ruajtura përmbledhin funksionalitetin; kjo siguron lidhje për aksesin dhe menaxhimin e të dhënave nëpër aplikacione të ndryshme.
- Izolimi i përdoruesve nga tabelat e bazës së të dhënave. Kjo ju lejon të jepni akses në procedurat e ruajtura, por jo në vetë të dhënat e tabelës.
- Ofron një mekanizëm mbrojtës. Sipas pikës së mëparshme, nëse mund t'i qaseni të dhënave vetëm përmes procedurave të ruajtura, askush tjetër nuk mund t'i fshijë të dhënat tuaja përmes komandës SQL DELETE.
- Përmirësimi i ekzekutimit si rezultat i trafikut të reduktuar të rrjetit. Duke përdorur procedurat e ruajtura, mund të kombinohen pyetje të shumta.
Kundër
- Rritja e ngarkesës në serverin e bazës së të dhënave për faktin se pjesa më e madhe e punës kryhet në anën e serverit, dhe më pak në anën e klientit.
- Do t'ju duhet të mësoni shumë. Ju do të duhet të mësoni sintaksën e shprehjes MySQL për të shkruar procedurat tuaja të ruajtura.
- Ju po dyfishoni logjikën e aplikacionit tuaj në dy vende: kodin e serverit dhe kodin për procedurat e ruajtura, duke e komplikuar kështu procesin e manipulimit të të dhënave.
- Migrimi nga një DBMS në tjetrin (DB2, SQL Server, etj.) mund të çojë në probleme.
Mjeti me të cilin punoj quhet MySQL Query Browser, i cili është mjaft standard për ndërveprim me bazat e të dhënave. Mjeti i linjës së komandës MySQL është një tjetër zgjedhje e shkëlqyer. Arsyeja që po ju them këtë është sepse phpMyAdmin i preferuar i të gjithëve nuk mbështet ekzekutimin e procedurave të ruajtura.
Nga rruga, unë jam duke përdorur një strukturë bazë të tabelës për ta bërë më të lehtë për ju të kuptoni këtë temë. Unë po flas për procedurat e ruajtura, dhe ato janë mjaft komplekse për të kërkuar thellimin në strukturën e rëndë të tabelës.
Hapi 1: Vendosni një kufizues
Një ndarës është një karakter ose varg karakteresh që përdoret për t'i treguar klientit MySQL që keni përfunduar së shkruari shprehjen SQL. Për shumë kohë, pikëpresja ka qenë kufizues. Megjithatë, mund të lindin probleme sepse mund të ketë shprehje të shumta në një procedurë të ruajtur, secila prej të cilave duhet të përfundojë me një pikëpresje. Në këtë tutorial përdor vargun "//" si kufitar.
Hapi 2: Si të punoni me procedurat e ruajtura
Krijimi i një procedure të ruajtur
KUFIZUES // KRIJO PROCEDURË `p2` () GJUHË SQL DETERMINISTIC SQL PËRCAKTOR I SIGURISË KOMENT "Një procedurë" FILLO ZGJIDH "Përshëndetje Botë!"; FUND//
Pjesa e parë e kodit krijon një procedurë të ruajtur. Tjetri përmban parametra opsionalë. Pastaj vjen emri dhe, së fundi, trupi i vetë procedurës.
Emrat e procedurave të ruajtura janë të ndjeshme ndaj shkronjave të vogla. Ju gjithashtu nuk mund të krijoni procedura të shumta me të njëjtin emër. Nuk mund të ketë shprehje brenda një procedure të ruajtur që modifikojnë vetë bazën e të dhënave.
4 karakteristikat e një procedure të ruajtur:
- Gjuha: Për qëllime transportueshmërie, parazgjedhja është SQL.
- Deterministik: nëse procedura kthen gjithmonë të njëjtin rezultat dhe merr të njëjtat parametra hyrës. Kjo është për procesin e përsëritjes dhe regjistrimit. Vlera e paracaktuar NUK është DETERMINISTIKE.
- SQL Security: të drejtat e përdoruesit kontrollohen gjatë telefonatës. INVOKER është përdoruesi që thërret procedurën e ruajtur. DEFINER është “krijuesi” i procedurës. Vlera e paracaktuar është DEFINER.
- Koment: Për qëllime dokumentacioni, vlera e paracaktuar është ""
Thirrja e një procedure të ruajtur
Për të thirrur një procedurë të ruajtur, duhet të shkruani fjalën kyçe CALL, e ndjekur nga emri i procedurës, e ndjekur nga parametrat (variablat ose vlerat) në kllapa. Kërkohen kllapa.
Thirrje e ruajtur_procedurës_emri (param1, param2, ....) Procedura e Thirrjes1(10 , "parametri i vargut" , @parameter_var);
Modifikimi i një procedure të ruajtur
MySQL ka një deklaratë ALTER PROCEDURE për ndryshimin e procedurave, por është e përshtatshme vetëm për ndryshimin e disa karakteristikave. Nëse keni nevojë të ndryshoni parametrat ose trupin e një procedure, duhet ta fshini dhe rikrijoni atë.
Heqja e një procedure të ruajtur
PROCEDURA E HEQJES NËSE EKZISTON p2;
Ky është një komandë e thjeshtë. Deklarata IF EXISTS kap një gabim nëse një procedurë e tillë nuk ekziston.
Hapi 3: Opsionet
Le të shohim se si mund t'i kalojmë parametrat në një procedurë të ruajtur.
- CREATE PROCEDURE proc1(): lista e parametrave bosh
- CREATE PROCEDURE proc1 (Në varname DATA-TYPE): një parametër hyrës. Fjala IN është opsionale sepse parametrat e paracaktuar janë IN (in).
- KRIJO PROCEDURËN proc1 (JASHTË emrit të varrit TË DHËNA-TYPE): u kthye një parametër.
- CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): një parametër, si hyrje ashtu edhe kthim.
Natyrisht, ju mund të specifikoni disa parametra të llojeve të ndryshme.
Shembull i parametrit IN
DELIMITER // KRIJO PROCEDURE `proc_IN` (IN var1 INT) FILLO ZGJIDH var1 + 2 SI rezultat; FUND//
Shembull parametri OUT
DELIMITER // KRIJO PROCEDURE `proc_OUT` (OUT var1 VARCHAR(100)) BEGIN SET var1 = "Ky është një test"; FUND //
Shembull i parametrit INOUT
KUFIZUES // KRIJO PROCEDURË `proc_INOUT` (OUT var1 INT) FILLO SET var1 = var1 * 2; FUND //
Hapi 4: Variablat
Tani do t'ju mësoj se si të krijoni variabla dhe t'i ruani ato brenda procedurave. Ju duhet t'i deklaroni ato në mënyrë eksplicite në fillim të bllokut BEGIN/FUND, së bashku me llojet e tyre të të dhënave. Pasi të keni deklaruar një variabël, mund ta përdorni atë në të njëjtën mënyrë si variablat e sesioneve, literalet ose emrat e kolonave.
Sintaksa e deklarimit të ndryshores duket si kjo:
DEKLARONI vlerën e paracaktuar të emrit të vargut DATA-TYPE DEFAULT;
Le të deklarojmë disa variabla:
DEKLARONI a, b NË PARALAKTUAR 5; DECLARE rr VARCHAR(50); DEKLAROJ sot TIMESTAMP DEFAULT CURRENT_DATE; DEKLAROJ v1, v2, v3 TINYINT;
Puna me variablat
Pasi të keni deklaruar një ndryshore, mund të vendosni vlerën e saj duke përdorur komandat SET ose SELECT:
DELIMITER // KRIJO PROCEDURE `var_proc` (NE paramstr VARCHAR(20)) FILLO DEKLAROJE a, b INT DEFAULT 5; DECLARE rr VARCHAR(50); DEKLAROJ sot TIMESTAMP DEFAULT CURRENT_DATE; DEKLAROJ v1, v2, v3 TINYINT; VLERA NË TABELËN 1 (a); SET str = "Unë jam një varg"; SELECT CONCAT(str,paramstr), sot NGA tabela2 WHERE b >=5; FUND //
Hapi 5: Strukturat e kontrollit të fijeve
MySQL mbështet konstruktet IF, CASE, ITERATE, LEAVE LOOP, WHILE dhe REPEAT për të kontrolluar thread-et brenda një procedure të ruajtur. Ne do të shohim se si të përdorim IF, CASE dhe WHILE pasi ato janë më të përdorurat.
IF dizajni
Duke përdorur konstruktin IF, ne mund të kryejmë detyra që përmbajnë kushte:
DELIMITER // CREATE PROCEDURE `proc_IF` (IN param1 INT) BEGIN DECLARE variable1 INT; Ndryshorja SET1 = param1 + 1; NËSE ndryshorja1 = 0 THËNË ZGJEDH variablin1; ENDIF; NËSE param1 = 0 THËNË ZGJEDH "Vlera e parametrit = 0"; ELSE SELECT "Vlera e parametrit<>0"; FUND NËSE; FUND //
Dizajn CASE
CASE është një metodë tjetër e testimit të kushteve dhe zgjedhjes së një zgjidhjeje të përshtatshme. Kjo është një mënyrë e shkëlqyer për të zëvendësuar shumë konstruksione IF. Konstrukti mund të përshkruhet në dy mënyra, duke siguruar fleksibilitet në menaxhimin e shprehjeve të shumta të kushtëzuara.
DELIMITER // KRIJO PROCEDURE `proc_CASE` (NE param1 INT) BEGIN DECLARE variable1 INT; Ndryshorja SET1 = param1 + 1; Ndryshorja CASE 1 WHEN 0 THE INSERT NE Table1 VALUES (param1); WWEN 1 THEND INSERT NË TABELËN 1 VLERAT (variabla1); TË TJETËR INSERT NË tabelën1 VLERAT (99); RASTI FUNDIT; FUND //
DELIMITER // KRIJO PROCEDURE `proc_CASE` (NE param1 INT) BEGIN DECLARE variable1 INT; Ndryshorja SET1 = param1 + 1; RASTI KUR variabli 1 = 0 PASTAJ INSERT NE tabelen1 VLERAT (param1); KUR variabli 1 = 1 ATHE SHTU INSERT NE tabelen1 VLERAT (variabli 1); TË TJETËR INSERT NË tabelën1 VLERAT (99); RASTI FUNDIT; FUND //
NDERSA dizajn
Teknikisht, ekzistojnë tre lloje sythe: cikli WHILE, cikli LOOP dhe cikli REPEAT. Ju gjithashtu mund të qarkulloni duke përdorur teknikën e programimit Darth Vader: deklaratat GOTO. Këtu është një lak shembull:
DELIMITER // CREATE PROCEDURE `proc_WHILE` (NE param1 INT) BEGIN DECLARE variable1, variable2 INT; Ndryshorja SET1 = 0; WHILE variabli1< param1 DO INSERT INTO table1 VALUES (param1); SELECT COUNT(*) INTO variable2 FROM table1; SET variable1 = variable1 + 1; END WHILE; END //
Hapi 6: Kursorët
Kursorët përdoren për të përshkuar grupin e rreshtave të kthyer nga një pyetje dhe për të përpunuar çdo rresht.
MySQL mbështet kursorët në procedurat e ruajtura. Këtu është një sintaksë e shkurtër për krijimin dhe përdorimin e një kursori.
DEKLAROJ emrin e kursorit KURSORIN PËR ZGJEDHJE ...; /*Deklarimi i kursorit dhe plotësimi i tij */ DEKLARONI VAZHDIM TRAJTUESI PER NUK FUND /*Çfarë duhet bërë kur nuk ka më regjistrime*/ HAPE EMRI i kursorit; /*Hap kursorin*/ FETCH emri i kursorit INTO variabli [, ndryshore]; /*Cakto një vlerë për një variabël të barabartë me vlerën aktuale të kolonës*/ CLOSE cursor-name; /*Mbyll kursorin*/
Në këtë shembull ne do të kryejmë disa operacione të thjeshta duke përdorur një kursor:
KUFIZUES // KRIJO PROCEDURE `proc_CURSOR` (OUT param1 INT) FILLO DEKLARONI a, b, c INT; DEKLAROJE KURSORIN PER ZGJEDHJE col1 NGA tabela1; DEKLARONI VAZHDIM TRAJTUESI PËR SETIN NUK GJETUR b = 1; HAPUR cur1; SET b = 0; SET c = 0; NDËRSA b = 0 SHKONI kur1 NE a; NËSE b = 0 ATEHERE SET C = c + a; ENDIF; FUND DERI; MBYLL cur1; SET param1 = c; FUND //
Kursorët kanë tre veti që duhet t'i kuptoni për të shmangur marrjen e rezultateve të papritura:
- Jo i ndjeshëm: kursori që hapet një herë nuk do të pasqyrojë ndryshimet në tabelë që ndodhin më vonë. Në realitet, MySQL nuk garanton që kursori do të përditësohet, prandaj mos u mbështetni në të.
- Vetëm për lexim: Kursorët nuk mund të modifikohen.
- Nuk ka kthim prapa: kursori mund të lëvizë vetëm në një drejtim - përpara, nuk do të mund të kaloni rreshta pa i zgjedhur ato.
konkluzioni
Në këtë tutorial, ju njoha me bazat e punës me procedurat e ruajtura dhe disa nga vetitë specifike që lidhen me të. Sigurisht, do t'ju duhet të thelloni njohuritë tuaja në fusha të tilla si siguria, shprehjet SQL dhe optimizimi përpara se të bëheni një mësues i vërtetë i procedurës MySQL.
Ju duhet të llogaritni përfitimet e përdorimit të procedurave të ruajtura në aplikacionin tuaj specifik dhe më pas të krijoni vetëm procedurat e nevojshme. Në përgjithësi, unë përdor procedura; Sipas mendimit tim, ato ia vlen të zbatohen në projekte për shkak të sigurisë së tyre, mirëmbajtjes së kodit dhe dizajnit të përgjithshëm. Gjithashtu, mbani në mend se procedurat MySQL janë ende një punë në progres. Prisni përmirësime në lidhje me funksionalitetin dhe përmirësimet. Ju lutem mos ngurroni të ndani mendimet tuaja.
Kujdes! Kjo vepër bazohet në përkthimin e seksionit “17.1. Rutinat e ruajtura dhe tabelat e granteve" përshkrim i softuerit MySQL 5.0.19, "Manual referencë. Dokumenton MySQL 5.0 deri në 5.0.19. Dokument i krijuar më: 2006-01-23 (rishikimi:995)"
"Lexoni gjithçka së pari, pastaj provoni shembujt."
Procedurat e ruajtura janë një grup komandash SQL që mund të kompilohen dhe ruhen në një server. Në këtë mënyrë, në vend që të ruajnë një pyetje të përdorur shpesh, klientët mund të referojnë procedurën përkatëse të ruajtur. Kjo siguron performancë më të mirë sepse një kërkesë e caktuar duhet të analizohet vetëm një herë dhe redukton trafikun midis serverit dhe klientit. Niveli konceptual gjithashtu mund të rritet duke krijuar një bibliotekë funksionesh në server.
Një nxitës është një procedurë e ruajtur që ekzekutohet kur ndodh një ngjarje specifike. Për shembull, mund të përcaktoni një procedurë të ruajtur që ekzekutohet çdo herë që një rekord fshihet nga një tabelë transaksionesh, duke siguruar kështu që klienti përkatës të fshihet automatikisht nga tabela e klientit kur të gjitha transaksionet e tij fshihen.
Programet e ruajtura (procedurat dhe funksionet) mbështeten në MySQL 5.0. Procedurat e ruajtura janë një grup shprehjesh SQL që mund të ruhen në server. Pasi të bëhet kjo, klienti nuk ka më nevojë të ritransmetojë kërkesën, por thjesht thërret programin e ruajtur.
Kjo mund të jetë e dobishme kur:
- Aplikacione të shumta klientësh janë shkruar në gjuhë të ndryshme ose ekzekutohen në platforma të ndryshme, por duhet të përdorin të njëjtën bazë të dhënash transaksionesh
- siguria vjen e para
Procedurat dhe funksionet e ruajtura (rutinat) mund të ofrojnë performancë më të mirë sepse kërkohet më pak informacion për t'u dërguar midis klientit dhe serverit. Zgjedhja rrit ngarkesën në serverin e bazës së të dhënave, por zvogëlon kostot nga ana e klientit. Përdoreni këtë nëse shumë makineri klientësh (siç janë serverët e uebit) shërbehen nga një ose më shumë baza të dhënash.
Rutinat e ruajtura ju lejojnë gjithashtu të përdorni bibliotekat e funksioneve të ruajtura në bazën e të dhënave të serverit. Kjo veçori ofrohet për shumë gjuhë programimi moderne, të cilat i lejojnë ato të thirren drejtpërdrejt (për shembull, duke përdorur klasa).
MySQL ndjek sintaksën e SQL:2003 për procedurat e ruajtura, e cila përdoret tashmë në DB2 të IBM.
Nga fjalët tek veprat...
Kur krijon, modifikon, fshin rutinat e ruajtura, serveri manipulon tabelën mysql.proc
Që nga MySQL 5.0.3 kërkohen privilegjet e mëposhtme:
KRIJO RUTINË për të krijuar procedura të ruajtura
Ndrysho rutinën të nevojshme për të ndryshuar ose fshirë procedurat. Ky privilegj i caktohet automatikisht krijuesit të procedurës (funksionit)
EKZEKUTOHET do të kërkohet privilegj për të ekzekutuar nënprogramin. Megjithatë, ai i caktohet automatikisht krijuesit të procedurës (funksionit). Gjithashtu, si parazgjedhje, parametri SQL SECURITY për rutinën DEFINER, i cili lejon përdoruesit me akses në bazën e të dhënave të thërrasin rutina të lidhura me këtë bazë të dhënash.
Sintaksa e procedurave dhe funksioneve të ruajtura
Një rutinë e ruajtur është një procedurë ose funksion. Rutinat e ruajtura krijohen duke përdorur deklaratat CREATE PROCEDURE ose CREATE FUNCTION. Një rutinë e ruajtur thirret duke përdorur një deklaratë CALL, me vetëm variablat që kthejnë vlerën që përdoren si variabla dalëse. Funksioni mund të thirret si çdo funksion tjetër dhe mund të kthejë një vlerë skalare. Rutinat e ruajtura mund të thërrasin rutina të tjera të ruajtura.
Që nga MySQL 5.0.1, procedura ose funksioni i ngarkuar shoqërohet me një bazë të dhënash specifike. Kjo ka disa kuptime:
- Kur thirret një nënprogram, qëllimi është të telefononi USE db_name (dhe të hidhni poshtë bazën e të dhënave kur nënprogrami të ketë përfunduar dhe baza e të dhënave nuk është më e nevojshme)
- Ju mund të kualifikoni emrat e rregullt me një emër të bazës së të dhënave. Kjo mund të përdoret për të referuar një rutinë që nuk është në bazën aktuale të të dhënave. Për shembull, për të ekzekutuar një procedurë të ruajtur p ose një funksion f që është i lidhur me bazën e të dhënave të testit, mund t'i tregoni interpretuesit të komandës si kjo: CALL test.p() ose test.f().
- Kur një bazë të dhënash fshihet, të gjitha rutinat e ngarkuara që lidhen me të fshihen gjithashtu. Në MySQL 5.0.0, rutinat e ngarkuara janë globale dhe nuk lidhen me bazën e të dhënave. Ata trashëgojnë bazën e të dhënave të paracaktuar nga deklarata thirrëse. Nëse USE db_name kryhet brenda një nënprogrami, baza e të dhënave origjinale aktuale do të rikthehet pas daljes nga nënprogrami (Për shembull, baza aktuale e të dhënave është db_11, ne quajmë një nënprogram që përdor db_22, pas daljes nga nënprogrami mbetet db_11 aktuale)
MySQL mbështet plotësisht shtesat që ju lejojnë të përdorni shprehje të rregullta SELECT (pa përdorur kursorë ose ndryshore lokale) brenda procedurave të ruajtura. Seti i rezultateve i kthyer nga pyetja thjesht i dërgohet direkt klientit. Një pyetje e shumëfishtë SELECT gjeneron grupe të shumta rezultatesh, kështu që klienti duhet të përdorë një bibliotekë që mbështet grupe të shumta rezultatesh.
KRIJO PROCEDURA- krijoni një procedurë të ruajtur.
KRIJO FUNKSION- krijoni një funksion të ruajtur.
Sintaksë:
KRIJO PROCEDURA emri_procedurës ([parametri_procedurës[,...]])[karakteristikat...] nënrutinë_trup
KRIJO FUNKSION emri_funksionit ([parametri_funksioni[,...]])
KTHIMET lloji
[karakteristikë...] nënprogram_trup
procedura_parametri:
[ NË | JASHTË | BRENDA JASHTE] lloji i emrit të parametrit
funksioni_parametri:
lloji i emrit të parametrit
lloji:
Çdo lloj të dhënash MySQL
karakteristike:
GJUHA SQL
| DETERMINISTIKE
| ( PËRMBAN SQL | JO SQL | LEXON TË DHËNAT SQL | MODIFIkon TË DHËNAT SQL )
| SQL SIGURIA (DEFINER | INVOKER)
| KOMENT "string"
nënprogrami_trupi:
Shprehja e saktë SQL.
Le të shohim gjithçka në praktikë.
Së pari, le të krijojmë një procedurë të ruajtur me pyetjen e mëposhtme:
KRIJO PROCEDURE `my_proc`(JALË T INTEGER(11))
JO DETERMINISTE
INVOKER SQL SECURITY
KOMENT ""
FILLOJ
zgjidhni val1+val2 në "t" nga "my" LIMIT 0,1;
FUND;
Përdorimi i shprehjes LIMIT në këtë pyetje është bërë në njohjen e faktit që jo çdo klient është në gjendje të pranojë një grup rezultatesh me shumë rreshta.
Pas kësaj, le ta quajmë atë:
THIRRJE my_proc(@a);
ZGJIDH @a;
Për të ndarë një kërkesë të brendshme nga një e jashtme, përdoret gjithmonë një ndarës i ndryshëm nga ai i zakonshëm (për detyrën, përdorni komandën DELIMITER <строка/символ>)
Këtu është një shembull tjetër duke marrë parasysh të gjitha kërkesat.
Mysql> delimiter //
mysql> KRIJO PROCEDURE simpleproc (OUT param1 INT)
-> FILLO
-> SELECT COUNT(*) INTO param1 FROM t;
->FUND;
-> //
mysql>kufizues;
mysql> THIRRJE simpleproc(@a);
Kërkesa në rregull, 0 rreshta të prekur (0,00 sek)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 rresht në grup (0,00 sek)
I gjithë procesi mund të shihet në figurën më poshtë:
Shkaqet
Mbështetja e aktivizimit ka qenë e disponueshme në MySQL që nga versioni 5.0.2.
Kthesë- një objekt i emërtuar i bazës së të dhënave që shoqërohet me një tabelë dhe aktivizohet kur ndodh një ngjarje e caktuar, një ngjarje e lidhur me këtë tabelë.
Për shembull, kodi i mëposhtëm krijon një tabelë dhe një shkas INSERT. Shkaku përmbledh vlerat e futura në një nga kolonat e tabelës.
Mysql> CREATE TABLE account (acct_num INT, shuma DECIMAL(10,2));
Kërkesa në rregull, 0 rreshta të prekur (0,03 sek)
mysql> CREATE TRIGGER ins_sum PARA INSERT ON llogarinë
-> PËR ÇDO RRESHT SET @sum = @sum + NEW.samount;
Kërkesa në rregull, 0 rreshta të prekur (0,06 sek)
Le të deklarojmë variablin shuma dhe t'i caktojmë vlerën 1. Pas kësaj, sa herë që futim në tabelë llogari vlera e kësaj variabli do të rritet sipas pjesës së futur.
Koment. Nëse vlera e ndryshores nuk është inicializuar, këmbëza nuk do të funksionojë!
Sintaksë për krijimin e një shkas
KRIJONISHKËZUES trigger_name trigger_time trigger_event
ON tabela_emri PËR ÇDO RRESHT shprehje_ekzekutuar_kur_trigger_fires
Nëse gjithçka është e qartë menjëherë me emrin e aktivizimit dhe emrin e përdoruesit, atëherë do të flasim veçmas për "kohën e nxitjes" dhe "ngjarjen".
koha_shkakëzimi
Përcakton kohën në të cilën ndodh veprimi i nxitjes. PARA do të thotë që këmbëza do të ekzekutohet përpara se të përfundojë ngjarja e nxitjes, dhe AFTER do të thotë pas. Për shembull, kur futni rekorde (shih shembullin më lart), këmbëza jonë u ndez përpara futjes aktuale të rekordit dhe llogariti shumën. Ky opsion është i përshtatshëm për llogaritjen paraprake të disa fushave shtesë në tabelë ose futjen paralele në një tabelë tjetër.
shkaktari_ngjarja
Gjithçka është më e thjeshtë këtu. Ai tregon qartë se në cilën ngjarje ekzekutohet këmbëza.
- INSERT: d.m.th. gjatë operacioneve të futjes ose shprehjeve të ngjashme (INSERT, LOAD DATA dhe REPLACE)
- UPDATE: kur modifikohet një entitet (rresht).
- FSHIJE: Kur një regjistrim fshihet (pyetjet që përmbajnë deklarata DELETE dhe/ose REPLACE)
