Izinqubo Ezigciniwe ku-MySQL: Umhlahlandlela Ophelele

Informatec Digital » Yolwazi » Izinqubo Ezigciniwe ku-MySQL: Umhlahlandlela Ophelele

Yiziphi izinqubo ezigcinwe ku-MySQL?

Izinqubo ezigciniwe ku-MySQL ziwukulandelana kwemibhalo noma amabhulokhi ekhodi ye-SQL agcinwa kuseva yedathabhesi futhi asetshenziswe lapho ecelwa. Ziyindlela enamandla yokuqoqa izitatimende ze-SQL ezihlobene zibe iyunithi enengqondo, esebenziseka kabusha. I izinqubo ezigciniwe Zikuvumela ukuthi wenze lula uhlelo lokuhlela, uthuthukise ukusebenza futhi ukhuphule ukuphepha kwesizindalwazi.

Izinzuzo zokusebenzisa izinqubo ezigciniwe

Izinqubo ezigciniwe zinikeza izinzuzo eziningi ekuthuthukisweni kwesicelo kanye nokuphathwa kwesizindalwazi. Ezinye zezinzuzo eziyinhloko yilezi:

1. I-modularity kanye nokusetshenziswa kabusha kwekhodiIzinqubo ezigciniwe zikuvumela ukuthi uqoqe izitatimende ze-SQL ezihlobene zibe iyunithi enengqondo, okwenza kube lula ukuphinda uzisebenzise ezingxenyeni ezihlukene zohlelo lokusebenza. Lokhu kuthuthukisa ukugcinwa kwekhodi futhi kunciphisa ukuphindaphinda kwekhodi.
2. Ukusebenza kangcono: Lapho ibulawa ku iseva yedathabheyisi Ukugcina idatha, izinqubo ezigciniwe zigwema isidingo sokuthumela imibuzo eminingi kusuka kuhlelo lokusebenza lweklayenti. Lokhu kunciphisa ithrafikhi yenethiwekhi futhi kuthuthukisa ukusebenza kohlelo lokusebenza jikelele.
3. UkuphephaIzinqubo ezigciniwe zingasetshenziswa ukulawula ukufinyelela kudatha nokuphoqelela imithetho ethile yokuphepha. Izimvume zokwenza zezinqubo ezigciniwe zingabelwa izindima zabasebenzisi, zinikeze izinga elingeziwe lokuphepha.
4. ukuncishiswa kwephuthaNgokuhlanganisa ingqondo yokuhlela ezinqubweni ezigciniwe, wehlisa ithuba lokwenza amaphutha kuhlelo lwakho lokusebenza. Lokhu kungenxa yokuthi izinqubo ezigciniwe ziyahlolwa futhi zilungiswe kanye, futhi zingasetshenziswa izinhlelo zokusebenza eziningi ngaphandle kokulungisa ikhodi yomthombo.

Ukudala Izinqubo Ezigciniwe

Ukudala izinqubo ezigciniwe ku-MySQL kuyinqubo elula. Ukwakha inqubo egciniwe, isitatimende sisetshenziswa CREATE PROCEDURE. Ngezansi isibonelo esiyisisekelo sokudala inqubo egciniwe ebonisa wonke amarekhodi kuthebula:

DALA INQUBO sp_show_records()
QALA
KHETHA * KUSUKA etafuleni;
Ukuphela

Kulesi sibonelo, sp_mostrar_registros igama lenqubo egciniwe. Ibhulokhi BEGIN y END ichaza umzimba wenqubo, kulokhu okuqukethe umbuzo olula SELECT ukuze ubonise wonke amarekhodi kuthebula eliqanjwe "ithebula".

Amapharamitha ezinqubweni ezigciniwe

Izinqubo ezigciniwe zingamukela amapharamitha, okubavumela ukuthi bathole amanani angaphandle ngesikhathi sokubulawa. Amapharamitha achazwa esimemezelweni senqubo futhi asetshenziswa ngaphakathi kwendikimba yenqubo. Ngezansi kunesibonelo senqubo egciniwe eyamukela amapharamitha amabili futhi yenze a umbuzo onemibandela:

DALA INKQUBO sp_search_product(Egameni elithi VARCHAR(50), NGEnani elingu-DECIMAL(8,2))
QALA
KHETHA * KUSUKA emikhiqizweni LAPHO igama NJENGE-CONCAT('%', igama, '%') KANYE nentengo <= intengo;
Ukuphela

