Pansin! Ang gawaing ito binuo batay sa pagsasalin ng seksyong “17.1. Stored Routines and the Grant Tables" paglalarawan ng MySQL 5.0.19 software, "Reference Manual. Ito ay nagdodokumento ng MySQL 5.0 hanggang 5.0.19. Nabuo ang dokumento noong: 2006-01-23 (rebisyon:995)"
``Basahin muna ang lahat, pagkatapos ay subukan ang mga halimbawa.''

Ang mga stored procedure ay isang set ng mga SQL command na maaaring i-compile at iimbak sa isang server. Sa ganitong paraan, sa halip na mag-imbak ng isang madalas na ginagamit na query, ang mga kliyente ay maaaring sumangguni sa kaukulang naka-imbak na pamamaraan. Nagbibigay ito ng mas mahusay na pagganap, dahil ang kahilingang ito dapat na i-parse nang isang beses lamang at binabawasan ang trapiko sa pagitan ng server at kliyente. Ang antas ng konsepto ay maaari ding tumaas sa pamamagitan ng paglikha ng isang library ng mga function sa server.

Ang trigger ay isang naka-imbak na pamamaraan na isinasagawa kapag naganap ang isang partikular na kaganapan. Halimbawa, maaari mong tukuyin ang isang naka-imbak na pamamaraan na tumatakbo sa bawat oras na ang isang tala ay tinanggal mula sa isang talahanayan ng transaksyon, sa gayon ay matiyak na ang kaukulang customer ay awtomatikong tatanggalin mula sa talahanayan ng customer kapag ang lahat ng mga transaksyon nito ay tinanggal.

Ang mga nakaimbak na programa (mga pamamaraan at function) ay sinusuportahan sa MySQL 5.0. Ang mga nakaimbak na pamamaraan ay isang hanay ng mga SQL expression na maaaring maimbak sa server. Kapag tapos na ito, hindi na kailangan ng kliyente na muling ipadala ang kahilingan, ngunit tawagan na lang ang nakaimbak na programa.

Ito ay maaaring maging kapaki-pakinabang kapag:

• Maraming mga application ng kliyente ang nakasulat sa iba't ibang mga wika o tumatakbo sa iba't ibang mga platform, ngunit dapat gumamit ng parehong database ng transaksyon
• unahin ang kaligtasan

Ang mga nakaimbak na pamamaraan at function (routines) ay maaaring magbigay ng mas mahusay na pagganap dahil mas kaunting impormasyon ang kinakailangan upang maipadala sa pagitan ng kliyente at server. Ang pagpipilian ay nagpapataas ng pagkarga sa database server, ngunit binabawasan ang mga gastos sa panig ng kliyente. Gamitin ito kung marami mga makina ng kliyente(tulad ng mga Web server) ay inihahatid ng isa o higit pang mga database.

Nagbibigay-daan din sa iyo ang mga nakaimbak na gawain na gumamit ng mga function library na nakaimbak sa database ng server. Ang pagkakataong ito ay ipinakita sa marami modernong mga wika programming na nagpapahintulot sa iyo na tawagan sila nang direkta (halimbawa, gamit ang mga klase).

Sinusundan ng MySQL ang syntax ng SQL:2003 para sa mga nakaimbak na pamamaraan, na ginagamit na sa DB2 ng IBM.

Mula sa salita hanggang sa gawa...

Kapag gumagawa, nagbabago, nagtatanggal ng mga nakaimbak na gawain, minamanipula ng server ang mysql.proc table

Sa MySQL 5.0.3 ang mga sumusunod na pribilehiyo ay kinakailangan:

GUMAWA NG ROUTINE upang lumikha ng mga nakaimbak na pamamaraan

ALTER ROUTINE kinakailangan upang baguhin o tanggalin ang mga pamamaraan. Ang pribilehiyong ito ay awtomatikong itinalaga sa lumikha ng pamamaraan (function)

IPATUPAD Kakailanganin ang pribilehiyo upang maisagawa ang subroutine. Gayunpaman, ito ay awtomatikong itinalaga sa lumikha ng pamamaraan (function). Gayundin, bilang default, ang SQL SECURITY parameter para sa DEFINER routine, na nagbibigay-daan sa mga user na may access sa database na tumawag sa mga routine na nauugnay sa database na ito.

Syntax ng mga naka-imbak na pamamaraan at pag-andar

Ang nakaimbak na gawain ay isang pamamaraan o function. Ginagawa ang mga nakaimbak na gawain gamit ang CREATE PROCEDURE o CREATE FUNCTION na mga pahayag. Ang isang nakaimbak na gawain ay tinatawag gamit ang isang CALL na pahayag, na may mga variable na bumabalik ng halaga lamang ang ginagamit bilang mga variable ng output. Ang function ay maaaring tawaging tulad ng anumang iba pang function at maaaring magbalik ng scalar value. Ang mga nakaimbak na gawain ay maaaring tumawag sa iba pang mga nakaimbak na gawain.

Dahil ang MySQL 5.0.1, ang na-load na procedure o function ay nauugnay sa isang partikular na database. Ito ay may ilang mga kahulugan:

• Kapag tinawag ang isang subroutine, ang layunin ay tumawag sa USE db_name (at itapon ang database kapag nakumpleto na ang subroutine at hindi na kailangan ang database)
• Maaari mong maging kwalipikado ang mga regular na pangalan na may pangalan ng database. Ito ay maaaring gamitin upang sumangguni sa isang gawain na wala sa kasalukuyang database. Halimbawa, upang magsagawa ng naka-imbak na pamamaraan p o isang function f na nauugnay sa database ng pagsubok, maaari mong sabihin sa command interpreter tulad nito: TUMAWAG test.p() o test.f() .
• Kapag ang isang database ay tinanggal, ang lahat ng mga naka-load na gawain na nauugnay dito ay tatanggalin din. Sa MySQL 5.0.0, ang mga naka-load na gawain ay pandaigdigan at hindi nauugnay sa database. Nagmana sila ng default na database mula sa calling statement. Kung ang USE db_name ay isinagawa sa loob ng isang subroutine, ang orihinal na kasalukuyang database ay ibabalik pagkatapos lumabas sa subroutine (Halimbawa, ang kasalukuyang database ay db_11, tinatawag namin ang isang subroutine na gumagamit ng db_22, pagkatapos lumabas sa subroutine ang kasalukuyang db_11 ay nananatili)

Ganap na sinusuportahan ng MySQL ang mga extension na nagbibigay-daan sa iyong gumamit ng mga regular na SELECT expression (nang hindi gumagamit ng mga cursor o lokal na variable) sa loob ng mga nakaimbak na pamamaraan. Ang resultang set na ibinalik mula sa query ay direktang ipinadala sa kliyente. Maramihan PUMILI ng query bumubuo ng maraming set ng resulta, kaya dapat gumamit ang kliyente ng library na sumusuporta sa maraming set ng resulta.

GUMAWA NG PAMAMARAAN- lumikha ng isang naka-imbak na pamamaraan.

GUMAWA NG FUNCTION- lumikha ng isang naka-imbak na function.

Syntax:

GUMAWA NG PAMAMARAAN procedure_name ([procedure_parameter[,...]])
[mga katangian...] subroutine_body

GUMAWA NG FUNCTION function_name ([function_parameter[,...]])
NAGBABALIK uri
[characteristic...] subroutine_body

procedure_parameter:
[ SA | LABAS | INOUT] uri ng parameter_name
function_parameter:
uri ng parameter_name

uri:
Anumang uri ng data ng MySQL

katangian:
WIKA SQL
|
DETERMINISTIC
| ( NAGLALAMAN NG SQL | WALANG SQL | NAGBASA NG SQL DATA | NAGBABAGO NG SQL DATA )
| SQL SECURITY (DEFINER | INVOKER)

| COMMENT "string"
subroutine_body:

Tamang SQL expression.

Tingnan natin ang lahat sa pagsasanay.

Una, gumawa tayo ng nakaimbak na pamamaraan na may sumusunod na query:
GUMAWA NG PAMAMARAAN `my_proc`(OUT t INTEGER(11))
HINDI DETERMINISTIC
SQL SECURITY INVOKER
COMMENT ""
MAGSIMULA
piliin ang val1+val2 sa "t" mula sa `my` LIMIT 0,1;

WAKAS;

Ang paggamit ng LIMIT na expression sa query na ito ay ginawa bilang pagkilala sa katotohanang hindi lahat ng kliyente ay maaaring tumanggap ng isang hanay ng resultang multi-row.

Pagkatapos nito, tawagan natin ito:
TUMAWAG sa aking_proc(@a);

