Хадгалагдсан процедур гэж юу вэ? CREATE PROCEDURE заалт. Хялбар SQL дибаг хийх
Процедурууддаа мөр оруулах - SET NOCOUNT ON:
DML илэрхийлэл бүрээр SQL сервер нь боловсруулсан бичлэгийн тоог агуулсан мессежийг бидэнд буцааж өгдөг. Энэ мэдээлэлЭнэ нь кодыг дибаг хийх явцад бидэнд ашигтай байж болох ч дараа нь энэ нь огт хэрэггүй болно. SET NOCOUNT ON гэж бичснээр бид энэ функцийг идэвхгүй болгодог. Олон илэрхийлэл эсвэл/буюу гогцоо агуулсан хадгалагдсан процедурын хувьд энэ үйлдэл нь гүйцэтгэлийг мэдэгдэхүйц нэмэгдүүлэх боломжтой, учир нь замын хөдөлгөөний хэмжээ мэдэгдэхүйц буурах болно.
Transact-SQL
Схемийн нэрийг объектын нэрээр ашиглана уу:
За, энэ нь ойлгомжтой гэж бодож байна. Энэ үйлдэл нь серверт объектуудыг хаанаас хайхыг зааж өгдөг бөгөөд хогийн савнуудаа санамсаргүй гүйлгэхийн оронд хаашаа явах, юу авахаа шууд мэдэх болно. Олон тооны мэдээллийн сан, хүснэгт, хадгалагдсан процедурын ачаар энэ нь бидний цаг хугацаа, мэдрэлийг ихээхэн хэмнэх болно.
Transact-SQL
SELECT * FROM dbo.MyTable --Ингэж хийх нь сайн -- SELECT * FROM MyTable-ийн оронд --Ингэж хийх нь муу --EXEC dbo.MyProc процедурыг дуудах --Дахин сайн --EXEC MyProc-ийн оронд --Муу!
Хадгалсан процедурынхаа нэрэнд "sp_" угтварыг бүү ашигла:
Хэрэв бидний процедурын нэр "sp_" -ээр эхэлсэн бол. SQL серверэхлээд үндсэн мэдээллийн санг хайх болно. Баримт нь энэ угтварыг серверийн хувийн дотоод хадгалагдсан процедурт ашигладаг. Тиймээс мэдээллийн сангаас тантай ижил нэртэй процедур олдвол түүнийг ашиглах нь нэмэлт зардал, бүр буруу үр дүнд хүргэж болзошгүй юм.
IF EXISTS (SELECT *)-ийн оронд IF EXISTS (SELECT 1)-г ашиглана уу:
Өөр хүснэгтэд бичлэг байгаа эсэхийг шалгахын тулд бид IF EXISTS хэллэгийг ашигладаг. Энэ илэрхийлэлХэрэв дотоод илэрхийллээс дор хаяж нэг утгыг буцаавал үнэнийг буцаана, энэ нь "1", бүх багана эсвэл хүснэгтэд хамаагүй. Буцаагдсан өгөгдлийг үндсэндээ ямар ч байдлаар ашигладаггүй. Тиймээс өгөгдөл дамжуулах явцад урсгалыг шахахын тулд доор үзүүлсэн шиг "1"-ийг ашиглах нь илүү логик юм.
Хадгалагдсан журамхадгалагдсан процедур) нь нэртэй мэдээллийн сангийн програмын объект юм. SQL Server нь хэд хэдэн төрлийн хадгалагдсан процедуртай.
Системд хадгалагдсан процедуруудсистемийн хадгалагдсан процедур) нь DBMS хөгжүүлэгчид нийлүүлдэг бөгөөд системийн лавлахтай холбоотой үйлдэл хийх эсвэл системийн мэдээллийг авахад ашиглагддаг. Тэдний нэр ихэвчлэн "sp_" угтвараас эхэлдэг. Та EXECUTE командыг ашиглан бүх төрлийн хадгалагдсан процедурыг ажиллуулдаг бөгөөд үүнийг EXEC болгон богиносгож болно. Жишээлбэл, параметргүйгээр ажилладаг sp_helplogins хадгалагдсан процедур нь дансны нэрсийн талаар хоёр тайлан гаргадаг. (Англи)нэвтрэх) болон мэдээллийн сан бүрийн харгалзах хэрэглэгчид (Англи)хэрэглэгчид).
EXEC sp_helplogins;
Системд хадгалагдсан процедурыг ашиглан гүйцэтгэсэн үйлдлүүдийн талаар ойлголт өгөхийн тулд Хүснэгт 10.6-д зарим жишээг харуулав. SQL Server-д нийтдээ мянга гаруй системд хадгалагдсан процедурууд байдаг.
Хүснэгт 10.6
Хадгалагдсан системийн жишээ SQL процедуруудСервер
Хэрэглэгч хэрэглэгчийн мэдээллийн сан болон түр объектуудын мэдээллийн санд хадгалагдсан процедурыг үүсгэж болно. Сүүлчийн тохиолдолд хадгалсан процедур нь байх болно түр зуурын.Түр зуурын хүснэгтүүдийн нэгэн адил түр хадгалагдсан процедурын нэр нь хэрэв энэ нь локал түр хадгалагдсан процедур бол "#" угтвараар, эсвэл дэлхийнх бол "##" угтвараар эхлэх ёстой. Орон нутгийн түр зуурын процедурыг зөвхөн үүсгэсэн холболтын хүрээнд ашиглаж болно, глобалыг бусад холболтод ашиглаж болно.
SQL Server программчлагдах объектуудыг Transact-SQL хэрэгслүүд эсвэл угсралтуудыг ашиглан үүсгэж болно (Англи) assembly) Microsoft.Net Framework-ийн CRL (Common Language Runtime) орчинд. Энэ заавар нь зөвхөн эхний аргыг хамрах болно.
Хадгалсан процедурыг үүсгэхийн тулд CREATE PROCEDURE (PROC болгон богиносгож болно) хэллэгийг ашиглана уу.
ҮЗҮҮЛЭХ (PROC I PROCEDURE) proc_name [ ; тоо]
[(gparameter data_type)
[“өгөгдмөл] |
[ХАМТ
[ДАВЛУУЛАХ ЗОХИОН БАЙГУУЛЛАГА]
AS ([ BEGIN ] sql_statement [;] [ ...n ] [ END ] )
Хэрэв хадгалагдсан процедур (эсвэл триггер, функц, харагдац) нь ШИНЖЛҮҮЛЭХ сонголтоор үүсгэгдсэн бол түүний кодыг текстийг унших боломжгүй болгож хувиргадаг. Үүний зэрэгцээ, ашигласан алгоритмыг SQL Server-ийн өмнөх хувилбаруудаас шилжүүлсэн бөгөөд үүнийг найдвартай хамгаалалтын алгоритм гэж үзэх боломжгүй юм - урвуу хөрвүүлэлтийг хурдан хийх боломжийг олгодог хэрэгслүүд байдаг.
RECOMPILE сонголт нь процедурыг дуудах бүрт систем текстийг дахин эмхэтгэхийг зааж өгдөг. Ердийн тохиолдолд эхний ээлжинд эмхэтгэсэн процедур нь кэшэд хадгалагддаг бөгөөд энэ нь гүйцэтгэлийг нэмэгдүүлэх боломжийг олгодог.
EXECUTE AS нь процедурыг гүйцэтгэх аюулгүй байдлын контекстийг тодорхойлдог. Дараа нь утгуудын нэг нь CALLER | ӨӨРӨӨ | ЭЗЭН | "хэрэглэгчийн_нэр"). CALLER нь анхдагч бөгөөд энэ модулийг дуудаж буй хэрэглэгчийн аюулгүй байдлын хүрээнд кодыг гүйцэтгэнэ гэсэн үг юм. Үүний дагуу хэрэглэгч зөвхөн программчлагдах объектод төдийгүй түүнд өртсөн мэдээллийн сангийн бусад объектуудад зөвшөөрөлтэй байх ёстой. ӨӨРИЙНХӨӨ ГҮЙЦЭТГЭХ гэдэг нь программчлагдах объектыг үүсгэх эсвэл өөрчлөх хэрэглэгчийн контекстийг ашиглахыг хэлнэ. OWNER нь кодыг процедурын одоогийн эзэмшигчийн контекст дээр гүйцэтгэхийг зааж өгдөг. Хэрэв эзэмшигчийг заагаагүй бол түүний харьяалагдах схемийн эзэмшигч гэж үзнэ. "хэрэглэгчийн_нэр"-ээр ГҮЙЦЭТГЭХ нь хэрэглэгчийн нэрийг (дан хашилтанд) тодорхой зааж өгөх боломжийг олгоно.
Процедурын хувьд параметрүүдийг зааж өгч болно. Эдгээр нь утгыг процедурт дамжуулахад ашигладаг орон нутгийн хувьсагч юм. Хэрэв параметрийг OUTPUT (эсвэл товчоор OUT) гэсэн түлхүүр үгээр зарласан бол энэ нь гаралтын утга юм: дууссаны дараа процедурт өгөгдсөн утгыг процедурыг дуудсан програм ашиглаж болно. READONLY түлхүүр үг нь хадгалагдсан процедур дотор параметрийн утгыг өөрчлөх боломжгүй гэсэн үг юм.
Параметрүүдэд өгөгдмөл утгыг оноож болох бөгөөд процедурыг дуудах үед параметрийн утгыг тодорхой заагаагүй тохиолдолд хэрэглэнэ. Нэг жишээг харцгаая:
PROC surma (@a int, @b int=0,
©result int OUTPUT) AS
SET @result=0a+0b
Бид гурван параметр бүхий процедурыг үүсгэсэн бөгөөд @b параметр нь өгөгдмөл утгатай =0 бөгөөд @result параметр нь гаралтын параметр юм: энэ нь утгыг дуудаж буй програм руу буцаана. Гүйцэтгэсэн үйлдлүүд нь маш энгийн байдаг - гаралтын параметр нь хоёр оролтын нийлбэрийн утгыг хүлээн авдаг.
SQL Server Management Studio дээр ажиллахдаа үүсгэсэн хадгалагдсан процедурыг програмчлагдах өгөгдлийн сангийн объектууд хэсгээс олж болно (Англи)Програмчлах чадвар) хадгалагдсан процедурын дэд хэсэгт (Зураг 10.2).
Процедурыг дуудахдаа та хувьсагч болон тогтмолыг хоёуланг нь оролтын параметр болгон ашиглаж болно. Хоёр жишээг харцгаая. Эхний хэсэгт процедурын оролтын параметрүүдийг тогтмол гэж тодорхой зааж өгсөн бөгөөд дуудлагад гаралтын параметрт OUTPUT түлхүүр үгийг зааж өгсөн болно. Хоёрдахь хувилбарт хувьсагчийн утгыг эхний оролтын параметр болгон, хоёр дахь параметрийг ашиглана. түлхүүр үг DEFAULT нь үндсэн утгыг ашиглах ёстойг харуулж байна:
Цагаан будаа. 10.2.
@с int;
EXEC нийлбэр 10.5,@c OUTPUT;
PRINT 0c; – 15 гарч ирнэ
ЗАРЛАХ Gi int = 5;
– залгахдаа үндсэн утгыг ашиглана уу
EXEC нийлбэр Gi,DEFAULT , 0c OUTPUT;
PRINT 0c; – 5 гарч ирнэ
Одоо процедур дууссан буцах кодын дүн шинжилгээ хийх жишээг авч үзье. Бид тухайн жилийн хугацаанд Bookl хүснэгтэд хэдэн ном хэвлэгдсэнийг тооцоолох хэрэгтэй гэж бодъё. Түүнчлэн, хэрэв эхний жил нь эцсийн жилээс их байвал процедур нь "1"-ийг буцаадаг бөгөөд тооцохгүй, үгүй бол бид номын тоог тоолж, 0-ийг буцаана:
PROC CREATE dbo.rownum (0FirsYear int, GLastYear int, 0result int OUTPUT) AS
ХЭРВЭЭ 0FirsYear>0 Өнгөрсөн жил БУЦАХ 1
SET @result= (dbo.Bookl-аас COUNT(*) СОНГОХ
0 эхний жилээс өнгөрсөн жил 0 ХООРОНД БАЙНА);
Буцах код нь бүхэл тоон 0ret хувьсагчд хадгалагдаж, дараа нь түүний утгыг шинжилдэг (энэ тохиолдолд 1 байх болно) энэ процедурыг дуудах хувилбарыг авч үзье. Мэдэгдэлд ашигласан Хэвлэх функц CAST нь Gres бүхэл тоон хувьсагчийн утгыг мөрийн төрөл болгон хөрвүүлэхэд ашиглагддаг:
0ret int, Gres int
EXEC Gret = rownum 2004, 2002, Gres OUT;
IF 0ret=l PRINT "Эхлэх жил эцсийн жилээс их байна"
ХЭВЛЭХ "Номын тоо" + CAST(Gres as varchar(20))
Хадгалагдсан процедур нь зөвхөн хүснэгтээс өгөгдлийг уншихаас гадна өгөгдлийг өөрчлөх, хүснэгт болон бусад олон тооны мэдээллийн сангийн объектуудыг үүсгэх боломжтой.
Гэсэн хэдий ч та хадгалагдсан процедураас схем, функц, триггер, процедур, харагдац үүсгэх боломжгүй.
Дараах жишээ нь эдгээр чадварууд болон түр зуурын объектуудын хамрах хүрээтэй холбоотой асуудлуудыг хоёуланг нь харуулж байна. Дараах хадгалагдсан процедур нь #TaL2 түр хүснэгт байгаа эсэхийг шалгадаг; Хэрэв энэ хүснэгт байхгүй бол түүнийг үүсгэдэг. Үүний дараа хоёр баганын утгыг #TaL2 хүснэгтэд оруулж, SELECT мэдэгдлийг ашиглан хүснэгтийн агуулгыг харуулна.
PROC ҮҮСГЭХ My_Procl (@id int, @name varchar(30))
ХЭРЭВ OBJECT_ID("tempdb.dbo.#Tab21) ҮНЭГҮЙ БОЛ
dbo-д INSERT INSERT.#Tab2 (id, name)VALUES (0id,0name)
dbo-аас * СОНГОХ. # Таб2 – №1
Хадгалагдсан процедурыг анх удаа дуудахын өмнө бид түүнд ашигласан #TaL2 түр хүснэгтийг үүсгэнэ. EXEC операторт анхаарлаа хандуулаарай. Өмнөх жишээнүүдэд параметрүүдийг "байрлалаар" горимд шилжүүлсэн боловч энэ тохиолдолд параметр дамжуулах өөр форматыг ашигладаг - "нэрээр", параметрийн нэр ба түүний утгыг тодорхой зааж өгсөн болно.
CREATE TABLE dbo.#Tab2 (id int, нэр varchar(30));
EXEC My_Procl 0name="lvan", 0id=2;
dbo-аас * СОНГОХ.#Tab2; -№2
Дээрх жишээн дээр SELECT мэдэгдлийг хоёр удаа боловсруулах болно: эхний удаа - процедур дотор, хоёр дахь удаагаа - дуудлагын кодын фрагментээс ("No 2" тайлбараар тэмдэглэгдсэн).
Процедурын хоёр дахь дуудлагын өмнө бид түр зуурын хүснэгтийг устгана #TaL2. Дараа нь хадгалагдсан процедураас ижил нэртэй түр зуурын хүснэгт үүсгэгдэх болно.
DROP TABLE dbo.#Tab2;
EXEC My_Procl 0name="Иван", 0id=2;
dbo-аас * СОНГОХ.#Tab2; -№2
Энэ тохиолдолд зөвхөн процедурын дотор байрлах SELECT мэдэгдэл ("Xa 1" тайлбартай) өгөгдлийг харуулах болно. Хадгалсан процедурт үүсгэсэн түр хүснэгт нь процедур буцаж ирэх үед tempdb мэдээллийн баазаас аль хэдийн устгагдах тул SELECT "No. 2"-г гүйцэтгэхэд алдаа гарах болно.
Та DROP PROCEDURE мэдэгдлийг ашиглан хадгалагдсан процедурыг устгаж болно. Түүний форматыг доор үзүүлэв. Та хэд хэдэн хадгалагдсан процедурыг таслалаар тусгаарлан жагсааж нэг мэдэгдлээр устгаж болно:
DROP (PROC I PROCEDURE) ( процедур ) [
Жишээлбэл, өмнө нь үүсгэсэн хураангуй процедурыг устгая:
DROP PROC нийлбэр;
Та ALTER PROCEDURE мэдэгдлийг (зөвшөөрөгдсөн) ашиглан одоо байгаа процедурт өөрчлөлт оруулах боломжтой (мөн үүнийг дахин тодорхойлох боломжтой).
товчлол PROC). ALTER түлхүүр үгээс бусад тохиолдолд мэдэгдлийн хэлбэр нь CREATE PROCEDURE-тэй үндсэндээ ижил байна. Жишээ нь dbo процедурыг өөрчилье. rownum, үүнийг эзэмшигчийн аюулгүй байдлын хүрээнд гүйцэтгэхээр тохируулах:
ALTER PROC dbo.rownum(SFirsYear int,
SLastYear int, Sresult int OUTPUT)
EXECUTE AS Owner - суулгах боломжтой
ХЭРВЭЭ 0FirsYear>0Өнгөрсөн жил БУЦАХ 1 ӨӨРӨӨ ЭХЭЛНЭ
SET 0result= (dbo.Bookl-аас COUNT(*) СОНГОХ
SFirsYear болон SastYear ХООРОНД ХААНА);
Зарим тохиолдолд командыг динамикаар үүсгэж, мэдээллийн сангийн сервер дээр ажиллуулах шаардлагатай байж болно. Энэ асуудлыг мөн EXEC оператор ашиглан шийдэж болно. Жилийн шинж чанар нь хувьсагчийн заасан утгатай тэнцүү бол доорх жишээ нь Bookl хүснэгтээс бичлэгүүдийг татаж авдаг.
ЗАРЛАХ 0у int = 2000;
EXEC ("SELECT * FROM dbo.Bookl WHERE = "+@y) ;
Динамикаар үүсгэгдсэн зааврыг гүйцэтгэх нь "SQL injection" гэх мэт компьютерийн халдлагыг хэрэгжүүлэх урьдчилсан нөхцөлийг бүрдүүлдэг. (Англи) SQL тарилга). Халдлагын мөн чанар нь халдагч өөрийн SQL кодыг динамикаар үүсгэгдсэн асуулгад оруулдагт оршино. Орлуулж буй параметрүүдийг хэрэглэгчийн оруулсан үр дүнгээс авах үед энэ нь ихэвчлэн тохиолддог.
Өмнөх жишээг бага зэрэг өөрчилье:
DECLARE 0у varchar(100);
SET 0у="2ООО"; - Бид үүнийг хэрэглэгчээс хүлээн авсан
Хэрэв бид SET мэдэгдэлд өгөгдсөн утгыг хэрэглэгчээс (жишээ нь вэб програмаар дамжуулан яаж ч хамаагүй) хүлээн авсан гэж үзвэл жишээ нь бидний кодын "хэвийн" үйлдлийг харуулж байна.
DECLARE 0у varchar(100);
SET 0у="2000; dbo.Book2-с УСТГАХ"; - тарилга
EXEC("SELECT * FROM dbo.Book2 WHERE ="+0y);
Ийм тохиолдолд боломжтой бол системд хадгалагдсан sp_executcsql процедурыг ашиглахыг зөвлөж байна, энэ нь параметрийн төрлийг хянах боломжийг олгодог бөгөөд энэ нь танд саад тотгор болдог. SQL тарилга. Түүний форматыг нарийвчлан авч үзэхгүйгээр өмнө нь танилцуулсантай төстэй жишээг харцгаая.
sp_executesql-г ГҮЙЦЭТГЭХ
N"СОНГОХ * dbo.Bookl WHERE =0y",
Энэ нь асуулгад ашигласан параметрийн төрлийг тодорхой зааж өгөх бөгөөд SQL Server үүнийг гүйцэтгэх явцад хянах болно. Хашилтын өмнөх "N" үсэг нь процедурын шаардлагын дагуу Юникод үсгийн тогтмол тогтмол болохыг харуулж байна. Параметрт зөвхөн тогтмол утга төдийгүй өөр хувьсагчийн утгыг оноож болно.
1. Процедурууддаа мөр оруулах - SET NOCOUNT ON: DML илэрхийлэл бүрээр SQL сервер нь боловсруулсан бичлэгийн тоог агуулсан мессежийг бидэнд буцааж өгдөг. Энэ мэдээлэл нь кодыг дибаг хийх явцад бидэнд хэрэгтэй байж болох ч үүний дараа энэ нь огт хэрэггүй болно. SET NOCOUNT ON гэж бичснээр бид энэ функцийг идэвхгүй болгодог. Олон илэрхийлэл эсвэл/буюу гогцоо агуулсан хадгалагдсан процедурын хувьд энэ үйлдэл нь гүйцэтгэлийг мэдэгдэхүйц нэмэгдүүлэх боломжтой, учир нь замын хөдөлгөөний хэмжээ мэдэгдэхүйц буурах болно.
PROC dbo.ProcName үүсгэх
AS
ТООЛГОЙГҮЙ ТОХИРУУЛАХ;
--Энд процедурын код байна
dbo.TblХүснэгт1-ээс багана1 СОНГОХ
--SET NOCOUNT-г анхны төлөв рүү шилжүүлнэ үү
NOCOUNT-ийг унтраах;
ЯВ
2. Схемийн нэрийг объектын нэрээр ашиглана уу:За, энэ нь ойлгомжтой гэж бодож байна. Энэ үйлдэл нь серверт объектуудыг хаанаас хайхыг зааж өгдөг бөгөөд хогийн савнуудаа санамсаргүй гүйлгэхийн оронд хаашаа явах, юу авахаа шууд мэдэх болно. Олон тооны мэдээллийн сан, хүснэгт, хадгалагдсан процедурын ачаар энэ нь бидний цаг хугацаа, мэдрэлийг ихээхэн хэмнэх болно.
SELECT * FROM dbo.MyTable --Үүнийг хийхэд тохиромжтой
-- Оронд нь
MyTable-аас * СОНГОХ --Үүнийг хийх нь муу
--Журмын дуудлага
EXEC dbo.MyProc --Дахин сайн
-- Оронд нь
EXEC MyProc --Муу!
3. Хадгалсан процедурынхаа нэрэнд “sp_” угтварыг бүү ашигла:Хэрэв бидний процедурын нэр "sp_" -ээр эхэлсэн бол SQL Server эхлээд үндсэн мэдээллийн сандаа харна. Баримт нь энэ угтварыг серверийн хувийн дотоод хадгалагдсан процедурт ашигладаг. Тиймээс мэдээллийн сангаас тантай ижил нэртэй процедур олдвол түүнийг ашиглах нь нэмэлт зардал, бүр буруу үр дүнд хүргэж болзошгүй юм.
4. IF EXISTS (SELECT *)-ийн оронд IF EXISTS (SELECT 1) гэснийг ашиглана уу:Өөр хүснэгтэд бичлэг байгаа эсэхийг шалгахын тулд бид IF EXISTS хэллэгийг ашигладаг. Хэрэв дотоод илэрхийллээс дор хаяж нэг утгыг буцаавал энэ илэрхийлэл үнэнийг буцаана, үүнд "1", бүх багана эсвэл хүснэгт хамаагүй. Буцаагдсан өгөгдлийг үндсэндээ ямар ч байдлаар ашигладаггүй. Тиймээс өгөгдөл дамжуулах явцад урсгалыг шахахын тулд доор үзүүлсэн шиг "1"-ийг ашиглах нь илүү логик юм.
БАЙВАЛ (Sysobjects-аас 1-ийг сонго
ХААНА нэр = "Миний хүснэгт" ба төрөл = "U")
5. Алдаа барихын тулд TRY-Catch ашиглана уу: 2005 оны серверүүдээс өмнө хүсэлт бүрийн дараа процедурт маш олон тооны алдаа шалгах бичигдсэн байдаг. Илүү их код нь үргэлж илүү их нөөц, илүү их цаг зарцуулдаг. 2005 SQL Server-тэй бол илүү зөв бөгөөд тохиромжтой аргаэнэ асуудлын шийдлүүд:
ЭХЛҮҮЛЭЭРЭЙ
--код
ХУРАЛДАА ТӨГСГӨӨ
БАРЬЖ ЭХЭЛ
--код барихад алдаа гарлаа
БАРЬЖ БАЙНА
Дүгнэлт
Нэг ёсондоо өнөөдрийн надад байгаа зүйл л энэ. Би дахин нэг удаа давтан хэлэхэд, энд зөвхөн миний практикт ашигласан техникүүд байгаа бөгөөд би тэдний үр дүнтэй болохыг баталж чадна.
P.S.
Миний анхны бичлэг, битгий хатуу шүүмжил.
Хадгалагдсан журамнь SQL хэл болон процедурын өргөтгөлүүдийг ашиглан бүтээгдсэн Transact-SQL мэдэгдлийн багцын тусгай төрөл юм. Багц болон хадгалагдсан процедурын гол ялгаа нь сүүлийнх нь өгөгдлийн сангийн объект хэлбэрээр хадгалагддагт оршино. Өөрөөр хэлбэл, дахин давтагдах даалгавруудын гүйцэтгэл, тууштай байдлыг сайжруулахын тулд хадгалагдсан процедурыг серверийн талд хадгалдаг.
Өгөгдлийн сангийн систем нь хадгалагдсан процедур болон системийн процедурыг дэмждэг. Хадгалагдсан процедурууд нь бусад бүх мэдээллийн сангийн объектуудтай ижил аргаар үүсгэгддэг, өөрөөр хэлбэл. DDL хэлийг ашиглан. Системийн процедурМэдээллийн сангийн хөдөлгүүрээр хангагдсан бөгөөд системийн каталогийн мэдээлэлд хандах, өөрчлөхөд ашиглаж болно.
Хадгалагдсан процедурыг үүсгэх үед та параметрийн нэмэлт жагсаалтыг тодорхойлж болно. Ингэснээр процедур нь дуудагдах бүрт тохирох аргументуудыг хүлээн авах болно. Хадгалагдсан процедур нь хэрэглэгчийн тодорхойлсон мэдээллийг агуулсан утгыг эсвэл алдаа гарсан тохиолдолд зохих алдааны мессежийг буцаана.
Хадгалагдсан процедурыг өгөгдлийн санд объект болгон хадгалахаас өмнө урьдчилан хөрвүүлдэг. Процедурын урьдчилан эмхэтгэсэн хэлбэр нь мэдээллийн санд хадгалагдаж, дуудагдах бүрт ашиглагддаг. Хадгалагдсан процедурын энэ шинж чанар нь (бараг бүх тохиолдолд) процедурын давталтыг арилгах, гүйцэтгэлийн зохих сайжруулалтыг бий болгох чухал ач холбогдолтой юм. Хадгалагдсан процедурын энэ шинж чанар нь өгөгдлийн сангийн систем болон програмуудын хооронд солилцох өгөгдлийн хэмжээнд эерэг нөлөө үзүүлдэг. Ялангуяа хэдэн мянган байт хэмжээтэй хадгалагдсан процедурыг дуудахад 50 байтаас бага хугацаа шаардагдана. Хэд хэдэн хэрэглэгчид хадгалагдсан процедурыг ашиглан давтагдах даалгавруудыг гүйцэтгэх үед эдгээр хэмнэлтийн хуримтлагдсан үр нөлөө нь нэлээд их байх болно.
Хадгалагдсан процедурыг дараахь зорилгоор ашиглаж болно.
өгөгдлийн сангийн хүснэгтүүдтэй үйлдлийн бүртгэл үүсгэх.
Хадгалагдсан процедурыг ашиглах нь хэрэглэгчдэд өөр өөр хандалтын эрхийг олгодог GRANT болон REVOKE мэдэгдлүүдийг ашиглан хангагдсан аюулгүй байдлын түвшингээс давсан аюулгүй байдлын хяналтын түвшинг хангадаг. Хадгалсан процедурыг гүйцэтгэх зөвшөөрөл нь дараагийн хэсэгт тайлбарласны дагуу хадгалагдсан процедурт агуулагдах объектуудыг өөрчлөх эрхээс хамааралгүй учраас энэ нь боломжтой юм.
Хүснэгт бичих ба/эсвэл унших үйлдлүүдийн бүртгэлийг үүсгэдэг хадгалагдсан процедурууд нэмэлт боломжмэдээллийн сангийн аюулгүй байдлыг хангах. Ийм процедурыг ашиглан мэдээллийн сангийн администратор нь хэрэглэгчид эсвэл програмын мэдээллийн санд хийсэн өөрчлөлтийг хянах боломжтой.
Хадгалагдсан процедурыг үүсгэх, гүйцэтгэх
Хадгалсан процедурыг мэдэгдэл ашиглан үүсгэнэ ЖУРАМ ҮҮСГЭ, дараах синтакстай байна:
PROC proc_name CREATE [((@param1) type1 [ VARYING] [= default1] )] (, ...) AS багц | ГАДААД НЭР арга_нэр Синтакс конвенци
schema_name параметр нь үүсгэсэн хадгалагдсан процедурын эзэмшигчийн өгсөн схемийн нэрийг зааж өгдөг. proc_name параметр нь хадгалагдсан процедурын нэрийг зааж өгдөг. @param1 параметр нь өгөгдлийн төрлийг type1 параметрээр тодорхойлдог процедурын параметр (албан ёсны аргумент) юм. Процедурын параметрүүд нь багц доторх орон нутгийн хувьсагчтай адил процедурын доторх орон нутгийнх байдаг. Процедурын параметрүүд нь дуудагч түүнийг ашиглах журам руу дамжуулсан утгууд юм. default1 параметр нь холбогдох процедурын параметрийн өгөгдмөл утгыг тодорхойлдог. (Өгөгдмөл утга нь мөн NULL байж болно.)
OUTPUT сонголтнь процедурын параметр нь буцах параметр бөгөөд хадгалагдсан процедураас дуудах процедур эсвэл систем рүү утгыг буцаахад ашиглагдаж болохыг харуулж байна.
Өмнө дурьдсанчлан процедурын урьдчилан эмхэтгэсэн хэлбэр нь мэдээллийн санд хадгалагдаж, дуудагдах болгонд ашиглагддаг. Хэрэв ямар нэг шалтгааны улмаас хадгалагдсан процедурыг дуудах бүрт эмхэтгэх шаардлагатай бол процедурыг зарлахдаа ДАХИН ХЭРЭГЛЭХ сонголттой. WITH RECOMPILE сонголтыг ашиглах нь хадгалагдсан процедурын хамгийн чухал давуу талуудын нэгийг үгүйсгэдэг: нэг эмхэтгэлээс болж гүйцэтгэл сайжирна. Иймд WITH RECOMPILE сонголтыг зөвхөн хадгалагдсан процедурын ашигладаг өгөгдлийн сангийн объектууд байнга өөрчлөгддөг үед л ашиглах ёстой.
EXECUTE AS заалтХадгалагдсан процедурыг дуудсаны дараа гүйцэтгэх аюулгүй байдлын контекстийг тодорхойлдог. Энэ контекстийг тохируулснаар Өгөгдлийн сангийн систем нь хадгалагдсан процедурын иш татсан объектуудад хандах зөвшөөрлийг баталгаажуулахын тулд хэрэглэгчийн бүртгэлүүдийн сонголтыг хянах боломжтой.
Анхдагч байдлаар, зөвхөн sysadmin тогтмол серверийн үүргийн гишүүд болон db_owner эсвэл db_ddladmin тогтмол өгөгдлийн сангийн үүргүүд CREATE PROCEDURE мэдэгдлийг ашиглаж болно. Гэхдээ эдгээр үүргийн гишүүд уг мэдэгдлийг ашиглан бусад хэрэглэгчдэд энэ эрхийг оноож болно ТЭТГЭЛЭГЛЭХ ЖУРАМ бий болгох.
Төслийн хүснэгттэй ажиллах энгийн хадгалагдсан процедурыг хэрхэн үүсгэхийг доорх жишээнд харуулав.
SampleDb-г ашиглах; ЯВАХ ЖУРАМ БҮРДҮҮЛЭХ Төсвийг нэмэгдүүлэх (@хувь INT=5) Төслийн багцыг ШИНЭЧЛҮҮЛЭХ Төсөв = Төсөв + Төсөв * @ хувь/100;
Өмнө дурьдсанчлан, хоёр пакетыг салгахын тулд ашиглана уу GO заавар. CREATE PROCEDURE мэдэгдлийг ижил багц дахь Transact-SQL хэллэгтэй нэгтгэх боломжгүй. IncreaseBudget хадгалагдсан процедур нь @percent параметрээр тодорхойлогдсон бүх төслийн төсвийг тодорхой хувиар нэмэгдүүлдэг. Уг процедур нь мөн процедур ажиллаж байх үед энэ аргумент байхгүй тохиолдолд ашиглагдах өгөгдмөл хувийн утгыг (5) тодорхойлдог.
Хадгалагдсан процедур нь байхгүй хүснэгтэд хандах боломжтой. Энэ шинж чанар нь эхлээд тохирох хүснэгтүүдийг үүсгэх, тэр ч байтугай очих сервертэй холбогдохгүйгээр процедурын кодыг дибаг хийх боломжийг олгодог.
Одоогийн өгөгдлийн санд үргэлж хадгалагддаг анхдагч хадгалагдсан процедуруудаас ялгаатай нь tempdb түр системийн мэдээллийн санд үргэлж хадгалагддаг түр хадгалагдсан процедуруудыг үүсгэх боломжтой. Түр хадгалагдсан процедурыг бий болгох нэг шалтгаан нь мэдээллийн санд холбогдох үед тодорхой бүлгийн мэдэгдлийг давтан гүйцэтгэхээс зайлсхийх явдал юм. Та орон нутгийн эсвэл дэлхийн түр зуурын журмыг үүсгэж болно. Үүний тулд локал процедурын нэрийг нэг # тэмдэгтээр (#proc_name), глобал процедурын нэрийг давхар тэмдэгтээр (##proc_name) зааж өгнө.
Орон нутгийн түр хадгалагдсан процедурыг зөвхөн үүсгэсэн хэрэглэгч л гүйцэтгэх боломжтой бөгөөд зөвхөн үүсгэсэн мэдээллийн санд холбогдсон үед л гүйцэтгэнэ. Глобал түр зуурын процедурыг бүх хэрэглэгчид гүйцэтгэх боломжтой, гэхдээ зөвхөн хамгийн сүүлд хийгдсэн холболт (ихэвчлэн процедурыг үүсгэгчийн холболт) дуусах хүртэл.
Хадгалагдсан процедурын амьдралын мөчлөг нь түүнийг үүсгэх, гүйцэтгэх гэсэн хоёр үе шатаас бүрдэнэ. Процедур бүрийг нэг удаа үүсгэж, олон удаа гүйцэтгэдэг. Хадгалагдсан процедурыг ашиглан гүйцэтгэнэ Зааврыг ГҮЙЦЭТГЭХПроцедурын эзэмшигч эсвэл тухайн процедурт хандах EXECUTE эрхтэй хэрэглэгч. EXECUTE мэдэгдэл нь дараах синтакстай байна:
[] [@return_status =] (proc_name | @proc_name_var) ([[@parameter1 =] утга | [@parameter1=] @variable ] | DEFAULT).. Синтакс конвенцууд
return_status параметрийг эс тооцвол EXECUTE мэдэгдлийн бүх параметрүүд нь CREATE PROCEDURE мэдэгдлийн параметрүүдтэй ижил логик утгатай байна. return_status параметр нь процедурын буцах төлөвийг хадгалах бүхэл тоон хувьсагчийг зааж өгдөг. Тогтмол (утга) эсвэл орон нутгийн хувьсагч (@ хувьсагч) ашиглан параметрт утгыг оноож болно. Нэрлэсэн параметрүүдийн утгын дараалал нь чухал биш боловч нэргүй параметрүүдийн утгыг CREATE PROCEDURE мэдэгдэлд тодорхойлсон дарааллаар өгөх ёстой.
DEFAULT заалтПроцедурын тодорхойлолтод заасан процедурын параметрийн өгөгдмөл утгыг өгдөг. Процедур нь өгөгдмөл утга нь тодорхойлогдоогүй, параметр нь байхгүй эсвэл DEFAULT түлхүүр үг заасан параметрийн утгыг хүлээх үед алдаа гарна.
EXECUTE хэллэг нь багцын эхний мэдэгдэл байх үед EXECUTE түлхүүр үгийг орхигдуулж болно. Гэхдээ энэ үгийг багц болгонд оруулах нь илүү найдвартай. EXECUTE мэдэгдлийн хэрэглээг доорх жишээнд үзүүлэв.
SampleDb-г ашиглах; IncreaseBudget 10 ГҮЙЦЭТГЭХ;
Энэ жишээн дэх ГҮЙЦЭТГЭХ мэдэгдэл нь IncreaseBudget хадгалагдсан процедурыг гүйцэтгэдэг бөгөөд энэ нь бүх төслийн төсвийг 10%-иар нэмэгдүүлдэг.
Доорх жишээнд Ажилтан болон Ажлын_хүснэгт дэх өгөгдлийг боловсруулахын тулд хадгалагдсан процедурыг хэрхэн үүсгэхийг харуулав.
ModifyEmpId жишээ процедур нь лавлагааны бүрэн бүтэн байдлыг хадгалах үйл явцын нэг хэсэг болгон хадгалагдсан процедурыг (энэ тохиолдолд Ажилтан болон Works_on хүснэгтүүдийн хооронд) ашиглахыг харуулж байна. Үүнтэй төстэй хадгалагдсан процедурыг триггерийн тодорхойлолт дотор ашиглаж болох бөгөөд энэ нь лавлагааны бүрэн бүтэн байдлыг хангадаг.
Дараах жишээ нь хадгалагдсан процедурт OUTPUT заалтыг ашиглахыг харуулж байна:
Энэхүү хадгалагдсан процедурыг дараах зааврыг ашиглан гүйцэтгэж болно.
@quantityDeleteEmployee INT ЗАРЛАХ; DeleteEmployee @empId=18316, @counter=@quantityDeleteEmployee OUTPUT ГҮЙЦЭТГЭХ; PRINT N"Устгасан ажилчид: " + convert(nvarchar(30), @quantityDeleteEmployee);
Энэ процедур нь @empId боловсон хүчний дугаартай ажилтны ажиллаж байгаа төслийн тоог тоолж, үр дүнгийн утгыг ©counter параметрт онооно. Тухайн ажилтны дугаарын бүх мөрийг Ажилтан болон Ажлын_хүснэгтээс устгасны дараа тооцоолсон утгыг @quantityDeleteEmployee хувьсагчид онооно.
Зөвхөн OUTPUT сонголтыг зааж өгсөн тохиолдолд параметрийн утгыг дуудлагын горимд буцаана. Дээрх жишээн дээр DeleteEmployee процедур нь @counter параметрийг дуудах процедурт дамжуулдаг тул хадгалагдсан процедур нь системд утгыг буцаадаг. Иймд @counter параметрийг процедурыг зарлахдаа OUTPUT сонголт болон түүнийг дуудах үед ГҮЙЦЭТГЭХ командын аль алинд нь зааж өгөх ёстой.
WITH RESULTS SETS EXECUTE мэдэгдлийн заалт
SQL Server 2012 дээр EXECUTE мэдэгдлийн хувьд та оруулна WITH RESULTS SETS заалт, үүгээр дамжуулан тодорхой нөхцөл хангагдсан тохиолдолд хадгалагдсан процедурын үр дүнгийн багцын хэлбэрийг өөрчлөх боломжтой.
Дараах хоёр жишээ нь энэ өгүүлбэрийг тайлбарлахад тусална. Эхний жишээ нь WITH RESULTS SETS заалтыг орхигдуулсан үед үр дүн нь ямар харагдахыг харуулсан танилцуулах жишээ юм:
EmployeesInDept журам нь тодорхой хэлтэст ажиллаж байгаа бүх ажилтны боловсон хүчний дугаар, овог нэрийг харуулдаг энгийн журам юм. Хэлтсийн дугаар нь процедурын параметр бөгөөд түүнийг дуудахдаа заавал зааж өгөх ёстой. Энэ процедурыг гүйцэтгэснээр гарчиг нь өгөгдлийн сангийн хүснэгтийн харгалзах баганын нэртэй таарч байгаа хоёр багана бүхий хүснэгтийг үүсгэдэг, i.e. Id болон овог нэр. Үр дүнгийн баганын толгой хэсгийг (түүнчлэн тэдгээрийн өгөгдлийн төрлийг) өөрчлөхийн тулд SQL Server 2012 нь шинэ WITH RESULTS SETS заалтыг ашигладаг. Энэ өгүүлбэрийн хэрэглээг доорх жишээнд үзүүлэв.
SampleDb-г ашиглах; EXEC EmployeesInDept "d1" ҮР ДҮНТЭЙ БАГЦ (( INT NOT NULL, [Овог] CHAR(20) NOT NULL));
Ийм байдлаар дуудагдсан хадгалагдсан процедурыг гүйцэтгэсний үр дүн дараах байдалтай байна.
Таны харж байгаагаар EXECUTE мэдэгдлийн WITH RESULT SETS заалтыг ашиглан хадгалагдсан процедурыг ажиллуулах нь процедурын үүсгэсэн үр дүнгийн багц дахь баганын нэр, өгөгдлийн төрлийг өөрчлөх боломжийг танд олгоно. Тиймээс энэхүү шинэ функц нь хадгалагдсан процедурыг гүйцэтгэх, үр дүнг шинэ хүснэгтэд байрлуулахад илүү уян хатан байдлыг хангадаг.
Хадгалагдсан процедурын бүтцийг өөрчлөх
Өгөгдлийн сангийн хөдөлгүүр нь мөн зааврыг дэмждэг ЖУРАМЫГ ӨӨРЧЛӨХхадгалагдсан процедурын бүтцийг өөрчлөх. ALTER мэдэгдэл PROCEDURE нь ихэвчлэн процедур доторх Transact-SQL хэллэгийг өөрчлөхөд ашиглагддаг. ALTER PROCEDURE мэдэгдлийн бүх параметрүүд нь CREATE PROCEDURE мэдэгдлийн ижил параметрүүдтэй ижил утгатай. Энэхүү мэдэгдлийг ашиглах гол зорилго нь хадгалагдсан процедурын одоо байгаа эрхийг хүчингүй болгохоос зайлсхийх явдал юм.
Өгөгдлийн сангийн хөдөлгүүр нь дэмждэг CURSOR өгөгдлийн төрөл. Энэ өгөгдлийн төрлийг хадгалсан процедурт курсор зарлахад ашигладаг. Курсорнь асуулгын үр дүнг (ихэвчлэн мөрийн багц) хадгалахад ашигладаг програмчлалын бүтэц бөгөөд хэрэглэгчдэд тухайн үр дүнг мөр мөрөөр харуулах боломжийг олгодог.
Нэг буюу хэсэг хадгалагдсан процедурыг устгахын тулд ашиглана уу DROP PROCEDURE заавар. Зөвхөн db_owner болон sysadmin-н тогтсон үүргийн эзэмшигч эсвэл гишүүд хадгалагдсан процедурыг устгах боломжтой.
Хадгалагдсан процедур ба нийтлэг хэлний ажиллах хугацаа
SQL Server нь C# ашиглан янз бүрийн өгөгдлийн сангийн объектуудыг (хадгалагдсан процедур, хэрэглэгчийн тодорхойлсон функц, триггер, хэрэглэгчийн тодорхойлсон нэгтгэлүүд болон захиалгат өгөгдлийн төрлүүд) боловсруулах боломжийг олгодог нийтлэг хэлний ажиллах цагийг (CLR) дэмждэг. Visual Basic. CLR нь танд эдгээр объектуудыг нийтлэг ажиллах цагийн системийг ашиглан гүйцэтгэх боломжийг олгодог.
Сонголтыг ашиглан нийтлэг хэлний ажиллах хугацааг идэвхжүүлж, идэвхгүй болгосон clr_идэвхжүүлсэнсистемийн журам sp_configure, зааварчилгаагаар гүйцэтгэхээр эхлүүлсэн ДАХИН ТОХИРУУЛАХ. Дараах жишээ нь CLR-ийг идэвхжүүлэхийн тулд sp_configure системийн процедурыг хэрхэн ашиглаж болохыг харуулж байна:
SampleDb-г ашиглах; EXEC sp_configure "clr_enabled",1 ДАХИН ТОХИРУУЛАХ
CLR ашиглан процедур үүсгэх, эмхэтгэх, хадгалахын тулд та дараах дарааллыг харуулсан дарааллаар хийх ёстой.
Хадгалсан процедурыг C# эсвэл Visual Basic хэл дээр үүсгээд дараа нь тохирох хөрвүүлэгчийг ашиглан хөрвүүлнэ.
Хэрэглэх заавар УГСРАЛТ ҮЗҮҮЛЭХ, харгалзах гүйцэтгэх файлыг үүсгэнэ.
EXECUTE хэллэгийг ашиглан процедурыг гүйцэтгэнэ.
Доорх зургийг харуулж байна график диаграмөмнө нь тодорхойлсон алхамууд. Доорх нь илүү Дэлгэрэнгүй тодорхойлолтэнэ үйл явц.
Эхлээд шаардлагатай программыг зарим хөгжлийн орчинд үүсгэнэ үү Visual Studio. Дууссан программыг C# эсвэл Visual Basic хөрвүүлэгч ашиглан объект код болгон хөрвүүлнэ. Энэ код нь динамик холбоосын номын санд (.dll) хадгалагддаг бөгөөд энэ нь завсрын гүйцэтгэх кодыг үүсгэдэг CREATE ASSEMBLY мэдэгдлийн эх сурвалж болдог. Дараа нь гүйцэтгэх кодыг мэдээллийн сангийн объект болгон хадгалахын тулд CREATE PROCEDURE мэдэгдлийг гарга. Эцэст нь мэддэг EXECUTE мэдэгдлийг ашиглан процедурыг ажиллуулна уу.
Доорх жишээ нь C# хэл дээрх хадгалагдсан процедурын эх кодыг харуулж байна:
System.Data.SqlClient ашиглах; Microsoft.SqlServer.Server ашиглах; нийтийн хэсэгчилсэн анги StoredProcedures ( public static int CountEmployees() ( int rows; SqlConnection холболт = шинэ SqlConnection("Context Connection=true"); connection.Open(); SqlCommand cmd = connection.CreateCommand(); cmd.CommandText = "сонгох count(*) "Ажилтны тоо" " + "ажилтанаас"; rows = (int)cmd.ExecuteScalar(); connection.Close(); буцах мөр; ) )
Энэ процедур нь Ажилтны хүснэгтийн мөрийн тоог тоолох хүсэлтийг хэрэгжүүлдэг. Програмын эхэнд байгаа удирдамжийг ашиглан програмыг гүйцэтгэхэд шаардагдах нэрийн орон зайг зааж өгнө. Эдгээр удирдамжийг ашиглах нь танд харгалзах нэрийн орон зайг тодорхой зааж өгөхгүйгээр эх кодод ангийн нэрийг зааж өгөх боломжийг олгоно. Дараа нь StoredProcedures анги тодорхойлогдоно SqlProcedure шинж чанар, энэ нь энэ анги нь хадгалагдсан процедур гэдгийг хөрвүүлэгчид мэдээлдэг. CountEmployees() аргыг ангийн код дотор тодорхойлсон. Өгөгдлийн сангийн системтэй холболтыг тухайн ангийн жишээгээр дамжуулан хийдэг SqlConnection. Холболтыг нээхийн тулд энэ жишээний Open() аргыг ашиглана. А CreateCommand() аргаангийн жишээнд хандах боломжийг танд олгоно SqlCommnd, шаардлагатай SQL командыг дамжуулдаг.
Дараах кодын хэсэгчилсэн хэсэгт:
Cmd.CommandText = "Ажилтнаас " + "тооцоог (*) "Ажилчдын тоо" гэж сонгоно уу";
Ажилчдын хүснэгтийн мөрийн тоог тоолж, үр дүнг харуулахын тулд SELECT мэдэгдлийг ашигладаг. Командын текстийг CreateCommand() аргаар буцаасан жишээнд cmd хувьсагчийн CommandText шинж чанарыг тохируулах замаар тодорхойлно. Дараа нь гэж нэрлэдэг ExecuteScalar() арга SqlCommand жишээ. Энэ арга нь бүхэл тоо өгөгдлийн төрөлд хөрвүүлж, мөрийн хувьсагчдад оноогдсон скаляр утгыг буцаана.
Та одоо Visual Studio ашиглан энэ кодыг эмхэтгэх боломжтой. Би энэ ангийг CLRStoredProcedures нэртэй төсөлд нэмсэн тул Visual Studio нь *.dll өргөтгөлтэй ижил нэртэй угсралтыг эмхэтгэх болно. Доорх жишээ нь хадгалагдсан процедурыг үүсгэх дараагийн алхамыг харуулж байна: гүйцэтгэх кодыг бий болгох. Энэ жишээн дээрх кодыг ажиллуулахын өмнө та эмхэтгэсэн dll файлын байршлыг мэдэх хэрэгтэй (ихэвчлэн төслийн Debug хавтсанд байдаг).
SampleDb-г ашиглах; "D:\Projects\CLRStoredProcedures\bin\Debug\CLRStoredProcedures.dll"-с PERMISSION_SET = АЮУЛГҮЙ БАЙДАЛ АСЕМБЛИЙГ ҮҮСГЭЭРЭЙ.
CREATE ASSEMBLY мэдэгдэл нь удирддаг кодыг оролт болгон авч, CLR хадгалагдсан процедур, хэрэглэгчийн тодорхойлсон функц, триггер үүсгэх боломжтой харгалзах объектыг үүсгэдэг. Энэ заавар нь дараах синтакстай байна.
АССАМБЛИЙГ ҮЗҮҮЛЭХ Ассемблерийн нэр [ ЗӨВШӨӨРӨЛИЙН эзний_нэр ] (dll_file) Синтакс конвенцуудаас
assembly_name параметр нь угсралтын нэрийг зааж өгдөг. Нэмэлт AUTRIZATION заалт нь энэ чуулганы эзэмшигчийн дүрийн нэрийг зааж өгдөг. FROM заалт нь ачаалах угсралт хаана байх замыг зааж өгдөг.
WITH PERMISSION_SET заалт CREATE ASSEMBLY мэдэгдлийн маш чухал заалт бөгөөд үргэлж зааж өгөх ёстой. Энэ нь угсралтын кодонд олгогдсон зөвшөөрлийн багцыг тодорхойлдог. SAFE зөвшөөрлийн багц нь хамгийн хязгаарлагдмал юм. Эдгээр эрхтэй Ассемблей код нь файл гэх мэт системийн гадаад эх сурвалжид хандах боломжгүй. EXTERNAL_ACCESS эрхийн багц нь угсралтын кодыг зарим гадаад системийн эх сурвалжид хандах боломжийг олгодог бол UNSAFE эрхийн багц нь мэдээллийн сангийн системийн дотор болон гаднах нөөцөд хязгаарлалтгүйгээр нэвтрэх боломжийг олгодог.
Ассемблей кодын мэдээллийг хадгалахын тулд хэрэглэгч CREATE ASSEMBLY мэдэгдлийг гаргах боломжтой байх ёстой. Чуулганы эзэмшигч нь зааварчилгааг гүйцэтгэх хэрэглэгч (эсвэл үүрэг) юм. Та CREATE SCHEMA мэдэгдлийн AUTORIZATION заалтыг ашиглан өөр хэрэглэгчийг уг чуулганы эзэмшигч болгож болно.
Өгөгдлийн сангийн хөдөлгүүр нь мөн ALTER ASSEMBLY болон DROP ASSEMBLY мэдэгдлүүдийг дэмждэг. ALTER ASSEMBLY мэдэгдэлугсралтыг шинэчлэхэд ашигладаг Хамгийн сүүлийн үеийн хувилбар. Энэ заавар нь холбогдох угсралттай холбоотой файлуудыг нэмж эсвэл устгана. DROP зааварУГСРАЛТЗаасан угсралт болон түүнтэй холбоотой бүх файлыг одоогийн мэдээллийн сангаас устгана.
Доорх жишээнд өмнө нь хэрэгжүүлсэн удирдсан код дээр үндэслэн хадгалагдсан процедурыг хэрхэн үүсгэхийг харуулав.
SampleDb-г ашиглах; ЯВАХ ПРОЦЕДУРАС ҮҮСГҮҮЛЭХ CountAmployees CLRStoredProcedures.StoredProcedures.CountEmployees ГАДААД НЭРЭЭР
Жишээн дэх CREATE PROCEDURE заавар нь өмнөх жишээнүүдийн ижил зааварчилгааг агуулж байгаагаараа ялгаатай. EXTERNAL NAME параметр. Энэ сонголт нь кодыг нийтлэг хэлний ажиллах хугацаанд үүсгэсэн болохыг зааж өгдөг. Энэ өгүүлбэр дэх нэр нь гурван хэсгээс бүрдэнэ.
угсралтын_нэр.ангийн_нэр.арга_нэр
assembly_name - угсралтын нэрийг заана;
ангийн_нэр - ерөнхий ангийн нэрийг заана;
method_name - нэмэлт хэсэг, анги дотор тодорхойлсон аргын нэрийг зааж өгнө.
CountEmployees процедурын гүйцэтгэлийг доорх жишээнд үзүүлэв.
SampleDb-г ашиглах; МЭДЭГДЭХ @count INT ГҮЙЦЭТГЭХ @count = Ажилчдын тоо PRINT @count -- Буцах 7
PRINT мэдэгдэл нь Ажилчдын хүснэгтийн одоогийн мөрийн тоог буцаана.
хадгалагдсан процедурПроцедур байрладаг мэдээллийн сангийн хүрээнд хийгдсэн тохиолдолд л боломжтой.Хадгалагдсан процедурын төрлүүд
SQL сервер нь хэд хэдэн төрөлтэй хадгалагдсан процедурууд.
- Систем хадгалагдсан процедуруудянз бүрийн захиргааны үйлдлийг гүйцэтгэхэд зориулагдсан. Бараг бүх серверийн удирдлагын үйл ажиллагааг тэдний тусламжтайгаар гүйцэтгэдэг. Үүнийг бид системтэй гэж хэлж болно хадгалагдсан процедурууднь системийн хүснэгтүүдтэй ажиллах боломжийг олгодог интерфейс бөгөөд энэ нь эцсийн дүндээ хэрэглэгчийн болон системийн мэдээллийн сангийн системийн хүснэгтээс өгөгдлийг өөрчлөх, нэмэх, устгах, сэргээхэд хүргэдэг. Систем хадгалагдсан процедурууд sp_ угтвартай, системийн мэдээллийн санд хадгалагддаг бөгөөд өөр ямар ч мэдээллийн сангийн контекстоор дуудаж болно.
- Захиалгат хадгалагдсан процедуруудтодорхой арга хэмжээг хэрэгжүүлэх. Хадгалагдсан процедурууд– бүрэн хэмжээний мэдээллийн сангийн объект. Үүний үр дүнд тус бүр хадгалагдсан процедургүйцэтгэх тодорхой мэдээллийн санд байрладаг.
- Түр зуурын хадгалагдсан процедуруудхэсэг хугацаанд л байх ба үүний дараа сервер автоматаар устгагдах болно. Тэдгээрийг орон нутгийн болон дэлхийн гэж хуваадаг. Орон нутгийн түр зуурын хадгалагдсан процедуруудТэдний үүсгэсэн холболтоос л дуудаж болно. Ийм журам үүсгэхдээ та түүнд нэг # тэмдэгтээр эхэлсэн нэр өгөх ёстой. Бүх түр зуурын объектуудын нэгэн адил хадгалагдсан процедуруудХэрэглэгч салгах эсвэл серверийг дахин эхлүүлэх эсвэл зогсоох үед энэ төрлийн автоматаар устгагдах болно. Дэлхийн түр зуурын хадгалагдсан процедуруудижил журамтай серверээс ямар ч холболт хийх боломжтой. Үүнийг тодорхойлохын тулд ## тэмдэгтээр эхэлсэн нэр өгөхөд л хангалттай. Эдгээр процедур нь серверийг дахин эхлүүлэх эсвэл зогсоох, эсвэл үүсгэсэн контекст дэх холболтыг хаах үед устгагдана.
Хадгалагдсан процедурыг үүсгэх, өөрчлөх, устгах
Бүтээл хадгалагдсан процедурдараахь асуудлуудыг шийдвэрлэхэд хамаарна.
- үүсгэсэн төрлийг тодорхойлох хадгалагдсан процедур: түр зуурын эсвэл захиалгат. Үүнээс гадна та өөрийн системийг үүсгэж болно хадгалагдсан процедур, sp_ угтвартай нэр өгч, системийн мэдээллийн санд байрлуулна. Энэ процедур нь ямар ч локал серверийн мэдээллийн сангийн хүрээнд боломжтой байх болно;
- нэвтрэх эрхийг төлөвлөх. Бүтээж байхдаа хадгалагдсан процедурөгөгдлийн сангийн объектуудад түүнийг үүсгэсэн хэрэглэгчийн адил хандалтын эрхтэй байх болно гэдгийг анхаарах хэрэгтэй;
- тодорхойлолт хадгалагдсан процедурын параметрүүд. Ихэнх програмчлалын хэлэнд байдаг процедуртай адил, хадгалагдсан процедуруудоролт ба гаралтын параметртэй байж болно;
- код боловсруулах хадгалагдсан процедур. Процедурын код нь бусад руу залгах гэх мэт ямар ч SQL командын дарааллыг агуулж болно хадгалагдсан процедурууд.
Шинээр бий болгож, одоо байгаа зүйлийг өөрчлөх хадгалагдсан процедурдараах тушаалыг ашиглан хийсэн:
<определение_процедуры>::= (CREATE | ALTER ) procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]
Энэ командын параметрүүдийг харцгаая.
sp_ , #, ## угтваруудыг ашиглан үүсгэсэн процедурыг систем эсвэл түр зуурын гэж тодорхойлж болно. Командын синтаксаас харахад үүсгэсэн процедурыг эзэмших эзэмшигчийн нэр, түүнчлэн түүнийг байрлуулах мэдээллийн сангийн нэрийг зааж өгөхийг хориглоно. Тиймээс, бий болгосон байрлуулахын тулд хадгалагдсан процедуртодорхой мэдээллийн санд та тухайн мэдээллийн сангийн контекст дээр CREATE PROCEDURE командыг өгөх ёстой. Бие махбодоос эргэх үед хадгалагдсан процедуртовчилсон нэрийг ижил мэдээллийн сангийн объектод ашиглаж болно, өөрөөр хэлбэл мэдээллийн сангийн нэрийг зааж өгөхгүйгээр. Бусад өгөгдлийн санд байгаа объектуудад хандах шаардлагатай бол мэдээллийн сангийн нэрийг зааж өгөх шаардлагатай.
Нэрэнд байгаа дугаар нь таних дугаар юм хадгалагдсан процедур, энэ нь түүнийг процедурын бүлэгт өвөрмөц байдлаар тодорхойлдог. Удирдлагад хялбар болгох үүднээс процедурууд нь логикийн хувьд ижил төрлийн байдаг хадгалагдсан процедуруудижил нэртэй боловч өөр таних дугаар өгөх замаар бүлэглэж болно.
Үүсгэсэн доторх оролт, гаралтын өгөгдлийг дамжуулах хадгалагдсан процедурпараметрүүдийг ашиглаж болох бөгөөд тэдгээрийн нэр нь локал хувьсагчийн нэр шиг @ тэмдэгээр эхлэх ёстой. Нэг хадгалагдсан процедурТа таслалаар тусгаарлагдсан олон параметрүүдийг зааж өгч болно. Процедурын үндсэн хэсэгт нэр нь энэ процедурын параметрүүдийн нэртэй давхцаж байгаа локал хувьсагчийг ашиглах ёсгүй.
Харгалзах өгөгдлийн төрлийг тодорхойлох хадгалагдсан процедурын параметр, ямар ч төрөл тохиромжтой SQL өгөгдөл, үүнд хэрэглэгчийн тодорхойлсон. Гэхдээ CURSOR өгөгдлийн төрлийг зөвхөн байдлаар ашиглах боломжтой гаралтын параметр хадгалагдсан процедур, өөрөөр хэлбэл OUTPUT түлхүүр үгийг зааж өгч байна.
OUTPUT түлхүүр үг байгаа нь харгалзах параметр нь өгөгдлийг буцаах зорилготой гэсэн үг юм хадгалагдсан процедур. Гэхдээ энэ нь параметр нь утгыг дамжуулахад тохиромжгүй гэсэн үг биш юм хадгалагдсан процедур. OUTPUT түлхүүр үгийг зааж өгснөөр серверээс гарах заавар өгнө хадгалагдсан процедурпараметрийн утга болгон процедурыг дуудах үед тодорхойлсон локал хувьсагчид параметрийн одоогийн утгыг оноох. OUTPUT түлхүүр үгийг зааж өгөхдөө процедурыг дуудах үед тохирох параметрийн утгыг зөвхөн локал хувьсагч ашиглан тохируулах боломжтой гэдгийг анхаарна уу. Ердийн параметрүүдэд зөвшөөрөгдсөн аливаа илэрхийлэл эсвэл тогтмолыг ашиглахыг зөвшөөрөхгүй.
VARYING түлхүүр үг нь хамт хэрэглэгддэг