T-SQL-де сақталатын процедуралар – құру, өзгерту, жою. Сақталған процедура Сақталған процедуралар ms sql
Бұл оқулықта сіз қалай істеу керектігін білесіз SQL серверінде процедураларды жасау және жою(Transact-SQL) синтаксисі және мысалдары бар.
Сипаттама
SQL серверінде процедура - бұл параметрлерді жіберуге болатын сақталған бағдарлама. Ол функция сияқты мәнді қайтармайды. Дегенмен, ол сәтті/сәтсіздік күйін шақырған процедураға қайтара алады.
Процедура құру
SQL Server (Transact-SQL) ішінде өзіңіздің сақталатын процедураларыңызды жасай аласыз. Толығырақ қарастырайық.
Синтаксис
SQL серверіндегі процедуралар синтаксисі (Transact-SQL):
CREATE ( PROCEDURE | PROC ) процедура_атауы
[ @параметр деректер түрі
[ VARYING ] [ = әдепкі ] [ OUT | OUTPUT | ТЕК ОҚУ]
, @параметр деректер түрі
[ VARYING ] [ = әдепкі ] [ OUT | OUTPUT | ТЕК ОҚУ ]]
[ МЕН ( ШИФРЛЕУ | ҚАЙТА ҚҰРАСТЫРУ | АБАЙДА ОРЫНДАУ ) ]
[РЕПЛИКАЦИЯ ҮШІН]
AS
БАСТА
орындалатын_бөлім
СОҢЫ;
Параметрлер немесе аргументтер
schema_name — сақталатын процедура жататын схеманың атауы.
procedure_name - бұл процедураны SQL серверінде тағайындайтын атау.
@parameter - процедураға бір немесе бірнеше параметрлер беріледі.
type_schema_name — егер бар болса, деректер түріне иелік ететін схема.
Деректер түрі — @parameter үшін деректер түрі.
VARYING - нәтиже жинағы шығыс параметрі болған кезде курсор параметрлері үшін орнату.
әдепкі - @parameter параметріне тағайындалатын әдепкі мән.
OUT - бұл @parameter шығыс параметрі екенін білдіреді.
OUTPUT - Бұл @parameter шығыс параметрі екенін білдіреді.
READONLY - бұл @parameter сақталған процедура арқылы қайта жазылмайтынын білдіреді.
ШІфрлау - бұл сақталған процедураның көзі SQL Server жүйесінің көріністерінде кәдімгі мәтін ретінде сақталмайтынын білдіреді.
ҚАЙТА КОМПИЛЬЯДАУ - бұл осы сақталған процедура үшін сұрау жоспары кэштелмейтінін білдіреді.
EXECUTE AS - сақталатын процедураны орындау үшін қауіпсіздік контекстін орнатады.
РЕПЛИКАЦИЯЛАУ ҮШІН - Бұл сақталған процедура тек репликация кезінде орындалатынын білдіреді.
Мысал
SQL Server (Transact-SQL) жүйесінде сақталатын процедураны құру мысалын қарастырайық.
Төменде процедураның қарапайым мысалы келтірілген:
Transact-SQL
ПРОЦЕДУРАНЫ ЖАСАУ FindSite @site_name VARCHAR(50) БАСТАУ САЙЫН ШЫҒУ @site_id INT; SET @site_id = 8; ЕГЕР @site_id< 10 SET @site_name = "yandex.com"; ELSE SET @site_name = "google.com"; END;
ПРОЦЕДУРАНЫ ЖАСАУ FindSite @site_name VARCHAR(50) OUT БАСТА DECLARE @site_id INT ; SET @site_id = 8 ; IF@site_id< 10 SET @ site_name = "yandex.com" ; БАСҚА SET @site_name = "google.com" ; СОҢЫ ; |
Бұл процедура FindSite деп аталады. Оның @site_id айнымалысы негізінде жаңартылатын шығыс параметрі болып табылатын @site_name деп аталатын бір параметрі бар.
Содан кейін FindSite деп аталатын жаңа сақталған процедураға келесідей сілтеме жасай аласыз.
Соңғы жаңарту: 14.08.2017
Көбінесе деректер операциясы белгілі бір реттілікпен орындалуы керек нұсқаулар жиынтығын білдіреді. Мысалы, өнімді сатып алуды қосқанда тапсырыстар кестесіне деректерді енгізу қажет. Дегенмен, мұны жасамас бұрын, сіз сатып алатын өнімнің қоймада бар-жоғын тексеруіңіз керек. Сізге бірқатар қосымша шарттарды тексеру қажет болуы мүмкін. Яғни, шын мәнінде, өнімді сатып алу процесі белгілі бір реттілікпен орындалуы керек бірнеше әрекеттерді қамтиды. Бұл жағдайда барлық осы әрекеттерді бір нысанға инкапсуляциялау оңтайлы болар еді - сақталатын процедура(сақталған процедура).
Яғни, мәні бойынша сақталатын процедуралар бір бірлік ретінде орындалатын нұсқаулар жиынтығы болып табылады. Осылайша, сақталған процедуралар күрделі операцияларды жеңілдетуге және оларды бір объектіге орналастыруға мүмкіндік береді. Өнімді сатып алу процесі өзгереді, сәйкесінше процедура кодын өзгерту жеткілікті болады. Яғни, процедура кодты басқаруды да жеңілдетеді.
Сақталған процедуралар сонымен қатар кестелердегі деректерге қол жеткізуді шектеуге мүмкіндік береді және осылайша осы деректерге қатысты әдейі немесе әдейі емес қалаусыз әрекеттердің ықтималдығын азайтады.
Және тағы бір маңызды аспект - өнімділік. Сақталған процедуралар әдеттегі SQL мәлімдемелеріне қарағанда жылдамырақ орындалады. Себебі процедура коды алғаш рет іске қосылғанда бір рет құрастырылады, содан кейін компиляцияланған түрде сақталады.
Сақталған процедураны жасау үшін CREATE PROCEDURE немесе CREATE PROC командасын пайдаланыңыз.
Осылайша, сақталатын процедураның үш негізгі мүмкіндігі бар: кодты жеңілдету, қауіпсіздік және өнімділік.
Мысалы, деректер базасында өнімдер туралы деректерді сақтайтын кесте бар делік:
CREATE TABLE өнімдері (Id INT IDENTITY PRIMARY KEY, Өнім атауы NVARCHAR(30) NO NULL, Өндіруші NVARCHAR(20) NO NULL, ProductCount INT DEFAULT 0, Price MONEY NULL ЕМЕС);
Осы кестеден деректерді алу үшін сақталатын процедураны жасайық:
USE productsdb; ӨТУ ПРОЦЕДУРАСЫ ЖАСАУ Өнімнің жиынтық сипаттамасы Өнім, өндіруші, өнімдерден алынған баға
CREATE PROCEDURE пәрмені бөлек бумада шақырылуы керек болғандықтан, ағымдағы дерекқорды орнататын USE пәрменінен кейін жаңа буманы анықтау үшін GO пәрмені орындалады.
Процедураның атауынан кейін AS түйінді сөзі болуы керек.
Процедураның денесін сценарийдің қалған бөлігінен бөлу үшін процедура коды көбінесе BEGIN...END блогында орналасады:
USE productsdb; ӨТІҢІЗ ПРОЦЕДУРАНЫ ЖАСАУ Өнімнің жиынтық мазмұнын БАСТАУ САЙЫНДА Өнім атауын өнім, өндіруші, өнімдерден бастап бағасы АЯҚТА ТАҢДАҢЫЗ;
Процедураны қосқаннан кейін оны ішкі түйіндегі SQL Server Management Studio бағдарламасындағы дерекқор түйінінде көре аламыз Бағдарламалау мүмкіндігі -> Сақталған процедуралар:
Біз процедураны визуалды интерфейс арқылы да басқара аламыз.
Процедураны орындау
Сақталған процедураны орындау үшін EXEC немесе EXECUTE командасын шақырыңыз:
EXEC өнімінің қорытындысы
Процедураны жою
Процедураны жою үшін DROP PROCEDURE пәрменін пайдаланыңыз:
ТӨШІРУ ПРОЦЕДУРАСЫ Өнімнің қорытындысы
Бұл бөлім Transact-SQL CREATE PROCEDURE мәлімдемесін пайдалану арқылы SQL Server Management Studio бағдарламасында Transact-SQL сақталған процедурасын қалай жасауға болатынын сипаттайды.
Бастамас бұрын, келесі қадамдарды орындаңыз.Рұқсаттар
Қолдану арқылы процедураны жасаңыз: SQL Server Management Studio, Transact-SQL
Бұл мәлімдемені іске қосу дерекқорда CREATE PROCEDURE рұқсатын және процедура жасалып жатқан схемада ALTER рұқсатын талап етеді.
Сіз келесі әдістердің бірін пайдалана аласыз:
SQL Server Management Studio
SQL Server Management Studio пайдалану
Object Explorer бағдарламасында процедураны жасаңыз
түймешігін басыңыз ЖАРАЙДЫ МА.
IN сұрау редакторы SELECT операторын келесі мәлімдемемен ауыстырыңыз:
Синтаксисті тексеру үшін таңдаңыз Талдаумәзірде Сұраныс. Қате туралы хабар қайтарылса, нұсқауларды жоғарыдағылармен салыстырыңыз және қажет болса түзетулер енгізіңіз.
Процедураны жасау үшін мәзірде Сұранысэлементті таңдаңыз Орындау. Процедура дерекқорда объект ретінде жасалады.
Object Explorer бағдарламасында процедураны көру үшін элементті тінтуірдің оң жақ түймешігімен басыңыз Сақталған процедураларжәне таңдаңыз Жаңарту.
Процедураны орындау үшін Object Explorer бағдарламасында сақталған процедура атауын тінтуірдің оң жақ түймешігімен басыңыз HumanResources.uspGetEmployeesTestжәне таңдаңыз Сақталған процедураны орындау.
Терезеде Процедураны орындау@LastName параметрінің мәні ретінде Margheim және @FirstName параметрінің мәні ретінде Дайанды енгізіңіз.
IN Object ExplorerДерекқор механизмінің данасына қосылыңыз және оны орналастырыңыз.
Түйінді ретімен ашыңыз Дерекқор, дерекқор AdventureWorks2012және түйін Бағдарламалау.
Элементті тінтуірдің оң жақ түймешігімен басыңыз Сақталған процедураларжәне таңдаңыз Сақталған процедураны жасаңыз.
Мәзірде Сұранысэлементті таңдаңыз Үлгі параметрлері үшін мәндерді көрсетіңіз.
Диалогтық терезеде Үлгі параметрлері үшін мәндерді орнатуКөрсетілген параметрлер үшін келесі мәндерді енгізіңіз.
| Параметр | Мағынасы |
|---|---|
| Автор | Сіздің атыңыз |
| құрылған күні | Бүгінгі күн |
| Сипаттама | Қызметкерлер туралы деректерді қайтарады. |
| Процедура_атауы | |
| @Param1 | @Тек |
| @Datatype_For_Param1 | nvarchar(50) |
| Параметр1 үшін әдепкі_мән | NULL |
| @Param2 | @Аты |
| @Datatype_For_Param2 | nvarchar(50) |
| Парам2 үшін әдепкі_мән | NULL |
Transact-SQL пайдалану
Сұраныс өңдегішінде процедураны жасау
IN Object ExplorerДерекқор механизмінің данасына қосылыңыз.
Мәзірде Файлэлементті таңдаңыз Сұраныс жасаңыз.
Келесі мысалды сұрау терезесіне көшіріп, түймешігін басыңыз Орындау. Бұл мысал бұрынғыдай сақталатын процедураны жасайды, бірақ басқа процедура атымен.
AdventureWorks2012 пайдалану; ЖАСАУ ПРОЦЕДУРА HumanResources .uspGetEmployeesTest2 @LastName nvarchar (50) , @FirstName nvarchar (50) NO COUNT ҚОСУ АСЫ ; HumanResources.vEmployeeDepartmentHistory FROM Атын, Фамилияны, Бөлімді ТАҢДАҢЫЗ ҚАЙДА FirstName = @FirstName ЖӘНЕ Фамилия = @LastName ЖӘНЕ Аяқталу күні NULL БОЛСА; ӨТ
Процедураны аяқтау үшін келесі мысалды жасап жатқан сұрау терезесіне көшіріп, түймешігін басыңыз Орындау. Параметр мәндерін орнатудың әртүрлі әдістері көрсетілген.
Сақталған процедура - деректер қорының объектісі, ол бір рет құрастырылатын және серверде сақталатын SQL нұсқауларының жиынтығы. Сақталған процедуралар қарапайым жоғары деңгейлі тілдік процедураларға өте ұқсас, олардың енгізу-шығару параметрлері және жергілікті айнымалылар болуы мүмкін, олар сандық есептеулер мен символдар деректерімен операцияларды орындай алады, олардың нәтижелері айнымалылар мен параметрлерге тағайындалуы мүмкін. Сақталған процедуралар стандартты дерекқор әрекеттерін орындай алады (DDL де, DML де). Сонымен қатар, сақталған процедуралар циклдар мен тармақтарға мүмкіндік береді, яғни орындау процесін басқару үшін нұсқауларды пайдалана алады.
Сақталған процедуралар пайдаланушы анықтайтын функцияларға (UDF) ұқсас. Негізгі айырмашылығы пайдаланушы анықтайтын функцияларды SQL операторындағы кез келген басқа өрнек сияқты пайдалануға болады, ал сақталған процедураларды CALL функциясы арқылы шақыру керек:
ҚОҢЫРАУ процедурасы(…)
EXECUTE процедурасы(…)
Сақталған процедуралар бірнеше нәтижелерді, яғни ТАҢДАУ сұрауының нәтижелерін қайтара алады. Мұндай нәтиже жиындарын курсорлар, нәтижелер жиынының көрсеткішін қайтаратын басқа сақталатын процедуралар немесе қолданбалар арқылы өңдеуге болады. Сақталған процедуралар сонымен қатар кестедегі бірнеше жолдарды айналдыруға мүмкіндік беретін деректерді өңдеуге арналған жарияланған айнымалыларды және курсорларды қамтуы мүмкін. SQL стандарты IF, LOOP, REPEAT, CASE және көптеген басқалармен жұмыс істеуге мүмкіндік береді. Сақталған процедуралар айнымалы мәндерді қабылдай алады, нәтижелерді қайтара алады немесе айнымалы мәндерді өзгертіп, айнымалының қай жерде жарияланғанына байланысты қайтара алады.
Сақталған процедуралардың орындалуы бір ДҚБЖ-дан екіншісіне өзгереді. Көптеген ірі дерекқор жеткізушілері оларды сол немесе басқа пішінде қолдайды. ДҚБЖ-ға байланысты сақталатын процедуралар SQL, Java, C немесе C++ сияқты әртүрлі бағдарламалау тілдерінде жүзеге асырылуы мүмкін. SQL тілінде жазылмаған сақталатын процедуралар SQL сұрауларын өздігінен орындауы немесе орындамауы мүмкін.
Артында
Логиканы басқа қолданбалармен бөлісу. Сақталған процедуралар функционалдылықты инкапсуляциялайды; бұл әртүрлі қолданбалар арқылы деректерге қол жеткізу және басқару үшін қосылымды қамтамасыз етеді.
Пайдаланушыларды дерекқор кестелерінен оқшаулау. Бұл кесте деректеріне емес, сақталған процедураларға рұқсат беруге мүмкіндік береді.
Қорғаныс механизмін қамтамасыз етеді. Алдыңғы тармаққа сәйкес, деректерге тек сақталған процедуралар арқылы қол жеткізе алсаңыз, SQL DELETE пәрмені арқылы деректеріңізді басқа ешкім өшіре алмайды.
Желілік трафикті азайту нәтижесінде орындалу жақсарды. Сақталған процедураларды пайдалана отырып, бірнеше сұрауларды біріктіруге болады.
Қарсы
Жұмыстың көп бөлігі сервер жағында, ал клиенттік жағында аз орындалатындығына байланысты деректер қоры серверіне жүктеменің артуы.
Сізге көп нәрсені үйрену керек болады. Сақталған процедураларды жазу үшін MySQL өрнек синтаксисін үйрену керек.
Сіз қолданба логикасын екі жерде қайталайсыз: сервер коды және сақталған процедуралар коды, осылайша деректерді өңдеу процесін қиындатасыз.
Бір ДҚБЖ-дан екіншісіне көшу (DB2, SQL Server, т.б.) ақауларға әкелуі мүмкін.
Сақталған процедуралардың мақсаты мен артықшылықтары
Сақталған процедуралар өнімділікті жақсартады, бағдарламалау мүмкіндіктерін жақсартады және деректер қауіпсіздігі мүмкіндіктерін қолдайды.
Жиі пайдаланылатын сұрауды сақтаудың орнына, тұтынушылар сәйкес сақталатын процедураға сілтеме жасай алады. Сақталған процедура шақырылған кезде оның мазмұны сервермен дереу өңделеді.
Сұрауды нақты орындаудан басқа, сақталған процедуралар есептеулерді орындауға және деректерді өңдеуге – өзгертуге, жоюға, DDL мәлімдемелерін орындауға (барлық ДҚБЖ-да емес!) және басқа сақталатын процедураларды шақыруға және күрделі транзакциялық логиканы орындауға мүмкіндік береді. Жалғыз мәлімдеме желі бойынша жүздеген командаларды жіберуден және, атап айтқанда, клиенттен серверге деректердің үлкен көлемін тасымалдау қажеттілігінен аулақ бола отырып, сақталған процедурада қамтылған күрделі сценарийді шақыруға мүмкіндік береді.
Көптеген ДҚБЖ-да сақталған процедура бірінші рет іске қосылғанда, ол компиляцияланады (талданады және деректерге қол жеткізу жоспары жасалады). Болашақта оны өңдеу жылдамырақ болады. Oracle ДҚБЖ деректер сөздігінде сақталған сақталған процедуралық кодты түсіндіреді. Oracle 10g жүйесінен бастап С тілінде сақталған процедуралық кодтың жергілікті компиляциясы деп аталатын, содан кейін мақсатты машинаның машиналық кодына қолдау көрсетіледі, содан кейін сақталған процедура шақырылған кезде оның құрастырылған нысан коды тікелей орындалады.
Бағдарламалау мүмкіндіктері
Құрылған сақталатын процедураны кез келген уақытта шақыруға болады, ол модульдік қамтамасыз етеді және кодты қайта пайдалануды ынталандырады. Соңғысы дерекқорды қолдауды жеңілдетеді, өйткені ол бизнес ережелерін өзгертуден оқшауланады. Сақталған процедураны жаңа ережелерге сәйкес кез келген уақытта өзгертуге болады. Осыдан кейін оны пайдаланатын барлық қолданбалар тікелей өзгертусіз автоматты түрде жаңа бизнес ережелеріне сәйкес келеді.
Қауіпсіздік
Сақталған процедураларды пайдалану пайдаланушыларға деректерге жанама және қатаң реттелетін қатынасты қамтамасыз ететін сақталатын процедураларды орындауға ғана рұқсаттарды қалдырып, дерекқор кестелеріне пайдаланушының тікелей қатынасын шектеуге немесе толығымен жоюға мүмкіндік береді. Сонымен қатар, кейбір ДҚБЖ сақталған процедураның мәтіндік шифрлауын (орауды) қолдайды.
Бұл қауіпсіздік мүмкіндіктері дерекқор құрылымын пайдаланушыдан оқшаулауға мүмкіндік береді, дерекқордың тұтастығы мен сенімділігін қамтамасыз етеді.
SQL инъекциясы сияқты әрекеттердің ықтималдығы азаяды, себебі жақсы жазылған сақталған процедуралар сұрауды ДҚБЖ-ға бермес бұрын енгізу параметрлерін қосымша тексереді.
Сақталған процедураларды іске асыру
Сақталған процедуралар әдетте SQL тілін немесе таңдалған ДҚБЖ-да оның нақты орындалуын пайдаланып жасалады. Мысалы, осы мақсаттар үшін Microsoft SQL Server ДҚБЖ-да Transact-SQL тілі, Oracle-де - PL/SQL, InterBase және Firebird-де - PSQL, PostgreSQL-де - PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, IBM DB2 - SQL/PL (ағылшын тілі), Informix - SPL. MySQL SQL:2003 стандартына өте жақын, оның тілі SQL/PL-ге ұқсас.
Кейбір ДҚБЖ тәуелсіз орындалатын файлдарды жасай алатын кез келген бағдарламалау тілінде жазылған сақталатын процедураларды пайдалануға мүмкіндік береді, мысалы, C++ немесе Delphi. Microsoft SQL Server терминологиясында мұндай процедуралар кеңейтілген сақталатын процедуралар деп аталады және жай ғана Win32 DLL файлында қамтылған функциялар болып табылады. Және, мысалы, Interbase және Firebird бағдарламаларында DLL/SO-дан шақырылатын функциялардың басқа атауы бар - UDF (User Defined Function). MS SQL 2005 кез келген .NET тілінде сақталатын процедураларды жазу мүмкіндігін енгізді, ал кеңейтілген сақталатын процедуралардан болашақта бас тарту жоспарлануда. Oracle ДҚБЖ өз кезегінде Java тілінде сақталған процедураларды жазуға мүмкіндік береді. IBM DB2-де сақталған процедуралар мен функцияларды кәдімгі бағдарламалау тілдерінде жазу дәстүрлі әдіс болып табылады, ол ең басынан қолдауға ие және SQL процедуралық кеңейтімі бұл ДҚБЖ-ға ANSI стандартына енгізілгеннен кейін өте кеш нұсқаларында ғана қосылды. Informix сонымен қатар Java және C тілдеріндегі процедураларды қолдайды.
Oracle ДҚБЖ-да сақталған процедураларды пакеттер деп аталатын біріктіруге болады. Бума екі бөліктен тұрады – сақталатын процедураның анықтамасын көрсететін бума спецификациясы және оның орындалуын қамтитын буманың денесі. Осылайша, Oracle бағдарлама кодының интерфейсін оны жүзеге асырудан бөлуге мүмкіндік береді.
IBM DB2 ДҚБЖ-да сақталған процедураларды модульдерге біріктіруге болады.
Синтаксис
`p2`() ПРОЦЕДУРАСЫ ЖАСАУ
SQL ҚАУІПСІЗДІК АНЫҚТАУШЫ
Пікір «Рәсім»
«Сәлем әлем!» ТАҢДАУ;
Кодтың бірінші бөлігі сақталатын процедураны жасайды. Келесі параметр қосымша параметрлерді қамтиды. Содан кейін аты және, ең соңында, рәсімнің өзі келеді.
Сақталған процедураның 4 сипаттамасы:
Тіл: Тасымалдау мақсатында әдепкі мән SQL болып табылады.
Детерминистикалық: егер процедура әрқашан бірдей нәтижені қайтарса және бірдей енгізу параметрлерін қабылдаса. Бұл көшіру және тіркеу процесіне арналған. Әдепкі мән АНЫҚТАЙДЫ ЕМЕС.
SQL қауіпсіздігі: пайдаланушы құқықтары қоңырау кезінде тексеріледі. INVOKER — сақталған процедураны шақыратын пайдаланушы. DEFINER – процедураның «жасаушысы». Әдепкі мән – DEFINER.
Түсініктеме: құжаттама мақсатында әдепкі мән "" болып табылады.
Сақталған процедураны шақыру
Сақталған_процедураның_атын ҚОҢЫРАУ (парам1, парам2, ....)
CALL процедурасы1(10 , "жол параметрі" , @parameter_var);
Сақталған процедураны өзгерту
MySQL-де процедураларды өзгертуге арналған ALTER PROCEDURE мәлімдемесі бар, бірақ ол белгілі бір сипаттарды өзгерту үшін ғана жарамды. Егер процедураның параметрлерін немесе денесін өзгерту қажет болса, оны жойып, қайта жасау керек.
Жоюсақталадыпроцедуралар
БАР БОЛСА, ТҮСІРУ ПРОЦЕДУРАСЫ p2;
Бұл қарапайым пәрмен. IF EXISTS операторы мұндай процедура жоқ болса, қатені ұстайды.
Опциялар
CREATE PROCEDURE proc1(): бос параметрлер тізімі
CREATE PROCEDURE proc1 (IN varname DATA-TYPE): бір енгізу параметрі. IN сөзі міндетті емес, себебі әдепкі параметрлер IN (in) болып табылады.
CREATE PROCEDURE proc1 (OUT varname DATA-TYPE): бір параметр қайтарылды.
CREATE PROCEDURE proc1 (INOUT varname DATA-TYPE): бір параметр, енгізу де, қайтару да.
Айнымалы декларация синтаксисі келесідей көрінеді:
Varname DATA-TYPE DEFAULT әдепкі мәнді ЖАРИЯЛАУ;
Microsoft SQL серверінде өзіңіздің алгоритмдеріңізді енгізу және автоматтандыру үшін ( есептеулер) сақталатын процедураларды пайдалана аласыз, сондықтан бүгін біз олардың қалай құрылатыны, өзгертілгені және жойылатыны туралы айтатын боламыз.
Бірақ алдымен, сақталатын процедуралардың не екенін және олардың T-SQL-де не үшін қажет екенін түсіну үшін кішкене теория.
Ескерту! Жаңадан бастаған бағдарламашылар үшін T-SQL бойынша келесі пайдалы материалдарды ұсынамын:
- T-SQL тілін неғұрлым егжей-тегжейлі зерттеу үшін мен кітапты оқуды ұсынамын - T-SQL бағдарламашысының жолы. Transact-SQL тілінің оқулығы.
T-SQL тілінде қандай сақталатын процедуралар бар?
Сақталған процедуралар– бұл SQL нұсқауларының жиынтығы түріндегі алгоритмді қамтитын мәліметтер қорының объектілері. Басқаша айтқанда, сақталатын процедуралар мәліметтер қорының ішіндегі бағдарламалар деп айта аламыз. Сақталған процедуралар серверде қайта пайдалануға болатын кодты сақтау үшін пайдаланылады, мысалы, сіз белгілі бір алгоритмді, дәйекті есептеуді немесе көп сатылы SQL операторын жаздыңыз және осы алгоритмге енгізілген барлық нұсқауларды әр уақытта орындамау үшін оны пішімдеуге болады. сақталатын процедура ретінде. Сонымен қатар, SQL процедурасын жасаған кезде сервер кодты құрастырады, содан кейін осы SQL процедурасын іске қосқан сайын сервер оны қайта құрастырмайды.
SQL серверінде сақталатын процедураны орындау үшін оның атының алдында EXECUTE командасын жазу керек, бұл пәрменді EXEC деп қысқартуға да болады. Сақталған процедураны SELECT операторында шақыру, мысалы, функция ретінде бұдан былай жұмыс істемейді, яғни. процедуралар бөлек іске қосылады.
Сақталған процедураларда функциялардан айырмашылығы, деректерді өзгерту операцияларын орындауға болады, мысалы: UNSERT, UPDATE, DELETE. Сондай-ақ процедураларда кез келген дерлік түрдегі SQL операторларын қолдануға болады, мысалы, кестелерді жасау үшін CREATE TABLE немесе EXECUTE, яғни. басқа процедураларды шақыру. Ерекшелік нұсқаулардың бірнеше түрі болып табылады, мысалы: функцияларды, көріністерді, триггерлерді жасау немесе өзгерту, схемаларды жасау және бірнеше басқа ұқсас нұсқаулар, мысалы, сақталған процедурада дерекқор қосылымының контекстін (USE) ауыстыра алмайсыз.
Сақталған процедураның кіріс және шығыс параметрлері болуы мүмкін, ол кестелік деректерді қайтара алады немесе ештеңені қайтара алмайды, тек ондағы нұсқауларды орындайды.
Сақталған процедуралар өте пайдалы, олар бізге көптеген операцияларды автоматтандыруға немесе жеңілдетуге көмектеседі, мысалы, сіз үнемі жиынтық кестелерді пайдаланып әртүрлі күрделі аналитикалық есептерді жасауыңыз керек, яғни. PIVOT операторы. Осы оператормен сұрауларды құрастыруды жеңілдету үшін ( Өздеріңіз білетіндей, PIVOT синтаксисі өте күрделі), Сіз үшін жиынтық есептерді динамикалық түрде генерациялайтын процедураны жаза аласыз, мысалы, «T-SQL ішіндегі динамикалық PIVOT» материалы сақталатын процедура түрінде осы мүмкіндікті іске асырудың мысалын береді.
Microsoft SQL Server серверінде сақталатын процедуралармен жұмыс істеу мысалдары
Мысалдар үшін бастапқы деректер
Төмендегі барлық мысалдар Microsoft SQL Server 2016 Express бағдарламасында іске қосылады. Сақталған процедуралардың нақты деректермен қалай жұмыс істейтінін көрсету үшін бізге бұл деректер қажет, оны жасайық. Мысалы, сынақ кестесін құрайық және оған кейбір жазбаларды қосамыз, оның бағасы көрсетілген өнімдердің тізімі бар кесте болады делік.
Кестені құру нұсқаулығы CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- Деректерді қосу нұсқаулығы INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (1 , «Тінтуір», 100), (1, «Пернетақта», 200), (2, «Телефон», 400) GO --Сұрауды таңдау SELECT * TestTable FROM
Бізде деректер бар, енді сақталатын процедураларды құруға көшейік.
T-SQL-де сақталатын процедураны құру – CREATE PROCEDURE операторы
Сақталған процедуралар оператордың көмегімен жасалады ПРОЦЕДУРАНЫ ЖАСАУ, осы нұсқаудан кейін процедураңыздың атын жазуыңыз керек, содан кейін қажет болса, жақшаның ішінде енгізу және шығару параметрлерін анықтаңыз. Осыдан кейін сіз AS түйінді сөзін жазасыз және BEGIN түйінді сөзі бар нұсқаулар блогын ашасыз, бұл блокты END сөзімен жабыңыз. Бұл блоктың ішінде сіз өзіңіздің алгоритміңізді немесе қандай да бір ретті есептеуді жүзеге асыратын барлық нұсқауларды жазасыз, басқаша айтқанда, T-SQL тілінде бағдарламалайсыз.
Мысалы, жаңа жазбаны қосатын сақталған процедураны жазайық, яғни. сынақ үстелімізге жаңа өнім. Ол үшін біз үш енгізу параметрін анықтаймыз: @CategoryId – өнім санатының идентификаторы, @ProductName – өнім атауы және @Price – өнім бағасы; бұл параметр міндетті емес болады, яғни. оны процедураға берудің қажеті жоқ ( мысалы, біз бағасын әлі білмейміз), осы мақсат үшін оның анықтамасында әдепкі мәнді орнатамыз. Бұл параметрлер процедураның денесінде, яғни. BEGIN...END блогында әдеттегі айнымалылар сияқты пайдалануға болады ( Өздеріңіз білетіндей, айнымалылар @ белгісімен белгіленеді). Егер шығыс параметрлерін көрсету қажет болса, онда параметр атынан кейін OUTPUT кілт сөзін көрсетіңіз ( немесе қысқаша OUT).
BEGIN...END блогында біз деректерді қосу нұсқаулығын, сондай-ақ процедураның соңында ТАҢДАУ нұсқаулығын жазамыз, осылайша сақталған процедура бізге көрсетілген санаттағы өнімдер туралы кестелік деректерді қайтарады. жаңа, жаңа ғана қосылған өнімді есепке алыңыз. Сондай-ақ, осы сақталған процедурада мен кіріс параметрін өңдеуді қостым, атап айтқанда, бірнеше бос орындар кездейсоқ енгізілген жағдайларды жою үшін мәтін жолының басы мен соңындағы қосымша бос орындарды алып тастау.
Міне, осы процедураның коды ( Мен де оған түсініктеме бердім).
Процедураны жасау CREATE PROCEDURE TestProcedure (--Енгізу параметрлері @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) БАСТАЛУ АСЫНДА --Алгоритміңізді жүзеге асыратын нұсқаулар --Кіріс параметрлерді өңдеу --Басында қосымша бос орындарды жою және мәтін жолының соңында SET @ProductName = LTRIM(RTRIM(@ProductName)); --Жаңа жазба қосу INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Деректерді қайтару SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
T-SQL - EXECUTE пәрменінде сақталатын процедураны орындау
EXECUTE немесе EXEC пәрменін пайдаланып, мен бұрын атап өткендей, сақталған процедураны орындауға болады. Кіріс параметрлер процедураларға жай тізімдеу және процедура атынан кейін сәйкес мәндерді көрсету арқылы беріледі ( шығыс параметрлері үшін OUTPUT пәрменін де көрсету керек). Дегенмен, параметрлердің атаулары көрсетілмеуі мүмкін, бірақ бұл жағдайда мәндерді көрсету дәйектілігін сақтау қажет, яғни. кіріс параметрлері анықталған ретпен мәндерді көрсетіңіз ( бұл шығыс параметрлеріне де қатысты).
Әдепкі мәндері бар параметрлерді көрсетудің қажеті жоқ; бұл қосымша параметрлер деп аталатындар.
Мұнда сақталатын процедураларды іске қосудың бірнеше түрлі, бірақ баламалы жолдары, әсіресе біздің сынақ процедурасы берілген.
1. EXECUTE TestProcedure @CategoryId = 1, @ProductName = "1-сынақ өнімі" --2 бағасын көрсетпей процедураны шақырыңыз. EXEC TestProcedure @CategoryId = 1, @ProductName = "2-сынақ өнімі", @Price = 300 --3 бағасын көрсететін процедураны шақырыңыз. Параметрлердің атын көрсетпей процедураны шақырыңыз EXEC TestProcedure 1, «3-сынақ өнімі», 400
Сақталған процедураны T-SQL - ALTER PROCEDURE операторына өзгерту
Нұсқауларды пайдалана отырып, процедураның алгоритміне өзгертулер енгізуге болады ПРОЦЕДУРАНЫ ӨЗГЕРТУ. Басқаша айтқанда, бұрыннан бар процедураны өзгерту үшін CREATE PROCEDURE орнына ALTER PROCEDURE деп жазып, қажетінше қалғанының бәрін өзгерту керек.
Сынақ процедурасына өзгертулер енгізу керек делік, @Price параметрін айтайық, яғни. бағасы болса, біз оны міндетті етеміз, ол үшін әдепкі мәнді алып тастаймыз, сонымен қатар алынған деректер жинағын енді алудың қажеті жоқ деп елестетеміз, ол үшін сақталған процедурадан SELECT операторын алып тастаймыз.
Біз ALTER PROCEDURE TestProcedure (--Кіріс параметрлері @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) процедурасын өзгертеміз. мәтін жолдарының соңы SET @ProductName = LTRIM(RTRIM(@ProductName)); --Жаңа жазба қосу INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) АЯҚТАУ.
T-SQL - DROP PROCEDURE операторында сақталған процедураны жою
Қажет болса, сақталған процедураны жоюға болады, бұл нұсқауларды қолдану арқылы жасалады ТАСТАУ ПРОЦЕДУРАСЫ.
Мысалы, біз жасаған сынақ процедурасын жойайық.
TROP PROCEDURE TestProcedure
Сақталған процедураларды жою кезінде, егер процедураға басқа процедуралармен немесе SQL мәлімдемелерімен сілтеме жасалса, ол жойылғаннан кейін олар қатеге ұшырайтынын есте ұстаған жөн, өйткені олар сілтеме жасайтын процедура енді жоқ.
Менде бар нәрсе осы, материал сізге қызықты және пайдалы болды деп үміттенемін, сау болыңыз!