PUMILI @a; Upang paghiwalayin ang isang panloob na kahilingan mula sa isang panlabas, isang separator na naiiba sa karaniwan ay palaging ginagamit (para sa gawain, gamitin ang command <строка/символ>)

DELIMITER

Narito ang isa pang halimbawa na isinasaalang-alang ang lahat ng mga kinakailangan.
Mysql> delimiter //
mysql> GUMAWA NG PROCEDURE simpleproc (OUT param1 INT)
-> MAGSIMULA
-> PUMILI NG BILANG(*) SA param1 MULA t;
-> //

->WAKAS;
mysql>delimiter;
mysql> TUMAWAG simpleproc(@a);
OK ang query, 0 row ang apektado (0.00 sec)
+------+
mysql> SELECT @a;
+------+
| 3 |
+------+
| @a |

1 row sa set (0.00 sec)

Ang buong proseso ay makikita sa figure sa ibaba:

Mga nag-trigger

Ang suporta sa trigger ay magagamit sa MySQL mula noong bersyon 5.0.2. Trigger

- isang pinangalanang object ng database na nauugnay sa isang talahanayan at naisaaktibo kapag may nangyaring partikular na kaganapan, isang kaganapan na nauugnay sa talahanayang ito.

Halimbawa, ang sumusunod na code ay lumilikha ng isang talahanayan at isang INSERT trigger. Binubuo ng trigger ang mga value na ipinasok sa isa sa mga column ng talahanayan.
Mysql> GUMAWA NG TABLE account (acct_num INT, halagang DECIMAL(10,2));
OK ang query, 0 row ang apektado (0.03 sec)
-> PARA SA BAWAT ROW SET @sum = @sum + NEW.amount;
OK ang query, 0 row ang apektado (0.06 sec)

Magdeklara tayo ng variable sum at italaga ito ng value 1. Pagkatapos nito, sa tuwing ilalagay natin sa table account tataas ang halaga ng variable na ito ayon sa ipinasok na bahagi.

Magkomento. Kung hindi nasimulan ang value ng variable, hindi gagana ang trigger!

Syntax para sa paggawa ng trigger

GUMAWA

TRIGGER trigger_name trigger_time trigger_event
SA table_name PARA SA BAWAT ROW expression_executed_when_trigger_fires

Kung malinaw na kaagad ang lahat sa trigger name at user name, pag-uusapan natin ang tungkol sa "oras ng pag-trigger" at "kaganapan" nang hiwalay.

trigger_time

Tinutukoy ang oras kung kailan nagaganap ang pagkilos ng trigger. BEFORE ay nangangahulugan na ang trigger ay isasagawa bago makumpleto ang trigger na kaganapan, at PAGKATAPOS ay nangangahulugan pagkatapos. Halimbawa, kapag naglalagay ng mga tala (tingnan ang halimbawa sa itaas), ang aming trigger ay nagpaputok bago ang aktwal na pagpasok ng tala at kinakalkula ang kabuuan. Ang pagpipiliang ito ay angkop para sa paunang pagkalkula ng ilang karagdagang mga patlang sa talahanayan o parallel na pagpapasok sa isa pang talahanayan.

trigger_event

Mas simple ang lahat dito. Ito ay malinaw na nagpapahiwatig kung anong kaganapan ang trigger ay isinasagawa.

• INSERT: ibig sabihin. sa panahon ng mga operasyon ng pagpasok o katulad na mga expression (INSERT, LOAD DATA, at REPLACE)
• I-UPDATE: kapag binago ang isang entity (row).
• DELETE: Kapag ang isang tala ay tinanggal (mga query na naglalaman ng DELETE at/o REPLACE na mga pahayag)

Ang SQL stored procedures ay mga executable program modules na maaaring iimbak sa form iba't ibang bagay. Sa madaling salita, ito ay isang bagay na naglalaman ng mga pahayag ng SQL. Ang mga nakaimbak na pamamaraan na ito ay maaaring isagawa sa kliyente ng aplikasyon upang makakuha ng mahusay na pagganap. Bilang karagdagan, ang mga naturang bagay ay madalas na tinatawag mula sa iba pang mga script o kahit na mula sa ibang seksyon.

Panimula

Maraming mga tao ang naniniwala na ang mga ito ay katulad sa iba't ibang mga pamamaraan (ayon sa pagkakabanggit, maliban sa MS SQL). Marahil ito ay totoo. Mayroon silang magkatulad na mga parameter at maaaring makagawa ng mga katulad na halaga. Bukod dito, sa ilang mga kaso ay hinawakan nila. Halimbawa, pinagsama ang mga ito sa mga database ng DDL at DML, pati na rin sa mga function ng user (codenamed UDF).

Sa katotohanan, ang mga pamamaraang naka-imbak ng SQL ay may malawak na hanay ng mga pakinabang na nagbubukod sa kanila sa mga katulad na proseso. Seguridad, kakayahang umangkop sa programming, produktibidad - lahat ng ito ay umaakit ng mas maraming user na nagtatrabaho sa mga database. Ang pinakamataas na katanyagan ng mga pamamaraan ay naganap noong 2005-2010, nang ang isang programa mula sa Microsoft na tinatawag na "SQL Server Management Studio" ay inilabas. Sa tulong nito, ang pagtatrabaho sa mga database ay naging mas madali, mas praktikal at maginhawa. Mula taon hanggang taon, ang isang ito ay nakakuha ng katanyagan sa mga programmer. Ngayon ito ay isang ganap na pamilyar na programa, na para sa mga gumagamit na "nakikipag-usap" sa mga database ay katumbas ng Excel.

Kapag tinawag ang isang pamamaraan, agad itong naproseso ng server mismo nang wala mga hindi kinakailangang proseso at interbensyon ng gumagamit. Pagkatapos nito, maaari kang magsagawa ng anumang pagtanggal, pagpapatupad, o pagbabago. Ang DDL operator ang may pananagutan para sa lahat ng ito, na nag-iisa ang gumaganap ng mga pinaka-kumplikadong aksyon upang iproseso ang mga bagay. Bukod dito, ang lahat ng ito ay nangyayari nang napakabilis, at ang server ay hindi aktwal na na-load. Ang bilis at pagganap na ito ay nagbibigay-daan sa iyo upang mabilis na maglipat ng malaking halaga ng impormasyon mula sa user patungo sa server at vice versa.

Upang ipatupad ang teknolohiyang ito para sa pagtatrabaho sa impormasyon, mayroong ilang mga programming language. Kabilang dito, halimbawa, ang PL/SQL mula sa Oracle, PSQL sa InterBase at Firebird system, pati na rin ang klasikong Microsoft Transact-SQL. Ang lahat ng mga ito ay idinisenyo para sa paglikha at pagpapatupad ng mga nakaimbak na pamamaraan, na nagpapahintulot sa malalaking database processor na gumamit ng kanilang sariling mga algorithm. Ito ay kinakailangan din upang ang mga namamahala sa naturang impormasyon ay maprotektahan ang lahat ng mga bagay mula sa hindi awtorisadong pag-access ng mga ikatlong partido at, nang naaayon, ang paglikha, pagbabago o pagtanggal ng ilang partikular na data.

Produktibidad

Ang mga database object na ito ay maaaring ma-program sa iba't ibang paraan. Nagbibigay-daan ito sa mga user na pumili ng uri ng pamamaraang ginamit na pinakaangkop, nakakatipid ng pagod at oras. Bilang karagdagan, ang pamamaraan ay naproseso mismo, na nag-iwas sa malaking oras na ginugol sa komunikasyon sa pagitan ng server at ng gumagamit. Ang module ay maaari ding i-reprogram at palitan ang tamang direksyon sa ganap na anumang sandali. Ito ay lalong nagkakahalaga ng pagpuna sa bilis kung saan ang SQL stored procedure ay inilunsad: ang prosesong ito ay nangyayari nang mas mabilis kaysa sa iba pang katulad nito, na ginagawang maginhawa at unibersal.

Kaligtasan

Ang ganitong uri ng pagproseso ng impormasyon ay naiiba sa mga katulad na proseso dahil ginagarantiyahan nito ang mas mataas na seguridad. Ito ay sinisiguro ng katotohanan na ang pag-access sa mga pamamaraan ng ibang mga gumagamit ay maaaring ganap na maibukod. Ito ay magpapahintulot sa administrator na magsagawa ng mga operasyon sa kanila nang nakapag-iisa, nang walang takot sa pagharang ng impormasyon o hindi awtorisadong pag-access sa database.

Paglipat ng data

