SQL запомнени процедури: създаване и използване

Anonim

SQL съхранените процедури са изпълним програмен модул, който може да се съхранява в база данни като различни обекти. С други думи, това е обект, който съдържа SQL изрази. Тези съхранени процедури могат да бъдат изпълнени в клиент на приложение, за да се получи добра производителност. В допълнение, такива обекти често се наричат ​​от други скриптове или дори от друг раздел.

въведение

Много хора смятат, че те са подобни на процедурите на различни езици за програмиране на високо ниво (съответно, с изключение на MS SQL). Може би това е вярно. Те имат сходни параметри, могат да произвеждат подобни стойности. Освен това в някои случаи те влизат в контакт. Например, те се комбинират с DDL и DML бази данни, както и с потребителски функции (кодово име - UDF).

Всъщност SQL съхранените процедури имат широк спектър от предимства, които ги отличават от подобни процеси. Сигурност, променливост на програмирането, производителност - всичко това привлича потребителите, работещи с бази данни, все повече и повече. Пикът на популярността на процедурите дойде през 2005-2010 г., когато беше освободена програмата от Microsoft, наречена SQL Server Management Studio. С негова помощ работата с бази данни стана много по-лесна, по-практична и по-удобна. От година на година този метод на предаване на информация придоби популярност сред програмистите. Днес MS SQL Server е абсолютно позната програма, която за потребителите, които „комуникират” с бази данни, е станала равна на Excel.

Когато извикате процедура, тя се обработва незабавно от самия сървър, без излишни процеси и намеса на потребителя. След това можете да извършите всяко действие с информацията: изтриване, изпълнение, модификация. За всичко това е отговорен DDL операторът, който сам извършва най-сложните операции по обработка на обекти. И всичко това се случва много бързо и сървърът не се зарежда. Такава скорост и производителност ви позволяват бързо да прехвърляте големи количества информация от потребителя на сървъра и обратно.

За да се приложи тази технология за работа с информация, има няколко езика за програмиране. Те включват, например, PL / SQL от системата за управление на бази данни на Oracle, PSQL в системите InterBase и Firebird, както и класическия Microsoft Microsoft Transact-SQL. Всички те са предназначени за създаване и изпълнение на съхранени процедури, което позволява използването на големи алгоритми на база данни за използване на собствени алгоритми. Това е необходимо, така че тези, които управляват такава информация, могат да защитят всички обекти от неоторизиран достъп на трети лица и съответно да създават, променят или изтриват определени данни.

продуктивност

Тези обекти на базата данни могат да бъдат програмирани по различни начини. Това позволява на потребителите да изберат най-подходящия тип използван метод, което спестява време и усилия. Освен това самата процедура се обработва, като по този начин се избягва огромното време, прекарано в обмена между сървъра и потребителя. Също така, модулът може да бъде препрограмиран и променен в правилната посока по всяко време. Особено си струва да се отбележи скоростта, с която се стартира SQL съхранената процедура: този процес е по-бърз от други подобни на него, което го прави удобен и гъвкав.

безопасност

Този вид обработка на информация се различава от подобни процеси, тъй като гарантира повишена сигурност. Това се гарантира от факта, че достъпът на други потребители до процедурите може да бъде изцяло изключен. Това ще позволи на администратора да извършва операции с тях самостоятелно, без страх за прихващане на информация или неоторизиран достъп до базата данни.

Пренос на данни

Връзката между SQL запаметената процедура и клиентското приложение е в използването на параметри и връщани стойности. Последните не трябва да прехвърлят данни към съхранената процедура, но тази информация (главно по искане на потребителя) се обработва за SQL. След като съхранената процедура приключи работата си, тя изпраща обратно пакетите с данни (но отново, ако е необходимо) към приложението, което го е извикало, като използва различни методи, които могат да се използват за извикване на съхранената SQL процедура или връщане, например

- пренос на данни чрез тип параметър Output;

- пренос на данни чрез връщане на оператора;

- прехвърляне на данни чрез избрания оператор.

И сега ще разберем как изглежда този процес отвътре.

1. Създаване на EXEC съхранена процедура в SQL

Можете да създадете процедура в MS SQL (Managment Studio). След като процедурата е създадена, тя ще бъде прехвърлена към програмируемия възел на базата данни, в която процедурата за създаване се извършва от оператора. За да изпълните SQL съхранени процедури, използвайте EXEC процеса, който съдържа името на самия обект.

