Tsare-tsaren Ajiye a cikin MySQL: Yaya ake amfani da su?

Informatec Digital » Databases » Tsare-tsaren Ajiye a cikin MySQL: Yaya ake amfani da su?

Hanyoyin da aka adana su ne abubuwan yau da kullun da aka adana a cikin bayanan MySQL waɗanda ke ɗaukar ayyukan SQL da dabaru na shirye-shirye. Suna aiki azaman ƙananan shirye-shirye waɗanda za a iya kiran su don yin ayyuka masu maimaitawa ko hadaddun ayyuka.

Suna ba da fa'idodi da yawa kamar modularity, sake amfani da lambar, babban tsaro da ingantaccen aiki. Don haka, sarrafa hanyoyin da aka adana yana da mahimmanci ga kowane mai haɓaka MySQL ko mai gudanarwa.

A ƙasa za mu ga menene ainihin su, fa'idodin su, yadda ake ƙirƙira da amfani da hanyoyin da aka adana a cikin MySQL mataki-mataki da wasu misalai masu amfani don ku sami mafi kyawun su.

Menene hanyoyin da aka adana a cikin MySQL?

Hanyoyin da aka adana sune saitin maganganun SQL da aka riga aka tattara waɗanda aka adana a cikin MySQL database tare da suna da shigar da sigogi na fitarwa.

Ana iya kiran su da suna ta hanyar ƙaddamar da ƙima don sigogin su. Lokacin da aka kashe su, suna aiwatar da ayyukan da aka ayyana a cikinsu, kamar tambayoyi, sabuntawa, dabarun sarrafa kwarara, da ƙari.

Suna kama da ƙananan shirye-shiryen da ke zaune a cikin bayanan da ke tattare da hadaddun ayyuka don:

• Sauƙaƙe ayyukan gudanarwa masu maimaitawa
• Tsaya dabarun kasuwanci akan sabar
• Rage zirga-zirgar abokin ciniki/server
• Inganta aiki lokacin da ake harhada tambaya

Lokacin da aka adana akan uwar garken, suna samar da abubuwan da ake so kamar:

• Yanci: Suna rarrabuwar hankali zuwa ɓangarorin da za a iya sarrafa su.
• Sake amfani: Cire kwafin lambar.
• Tsaro: Izinin matakin tsari.
• Boye dabaru: Abokin ciniki baya ganin cikakkun bayanai na ciki.

A takaice, hanyoyin da aka adana kayan aiki ne masu amfani sosai ga masu haɓaka MySQL. Bari mu dubi manyan fa'idodinsa dalla-dalla.

Amfanin amfani da hanyoyin da aka adana

Hanyoyin da aka adana suna ba da fa'idodi masu mahimmanci:

1. Kyakkyawan tsari da kiyaye lambar

Suna ba da izinin daidaita ayyukan hadaddun ayyuka zuwa sassa daban-daban, masu sauƙin sarrafawa. Ana iya sake amfani da lambar daga wurare da yawa ta hanyar kiran tsarin da aka adana.

Wannan yana haifar da ƙarin tsari, lambar da za a iya kiyayewa tare da ƙarancin kwafi.

2. Rage zirga-zirga tsakanin abokin ciniki da uwar garken

Ta hanyar adanawa akan uwar garken, an rage canja wurin bayanai tunda ba lallai ba ne a aika tambayoyin SQL kowane lokaci. Wannan yana inganta aiki.

3. Saurin ayyuka da ma'amaloli

MySQL kawai yana tattara tsarin sau ɗaya kuma yana sake amfani da shi. Wannan yana hanzarta aiwatar da kisa ta hanyar kawar da buƙatar sake tarawa kowane lokaci.

Bugu da ƙari, ana iya amfani da su don ƙaddamar da hadaddun ma'amaloli waɗanda za su aiwatar da sauri.

4. Ƙara tsaro da izini

Ana iya ba da izini da gata a matakin tsarin da aka adana, maimakon ba da cikakkiyar dama ga teburin bayanai.

Wannan yana ba da damar ƙarin iko akan bayanai da ayyukan da masu amfani zasu iya yi.

5. Sauƙi don amfani da kulawa da sauran masu haɓakawa

Tunda an raba su a cikin ma'ajin bayanai, kowane mai haɓakawa tare da izini na iya aiwatar da hanyoyin ba tare da sanin lambar su ta ciki ba. Don zurfafa zurfafa cikin sarrafa izini, duba masu amfani a cikin MySQL.

Wannan yana sauƙaƙe kulawa kuma yana rage kurakurai.

6. Zazzagewa tsakanin aikace-aikace daban-daban

Tunda dabarar sa tana cikin ma’adanar bayanai, ana iya kiranta daga kowane yaren shirye-shiryen da ke goyan bayansa, gami da Ƙirƙiri ayyuka a cikin MySQL.

Yadda ake ƙirƙirar hanyoyin da aka adana a cikin MySQL

Ƙirƙirar hanyar da aka adana a cikin MySQL abu ne mai sauƙi ta amfani da SQL syntax:

Ƙirƙiri tsari_suna ()
GAME
umarni;
KARSHEN