Ang kaugnayan sa pagitan ng SQL stored procedure at ng client application ay ang paggamit ng mga parameter at return value. Ang huli ay hindi kailangang ipasa ang data sa nakaimbak na pamamaraan, ngunit ang impormasyong ito (pangunahin sa kahilingan ng user) ay pinoproseso para sa SQL. Matapos makumpleto ng nakaimbak na pamamaraan ang gawain nito, nagpapadala ito ng mga data packet pabalik (ngunit, muli, opsyonal) sa application na tumawag dito, gamit ang iba't ibang pamamaraan, sa tulong kung saan parehong maaaring gawin ang isang tawag sa isang nakaimbak na pamamaraan ng SQL at isang pagbabalik, halimbawa:

Paglilipat ng data gamit ang isang parameter ng uri ng Output;

Pagpasa ng data gamit ang return operator;

Pagpasa ng data gamit ang piling operator.

Ngayon, alamin natin kung ano ang hitsura ng prosesong ito mula sa loob.

1. Gumawa ng EXEC stored procedure sa SQL

Maaari kang lumikha ng isang pamamaraan sa MS SQL (Managment Studio). Matapos malikha ang pamamaraan, ito ay ililista sa programmable node ng database, kung saan ang pamamaraan ng paglikha ay isinasagawa ng operator. Upang maisagawa, ang mga nakaimbak na pamamaraan ng SQL ay gumagamit ng isang proseso ng EXEC na naglalaman ng pangalan ng mismong bagay.

Kapag lumikha ka ng isang pamamaraan, unang lalabas ang pangalan nito, na sinusundan ng isa o higit pang mga parameter na itinalaga dito. Maaaring opsyonal ang mga parameter. Matapos maisulat ang (mga) parameter, iyon ay, ang katawan ng pamamaraan, kailangang isagawa ang ilang kinakailangang operasyon.

Ang punto ay ang isang katawan ay maaaring magkaroon ng mga lokal na variable na matatagpuan dito, at ang mga variable na ito ay lokal din na may kaugnayan sa mga pamamaraan. Sa madaling salita, maaari lamang silang tingnan sa loob ng katawan ng isang pamamaraan ng Microsoft SQL Server. Ang mga nakaimbak na pamamaraan sa kasong ito ay itinuturing na lokal.

Kaya, upang lumikha ng isang pamamaraan, kailangan namin ang pangalan ng pamamaraan at hindi bababa sa isang parameter bilang katawan ng pamamaraan. tandaan mo yan mahusay na pagpipilian sa kasong ito, ito ay upang lumikha at magsagawa ng isang pamamaraan na may pangalan ng schema sa classifier.

Ang katawan ng pamamaraan ay maaaring maging anumang uri, tulad ng paglikha ng isang talahanayan, pagpasok ng isa o higit pang mga hilera ng isang talahanayan, pagtatatag ng uri at kalikasan ng database, at iba pa. Gayunpaman, ang katawan ng pamamaraan ay naghihigpit sa ilang mga operasyon na maisagawa sa loob nito. Ang ilan sa mahahalagang limitasyon ay nakalista sa ibaba:

Ang katawan ay hindi dapat lumikha ng anumang iba pang nakaimbak na pamamaraan;

Ang katawan ay hindi dapat lumikha ng isang maling impresyon ng bagay;

Ang katawan ay hindi dapat lumikha ng anumang mga pag-trigger.

2. Pagtatakda ng variable sa katawan ng pamamaraan

Maaari kang gumawa ng mga variable na lokal sa katawan ng pamamaraan, at pagkatapos ay mananatili sila nang eksklusibo sa loob ng katawan ng pamamaraan. Magandang kasanayan na lumikha ng mga variable sa simula ng naka-imbak na katawan ng pamamaraan. Ngunit maaari ka ring magtakda ng mga variable saanman sa katawan ng isang partikular na bagay.

Minsan maaari mong mapansin na maraming mga variable ang nakatakda sa isang linya, at ang bawat variable na parameter ay pinaghihiwalay ng kuwit. Tandaan din na ang variable ay may prefix na @. Sa katawan ng pamamaraan maaari mong itakda ang variable sa kung saan mo gusto. Halimbawa, ang variable na @NAME1 ay maaaring ideklara malapit sa dulo ng katawan ng pamamaraan. Upang magtalaga ng isang halaga sa isang ipinahayag na variable, isang set ng personal na data ang ginagamit. Hindi tulad ng sitwasyon kung saan higit sa isang variable ang idineklara sa parehong linya, sa sitwasyong ito ay isang set lamang ng personal na data ang ginagamit.

Madalas itanong ng mga gumagamit ang tanong: "Paano magtalaga ng maraming mga halaga sa isang pahayag sa katawan ng isang pamamaraan?" Well. Ito ay isang kawili-wiling tanong, ngunit ito ay mas madaling gawin kaysa sa iyong iniisip. Sagot: Gamit ang mga pares gaya ng "Select Var = value". Magagamit mo ang mga pares na ito sa pamamagitan ng paghihiwalay sa kanila gamit ang kuwit.

Sa karamihan iba't ibang halimbawa ipinapakita ng mga tao ang paglikha ng isang simpleng naka-imbak na pamamaraan at pagsasagawa nito. Gayunpaman, ang isang pamamaraan ay maaaring tumanggap ng mga parameter na ang proseso ng pagtawag dito ay magkakaroon ng mga halaga na malapit dito (ngunit hindi palaging). Kung nag-tutugma sila, ang mga kaukulang proseso ay magsisimula sa loob ng katawan. Halimbawa, kung gagawa ka ng pamamaraan na tatanggap ng lungsod at rehiyon mula sa tumatawag at magbabalik ng data tungkol sa kung ilang may-akda ang nabibilang sa kaukulang lungsod at rehiyon. Ang pamamaraan ay magtatanong sa mga talahanayan ng may-akda ng database, tulad ng mga Pub, upang maisagawa ang bilang ng may-akda na ito. Upang makuha ang mga database na ito, halimbawa, dina-download ng Google ang SQL script mula sa pahina ng SQL2005.

Sa nakaraang halimbawa, ang pamamaraan ay tumatagal ng dalawang parameter, na sa Ingles ay karaniwang tinatawag na @State at @City. Ang uri ng data ay tumutugma sa uri na tinukoy sa application. Ang katawan ng pamamaraan ay may mga panloob na variable @TotalAuthors, at ang variable na ito ay ginagamit upang ipakita ang bilang ng mga may-akda. Susunod ay ang seksyon ng pagpili ng query, na kinakalkula ang lahat. Sa wakas, ang kinakalkula na halaga ay naka-print sa output window gamit ang print statement.

Paano magsagawa ng isang naka-imbak na pamamaraan sa SQL

Mayroong dalawang mga paraan upang maisagawa ang pamamaraan. Ipinapakita ng unang paraan, sa pamamagitan ng pagpasa ng mga parameter, kung paano isinasagawa ang isang listahang pinaghihiwalay ng kuwit pagkatapos ng pangalan ng pamamaraan. Sabihin nating mayroon tayong dalawang halaga (tulad ng sa nakaraang halimbawa). Ang mga halagang ito ay kinokolekta gamit ang @State at @City procedure parameter variables. Sa ganitong paraan ng pagpasa ng mga parameter, ang pagkakasunud-sunod ay mahalaga. Ang pamamaraang ito ay tinatawag na ordinal argument passing. Sa pangalawang paraan, ang mga parameter ay direktang itinalaga, at sa kasong ito ang pagkakasunud-sunod ay hindi mahalaga. Ang pangalawang paraan na ito ay kilala bilang pagpasa ng pinangalanang mga argumento.

Ang pamamaraan ay maaaring bahagyang lumihis mula sa karaniwang isa. Ang lahat ay pareho sa nakaraang halimbawa, ngunit dito lamang ang mga parameter ay inilipat. Iyon ay, ang @City ay unang nakaimbak, at ang @State ay nakaimbak sa tabi ng default na halaga. Ang default na parameter ay karaniwang naka-highlight nang hiwalay. Ang mga nakaimbak na pamamaraan ng SQL ay ipinasa bilang mga parameter lamang. Sa kasong ito, sa kondisyon, pinapalitan ng parameter na "UT" ang default na halaga na "CA". Sa pangalawang execution, isang value ng argument lang ang ipinasa para sa parameter na @City, at ang parameter na @State ay tumatagal sa default na value ng "CA". Pinapayuhan ng mga nakaranasang programmer na ang lahat ng mga variable ay dapat na matatagpuan sa dulo ng listahan ng parameter bilang default. Kung hindi, hindi posible ang pagpapatupad, at pagkatapos ay kailangan mong magtrabaho kasama ang pagpasa ng mga pinangalanang argumento, na mas mahaba at mas kumplikado.

4. SQL Server Stored Procedures: Mga Paraan ng Pagbabalik

