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

Съхранените SQL процедури са изпълним програмен модул, който може да се съхранява в a база данни под формата на различни предмети. С други думи, това е обект, който съдържа 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" Transact-SQL. Всички те са предназначени да създават и изпълняват съхранени процедури, което ни позволява да използваме собствените си алгоритми в големи манипулатори на бази данни. Необходимо е също така тези, които управляват такава информация, да защитават всички обекти от неразрешен достъп от трети страни и съответно да създават, променят или изтриват определени данни.

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

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

безопасност

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

Трансфер на данни

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

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

- прехвърляне на данни чрез извлечение за връщане;

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

Сега нека видим как този процес изглежда отвътре.

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

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

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

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

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

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

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

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

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

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

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

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

Често потребителите задават въпроса: "Как да зададете няколко стойности в един оператор в тялото на процедурата?". Въпросът е интересен, но е много по-лесно да го направите, отколкото си мислите. Отговор: използвайки двойки като "Select 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, например Select @RetValue и OutputValue.

4.2 Излезте от параметъра SQL stored procedure

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

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

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

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

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

В заключение

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