Захоўваемыя працэдуры SQL: стварэнне і выкарыстанне

Захоўваемыя працэдуры SQL ўяўляюць сабой выкананы праграмны модуль, які можа захоўвацца ў базе дадзеных у выглядзе розных аб'ектаў. Іншымі словамі, гэта аб'ект, у якім змяшчаюцца SQL-інструкцыі. Гэтыя захоўваемыя працэдуры могуць быць выкананы ў кліенце прыкладных праграм, каб атрымаць добрую прадукцыйнасць. Акрамя таго, такія аб'екты нярэдка выклікаюцца з іншых сцэнарыяў ці нават з якога-небудзь іншага падзелу.

ўвядзенне

Многія лічаць, што яны падобныя на працэдуры розных моў праграмавання высокага ўзроўню (адпаведна, акрамя MS SQL). Мабыць, гэта сапраўды так. У іх ёсць падобныя параметры, яны могуць выдаваць падобныя значэння. Больш за тое, у шэрагу выпадкаў яны датыкаюцца. Напрыклад, яны спалучаюцца з базамі дадзеных DDL і DML, а таксама з функцыямі карыстальніка (кодавы назоў - UDF).

У рэчаіснасці ж захоўваемыя працэдуры SQL валодаюць шырокім спектрам пераваг, якія вылучаюць іх сярод падобных працэсаў. Бяспека, варыятыўнасць праграмавання, прадуктыўнасць - усё гэта прыцягвае карыстальнікаў, якія працуюць з базамі дадзеных, усё больш і больш. Пік папулярнасці працэдур прыйшоўся на 2005-2010 гады, калі выйшла праграма ад "Майкрасофт" пад назвай «SQL Server Management Studio». З яе дапамогай працаваць з базамі дадзеных стала значна прасцей, практычней і зручней. З году ў год такі спосаб перадачы інфармацыі набіраў папулярнасць у асяроддзі праграмістаў. Сёння ж MS SQL Server з'яўляецца абсалютна звыклай праграмай, якая для карыстальнікаў, «якія маюць зносіны» з базамі дадзеных, ўстала нароўні з «Эксэля».

Пры выкліку працэдуры яна маментальна апрацоўваецца самім серверам без лішніх працэсаў і ўмяшання карыстальніка. Пасля гэтага можна ажыццяўляць любыя дзеянні з інфармацыяй: выдаленне, выкананне, змяненне. За ўсё гэта адказвае DDL-аператар, які ў адзіночку здзяйсняе вельмі складаныя дзеянні па апрацоўцы аб'ектаў. Прычым усё гэта адбываецца вельмі хутка, а сервер фактычна не нагружаецца. Такая хуткасць і прадукцыйнасць дазваляюць вельмі хутка перадаваць вялікія аб'ёмы інфармацыі ад карыстальніка на сервер і наадварот.

Для рэалізацыі дадзенай тэхналогіі працы з інфармацыяй існуе некалькі моў праграмавання. Да іх можна аднесці, напрыклад, PL / SQL ад сістэмы кіравання базамі дадзеных Oracle, PSQL ў сістэмах InterBase і Firebird, а таксама класічны «Майкрасофтаўскі» Transact-SQL. Усе яны прызначаны для стварэння і выканання захоўваемых працэдур, што дазваляе ў буйных апрацоўшчык баз выкарыстоўваць уласныя алгарытмы. Гэта трэба і для таго, каб тыя, хто ажыццяўляе кіраванне такой інфармацыяй, маглі абараніць ўсе аб'екты ад несанкцыянаванага доступу да іншых асоб і, адпаведна, стварэння, змены або выдаленні тых ці іншых дадзеных.

прадуктыўнасць

Гэтыя аб'екты баз дадзеных могуць быць запраграмаваныя рознымі шляхамі. Гэта дазваляе карыстальнікам выбіраць тып выкарыстоўванага спосабу, які будзе найбольш прыдатным, што эканоміць сілы і час. Акрамя таго, працэдура сама апрацоўваецца, што дазваляе пазбегнуць велізарных часавых выдаткаў на абмен паміж серверам і карыстальнікам. Таксама модуль можна перапраграмаваць і змяніць у патрэбны кірунак у абсалютна любы момант. Асабліва варта адзначыць хуткасць, з якой адбываецца запуск захоўваемай працэдуры SQL: гэта працэс адбываецца хутчэй іншых, падобных з ім, што робіць яго зручным і універсальным.

бяспеку