Когато създавате процедура, името й се появява първо, след което се задават един или няколко присвоени на него параметри. Параметрите могат да бъдат незадължителни. След като са написани параметрите, т.е. тялото на процедурата, трябва да извършите някои необходими операции.

Въпросът е, че тялото може да има локални променливи, намиращи се в него, и тези променливи са локални и по отношение на процедурите. С други думи, те могат да се разглеждат само в рамките на процедурата на Microsoft SQL Server. Запазените процедури се считат за местни.

Така, за да създадем процедура, се нуждаем от име на процедура и поне един параметър като тяло на процедурата. Моля, обърнете внимание, че в този случай отлична опция е създаването и изпълнението на процедура с името на схемата в класификатора.

Тялото на процедурата може да бъде от всякакъв вид от SQL изрази, като например създаване на таблица, вмъкване на една или повече редове от таблица, определяне на вида и естеството на базата данни и т.н. Въпреки това органът на процедурата ограничава изпълнението на някои операции в него. Някои от важните ограничения са изброени по-долу:

- тялото не трябва да създава друга запазена процедура;

- тялото не трябва да създава фалшиво представяне на обекта;

- Тялото не трябва да създава никакви тригери.

2. Задаване на променлива в тялото на процедурата

Можете да направите локалните променливи в тялото на процедурата и след това те ще бъдат разположени изключително вътре в тялото на процедурата. Добра практика е да се създадат променливи в началото на тялото на съхранената процедура. Но също така можете да зададете променливи навсякъде в тялото на обекта.

Понякога ще забележите, че няколко променливи са зададени в един ред и всеки параметър на променлива е разделен със запетая. Също така имайте предвид, че променливата има префикс @. В тялото на процедурата можете да зададете променливата там, където искате. Например променливата @ NAME1 може да бъде обявена по-близо до края на процедурното тяло. За да се присвои стойността на декларирана променлива, се използва набор от лични данни. За разлика от ситуация, когато повече от една променлива е декларирана в един ред, в такава ситуация се използва само един набор от лични данни.

Често потребителите задават въпроса: "Как да присвоите няколко стойности в едно изявление в тялото на процедурата?" Въпросът е интересен, но е много по-лесно да се направи, отколкото си мислите. Отговор: използвайки двойки като "Изберете Var = Value". Можете да използвате тези двойки, като ги разделите със запетая.

3. Създаване на SQL запомнена процедура

В различни примери хората показват създаването на проста запаметена процедура и нейното изпълнение. Въпреки това, една процедура може да приеме такива параметри, че нейният процес на повикване ще има стойности, които са близки до него (но не винаги). Ако те съвпадат, съответните процеси започват вътре в тялото. Например, ако създадете процедура, която ще вземе града и региона от повикващия и ще върнете данни за това колко автори принадлежат на съответния град и регион. Процедурата ще потърси таблиците на авторите на базата данни, например Pubs, за да извърши това изчисление на авторите. За да получите тези бази данни, например, Google зарежда SQL скрипта от страницата SQL2005.

В предишния пример процедурата взема два параметъра, които на английски език ще се наричат ​​условно @State и @City. Типът данни съответства на типа, определен в приложението. Тялото на процедурата има вътрешни променливи @TotalAuthors (общо автори) и тази променлива се използва за показване на техния номер. След това идва секцията за избор на заявка, която брои всичко. И накрая, изчислената стойност се показва в изходния прозорец с помощта на оператор за печат.

Как да изпълним съхранена процедура в SQL

Има два начина за изпълнение на процедурата. Първият начин е показан чрез предаване на параметрите като разделен със запетая списък след името на процедурата. Да предположим, че имаме две стойности (както в предишния пример). Тези стойности се събират с помощта на променливите параметри на @State и @City процедурите. В този метод на преминаване на параметрите, редът е важен. Такъв метод се нарича преминаване на ординален аргумент. При втория метод параметрите вече са директно зададени, като в този случай редът не е важен. Този втори метод е известен като преминаващи имена на аргументи.

