Өгөгдлийн санд хадгалагдсан процедурууд sql. T-SQL-д хадгалагдсан процедурыг өөрчлөх - ALTER PROCEDURE мэдэгдэл. Процедурыг гүйцэтгэх давуу эрх

Microsoft дээр SQL серверөөрийн алгоритмаа хэрэгжүүлэх, автоматжуулах ( тооцоолол) та хадгалагдсан процедурыг ашиглаж болох тул өнөөдөр бид тэдгээрийг хэрхэн үүсгэх, өөрчлөх, устгах талаар ярих болно.

Гэхдээ эхлээд хадгалагдсан процедур гэж юу болох, T-SQL-д яагаад хэрэгтэйг ойлгохын тулд бага зэрэг онолыг хэлье.

Анхаар! Эхлэн програмистуудад би T-SQL дээр дараах хэрэгтэй материалыг санал болгож байна.

T-SQL хэлийг илүү нарийвчлан судлахын тулд би T-SQL програмистын зам номыг уншихыг зөвлөж байна. Transact-SQL хэлний заавар.

T-SQL-д хадгалагдсан процедур гэж юу вэ?

Хадгалагдсан процедурууд– Эдгээр нь SQL зааврын багц хэлбэрээр алгоритм агуулсан мэдээллийн сангийн объектууд юм. Өөрөөр хэлбэл, хадгалагдсан процедурууд нь мэдээллийн сан доторх програмууд гэж хэлж болно. Хадгалагдсан процедурыг сервер дээр дахин ашиглах боломжтой кодыг хадгалахад ашигладаг, жишээлбэл, та ямар нэг алгоритм, дараалсан тооцоолол эсвэл олон шатлалт SQL мэдэгдлийг бичсэн бөгөөд үүнд багтсан бүх зааврыг биелүүлэхгүй байх болно. энэ алгоритмТа үүнийг хадгалагдсан процедур хэлбэрээр форматлаж болно. Үүний зэрэгцээ, таныг SQL процедурыг үүсгэх үед сервер кодыг эмхэтгэж, дараа нь энэ процедурыг ажиллуулах бүртээ SQL сервердахиж эмхэтгэхгүй.

SQL Server дээр хадгалагдсан процедурыг ажиллуулахын тулд та EXECUTE командыг нэрний өмнө бичих ёстой бөгөөд энэ тушаалыг EXEC гэж товчлох боломжтой. Хадгалагдсан процедурыг SELECT мэдэгдэлд дуудах, жишээлбэл, функцийн хувьд ажиллахаа болино, i.e. процедурыг тусад нь эхлүүлдэг.

Хадгалагдсан процедуруудад функцүүдээс ялгаатай нь UNSERT, UPDATE, DELETE гэх мэт өгөгдлийг өөрчлөх үйлдлүүдийг аль хэдийн хийх боломжтой байдаг. Та мөн бараг бүх төрлийн SQL хэллэгийг процедурт ашиглаж болно, жишээлбэл, CREATE TABLE хүснэгт үүсгэх эсвэл ГҮЙЦЭТГЭХ гэх мэт. бусад процедурыг дуудах. Үл хамаарах зүйл бол функц, харагдац, триггер үүсгэх эсвэл өөрчлөх, схем үүсгэх болон бусад ижил төстэй зааварчилгаа гэх мэт хэд хэдэн төрлийн заавар юм, жишээлбэл, та хадгалагдсан процедурт өгөгдлийн сангийн холболтын контекстийг (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, БүтээгдэхүүнийНэр, Үнэ) VALUES (1 , "Хулгана", 100), (1, "Гар", 200), (2, "Утас", 400) GO --Асуулга сонгох СОНГОХ * Туршилтын Хүснэгтээс

Бидэнд өгөгдөл байгаа, одоо хадгалагдсан процедур үүсгэх рүү шилжье.

T-SQL-д хадгалагдсан процедур үүсгэх - CREATE PROCEDURE мэдэгдэл

Хадгалсан процедурыг мэдэгдэл ашиглан үүсгэнэ ЖУРАМ ҮҮСГЭ, энэ зааврын дараа та процедурынхаа нэрийг бичиж, шаардлагатай бол хаалтанд оролт, гаралтын параметрүүдийг тодорхойлно. Үүний дараа та AS түлхүүр үгийг бичиж, зааврын блокыг нээнэ түлхүүр үгЭХЛЭХ, хаах энэ блок END гэдэг үгтэй. Энэ блок дотор та өөрийн алгоритмыг хэрэгжүүлэх бүх заавар эсвэл ямар нэгэн дараалсан тооцоолол бичих, өөрөөр хэлбэл T-SQL дээр программчлах болно.

Жишээлбэл, нэмэх хадгалагдсан процедурыг бичье шинэ оруулга, өөрөөр хэлбэл Манай туршилтын ширээний шинэ бүтээгдэхүүн. Үүнийг хийхийн тулд бид гурван оролтын параметрийг тодорхойлох болно: @CategoryId - бүтээгдэхүүний ангиллын тодорхойлогч, @ БүтээгдэхүүнийНэр - бүтээгдэхүүний нэр, @Price - бүтээгдэхүүний үнэ, энэ параметрБид нэмэлт сонголттой байх болно, өөрөөр хэлбэл. үүнийг процедурт шилжүүлэх шаардлагагүй болно ( жишээлбэл, бид үнийг хараахан мэдэхгүй байна), энэ зорилгоор бид түүний тодорхойлолтод анхдагч утгыг тохируулах болно. Эдгээр параметрүүд нь процедурын биед, i.e. Энгийн хувьсагчтай адил BEGIN...END блокыг ашиглаж болно ( Таны мэдэж байгаагаар хувьсагчдыг @ тэмдгээр тэмдэглэдэг). Хэрэв та гаралтын параметрүүдийг зааж өгөх шаардлагатай бол параметрийн нэрний дараа OUTPUT ( гэсэн түлхүүр үгийг оруулна уу. эсвэл товчоор OUT).

BEGIN...END блок дээр бид өгөгдөл нэмэх заавар, мөн процедурын төгсгөлд SELECT зааврыг бичих бөгөөд ингэснээр хадгалагдсан процедур нь заасан ангилалд багтсан бүтээгдэхүүний талаархи хүснэгтэн өгөгдлийг буцааж өгөх болно. шинэ, дөнгөж нэмсэн бүтээгдэхүүнийг тооц. Мөн энэ хадгалагдсан процедурт би орж ирж буй параметрийн боловсруулалтыг нэмсэн, тухайлбал хэд хэдэн хоосон зайг санамсаргүйгээр оруулсан нөхцөл байдлыг арилгахын тулд текстийн мөрийн эхэн ба төгсгөлд нэмэлт зайг арилгах.

Энэ процедурын код энд байна ( Би ч гэсэн энэ талаар тайлбар хийсэн).

Процедур үүсгэх CREATE PROCEDURE TestProcedure (--Оролтын параметрүүдийг @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) ЭХЛЭХЭЭР --Таны алгоритмыг хэрэгжүүлэх заавар --Ирж буй параметрүүдийг боловсруулах --Эхлэл дэх нэмэлт зайг арилгах мөн текстийн мөрийн төгсгөлд SET @ProductName = LTRIM(RTRIM(@ProductName)); --Шинэ бичлэг нэмэх Туршилтын хүснэгтэнд INSERT INSERT(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --Өгөгдлийг буцаах СОНГОХ * TestTable-с CategoryId = @CategoryId Төгсгөл ГАРНА.

T-SQL - EXECUTE команд дээр хадгалагдсан процедурыг ажиллуулж байна

Та EXECUTE эсвэл EXEC командыг ашиглан хадгалагдсан процедурыг ажиллуулж болно. Ирж буй параметрүүдийг жагсааж, зааж өгөх замаар процедурт дамжуулдаг харгалзах утгуудпроцедурын нэрний дараа ( гаралтын параметрүүдийн хувьд OUTPUT командыг зааж өгөх шаардлагатай). Гэсэн хэдий ч параметрүүдийн нэрийг зааж өгөхгүй байж болох ч энэ тохиолдолд утгыг зааж өгөх дарааллыг дагаж мөрдөх шаардлагатай, i.e. оролтын параметрүүдийг тодорхойлсон дарааллаар утгуудыг зааж өгөх ( Энэ нь гаралтын параметрүүдэд мөн хамаарна).

Анхдагч утгатай параметрүүдийг зааж өгөх шаардлагагүй бөгөөд эдгээр нь нэмэлт параметрүүд юм.

Хадгалагдсан процедурыг ажиллуулах хэд хэдэн өөр боловч ижил төстэй аргууд, тухайлбал бидний тестийн процедурыг энд оруулав.

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 (--Incoming parameters @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) процедурыг ЭХЭЛЛЭЭД өөрчилнө --Таны алгоритмыг хэрэгжүүлэх заавар --Ирж буй параметрүүдийг боловсруулах --Эхлэл дэх нэмэлт зайг арилгах болон текст мөрүүдийн төгсгөл SET @ProductName = LTRIM(RTRIM(@ProductName)); --Шинэ бичлэг нэмэх Туршилтын хүснэгтэд INSERT INTO(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) ТӨГСГӨЛ.

T-SQL - DROP PROCEDURE мэдэгдэл дэх хадгалагдсан процедурыг устгах

Шаардлагатай бол та хадгалагдсан процедурыг устгаж болно, үүнийг зааврыг ашиглан хийдэг УНАХ ЖУРАМ.

Жишээлбэл, бидний үүсгэсэн тестийн процедурыг устгая.

DROP PROCEDURE TestProcedure

Хадгалагдсан процедурыг устгахдаа хэрэв уг процедурыг бусад процедур эсвэл SQL хэллэгээр иш татсан бол устгасны дараа алдаа гарна гэдгийг санах нь зүйтэй.

Энэ бол надад байгаа зүйл, материал танд сонирхолтой, хэрэгтэй байсан гэж найдаж байна, баяртай!

Энэ цувралын өмнөх нийтлэлд бид хэрхэн хүснэгтээс өгөгдөл гаргаж авах, бүтцийг нь өөрчлөх, мэдээллийн сан болон тэдгээрт агуулагдах объектуудыг үүсгэх, өөрчлөх, устгах талаар авч үзсэн. Энэ нийтлэлд бид серверийн DBMS-ийн ердийн объектуудын талаар илүү дэлгэрэнгүй ярих болно: үзэл бодол, триггер, хадгалагдсан процедур.

Манай сэтгүүлийн 3'2000 дугаарт нийтлэгдсэн энэхүү цувралын эхний нийтлэлд бид ихэнх орчин үеийн серверийн DBMS нь үзэл бодол, триггер болон хадгалагдсан процедурыг дэмждэг болохыг тэмдэглэсэн. Мөн Access, dBase, Clipper гэх мэт олон ширээний DBMS-уудаар харахыг дэмждэг.

Триггер болон хадгалагдсан процедурыг ихэвчлэн SQL хэлний процедурын өргөтгөл болох програмчлалын хэл дээр бичдэг гэдгийг тэмдэглэх нь зүйтэй. Эдгээр өргөтгөлүүд нь алгоритмуудыг тайлбарлах боломжийг олгодог операторуудыг агуулдаг, жишээ нь do... байхад, хэрэв... тэгвэл... өөрөөр хэлбэл, SQL хэлэнд байхгүй (хэрэв та санаж байгаа бол SQL бол процедурын бус хэл юм). , мөн та түүнд даалгаврыг боловсруулж болно, гэхдээ та түүнийг гүйцэтгэх алгоритмыг тайлбарлаж чадахгүй). Стандартад нийцсэн SQL хэлнээс ялгаатай нь түүний процедурын өргөтгөлүүд нь ямар ч байдлаар стандартчилагдаагүй бөгөөд өөр өөр DBMS нь ижил алгоритмын бүтцийг хэрэгжүүлэхийн тулд өөр өөр синтакс бүтцийг ашигладаг боловч өөр өөр DBMS-ийн SQL өргөтгөлүүдийн синтакс дахь ялгааг хэлэлцэх нь хамрах хүрээнээс гадуур юм. энэ нийтлэлийн.