Такі тып апрацоўкі інфармацыі адрозніваецца ад падобных працэсаў тым, што ён гарантуе павышаную бяспеку. Гэта забяспечваецца за кошт таго, што доступ іншых карыстальнікаў да працэдур можа быць выключаны цалкам і цалкам. Гэта дазволіць адміністратару праводзіць аперацыі з імі самастойна, не баючыся за перахоп інфармацыі або несанкцыянаваны доступ да базы дадзеных.

перадача дадзеных

Сувязь паміж захоўваемай працэдурай SQL і кліенцкім прыкладаннем заключаецца ў выкарыстанні параметраў і што вяртаюцца значэннях. Апошнім не абавязкова перадаваць дадзеныя ў зберажоную працэдуру, аднак гэтая інфармацыя (у асноўным па запыту карыстальніка) і перапрацоўваецца для SQL. Пасля таго як захоўваемая працэдура завяршыла сваю працу, яна адсылае пакеты дадзеных назад (але, зноў жа, па жаданні) да якая выклікала яго з дадаткам, выкарыстоўваючы розныя метады, з дапамогай якіх можа быць ажыццёўлены як выклік захоўваемай працэдуры SQL, так і вяртанне, напрыклад:

- перадача дадзеных з дапамогай параметру тыпу Output;

- перадача дадзеных з дапамогай аператара вяртання;

- перадача дадзеных з дапамогай аператара выбару.

А цяпер разбярэмся, як жа выглядае гэты працэс знутры.

1. Стварэнне EXEC-захоўваемай працэдуры ў SQL

Вы можаце стварыць працэдуру ў MS SQL (Managment Studio). Пасля таго як створыцца працэдура, яна будзе пералічаная ў праграмуемы вузел базы дадзеных, у якой працэдура стварэння выконваецца аператарам. Для выканання захоўваемыя працэдуры SQL выкарыстоўваюць EXEC-працэс, які змяшчае імя самога аб'екта.

Пры стварэнні працэдуры яе назва з'яўляецца першым, пасля чаго вырабляецца адзін або некалькі параметраў, прысвоеных яму. Параметры могуць быць неабавязковымі. Пасля таго як параметр (ы), то ёсць цела працэдуры, будуць напісаны, трэба правесці некаторыя неабходныя аперацыі.

Справа ў тым, што цела можа мець лакальныя зменныя, размешчаныя ў ёй, і гэтыя зменныя з'яўляюцца лакальнымі таксама ў адносінах да працэдур. Іншымі словамі, іх можна разглядаць толькі ўнутры цела працэдуры Microsoft SQL Server. Захоўваемыя працэдуры ў такім выпадку лічацца лакальнымі.

Такім чынам, каб стварыць працэдуру, нам трэба імя працэдуры і, па меншай меры, адзін параметр у якасці цела працэдуры. Звярніце ўвагу, што выдатным варыянтам у такім выпадку з'яўляецца стварэнне і выкананне працэдуры з імем схемы ў класіфікатары.

Цела працэдуры можа мець любы від з аператараў SQL, напрыклад, такія як стварэнне табліцы, ўстаўкі аднаго або некалькіх радкоў табліцы, усталяванне тыпу і характару базы дадзеных і гэтак далей. Тым не менш цела працэдуры абмяжоўвае выкананне некаторых аперацый у ім. Некаторыя з важных абмежаванняў пералічаныя ніжэй:

- цела не павінна ствараць якой-небудзь іншай захоўваемай працэдуры;

- цела не павінна стварыць няправільнае ўяўленне аб аб'екце;

- цела не павінна ствараць ніякіх трыгераў.

2. Усталяванне зменнай ў цела працэдуры

Вы можаце зрабіць зменныя лакальнымі для цела працэдуры, і тады яны будуць знаходзіцца выключна ўнутры цела працэдуры. Добрай практыкай з'яўляецца стварэнне зменных у пачатку цела захоўваемай працэдуры. Але таксама вы можаце ўсталёўваць зменныя ў любым месцы ў целе дадзенага аб'екта.

Часам можна заўважыць, што некалькі зменных ўстаноўлены ў адным радку, і кожны пераменны параметр аддзяляецца коскі. Таксама зьвярніце ўвагу, што пераменная мае прэфікс @. У целе працэдуры вы можаце ўсталяваць пераменную, куды вы хочаце. Да прыкладу, пераменная @ NAME1 можа аб'яўлена бліжэй да канца цела працэдуры. Для таго каб прысвоіць значэнне абвешчанай зменнай выкарыстоўваецца набор асабістых дадзеных. У адрозненне ад сітуацыі, калі абвешчана больш адной зменнай у адным радку, у такой сітуацыі выкарыстоўваецца толькі адзін набор асабістых дадзеных.