Mayroong tatlong mahahalagang paraan upang magpadala ng data sa tinatawag na stored procedure. Nakalista sila sa ibaba:

Ibalik ang halaga ng isang nakaimbak na pamamaraan;

Output ng parameter ng naka-imbak na pamamaraan;

Pagpili ng isa sa mga nakaimbak na pamamaraan.

4.1 Pagbabalik ng mga halaga mula sa mga pamamaraan na nakaimbak ng SQL

Sa diskarteng ito, ang isang pamamaraan ay nagtatalaga ng isang halaga sa isang lokal na variable at ibinabalik ito. Ang isang pamamaraan ay maaari ring direktang magbalik ng isang palaging halaga. Sa sumusunod na halimbawa, gumawa kami ng pamamaraan na nagbabalik ng kabuuang bilang ng mga may-akda. Kung ihahambing mo ang pamamaraang ito sa mga nauna, makikita mo na ang halaga ng pag-print ay baligtad.

Ngayon tingnan natin kung paano magsagawa ng procedure at i-print ang return value nito. Ang pagpapatupad ng pamamaraan ay nangangailangan ng pagtatakda ng isang variable at pag-print, na isinasagawa pagkatapos ng buong prosesong ito. Tandaan na sa halip na isang print statement, maaari kang gumamit ng Select statement, gaya ng Select @RetValue pati na rin ang OutputValue.

4.2 SQL Stored Procedure Parameter Output

Ang halaga ng tugon ay maaaring gamitin upang ibalik ang isang solong variable, na kung ano ang nakita namin sa nakaraang halimbawa. Ang paggamit ng Output parameter ay nagbibigay-daan sa isang pamamaraan na magpadala ng isa o higit pang mga variable na halaga sa tumatawag. Ang parameter ng output ay tiyak na itinalaga ng keyword na ito na "Output" kapag gumagawa ng isang pamamaraan. Kung ang isang parameter ay ibinigay bilang isang parameter ng output, ang object ng procedure ay dapat magtalaga ng isang halaga dito. Ang mga pamamaraan na naka-imbak ng SQL, ang mga halimbawa nito ay makikita sa ibaba, sa kasong ito ay bumalik na may buod ng impormasyon.

Sa aming halimbawa magkakaroon ng dalawang pangalan ng output: @TotalAuthors at @TotalNoContract. Ang mga ito ay ipinahiwatig sa listahan ng mga parameter. Ang mga variable na ito ay nagtatalaga ng mga halaga sa loob ng katawan ng pamamaraan. Kapag gumamit kami ng mga parameter ng output, makikita ng tumatawag ang halagang itinakda sa loob ng katawan ng pamamaraan.

Gayundin, sa nakaraang senaryo, dalawang variable ang idineklara upang makita ang mga halaga na naka-imbak ng mga pamamaraan ng MS SQL Server na itinakda sa parameter ng output. Pagkatapos ay isinasagawa ang pamamaraan sa pamamagitan ng pagbibigay ng normal na halaga ng parameter na "CA". Ang mga sumusunod na parameter ay output at samakatuwid ang ipinahayag na mga variable ay ipinapasa sa sa inireseta na paraan. Tandaan na kapag nagpapasa ng mga variable, nakatakda rin dito ang output na keyword. Matapos matagumpay na makumpleto ang pamamaraan, ang mga halaga na ibinalik ng mga parameter ng output ay ipinapakita sa window ng mensahe.

4.3 Pagpili ng isa sa mga nakaimbak na pamamaraan ng SQL

Ang pamamaraan na ito ay ginagamit upang ibalik ang isang hanay ng mga halaga bilang isang talahanayan ng data (RecordSet) sa naka-imbak na pamamaraan ng pagtawag. Dito Halimbawa ng SQL Ang isang naka-imbak na pamamaraan na may mga parameter na @AuthID ay nagtatanong sa talahanayan ng Mga May-akda sa pamamagitan ng pag-filter sa mga talaang ibinalik gamit ang parameter na ito ng @AuthId. Ang Select statement ay nagpapasya kung ano ang dapat ibalik sa tumatawag ng nakaimbak na pamamaraan. Kapag ang naka-imbak na pamamaraan ay naisakatuparan, ang AuthId ay ibabalik. Ang pamamaraang ito dito ay palaging nagbabalik ng isang tala lamang o wala. Ngunit ang isang nakaimbak na pamamaraan ay walang anumang mga paghihigpit sa pagbabalik ng higit sa isang tala. Karaniwang makakita ng mga halimbawa kung saan nangyayari ang pagbabalik ng data gamit ang mga napiling parameter na kinasasangkutan ng mga kalkuladong variable sa pamamagitan ng pagbibigay ng maramihang kabuuan.

Sa konklusyon

Ang nakaimbak na pamamaraan ay medyo seryoso module ng software, pagbabalik o pagpapadala, pati na rin ang pagtatakda ng mga kinakailangang variable salamat sa aplikasyon ng kliyente. Dahil ang naka-imbak na pamamaraan ay tumatakbo mismo sa server, ang malaking halaga ng palitan ng data sa pagitan ng server at ng client application (para sa ilang mga kalkulasyon) ay maiiwasan. Ito ay nagpapahintulot sa iyo na bawasan ang load sa SQL server, na, siyempre, nakikinabang sa kanilang mga may hawak. Ang isa sa mga subtype ay T SQL stored procedures, ngunit ang kanilang pag-aaral ay kinakailangan para sa mga gumagawa ng mga kahanga-hangang database. Mayroon ding isang malaki, kahit na malaking bilang ng mga nuances na maaaring maging kapaki-pakinabang kapag nag-aaral ng mga naka-imbak na pamamaraan, ngunit ito ay higit na kinakailangan para sa mga nagpaplanong makisali sa programming, kabilang ang propesyonal.

GUMAWA[ O PALITAN ] FUNCTION
function_name ([ [ argument_method ][ argument_name ] argument_type [,…] ])
NAGBABALIKreturn_value_type
AS" kahulugan"
WIKA" wika"
[ MAY (katangian […])]

• GUMAWA NG FUNCTION function_name ([[ argument_method ] [argument_name ] argument_type[,…] ]) - pagkatapos ng CREATE FUNCTION na mga keyword, ang pangalan ng function na gagawin ay ipinahiwatig, pagkatapos kung saan ang mga argumento ay nakalista sa mga panaklong, na pinaghihiwalay ng mga kuwit. Para sa bawat argumento, sapat na upang tukuyin lamang ang uri, ngunit kung nais, maaari mong tukuyin ang pamamaraan (in, out, inout; default sa ) at pangalan. Kung ang listahan sa mga panaklong ay walang laman, ang function ay tinatawag na walang mga argumento (bagaman ang mga panaklong mismo ay dapat na naroroon sa parehong kahulugan ng function at kapag ginagamit ito). Ang OR REPLACE keywords ay ginagamit upang baguhin ang isang mayroon nang function.
  
• NAGBABALIK return_value_type -uri ng data na ibinalik ng function.
  
• AS "kahulugan"- kahulugan ng software mga function. Sa pamamaraang mga wika (tulad ng PL/pgSQL) ito ay binubuo ng function code. Para sa mga compiled C function, tukuyin ang absolute system path sa file na naglalaman ng object code.
  
• WIKA "wika". Ang pangalan ng wika kung saan nakasulat ang function. Maaaring ipasa ng argumento ang pangalan ng anumang procedural na wika (gaya ng plpgsqlo plperl, kung ang kaukulang suporta ay na-install sa panahon ng compilation), C o SQL.
  [
• SA (katangian [. ...]) ] -katangian maaaring tumagal ng dalawang halaga: iscachable at isstrict.
  isachable. Ang optimizer ay maaaring gumamit ng mga nakaraang function na tawag upang pabilisin ang mga tawag sa hinaharap na may parehong hanay ng mga argumento. Karaniwang ginagamit ang pag-cache kapag nagtatrabaho sa mga function na nangangailangan ng maraming mapagkukunan ngunit ibinabalik ang parehong resulta na ibinigay sa parehong mga halaga ng argumento.
  mahigpit. Ang function ay palaging nagbabalik ng NULL kung hindi bababa sa isa sa mga argumento nito ay NULL. Kapag pumasa sa isstrict attribute, ang resulta ay ibabalik kaagad, nang hindi aktwal na isinasagawa ang function.
  

Sa PostgreSQL c Ang mga superuser lang ang pinapayagang gumawa ng mga function sa C dahil ang mga function na ito ay maaaring maglaman ng mga system call na nagdudulot ng potensyal na banta sa seguridad ng system. Tingnan natin ang paglikha ng mga functionsql At plpgsql .

Paglikha ng SQL Function