Та харагдац, триггер болон хадгалагдсан процедурыг хэрхэн ашиглаж болохыг харуулахын тулд бид Microsoft SQL Server 7.0 болон энэхүү DBMS-д багтсан NorthWind мэдээллийн санг сонгосон.

Жишээнүүдийг ажиллуулахаасаа өмнө таны ашигладаг DBMS дахь триггер болон хадгалагдсан процедурыг хэрэгжүүлэх, хадгалах нь энэ нийтлэлд үзүүлсэнээс өөр байж болохыг анхаарна уу. Нэмж дурдахад, та серверийн объект үүсгэхийн тулд өгөгдлийн сангийн администратороос олгосон зохих зөвшөөрөлтэй байх ёстой.

Зарим ODBC драйверууд нь DBMS өөрөө дэмждэг байсан ч клиент програмуудаас хадгалагдсан процедурыг дуудахыг дэмждэггүйг анхаарна уу. Гэхдээ энэ тохиолдолд хадгалагдсан процедурыг триггерээс дуудаж болно.

Харагдах байдлаас эхэлж, хадгалагдсан процедурын талаар ярилцаж, триггерүүдийн тоймоор бүлгийг төгсгөе.

Төлөөлөл

Харагдах байдал нь ихэвчлэн нэг буюу хэд хэдэн хүснэгтийн баганын багцыг агуулсан виртуал хүснэгт юм. Бодит байдал дээр харагдац нь өгөгдөл агуулаагүй бөгөөд зөвхөн SELECT төрлийн SQL асуулга бөгөөд энэ харагдац руу хандахдаа ямар өгөгдөл, ямар хүснэгтээс авахыг зааж өгдөг. Энэ үүднээс авч үзвэл харагдац нь хадгалагдсан хайлт юм.

Ихэнх тохиолдолд өгөгдлийн аюулгүй байдлыг хангах үүднээс үзэл баримтлалыг ашигладаг. Жишээлбэл, зарим ангиллын хэрэглэгчид харагдац руу хандах боломжтой боловч өгөгдөл нь үүнийг бүрдүүлдэг хүснэгтэд хандах боломжгүй; Нэмж дурдахад, SQL асуулга нь USER параметрийг (хэрэглэгчийн нэвтэрсэн нэр) агуулж болох бөгөөд энэ тохиолдолд харагдац руу нэвтрэх үед байгаа өгөгдөл нь хэрэглэгчийн нэрээс хамаарна.

Үзэлтийн үндсэн шинж чанарууд нь дараах байдалтай байна.

үзэл бодол нь хүснэгт шиг ажилладаг;
үзэл бодолд өгөгдөл агуулаагүй;
View нь нэгээс олон хүснэгтийн өгөгдлийг ашиглаж болно.

Бид харагдац үүсгэхийн тулд SQL CREATE VIEW мэдэгдлийг, өөрчлөхийн тулд ALTER VIEW мэдэгдлийг, устгахын тулд DROP VIEW мэдэгдлийг ашиглаж болно.

Бид CREATE VIEW мэдэгдлээс эхлэх бөгөөд энэ нь одоогийн мэдээллийн санд харагдац үүсгэх боломжийг олгодог.

CREATE VIEW заалт

Харагдац үүсгэх мэдэгдлийн синтакс нь хэд хэдэн нэмэлт түлхүүр үг бүхий SQL SELECT мэдэгдэлтэй төстэй. Түүний хялбаршуулсан синтаксийг доор харуулав.

ҮЗҮҮЛЭХ_НЭРИЙГ сонгох_мэдэгдэл болгон ҮЗҮҮЛЭХ

View_name аргумент нь харагдацын нэрийг зааж өгдөг. Microsoft SQL Server-д хэрэглэгддэг түлхүүр үг нь CREATE VIEW мэдэгдлийн эх текстийг syscomments хүснэгтэд нуух боломжийг олгодог.

AS түлхүүр үг нь харагдац руу нэвтрэх үед яг ямар SELECT хайлтыг гүйцэтгэхийг зааж өгдөг. Энэ асуулга нь ORDER BY, COMPUTE эсвэл COMPUTE BY, INTO гэсэн түлхүүр үгсийг агуулж болохгүй бөгөөд түр зуурын хүснэгтийг лавлах боломжгүй гэдгийг анхаарна уу.

Өмнө нь үүсгэсэн харагдацыг өөрчлөхийн тулд дараагийн хэсэгт товч тайлбарласан ALTER VIEW заалтыг ашиглана уу.

DROP VIEW санал

Энэ заалт нь мэдээллийн сангаас харагдац устгахад хэрэглэгддэг. Өгөгдлийн сангаас хүснэгтийг устгах үед түүнд хамаарах бүх харагдац мөн устах болно гэдгийг анхаарна уу. Энэ заалтыг ашиглан бид устгах харагдацын нэрийг зааж өгөх ёстой. Харагдах байдлыг устгасны дараа түүний талаарх бүх мэдээлэл системийн хүснэгтээс устгагдана.

Харагдацыг устгах шаардлагатай өөр нэг тохиолдол бол харагдац үүсгэгдсэнээс хойш түүний суурилсан хүснэгтүүдийн бүтэц өөрчлөгдсөн бол. Энэ тохиолдолд та харагдацыг устгаад CREATE VIEW заалтыг ашиглан дахин үүсгэж болно.

Харагдац үүсгэх, ашиглах

CREATE VIEW заалт нь тодорхой шаардлагад нийцсэн өгөгдлийг олж авах боломжийг олгодог үзэл бодлыг бий болгоход хэрэглэгддэг. Харагдац нь одоогийн мэдээллийн санд үүсгэгдэж, тусдаа мэдээллийн сангийн объект болгон хадгалагддаг.

Харагдах байдал үүсгэх хамгийн сайн арга бол SELECT мэдэгдлийг үүсгэж, үүнийг туршсаны дараа CREATE VIEW мэдэгдлийн дутуу хэсгийг нэмэх явдал юм. NorthWind мэдээллийн сан дахь Бүтээгдэхүүнийг Ангилалаар харах эх кодыг харцгаая (Жагсаалт 1).

Тод үсгээр бичсэн эхний мөр нь харагдац үүсгэх SQL хэллэг нь өгөгдлийг сонгох ажлыг гүйцэтгэдэг ердийн SELECT мэдэгдлээс хэрхэн ялгаатай болохыг харуулж байна. Энэ харагдац дахь SELECT заалт нь CATEGORIES хүснэгтээс CategoryName талбар болон PRODUCTS хүснэгтээс ProductName, QuantityPerUnit, UnitsInStock, Continued талбаруудыг гэсэн хоёр хүснэгтээс талбаруудыг сонгоно. Дараа нь хоёр хүснэгтийн өгөгдлийг CategoryID талбарыг ашиглан холбож, зөвхөн нөөцөд байгаа бүтээгдэхүүнүүд (WHERE түлхүүр үгийн дараах шалгуурыг харна уу) өгөгдлийн багцад багтана. Энэ харагдац руу нэвтрэх үр дүнг Зураг дээр үзүүлэв. 1 .

Одоо зүүн бүсийн бүх нутаг дэвсгэрийг харуулсан дүр төрхийг бий болгоё. Энэхүү харагдац нь дараах асуулгад үндэслэсэн болно (Жагсаалт 2).

SELECT заалт нь бидний хүссэн үр дүнг буцаана гэдэгт итгэлтэй болмогц бид CREATE VIEW мэдэгдлийг нэмж, үүсгэж буй харагдацаа EASTTERR гэж нэрлэнэ (Жагсаалт 3).

Үзүүлэнгийн текстийг гараар үүсгэхийн оронд ихэвчлэн DBMS-д багтдаг визуал хэрэгслийг ашиглаж болно. Зураг дээр. Зураг 2-т Microsoft SQL Server-д багтсан Enterprise Manager-ийн нэг хэсэг болох View Designer хэрэглүүрийг ашиглан ижил харагдацыг хэрхэн үүсгэж болохыг харуулж байна.

Харах Дизайнерын дээд хэсэг нь хүснэгтүүд хэрхэн хамааралтай, аль талбарууд харагдацад гарч ирэхийг зааж өгөх боломжийг олгодог. Доор та хүснэгт, талбарын нэрс, тэдгээрийн утгын хязгаарлалт, харуулах аргыг зааж өгч болно. Үзэлтийн эх бичвэр болон түүний гүйцэтгэлийн үр дүнг доор харуулав.

Биднийг дуусгахаас өмнө богино тоймүзэл бодол, тэдгээрийн талаар илүү их мэдээлэл авах талаар бага зэрэг ярилцъя. Microsoft SQL Server 7.0 дээр бид дараах системд хадгалагдсан процедуруудыг ашиглаж болно:

Харах мэдээллийг авахын тулд та sp_help системийн хадгалагдсан процедурыг ашиглаж болно. Жишээ нь, sp_help EastTerr нь шинээр үүсгэсэн харагдацын талаарх мэдээллийг буцаана;
авахын төлөө эх текстүзсэн бол та sp_helptext хадгалагдсан процедурыг ашиглаж болно;
Та харагдац хамаарах хүснэгтүүдийн жагсаалтыг олохын тулд sp_depends системийн хадгалагдсан процедурыг ашиглаж болно;
Та харагдацын нэрийг өөрчлөхийн тулд sp_rename системийн хадгалагдсан процедурыг ашиглаж болно.

IN энэ хэсэгБид тодорхой шалгуурыг хангасан өгөгдлийг олж авахын тулд үзэл бодлыг хэрхэн ашиглах талаар авч үзсэн. Гэсэн хэдий ч сүүлчийн жишээ рүү буцъя. NorthWind мэдээллийн санд дөрвөн бүс байдаг бөгөөд бүх бүс нутгийн нутаг дэвсгэрийг жагсаахад дөрвөн өөр үзэл бодол хэрэгтэй. Хэрэв бид RegionID утгыг параметр болгон дамжуулж чадвал энэ ажлыг хөнгөвчлөх боломжтой. Үүнийг хадгалсан процедурыг ашиглан хийж болох бөгөөд бид дараагийн хэсэгт ярих болно.

Хадгалагдсан процедурууд