Часта карыстальнікі задаюць пытанне: «Як прызначыць некалькі значэнняў у адным аператары ў целе працэдуры?» Што ж. Пытанне цікавы, але зрабіць гэта значна прасцей, чым вы думаеце. Адказ: з дапамогай такіх пар, як «Select Var = значэнне". Вы можаце выкарыстоўваць гэтыя пары, падзяляючы іх коскі.

3. Стварэнне захоўваемай працэдуры SQL

У самых розных прыкладах людзі паказваюць стварэнне просты захоўваемай працэдуры і выкананне яе. Аднак працэдура можа прымаць такія параметры, што выклікае яе працэс будзе мець значэння, блізкія да яго (але не заўсёды). Калі яны супадаюць, то ўнутры цела пачынаюцца адпаведныя працэсы. Напрыклад, калі стварыць працэдуру, якая будзе прымаць горад і рэгіён ад выклікае абанента і вяртаць дадзеныя пра тое, колькі аўтараў ставяцца да адпаведных горадзе і рэгіёне. Працэдура будзе запытваць табліцы аўтараў базы дадзеных, да прыкладу, Pubs, для выканання гэтага падліку аўтараў. Каб атрымаць гэтыя базы дадзеных, да прыкладу, Google загружае сцэнар SQL са старонкі SQL2005.

У папярэднім прыкладзе працэдура прымае два параметру, якія на англійскай мове ўмоўна будуць называцца @State і @City. Тып дадзеных адпавядае тыпу, вызначанага ў дадатку. Цела працэдуры мае ўнутраныя зменныя @TotalAuthors (усяго аўтараў), і гэтая пераменная выкарыстоўваецца для адлюстравання іх колькасці. Далей з'яўляецца раздзел выбару запыту, які ўсё падлічвае. Нарэшце, што падлiчаны значэнне выводзіцца ў акне вываду з дапамогай аператара друку.

Як у SQL выканаць зберажоную працэдуру

Ёсць два спосабу выканання працэдуры. Першы шлях паказвае, перадаючы параметры, як падзелены коскамі спіс выконваецца пасля імя працэдуры. Дапусцім, мы маем два значэння (як у папярэднім прыкладзе). Гэтыя значэння збіраюцца з дапамогай зменных параметраў працэдуры @State і @City. У гэтым спосабе перадачы параметраў важны парадак. Такі метад называецца парадкавая перадача аргументаў. У другім спосабе параметры ўжо непасрэдна прызначаныя, і ў гэтым выпадку парадак не важны. Гэты другі метад вядомы як перадача найменных аргументаў.

Працэдура можа некалькі адхіляцца ад тыповай. Усё гэтак жа, як і ў папярэднім прыкладзе, але толькі тут параметры зрушваюцца. Гэта значыць параметр @City захоўваецца першым, а @State захоўваецца побач са значэннем па змаўчанні. Параметр па змаўчанні вылучаецца звычайна асобна. Захоўваемыя працэдуры SQL праходзяць як проста параметры. У гэтым выпадку, пры ўмове, параметр «UT» замяняе значэнне па змаўчанні «СА». У другім выкананні праходзіць толькі адно значэнне аргументу для параметру @City, і параметр @State прымае значэнне па змаўчанні «СА». Дасведчаныя праграмісты раяць, каб усе зменныя па змаўчанні размяшчаліся бліжэй да канца спісу параметраў. У адваротным выпадку выкананне не ўяўляецца магчымым, і тады вы павінны працаваць з перадачай найменных аргументаў, што даўжэй і складаней.

4. Захоўваемыя працэдуры SQL Server: спосабы вяртання

Існуе тры важных спосабу адпраўкі дадзеных у выкліканай захоўваемай працэдуры. Яны пералічаны ніжэй:

- вяртанне значэння захоўваемай працэдуры;

- выхад параметру захоўваемых працэдур;

- выбар адной з захоўваемых працэдур.

4.1 Зварот значэнняў захоўваемых працэдур SQL

У гэтай методыцы працэдура прысвойвае значэнне лакальнай зменнай і вяртае яго. Працэдура можа таксама непасрэдна вяртаць пастаяннае значэнне. У наступным прыкладзе, мы стварылі працэдуру, якая вяртае агульная колькасць аўтараў. Калі параўнаць гэтую працэдуру з папярэднімі, вы можаце ўбачыць, што значэнне для друку замяняецца адваротным.