GUMAWA[ O PALITAN ] FUNCTION
function_name ([ [ argument_method ][ argument_name ] argument_type [,…] ])
NAGBABALIKreturn_value_type
AS"
operator SQL;
[operator SQL;
… ]
"
LANGUAGE sql
[ MAY (katangian […])] ;

• Sa katawan ng pag-andar sql kayang tumayo lamang mga operator wika ng SQL, anumang (Piliin, ipasok, tanggalin, likhain,...) maliban sa mga operator ng kontrol sa transaksyon (commit, rollback...). Ang return value ay ang resulta ng SELECT statement, ang uri nito ay dapat tumugma sa uri na tinukoy pagkatapos NAGBABALIK. Kung mayroong ilang SELECT statement sa function body, ibabalik ng function ang resulta ng pagsasagawa ng huling naturang statement. Kung ang function sql ay hindi naglalaman ng mga SELECT statement, ang uri ng resulta nito ay dapat na tinukoy bilang walang bisa (sa katunayan, ito ay isang pamamaraan).
  

Mga halimbawa ng paglikha at paggamit ng mga function ng sql

Halimbawa 1: Gumawa ng function na nagbabalik ng column mga halaga ng teksto

GUMAWA NG FUNCTIONonef (integer) NAGBABALIK SETOF na karakter AS "
-- Ibinabalik ng function ang mga pangalan ng mga supplier na may mga rating na higit sa $1
pumili ng mga pangalan mula sa s kung saan rg>$1;
" WIKA sql;

Paggamit ng function na nagbabalik ng column ng mga text value

PUMILI isa(10) AS pangalan ;

Resulta

Halimbawa 2: Gumawa ng function na nagbabalik ng column ng mga record

GUMAWA NG FUNCTIONmanyf (integer) NAGBABALIK SETOF record AS "
- - Ang function ay nagbabalik ng impormasyon tungkol sa mga supplier na may mga rating na higit sa $1
piliin ang ns,pangalan,rg,bayan mula sa s kung saan ang rg>$1 order ng ns;
" WIKA sql;

Tandaan. Ang isang function na nagbabalik ng isang column ng mga record ay maaaring gamitin sa isang SELECT statement kung ito ay tinukoy bilang isang SQL function (ang plpgsql function ay may ibang paggamit)

Paggamit ng function na nagbabalik ng column ng mga record

PUMILI manyf(10) AS resulta ;

Resulta

Paglikha ng mga function ng plpgSQL

GUMAWA[O PALITAN] FUNCTION
Pangalan_ mga function ([ [ method_argument ][ Pangalan_ argumento] uri_ argumento[,…] ])
NAGBABALIKreturn_value_type
AS "
[ IDEKLARA
mga patalastas ]
MAGSIMULA
operator;
[ operator;…]
TAPOS

[KAILAN kundisyon[Okundisyon...] TAPOS
mga pahayag sa paghawak ng exception;
... ]
piliin ang val1+val2 sa "t" mula sa `my` LIMIT 0,1;
"
WIKA plpgsql
[ MAY ( katangian […])] ;

• Sa katawan ng function, ina-access ang mga parameter ayon sa pangalan o numero: $1 ang unang parameter, $2 ang pangalawang parameter, atbp.
  
• Sa katawan ng pag-andar plpgsql Bilang karagdagan sa mga SQL statement, maaaring gamitin ang mga construct ng PL/pgSQL na wika, na isang procedural extension ng SQL. Maaari itong maging mga operator ng pagtatalaga, mga kondisyong pahayag, mga cycle, atbp. Ang resulta ay ibinalik sa pamamagitan ng utos BUMALIK. Sa katawan ng pag-andar plpgsql Kailangan dapat mayroong kahit isang utos BUMALIK, maliban kung ang uri ng resulta ay walang bisa. Tingnan ang higit pang mga detalye. http://www.postgresql.org/docs/8.2/interactive/extend.html
• Ang mga lokal na variable na ginamit sa function body ay idineklara sa DECLARE block
  MAGDEKLARA
  variable_name variable_type;
  [ variable_name variable_type;... ]
  