Kulesi sibonelo, inqubo egcinwe sp_buscar_producto yamukela amapharamitha amabili: nombre y precio. Ukubonisana SELECT Endikimbeni yenqubo, sebenzisa le mingcele ukuze uhlunge amarekhodi kuthebula "lemikhiqizo" ngokusekelwe egameni eliyingxenye kanye nenani eliphezulu.

Okuguquguqukayo kwendawo nokulawula ukugeleza

Izinqubo ezigciniwe ku-MySQL zingasebenzisa okuguquguqukayo kwendawo kanye nezakhiwo zokulawula ukugeleza ezifana nezimo nezihibe. Lokhu kuvumela imisebenzi eyinkimbinkimbi futhi enemibandela ngaphakathi kwezinqubo ezigciniwe. Ngezansi isibonelo senqubo egciniwe esebenzisa okuguquguqukayo nokulawula ukugeleza:

DALA INKQUBO sp_actualizar_stock(IN producto_id INT, IN cantidad INT)
QALA
DECLARE stock_actual INT;

KHETHA isitoko UBE YI-current_stock FROM imikhiqizo LAPHO i-id = product_id;

IF stock_actual >= quantity KE
BUYEKEZA imikhiqizo SET stock = stock – quantity LAPHO id = product_id;
KUNYE
KHETHA 'Isitoko esinganele' NJENGOBA umlayezo;
PHELA UMA;
Ukuphela

Kulesi sibonelo, inqubo egcinwe sp_actualizar_stock ithatha amapharamitha amabili: producto_id y cantidad. Isebenzisa okuguquguqukayo kwendawo okubizwa ngokuthi stock_actual ukugcina inani lamanje lesitoko somkhiqizo. Bese usebenzisa isakhiwo sokulawula IF ukuhlola ukuthi isitoko sanele yini futhi wenze isibuyekezo kuthebula "lemikhiqizo" noma ubonise umlayezo wephutha ngenye indlela.

Imisebenzi ezinqubweni ezigciniwe

Ngokungeziwe ekusebenziseni imibuzo ye-SQL, izinqubo ezigciniwe ku-MySQL nazo zingakwazi ziqukethe imisebenzi. Imisebenzi ikuvumela ukuthi wenze izibalo futhi ubuyisele amanani esikhundleni sokubonisa imiphumela yemibuzo. Ngezansi isibonelo senqubo egciniwe esebenzisa umsebenzi ukubala inani lentengo ye-oda:

DALA UMSEBENZI fn_calculate_total_price(order_id INT) UBUYISA IDECIMAL(8,2)
QALA
IMEMEZA ingqikithi yeDECIMAL(8,2);

KHETHA IMINININGWANE(inani * inani) IBE IYIngqikithi FROM order_detail LAPHO order_id = order_id;

BUYISA isamba;
Ukuphela

Kulesi sibonelo, inqubo egciniwe iqukethe umsebenzi okuthiwa fn_calcular_precio_total eyamukela ipharamitha pedido_id futhi ibuyisela inani lentengo ye-oda. Umsebenzi usebenzisa okuguquguqukayo kwasendaweni total ukugcina umphumela wesibalo bese uwubuyisela usebenzisa isitatimende RETURN.

Izinqubo Ezigciniwe Ezichazwe Ngaphambilini

I-MySQL inikeza inani lezinqubo ezigciniwe ezichazwe ngaphambilini ezihlanganisa imisebenzi eminingi evamile. Lezi zinqubo egcinwe kungaba esetshenziswa ngqo ezisekelweni idatha ngaphandle kokuthi ubhale ikhodi eyengeziwe. Ezinye zezinqubo ezivame ukusetshenziswa ezichazwe ngaphambili yilezi:

• COUNT(): Ibuyisela inombolo yemigqa efana nesimo esishiwo.
• SUM(): Ibala isamba samanani kukholamu eshiwo.
• AVG(): Ibala isilinganiso samanani kukholamu eshiwo.
• MAX(): Ibuyisela inani eliphakeme kukholomu eshiwo.
• MIN(): Ibuyisela inani elincane kukholomu eshiwo.

Lezi zinqubo ezigciniwe ezichazwe ngaphambilini zenzelwe kahle kakhulu futhi zinikeza ukusebenza okuthuthukisiwe uma kuqhathaniswa nokubhala imibuzo efanayo ye-SQL kusukela ekuqaleni.

Izimvume zokuvikeleka nokusebenza