Зараз давайце паглядзім, як выканаць працэдуру і вывесці значэнне, якое вяртаецца ёй. Выкананне працэдуры патрабуе ўстанаўлення зменнай і друку, якая праводзіцца пасля ўсяго гэтага працэсу. Звярніце ўвагу, што замест аператара друку вы можаце выкарыстоўваць Select-аператар, напрыклад, Select @RetValue, а таксама OutputValue.

4.2 Выхад параметру захоўваемых працэдур SQL

Адказнае значэнне можа быць выкарыстана для вяртання адной зменнай, што мы і бачылі ў папярэднім прыкладзе. Выкарыстанне параметру Output дазваляе працэдуры адправіць адно або некалькі значэнняў зменных для выклікалага боку. Выхадны параметр пазначаецца як раз-ткі гэтым ключавым словам «Output» пры стварэнні працэдуры. Калі параметр зададзены ў якасці выхаднога параметра, то аб'ект працэдуры павінен надаць яму значэнне. Захоўваемыя працэдуры SQL, прыклады якіх можна ўбачыць ніжэй, у такім выпадку вяртаюцца з выніковай інфармацыяй.

У нашым прыкладзе будзе два выходных імя: @TotalAuthors і @TotalNoContract. Яны паказваюцца ў спісе параметраў. Гэтыя зменныя прысвойваюць значэння ўнутры цела працэдуры. Калі мы выкарыстоўваем выходныя параметры, выклікае абанент можа ўбачыць значэнне, усталяванае ўнутры цела працэдуры.

Акрамя таго, у папярэднім сцэнары дзве зменныя абвяшчаюцца, каб убачыць значэння, якія установливают захоўваемыя працэдуры MS SQL Server ў выходным параметры. Тады працэдура выконваецца шляхам падачы нармальнага значэння параметру «CA». Наступныя параметры з'яўляюцца выходнымі і, такім чынам, абвешчаныя зменныя перадаюцца ва ўстаноўленым парадку. Звярніце ўвагу, што пры праходжанні зменных выходную ключавое слова таксама задаецца тут. Пасля таго, як працэдура выканана паспяхова, значэння, якiя вяртаюцца з дапамогай выходных параметраў, выводзяцца на акно паведамленняў.

4.3 Выбар адной з захоўваемых працэдур SQL

Гэтая тэхніка выкарыстоўваецца для вяртання набору значэнняў у выглядзе табліцы дадзеных (RecordSet) да выклікалай захоўваемай працэдуры. У гэтым прыкладзе SQL захоўваемая працэдура з параметрамі @AuthID запытвае табліцу «Аўтары» шляхам фільтрацыі якiя вяртаюцца запісаў з дапамогай гэтага параметру @AuthId. Аператар Select вырашае, што павінна быць вернута выклікаламу захоўваемай працэдуры. Пры выкананні захоўваемай працэдуры AuthId перадаецца назад. Такая працэдура тут заўсёды вяртае толькі адзін запіс ці ж наогул ніводнай. Але захоўваемая працэдура не мае якіх-небудзь абмежаванняў на вяртанне больш аднаго запісу. Нярэдка можна сустрэць прыклады, у якіх вяртанне дадзеных з выкарыстаннем абраных параметраў з удзелам вылічаных зменных адбываецца шляхам прадастаўлення некалькіх выніковых значэнняў.

У заключэнне

Захоўваемая працэдура з'яўляецца даволі сур'ёзным праграмным модулем, якія вяртаюць або якія перадаюць, а таксама ўстанаўлівае неабходныя зменныя дзякуючы кліенцкага прыкладання. Паколькі захоўваемая працэдура выконваецца на сэрвэры сама, абмену дадзенымі ў велізарных аб'ёмах паміж серверам і кліенцкім прыкладаннем (для некаторых вылічэнняў) можна пазбегнуць. Гэта дазваляе зніжаць нагрузкі на сервера SQL, што, вядома ж, ідзе на руку іх трымальнікам. Адным з падвідаў з'яўляюцца захоўваемыя працэдуры T SQL, аднак іх вывучэнне неабходна тым, хто займаецца стварэннем вялікіх баз дадзеных. Таксама існуе вялікая, нават велізарная колькасць нюансаў, якія могуць быць карысныя пры вывучэнні захоўваемых працэдур, аднак гэта трэба больш для тых, хто плануе шчыльна заняцца праграмаваннем, у тым ліку прафесійна.