Хадгалагдсан процедур нь мэдээллийн санд нэрлэгдсэн объект хэлбэрээр хадгалагдаж, нэг код хэлбэрээр гүйцэтгэгддэг SQL хэллэгүүдийн эмхэтгэсэн багц юм. Хадгалагдсан процедур нь параметрүүдийг хүлээн авч, буцаах боломжтой. Хэрэглэгч хадгалагдсан процедурыг үүсгэх үед сервер үүнийг эмхэтгэж, хуваалцсан кэшэд байршуулдаг бөгөөд үүний дараа хөрвүүлсэн кодыг олон хэрэглэгчид ашиглах боломжтой. Програм нь хадгалагдсан процедурыг ашиглах үед шаардлагатай бол параметрүүдийг түүнд дамжуулдаг бөгөөд сервер нь процедурыг дахин эмхэтгэлгүйгээр гүйцэтгэдэг.

Хадгалагдсан процедур нь програмын гүйцэтгэлийг сайжруулж чадна. Нэгдүгээрт, үйлчлүүлэгчийн програмаас илгээсэн ердийн SQL асуулгатай харьцуулахад тэдгээр нь аль хэдийн хөрвүүлэгдсэн, хадгалагдсан тул гүйцэтгэхэд бэлтгэхэд бага хугацаа шаардагддаг. Хоёрдугаарт, сүлжээний урсгалЭнэ тохиолдолд сүлжээгээр бага өгөгдөл дамжуулдаг тул SQL асуулга дамжуулахтай харьцуулахад бага байна. Цагаан будаа. 3-р зурагт үйлчлүүлэгчийн програмын хадгалагдсан процедур руу залгах үйлдлийг дүрсэлсэн байна.

Нөлөөлөх объектод ямар нэгэн өөрчлөлт орсон тохиолдолд хадгалагдсан процедурууд автоматаар дахин хөрвүүлэгддэг; өөрөөр хэлбэл тэд үргэлж хамааралтай байдаг. Дээр дурьдсанчлан хадгалагдсан процедурууд нь параметрүүдийг хүлээн авч, өөр өөр программуудад оролтын өгөгдлийн янз бүрийн багцыг ашиглан ижил процедурыг ашиглах боломжийг олгодог.

Хадгалагдсан процедурыг ихэвчлэн мэдээллийн лавлагааны бүрэн бүтэн байдлыг хадгалах, бизнесийн дүрмийг хэрэгжүүлэхэд ашигладаг. Сүүлийнх нь нэмэлт уян хатан байдлыг бий болгодог, учир нь бизнесийн дүрэм өөрчлөгдвөл үйлчлүүлэгчийн програмыг өөрчлөхгүйгээр зөвхөн процедурын текстийг өөрчлөх боломжтой.

Процедурыг үүсгэх, өөрчлөх, устгахын тулд тусгай SQL хэллэгүүд байдаг - CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE. Бид тэдгээрийг дараагийн хэсэгт авч үзэх болно.

CREATE PROCEDURE заалт

CREATE PROCEDURE заалт нь хадгалагдсан процедур үүсгэхэд хэрэглэгддэг. Энэ нь дараах хялбаршуулсан синтакстай:

PROC proc_name CREATE [ (@parameter data_type) [= default] ] [...] AS sql_statements

proc_name аргумент нь хадгалагдсан процедурын нэрийг зааж өгдөг бөгөөд энэ нь одоогийн мэдээллийн санд өвөрмөц байх ёстой. @parameter аргумент нь процедурын параметрийг тодорхойлдог. Та нэг буюу хэд хэдэн параметрийг CREATE PROCEDURE зүйлд зааж өгч болно. Хэрэв параметр нь анхдагч утгагүй бол процедурыг дуудах үед хэрэглэгч (эсвэл үйлчлүүлэгчийн програм) дамжуулсан байх ёстой. Microsoft SQL Server 7.0-д хадгалагдсан процедурын параметрийн тоо 1024-ээс хэтрэхгүй байх ёстой; анхдагчаар тэдгээр нь NULL утгатай байж болно.

Гэхдээ өгөгдөлд нэвтрэх зарим ерөнхий механизм нь хадгалагдсан процедурын параметрийн тоонд нэмэлт хязгаарлалт тавьж болохыг анхаарна уу. Жишээлбэл, Oracle 8-ийн BDE драйвер нь зөвхөн параметрийн тоо нь 10-аас хэтрэхгүй процедуртай ажиллах боломжтой.

Өгөгдлийн_төрлийн аргумент нь параметрийн өгөгдлийн төрлийг тодорхойлдог. Өгөгдмөл түлхүүр үгийг анхдагч утгуудыг тохируулахад ашиглаж болно - энэ нь тогтмол эсвэл NULL байж болно. Хэрэв анхдагч утгыг зааж өгсөн бол параметрийн утгыг заахгүйгээр процедурыг дуудаж болно. Хэрэв процедур нь LIKE түлхүүр үгтэй параметрийг ашигладаг бол түүний өгөгдмөл утга нь орлуулагдах тэмдэг (%, _, болон [^]) агуулж болно.

OUTPUT түлхүүр үг нь энэ нь буцах параметр гэдгийг харуулж байна.

AS түлхүүр үг нь серверт зориулсан SQL процедурын өргөтгөл дэх SQL хэллэг болон мэдэгдлийн аль ч тооны хэлбэрээр процедурын гүйцэтгэх ёстой үйлдлийг тодорхойлдог.

CREATE PROCEDURE заалтыг ашиглан үүсгэсэн процедур нь одоогийн мэдээллийн санд хадгалагдах болно. Microsoft SQL Server дээр процедурын нэр нь sysobjects системийн хүснэгтэд, эх текст нь syscomments хүснэгтэд агуулагддаг.

Өмнө нь үүсгэсэн хадгалагдсан процедурыг өөрчлөхийн тулд та дараагийн хэсэгт товч тайлбарласан ALTER PROCEDURE заалтыг ашиглах ёстой.

DOP PROCEDURE санал болгож байна

Энэ заалт нь мэдээллийн сангаас хадгалагдсан процедурыг устгахад ашиглагддаг. DROP PROCEDURE заалт нь хасах процедурын нэр болох нэг аргументыг авдаг.

Хадгалагдсан процедурыг устгах үед түүний талаарх мэдээлэл sysobjects болон syscomments системийн хүснэгтээс хасагдана.

Хадгалагдсан журам үүсгэх, ашиглах

Харах хэсэгт бид NorthWind мэдээллийн баазын дөрвөн бүсээс аль нэгийг сонгохдоо RegionID утгыг агуулсан харагдац руу параметр дамжуулж чадвал тохиромжтой байх болно гэдгийг онцолсон. Бүс нутгийн нутаг дэвсгэрийн жагсаалтыг буцаадаг асуулгыг дахин харцгаая:

Нутаг дэвсгэрээс сонгон нутаг дэвсгэр.TerritoryDescription, Region.RegionDescription.

Өөр бүс нутгийг сонгохын тулд бид асуулгын сүүлчийн мөрөнд байгаа WHERE заалтын нөхцөлийг өөрчлөх шаардлагатай. Тиймээс хэрэв бид хувьсагчийг (үүнийг RegID гэж нэрлэе) ашиглавал асуулгын бусад хэсгийг өөрчлөхгүйгээр дөрвөн бүсээс аль нэгийг нь сонгож болно.

NorthWind мэдээллийн санд 1-ээс 4 хүртэл дугаарлагдсан дөрвөн бүс байдаг. Энэ нь RegID хувьсагч нь бүхэл тоо байх ёстой гэсэн үг юм. Хадгалагдсан процедурын кодыг доор харуулав.

ЖУРАМ ҮЗҮҮЛЭХ ShowRegion @RegID int Нутаг дэвсгэр.TerritoryDescription, Region.RegionDescription FROM Territories.Territories.TerritoryDescription, Region.RegionDescription.

Бид SELECT асуулгын бараг бүх текстийг хэвээр үлдээж (налуу үсгээр бичсэн) зөвхөн шинээр үүсгэсэн хадгалагдсан процедурын нэр (эхний мөрөнд), параметрийн мэдэгдэл (хоёр дахь мөрөнд) бүхий CREATE PROCEDURE заалтыг нэмсэн болохыг анхаарна уу. AS түлхүүр үг нь өгүүлбэрийн эхэнд үнэхээр үйлдэл хийдэг.

RegID =2-д зориулсан SQL Server Query Analyzer дээр үүсгэсэн процедурыг гүйцэтгэсний үр дүнг Зураг дээр үзүүлэв. 3.

Мэдээжийн хэрэг, бид хадгалагдсан процедурыг зөвхөн үзвэрийн дэвшилтэт хувилбарууд эсвэл ухаалаг SELECT асуулгад ашиглахаас илүүтэйгээр ашиглаж болно. Хадгалагдсан процедур нь ердийн олон ажлыг автоматжуулах боломжийг олгодог механизмуудыг өгдөг.

Microsoft SQL Server 7.0 дээр бид ердийн хадгалсан процедуртай ажиллахын тулд системийн хадгалсан процедурыг ашиглаж болно:

sp_stored_procedures - хадгалагдсан процедурын жагсаалтыг харуулна;
sp_helptext - хадгалагдсан процедурын эх текстийг харуулна;
sp_depends - хадгалагдсан процедурын хамаарлын талаарх мэдээллийг харуулдаг;
sp_procoption - хадгалагдсан процедурын сонголтыг тохируулах эсвэл тохируулах;
sp_recompile - дараагийн дуудлагын үед процедурыг дахин эмхэтгэдэг;
sp_rename - процедурын нэрийг өөрчилдөг.

Системд хадгалагдсан журам

Бид Microsoft SQL Server-ийн тухай ярьж байгаа тул түүнд хэрэгжсэн маш олон тооны системийн хадгалагдсан процедурыг тэмдэглэх нь зүйтэй. Системд хадгалагдсан процедурын нэрс нь SP_ эсвэл XP_-ээс эхэлж, мастер мэдээллийн санд хадгалагдана. Бид дээр дурдсан түгээмэл хэрэглэгддэг системийн хадгалагдсан процедуруудын заримыг аль хэдийн тайлбарласан.

Өдөөгч нь хэрэглэгчдэд өгөгдөл буцааж өгөх ёсгүйг анхаарна уу.

CREATE TRIGGER заалтад ашиглаж болох хоёр тусгай хүснэгт байдаг. Жишээлбэл, устгасан болон оруулсан хүснэгтүүд нь гохыг тодорхойлсон хүснэгттэй ижил бүтэцтэй бөгөөд хэрэглэгчийн өөрчилсөн бичлэгийн хуучин болон шинэ утгыг агуулна. Жишээлбэл, устгасан бичлэгүүдийг олохын тулд бид дараах SQL мэдэгдлийг ашиглаж болно.

SELECT * FROM устгагдсан

Хүснэгтэнд Зураг 3-т бүх боломжит өгөгдлийн өөрчлөлтийг устгасан болон оруулсан хүснэгтүүдийн агуулгыг харуулав.

Одоо байгаа триггерийг өөрчлөхийн тулд ALTER TRIGGER заалтыг ашиглана уу. Бид дараагийн хэсэгт энэ тухай ярих болно.