An ƙirƙiri hanyar tare da suna na musamman kuma tsakanin BEGIN – END za mu sanya maganganun SQL don aiwatarwa lokacin da aka kira shi.

Misali, wannan hanyar tana yin rikodin sabon siyarwa:

Ƙirƙiri tsari saka_sale(
p_darajar FLOAT,
abokin ciniki VARCHAR (50)
)
GAME
SHIGA tallace-tallace (darajar, abokin ciniki)
VALUES(p_value, p_client);
KARSHEN

Wannan hanya tana karɓar sigogi p_value da p_customer, saka sabon siyarwa a cikin teburin tallace-tallace kuma ya dawo da id ɗin da aka ƙirƙira ta atomatik. Idan kuna son faɗaɗa ilimin ku game da sarrafa bayanai, duba Tambayar MySQL a cikin PHP tare da misalai.

Tsarin kuma yana goyan bayan ƙarin ci-gaba lambar kamar masu canji na gida, siginan kwamfuta, sarrafa kuskure tare da BEGIN-EXCEPTION-END, da sauransu.

Yadda ake amfani da hanyoyin da aka adana a cikin MySQL

Da zarar an ƙirƙiri hanyar da aka adana, ana iya kiranta a kowace tambaya ta SQL ta amfani da KIRA:

KIRA tsarin_suna (parameter_value1, parameter_value2);

Misali, don saka siyarwa:

CALL saka_sale (125.50, 'Juan Perez');

Hakanan ana iya kiran tsari daga kowane yaren shirye-shirye ta hanyar ƙaddamar da ƙima zuwa sigogi. Alal misali, a cikin PHP:

$ sakamako = $ mysqli-> tambaya ("KIRAN saka_sale (125.50, 'Juan Perez')");

Kuma daga Java:

CallableStatement stmt = conn.prepareCall("{KIRAN saka_sale(?,?)}");
stmt.setDouble (1, 125.50);
stmt.setString (2, "John Perez");
stmt.executeUpdate();

Misalan hanyoyin da aka adana a cikin MySQL

Bari mu kalli wasu misalai masu amfani don ingantaccen amfani da wannan fasalin MySQL mai ƙarfi:

Sami jimlar tallace-tallace kowane abokin ciniki

Ƙirƙiri tsari na jimlar_sales_abokin ciniki(IN p_abokin ciniki VARCHAR(50))
GAME
SELECT SUM(darajar) AS jimlar
DAGA tallace-tallace
INA abokin ciniki = p_abokin ciniki;
KARSHEN

Yi amfani da:

CALL jimlar_sales_abokin ciniki('Ana Valdez');

Jerin samfuran ƙarƙashin farashin da aka bayar

Ƙirƙiri tsari mai arha (IN p_price DECIMAL(10,2))
GAME
Zabi id, suna, farashi DAGA samfur
INA farashin <= p_price;
KARSHEN

Yi amfani da:

KIRA samfuran arha (500);

Share kuma sake saka bayanan

Ƙirƙiri tsari na sake haɓaka_tebur()
GAME
GARE DAGA tebur na;
- Saka sabbin bayanai
END;

Mai amfani don sabunta teburin gwaji. Don ƙarin bayani kan tsarin bayanai, ziyarci abubuwan bayanai.

Kira wata hanya ta ciki

Ƙirƙiri tsari na ()
GAME
KIRA sauran_tsari();

- ƙarin umarni
END;

Yana ba ku damar rarrabuwa zuwa sassa masu sake amfani da su.

Canja wurin kuɗi tsakanin asusun

Ƙirƙiri hanyar canja wuri(
p_cuentaorigen INT,
p_cuentaDestino INT,
p_madaidaicin adadin (10,2)
)
GAME
FARA MULKI;

UPDATE asusu
SET balance = ma'auni - p_amount
INA id = p_cuentaOrigen;

UPDATE asusu
SET balance = ma'auni + p_adadin
INA id = p_cuentaDestino;

SANARWA;
END;

Ƙaddamar da dabarar canja wuri a cikin ma'amala.

Ƙarshe akan hanyoyin da aka adana

Hanyoyi da aka adana su ne babban fasalin MySQL wanda kowane mai haɓaka ya kamata ya kware. Don zurfafa zurfin cikin sauran abubuwan da ke da alaƙa, duba bayanai masu dangantaka.

Suna ba ku damar daidaita hadaddun ayyuka, sake amfani da lambar, rage zirga-zirga tsakanin abokin ciniki da uwar garken, haɓaka aiki da tsaro.

Baya ga hanyoyin da aka adana, MySQL yana ba da ayyuka iri-iri waɗanda kuma suke da mahimmanci ga kowane mai haɓakawa. Waɗannan ayyuka suna ba da damar takamaiman ayyuka masu rikitarwa da sarƙaƙƙiya don yin su cikin inganci da tsari. Wasu daga cikin ayyukan da aka fi amfani da su a cikin MySQL sun haɗa da `CONCAT` don haɗa kirtani, `SUBSTRING` don cire kirtani, da ayyukan lissafi kamar `ROUND` da `CEIL` don zagayawa. kuma zaka iya