Ku-MySQL, izimvume zokusebenza zezinqubo ezigciniwe zingabelwa izindima zomsebenzisi. Lokhu kukuvumela ukuthi ulawule ukufinyelela ezinkambisweni ezigciniwe futhi uvikele i- ubuqotho nokuvikeleka kwedatha. Izimvume zokukhipha ziphathwa ngohlelo lomsebenzisi we-MySQL kanye nelungelo lokuphatha.

Kubalulekile ukunikeza izimvume zokusebenza zezinqubo ezigcinwe ngokufanelekile ukuze kuvinjelwe ukufinyelela okungagunyaziwe nokuqinisekisa ukugcinwa kuyimfihlo kwedatha. Abaphathi be yolwazi Kufanele banikeze izimvume zokusebenza ngokukhawulelwe futhi ngezikhathi ezithile babuyekeze amalungelo anikezwe abasebenzisi ukuze bagcine indawo evikelekile.

Ukuthuthukisa izinqubo ezigciniwe

Ukuze uqinisekise ukusebenza kahle, kubalulekile ukuthuthukisa izinqubo ezigcinwe ku-MySQL. Amanye amasu okuthuthukisa ajwayelekile ahlanganisa:

1. Ukusebenzisa izinkomba: Izinkomba kumakholomu asetshenziswa emibuzweni ngaphakathi kwezinqubo ezigciniwe zingathuthukisa kakhulu ukusebenza. Izinkomba zisheshisa ukusesha nokubuyisa idatha ngokunciphisa isikhathi sokwenziwa kwezinqubo ezigciniwe.
2. Nciphisa ukusetshenziswa kwezikhombisi: Ukusetshenziswa ngokweqile kwezikhombisi kungaba nomthelela omubi ekusebenzeni kwezinqubo ezigciniwe. Esikhundleni salokho, kunconywa ukusebenzisa imisebenzi emisiwe efana JOIN y GROUP BY ukukhohlisa idatha esikhundleni sokungena emigqeni ngayinye ngayinye.
3. Gwema ukusebenzisa ngokweqile imibuzo engezansi: Imibuzo engaphansi ingaba usizo ezimeni ezithile, kodwa ukuzisebenzisa ngokweqile kungaholela ekusebenzeni kabi. Kunalokho, kunconywa ukusebenzisa JOIN y GROUP BY ukuhlanganisa ngempumelelo nokuphatha idatha.
4. Yenza izivivinyo kanye nokulungiswa: Kubalulekile ukwenza ukuhlolwa kokusebenza okuphelele nokushuna izinqubo ezigciniwe njengoba kudingeka. Lokhu kuhlanganisa ukuhlonza izingqinamba, ukulinganisa izikhathi zokwenziwa, kanye nokwenza izinguquko kumklamo noma kunengqondo yenqubo ukuze kuthuthukiswe ukusebenza.

Isiphetho

Sengiphetha, izinqubo ezigciniwe ku-MySQL ziyithuluzi elinamandla lokwenza lula ukuhleleka kwezinhlelo, ukuthuthukisa ukusebenza, nokukhulisa ukuphepha ekuthuthukisweni kohlelo lokusebenza kanye nokuphathwa kwesizindalwazi. Bavumela ukuqoqa izitatimende ze-SQL ezihlobene zibe iyunithi enengqondo nesebenzisekayo, enikeza izinzuzo ezifana nemodularity, ukusetshenziswa kabusha kwekhodi, ukusebenza okungcono nokuphepha.

Ukudala izinqubo ezigciniwe ku-MySQL kulula, usebenzisa isitatimende CREATE PROCEDURE, futhi ingamukela imingcele ukuze ivumelane nezimo ezihlukahlukene. Ukwengeza, izinqubo ezigciniwe zingaqukatha okuguquguqukayo kwasendaweni, ukulawulwa kokugeleza, nemisebenzi, okuyenza iguquguquke nakakhulu futhi ibe namandla.

Kubalulekile ukuthuthukisa izinqubo ezigciniwe usebenzisa amasu afana nokusebenzisa izinkomba, ukukhawulela ukusetshenziswa kwamakhesa nama-subqueries, kanye nokwenza ukuhlola kokusebenza okuphelele. Lokhu kuqinisekisa ukusebenza okuphezulu nolwazi olusebenzayo kubasebenzisi bokugcina.

Kafushane, izinqubo ezigciniwe ku-MySQL ziyithuluzi elibalulekile labathuthukisi nabaphathi besizindalwazi, futhi ukuqaliswa kwazo okulungile nokwenziwa kahle kungenza umehluko ekusebenzeni nasekuvikelekeni kwezinhlelo zokusebenza nezinhlelo zesizindalwazi.