Эхлээд бид энэ мэдээллийг агуулсан хоёр шинэ талбарыг хүснэгтэд нэмэх хэрэгтэй. Тэднийг UpdatedBy (сүүлд бичлэгийг шинэчилсэн менежерийн нэр) болон UpdatedWhen (бичлэг өөрчлөгдсөн цаг) гэж нэрлэе. Дараа нь KeepTrack нэртэй триггер үүсгэцгээе. Түүний код энд байна:

ТРИГГЕР ҮЗҮҮЛЭХ Хэрэглэгчид ОРУУЛАХ, ШИНЭЧЛЭХ Хэрэглэгчийн Тохиргоонд Шинэчлэх

Триггерийн эх кодоос харахад энэ нь Customers хүснэгт дээрх INSERT болон UPDATE үйлдэл бүрийн дараа хийгддэг. Энэ триггер нь Customers.UpdatedBy талбарт менежерийн (өгөгдлийн сангийн хэрэглэгч) нэрийг, Customers.UpdatedWhen талбарт өөрчлөлтийн огноо, цагийг хадгална. Энэ өгөгдлийг оруулсан түр хүснэгтээс гаргаж авсан.

Таны харж байгаагаар энэ триггер нь хүснэгтэд өөрчлөлт оруулах, шинэ бичлэг оруулахыг хянах боломжийг танд олгоно.

Бид триггерүүдийн товч тоймыг дуусгахаасаа өмнө боломжтой триггерүүдийн талаарх мэдээллийг хаанаас олж болохыг хэлэх хэрэгтэй. Sysobjects хүснэгт нь триггер болон тэдгээрийн төрлүүдийн талаарх мэдээллийг хадгалдаг бөгөөд syscomments хүснэгтэд тэдгээрийн эх текстийг агуулна.

Дүгнэлт

Энэ хэсэгт бид хэд хэдэн төрлийн өгөгдлийн сангийн объектуудыг авч үзсэн - хадгалагдсан процедур, үзэл бодол, триггер. Бид дараахь зүйлийг сурсан.

Харагдах байдал нь ихэвчлэн нэг буюу хэд хэдэн хүснэгтийн баганын дэд багц хэлбэрээр бүтээгдсэн виртуал хүснэгт юм. CREATE VIEW өгүүлбэр нь харагдац үүсгэхэд, ALTER VIEW заалтыг өөрчлөхөд, DROP VIEW заалтыг устгахад ашиглагддаг.
Хадгалагдсан процедур нь мэдээллийн санд нэрлэгдсэн объект хэлбэрээр хадгалагдаж, нэг код хэлбэрээр гүйцэтгэгддэг SQL хэллэгүүдийн эмхэтгэсэн багц юм. CREATE PROCEDURE заалт нь хадгалагдсан процедурыг үүсгэхэд, ALTER PROCEDURE-ийг өөрчлөхөд, DROP PROCEDURE-ийг устгахад ашиглагддаг.
Триггер нь INSERT, DELETE, эсвэл UPDATE SQL хэллэгийг ашиглан тодорхой хүснэгтэд өгөгдлийг нэмэх, устгах, өөрчлөх үед автоматаар дуудагддаг хадгалагдсан процедурын тусгай төрөл юм. Триггерийг CREATE TRIGGER заалтыг ашиглан үүсгэнэ. Триггерийг өөрчлөхийн тулд ALTER TRIGGER заалтыг, устгахын тулд DROP TRIGGER заалтыг ашиглана уу.

ComputerPress 12"2000

Ажлын зорилго– өгөгдлийн сангийн сервер дээр хадгалагдсан процедур үүсгэж, ашиглаж сурах.

1. Бүх жишээн дээр ажиллаж, тэдгээрийн гүйцэтгэлийн үр дүнг SQL Server Management Studio хэрэглүүрт дүн шинжилгээ хийнэ үү. Одоогийн мэдээллийн санд үүсгэсэн процедур байгаа эсэхийг шалгаж байна.

2. Лабораторийн ажлын явцад бүх жишээ, даалгавруудыг гүйцэтгэх.

3. Сонголтуудын дагуу бие даасан даалгавруудыг гүйцэтгэх.

Ажил гүйцэтгэх талаархи тайлбар

Хадгалагдсан процедурын програмчлалыг эзэмшихийн тулд бид жишээ мэдээллийн санг ашигладаг DB_Books 1-р лабораторийн ажилд бий болсон . Жишээ, даалгавруудыг гүйцэтгэхдээ мэдээллийн сан, хүснэгт болон бусад төслийн объектуудын нэрсийн нийцлийг анхаарч үзээрэй.

Хадгалагдсан процедурууднь нэг буюу хэд хэдэн SQL хэллэг эсвэл функцээс бүрдэх командуудын багц бөгөөд эмхэтгэсэн хэлбэрээр мэдээллийн санд хадгалагддаг.

Хадгалагдсан процедурын төрлүүд

Системд хадгалагдсан процедурууд нь янз бүрийн захиргааны үйлдлийг гүйцэтгэхэд зориулагдсан байдаг. Бараг бүх серверийн удирдлагын үйл ажиллагааг тэдний тусламжтайгаар гүйцэтгэдэг. Системийн хадгалагдсан процедурууд нь системийн хүснэгтүүдтэй ажиллах боломжийг олгодог интерфейс гэж бид хэлж чадна. Системд хадгалагдсан процедурууд нь sp_-ийн угтвартай, системийн мэдээллийн санд хадгалагддаг бөгөөд өөр ямар ч өгөгдлийн сангийн контекстоор дуудагдах боломжтой.

Захиалгат хадгалсан процедур нь тодорхой үйлдлүүдийг гүйцэтгэдэг. Хадгалагдсан процедур нь мэдээллийн сангийн бүрэн хэмжээний объект юм. Үүний үр дүнд хадгалагдсан процедур бүр нь тодорхой өгөгдлийн санд байрладаг бөгөөд үүнийг гүйцэтгэдэг.

Түр хадгалагдсан процедурууд нь зөвхөн богино хугацаанд байдаг бөгөөд дараа нь сервер автоматаар устгадаг. Тэдгээрийг орон нутгийн болон дэлхийн гэж хуваадаг. Орон нутгийн түр хадгалагдсан процедурыг зөвхөн тэдгээрийн үүсгэсэн холболтоос л дуудаж болно. Ийм журам үүсгэхдээ та түүнд нэг # тэмдэгтээр эхэлсэн нэр өгөх ёстой. Бүх түр зуурын объектуудын нэгэн адил хэрэглэгч салгах эсвэл серверийг дахин эхлүүлэх эсвэл зогсоох үед энэ төрлийн хадгалагдсан процедурууд автоматаар устдаг. Глобал түр хадгалагдсан процедурыг ижил процедуртай серверээс ямар ч холболт хийх боломжтой. Үүнийг тодорхойлохын тулд ## тэмдэгтээр эхэлсэн нэр өгөхөд л хангалттай. Эдгээр процедур нь серверийг дахин эхлүүлэх эсвэл зогсоох, эсвэл үүсгэсэн контекст дэх холболтыг хаах үед устгагдана.

Хадгалагдсан процедурыг үүсгэх, өөрчлөх

Хадгалагдсан процедурыг бий болгох нь дараах асуудлуудыг шийдвэрлэхэд хамаарна: хандалтын эрхийг төлөвлөх. Хадгалагдсан процедурыг үүсгэх үед энэ нь түүнийг үүсгэсэн хэрэглэгчийн адил мэдээллийн сангийн объектод хандах эрхтэй байх болно гэдгийг анхаарна уу; хадгалагдсан процедурын параметрүүдийг тодорхойлох, хадгалагдсан процедур нь оролт, гаралтын параметртэй байж болно; хадгалагдсан процедурын код боловсруулах. Процедурын код нь бусад хадгалагдсан процедур руу залгах гэх мэт ямар ч SQL командын дарааллыг агуулж болно.

MS SQL Server тэмдэглэгээнд шинэ хадгалагдсан процедурыг үүсгэх эсвэл өөрчлөх операторын синтакс:

( CREATE | ALTER ) PROC[ EDURE] procedure_name [ ;number] [ ( @parameter_name data_type ) [ VARYING ] [ = FAULT ] [ OUTPUT ] ] [ ,... n] [ WITH ( ДАХИН ЭРГҮҮЛЭХ | ШИФРЛҮҮЛЭХ [ ХОЛБООТОЙ] AS sql_statement [ ... n]

Энэ командын параметрүүдийг харцгаая.

sp_, #, ## угтваруудыг ашиглан үүсгэсэн процедурыг систем эсвэл түр зуурын гэж тодорхойлж болно. Командын синтаксаас харахад үүсгэсэн процедурыг эзэмших эзэмшигчийн нэр, түүнчлэн түүнийг байрлуулах мэдээллийн сангийн нэрийг зааж өгөхийг хориглоно. Иймд үүсгэж буй хадгалагдсан процедурыг тодорхой мэдээллийн санд байрлуулахын тулд тухайн мэдээллийн сангийн контекст дээр CREATE PROCEDURE командыг өгөх ёстой. Хадгалсан процедурын үндсэн хэсгээс ижил мэдээллийн сангийн объектуудад хандахдаа мэдээллийн сангийн нэрийг зааж өгөхгүйгээр товчилсон нэрийг ашиглаж болно. Бусад өгөгдлийн санд байгаа объектуудад хандах шаардлагатай бол мэдээллийн сангийн нэрийг зааж өгөх шаардлагатай.

Оролтын болон гаралтын өгөгдлийг дамжуулахын тулд таны үүсгэсэн хадгалагдсан процедур дахь параметрийн нэр нь @ тэмдэгтээр эхлэх ёстой. Та таслалаар тусгаарлагдсан нэг хадгалсан процедурт олон параметрүүдийг зааж өгч болно. Процедурын үндсэн хэсэгт нэр нь энэ процедурын параметрүүдийн нэртэй давхцаж байгаа локал хувьсагчийг ашиглах ёсгүй. Хадгалагдсан процедурын параметрийн өгөгдлийн төрлийг тодорхойлохын тулд ямар ч төрлүүд тохиромжтой SQL өгөгдөл, үүнд хэрэглэгчийн тодорхойлсон. Гэсэн хэдий ч CURSOR өгөгдлийн төрлийг зөвхөн хадгалагдсан процедурын гаралтын параметр болгон ашиглаж болно, i.e. OUTPUT түлхүүр үгийг зааж өгч байна.

OUTPUT түлхүүр үг байгаа нь харгалзах параметр нь хадгалагдсан процедураас өгөгдлийг буцаах зорилготой гэсэн үг юм. Гэсэн хэдий ч энэ нь параметр нь хадгалагдсан процедурт утгыг дамжуулахад тохиромжгүй гэсэн үг биш юм. OUTPUT түлхүүр үгийг зааж өгснөөр серверт хадгалагдсан процедураас гарахдаа тухайн процедурыг дуудах үед параметрийн утга болгон тодорхойлсон локал хувьсагчид одоогийн параметрийн утгыг оноож өгөхийг заадаг. OUTPUT түлхүүр үгийг зааж өгөхдөө процедурыг дуудах үед тохирох параметрийн утгыг зөвхөн локал хувьсагч ашиглан тохируулах боломжтой гэдгийг анхаарна уу. Ердийн параметрүүдэд зөвшөөрөгдсөн аливаа илэрхийлэл эсвэл тогтмолыг зөвшөөрөхгүй. VARYING түлхүүр үг нь CURSOR төрлийн OUTPUT параметртэй хамт хэрэглэгддэг. Энэ нь гаралт нь үр дүнгийн багц байх болно гэдгийг тодорхойлдог.

DEFAULT түлхүүр үг нь харгалзах параметрийн анхдагчаар авах утгыг илэрхийлнэ. Тиймээс процедурыг дуудахдаа харгалзах параметрийн утгыг тодорхой зааж өгөх шаардлагагүй болно.

Сервер нь асуулгын гүйцэтгэлийн төлөвлөгөө болон хөрвүүлсэн кодыг кэш болгодог тул дараагийн удаа процедурыг дуудах үед бэлэн утгуудыг ашиглах болно. Гэсэн хэдий ч зарим тохиолдолд процедурын кодыг дахин эмхэтгэх шаардлагатай хэвээр байна. RECOMPILE түлхүүр үгийг зааж өгснөөр системд хадгалагдсан процедурыг дуудах бүрд нь гүйцэтгэх төлөвлөгөөг бий болгох заавар өгдөг.

Өгөгдлийг хуулбарлах, үүсгэсэн хадгалагдсан процедурыг нийтлэлд нийтлэл болгон оруулахад FOR REPLICATION параметр шаардлагатай. ENCRYPTION түлхүүр үг нь серверт хадгалагдсан процедурын кодыг шифрлэх заавар өгдөг бөгөөд энэ нь хадгалагдсан процедурыг хэрэгжүүлдэг хувийн алгоритмуудыг ашиглахаас хамгаалж чадна. AS түлхүүр үгийг хадгалсан процедурын эхэнд байрлуулна. Процедурын бие нь бараг бүх SQL командыг ашиглах, гүйлгээг зарлах, түгжээ тогтоох, бусад хадгалагдсан процедурыг дуудах боломжтой. Та RETURN командыг ашиглан хадгалагдсан процедураас гарч болно.

Хадгалагдсан процедурыг устгах

DROP PROCEDURE (процедурын_нэр) [,... n]

Хадгалагдсан процедурыг гүйцэтгэх

Хадгалсан процедурыг гүйцэтгэхийн тулд дараах тушаалыг ашиглана: [ [ EXEC [ UTE] процедурын_нэр [ ;тоо] [ [ @parameter_name= ] ( утга | @ хувьсагч_нэр) [ OUTPUT ] | [ өгөгдмөл ] ] [ ,... n]

Хэрэв хадгалагдсан процедурын дуудлага нь багц дахь цорын ганц команд биш бол EXECUTE команд шаардлагатай. Түүнчлэн, энэ команд нь өөр процедур эсвэл триггерийн биеэс процедурыг дуудах шаардлагатай.

Процедурыг дуудахдаа OUTPUT түлхүүр үгийг ашиглахыг зөвхөн OUTPUT түлхүүр үгээр процедур үүсгэх үед зарласан параметрүүдэд зөвшөөрнө.

Процедурыг дуудах үед параметрийн хувьд DEFAULT түлхүүр үгийг зааж өгсөн тохиолдолд анхдагч утгыг ашиглана. Мэдээжийн хэрэг, заасан DEFAULT үгийг зөвхөн анхдагч утгыг тодорхойлсон параметрүүдэд зөвшөөрдөг.

EXECUTE командын синтакс нь процедурыг дуудах үед параметрийн нэрийг орхигдуулж болохыг харуулж байна. Гэсэн хэдий ч, энэ тохиолдолд хэрэглэгч параметрийн утгыг процедурыг үүсгэх үед жагсаасан дарааллаар нь зааж өгөх ёстой. Тооцооллын явцад үүнийг орхигдуулах замаар параметрт өгөгдмөл утгыг оноож болохгүй. Хэрэв та анхдагч утгатай параметрүүдийг орхихыг хүсвэл хадгалагдсан процедурыг дуудахдаа параметрийн нэрийг тодорхой зааж өгөхөд хангалттай. Үүнээс гадна, ийм байдлаар та параметрүүд болон тэдгээрийн утгыг дурын дарааллаар жагсааж болно.

Процедурыг дуудахдаа утга бүхий параметрийн нэр эсвэл зөвхөн параметрийн нэргүй утгуудыг зааж өгөхийг анхаарна уу. Тэдгээрийг хослуулахыг зөвшөөрдөггүй.

Хадгалсан процедурт RETURN ашиглах

Тодорхой нөхцлийн дагуу процедураас аль ч үед гарах боломжийг олгохоос гадна процедурын үр дүнг тоо болгон дамжуулах боломжийг олгодог бөгөөд энэ нь процедурын чанар, зөв эсэхийг дүгнэх боломжийг олгоно. Параметргүй процедур үүсгэх жишээ:

Books GO-с ТООЛОН_НОМЫГ СОНГОЖ ТООЛОХ (Код_ном) ЖУРАМ ҮҮСГЭХ

Дасгал 1.

EXEC-н ном

Үр дүнг шалгана уу.

Оролтын параметр бүхий процедур үүсгэх жишээ:

Count_Books_Pages @Count_pages ЖУРАМ ҮҮСГЭХ Хуудас ХААНА Номноос СОНГОХ ТООЛ (Код_ном) ХАЙХ ЗААВАР ТОВЧЛОЛУУД ХУДАЛДАА.

Даалгавар 2. Үүсгэх энэ журам DB_Books мэдээллийн сангийн Хадгалагдсан журам хэсэгт SQL серверийн Management Studio хэрэгслээр дамжуулан. Үүнийг командыг ашиглан ажиллуулна уу

EXEC-ийн_тоо_ном_хуудас 100

Үр дүнг шалгана уу.

Оролтын параметр бүхий процедур үүсгэх жишээ:

ЖУРАМ ҮҮСГЭЭРЭЙ Count_Books_Title @Count_pages AS INT , @Title AS CHAR (10 ) AS СОНГОХ ТООН (Код_ном) FROM WHERE Pages>= @To_pages , Title_book LIKE @Title GO

Даалгавар 3. SQL server Management Studio хэрэглүүрийг ашиглан DB_Books мэдээллийн сангийн Хадгалагдсан журам хэсэгт энэ процедурыг үүсгэ. Үүнийг командыг ашиглан ажиллуулна уу

EXEC Номын_тоо_гарчиг 100 , "P%"

Үр дүнг шалгана уу.

Оролтын параметр ба гаралтын параметр бүхий процедурыг үүсгэх жишээ:

ЖУРАМ ҮҮСГЭХ Count_Books_Itogo @Count_pages INT , @Title CHAR (10 ) , @Itogo INT OUTPUT AS SELECT @Itogo = COUNT (Code_book) FROM WHERE Pages>= @Count_pages AND Title_book LIKE @TitleGO

Даалгавар 4. SQL server Management Studio хэрэглүүрийг ашиглан DB_Books мэдээллийн сангийн Хадгалагдсан журам хэсэгт энэ процедурыг үүсгэ. Командын багцыг ашиглан ажиллуулна уу:

Sql> @q-г int EXEC Count_Books_Itogo 100, "P%" гэж зарлах, @q гаралтыг сонгох @q

Үр дүнг шалгана уу.

Оролтын параметрүүд болон RETURN бүхий процедурыг үүсгэх жишээ:

CREATE PROCEDURE checkname @param INT ГЭЖ БАЙНА (Нэр_зохиогчийн ХААНА зохиогчдоос СОНГОХ Code_author = @param) = "Пушкин А.С." 1-ийг БУЦАХ БУЦАХ 2

Даалгавар 5. SQL server Management Studio хэрэглүүрийг ашиглан DB_Books мэдээллийн сангийн Хадгалагдсан журам хэсэгт энэ процедурыг үүсгэ. Үүнийг дараах тушаалуудыг ашиглан ажиллуулна уу.

@return_status МЭДЭГДЭХ INT EXEC @return_status = шалгах нэр 1 "Буцах төлөв"-ийг сонгох = @return_status

Худалдан авалтын хүснэгтийн түлхүүр талбарын утгыг 2 дахин нэмэгдүүлэх параметргүй процедурыг үүсгэх жишээ:

PROC update_proc-ийг ШИНЭЧЛЭГДСЭН БОЛГОХ Худалдан авалтын багц код_худалдан авалт = Кодын_худалдан авалт* 2

Даалгавар 6. SQL server Management Studio хэрэглүүрийг ашиглан DB_Books мэдээллийн сангийн Хадгалагдсан журам хэсэгт энэ процедурыг үүсгэ. Үүнийг командыг ашиглан ажиллуулна уу

EXEC update_proc

Тодорхой зохиогчийн талаархи бүх мэдээллийг олж авах оролтын параметр бүхий процедурын жишээ:

PROC сонгох_author @k CHAR (30 ) СОНГОХ БОЛОМЖТОЙ * Зохиогчид ХААНА нэр_author= @k

Даалгавар 7.

EXEC select_author "Пушкин А.С." or select_author @k= "Пушкин А.С." эсвэл EXEC select_author @k= "Пушкин А.С."

Худалдан авалтын хүснэгтийн түлхүүр талбарын утгыг заасан тоогоор (анхдагчаар 2 удаа) нэмэгдүүлэхийн тулд оролтын параметр болон анхдагч утга бүхий процедурыг үүсгэх жишээ:

PROC update_proc ҮҮСГЭХ @p INT = 2 ШИНЭЧЛЭГДСЭН Худалдан авалтууд SET Code_purchase = Code_purchase * @p

Уг процедур нь ямар ч өгөгдөл буцаахгүй.

Даалгавар 8. SQL server Management Studio хэрэглүүрийг ашиглан DB_Books мэдээллийн сангийн Хадгалагдсан журам хэсэгт энэ процедурыг үүсгэ. Үүнийг дараах тушаалуудыг ашиглан ажиллуулна уу.

EXEC update_proc 4 эсвэл EXEC update_proc @p = 4 эсвэл EXEC update_proc --өгөгдмөл утгыг ашиглана.

Оролт, гаралтын параметрүүдтэй процедур үүсгэх жишээ. Тодорхой хугацаанд гүйцэтгэсэн захиалгын тоог тодорхойлох журам үүсгэ.

@d1 БА @d2 ХОТЫН ОДОО_захиалга @c = ISNULL(@c, 0) ХЭРЭГСЭЛТЭЙ Худалдан авалтаас СОНГОХ @c= COUNT (Код_худалдан авалт) PROC-ийн_худалдан авалтын_тооцоог @d1 SMALLDATETIME, @d2 SMALLDATETIME, @c ОРОЛЦООНЫ ГАРЦЫГ ҮҮСГЭ.

Даалгавар 9. SQL server Management Studio хэрэглүүрийг ашиглан DB_Books мэдээллийн сангийн Хадгалагдсан журам хэсэгт энэ процедурыг үүсгэ. Үүнийг дараах тушаалуудыг ашиглан ажиллуулна уу.

'01-6-2006 ', '01-7-р сарын 2006 ', @c2 OUTPUT SELECT @c2-д @c2 INT EXEC-ийн худалдан авалтын тоог МЭДЭГЛЭХ

Даалгаврын сонголтууд лабораторийн ажил №4

Ерөнхий заалтууд. SQL Server Management Studio дээр үүсгэнэ үү шинэ хуудаскодын хувьд ("Хүсэлт үүсгэх" товч). Үүсгэсэн DB_Books мэдээллийн санг Use мэдэгдлийг ашиглан программчлан идэвхжүүлнэ. Операторуудыг ашиглан хадгалсан процедурыг үүсгэх Процедур үүсгэх, мөн журмын нэрийг бие даан тодорхойлох. Процедур бүр нь хоёр дахь лабораторид гүйцэтгэсэн нэг SQL хайлтыг гүйцэтгэнэ. Түүнчлэн хайлт хийхэд ашигласан талбаруудын утгыг дамжуулахын тулд асуулгын SQL кодыг өөрчлөх шаардлагатай.

Жишээлбэл, 2-р лабораторийн ажлын эхний даалгавар, хүсэлт:

/*Нийлүүлэгчийн лавлахаас (Хүргэлтийн хүснэгт) компанийн нэр (Нэр_компанийн талбар) нь “OJSC MIR” болох компанийн нэр, утасны дугаар, INN (Нэр_компани, Утас ба INN талбар)-ыг сонгоно уу.

Нэр_компани, утас, ИНН-ийг ХААНА хүргэлтээс СОНГОХ Нэр_компани = "OJSC MIR"

*/ – Энэ ажилд дараах журам бий болно.

Нэр_компани @comp CHAR (30 ) Нэр_компани, Утас, INN НЭР ХААНА хүргэлтээс СОНГОХ БОЛОВСРУУЛАХ Нэр_компани = @comp.

- Процедурыг эхлүүлэхийн тулд дараах тушаалыг ашиглана уу.

EXEC сонгох_нэр_компани "JSC MIR"

Ажлын жагсаалт

SQL Server Management Studio дээр шинэ програм үүсгэ. 1-р лабораторийн ажилд үүсгэсэн мэдээллийн санг Use командыг ашиглан программчлан идэвхжүүлнэ. Create процедурын мэдэгдлийг ашиглан хадгалагдсан процедурыг үүсгэж, процедурын нэрийг өөрөө тодорхойлно уу. Процедур бүр нь сонголтуудын дагуу тусдаа даалгавруудын хэлбэрээр харуулсан нэг SQL хайлтыг гүйцэтгэх болно.

Сонголт 1

1. Дор хаяж нэг хүүхэдтэй ажилчдын жагсаалтыг харуулах.

2. Заасан хугацаанд бэлэг авсан хүүхдүүдийн жагсаалтыг харуулах.

3. Насанд хүрээгүй хүүхэдтэй эцэг эхийн жагсаалтыг харуулах.

4. Заасан тооноос их үнэ бүхий бэлгийн талаарх мэдээллийг огноогоор нь ангилан харуулах.

Сонголт 2

1. Заасан төрлийн төхөөрөмжүүдийн жагсаалтыг харуулах.

2. Зассан техникчээс засварласан төхөөрөмжийн тоо болон засварын нийт зардлыг харуулах.

3. Төхөөрөмж эзэмшигчдийн жагсаалт болон тэдгээрийн хүсэлтийн тоог буурах дарааллаар эрэмбэлэн харуулах.

4. Заасан тооноос дээш зэрэгтэй, эсвэл заасан хугацаанаас бага ажилд авсан урчуудын талаарх мэдээллийг харуулах.

Сонголт 3

2. Заасан тооноос илүү үнээр цэцэг зарсан борлуулалтын кодын жагсаалтыг харуулах.

3. Заасан борлуулалтын кодын дагуу борлуулсан огноо, үнийн дүн, худалдагч, цэцэг зэргийг харуулна.

4. Заасан тооноос их өндөртэй эсвэл цэцэглэж буй цэцэгсийн жагсаалт, сортыг харуул.

Сонголт 4

1. Хэрэглэх заалттай эмийн жагсаалтыг харуулах.

2. Ижил нэртэй эмийг заасан тооноос илүү хугацаанд борлуулсан хүргэлтийн огнооны жагсаалтыг харуулах.

3. Хүргэлтийн хугацаа, үнийн дүн, ханган нийлүүлэгчээс менежерийн овог нэр, эмийн нэрийг заасан тооноос илүү баримтын кодоор харуулах.

Сонголт 5

2. Тодорхой шалтгааны улмаас ашиглалтаас гарсан тоног төхөөрөмжийн жагсаалтыг харуулах.

3. Заасан хугацаанд хасагдсан тоног төхөөрөмжийг хүлээн авсан огноо, тоног төхөөрөмжийн нэр, хариуцах хүний овог нэр, данснаас хассан огноог харуулна.

4. Заасан төрлийн буюу хүлээн авсан огноо нь тодорхой утгаас их байгаа тоног төхөөрөмжийн жагсаалтыг харуулах

Сонголт 6

1. Заасан тооноос их жинтэй аяга тавагны жагсаалтыг харуулах.

2. Нэр нь заасан үгийн фрагмент агуулсан бүтээгдэхүүний жагсаалтыг харуулах.

3. Бүтээгдэхүүний хэмжээ, тавагны нэр, бүтээгдэхүүний нэр, тавагны кодыг заасан хэмжээнээс харуулах анхны утгатодорхой эцсийн үнэ цэнээр.

4. Нүүрс ус нь тодорхой хэмжээнээс их буюу илчлэгийн хэмжээ нь тогтоосон хэмжээнээс их байгаа таваг бэлтгэх дараалал болон хоолны нэрийг дэлгэцэнд үзүүлнэ.

Сонголт 7

1. Заасан албан тушаалтай ажилчдын жагсаалтыг харуулах.

3. Заасан хугацаанд бүртгүүлсэн баримтын бүртгэлийн огноо, баримт бичгийн төрөл, бүртгэгчийн овог нэр, байгууллагын нэрийг харуулна.

4. Бүртгэгдсэн баримт бичгийн жагсаалтыг тодорхой баримт бичгийн төрөл эсвэл заасан хэмжээнээс их бүртгэлийн огноотой харуулах.

Сонголт 8

1. Ажлаас халах тодорхой шалтгаан бүхий ажилчдын жагсаалтыг харуулах.

3. Бүртгүүлсэн огноо, ажлаас халагдсан шалтгаан, ажилтны овог нэрийг заасан хугацаанд бүртгүүлсэн баримт бичгүүдийг харуулах.

Сонголт 9

1. Заасан төрлийн чөлөө авсан ажилчдын жагсаалтыг харуулах.

2. Заасан хугацаанд бүртгэлийн огноо бүхий баримт бичгийн жагсаалтыг харуулах.

3. Заасан хугацаанд бүртгүүлсэн баримт бичгүүдэд бүртгүүлсэн огноо, амралтын төрөл, ажилтны овог нэрийг харуулах.

4. Бүртгэгдсэн баримт бичгийн жагсаалтыг заасан мужид баримт бичгийн кодтой харуулна.

Сонголт 10

1. Заасан албан тушаалтай ажилчдын жагсаалтыг харуулах.

2. Агуулга нь заасан үгийн фрагмент агуулсан баримт бичгийн жагсаалтыг харуулах.

3. Заасан хугацаанд бүртгүүлсэн баримтын бүртгэлийн огноо, баримт бичгийн төрөл, илгээгчийн овог нэр, байгууллагын нэрийг харуулна.

4. Бүртгэгдсэн баримт бичгийн жагсаалтыг заасан баримт бичгийн төрөл эсвэл тодорхой утгаас бага баримт бичгийн кодтой харуулах.

Сонголт 11

1. Заасан албан тушаалд томилогдсон ажилчдын жагсаалтыг харуулах.

2. Заасан хугацаанд бүртгэлийн огноо бүхий баримт бичгийн жагсаалтыг харуулах.

3. Заасан хугацаанд бүртгүүлсэн баримт бичгийн бүртгэлийн огноо, албан тушаал, ажилтны овог нэрийг харуулах.

4. Бүртгэгдсэн баримт бичгийн жагсаалтыг заасан мужид баримт бичгийн кодтой харуулна.

Сонголт 12

3. Тоног төхөөрөмж түрээсэлсэн хүмүүсийн нэрсийн жагсаалт болон тэдний хүсэлтийн тоог хүсэлтийн тоогоор нь буурах дарааллаар эрэмбэлж харуулна.

Сонголт 13

1. Заасан төрлийн тоног төхөөрөмжийн жагсаалтыг харуулах. 2. Тодорхой ажилтнаас хасагдсан тоног төхөөрөмжийн жагсаалтыг харуулах.

3. Ашиглалтаас хасагдсан тоног төхөөрөмжийн хэмжээг тоног төхөөрөмжийн төрлөөр бүлэглэн харуулах.

4. Тодорхой огнооноос дээш ажилд авсан ажилчдын талаарх мэдээллийг харуулах.

Сонголт 14

1. Заасан навчны төрлөөр цэцэгсийн жагсаалтыг хэвлэ.

2. Тодорхой үнийн дүнгээс илүү үнээр цэцэг худалдсан баримтын кодын жагсаалтыг харуулах.

3. Хүлээн авсан огноо, дүн, нийлүүлэгчийн нэр, өнгө зэргийг тодорхой нийлүүлэгчийн кодоор харуулна.

4. Тодорхой тооноос дээш өндөртэй эсвэл цэцэглэж буй цэцэгсийн жагсаалт, сортыг харуул.

Сонголт 15

1. Заасан хугацаанд өрөөнд орж ирсэн үйлчлүүлэгчдийн жагсаалтыг харуулах.

2. Үйлчлүүлэгч бүрийн өрөөнүүдийн төлбөрийн нийт дүнг харуулах.

3. Заасан хугацаанд бүртгүүлсэн үйлчлүүлэгчдийн ирэх огноо, өрөөний төрөл, овог нэр зэргийг харуулна.

4. Тодорхой төрлийн өрөөнд бүртгүүлсэн үйлчлүүлэгчдийн жагсаалтыг харуулах.

Сонголт 16

1. Заасан төрлийн тоног төхөөрөмжийн жагсаалтыг харуулах.

2. Тодорхой үйлчлүүлэгчийн түрээсэлсэн тоног төхөөрөмжийн жагсаалтыг харуулах.

4. Үйлчлүүлэгчдийн талаарх мэдээллийг хаягаар эрэмбэлсэн харуулах.

Сонголт 17

1. Худалдан авах үнэ нь тодорхой үнэ цэнээс дээш эсвэл баталгаат хугацаа нь заасан тооноос дээш үнэтэй үнэт зүйлсийн жагсаалтыг харуулах.

2. Нэр нь заасан үг агуулсан материаллаг хөрөнгийн байршлын жагсаалтыг харуулах.

3. Утгын утгын нийлбэрийг заасан мужид кодоор харуул.

4. Санхүүгийн хариуцлагатай хүмүүсийн жагсаалтыг заасан хугацаанд ажилласан огноотой харуулах.

Сонголт 18

1. Тодорхой техникчээр хийсэн засварын жагсаалтыг харуулах.

2. Гарчиг нь заасан үгийг агуулсан ажилд орсон ажлын үе шатуудын жагсаалтыг харуулах.

3. Заасан мужид кодтой ажиллах засварын ажлын үе шатуудын зардлын нийлбэрийг харуулах.

4. Заасан мужид ажилд авсан огноотой мастеруудын жагсаалтыг харуулах.

Сонголт 19

1. Тодорхой заалт бүхий эмийн жагсаалтыг харуулах.

2. Тодорхой тооноос илүү эм зарагдсан баримтын дугаарын жагсаалтыг харуулах.

3. Заасан дугаартай баримт дээр худалдсан огноо, үнийн дүн, няравын нэр, эм зэргийг харуулах.

4. Баглаанд байгаа тоо хэмжээ нь заасан тооноос их буюу эмийн код тодорхой хэмжээнээс бага байгаа эмийн жагсаалт, хэмжих нэгжийг харуулах.

Сонголт 20

1. Заасан албан тушаалтай ажилчдын жагсаалтыг харуулах.

2. Агуулга нь заасан үгийн фрагмент агуулсан баримт бичгийн жагсаалтыг харуулах.

3. Заасан хугацаанд бүртгүүлсэн баримтын бүртгэлийн огноо, баримт бичгийн төрөл, гүйцэтгэгчийн овог нэр, гүйцэтгэлийн баримтыг харуулна.

4. Бүртгэгдсэн баримт бичгийн жагсаалтыг тодорхой хүрээнд заасан баримт бичгийн төрөл эсвэл баримт бичгийн кодоор харуулах.

Хадгалагдсан журамнь 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 мэдэгдэлугсралтыг шинэчлэхэд ашигладаг Хамгийн сүүлийн үеийн хувилбар. Энэ заавар нь холбогдох угсралттай холбоотой файлуудыг нэмж эсвэл устгана. ДУГААР УГСРАХ зааварЗаасан угсралт болон түүнтэй холбоотой бүх файлыг одоогийн мэдээллийн сангаас устгана.

Доорх жишээнд өмнө нь хэрэгжүүлсэн удирдсан код дээр үндэслэн хадгалагдсан процедурыг хэрхэн үүсгэхийг харуулав.

SampleDb-г ашиглах; ЯВАХ ПРОЦЕДУРАС ҮҮСГҮҮЛЭХ CountAmployees CLRStoredProcedures.StoredProcedures.CountEmployees ГАДААД НЭРЭЭР

Жишээн дэх CREATE PROCEDURE заавар нь өмнөх жишээнүүдийн ижил зааварчилгааг агуулж байгаагаараа ялгаатай. EXTERNAL NAME параметр. Энэ сонголт нь кодыг нийтлэг хэлний ажиллах хугацаанд үүсгэсэн болохыг зааж өгдөг. Энэ өгүүлбэр дэх нэр нь гурван хэсгээс бүрдэнэ.

угсралтын_нэр.ангийн_нэр.арга_нэр

assembly_name - угсралтын нэрийг заана;

ангийн_нэр - ерөнхий ангийн нэрийг заана;

method_name - нэмэлт хэсэг, анги дотор тодорхойлсон аргын нэрийг зааж өгнө.

CountEmployees процедурын гүйцэтгэлийг доорх жишээнд үзүүлэв.

SampleDb-г ашиглах; МЭДЭГДЭХ @count INT ГҮЙЦЭТГЭХ @count = Ажилчдын тоо PRINT @count -- Буцах 7

PRINT мэдэгдэл нь Ажилчдын хүснэгтийн одоогийн мөрийн тоог буцаана.

Хадгалагдсан процедурын тухай ойлголтыг тодорхойлсон. Параметр бүхий хадгалсан процедурыг үүсгэх, өөрчлөх, ашиглах жишээг үзүүлэв. Оролтын болон гаралтын параметрүүдийн тодорхойлолтыг өгсөн болно. Хадгалагдсан процедурыг үүсгэх, дуудах жишээг үзүүлэв.

Хадгалагдсан процедурын тухай ойлголт

Хадгалагдсан процедуруудЭдгээр нь хоорондоо холбоотой SQL хэллэгүүдийн бүлгүүд бөгөөд тэдгээрийн хэрэглээ нь програмистын ажлыг илүү хялбар, уян хатан болгодог. хадгалагдсан процедурнь ихэвчлэн SQL хэллэгүүдийн дарааллаас хамаагүй хялбар байдаг. Хадгалагдсан процедур гэдэг нь нэг буюу хэд хэдэн SQL хэллэг эсвэл функцээс бүрдэх, мэдээллийн санд эмхэтгэсэн хэлбэрээр хадгалагдсан командуудын багц юм. Мэдээллийн сан дахь гүйцэтгэл хадгалагдсан процедуруудХувь хүний SQL хэллэгийн оронд хэрэглэгч дараах давуу талуудтай.

шаардлагатай операторууд мэдээллийн санд аль хэдийн орсон байна;
Тэд бүгд шат дамжсан задлан шинжлэхгүйцэтгэх боломжтой форматтай; өмнө хадгалагдсан процедурыг гүйцэтгэх SQL Server нь түүний гүйцэтгэлийн төлөвлөгөөг гаргаж, оновчтой болгох, эмхэтгэх ажлыг гүйцэтгэдэг;
хадгалагдсан процедурууддэмжлэг үзүүлэх модульчлагдсан програмчлал, учир нь тэдгээр нь том ажлуудыг бие даасан, жижиг, удирдахад хялбар хэсгүүдэд хуваах боломжийг олгодог;
хадгалагдсан процедуруудбусдад хүргэж болзошгүй хадгалагдсан процедуруудболон функцууд;
хадгалагдсан процедуруудбусад төрлийн хэрэглээний програмуудаас дуудаж болно;
ихэвчлэн, хадгалагдсан процедуруудбие даасан мэдэгдлийн дарааллаас илүү хурдан гүйцэтгэх;
хадгалагдсан процедуруудхэрэглэхэд илүү хялбар: тэдгээр нь хэдэн арван эсвэл хэдэн зуун тушаалаас бүрдэх боломжтой, гэхдээ тэдгээрийг ажиллуулахын тулд та хүссэн тушаалынхаа нэрийг зааж өгөх хэрэгтэй. хадгалагдсан процедур. Энэ нь үйлчлүүлэгчээс сервер рүү илгээсэн хүсэлтийн хэмжээ, улмаар сүлжээний ачааллыг багасгах боломжийг олгодог.

Процедуруудыг гүйцэтгэж байгаа газар нь хадгалах нь сүлжээгээр дамжих өгөгдлийн хэмжээг бууруулж, системийн ерөнхий гүйцэтгэлийг сайжруулдаг. Өргөдөл хадгалагдсан процедуруудпрограм хангамжийн системийн засвар үйлчилгээ, тэдгээрт өөрчлөлт оруулах ажлыг хялбаршуулдаг. Дүрэм, өгөгдөл боловсруулах алгоритм хэлбэрээр бүх бүрэн бүтэн байдлын хязгаарлалтууд нь ихэвчлэн мэдээллийн сангийн сервер дээр хэрэгждэг бөгөөд эцсийн хэрэглээнд багц хэлбэрээр ашиглах боломжтой байдаг. хадгалагдсан процедурууд, өгөгдөл боловсруулах интерфейсийг төлөөлдөг. Өгөгдлийн бүрэн бүтэн байдлыг хангахын тулд, мөн аюулгүй байдлын үүднээс програм нь өгөгдөлд шууд хандах хандалтыг ихэвчлэн хүлээн авдаггүй - түүнтэй ажиллах бүх ажлыг тодорхой дугаар руу залгах замаар гүйцэтгэдэг. хадгалагдсан процедурууд.

Энэ арга нь бүх сүлжээний хэрэглэгчдэд шууд ашиглах боломжтой өгөгдөл боловсруулах алгоритмыг өөрчлөхөд маш хялбар болгодог бөгөөд програмд өөрөө өөрчлөлт оруулахгүйгээр системийг өргөжүүлэх боломжийг олгодог: зүгээр л өөрчлөхөд хангалттай. хадгалагдсан процедурмэдээллийн сангийн сервер дээр. Хөгжүүлэгч програмыг дахин эмхэтгэх, түүний хуулбарыг үүсгэх, хэрэглэгчдэд шинэ хувилбартай ажиллахыг зааварлах шаардлагагүй. Хэрэглэгчид системд өөрчлөлт орсныг мэдээгүй байж магадгүй.

Хадгалагдсан процедуруудХүснэгт болон бусад мэдээллийн сангийн объектоос үл хамааран оршин тогтнох. Тэднийг үйлчлүүлэгчийн хөтөлбөрөөр дууддаг, өөр хадгалагдсан процедурэсвэл гох. Хөгжүүлэгч нь хандах эрхийг удирдах боломжтой хадгалагдсан процедур, түүнийг хэрэгжүүлэхийг зөвшөөрөх буюу хориглох. Кодыг өөрчлөх хадгалагдсан процедурзөвхөн эзэмшигч эсвэл тогтмол мэдээллийн сангийн гишүүнээр зөвшөөрнө. Шаардлагатай бол та эзэмшигчийн эрхийг нэг хэрэглэгчээс нөгөөд шилжүүлэх боломжтой.

MS SQL Server орчинд хадгалагдсан процедурууд

SQL Server-тэй ажиллахдаа хэрэглэгчид тодорхой үйлдлүүдийг хэрэгжүүлэх өөрийн процедурыг үүсгэж болно. Хадгалагдсан процедурууднь бүрэн хэмжээний мэдээллийн сангийн объектууд тул тус бүр нь тодорхой мэдээллийн санд хадгалагддаг. Шууд дуудлага хадгалагдсан процедурПроцедур байрладаг мэдээллийн сангийн хүрээнд хийгдсэн тохиолдолд л боломжтой.

Хадгалагдсан процедурын төрлүүд

SQL сервер нь хэд хэдэн төрөлтэй хадгалагдсан процедурууд.

Систем хадгалагдсан процедуруудянз бүрийн захиргааны үйлдлийг гүйцэтгэхэд зориулагдсан. Бараг бүх серверийн удирдлагын үйл ажиллагааг тэдний тусламжтайгаар гүйцэтгэдэг. Үүнийг бид системтэй гэж хэлж болно хадгалагдсан процедурууднь системийн хүснэгтүүдтэй ажиллах боломжийг олгодог интерфейс бөгөөд энэ нь эцсийн дүндээ хэрэглэгчийн болон системийн мэдээллийн сангийн системийн хүснэгтээс өгөгдлийг өөрчлөх, нэмэх, устгах, сэргээхэд хүргэдэг. Систем хадгалагдсан процедурууд sp_ угтвартай, системийн мэдээллийн санд хадгалагддаг бөгөөд өөр ямар ч мэдээллийн сангийн контекстоор дуудаж болно.
Захиалгат хадгалагдсан процедуруудтодорхой арга хэмжээг хэрэгжүүлэх. Хадгалагдсан процедурууд– бүрэн хэмжээний мэдээллийн сангийн объект. Үүний үр дүнд тус бүр хадгалагдсан процедургүйцэтгэх тодорхой мэдээллийн санд байрладаг.
Түр зуурын хадгалагдсан процедуруудхэсэг хугацаанд л байх ба үүний дараа сервер автоматаар устгагдах болно. Тэдгээрийг орон нутгийн болон дэлхийн гэж хуваадаг. Орон нутгийн түр зуурын хадгалагдсан процедуруудТэдний үүсгэсэн холболтоос л дуудаж болно. Ийм журам үүсгэхдээ та түүнд нэг # тэмдэгтээр эхэлсэн нэр өгөх ёстой. Бүх түр зуурын объектуудын нэгэн адил хадгалагдсан процедуруудХэрэглэгч салгах эсвэл серверийг дахин эхлүүлэх эсвэл зогсоох үед энэ төрлийн автоматаар устгагдах болно. Дэлхийн түр зуурын хадгалагдсан процедуруудижил журамтай серверээс ямар ч холболт хийх боломжтой. Үүнийг тодорхойлохын тулд ## тэмдэгтээр эхэлсэн нэр өгөхөд л хангалттай. Эдгээр процедур нь серверийг дахин эхлүүлэх эсвэл зогсоох, эсвэл үүсгэсэн контекст дэх холболтыг хаах үед устгагдана.

Хадгалагдсан процедурыг үүсгэх, өөрчлөх, устгах

Бүтээл хадгалагдсан процедурдараахь асуудлуудыг шийдвэрлэхэд хамаарна.

үүсгэсэн төрлийг тодорхойлох хадгалагдсан процедур: түр зуурын эсвэл захиалгат. Үүнээс гадна та өөрийн системийг үүсгэж болно хадгалагдсан процедур, sp_ угтвартай нэр өгч, системийн мэдээллийн санд байрлуулна. Энэ процедур нь ямар ч локал серверийн мэдээллийн сангийн хүрээнд боломжтой байх болно;
нэвтрэх эрхийг төлөвлөх. Бүтээж байхдаа хадгалагдсан процедурөгөгдлийн сангийн объектуудад түүнийг үүсгэсэн хэрэглэгчийн адил хандалтын эрхтэй байх болно гэдгийг анхаарах хэрэгтэй;
тодорхойлолт хадгалагдсан процедурын параметрүүд. Ихэнх програмчлалын хэлэнд байдаг процедуртай адил, хадгалагдсан процедуруудоролт ба гаралтын параметртэй байж болно;
код боловсруулах хадгалагдсан процедур. Процедурын код нь бусад руу залгах гэх мэт ямар ч SQL командын дарааллыг агуулж болно хадгалагдсан процедурууд.

Шинээр бий болгож, одоо байгаа зүйлийг өөрчлөх хадгалагдсан процедурдараах тушаалыг ашиглан хийсэн:

<определение_процедуры>::= (CREATE | ALTER ) PROC procedure_name [;number] [(@parameter_name data_type ) [=default] ][,...n] AS sql_operator [...n]

Энэ командын параметрүүдийг харцгаая.

sp_ , #, ## угтваруудыг ашиглан үүсгэсэн процедурыг систем эсвэл түр зуурын гэж тодорхойлж болно. Командын синтаксаас харахад үүсгэсэн процедурыг эзэмших эзэмшигчийн нэр, түүнчлэн түүнийг байрлуулах мэдээллийн сангийн нэрийг зааж өгөхийг хориглоно. Тиймээс, бий болгосон байрлуулахын тулд хадгалагдсан процедуртодорхой мэдээллийн санд та тухайн мэдээллийн сангийн контекст дээр CREATE PROCEDURE командыг өгөх ёстой. Бие махбодоос эргэх үед хадгалагдсан процедуртовчилсон нэрийг ижил мэдээллийн сангийн объектод ашиглаж болно, өөрөөр хэлбэл мэдээллийн сангийн нэрийг зааж өгөхгүйгээр. Бусад өгөгдлийн санд байгаа объектуудад хандах шаардлагатай бол мэдээллийн сангийн нэрийг зааж өгөх шаардлагатай.

Нэрэнд байгаа дугаар нь таних дугаар юм хадгалагдсан процедур, энэ нь түүнийг процедурын бүлэгт өвөрмөц байдлаар тодорхойлдог. Удирдлагад хялбар болгох үүднээс процедурууд нь логикийн хувьд ижил төрлийн байдаг хадгалагдсан процедуруудижил нэртэй боловч өөр таних дугаар өгөх замаар бүлэглэж болно.

Үүсгэсэн доторх оролт, гаралтын өгөгдлийг дамжуулах хадгалагдсан процедурпараметрүүдийг ашиглаж болох бөгөөд тэдгээрийн нэр нь локал хувьсагчийн нэр шиг @ тэмдэгээр эхлэх ёстой. Нэг хадгалагдсан процедурТа таслалаар тусгаарлагдсан олон параметрүүдийг зааж өгч болно. Процедурын үндсэн хэсэгт нэр нь энэ процедурын параметрүүдийн нэртэй давхцаж байгаа локал хувьсагчийг ашиглах ёсгүй.

Харгалзах өгөгдлийн төрлийг тодорхойлох хадгалагдсан процедурын параметр, ямар ч SQL өгөгдлийн төрөл тохиромжтой, үүнд хэрэглэгчийн тодорхойлсон. Гэхдээ CURSOR өгөгдлийн төрлийг зөвхөн байдлаар ашиглах боломжтой гаралтын параметр хадгалагдсан процедур, өөрөөр хэлбэл OUTPUT түлхүүр үгийг зааж өгч байна.

OUTPUT түлхүүр үг байгаа нь харгалзах параметр нь өгөгдлийг буцаах зорилготой гэсэн үг юм хадгалагдсан процедур. Гэхдээ энэ нь параметр нь утгыг дамжуулахад тохиромжгүй гэсэн үг биш юм хадгалагдсан процедур. OUTPUT түлхүүр үгийг зааж өгснөөр серверээс гарах заавар өгнө хадгалагдсан процедурпараметрийн утга болгон процедурыг дуудах үед тодорхойлсон локал хувьсагчид параметрийн одоогийн утгыг оноох. OUTPUT түлхүүр үгийг зааж өгөхдөө процедурыг дуудах үед тохирох параметрийн утгыг зөвхөн локал хувьсагч ашиглан тохируулах боломжтой гэдгийг анхаарна уу. Ердийн параметрүүдэд зөвшөөрөгдсөн аливаа илэрхийлэл эсвэл тогтмолыг ашиглахыг зөвшөөрөхгүй.

VARYING түлхүүр үг нь CURSOR төрлийн OUTPUT параметртэй хамт хэрэглэгддэг. Үүнийг тодорхойлдог гаралтын параметрүр дүнгийн багц байх болно.

DEFAULT түлхүүр үг нь тохирох утгыг илэрхийлнэ анхдагч параметр. Тиймээс процедурыг дуудахдаа харгалзах параметрийн утгыг тодорхой зааж өгөх шаардлагагүй болно.

Сервер нь асуулгын гүйцэтгэлийн төлөвлөгөө болон хөрвүүлсэн кодыг кэш болгодог тул дараагийн удаа процедурыг дуудах үед бэлэн утгуудыг ашиглах болно. Гэсэн хэдий ч зарим тохиолдолд процедурын кодыг дахин эмхэтгэх шаардлагатай хэвээр байна. RECOMPILE түлхүүр үгийг зааж өгснөөр системд гүйцэтгэлийн төлөвлөгөө гаргах заавар өгнө хадгалагдсан процедуртүүнийг залгах болгонд.

Өгөгдлийг хуулбарлах, үүсгэсэн зүйлийг идэвхжүүлэх үед FOR REPLICATION параметр шаардлагатай хадгалагдсан процедурхэвлэн нийтлэх нийтлэл болгон.

ENCRYPTION түлхүүр үг нь серверт кодыг шифрлэх заавар өгдөг хадгалагдсан процедур, энэ нь ажлыг хэрэгжүүлдэг өмчийн алгоритмуудыг ашиглахаас хамгаалах боломжтой хадгалагдсан процедур.

AS түлхүүр үгийг биеийн эхэнд байрлуулна хадгалагдсан процедур, өөрөөр хэлбэл SQL командуудын багц бөгөөд тэдгээрийн тусламжтайгаар энэ эсвэл бусад үйлдлийг гүйцэтгэх болно. Процедурын үндсэн хэсэгт бараг бүх SQL командыг ашиглах, гүйлгээг зарлах, түгжээг тохируулах, бусад командыг дуудах боломжтой. хадгалагдсан процедурууд. -аас гарах хадгалагдсан процедур RETURN командыг ашиглан хийж болно.

Хадгалагдсан процедурыг устгахтушаалаар гүйцэтгэнэ:

УНАХ ЖУРАМ (процедурын_нэр) [,...n]

Хадгалагдсан процедурыг гүйцэтгэх

Учир нь хадгалагдсан процедурыг гүйцэтгэхХэрэглэсэн тушаал нь:

[[ EXEC [ UTE] процедурын нэр [;тоо] [[@parameter_name=](утга | @хувьсагчийн_нэр) |][,...n]

Хэрэв дуудлага бол хадгалагдсан процедурнь багц дахь цорын ганц тушаал биш бөгөөд EXECUTE команд байх шаардлагатай. Түүнчлэн, энэ команд нь өөр процедур эсвэл триггерийн биеэс процедурыг дуудах шаардлагатай.

Процедурыг дуудах үед OUTPUT түлхүүр үгийг ашиглахыг зөвхөн тухайн үед зарласан параметрүүдэд зөвшөөрнө. журам бий болгох OUTPUT түлхүүр үгээр.

Процедурыг дуудах үед параметрийн хувьд DEFAULT гэсэн түлхүүр үгийг зааж өгсөн тохиолдолд үүнийг ашиглана анхдагч утга. Мэдээжийн хэрэг, заасан DEFAULT үгийг зөвхөн тодорхойлсон параметрүүдэд л зөвшөөрдөг анхдагч утга.

EXECUTE командын синтакс нь процедурыг дуудах үед параметрийн нэрийг орхигдуулж болохыг харуулж байна. Гэсэн хэдий ч, энэ тохиолдолд хэрэглэгч параметрүүдийн утгыг жагсаасан дарааллаар нь зааж өгөх ёстой. журам бий болгох. Параметрт оноох анхдагч утга, та жагсаалтад оруулахдаа үүнийг алгасаж болохгүй. Хэрэв та тодорхойлсон параметрүүдийг орхихыг хүсвэл анхдагч утга, дуудаж байхдаа параметрийн нэрийг тодорхой зааж өгөхөд хангалттай хадгалагдсан процедур. Үүнээс гадна, ийм байдлаар та параметрүүд болон тэдгээрийн утгыг дурын дарааллаар жагсааж болно.

Жишээ 12.1. Параметргүй журам. Ивановын худалдаж авсан барааны нэр, зардлыг олж авах журмыг боловсруулах.

PROC my_proc1-ийг Бүтээгдэхүүн.Нэр, Бүтээгдэхүүн.Үнэ*Гүйлгээ.Тоо хэмжээ, Өртөг, Хэрэглэгч. Овог Нэр, Хэрэглэгчийн ДОТООД НЭГДСЭН ГҮЙЛГЭЭ (Бүтээгдэхүүний ДОТООД НЭГДСЭН ГҮЙЛГЭЭ ON Product.ProductCode=Гүйлгээ.БүтээгдэхүүнийКод) ON Customer.CustomerCode=CmerHERERHER. Хэрэглэгч .Овог='Иванов' Жишээ 12.1. Ивановын худалдаж авсан барааны нэр, үнэ цэнийг олж авах журам.