• Sa block EXCEPTION naproseso ang mga error. Kundisyon– pangalan ng error (tingnan ang listahan). http://www.sbin.org/doc/pg/doc/errcodes-appendix.html sa Constant column). Kung ang lahat ng mga error ay naproseso ayon sa parehong pamamaraan (o ikaw ay tamad na mag-isip tungkol sa kung anong mga error ang maaaring magkaroon at hanapin kung ano ang tawag sa kanila), ang bloke na ito ay maaaring magmukhang ganito
  EXCEPTION
  KAILAN iba pa TAPOS
  RAISE exception " mensahe ng error";

  FunctionITAASantaspagbubukodnagtatapon ng exception at nagpapakita ng mensahe ng error (higit pa tungkol sa function ITAAScm. http://www.sbin.org/doc/pg/doc/plpgsql-errors-and-messages.html).
  

Mga halimbawa ng paglikha at paggamit ng mga function ng plpgsql

Halimbawa 3: Paglikha function na nagbabalik ng isang integer na halaga

GUMAWA NG FUNCTION apf (character) NAGBABALIK integer AS "
MAGDEKLARA
i integer;
-- Kinakalkula ng function ang dami ng supply ng bahagi na $1
MAGSIMULA
piliin ang bilang(*) mula sa spj papunta sa i kung saan spj.np=$1;
-- bumalikresulta
ibalik i;
WAKAS;
" WIKA plpgsql;

Halimbawa 4: Paglikha ng isang function na nagbabalik ng isang tunay na halaga

GUMAWA NG FUNCTIONavgves (character) NAGBABALIK totoo AS "
MAGDEKLARA
aves real;
-- Kinakalkula ng function ang average na bigat ng paghahatid ng bahaging $1
MAGSIMULA
piliin ang avg(spj.kol * p.ves) mula sa spj,p papunta sa aves kung saan spj.np=$1 at spj.np=p.np ;
-- bumalikresulta
bumalik aves;
WAKAS;
" WIKA plpgsql;

Halimbawa 5: Paggamit ng Mga Function sa Classic SELECT

PUMILIp.np, p.nanap,
apf(p.np) AS kol, avgves(p.np) bilang sves,
apf(p.np)* avgves(p.np) bilang oves MULA SA p;

Resulta

Np	namep	kol	sves	oves
P1	turnilyo	1	1200	1200
P2	Bolt	2	2550	5100
P3	tornilyo	9	6611.11	59500.001953125
P4	tornilyo	2	9100	18200
P5	Cam	4	3300	13200
P6	Bloom	4	6175	24700

Halimbawa 6. Paggamit ng mga function, hindi karaniwang opsyon (PostgreSql lang)

PUMILI apf("P5") AS kol ;

Resulta

kol

4

Mga kinakailangan para sa mga tinatawag na function

Para sa isang function na tinukoy ng PL/SQL programmer na matatawag mula sa mga SQL command, dapat itong matugunan ang mga sumusunod na kinakailangan:

• Ang lahat ng mga parameter ng function ay dapat magkaroon ng mode ng paggamit IN . Ang mga mode na IN OUT at OUT sa mga function na naka-embed sa SQL code ay hindi pinapayagan.
• Ang mga uri ng data ng mga parameter ng function at ang uri ng pagbabalik ay dapat kilalanin ng Oracle server. Ang PL/SQL ay umaakma sa mga pangunahing uri ng Oracle na hindi pa sinusuportahan ng database. Kasama sa mga uri na ito ang BOOLEAN, BINARY_INTEGER, associative arrays, PL/SQL record, at programmer-defined subtypes.
• Ang function ay dapat na naka-imbak sa database. Ang isang function na tinukoy sa panig ng kliyente ay hindi matatawag sa mga SQL command dahil hindi malulutas ng SQL ang reference ng function.

Bilang default, ang mga function na tinukoy ng user na tinatawag sa SQL ay gumagana sa data ng isang row sa halip na isang column (bilang pinagsama-samang mga function SUM, MIN at AVG). Upang lumikha ng mga pinagsama-samang function na tinatawag sa SQL, dapat mong gamitin ang interface ng ODCIAggregate, na bahagi ng Oracle Extensibility Framework. Para sa detalyadong impormasyon Mangyaring sumangguni sa dokumentasyon ng Oracle para sa higit pang impormasyon sa paksang ito.

Mga limitasyon sa mga function na tinukoy ng user na tinatawag sa SQL

Upang maprotektahan laban sa mga side effect at hindi mahuhulaan na pag-uugali, hindi pinapayagan ng Oracle ang mga nakaimbak na pamamaraan na gawin ang mga sumusunod:

• Ang mga naka-imbak na function ay hindi maaaring baguhin ang mga talahanayan ng database at hindi maaaring mag-isyu ng DDL (CREATE TABLE, DROP INDEX, atbp.), INSERT, DELETE, MERGE, o UPDATE na mga utos. Ang mga paghihigpit na ito ay maluwag kung ang function ay tinukoy bilang isang autonomous na transaksyon. Sa kasong ito, ang anumang mga pagbabagong ginagawa nito ay isinasagawa nang hiwalay sa panlabas na transaksyon kung saan isinasagawa ang kahilingan.
• Ang mga naka-imbak na function na tinatawag na malayuan o kahanay ay hindi maaaring basahin o baguhin ang mga halaga ng mga variable ng package. Oracle Server hindi sumusuporta side effects, ang epekto nito ay lumalampas sa session ng user.
• Ang isang naka-imbak na function ay maaaring baguhin ang mga halaga ng mga variable ng package kung ito ay tinatawag sa isang piling listahan o sa isang VALUES o SET na sugnay. Kung ang isang naka-imbak na function ay tinatawag sa isang WHERE o GROUP BY clause, hindi nito mababago ang mga halaga ng mga variable ng package.
• Bago ang Oracle8, hindi matawagan ng mga function na tinukoy ng user ang pamamaraang RAISE_APPLICATION_ERROR.
• Ang isang naka-imbak na function ay hindi maaaring tumawag sa isa pang module (naka-imbak na pamamaraan o function) na hindi nakakatugon sa mga kinakailangang ito.
• Hindi ma-access ng isang naka-imbak na function ang isang view na lumalabag sa alinman sa mga naunang panuntunan. Ang view ay isang nakaimbak na utos na SELECT kung saan matatawag ang mga nakaimbak na function.
• Bago ang Oracle11g, positional notation lang ang maaaring gamitin para ipasa ang mga parameter sa mga function. Simula sa Oracle11g, pinapayagan ang pagpasa ng mga parameter ayon sa pangalan at halo-halong notasyon.

Basahin ang Consistency at Custom na Function

Modelo ng pagkakapare-pareho ng pagbasa sa database Data ng Oracle ay simple at prangka: pagkatapos ng pagpapatupad, ang kahilingan ay "nakikita" ang data sa estado kung saan ito umiiral (naitala sa database) sa oras na nagsimula ang kahilingan, na isinasaalang-alang ang mga resulta ng mga pagbabago na ginawa ng mga utos ng DML ng kasalukuyang transaksyon. Kaya, kung tatakbo ang aking query sa 9:00 am at patuloy na tatakbo nang isang oras, kahit na may ibang user na gumawa ng mga pagbabago sa data sa panahong iyon, hindi sila makikita sa aking query.

Ngunit maliban kung gumawa ka ng mga espesyal na pag-iingat sa mga function na tinukoy ng gumagamit sa iyong mga query, maaari mong makita na ang iyong query ay lumalabag (kahit sa ibabaw) ang Oracle database read consistency model. Upang maunawaan ang aspetong ito, isaalang-alang susunod na function at ang query kung saan ito tinatawag na:

FUNCTION total_sales (id_in SA account.account_id%TYPE) RETURN NUMBER IS CURSOR tot_cur IS SELECT SUM (sales) total MULA sa mga order WHERE account_id = id_in AT TO_CHAR (ordered_on, "YYYY") = TO_CHAR (SYSDATE, "YYYY");

tot_rec tot_cur%ROWTYPE; BEGIN OPEN tot_cur;

Kapag nagpapatakbo ng mga query mula sa mga function na tinatawag sa SQL code, kailangan mong maging maingat tungkol sa pagkakapare-pareho ng pagbabasa. Kung tinawag ang mga function na ito sa mga matagal nang query o transaksyon, maaaring gusto mong patakbuhin ang sumusunod na command upang matiyak na pare-pareho ang pagbabasa sa pagitan ng mga SQL command ng kasalukuyang transaksyon: SET TRANSACTION READ ONLY

Sa kasong ito, kailangang mag-ingat upang matiyak na mayroong sapat na espasyo sa pag-undo ng mesa.

Pagtukoy sa Mga Routine ng PL/SQL sa Mga SQL Command (12.1 at mas mataas)

Matagal nang natawagan ng mga developer ang kanilang PL/SQL function mula sa mga SQL command. Sabihin nating gumawa ako ng function na tinatawag na BETWNSTR na nagbabalik ng substring na may ibinigay na panimulang posisyon at pagtatapos:

FUNCTION betwnstr (string_in SA VARCHAR2 , start_in SA PLS_INTEGER , end_in SA PLS_INTEGER ) continuation # RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1)); WAKAS;

Maaaring gamitin ang function sa mga query tulad ng sumusunod:

SELECT betwnstr (last_name, 3, 5) MULA sa mga empleyado

Binibigyang-daan ka ng feature na ito na "palawakin" ang wikang SQL na may likas na pag-andar tiyak na aplikasyon, at muling gumamit ng mga algorithm (sa halip na kopyahin). Kasama sa mga disadvantages ng pagpapatakbo ng mga function na tinukoy ng user sa SQL ang pangangailangang lumipat ng konteksto sa pagitan ng SQL at P L/SQL execution engine. Simula sa Oracle Database 12c, maaari mong tukuyin ang mga function at procedure ng PL/SQL sa WITH clause ng isang subquery para magamit mo ang mga ito tulad ng anumang built-in o custom na function. Binibigyang-daan ka ng feature na ito na pagsamahin ang isang function at isang query sa isang command:

WITH FUNCTION betwnstr (string_in SA VARCHAR2, start_in SA PLS_INTEGER, end_in SA PLS_INTEGER) RETURN VARCHAR2 IS BEGIN RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));

WAKAS; SELECT betwnstr (last_name, 3, 5) MULA sa mga empleyado Ang pangunahing bentahe ng solusyon na ito ay ang pagtaas ng pagganap, dahil sa ganitong kahulugan ng mga function, ang halaga ng paglipat ng konteksto mula sa SQL engine patungo sa PL/SQL engine ay makabuluhang nabawasan. Sa kabilang banda, kailangan mong bayaran ito nang may pagkakataon muling gamitin

lohika sa ibang bahagi ng application.

SQL> GUMAWA O PALITAN ANG PACKAGE pkg 2 AY 3 year_number CONSTANT INTEGER:= 2013;

4 WAKAS;

5/Package na ginawa. SQL> SELECT pkg.year_number FROM employees 2 WHERE employee_id = 138 3 / SELECT pkg.year_number FROM employees * ERROR sa linya 1: ORA-06553: PLS-221: "YEAR_NUMBER" ay hindi isang procedure o hindi natukoy

Ang klasikong workaround ay batay sa pagtukoy ng isang function sa isang package at pagkatapos ay tawagan ito:

SQL> GUMAWA O PALITAN ANG PACKAGE pkg 2 AY 3 FUNCTION year_number 4 RETURN INTEGER;

5 WAKAS; 6/Package na ginawa. SQL> GUMAWA O PALITAN ANG PACKAGE BODY pkg 2 AY 3 c_year_number CONSTANT INTEGER:= 2013; 4 5 FUNCTION year_number 6 RETURN INTEGER 7 IS 8 BEGIN 9 RETURN c_year_number;

10 WAKAS;

11 WAKAS;

12 / Nagawa ang katawan ng package. SQL> PUMILI pkg.year_number 2 MULA sa mga empleyado 3 WHERE employee_id = 138 4 / YEAR NUMBER ------------ 2013

SQL> BEGIN 2 NA MAY FUNCTION full_name (fname_in SA VARCHAR2, lname_in SA VARCHAR2) 3 IBABALIK ANG VARCHAR2 4 AY 5 BEGIN 6 RETURN fname_in || " " || lname_in;

7 WAKAS;

8 9 PUMILI NG HABA (full_name (first_name, last_name)) 10 INTO c 11 MULA sa mga empleyado;

12 13 DBMS_OUTPUT.put_line("bilang = " || c);

14 WAKAS;

15 / MAY FUNCTION full_name (fname_in SA VARCHAR2, lname_in SA VARCHAR2) * ERROR sa linya 2: ORA-06550: line 2, column 18: PL/SQL: ORA-00905: nawawalang keyword Bilang karagdagan sa sugnay na WITH FUNCTION, ipinakilala din ng bersyon 12.1 ang isang direktiba ng UDF upang mapabuti ang pagganap ng mga function ng PL/SQL na isinagawa mula sa SQL. .

Ang mga programming language ay karaniwang may dalawang uri ng routine:

nakaimbak na mga pamamaraan; User Defined Functions (UDFs). Tulad ng tinalakay sa nakaraang artikulo, ang mga naka-imbak na pamamaraan ay binubuo ng ilang mga pahayag at may zero sa ilang mga parameter ng pag-input, ngunit karaniwang hindi nagbabalik ng anumang mga parameter. Hindi tulad ng mga naka-imbak na pamamaraan, ang mga function ay palaging nagbabalik ng isang halaga. Sa seksyong ito titingnan natin ang paggawa at paggamit

User Defined Functions (UDF)

Paggawa at Pagpapatupad ng Mga Function na Tinukoy ng User Ang mga function na tinukoy ng gumagamit ay nilikha gamit ang GUMAWA ng mga pahayag ng FUNCTION

, na mayroong sumusunod na syntax: GUMAWA NG FUNCTION function_name [((@param ) type [= default]) (,...) RETURNS (scalar_type | [@variable] TABLE) (block | RETURN (select_statement)) Syntax convention

Maaaring maging scalar o table ang mga function na tinukoy ng user. Ang mga scalar function ay nagbabalik ng atomic (scalar) na halaga. Nangangahulugan ito na ang sugnay na RETURNS ng scalar function ay tumutukoy sa isa sa mga karaniwang uri ng data. Ang isang function ay tabular kung ang RETURNS clause ay nagbabalik ng isang set ng mga row.

MAY opsyon na ENCRYPTION V direktoryo ng system nag-e-encode ng impormasyon na naglalaman ng text ng CREATE FUNCTION statement. Pinipigilan nito ang hindi awtorisadong pagtingin sa teksto na ginamit upang lumikha ng function. Binibigyang-daan ka ng opsyong ito na pataasin ang seguridad ng iyong database system.

Alternatibo MAY SCHEMABINDING sugnay nagbubuklod ng UDF sa mga object ng database na ina-access ng function na ito. Pagkatapos nito, ang anumang pagtatangka na baguhin ang object ng database na na-access ng function ay nabigo. (Ang pag-binding ng isang function sa mga object ng database na ina-access nito ay aalisin lamang kapag binago ang function, pagkatapos nito ay hindi na tinukoy ang SCHEMABINDING.)

Upang magamit ang sugnay na SCHEMABINDING sa panahon ng paglikha ng function, ang mga object ng database na na-access ng function ay dapat matugunan ang mga sumusunod na kundisyon:

lahat ng view at iba pang UDF na na-access ng tinukoy na function ay dapat na naka-schema-bound;

lahat ng mga object ng database (mga talahanayan, view, at UDF) ay dapat nasa parehong database tulad ng tinukoy na function.

Ang block parameter ay tumutukoy sa isang BEGIN/END block na naglalaman ng pagpapatupad ng function. Ang huling tagubilin block ay dapat maglaman ng isang RETURN pagtuturo na may isang argumento. (Ang value ng argument ay ang return value ng function.) Tanging ang mga sumusunod na statement ang pinapayagan sa loob ng BEGIN/END block:

mga tagubilin sa pagtatalaga tulad ng SET;

mga pahayag upang kontrolin ang pag-unlad ng pagpapatupad, tulad ng WHILE at IF;

IDEKLARA ang mga pahayag, na nagdedeklara ng mga lokal na variable;

PUMILI ng mga pahayag, na naglalaman ng mga listahan ng mga hanay ng pagpili na may mga expression na ang mga halaga ay itinalaga sa mga variable na lokal sa function na ito;

INSERT, UPDATE, at DELETE na mga pahayag, na nagbabago sa mga variable ng uri ng TABLE na lokal sa function.

Bilang default, ang CREATE FUNCTION statement ay maaari lamang gamitin ng mga miyembro ng sysadmin fixed server role at db_owner o db_ddladmin fixed database roles. Gayunpaman, maaaring italaga ng mga miyembro ng mga tungkuling ito ang karapatang ito sa ibang mga user gamit ang pahayag na GRANT CREATE FUNCTION.

Ipinapakita ng halimbawa sa ibaba kung paano lumikha ng function na ComputeCosts:

GAMITIN ang SampleDb; -- Kinakalkula ng function na ito ang karagdagang kabuuang gastos na natamo kapag nag-budget ng proyekto -- taasan ang GO CREATE FUNCTION ComputeCosts (@percent INT = 10) RETURNS DECIMAL(16, 2) BEGIN DECLARE @addCosts DEC (14,2), @sumBudget DEC(16 ,2) PUMILI @sumBudget = SUM (Badyet) MULA SA Project SET @addCosts = @sumBudget * @percent/100 RETURN @addCosts END;

Kinakalkula ng function ng ComputeCosts ang mga karagdagang gastos na lalabas kapag tumaas ang mga badyet ng proyekto. Tinutukoy ng isang parameter ng input, @percent, ang pagtaas ng porsyento sa mga badyet. Ang BEGIN/END block ay unang nagdedeklara ng dalawang lokal na variable: @addCosts at @sumBudget, at pagkatapos ay gumagamit ng SELECT statement upang italaga ang kabuuang kabuuan ng lahat ng badyet sa variable na @sumBudget. Kinakalkula ng function ang kabuuang overhead at ibinabalik ang halagang iyon gamit ang RETURN statement.

Pagtawag sa Function na Tinukoy ng User

Ang isang function na tinukoy ng gumagamit ay maaaring tawagan gamit ang mga pahayag ng Transact-SQL tulad ng SELECT, INSERT, UPDATE, o DELETE. Tinatawag ang isang function sa pamamagitan ng pagtukoy sa pangalan nito na sinusundan ng isang pares ng mga panaklong sa dulo, kung saan maaaring tukuyin ang isa o higit pang mga argumento. Ang mga argumento ay mga value o expression na ipinapasa sa mga parameter ng input na tinukoy kaagad pagkatapos ng pangalan ng function. Kapag tumatawag sa isang function na walang mga default na halaga na tinukoy para sa mga parameter nito, dapat kang magbigay ng mga argumento para sa lahat ng mga parameter na iyon sa parehong pagkakasunud-sunod kung saan ang mga parameter na iyon ay tinukoy sa CREATE FUNCTION statement.

Ang halimbawa sa ibaba ay nagpapakita ng isang tawag sa ComputeCosts function sa isang SELECT statement:

Ang SELECT statement sa halimbawa ay nagpapakita ng mga pangalan at numero ng lahat ng proyekto na ang mga badyet ay mas mababa kaysa sa kabuuang incremental na gastos ng lahat ng mga proyekto para sa isang partikular na halaga ng pagtaas ng porsyento.

Sa mga statement ng Transact-SQL, dapat na tukuyin ang mga pangalan ng function gamit ang dalawang bahaging pangalan: pangalan ng schema at pangalan ng function, kaya ginamit namin ang prefix ng dbo schema sa halimbawa.

Mga function na pinahahalagahan ng talahanayan

Tulad ng nabanggit kanina, ang function ay isang return function halaga ng talahanayan kung ang sugnay na RETURNS nito ay nagbabalik ng isang hanay ng mga hilera. Depende sa kung paano tinukoy ang function body, ang mga function na pinahahalagahan ng talahanayan ay inuri bilang built-in (inline) At multistatement. Kung ang TABLE na keyword ay tinukoy sa isang RETURNS clause na walang kasamang listahan ng mga column, ang function ay inline. Ibinabalik ng SELECT statement ng isang inline na function ang set ng resulta bilang variable na may uri ng data TABLE.

Ang multistatement table-valued function ay naglalaman ng isang pangalan na tumutukoy sa isang panloob na variable ng uri ng TABLE. Ang uri ng data na ito ay ipinahiwatig keyword TABLE, na sumusunod sa variable na pangalan. Ang mga napiling row ay ipinapasok sa variable na ito at nagsisilbing return value ng function.

Ang paglikha ng isang function na pinahahalagahan ng talahanayan ay ipinapakita sa halimbawa sa ibaba:

Ang EmployeesInProject function ay nagpapakita ng mga pangalan ng lahat ng empleyadong nagtatrabaho sa isang partikular na proyekto, ang bilang nito ay tinukoy ng @projectNumber input parameter. Habang ang isang function ay karaniwang nagbabalik ng isang hanay ng mga row, ang RETURNS clause sa kahulugan ng function ay naglalaman ng TABLE na keyword upang ipahiwatig na ang function ay nagbabalik ng isang halaga ng talahanayan. (Tandaan na sa halimbawa ang BEGIN/END block ay dapat tanggalin at ang RETURN clause ay naglalaman ng SELECT statement.)

Ang paggamit ng Employees_in_Project function ay ipinapakita sa halimbawa sa ibaba:

GAMITIN ang SampleDb; PUMILI * MULA SA EmployeesInProject("p3")

Resulta ng pagpapatupad:

Mga function na may halaga sa talahanayan at ang pahayag na APPLY

Relational Ilapat ang mga tagubilin nagbibigay-daan sa iyo na tumawag sa isang function na pinahahalagahan ng talahanayan para sa bawat hilera ng isang expression ng talahanayan. Ang pahayag na ito ay tinukoy sa sugnay na FROM ng kaukulang SELECT statement sa parehong paraan tulad ng isang JOIN statement. Ang APPLY na pahayag ay maaaring isama sa isang function ng talahanayan upang makabuo ng isang resulta na katulad ng set ng resulta ng isang operasyon ng pagsasama sa pagitan ng dalawang talahanayan. Mayroong dalawang anyo ng pahayag na APPLY:

CROSS APPLY na mga tagubilin ibinabalik ang mga row na iyon mula sa panloob (kaliwa) na expression ng talahanayan na tumutugma sa panlabas (kanan) na expression ng talahanayan. Kaya, sa lohikal na paraan, ang CROSS APPLY na pahayag ay gumagana katulad ng INNER JOIN na pahayag.

OUTER APPLY na pagtuturo ibinabalik ang lahat ng mga hilera mula sa panloob (kaliwa) na expression ng talahanayan. (Para sa mga row kung saan walang mga tugma sa panlabas na expression ng talahanayan, naglalaman ito ng mga NULL na halaga sa mga column ng panlabas na expression ng talahanayan.) Logically, ang OUTER APPLY na pahayag ay katumbas ng LEFT OUTER JOIN na pahayag.

Ang paggamit ng APPLY na pahayag ay ipinapakita sa mga halimbawa sa ibaba:

Ang GetJob() function ay nagbabalik ng set ng mga row mula sa Works_on table. Sa halimbawa sa ibaba, ang hanay ng resultang ito ay "sinamahan" ng isang sugnay na APPLY na may mga nilalaman ng talahanayan ng Empleyado:

GAMITIN ang SampleDb; -- Ginamit na CROSS APPLY SELECT E.Id, FirstName, LastName, Job FROM Employee bilang E CROSS APPLY GetJob(E.Id) AS A -- Nagamit na OUTER APPLY SELECT E.Id, FirstName, LastName, Job FROM Employee bilang E OUTER APPLY GetJob(E.Id) AS A

Ang resulta ng pagpapatupad ng dalawang function na ito ay ang sumusunod na dalawang talahanayan (ipapakita pagkatapos isagawa ang pangalawang function):

Sa unang query ng halimbawa, ang set ng resulta ng GetJob() table function ay "join" sa mga nilalaman ng Employee table gamit ang isang CROSS APPLY na pahayag. Ang GetJob() function ay gumaganap bilang ang tamang input, at ang Employee table ay gumaganap bilang ang kaliwang input. Ang tamang input expression ay sinusuri para sa bawat kaliwang input row, at ang mga resultang row ay pinagsama-sama upang lumikha ng huling resulta.

Ang pangalawang query ay katulad ng una (ngunit ginagamit nito ang OUTER APPLY na pahayag), na lohikal na tumutugma sa isang panlabas na operasyon ng pagsasama sa pagitan ng dalawang talahanayan.

Mga parameter na may halaga sa talahanayan

Sa lahat ng mga bersyon ng server na mas maaga kaysa sa SQL Server 2008, ang gawain ay ipinapasa sa nakagawiang maramihang mga parameter ay puno ng mga makabuluhang paghihirap. Upang gawin ito, kailangan mo munang lumikha ng isang pansamantalang talahanayan, ipasok ang mga naipasa na halaga dito, at pagkatapos ay maaari mong tawagan ang subroutine. Simula sa SQL Server 2008, ang gawaing ito ay pinasimple sa pamamagitan ng pagpapahintulot sa paggamit ng mga parameter na may halaga sa talahanayan kung saan maipapasa ang set ng resulta sa naaangkop na gawain.

Ang paggamit ng isang parameter na may halaga sa talahanayan ay ipinapakita sa halimbawa sa ibaba:

GAMITIN ang SampleDb; GUMAWA NG URI ng departamentoType AS TABLE (Numero CHAR(4), Pangalan ng Kagawaran CHAR(40), Lokasyon CHAR(40)); GO CREATE TABLE #moscowTable (Number CHAR(4), DepartmentName CHAR(40), Location CHAR(40)); GO CREATE PROCEDURE InsertProc @Moscow departmentType READONLY AS SET NOCOUNT ON INSERT INTO #moscowTable (Number, DepartmentName, Location) SELECT * FROM @Moscow GO DECLARE @Moscow AS departmentType; INSERT INTO @Moscow (Number, DepartmentName, Location) SELECT * FROM department WHERE location = "Moscow"; EXEC InsertProc @Moscow;

Ang halimbawang ito ay unang tumutukoy sa isang uri ng talahanayan, departmentType. Nangangahulugan ito na ang uri ay isang TABLE na uri ng data, na nangangahulugang pinapayagan nito ang pagpasok ng row. Idineklara ng pamamaraan ng InsertProc ang variable na @Moscow na may uri ng data ng departmentType. (Ang READONLY clause ay tumutukoy na ang mga nilalaman ng talahanayang ito ay hindi dapat baguhin.) Sa isang kasunod na batch, ang data ay ipinasok sa variable ng talahanayan na ito, at ang pamamaraan ay pagkatapos ay tatakbo. Sa panahon ng pagpapatupad, ang pamamaraan ay naglalagay ng mga row mula sa isang variable ng talahanayan sa pansamantalang talahanayan na #moscowTable. Ang mga ipinasok na nilalaman ng pansamantalang talahanayan ay ganito ang hitsura:

Ang paggamit ng mga parameter na may halaga sa talahanayan ay nagbibigay ng mga sumusunod na benepisyo:

ang modelo ng subroutine programming ay pinasimple;

ang bilang ng mga kahilingan sa server at pagtanggap ng kaukulang mga tugon ay nabawasan;

ang talahanayan ng resulta ay maaaring magkaroon ng isang arbitrary na bilang ng mga hilera.

Pagbabago ng istraktura ng mga tagubiling tinukoy ng gumagamit

Sinusuportahan din ng Transact-SQL ang pahayag ALTER FUNCTION, na nagbabago sa istruktura ng mga tagubiling tinukoy ng gumagamit (UDF). Ang pagtuturong ito ay karaniwang ginagamit upang alisin ang pagkakatali ng isang function sa isang circuit. Ang lahat ng mga parameter ng ALTER FUNCTION statement ay may parehong kahulugan sa parehong mga parameter ng CREATE FUNCTION statement.

Upang alisin ang UDF ito ay ginagamit DROP FUNCTION na pagtuturo. Tanging ang may-ari o isang miyembro ng db_owner o sysadmin fixed role ang makakapagtanggal ng isang function.

Mga function na tinukoy ng user at ang CLR

Sa nakaraang artikulo, tiningnan namin kung paano lumikha ng mga nakaimbak na pamamaraan mula sa pinamamahalaang karaniwang runtime code ng wika sa C#. Ang diskarte na ito ay maaari ding gamitin para sa user-defined functions (UDFs), na ang kaibahan lang ay ginagamit mo ang CREATE FUNCTION statement sa halip na ang CREATE PROCEDURE statement para iimbak ang UDF bilang database object. Bukod pa rito, ginagamit din ang mga function na tinukoy ng user sa ibang konteksto kaysa sa mga nakaimbak na pamamaraan dahil palaging nagbabalik ng halaga ang mga UDF.

Ang halimbawa sa ibaba ay nagpapakita ng source code para sa user-defined function (UDFs) na ipinatupad sa C#:

Gamit ang System.Data.SqlTypes; public class BudgetPercent ( private const float percent = 12; public static SqlDouble ComputeBudget(float budget) ( return budget * percent; ) )

Kinakalkula ng halimbawa ng source code ng function na tinukoy ng gumagamit ang isang bagong badyet ng proyekto sa pamamagitan ng pagtaas ng lumang badyet ng isang tiyak na porsyento. Maaari mong gamitin ang CREATE ASSEMBLY na pahayag upang lumikha ng isang CLR assembly sa database, tulad ng ipinakita kanina. Kung nagtrabaho ka sa mga halimbawa sa nakaraang artikulo at naidagdag na ang pagpupulong ng CLRStoredProcedures sa database, maaari mong i-update ang pagpupulong na ito pagkatapos i-recompile ito ng isang bagong klase (Ang CLRStoredProcedures ay ang pangalan ng aking proyekto sa C# classes, kung saan idinagdag ko ang kahulugan ng mga naka-imbak na pamamaraan at pag-andar, mayroon kang pagpupulong ay maaaring tawaging naiiba).