Процедурата може да се отклони донякъде от типичната. Всичко е същото като в предишния пример, но само тук параметрите се изместват. Това означава, че параметърът @City се съхранява първо, а @State се съхранява до стойността по подразбиране. Параметърът по подразбиране обикновено се разпределя отделно. SQL съхранените процедури се предават като прости параметри. В този случай, ако параметърът "UT" замени стойността по подразбиране "CA". Във втората версия за параметър @City се предава само една стойност на аргумента, а параметър @State приема стойността по подразбиране CA. Опитните програмисти съветват всички променливи да бъдат разположени по подразбиране в края на списъка с параметри. В противен случай изпълнението не е възможно, а след това трябва да се работи с преминаване на име аргументи, което е по-дълго и по-сложно.

4. Съхранени процедури на SQL Server: Методи за връщане

Има три важни начина за изпращане на данни в извикана съхранена процедура. Те са изброени по-долу:

- връща стойността на запаметената процедура;

- изход на параметъра на запомнената процедура;

- избор на една от съхранените процедури.

4.1 Връщане на стойностите на SQL запазената процедура

В тази техника процедурата присвоява стойност на локална променлива и я връща. Процедурата също може директно да върне постоянна стойност. В следващия пример създадохме процедура, която връща общия брой автори. Ако сравните тази процедура с предишните, можете да видите, че стойността за печат се заменя с обратното.

Сега нека да видим как да изпълним процедурата и да изведем стойността, върната към нея. Изпълнението на процедурата изисква задаване на променлива и отпечатване, което се извършва след целия процес. Обърнете внимание, че вместо оператор за печат можете да използвате оператор Select, например, Изберете @RetValue, както и OutputValue.

4.2 Извеждане на параметъра на SQL хранилата процедура

Стойността на отговора може да се използва за връщане на една променлива, която видяхме в предишния пример. Използването на параметъра Output позволява на процедурата да изпрати една или повече стойности на променлива към повикващия. Изходният параметър се обозначава с тази ключова дума „Изход” при създаването на процедурата. Ако даден параметър е зададен като изходен параметър, тогава процедурният обект трябва да му присвои стойност. SQL съхранените процедури, примери за които могат да се видят по-долу, се връщат с обобщена информация.

В нашия пример ще има две имена на изхода: @TotalAuthors и @TotalNoContract. Те са изброени в списъка с параметри. Тези променливи присвояват стойности в тялото на процедурата. Когато използваме изходни параметри, повикващият може да види стойността, зададена в тялото на процедурата.

Освен това в предишния сценарий са обявени две променливи, за да се видят стойностите, които задават съхранените процедури на MS SQL Server в изходния параметър. След това процедурата се извършва чрез прилагане на нормалната стойност на параметъра "CA". Извеждат се следните параметри и следователно декларираните променливи се предават по предписания начин. Обърнете внимание, че при предаването на променливи, тук се задава и изходната ключова дума. След като процедурата е успешна, стойностите, върнати от изходните параметри, се показват в полето за съобщения.

4.3 Избор на една от SQL запомнените процедури

Тази техника се използва за връщане на набор от стойности под формата на таблица с данни (RecordSet) към извикваната съхранена процедура. В този пример, SQL запомнената процедура с @AuthID параметри запитва таблицата "Автори" чрез филтриране на върнатите записи, използвайки този параметър @AuthId. Изявлението Select решава какво да се върне на повикващия на съхранената процедура. Когато запомнената процедура се изпълни, AuthId се изпраща обратно. Такава процедура винаги връща само един запис или изобщо няма такъв. Но запаметената процедура няма ограничения за връщане на повече от един запис. Често можете да намерите примери, при които данните се връщат с помощта на избрани параметри с участието на изчислените променливи чрез предоставяне на няколко суми.

В заключение

Запаметената процедура е доста сериозен софтуерен модул, който връща или прехвърля, и също така задава необходимите променливи, дължащи се на клиентското приложение. Тъй като съхранената процедура се извършва на самия сървър, обменът на данни в огромни количества между сървъра и клиентското приложение (за някои изчисления) може да се избегне. Това ви позволява да намалите натоварването на SQL сървъра, което, разбира се, отива в ръцете на притежателите им. Един от подвидовете са T SQL SQL запомнени процедури, но те са необходими за онези, които участват в създаването на впечатляващи бази данни. Има и голям, дори огромен брой нюанси, които могат да бъдат полезни при изучаването на съхранените процедури, но това е необходимо повече за тези, които планират да се включат в програмирането, включително професионално.