გაფართოებული შენახული პროცედურების პროგრამირებისთვის, Microsoft გთავაზობთ ODS (Open Data Service) API-ს, მაკროებისა და ფუნქციების ერთობლიობას, რომელიც გამოიყენება სერვერის აპლიკაციების შესაქმნელად, რაც საშუალებას გაძლევთ გააფართოვოთ MS-ის ფუნქციონირება. SQL სერვერი 2000.
გაფართოებული შენახული პროცედურები არის ჩვეულებრივი ფუნქციები, რომლებიც დაწერილია C/C++-ში ODS API და WIN32 API-ის გამოყენებით, შექმნილია როგორც დინამიური ბმული ბიბლიოთეკა (dll) და შექმნილია, როგორც უკვე ვთქვი, SQL სერვერის ფუნქციონირების გაფართოებისთვის. ODS API უზრუნველყოფს დეველოპერს ფუნქციების მდიდარ კომპლექტს, რომელიც საშუალებას გაძლევთ გადასცეთ მონაცემები კლიენტს, რომელიც მიღებულია ნებისმიერი გარე მონაცემთა წყაროდან რეგულარული ჩანაწერების ნაკრების სახით. ასევე, გაფართოებულ შენახულ პროცედურას შეუძლია დააბრუნოს მნიშვნელობები მასზე გადაცემული პარამეტრის საშუალებით (OUTPUT პარამეტრი).
როგორ მუშაობს გაფართოებული შენახული პროცედურები.
- როდესაც კლიენტის აპლიკაცია იძახებს გაფართოებულ შენახულ პროცედურას, მოთხოვნა გადაიცემა TDS ფორმატში Net-Libraries და ღია მონაცემთა სერვისის მეშვეობით MS SQL SERVER ბირთვში.
- SQL Sever პოულობს dll ბიბლიოთეკას, რომელიც ასოცირდება გაფართოებული შენახული პროცედურის სახელთან და ატვირთავს მას მის კონტექსტში, თუ ის აქამდე არ იყო ჩატვირთული, და იძახებს გაფართოებულ შენახულ პროცედურას, რომელიც განხორციელებულია როგორც ფუნქცია dll-ში.
- გაფართოებული შენახული პროცედურა ასრულებს საჭირო მოქმედებებს სერვერზე და გადასცემს შედეგების კომპლექტს კლიენტის აპლიკაციაში ODS API-ს მიერ მოწოდებული სერვისის გამოყენებით.
გაფართოებული შენახული პროცედურების მახასიათებლები.
- გაფართოებული შენახული პროცედურები არის ფუნქციები, რომლებიც სრულდება MS SQL Server მისამართის სივრცეში და იმ ანგარიშის უსაფრთხოების კონტექსტში, რომლითაც მუშაობს MS SQL Server სერვისი;
- მას შემდეგ, რაც გაფართოებული შენახული პროცედურა dll ჩაიტვირთება მეხსიერებაში, ის რჩება იქ, სანამ SQL Server არ შეჩერდება, ან სანამ ადმინისტრატორი აიძულებს მას გადმოტვირთოს ბრძანების გამოყენებით:
DBCC DLL_name (უფასო). - გაფართოებული შენახული პროცედურა შესრულებულია ისევე, როგორც ჩვეულებრივი შენახული პროცედურა:
EXECUTE xp_extendedProcName @param1, @param2 OUTPUT
@param1 შეყვანის პარამეტრი
@param2 შეყვანის/გამოსვლის პარამეტრი
ვინაიდან გაფართოებული შენახული პროცედურები მუშაობს MS SQL Server სერვისის პროცესის მისამართების სივრცეში, ნებისმიერი კრიტიკული შეცდომებიმათ მუშაობაში წარმოქმნილმა პრობლემებმა შეიძლება დააზიანოს სერვერის ბირთვი, ამიტომ რეკომენდირებულია საფუძვლიანად შეამოწმოთ თქვენი DLL წარმოების სერვერზე დაყენებამდე.
გაფართოებული შენახული პროცედურების შექმნა.
გაფართოებული შენახული პროცედურა არის ფუნქცია, რომელსაც აქვს შემდეგი პროტოტიპი:
SRVRETCODE xp_extendedProcName(SRVPROC * pSrvProc);
Პარამეტრი pSrvProc SRVPROC სტრუქტურის მაჩვენებელი, რომელიც არის სახელური თითოეული კონკრეტული კლიენტის კავშირისთვის. ამ სტრუქტურის ველები არადოკუმენტირებულია და შეიცავს ინფორმაციას, რომელსაც ODS ბიბლიოთეკა იყენებს Open Data Services სერვერის აპლიკაციასა და კლიენტს შორის კომუნიკაციისა და მონაცემების სამართავად. ნებისმიერ შემთხვევაში, თქვენ არ გჭირდებათ ამ სტრუქტურის წვდომა, მით უმეტეს მისი შეცვლა. ეს პარამეტრი უნდა იყოს მითითებული ნებისმიერი ODS API ფუნქციის გამოძახებისას, ამიტომ მის აღწერაზე აღარ შევჩერდები.
xp_ პრეფიქსის გამოყენება არჩევითია, მაგრამ არსებობს კონვენცია, რომ დაიწყოს გაფართოებული შენახული პროცედურის სახელი ამ გზით, რათა განასხვავოს იგი ჩვეულებრივი შენახული პროცედურისგან, რომელიც, როგორც მოგეხსენებათ, თავის სახელებს იწყება sp_ პრეფიქსით.
ასევე უნდა გახსოვდეთ, რომ გაფართოებული შენახული პროცედურების სახელები რეგისტრის მგრძნობიარეა. არ დაგავიწყდეთ ამის შესახებ, როდესაც დარეკავთ გაფართოებულ შენახულ პროცედურას, წინააღმდეგ შემთხვევაში თქვენ მიიღებთ შეცდომის შეტყობინებას მოსალოდნელი შედეგის ნაცვლად.
თუ გჭირდებათ dll ინიციალიზაციის/დეინიციალიზაციის კოდის დაწერა, ამისათვის გამოიყენეთ სტანდარტული DllMain() ფუნქცია. თუ თქვენ არ გაქვთ ასეთი საჭიროება და არ გსურთ დაწეროთ DLLMain(), მაშინ შემდგენელი ააშენებს DLLMain() ფუნქციის საკუთარ ვერსიას, რომელიც არაფერს აკეთებს, უბრალოდ აბრუნებს TRUE-ს. dll-დან გამოძახებული ყველა ფუნქცია (ანუ გაფართოებული შენახული პროცედურები) უნდა გამოცხადდეს ექსპორტად. თუ წერთ MS Visual C++-ში, გამოიყენეთ დირექტივა __declspec (dllexport). თუ თქვენი შემდგენელი არ უჭერს მხარს ამ დირექტივას, აღწერეთ ექსპორტირებული ფუნქცია DEF ფაილის EXPORTS განყოფილებაში.
ასე რომ, პროექტის შესაქმნელად დაგვჭირდება შემდეგი ფაილები:
- Srv.h სათაურის ფაილი შეიცავს ODS API ფუნქციების და მაკროების აღწერას;
- Opends60.lib არის იმპორტის ფაილი Opends60.dll ბიბლიოთეკისთვის, რომელიც ახორციელებს ODS API-ს მიერ მოწოდებულ მთელ სერვისს.
Declspec(dllexport) ULONG __GetXpVersion()
{
დაბრუნება ODS_VERSION;
}
როდესაც MS SQL Server ატვირთავს DLL-ს გაფართოებული შენახული პროცედურებით, ის ჯერ იძახებს ამ ფუნქციას, რათა მოიპოვოს ინფორმაცია გამოყენებული ბიბლიოთეკის ვერსიის შესახებ.
თქვენი პირველი გაფართოებული შენახული პროცედურის დასაწერად, თქვენ უნდა დააინსტალიროთ თქვენს კომპიუტერში:
ნებისმიერი გამოცემის MS SQL Server 2000 (მე მაქვს პირადი გამოცემა). ინსტალაციის პროცესში, დარწმუნდით, რომ აირჩიეთ წყაროს ნიმუშის ვარიანტი
- MS Visual C++ (გამოვიყენე ვერსია 7.0), მაგრამ ზუსტად ვიცი, რომ 6.0 გამოვა
SQL Server -a-ს ინსტალაცია საჭიროა თქვენი DLL-ის შესამოწმებლად და გამართვისთვის. ასევე შესაძლებელია ქსელის საშუალებით გამართვა, მაგრამ მე ეს არასდროს გამიკეთებია, ამიტომ ყველაფერი დავაინსტალირე ადგილობრივი დისკი. მიწოდებაში Microsoft Visual C++ 7.0 Interprise Edition მოიცავს გაფართოებული შენახული პროცედურის DLL ოსტატს. პრინციპში, ის არაფერს აკეთებს დამატებით ბუნებრივ, მაგრამ მხოლოდ ქმნის შაბლონის შაბლონს გაფართოებული შენახული პროცედურისთვის. თუ მოგწონთ ოსტატები, შეგიძლიათ გამოიყენოთ იგი. მირჩევნია ყველაფერი ხელით გავაკეთო და ამიტომ ამ საქმეს არ განვიხილავ.
ახლა საქმეზე:
- გაუშვით Visual C++ და შექმენით ახალი პროექტი- Win32 Dynamic Link Library.
- პროექტში ჩართეთ ჰედერის ფაილი - #include
- გადადით Tools => Options მენიუში და დაამატეთ საძიებო ბილიკები ჩათვლით და ბიბლიოთეკის ფაილებისთვის. თუ არაფერი შეგიცვლიათ MS SQL Server-ის დაყენებისას, მიუთითეთ:
C:Program FilesMicrosoft SQL Server80ToolsDevToolsInclude სათაურის ფაილებისთვის;
- C:Program FilesMicrosoft SQL Server80ToolsDevToolsLib ბიბლიოთეკის ფაილებისთვის.
- მიუთითეთ ბიბლიოთეკის ფაილის სახელი opends60.lib ლინკერის ოფციებში.
ამ ეტაპზე მოსამზადებელი ეტაპი დასრულებულია, შეგიძლიათ დაიწყოთ თქვენი პირველი გაფართოებული შენახული პროცედურის წერა.
პრობლემის ფორმულირება.
სანამ პროგრამირებას დაიწყებთ, უნდა გქონდეთ მკაფიო წარმოდგენა, სად უნდა დაიწყოთ, როგორი უნდა იყოს საბოლოო შედეგი და როგორ მიაღწიოთ მას. ასე რომ, აქ არის ტექნიკური სპეციფიკაცია:
შეიმუშავეთ გაფართოებული შენახული პროცედურა MS SQL Server 2000-ისთვის, რომელიც იღებს სრული სიადომენში რეგისტრირებული მომხმარებლები და უბრუნებს მას კლიენტს სტანდარტული ჩანაწერების ნაკრების სახით. როგორც პირველი შეყვანის პარამეტრი, ფუნქცია იღებს სერვერის სახელს, რომელიც შეიცავს კატალოგის მონაცემთა ბაზას ( Მოქმედი დირექტორი), ანუ დომენის კონტროლერის სახელი. თუ ეს პარამეტრი არის NULL, მაშინ ლოკალური ჯგუფების სია უნდა გადაეცეს კლიენტს. მეორე პარამეტრი გამოყენებული იქნება გაფართოებული შენახული პროცედურის მიერ წარმატებული/წარუმატებელი ოპერაციის შედეგის მნიშვნელობის დასაბრუნებლად (OUTPUT პარამეტრი). თუ გაფართოებული შენახული პროცედურა წარმატებით დასრულდა, მაშინ აუცილებელია კლიენტის ჩანაწერების ნაკრებისთვის დაბრუნებული ჩანაწერების გადაცემა, თუ ოპერაციის დროს შეუძლებელი გახდა საჭირო ინფორმაციის მიღება, მეორე პარამეტრის მნიშვნელობა უნდა დაყენდეს - 1, წარუმატებელი დასრულების ნიშნად.
გაფართოებული შენახული პროცედურის პირობითი პროტოტიპი შემდეგია:
xp_GetUserList(@NameServer varchar, @CountRec int OUTPUT);
და აქ არის გაფართოებული შენახული პროცედურის შაბლონი, რომელიც უნდა შეავსოთ შინაარსით:
#შეიცავს
#შეიცავს
#define XP_NOERROR 0
#define XP_ERROR -1
__declspec(dllexport) SERVRETCODE xp_GetGroupList(SRVPROC* pSrvProc)
{
//გავლილი პარამეტრების რაოდენობის შემოწმება
//გავლილი პარამეტრების ტიპის შემოწმება
//შემოწმება არის თუ არა პარამეტრი 2 OUTPUT პარამეტრი
//შემოწმება არის თუ არა პარამეტრი 2 საკმარისი მნიშვნელობის შესანახად
//შეყვანის პარამეტრების მიღება
//მომხმარებელთა სიის მიღება
// მიღებული მონაცემების კლიენტისთვის გაგზავნა სტანდარტული ჩანაწერების ნაკრების სახით
//OUTPUT პარამეტრის მნიშვნელობის დაყენება
დაბრუნება (XP_NOERROR);
}
შეყვანის პარამეტრებთან მუშაობა
ამ თავში არ მსურს თქვენი ყურადღების გაფანტვა გარე საგნებზე, მაგრამ მინდა გავამახვილო ყურადღება გაფართოებულ შენახულ პროცედურაზე გადაცემულ პარამეტრებთან მუშაობაზე. აქედან გამომდინარე, ჩვენ გარკვეულწილად გავამარტივებთ ჩვენს ტექნიკურ მახასიათებლებს და განვავითარებთ მხოლოდ მის ნაწილს, რომელიც მუშაობს შეყვანის პარამეტრებთან. მაგრამ პირველი, არც ისე ბევრი თეორია
პირველი მოქმედება, რომელიც ჩვენმა გაფართოებულმა შენახულმა პროცედურამ უნდა შეასრულოს, არის იმ პარამეტრების მიღება, რომლებიც მას გადაეცა მისი გამოძახებისას. ზემოაღნიშნული ალგორითმის მიხედვით, ჩვენ უნდა შეასრულოთ შემდეგი ნაბიჯები:
გავლილი პარამეტრების რაოდენობის განსაზღვრა;
- დარწმუნდით, რომ გავლილ პარამეტრებს აქვთ მონაცემთა სწორი ტიპი;
- დარწმუნდით, რომ მითითებულ OUTPUT პარამეტრს აქვს საკმარისი სიგრძე, რათა შეინახოს მასში ჩვენი გაფართოებული შენახული პროცედურის მიერ დაბრუნებული მნიშვნელობა.
- მიიღეთ გავლილი პარამეტრები;
- დააყენეთ გამომავალი პარამეტრის მნიშვნელობები გაფართოებული შენახული პროცედურის წარმატებით/წარუმატებლად დასრულების შედეგად.
ახლა მოდით შევხედოთ თითოეულ პუნქტს დეტალურად:
გაფართოებულ შენახულ პროცედურაზე გადასული პარამეტრების რაოდენობის განსაზღვრა
გადასული პარამეტრების რაოდენობის მისაღებად, თქვენ უნდა გამოიყენოთ ფუნქცია:
int srv_rpcparams (SRV_PROC * srvproc);
წარმატების შემთხვევაში, ფუნქცია აბრუნებს გაფართოებულ შენახულ პროცედურაზე გადაცემული პარამეტრების რაოდენობას. თუ გაფართოებული შენახული პროცედურა გამოიძახეს პარამეტრების გარეშე, srv_rpcparams დაბრუნდება -1. პარამეტრების გადაცემა შესაძლებელია სახელით ან პოზიციის მიხედვით (უსახელო). ნებისმიერ შემთხვევაში, ეს ორი მეთოდი არ უნდა იყოს შერეული. შეყვანის პარამეტრების ფუნქციას სახელით და პოზიციით ერთდროულად გადაცემის მცდელობა გამოიწვევს შეცდომას და srv_rpcparams დააბრუნებს 0-ს.
გავლილი პარამეტრების მონაცემთა ტიპისა და სიგრძის განსაზღვრა
ინფორმაციის მისაღებად გადაცემის ტიპისა და ხანგრძლივობის შესახებ Microsoft-ის პარამეტრებიგირჩევთ გამოიყენოთ srv_paramifo ფუნქცია. ეს ზოგადი ფუნქცია ცვლის srv_paramtype, srv_paramlen, srv_parammaxlen ზარებს, რომლებიც ახლა მოძველებულია. აქ არის მისი პროტოტიპი:
int srv_paraminfo(
SRV_PROC * srvproc,
int n,
BYTE * pbType,
ULONG* pcbMaxLen,
ULONG * pcbActualLen,
BYTE * pbData,
BOOL * pfNull);
pbTypeგანსაზღვრავს პარამეტრის სერიულ ნომერს. პირველი პარამეტრის რიცხვი იწყება 1-დან.
pcbMaxLenმაჩვენებელი ცვლადისკენ, რომელშიც ფუნქცია ინახავს პარამეტრის სიგრძის მაქსიმალურ მნიშვნელობას. ეს ღირებულება განპირობებულია კონკრეტული ტიპიგავლილი პარამეტრის მონაცემებს, ჩვენ გამოვიყენებთ, რათა დავრწმუნდეთ, რომ OUTPUT პარამეტრი საკმარისია გადაცემული მონაცემების შესანახად.
pcbActualLenპარამეტრის რეალური სიგრძის მაჩვენებელი, რომელიც გადაცემულია გაფართოებულ შენახულ პროცედურაზე გამოძახებისას. თუ მიღებულ პარამეტრს აქვს ნულოვანი სიგრძე და pfNull დროშა დაყენებულია FALSE-ზე, მაშინ (* pcbActualLen) ==0.
pbData- მაჩვენებელი ბუფერზე, რომლისთვისაც მეხსიერება უნდა იყოს გამოყოფილი srv_paraminfo-ზე დარეკვამდე. ამ ბუფერში ფუნქცია ათავსებს გაფართოებული შენახული პროცედურისგან მიღებულ შეყვანის პარამეტრებს. ბუფერის ზომა ბაიტებში უდრის pcbMaxLen მნიშვნელობას. თუ ეს პარამეტრი დაყენებულია NULL-ზე, ბუფერში მონაცემები არ იწერება, მაგრამ ფუნქცია სწორად აბრუნებს მნიშვნელობებს *pbType, *pcbMaxLen, *pcbActualLen, *pfNull. ამიტომ, თქვენ უნდა გამოძახოთ srv_paraminfo ორჯერ: ჯერ pbData=NULL-ით, შემდეგ, როდესაც გამოვყოფთ მეხსიერების საჭირო ზომას pcbActualLen-ის ტოლი ბუფერისთვის, დარეკეთ მეორედ srv_paraminfo-ზე და გადასცემთ მაჩვენებელს გამოყოფილ მეხსიერების ბლოკზე pbData-ს.
pfNullმაჩვენებელი NULL დროშისკენ. srv_paraminfo აყენებს მას TRUE-ზე, თუ შეყვანის პარამეტრის მნიშვნელობა არის NULL.
შეამოწმეთ არის თუ არა მეორე OUTPUT პარამეტრი პარამეტრი.
srv_paramstatus() ფუნქცია შექმნილია გავლილი პარამეტრის სტატუსის დასადგენად:
int srv_paramstatus (
SRV_PROC * srvproc,
int n
);
n არის პარამეტრის რიცხვი, რომელიც გადაცემულია გაფართოებულ შენახულ პროცედურაზე გამოძახებისას. შეგახსენებთ: პარამეტრები ყოველთვის დანომრილია 1-დან.
მნიშვნელობის დასაბრუნებლად srv_paramstatus იყენებს ნულოვან ბიტს. თუ დაყენებულია 1-ზე, გადაცემული პარამეტრი არის OUTPUT პარამეტრი, თუ დაყენებულია 0-ზე, ეს არის ნორმალური პარამეტრი, რომელიც გადაცემულია მნიშვნელობით. თუ გაფართოებული შენახული პროცედურა გამოიძახეს პარამეტრების გარეშე, ფუნქცია დაბრუნდება -1.
გამომავალი პარამეტრის მნიშვნელობის დაყენება.
გაფართოებულ შენახულზე გადაცემული გამომავალი პარამეტრი შეიძლება მიენიჭოს მნიშვნელობა srv_paramsetoutput ფუნქციის გამოყენებით. ეს ახალი თვისებაცვლის ზარს srv_paramset ფუნქციაზე, რომელიც ახლა მოძველებულია, რადგან არ უჭერს მხარს ODS API-ში დანერგილ მონაცემთა ახალ ტიპებს და ნულოვანი სიგრძის მონაცემებს.
int srv_paramsetoutput(
SRV_PROC *srvproc,
int n,
BYTE *pbData,
ULONG cbLen,
BOOL fNull
);
pbDataმაჩვენებელი ბუფერზე მონაცემებით, რომელიც გაეგზავნება კლიენტს გამომავალი პარამეტრის მნიშვნელობის დასაყენებლად.
cbLenგაგზავნილი მონაცემთა ბუფერის სიგრძე. თუ OUTPUT-ზე გადაცემული პარამეტრის მონაცემთა ტიპი განსაზღვრავს მუდმივი სიგრძის მონაცემებს და არ იძლევა NULL მნიშვნელობის შენახვას (მაგალითად, SRVBIT ან SRVINT1), მაშინ ფუნქცია უგულებელყოფს cbLen პარამეტრს. cbLen=0 მიუთითებს ნულოვანი სიგრძის მონაცემებზე და fNull უნდა დაყენდეს FALSE-ზე.
fNullდააყენეთ ეს TRUE-ზე, თუ დაბრუნების პარამეტრის დაყენებაა საჭირო NULL-ზე და cbLen უნდა იყოს 0, წინააღმდეგ შემთხვევაში ფუნქცია ვერ მოხერხდება. ყველა სხვა შემთხვევაში fNull=FALSE.
წარმატების შემთხვევაში, ფუნქცია ბრუნდება SUCCEED. თუ დაბრუნებული მნიშვნელობა არის FAIL, მაშინ ზარი ვერ მოხერხდა. ყველაფერი მარტივი და გასაგებია
ახლა ჩვენ საკმარისად ვიცით, რომ დავწეროთ ჩვენი პირველი გაფართოებული შენახული პროცედურა, რომელიც დააბრუნებს მნიშვნელობას მასზე გადაცემული პარამეტრის მიხედვით, დაე, ეს იყოს სტრიქონი Hello world! მაგალითის გამართვის ვერსია შეგიძლიათ ჩამოტვირთოთ აქ.
#შეიცავს
#define XP_NOERROR 0
#define XP_ERROR 1
#define MAX_SERVER_ERROR 20000
#define XP_HELLO_ERROR MAX_SERVER_ERROR+1
void printError(SRV_PROC*, CHAR*);
#ifdef __cplusplus
გარე "C" (
#დაასრულე თუ
SRVRETCODE __declspec(dllexport) xp_helloworld(SRV_PROC* pSrvProc);
#ifdef __cplusplus
}
#დაასრულე თუ
SRVRETCODE xp_helloworld(SRV_PROC* pSrvProc)
{
char szText = "გამარჯობა სამყარო!";
BYTE bType;
ULONG cbMaxLen;
ULONG cbActualLen;
BOOL fNull;
/* გაფართოებულ საცავში გადატანილი რაოდენობის განსაზღვრა
პარამეტრის პროცედურა */
თუ (srv_rpcparams(pSrvProc) != 1)
{
printError(pSrvProc, "პარამეტრების არასწორი რაოდენობა!");
დაბრუნება (XP_ERROR);
}
/* ინფორმაციის მიღება მონაცემთა ტიპისა და გავლილი პარამეტრების სიგრძის შესახებ */
if (srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen,
&cbActualLen, NULL, &fNull) == ვერ)
{
printError (pSrvProc,
"შეყვანის პარამეტრების შესახებ ინფორმაციის მიღება ვერ ხერხდება...");
დაბრუნება (XP_ERROR);
}
/* შეამოწმეთ არის თუ არა გადასული OUTPUT პარამეტრი პარამეტრი */
თუ ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == ვერ)
{
printError (pSrvProc,
"გადავლილი პარამეტრი არ არის OUTPUT პარამეტრი!");
დაბრუნება (XP_ERROR);
}
/* შეამოწმეთ გავლილი პარამეტრის მონაცემთა ტიპი */
თუ (bType != SRVBIGVARCHAR && bType != SRVBIGCHAR)
{
printError (pSrvProc, "გადავლილი პარამეტრის ტიპი არასწორია!");
დაბრუნება (XP_ERROR);
}
/* დარწმუნდით, რომ მიღებული პარამეტრი საკმარისად გრძელია დაბრუნებული სტრიქონის შესანახად */
თუ (cbMaxLen< strlen(szText))
{
printError (pSrvProc,
"გადაცემული პარამეტრი არ არის საკმარისი სიგრძის n დაბრუნებული სტრიქონის შესანახად!");
დაბრუნება (XP_ERROR);
}
/* დააყენეთ OUTPUT პარამეტრის მნიშვნელობა */
თუ (FIL == srv_paramsetoutput(pSrvProc, 1, (BYTE*)szText, 13, FALSE))
{
printError (pSrvProc,
"მე ვერ დავაყენე OUTPUT პარამეტრის მნიშვნელობა...");
დაბრუნება (XP_ERROR);
}
srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);
დაბრუნება (XP_NOERROR);
}
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg)
{
srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
NULL, 0, 0, szErrorMsg,SRV_NULLTERM);
Srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}
ფუნქციები srv_sendmsg და srv_senddone დარჩა განუხილველი. srv_sendmsg ფუნქცია გამოიყენება კლიენტისთვის შეტყობინებების გასაგზავნად. აქ არის მისი პროტოტიპი:
int srv_sendmsg (
SRV_PROC * srvproc,
int msgtype,
DBINT msgnum,
DBTINYINT კლასი
DBTINYINT სახელმწიფო,
DBCHAR * rpcname,
int rpcnamelen,
DBUSMALLINT თეთრეული,
DBCHAR *მესიჯი,
int msglen
);
msgnum შეტყობინების ნომერი;
კლასი- დაშვებული შეცდომის სიმძიმე. საინფორმაციო შეტყობინებებიაქვთ სიმძიმის მნიშვნელობა 10-ზე ნაკლები ან ტოლი;
სახელმწიფოშეცდომის სტატუსის ნომერი მიმდინარე შეტყობინებისთვის. ეს პარამეტრი გვაწვდის ინფორმაციას დაშვებული შეცდომის კონტექსტზე. მოქმედი მნიშვნელობები მერყეობს 0-დან 127-მდე;
rpcname ამჟამად გამოუყენებელია;
rpcnamelen - ამჟამად არ გამოიყენება;
თეთრეულიაქ შეგიძლიათ მიუთითოთ ხაზის ნომერი საწყისი კოდი. ამ მნიშვნელობიდან გამომდინარე, შემდგომში ადვილი იქნება იმის დადგენა, თუ სად მოხდა შეცდომა. თუ არ გსურთ ამ ფუნქციის გამოყენება, დააყენეთ linenum 0-ზე;
კლიენტისთვის გაგზავნილი გზავნილის მაჩვენებელი სტრიქონზე;
msglenგანსაზღვრავს შეტყობინების სტრიქონის სიგრძეს ბაიტებში. თუ ეს სტრიქონი მთავრდება null სიმბოლოთი, მაშინ ამ პარამეტრის მნიშვნელობა შეიძლება დაყენდეს SRV_NULLTERM.
დაბრუნების მნიშვნელობები:
- თუ წარმატებას მიაღწევთ
- თუ FAIL ვერ მოხერხდა.
ექსპლუატაციის დროს, გაფართოებულმა შენახულმა პროცედურამ რეგულარულად უნდა აცნობოს თავისი სტატუსი კლიენტის აპლიკაციას, ე.ი. გაგზავნეთ შეტყობინებები დასრულებული მოქმედებების შესახებ. რისთვის არის შექმნილი srv_senddone ფუნქცია:
int srv_senddone(
SRV_PROC * srvproc,
DBUSMALLINT სტატუსი,
DBUSMALLINT ინფორმაცია,
DBINT რაოდენობა
);
სტატუსის სტატუსის დროშა. ამ პარამეტრის მნიშვნელობა შეიძლება დაყენდეს ლოგიკური ოპერატორების AND და OR გამოყენებით ცხრილში მოცემული მუდმივების გაერთიანებისთვის:
სტატუსის დროშის აღწერა
SRV_DONE_FINAL მიმდინარე შედეგების ნაკრები საბოლოოა;
SRV_DONE_MORE მიმდინარე შედეგების ნაკრები არ არის საბოლოო;
SRV_DONE_COUNT count პარამეტრი შეიცავს მოქმედ მნიშვნელობას
SRV_DONE_ERROR გამოიყენება შეცდომის წარმოქმნის შეტყობინებისთვის და დაუყოვნებლივ შეწყვეტისთვის.
შევიდადაჯავშნილი, უნდა დაყენდეს 0-ზე.
count არის კლიენტისთვის გაგზავნილი შედეგების ნაკრების რაოდენობა. თუ სტატუსის დროშა დაყენებულია SRV_DONE_COUNT, მაშინ რაოდენობა უნდა შეიცავდეს კლიენტისთვის გაგზავნილი ჩანაწერების სწორ რაოდენობას.
დაბრუნების მნიშვნელობები:
- თუ წარმატებას მიაღწევთ
- თუ FAIL ვერ მოხერხდა.
გაფართოებული შენახული პროცედურების ინსტალაცია MS SQL Server 2000-ზე
1. დააკოპირეთ dll ბიბლიოთეკა გაფართოებული შენახული პროცედურებით binn დირექტორიაში მანქანაზე დაყენებული MS SQL სერვერით. ჩემი გზა ასეთია: C:Program FilesMicrosoft SQL ServerMSSQLBinn;
2. დაარეგისტრირეთ გაფართოებული შენახული პროცედურა სერვერზე შემდეგი სკრიპტის გაშვებით:
გამოიყენე ოსტატი
შეასრულეთ SP_ADDEXTENDEDPROC xp_helloworld, xp_helloworld.dll
შეამოწმეთ xp_helloworld შემდეგი სკრიპტის გაშვებით:
გამოაცხადე @Param varchar(33)
შეასრულეთ xp_helloworld @Param OUTPUT
აირჩიეთ @Param AS OUTPUT_Param
დასკვნა
ამით მთავრდება ჩემი სტატიის პირველი ნაწილი. ახლა დარწმუნებული ვარ, რომ მზად ხართ ჩვენი გასატარებლად მითითების პირობები 100%-ზე. მომდევნო სტატიაში შეიტყობთ:
- ODS API-ში განსაზღვრული მონაცემთა ტიპები;
- გაფართოებული შენახული პროცედურების გამართვის მახასიათებლები;
- როგორ შევქმნათ ჩანაწერების ნაკრები და გადავიტანოთ კლიენტის აპლიკაციაში;
- ჩვენ ნაწილობრივ განვიხილავთ Active Directory Network Manegment API ფუნქციებს, რომლებიც აუცილებელია დომენის მომხმარებლების სიის მისაღებად;
- ჩვენ შევქმნით დასრულებულ პროექტს (ჩვენ განვახორციელებთ ჩვენს ტექნიკურ მახასიათებლებს)
იმედი მაქვს - მალე გნახავ!
PS: ჩამოტვირთეთ ფაილების მაგალითი სტატიისთვის Studio 7.0-ისთვის
ჩართეთ ხაზი თქვენს პროცედურებში - 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 Server ჯერ თავის მთავარ მონაცემთა ბაზაში იხილავს. ფაქტია, რომ ეს პრეფიქსი გამოიყენება სერვერის პირადი შიდა შენახული პროცედურებისთვის. ამიტომ, მისმა გამოყენებამ შეიძლება გამოიწვიოს დამატებითი ხარჯებიდა თუნდაც არასწორი შედეგი, თუ თქვენს მონაცემთა ბაზაში ნაპოვნია იგივე სახელის პროცედურა.
გამოიყენეთ IF EXISTS (SELECT 1) ნაცვლად IF EXISTS (SELECT *):
სხვა ცხრილში ჩანაწერის არსებობის შესამოწმებლად ვიყენებთ IF EXISTS განცხადებას. ეს გამოთქმააბრუნებს true თუ მინიმუმ ერთი მნიშვნელობა დაბრუნდა შიდა გამოსახულებიდან, არ აქვს მნიშვნელობა "1", ყველა სვეტი ან ცხრილი. დაბრუნებული მონაცემები ძირითადად არანაირად არ გამოიყენება. ამრიგად, მონაცემთა გადაცემის დროს ტრაფიკის შეკუმშვის მიზნით, უფრო ლოგიკურია გამოიყენოთ "1", როგორც ეს ნაჩვენებია ქვემოთ.
- შეისწავლეთ ოპერატორები, რომლებიც აღწერენ შენახულ პროცედურებს და მათი შეყვანისა და გამომავალი პარამეტრების გადაცემის პრინციპებს.
- ისწავლეთ როგორ შექმნათ და გამართოთ შენახული პროცედურები MS SQL Server 2000-ზე.
- ბიბლიოთეკის სასწავლო ბაზის ხუთი ძირითადი შენახული პროცედურის შემუშავება.
- შესრულებული სამუშაოს შესახებ ანგარიშის მომზადება ელექტრონული ფორმით.
1. შენახული პროცედურების გაგება
შენახვის პროცედურაეს არის სერვერზე შენახული ბრძანებების ნაკრები და შესრულებულია როგორც ერთი ერთეული. შენახული პროცედურები არის მექანიზმი, რომლითაც შეგიძლიათ შექმნათ რუტინები, რომლებიც მუშაობს სერვერზე და კონტროლდება მისი პროცესებით. ასეთი რუტინები შეიძლება გამოიძახოს აპლიკაციამ, რომელიც მათ უწოდებს. ისინი ასევე შეიძლება გამოწვეული იყოს მონაცემთა მთლიანობის წესებით ან ტრიგერით.
შენახულ პროცედურებს შეუძლიათ დააბრუნონ მნიშვნელობები. პროცედურას შეუძლია მომხმარებლის მიერ შეყვანილი მნიშვნელობების შედარება სისტემაში წინასწარ დაყენებულ ინფორმაციასთან. შენახული პროცედურები სარგებლობენ ძლიერი SQL Server ტექნიკის გადაწყვეტილებებით. ისინი მონაცემთა ბაზაზეა ორიენტირებული და მჭიდროდ მუშაობენ SQL Server ოპტიმიზატორთან. ეს საშუალებას გაძლევთ მიიღოთ მაღალი დონის შესრულებამონაცემთა დამუშავებისას.
თქვენ შეგიძლიათ გადასცეთ მნიშვნელობები შენახულ პროცედურებს და მიიღოთ შედეგები მათგან, რაც არ არის დაკავშირებული სამუშაო ფურცელთან. შენახულ პროცედურას შეუძლია შედეგების გამოთვლა გაშვებისას.
შენახული პროცედურების ორი ტიპი არსებობს: ჩვეულებრივიდა გაფართოებული. რეგულარული შენახული პროცედურები არის Transact-SQL ბრძანებების ნაკრები, ხოლო გაფართოებული შენახული პროცედურები წარმოდგენილია როგორც დინამიური ბმულის ბიბლიოთეკები (DLL). ასეთ პროცედურებს, ჩვეულებრივისგან განსხვავებით, აქვს პრეფიქსი xp_. სერვერს აქვს გაფართოებული პროცედურების სტანდარტული ნაკრები, მაგრამ მომხმარებლებს შეუძლიათ დაწერონ საკუთარი პროცედურები ნებისმიერ პროგრამირების ენაზე. მთავარია პროგრამირების ინტერფეისის გამოყენება SQL სერვერის გახსნამონაცემთა სერვისების API. გაფართოებული შენახული პროცედურები შეიძლება იყოს მხოლოდ Master მონაცემთა ბაზაში.
რეგულარული შენახული პროცედურები ასევე შეიძლება დაიყოს ორ ტიპად: სისტემურიდა საბაჟო. სისტემის პროცედურები ეს არის სტანდარტული პროცედურები, რომლებიც გამოიყენება სერვერის მუშაობისთვის; დააყენეთ მომხმარებლის მიერ შექმნილი ნებისმიერი პროცედურა.
1.1. შენახული პროცედურების უპირატესობები
ყველაზე ზოგად შემთხვევაში, შენახულ პროცედურებს აქვთ შემდეგი უპირატესობები:
- Მაღალი დონის შესრულება.არის სერვერზე შენახული პროცედურების ადგილმდებარეობის შედეგი. სერვერი, როგორც წესი, უფრო ძლიერი მანქანაა, ამიტომ სერვერზე პროცედურის შესრულების დრო მნიშვნელოვნად ნაკლებია ვიდრე სამუშაო სადგური. გარდა ამისა, მონაცემთა ბაზის ინფორმაცია და შენახული პროცედურა ერთსა და იმავე სისტემაშია, ასე რომ, ქსელში ჩანაწერების გადატანას მცირე დრო სჭირდება. შენახულ პროცედურებს აქვთ პირდაპირი წვდომა მონაცემთა ბაზებზე, რაც ინფორმაციას ძალიან აჩქარებს.
- კლიენტ-სერვერის არქიტექტურაში სისტემის განვითარების უპირატესობა.იგი მოიცავს კლიენტისა და სერვერის პროგრამული უზრუნველყოფის ცალკე შექმნის შესაძლებლობას. ეს უპირატესობა საკვანძოა განვითარებაში და შეუძლია მნიშვნელოვნად შეამციროს პროექტის დასრულებისთვის საჭირო დრო. სერვერზე გაშვებული კოდი შეიძლება განვითარდეს კლიენტის მხარის კოდისგან დამოუკიდებლად. ამ შემთხვევაში, სერვერის კომპონენტების გაზიარება შესაძლებელია კლიენტის მხარეს კომპონენტებთან.
- უსაფრთხოების დონე.შენახულ პროცედურებს შეუძლიათ იმოქმედონ როგორც უსაფრთხოების გაუმჯობესების ინსტრუმენტი. თქვენ შეგიძლიათ შექმნათ შენახული პროცედურები, რომლებიც ასრულებენ დამატების, რედაქტირების, წაშლის და სიის ჩვენების ოპერაციებს, რაც გაძლევს კონტროლს ინფორმაციის წვდომის ყველა ასპექტზე.
- სერვერის წესების გაძლიერება, რომლებიც მუშაობენ მონაცემებთან.ეს არის ერთ-ერთი ყველაზე მნიშვნელოვანი მიზეზებიინტელექტუალური მონაცემთა ბაზის ძრავის გამოყენება. შენახული პროცედურები საშუალებას გაძლევთ გამოიყენოთ წესები და სხვა ლოგიკა, რომელიც დაგეხმარებათ სისტემაში შეყვანილი ინფორმაციის კონტროლში.
მიუხედავად იმისა, რომ SQL განისაზღვრება, როგორც არაპროცედურული ენა, SQL Server იყენებს საკვანძო სიტყვებს, რომლებიც დაკავშირებულია პროცედურების ნაკადის მართვასთან. ასეთი საკვანძო სიტყვები გამოიყენება პროცედურების შესაქმნელად, რომლებიც შეიძლება შენახული იყოს შემდგომი შესრულებისთვის. შენახული პროცედურების გამოყენება შესაძლებელია პროგრამირების სტანდარტული ენების გამოყენებით შექმნილი პროგრამების ნაცვლად (მაგალითად, C ან Visual Basic) და ოპერაციების შესრულება მონაცემთა ბაზაში SQL მონაცემებისერვერი.
შენახული პროცედურები შედგენილია პირველად შესრულებისას და ინახება სისტემის ცხრილში მიმდინარე მონაცემთა ბაზაში. ისინი ოპტიმიზირებულია შედგენისას. ეს ირჩევს ცხრილის ინფორმაციაზე წვდომის საუკეთესო გზას. ეს ოპტიმიზაცია ითვალისწინებს ცხრილში მონაცემების რეალურ პოზიციას, ხელმისაწვდომ ინდექსებს, ცხრილის დატვირთვას და ა.შ.
შედგენილ შენახულ პროცედურებს შეუძლიათ მნიშვნელოვნად გააუმჯობესონ სისტემის მუშაობა. თუმცა, აღსანიშნავია, რომ მონაცემთა სტატისტიკა პროცედურის შექმნის მომენტიდან მის შესრულებამდე შეიძლება მოძველდეს და ინდექსები გახდეს არაეფექტური. მიუხედავად იმისა, რომ თქვენ შეგიძლიათ განაახლოთ სტატისტიკა და დაამატოთ ახალი, უფრო ეფექტური ინდექსები, პროცედურის შესრულების გეგმა უკვე დაწერილია, ანუ პროცედურა შედგენილია და შედეგად, მონაცემების წვდომის გზა შეიძლება აღარ იყოს ეფექტური. აქედან გამომდინარე, შესაძლებელია პროცედურების ხელახალი შედგენა ყოველ ჯერზე მათი გამოძახებისას.
მეორეს მხრივ, ხელახლა შედგენას ყოველ ჯერზე დრო დასჭირდება. ამიტომ, პროცედურის ხელახალი შედგენის ან მისი შესრულების გეგმის ერთბაშად შედგენის ეფექტურობის საკითხი საკმაოდ დელიკატურია და თითოეული კონკრეტული შემთხვევისთვის ცალკე უნდა განიხილებოდეს.
შენახული პროცედურები შეიძლება შესრულდეს როგორც ადგილობრივ აპარატზე, ასევე დისტანციურ SQL Server სისტემაზე. ეს შესაძლებელს ხდის პროცესების გააქტიურებას სხვა მანქანებზე და მუშაობა არა მხოლოდ ადგილობრივი მონაცემთა ბაზებიმონაცემები, არამედ ინფორმაცია რამდენიმე სერვერზე.
მაღალი დონის ენაზე დაწერილ აპლიკაციებს, როგორიცაა C ან Visual Basic .NET, ასევე შეუძლიათ შენახული პროცედურების გამოძახება, რაც უზრუნველყოფს ოპტიმალური დატვირთვის დაბალანსებას კლიენტის მხარეს და SQL სერვერის პროგრამულ უზრუნველყოფას შორის.
1.2. შენახული პროცედურების შექმნა
შენახული პროცედურის შესაქმნელად გამოიყენეთ Create Procedure განაცხადი. შენახული პროცედურის სახელი შეიძლება იყოს 128 სიმბოლომდე, მათ შორის # და # # სიმბოლოები. პროცედურის განსაზღვრის სინტაქსი:
CREATE PROC procedure_name [; ნომერი]
[(@data_type პარამეტრი) [= default_value] ] [,...n]
ას
<Инструкции_SQL>
მოდით შევხედოთ ამ ბრძანების პარამეტრებს:
- პროცედურის_სახელი პროცედურის სახელი; უნდა აკმაყოფილებდეს იდენტიფიკატორების წესებს: მისი სიგრძე არ უნდა აღემატებოდეს 128 სიმბოლოს; ადგილობრივი დროებითი პროცედურებისთვის სახელს წინ უძღვის # ნიშანი, ხოლო გლობალური დროებითი პროცედურებისთვის გამოიყენება ## ნიშნები;
- ნომერი არასავალდებულო მთელი რიცხვი, რომელიც გამოიყენება მრავალი პროცედურის დასაჯგუფებლად ერთი სახელის ქვეშ;
- @parameter data_typeპროცედურების პარამეტრების სახელების სია, სადაც მითითებულია თითოეულის შესაბამისი მონაცემთა ტიპი; NULL შეიძლება იყოს 2100-მდე ასეთი პარამეტრი. ყველა ტიპის მონაცემთა გამოყენება შესაძლებელია ტექსტის, ntext და გამოსახულების ტიპების გარდა. თქვენ შეგიძლიათ გამოიყენოთ კურსორის მონაცემთა ტიპი, როგორც გამომავალი პარამეტრი (OUTPUT ან VARYING საკვანძო სიტყვა). კურსორის მონაცემთა ტიპის პარამეტრები შეიძლება იყოს მხოლოდ გამომავალი პარამეტრები;
- VARYING საკვანძო სიტყვა, რომელიც განსაზღვრავს, რომ შედეგების ნაკრები გამოიყენება გამომავალ პარამეტრად (გამოიყენება მხოლოდ კურსორის ტიპისთვის);
- OUTPUT მიუთითებს, რომ მითითებული პარამეტრი შეიძლება გამოყენებულ იქნას როგორც გამოსავალი;
- ნაგულისხმევი_მნიშვნელობაგამოიყენება პროცედურის გამოძახებისას პარამეტრის გამოტოვებისას; უნდა იყოს მუდმივი და შეიძლება შეიცავდეს სიმბოლოებს (%, _, [, ], ^) და NULL მნიშვნელობას;
- WITH RECOMPILE საკვანძო სიტყვებით, რომლებიც მიუთითებს იმაზე, რომ SQL Server არ ჩაწერს პროცედურის გეგმას ქეშში, მაგრამ შექმნის მას ყოველი შესრულებისას;
- დაშიფვრის საკვანძო სიტყვებით, რომლებიც მიუთითებს იმაზე, რომ SQL Server დაშიფვრავს პროცედურას Syscomments სისტემის ცხრილში ჩაწერამდე. იმისათვის, რომ დაშიფრული პროცედურების ტექსტის აღდგენა შეუძლებელი იყოს, დაშიფვრის შემდეგ აუცილებელია შესაბამისი ტოპების ამოღება syscomments ცხრილიდან;
- FOR REPLICATION-ისთვის საკვანძო სიტყვები მიუთითებს, რომ ეს პროცედურა შექმნილია მხოლოდ რეპლიკაციისთვის. ეს ვარიანტი არ არის თავსებადი WITH RECOMPILE საკვანძო სიტყვებთან;
- AS პროცედურის ტექსტის განმარტების დასაწყისი;
- <Инструкции_SQL>სწორი SQL განცხადებების ნაკრები, შეზღუდული მხოლოდ შენახული პროცედურის მაქსიმალური ზომით 128 KB. შემდეგი განცხადებები არასწორია: ALTER DATABASE, ALTER PROCEDURE, ALTER TABLE, CREATE DEFAULT, CREATE PROCEDURE, ALTER TRIGGER, ALTER VIEW, CREATE DATABASE, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE TRIGGER, DATA BASE DROP ნაგულისხმევი, ჩამოშვების პროცედურა, ჩამოშვების წესი, ჩამოშვების ტრიგერი, DROP VIEW, RESOTRE მონაცემთა ბაზა, აღდგენა ჟურნალი, ხელახლა კონფიგურაცია, სტატისტიკის განახლება.
მოდით შევხედოთ შენახული პროცედურის მაგალითს. მოდით განვავითაროთ შენახული პროცედურა, რომელიც ითვლის და აჩვენებს იმ წიგნების რაოდენობას, რომლებიც ამჟამად ბიბლიოთეკაშია:
CREATE პროცედურის რაოდენობა_Ex1
- წიგნების ასლების რაოდენობის დათვლის პროცედურა,
- ამჟამად ბიბლიოთეკაში,
- და არა მკითხველის ხელში
როგორც
-- დააყენეთ დროებითი ლოკალური ცვლადი
გამოაცხადეთ @N int
აირჩიეთ @N = count(*) Exemplar-დან Where Yes_No = "1"
აირჩიეთ @N
წადი
ვინაიდან შენახული პროცედურა არის მონაცემთა ბაზის სრულფასოვანი კომპონენტი, მაშინ, როგორც უკვე მიხვდით, შეგიძლიათ შექმნათ ახალი პროცედურა მხოლოდ მიმდინარე მონაცემთა ბაზისთვის. SQL Server Query Analyzer-ში მუშაობისას, მიმდინარე მონაცემთა ბაზის დაყენება ხდება Use განაცხადით, რასაც მოჰყვება მონაცემთა ბაზის სახელი, სადაც უნდა შეიქმნას შენახული პროცედურა. თქვენ ასევე შეგიძლიათ აირჩიოთ მიმდინარე მონაცემთა ბაზა ჩამოსაშლელი სიის გამოყენებით.
სისტემაში შენახული პროცედურის შექმნის შემდეგ, SQL Server ადგენს მას და ამოწმებს შესრულებულ რუტინებს. თუ რაიმე პრობლემა წარმოიქმნება, პროცედურა უარყოფილია. ხელახალი გადაცემამდე შეცდომები უნდა გამოსწორდეს.
SQL Server 2000 იყენებს სახელის დაგვიანებულ გარჩევადობას სახელის გარჩევადობა), ასე რომ, თუ შენახული პროცედურა შეიცავს ზარს სხვა პროცედურაზე, რომელიც ჯერ არ არის განხორციელებული, მაშინ გამოჩნდება გაფრთხილება, მაგრამ ზარი არარსებულ პროცედურაზე შენარჩუნებულია.
თუ დაუტოვებთ ზარს სისტემაში დეინსტალირებულ შენახულ პროცედურას, მომხმარებელი მიიღებს შეცდომის შეტყობინებას, როდესაც ისინი შეეცდებიან მის შესრულებას.
თქვენ ასევე შეგიძლიათ შექმნათ შენახული პროცედურა SQL-ის გამოყენებითსერვერის საწარმო მენეჯერი:
შექმნილი შენახული პროცედურის ფუნქციონირების შესამოწმებლად, თქვენ უნდა შეხვიდეთ Query Analyzer-ზე და გაუშვათ ოპერატორის მიერ შესრულების პროცედურა. EXEC<имя процедуры> . ჩვენ მიერ შექმნილი პროცედურის შესრულების შედეგები ნაჩვენებია ნახ. 4.
ბრინჯი. 4. შენახული პროცედურის გაშვება Query Analyzer-ში
ბრინჯი. 5. ჩვენების ოპერატორის გარეშე პროცედურის შესრულების შედეგი
1.3. შენახული პროცედურის პარამეტრები
შენახული პროცედურები ძალიან მძლავრი ინსტრუმენტია, მაგრამ მაქსიმალური ეფექტურობის მიღწევა შესაძლებელია მხოლოდ მათი დინამიური გახდომით. დეველოპერს უნდა შეეძლოს გადასცეს შენახული პროცედურის მნიშვნელობები, რომლებთანაც ის იმუშავებს, ანუ პარამეტრები. ქვემოთ მოცემულია პარამეტრების გამოყენების ძირითადი პრინციპები შენახულ პროცედურებში.
- თქვენ შეგიძლიათ განსაზღვროთ ერთი ან მეტი პარამეტრი პროცედურისთვის.
- პარამეტრები გამოიყენება როგორც დასახელებული ადგილები მონაცემების შესანახად, ისევე როგორც ცვლადები პროგრამირების ენებში, როგორიცაა C, Visual Basic .NET.
- პარამეტრის სახელს წინ უნდა უძღოდეს @ სიმბოლო.
- პარამეტრების სახელები ადგილობრივია იმ პროცედურისთვის, სადაც ისინი განისაზღვრება.
- პარამეტრები გამოიყენება პროცედურისთვის ინფორმაციის გადასაცემად მისი შესრულებისას. გაგიჟდებიან ბრძანების ხაზიპროცედურის შემდეგ სახელი.
- თუ პროცედურას აქვს რამდენიმე პარამეტრი, ისინი გამოყოფილია მძიმეებით.
- პარამეტრად გადაცემული ინფორმაციის ტიპის დასადგენად გამოიყენება სისტემის ან მომხმარებლის მონაცემების ტიპები.
ქვემოთ მოცემულია პროცედურის განმარტება, რომელსაც აქვს ერთი შეყვანის პარამეტრი. შევცვალოთ წინა დავალება და დავთვალოთ არა წიგნების ყველა ეგზემპლარი, არამედ მხოლოდ კონკრეტული წიგნის ასლები. ჩვენი წიგნები ცალსახად იდენტიფიცირებულია უნიკალური ISBN-ით, ამიტომ ამ პარამეტრს გადავცემთ პროცედურას. ამ შემთხვევაში, შენახული პროცედურის ტექსტი შეიცვლება და ასე გამოიყურება:
შექმენით პროცედურა Count_Ex(@ISBN varchar(14))
როგორც
გამოაცხადეთ @N int
აირჩიეთ @N
წადი
როდესაც ამ პროცედურას ვიწყებთ შესასრულებლად, მას უნდა მივცეთ შეყვანის პარამეტრის მნიშვნელობა (ნახ. 6).
ბრინჯი. 6. პროცედურის დაწყება პარამეტრის გადაცემით
ერთი და იმავე პროცედურის მრავალი ვერსიის შესაქმნელად, რომლებსაც აქვთ იგივე სახელი, მიჰყევით საბაზისო სახელს მძიმით და მთელი რიცხვით. როგორ გავაკეთოთ ეს ნაჩვენებია შემდეგ მაგალითში, რომელიც აღწერს, თუ როგორ უნდა შექმნათ ორი პროცედურა ერთი და იგივე სახელით, მაგრამ სხვადასხვა ნომრებივერსიები (1 და 2). ნომერი გამოიყენება ამ პროცედურის რომელი ვერსიის შესრულების გასაკონტროლებლად. თუ ვერსიის ნომერი არ არის მითითებული, პროცედურის პირველი ვერსია შესრულდება. ეს პარამეტრი არ არის ნაჩვენები წინა მაგალითში, მაგრამ მაინც ხელმისაწვდომია თქვენი აპლიკაციისთვის.
ორივე პროცედურა იყენებს ბეჭდურ განცხადებას ვერსიის იდენტიფიცირების შეტყობინების დასაბეჭდად. პირველი ვერსია ითვლის უფასო ასლების რაოდენობას, ხოლო მეორე ასლების რაოდენობას მოცემული წიგნისთვის.
პროცედურების ორივე ვერსიის ტექსტი მოცემულია ქვემოთ:
CREATE პროცედურის რაოდენობა_Ex_all; 1
(@ISBN varchar(14))
-- მოცემული წიგნის უფასო ასლების დათვლის პროცედურა
როგორც
გამოაცხადეთ @N int
აირჩიეთ @N = count(*) Exemplar-დან, სადაც ISBN = @ISBN და Yes_No = "1"
აირჩიეთ @N
--
წადი
--
CREATE პროცედურის რაოდენობა_Ex_all; 2
(@ISBN varchar(14))
-- მოცემული წიგნის უფასო ასლების დათვლის პროცედურა
როგორც
გამოაცხადეთ @N1 int
აირჩიეთ @N1 = count(*) Exemplar-დან, სადაც ISBN = @ISBN და Yes_No = "0"
აირჩიეთ @N1
წადი
სხვადასხვა ვერსიით პროცედურის ჩატარების შედეგები ნაჩვენებია ნახ. 7.
ბრინჯი. 7. გაშვების შედეგები სხვადასხვა ვერსიებიიგივე შენახული პროცედურა
მრავალი ვერსიის დაწერისას გაითვალისწინეთ შემდეგი შეზღუდვები: ვინაიდან პროცედურის ყველა ვერსია შედგენილია ერთად, ყველა ლოკალური ცვლადი ითვლება გაზიარებულად. ამიტომ, თუ ამას დამუშავების ალგორითმი მოითხოვს, საჭიროა შიდა ცვლადების სხვადასხვა სახელების გამოყენება, რაც გავაკეთეთ ცვლადის @N გამოძახებით მეორე პროცედურაში სახელით @N1.
ჩვენ მიერ დაწერილი პროცედურები არ აბრუნებს არც ერთ პარამეტრს, ისინი უბრალოდ აჩვენებს მიღებულ რიცხვს ეკრანზე. თუმცა, ყველაზე ხშირად ჩვენ გვჭირდება პარამეტრის მიღება შემდგომი დამუშავებისთვის. შენახული პროცედურისგან პარამეტრების დაბრუნების რამდენიმე გზა არსებობს. უმარტივესი არის RETURN ოპერატორის გამოყენება. ეს ოპერატორი დააბრუნებს ერთს რიცხვითი მნიშვნელობა. მაგრამ ჩვენ უნდა მივუთითოთ ცვლადის სახელი ან გამოხატულება, რომელიც მინიჭებულია დაბრუნების პარამეტრზე. შემდეგი არის RETURN განაცხადის მიერ დაბრუნებული მნიშვნელობები და დაცულია სისტემის მიერ:
| კოდი | მნიშვნელობა |
| 0 | Ყველაფერი კარგადაა |
| 1 | ობიექტი ვერ მოიძებნა |
| 2 | მონაცემთა ტიპის შეცდომა |
| 3 | პროცესი ჩიხში შევიდა |
| 4 | წვდომის შეცდომა |
| 5 | Სინტაქსური შეცდომა |
| 6 | რაღაც შეცდომა |
| 7 | შეცდომა რესურსებთან (სივრცის გარეშე) |
| 8 | მოხდა აღდგენის შიდა შეცდომა |
| 9 | მიღწეულია სისტემის ლიმიტი |
| 10 | შინაგანი მთლიანობის გამოუსწორებელი დარღვევა |
| 11 | Იგივე |
| 12 | ცხრილის ან ინდექსის განადგურება |
| 13 | მონაცემთა ბაზის განადგურება |
| 14 | აპარატურის შეცდომა |
ამრიგად, იმისათვის, რომ სისტემას არ დავუპირისპირდეთ, ამ პარამეტრის მეშვეობით შეგვიძლია მხოლოდ დადებითი მთელი რიცხვების დაბრუნება.
მაგალითად, ჩვენ შეგვიძლია შევცვალოთ ადრე დაწერილი შენახული პროცედურის Count_ex ტექსტი შემდეგნაირად:
შექმენით პროცედურების რაოდენობა_Ex2(@ISBN varchar(14))
როგორც
გამოაცხადეთ @N int
აირჩიეთ @N = count(*) Exemplar-დან
სადაც ISBN = @ISBN და YES_NO = "1"
-- დააბრუნეთ @N ცვლადის მნიშვნელობა,
-- თუ ცვლადის მნიშვნელობა არ არის განსაზღვრული, დააბრუნეთ 0
Return Coalesce (@N, 0)
წადი
ახლა ჩვენ შეგვიძლია მივიღოთ @N ცვლადის მნიშვნელობა და გამოვიყენოთ შემდგომი დამუშავებისთვის. ამ შემთხვევაში, დაბრუნების მნიშვნელობა ენიჭება თავად შენახულ პროცედურას და მისი გასაანალიზებლად შეგიძლიათ გამოიყენოთ შემდეგი შენახული პროცედურის გამოძახების განცხადების ფორმატი:
აღმასრულებელი<переменная> = <имя_процедуры> <значение_входных_параметров>
ჩვენი პროცედურის გამოძახების მაგალითი ნაჩვენებია ნახ. 8.
ბრინჯი. 8. შენახული პროცედურის დაბრუნების მნიშვნელობის ლოკალურ ცვლადზე გადაცემა
შენახული პროცედურის შეყვანის პარამეტრებს შეუძლიათ გამოიყენონ ნაგულისხმევი მნიშვნელობა. ეს მნიშვნელობა გამოყენებული იქნება, თუ პარამეტრის მნიშვნელობა არ იყო მითითებული პროცედურის გამოძახებისას.
ნაგულისხმევი მნიშვნელობა მითითებულია ტოლობის ნიშნის გამოყენებით შეყვანის პარამეტრისა და მისი ტიპის აღწერის შემდეგ. განვიხილოთ შენახული პროცედურა, რომელიც ითვლის გამოცემის მოცემული წლის წიგნების ასლების რაოდენობას. გამოშვების ნაგულისხმევი წელია 2006.
CREATE PROCEDURE ex_books_now (@year int = 2006)
-- გამოცემის მოცემული წლის წიგნების ასლების რაოდენობის დათვლა
ას
გამოაცხადეთ @N_books int
აირჩიეთ @N_books = count(*) წიგნებიდან, სამაგალითო
სადაც Books.ISBN = exemplar.ISBN და YEARIZD = @year
დაბრუნების გაერთიანება (@N_books, 0)
წადი
ნახ. სურათი 9 გვიჩვენებს ამ პროცედურის გამოძახების მაგალითს შეყვანის პარამეტრის მითითებით და მის გარეშე.
ბრინჯი. 9. შენახული პროცედურის გამოძახება პარამეტრით და მის გარეშე
შენახულ პროცედურებში პარამეტრების გამოყენების ყველა ზემოთ მოყვანილი მაგალითი მხოლოდ შეყვანის პარამეტრებს შეიცავს. თუმცა, პარამეტრები შეიძლება ასევე იყოს გამომავალი. ეს ნიშნავს, რომ პარამეტრის მნიშვნელობა, პროცედურის დასრულების შემდეგ, გადაეცემა იმ პირს, ვინც გამოიძახა პროცედურა (სხვა პროცედურა, ტრიგერი, ბრძანებების ჯგუფი და ა.შ.). ბუნებრივია, გამომავალი პარამეტრის მისაღებად, გამოძახებისას თქვენ უნდა მიუთითოთ არა მუდმივი, არამედ ცვლადი, როგორც ფაქტობრივი პარამეტრი.
გაითვალისწინეთ, რომ პარამეტრის, როგორც გამომავალი პარამეტრის განსაზღვრა პროცედურაში არ ავალდებულებს თქვენ გამოიყენოთ იგი, როგორც ასეთი. ანუ, თუ თქვენ მიუთითებთ მუდმივას, როგორც ფაქტობრივ პარამეტრს, მაშინ შეცდომა არ მოხდება და ის გამოყენებული იქნება როგორც ნორმალური შეყვანის პარამეტრი.
იმის საჩვენებლად, რომ პარამეტრი არის გამომავალი, გამოიყენება OUTPUT განცხადება. ეს საკვანძო სიტყვა იწერება პარამეტრის აღწერილობის შემდეგ. შენახული პროცედურების პარამეტრების აღწერისას, მიზანშეწონილია მიუთითოთ გამომავალი პარამეტრების მნიშვნელობები შეყვანის შემდეგ.
მოდით შევხედოთ გამომავალი პარამეტრების გამოყენების მაგალითს. დავწეროთ შენახული პროცედურა, რომელიც მოცემული წიგნისთვის ითვლის ბიბლიოთეკაში მისი ასლების მთლიან რაოდენობას და უფასო ასლების რაოდენობას. ჩვენ ვერ გამოვიყენებთ RETURN განცხადებას აქ, რადგან ის აბრუნებს მხოლოდ ერთ მნიშვნელობას, ამიტომ აქ უნდა განვსაზღვროთ გამომავალი პარამეტრები. შენახული პროცედურის ტექსტი შეიძლება ასე გამოიყურებოდეს:
შექმენით პროცედურა Count_books_all
(@ISBN varchar(14), @all int output, @free int output)
-- მოცემული წიგნის ასლების საერთო რაოდენობის დათვლის პროცედურა
-- და უფასო ასლების რაოდენობა
როგორც
-- ასლების საერთო რაოდენობის დათვლა
აირჩიეთ @all = count(*) Exemplar-დან Where ISBN = @ISBN
აირჩიეთ @free = count(*) Exemplar-დან Where ISBN = @ISBN და Yes_No = "1"
წადი
ამ პროცედურის მაგალითი ნაჩვენებია ნახ. 10.
ბრინჯი. 10. შენახული პროცედურის ტესტირება გამომავალი პარამეტრებით
როგორც უკვე აღვნიშნეთ, ანალიზისთვის გამომავალი პარამეტრების მნიშვნელობების მისაღებად, ჩვენ უნდა დავაყენოთ ისინი ცვლადებზე და ეს ცვლადები უნდა იყოს აღწერილი Declare ოპერატორის მიერ. ბოლო გამომავალი განცხადება საშუალებას გვაძლევს უბრალოდ დაბეჭდოთ მიღებული მნიშვნელობები ეკრანზე.
პროცედურის პარამეტრები შეიძლება იყოს კურსორის ტიპის ცვლადებიც კი. ამისათვის ცვლადი უნდა იყოს აღწერილი, როგორც მონაცემთა სპეციალური ტიპი VARYING, სტანდარტულთან დაკავშირების გარეშე. სისტემის ტიპებიმონაცემები. გარდა ამისა, უნდა აღინიშნოს, რომ ეს არის კურსორის ტიპის ცვლადი.
მოდით დავწეროთ მარტივი პროცედურა, რომელიც აჩვენებს ჩვენს ბიბლიოთეკის წიგნების სიას. უფრო მეტიც, თუ არ არის სამზე მეტი წიგნი, მაშინ ჩვენ ვაჩვენებთ მათ სახელებს თავად პროცედურის ფარგლებში და თუ წიგნების სია აღემატება მითითებულ რაოდენობას, მაშინ მათ გადავცემთ კურსორად დარეკვის პროგრამას ან მოდულს.
პროცედურის ტექსტი ასე გამოიყურება:
GET3TITLES-ის პროცედურის შექმნა
(@MYCURSOR კურსორი VARYING OUTPUT)
-- კურსორით წიგნის სათაურების დაბეჭდვის პროცედურა
ას
-- განსაზღვრეთ ადგილობრივი ტიპის ცვლადიკურსორი პროცედურაშია
SET @MYCURSOR = კურსორი
განსხვავებული სათაურის არჩევისთვის
წიგნებიდან
-- გახსენით კურსორი
გახსენით @MYCURSOR
-- აღწერეთ შიდა ლოკალური ცვლადები
გამოაცხადეთ @TITLE VARCHAR(80), @CNT INT
--- დააყენეთ წიგნის მრიცხველის საწყისი მდგომარეობა
დააყენეთ @CNT = 0
-- გადადით კურსორის პირველ ხაზზე
-- სანამ არის კურსორის ხაზები,
-- ანუ გადასვლისას ახალი ხაზისწორი
WHILE (@@FETCH_STATUS = 0) და (@CNT<= 2) BEGIN
PRINT @TITLE
მიიღეთ შემდეგი @MYCURSOR-დან @TITLE-ში
-- შეცვალეთ წიგნების მრიცხველის მდგომარეობა
დააყენეთ @CNT = @CNT + 1
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
IF @CNT = 0 PRINT "არ შესატყვისი წიგნები"
წადი
ამ შენახული პროცედურის გამოძახების მაგალითი ნაჩვენებია ნახ. თერთმეტი.
გამოძახების პროცედურაში კურსორი უნდა გამოცხადდეს ლოკალურ ცვლადად. შემდეგ ჩვენ დავარქვით ჩვენს პროცედურას და გადავეცით კურსორის ტიპის ლოკალური ცვლადის სახელი. პროცედურამ დაიწყო მუშაობა და ეკრანზე აჩვენა პირველი სამი სახელი, შემდეგ კი კონტროლი გადაიტანა გამოძახების პროცედურაზე და განაგრძო კურსორის დამუშავება. ამისათვის მან მოაწყო while ციკლი გლობალური ცვლადის @@FETCH_STATUS გამოყენებით, რომელიც აკონტროლებს კურსორის მდგომარეობას, შემდეგ კი მარყუჟში აჩვენა კურსორის ყველა სხვა ხაზი.
გამომავალ ფანჯარაში ჩვენ ვხედავთ გაზრდილ ინტერვალს პირველ სამ ხაზსა და მომდევნო სათაურებს შორის. ეს ინტერვალი უბრალოდ აჩვენებს, რომ კონტროლი გადაეცა გარე პროგრამას.
გაითვალისწინეთ, რომ @TITLE ცვლადი, რომელიც არის პროცედურის ლოკალური, განადგურდება, როდესაც ის დასრულდება, ამიტომ იგი კვლავ გამოცხადდება ბლოკში, რომელიც იძახებს პროცედურას. ამ მაგალითში კურსორის შექმნა და გახსნა ხდება პროცედურაში, ხოლო დახურვა, განადგურება და დამატებითი დამუშავება ხორციელდება ბრძანების ბლოკში, რომელშიც პროცედურაა გამოძახებული.
პროცედურის ტექსტის სანახავად, მისი შეცვლის ან წაშლის უმარტივესი გზაა Enterprise Manager გრაფიკული ინტერფეისის გამოყენება. მაგრამ თქვენ ასევე შეგიძლიათ ამის გაკეთება სპეციალური Transact-SQL სისტემაში შენახული პროცედურების გამოყენებით. Transact-SQL-ში შეგიძლიათ ნახოთ პროცედურის განმარტება sp_helptext სისტემის პროცედურის გამოყენებით და შეგიძლიათ გამოიყენოთ sp_help სისტემის პროცედურა პროცედურის შესახებ საკონტროლო ინფორმაციის საჩვენებლად. sp_helptext და sp_help სისტემის პროცედურები ასევე გამოიყენება მონაცემთა ბაზის ობიექტების სანახავად, როგორიცაა ცხრილები, წესები და ნაგულისხმევი პარამეტრები.
ინფორმაცია ერთი პროცედურის ყველა ვერსიის შესახებ, ნომრის მიუხედავად, დაუყოვნებლივ გამოჩნდება. ამავე შენახული პროცედურის სხვადასხვა ვერსიის წაშლა ასევე ხდება ერთდროულად. შემდეგი მაგალითი გვიჩვენებს, თუ როგორ იბეჭდება Count_Ex_all ვერსიის 1 და 2 ვერსიის განმარტებები, როდესაც მისი სახელი მითითებულია sp_helptext სისტემის პროცედურის პარამეტრად (სურათი 12).
ბრინჯი. 12. შენახული პროცედურის ტექსტის ნახვა სისტემაში შენახული პროცედურის გამოყენებით
სისტემის პროცედურა SP_HELP აჩვენებს შექმნილი პროცედურის მახასიათებლებს და პარამეტრებს შემდეგი ფორმით:
|
Created_datetime | |||||
|
2006-12-06 23:15:01.217 | |||||
|
მასშტაბი | Param_order Collation | ||||
|
NULL | 1 | კირილიცა_ზოგადი_CI_AS | |||
|
0 | 2 | NULL | |||
|
0 | 3 | NULL | |||
შეეცადეთ თავად გაშიფროთ ეს პარამეტრები. რაზე ლაპარაკობენ?
1.4. შენახული პროცედურის შედგენა
Transact-SQL განცხადებების ნაკრების შესასრულებლად შენახული პროცედურების გამოყენების უპირატესობა არის ის, რომ ისინი შედგენილია პირველად შესრულებისას. შედგენის პროცესში, Transact-SQL განცხადებები გარდაიქმნება მათი ორიგინალური სიმბოლური წარმოდგენიდან შესრულებად ფორმაში. პროცედურაში წვდომის ნებისმიერი ობიექტი ასევე გარდაიქმნება ალტერნატიულ წარმომადგენლობაში. მაგალითად, ცხრილის სახელები გარდაიქმნება ობიექტების იდენტიფიკატორებად, ხოლო სვეტების სახელები გარდაიქმნება სვეტების იდენტიფიკატორებად.
შესრულების გეგმა იქმნება ისევე, როგორც ერთი Transact-SQL განაცხადის შესასრულებლად. ეს გეგმა შეიცავს, მაგალითად, ინდექსებს, რომლებიც გამოიყენება პროცედურით წვდომის ცხრილებიდან რიგების წასაკითხად. პროცედურის შესრულების გეგმა ინახება ქეშში და გამოიყენება ყოველი გამოძახებისას.
შენიშვნა: პროცედურის ქეშის ზომა შეიძლება შეიცავდეს შესასრულებლად ხელმისაწვდომი პროცედურების უმეტესობას ან ყველა. ეს დაზოგავს დროს საჭირო პროცედურის გეგმის რეგენერაციას.
1.5. ავტომატური ხელახალი კომპილაცია
როგორც წესი, შესრულების გეგმა განლაგებულია პროცედურის ქეშში. ეს საშუალებას გაძლევთ გაზარდოთ მისი შესრულების შესრულება. თუმცა, ზოგიერთ შემთხვევაში პროცედურა ავტომატურად ხელახლა შედგენილია.
- პროცედურა ყოველთვის ხელახლა კომპილირებულია SQL Server-ის დაწყებისას. ეს ჩვეულებრივ ხდება მას შემდეგ, რაც ოპერაციული სისტემა გადაიტვირთება და პროცედურა პირველად შესრულდება შექმნის შემდეგ.
- პროცედურის შესრულების გეგმა ყოველთვის ავტომატურად ხელახლა კომპილირებულია, თუ პროცედურის მიერ წვდომის მაგიდაზე ინდექსი ამოიშლება. იმის გამო, რომ მიმდინარე გეგმა წვდება იმ ინდექსს, რომელიც აღარ არსებობს ცხრილის რიგების წასაკითხად, უნდა შეიქმნას ახალი შესრულების გეგმა. პროცედურის მოთხოვნები შესრულდება მხოლოდ განახლების შემთხვევაში.
- შესრულების გეგმის შედგენა ასევე ხდება, თუ სხვა მომხმარებელი ამჟამად მუშაობს ამ გეგმასთან, რომელიც მდებარეობს ქეშში. მეორე მომხმარებლისთვის იქმნება შესრულების გეგმის ინდივიდუალური ასლი. თუ გეგმის პირველი ეგზემპლარი დაკავებული არ იქნებოდა, არ იქნებოდა საჭირო მეორე ასლის შექმნა. როდესაც მომხმარებელი ასრულებს პროცედურას, შესრულების გეგმა ხელმისაწვდომია ქეშში სხვა მომხმარებლისთვის, რომელსაც აქვს შესაბამისი წვდომის ნებართვა.
- პროცედურა ავტომატურად ხელახლა კომპილირებულია, თუ ის წაიშლება და ხელახლა შეიქმნა. იმის გამო, რომ ახალი პროცედურა შეიძლება განსხვავდებოდეს ძველი ვერსიისგან, ქეშში არსებული შესრულების გეგმის ნებისმიერი ასლი წაიშლება და გეგმა ხელახლა შედგენილია.
SQL Server ცდილობს შენახული პროცედურების ოპტიმიზაციას ყველაზე ხშირად გამოყენებული პროცედურების ქეშირებით. ამიტომ, ქეშში ჩატვირთული ძველი შესრულების გეგმა შეიძლება გამოყენებულ იქნას ახალი გეგმის ნაცვლად. ამ პრობლემის თავიდან ასაცილებლად, თქვენ უნდა წაშალოთ და ხელახლა შექმნათ შენახული პროცედურა, ან შეაჩეროთ და გადატვირთოთ SQL Server. ეს გაასუფთავებს პროცედურის ქეშს და აღმოფხვრის ძველი შესრულების გეგმასთან მუშაობის შესაძლებლობას.
პროცედურის შექმნა ასევე შესაძლებელია WITH RECOMPILE ოფციით. ამ შემთხვევაში, ის ავტომატურად ხელახლა კომპილირებული იქნება ყოველ ჯერზე, როდესაც ის შესრულდება. WITH RECOMPILE ვარიანტი უნდა იყოს გამოყენებული იმ შემთხვევებში, როდესაც პროცედურა წვდება ძალიან დინამიურ ცხრილებს, რომელთა რიგები ხშირად ემატება, იშლება ან განახლდება, რადგან ეს იწვევს ცხრილებზე განსაზღვრულ ინდექსებში მნიშვნელოვან ცვლილებებს.
თუ პროცედურები ავტომატურად არ შედგენილია, შეგიძლიათ აიძულოთ ისინი ამის გაკეთება. მაგალითად, თუ სტატისტიკა, რომელიც გამოიყენება იმის დასადგენად, შესაძლებელია თუ არა ინდექსის გამოყენება მოცემულ მოთხოვნაში, განახლდება, ან თუ შეიქმნა ახალი ინდექსი, უნდა განხორციელდეს იძულებითი ხელახალი კომპილაცია. ხელახალი კომპილაციის იძულებისთვის გამოიყენეთ WITH RECOMPILE პუნქტი EXECUTE განცხადებაში:
EXECUTE procedure_name;
ას
<инструкции Transact-SQL>
გადაკეთებით
თუ პროცედურა მუშაობს პარამეტრებით, რომლებიც აკონტროლებენ მისი შესრულების თანმიმდევრობას, უნდა გამოიყენოთ WITH RECOMPILE ვარიანტი. თუ შენახული პროცედურის პარამეტრებს შეუძლიათ განსაზღვრონ საუკეთესო გზა მისი შესასრულებლად, რეკომენდებულია მისი გაშვებისას შექმნათ შესრულების გეგმა, ვიდრე პირველად გამოიძახოთ პროცედურა ყველა შემდგომ ზარში გამოსაყენებლად.
შენიშვნა: ზოგჯერ რთულია იმის დადგენა, გამოიყენო თუ არა ოპცია WITH RECOMPILE პროცედურის შექმნისას. თუ ეჭვი გეპარებათ, უმჯობესია არ გამოიყენოთ ეს პარამეტრი, რადგან პროცედურის ხელახალი შედგენა ყოველ ჯერზე დახარჯავს CPU-ს ძალიან ძვირფას დროს. თუ მომავალში დაგჭირდებათ შენახული პროცედურის ხელახლა შედგენა, ამის გაკეთება შეგიძლიათ WITH RECOMPILE პუნქტის დამატებით EXECUTE განცხადებაში.
თქვენ არ შეგიძლიათ გამოიყენოთ WITH RECOMPILE ოფცია CREATE PROCEDURE განცხადებაში, რომელიც შეიცავს FOR REPLICATION ვარიანტს. გამოიყენეთ ეს პარამეტრი რეპლიკაციის პროცესში გაშვებული პროცედურის შესაქმნელად.
1.6. შენახული პროცედურების ბუდე
შენახულ პროცედურებს შეუძლიათ სხვა შენახული პროცედურების გამოძახება, მაგრამ არსებობს შეზღუდვა ბუდეების დონეზე. მობუდვის მაქსიმალური დონეა 32. ამჟამინდელი ბუდის დონე შეიძლება განისაზღვროს @@NESTLEVEL გლობალური ცვლადის გამოყენებით.
2. მომხმარებლის განსაზღვრული ფუნქციები (UDF)
MS SQL SERVER 2000-ს აქვს მრავალი წინასწარ განსაზღვრული ფუნქცია, რომელიც საშუალებას გაძლევთ შეასრულოთ სხვადასხვა მოქმედებები. თუმცა, შეიძლება ყოველთვის იყოს საჭირო გარკვეული ფუნქციების გამოყენება. ამისათვის, 8.0 (2000) ვერსიიდან დაწყებული, შესაძლებელი გახდა მომხმარებლის განსაზღვრული ფუნქციების (UDF) აღწერა და მათი შენახვა, როგორც მონაცემთა ბაზის სრულფასოვანი ობიექტი, შენახულ პროცედურებთან, ხედებთან და ა.შ.
მომხმარებლის მიერ განსაზღვრული ფუნქციების გამოყენების მოხერხებულობა აშკარაა. შენახული პროცედურებისგან განსხვავებით, ფუნქციები შეიძლება იყოს ჩასმული პირდაპირ SELECT განცხადებაში და შეიძლება გამოყენებულ იქნას როგორც კონკრეტული მნიშვნელობების მისაღებად (SELECT პუნქტში) და როგორც მონაცემთა წყარო (FROM პუნქტში).
UDF-ების მონაცემთა წყაროდ გამოყენებისას, მათი უპირატესობა ხედებთან შედარებით არის ის, რომ UDF-ებს, ხედებისგან განსხვავებით, შეიძლება ჰქონდეთ შეყვანის პარამეტრები, რომლებიც შეიძლება გამოყენებულ იქნას ფუნქციის შედეგზე ზემოქმედებისთვის.
მომხმარებლის მიერ განსაზღვრული ფუნქციები შეიძლება იყოს სამი ტიპის: სკალარული ფუნქციები, შიდა ფუნქციებიდა მრავალ განცხადების ფუნქციები, რომლებიც აბრუნებენ ცხრილის შედეგს. მოდით უფრო დეტალურად განვიხილოთ ყველა ამ ტიპის ფუნქცია.
2.1. სკალარული ფუნქციები
სკალარული ფუნქციები აბრუნებს ერთ სკალარ შედეგს. ეს შედეგი შეიძლება იყოს ზემოთ აღწერილი ნებისმიერი ტიპი, გარდა ტექსტის, ntext, სურათისა და დროის ანაბეჭდის ტიპებისა. ეს არის უმარტივესი ტიპის ფუნქცია. მისი სინტაქსი ასეთია:
RETURNS scalar_data_type
დასაწყისი
სხეულის_ფუნქცია
RETURN scalar_expression
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
- ENCRYPTION პარამეტრი უკვე აღწერილია შენახული პროცედურების განყოფილებაში;
- SCHEMABINDING აკავშირებს ფუნქციას სქემასთან. ეს ნიშნავს, რომ თქვენ არ შეგიძლიათ წაშალოთ ცხრილები ან ხედები, რომლებზეც დაფუძნებულია ფუნქცია თავად ფუნქციის წაშლის ან შეცვლის გარეშე. თქვენ ასევე არ შეგიძლიათ შეცვალოთ ამ ცხრილების სტრუქტურა, თუ შეცვლილი ნაწილი გამოიყენება ფუნქციით. ამრიგად, ეს პარამეტრი საშუალებას გაძლევთ აღმოფხვრათ სიტუაციები, როდესაც ფუნქცია იყენებს ზოგიერთ ცხრილს ან ხედს და ვინმემ, ამის ცოდნის გარეშე, წაშალა ან შეცვალა ისინი;
- RETURNS scalar_data_typeაღწერს მონაცემთა ტიპს, რომელსაც აბრუნებს ფუნქცია;
- სკალარული_გამოხატვაგამოხატულება, რომელიც პირდაპირ აბრუნებს ფუნქციის შედეგს. ის უნდა იყოს იგივე ტიპის, რაც აღწერილია დაბრუნების შემდეგ;
- function_body კომპლექტი Transact-SQL ინსტრუქციები.
მოდით შევხედოთ სკალარული ფუნქციების გამოყენების მაგალითებს.
შექმენით ფუნქცია, რომელიც შეარჩევს უმცირესს ორი მთელი რიცხვიდან, რომლებიც შეყვანილია პარამეტრებად.
დაე, ფუნქცია ასე გამოიყურებოდეს:
CREATE FUNCTION min_num(@a INT, @b INT)
RETURNS INT
დასაწყისი
გამოაცხადეთ @c INT
Თუ< @b SET @c = @a
ELSE SET @c = @b
დაბრუნება @c
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
მოდით ახლა შევასრულოთ ეს ფუნქცია:
აირჩიეთ dbo.min_num(4, 7)
შედეგად, ჩვენ ვიღებთ მნიშვნელობას 4.
თქვენ შეგიძლიათ გამოიყენოთ ეს ფუნქცია ცხრილის სვეტის უმცირესი მნიშვნელობების მოსაძებნად:
აირჩიეთ min_lvl, max_lvl, min_num(min_lvl, max_lvl)
სამუშაო ადგილებიდან
მოდით შევქმნათ ფუნქცია, რომელიც მიიღებს თარიღის ტიპის პარამეტრს შეყვანის სახით და დააბრუნებს მითითებული დღის დასაწყისს შესაბამის თარიღსა და დროს. მაგალითად, თუ შეყვანის პარამეტრი არის 09.20.03 13:31, მაშინ შედეგი იქნება 09.20.03 00:00.
CREATE FUNCTION dbo.daybegin(@dat DATETIME)
ბრუნდება smalldatetime AS
დასაწყისი
RETURN CONVERT (თარიღის დრო, FLOOR (კონვერტირება (FLOAT, @dat)))
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
აქ CONVERT ფუნქცია ასრულებს ტიპის კონვერტაციას. პირველი, თარიღის დროის ტიპი გადადის FLOAT-ზე. ამ შემცირებით, მთელი ნაწილი არის 1900 წლის 1 იანვრიდან დათვლილი დღეების რაოდენობა, ხოლო წილადი არის დრო. შემდეგი, ის მრგვალდება პატარა რიცხვამდე FLOOR ფუნქციის გამოყენებით და გარდაიქმნება თარიღის დროის ტიპად.
მოდით შევამოწმოთ ფუნქცია:
აირჩიეთ dbo.daybegin(GETDATE())
აქ GETDATE() არის ფუნქცია, რომელიც აბრუნებს მიმდინარე თარიღსა და დროს.
წინა ფუნქციები გამოთვლებში იყენებდნენ მხოლოდ შეყვანის პარამეტრებს. თუმცა, თქვენ ასევე შეგიძლიათ გამოიყენოთ მონაცემთა ბაზაში შენახული მონაცემები.
მოდით შევქმნათ ფუნქცია, რომელიც პარამეტრად მიიღებს ორ თარიღს: დროის ინტერვალის დასაწყისს და დასასრულს და გამოვთვალოთ გაყიდვების მთლიანი შემოსავალი ამ ინტერვალისთვის. გაყიდვის თარიღი და რაოდენობა აღებული იქნება გაყიდვების ცხრილიდან, ხოლო გაყიდული სათაურების ფასები აღებული იქნება სათაურების ცხრილიდან.
CREATE FUNCTION dbo.SumSales(@datebegin DATETIME, @dateend DATETIME)
აბრუნებს ფულს
ას
დასაწყისი
გამოაცხადე @Sum Money
SELECT @Sum = sum (t.price * s.qty)
დაბრუნება @ჯამ
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
2.2. Inline ფუნქციები
ამ ტიპის ფუნქციის შედეგად აბრუნებს არა სკალარული მნიშვნელობა, არამედ ცხრილი, უფრო სწორად მონაცემთა ნაკრები. ეს შეიძლება იყოს ძალიან მოსახერხებელი იმ შემთხვევებში, როდესაც ერთი და იგივე ტიპის ქვემოთხოვნა ხშირად სრულდება სხვადასხვა პროცედურებში, ტრიგერებში და ა.შ. ამის შემდეგ, ამ შეკითხვის ყველგან დაწერის ნაცვლად, შეგიძლიათ შექმნათ ფუნქცია და გამოიყენოთ იგი მომავალში.
ამ ტიპის ფუნქციები კიდევ უფრო სასარგებლოა იმ შემთხვევებში, როდესაც გსურთ დაბრუნებული ცხრილი დამოკიდებული იყოს შეყვანის პარამეტრებზე. მოგეხსენებათ, ხედებს არ შეიძლება ჰქონდეს პარამეტრები, ამიტომ მხოლოდ inline ფუნქციებს შეუძლიათ ამ სახის პრობლემის გადაჭრა.
inline ფუნქციების თავისებურება ის არის, რომ მათ შეუძლიათ შეიცავდეს მხოლოდ ერთი მოთხოვნა მათ სხეულში. ამრიგად, ამ ტიპის ფუნქციები ძალიან ჰგავს ხედებს, მაგრამ შეიძლება დამატებით ჰქონდეს შეყვანის პარამეტრები. ინლაინ ფუნქციის სინტაქსი:
CREATE FUNCTION [მფლობელი.]ფუნქციის_სახელი
([(@parameter_name scalar_data_type [= default_value]) [, n]])
დაბრუნების მაგიდა
ᲓᲐᲑᲠᲣᲜᲔᲑᲘᲡ [(<запрос>)]
ფუნქციის განსაზღვრაში ნათქვამია, რომ ის დააბრუნებს ცხრილს;<запрос>ეს არის მოთხოვნა, რომლის შედეგი იქნება ფუნქციის შედეგი.
მოდით დავწეროთ სკალარული ფუნქციის მსგავსი ფუნქცია ბოლო მაგალითიდან, მაგრამ დავაბრუნოთ არა მხოლოდ შეჯამების შედეგი, არამედ გაყიდვების რიგები, მათ შორის გაყიდვის თარიღი, წიგნის სათაური, ფასი, ცალი რაოდენობა და გაყიდვის თანხა. უნდა შეირჩეს მხოლოდ ის გაყიდვები, რომლებიც მიეკუთვნება მოცემულ პერიოდში. მოდით დავშიფროთ ფუნქციის ტექსტი ისე, რომ სხვა მომხმარებლებმა შეძლონ მისი გამოყენება, მაგრამ ვერ წაიკითხონ და შეასწორონ:
CREATE FUNCTION Sales_Period (@datebegin DATETIME, @dateend DATETIME)
დაბრუნების მაგიდა
დაშიფვრით
ას
ᲓᲐᲑᲠᲣᲜᲔᲑᲘᲡ (
აირჩიეთ t.title, t.price, s.qty, ord_date, t.price * s.qty როგორც stoim
სათაურებიდან t JOIN გაყიდვები ON t.title_Id = s.Title_ID
WHERE ord_date BETWEEN @datebegin-სა და @dateend-ს შორის
)
ახლა დავარქვათ ეს ფუნქცია. როგორც უკვე აღვნიშნეთ, მისი გამოძახება შესაძლებელია მხოლოდ SELECT განცხადების FROM პუნქტში:
SELECT * FROM Sales_Period("09/01/94", "09/13/94")
2.3. მრავალგანცხადების ფუნქციები, რომლებიც აბრუნებენ ცხრილის შედეგს
განხილულმა ფუნქციების პირველმა ტიპმა დაუშვა იმდენი Transact-SQL განცხადების გამოყენება, რამდენიც სასურველია, მაგრამ დააბრუნა მხოლოდ სკალარული შედეგი. მეორე ტიპის ფუნქციას შეუძლია ცხრილების დაბრუნება, მაგრამ მისი სხეული მხოლოდ ერთ მოთხოვნას წარმოადგენს. მრავალ განცხადების ფუნქციები, რომლებიც აბრუნებენ ცხრილის შედეგს, საშუალებას გაძლევთ დააკავშიროთ პირველი ორი ფუნქციის თვისებები, ანუ მათ შეუძლიათ შეიცავდეს ბევრ Transact-SQL განცხადებას სხეულში და შედეგად დააბრუნონ ცხრილი. მრავალგანცხადების ფუნქციის სინტაქსი:
CREATE FUNCTION [მფლობელი.]ფუნქციის_სახელი
([(@parameter_name scalar_data_type [= default_value]) [,... n]])
RETURNS @result ცვლადის სახელი TABLE
<описание_таблицы>
დასაწყისი
<тело_функции>
ᲓᲐᲑᲠᲣᲜᲔᲑᲘᲡ
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
- მაგიდა<описание_таблицы> აღწერს დაბრუნებული ცხრილის სტრუქტურას;
- <описание_таблицы> შეიცავს სვეტებისა და შეზღუდვების ჩამონათვალს.
ახლა მოდით შევხედოთ მაგალითს, რომელიც შეიძლება გაკეთდეს მხოლოდ ამ ტიპის ფუნქციების გამოყენებით.
დაე, იყოს მათში არსებული დირექტორიებისა და ფაილების ხე. დაე, მთელი ეს სტრუქტურა იყოს აღწერილი მონაცემთა ბაზაში ცხრილების სახით (ნახ. 13). არსებითად, აქ ჩვენ გვაქვს დირექტორიების იერარქიული სტრუქტურა, ამიტომ დიაგრამა აჩვენებს Folders ცხრილის ურთიერთობას თავისთან.
ბრინჯი. 13. მონაცემთა ბაზის სტრუქტურა ფაილების და დირექტორიების იერარქიის აღსაწერად
ახლა მოდით დავწეროთ ფუნქცია, რომელიც მიიღებს დირექტორიას იდენტიფიკატორს შეყვანად და გამოაქვს ყველა ფაილი, რომელიც ინახება მასში და ყველა დირექტორიაში იერარქიის ქვემოთ. მაგალითად, თუ დირექტორიები Faculty1, Faculty2 და ა.შ. იქმნება ინსტიტუტის დირექტორიაში, ისინი შეიცავს დეპარტამენტის დირექტორიებს და თითოეული დირექტორია შეიცავს ფაილებს, მაშინ როდესაც ჩვენ მივუთითებთ ინსტიტუტის დირექტორიის იდენტიფიკატორს, როგორც ჩვენი ფუნქციის პარამეტრს, ყველა ფაილის სიას. ყველა ამ დირექტორიისთვის. თითოეული ფაილისთვის უნდა იყოს ნაჩვენები სახელი, ზომა და შექმნის თარიღი.
პრობლემის გადაჭრა შეუძლებელია inline ფუნქციის გამოყენებით, რადგან SQL არ არის შექმნილი იერარქიული მოთხოვნების შესასრულებლად, ამიტომ ერთი SQL მოთხოვნა არ არის საკმარისი. სკალარული ფუნქციის გამოყენებაც არ შეიძლება, რადგან შედეგი უნდა იყოს ცხრილი. აქ დაგვეხმარება მრავალ განცხადების ფუნქცია, რომელიც აბრუნებს ცხრილს:
CREATE FUNCTION dbo.GetFiles(@Folder_ID int)
RETURNS @files TABLE(სახელი VARCHAR(100), Date_Create DATETIME, FileSize INT) AS
დასაწყისი
გამოაცხადეთ @tmp TABLE(Folder_Id int)
გამოაცხადეთ @Cnt INT
ჩასმა @tmp მნიშვნელობებში (@Folder_ID)
SET @Cnt = 1
სანამ @Cnt<>0 დასაწყისი
INSERT INTO @tmp SELECT Folder_Id
FROM საქაღალდეებიდან f JOIN @tmp t ON f.parent=t.Folder_ID
WHERE F.id NOT IN (SELECT Folder_ID FROM @tmp)
SET @Cnt = @@ROWCOUNT
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
ჩასმა @Files-ში (სახელი, შექმნის_თარიღი, ფაილის ზომა)
აირჩიეთ F.Name, F.Date_Create, F.FileSize
FROM ფაილებიდან f JOIN საქაღალდეები Fl on f.Folder_id = Fl.id
შეუერთდით @tmp t Fl.id = t.Folder_Id-ზე
ᲓᲐᲑᲠᲣᲜᲔᲑᲘᲡ
ᲓᲐᲡᲐᲡᲠᲣᲚᲘ
აქ, მარყუჟში, ყველა ქვედირექტორია ყველა ბუდე დონეზე ემატება @tmp ცვლადს, სანამ აღარ დარჩება ქვედირექტორიები. @Files შედეგის ცვლადი შემდეგ ჩაწერს @tmp ცვლადში ჩამოთვლილ დირექტორიაში მდებარე ფაილების ყველა საჭირო ატრიბუტს.
ამოცანები დამოუკიდებელი მუშაობისთვის
თქვენ უნდა შექმნათ და გამართოთ ხუთი შენახული პროცედურა შემდეგი საჭირო სიიდან:
პროცედურა 1. წიგნის ეგზემპლარების გაგზავნის ვადის გაზრდა ერთი კვირით, თუ ვადა მდგომარეობს მიმდინარე თარიღამდე სამი დღით ადრე მიმდინარე თარიღიდან სამ დღემდე.
პროცედურა 2. მოცემული წიგნის უფასო ასლების რაოდენობის დათვლა.
პროცედურა 3. მოცემული გვარისა და დაბადების თარიღის მქონე მკითხველის არსებობის შემოწმება.
პროცედურა 4. ახალი მკითხველის შეყვანა, მონაცემთა ბაზაში მისი არსებობის შემოწმება და ახალი ბიბლიოთეკის ბარათის ნომრის დადგენა.
პროცედურა 5. ჯარიმების გამოთვლა მოვალე მკითხველისთვის.
პროცედურების მოკლე აღწერა
პროცედურა 1. წიგნების მიწოდების ვადის გაზრდა
Exemplar ცხრილის თითოეული ჩანაწერისთვის მოწმდება, ემთხვევა თუ არა წიგნის ვადა მითითებულ დროის ინტერვალში. თუ ასეა, წიგნის დაბრუნების თარიღი ერთი კვირით გაგრძელდება. პროცედურის შესრულებისას უნდა გამოიყენოთ ფუნქცია თარიღებთან მუშაობისთვის:
თარიღის დამატება (დღე,<число добавляемых дней>, <начальная дата>)
პროცედურა 2. მოცემული წიგნის უფასო ასლების რაოდენობის დათვლა
პროცედურის შეყვანის პარამეტრი არის ISBN წიგნის უნიკალური შიფრი. პროცედურა აბრუნებს 0 (ნულს), თუ ამ წიგნის ყველა ეგზემპლარი მკითხველის ხელშია. პროცედურა აბრუნებს მნიშვნელობას N, რაც უდრის წიგნის იმ ასლების რაოდენობას, რომლებიც ამჟამად მკითხველთა ხელშია.
თუ წიგნი მოცემული ISBN-ით არ არის ბიბლიოთეკაში, მაშინ პროცედურა აბრუნებს 100-ს (მინუს ასი).
პროცედურა 3. მოცემული გვარისა და დაბადების თარიღის მქონე მკითხველის არსებობის შემოწმება
პროცედურა აბრუნებს ბიბლიოთეკის ბარათის ნომერს, თუ ასეთი მონაცემების მქონე მკითხველი არსებობს, ხოლო 0 (ნული) წინააღმდეგ შემთხვევაში.
დაბადების თარიღის შედარებისას, თქვენ უნდა გამოიყენოთ Convert() კონვერტაციის ფუნქცია დაბადების თარიღის გადასაყვანად, Varchar(8) სიმბოლოს ცვლადი, რომელიც გამოიყენება პროცედურის შეყვანის პარამეტრად, მონაცემთა დროში, რომელიც გამოიყენება Readers ცხრილში. წინააღმდეგ შემთხვევაში, შედარების ოპერაცია მოცემული მკითხველის ძიებისას არ იმუშავებს.
პროცედურა 4: ახალი მკითხველის შეყვანა
პროცედურას აქვს ხუთი შეყვანის და სამი გამომავალი პარამეტრი.
შეყვანის პარამეტრები:
- სრული სახელი ინიციალებით;
- მისამართი;
- Დაბადების თარიღი;
- Სახლის ტელეფონი;
- ტელეფონი მუშაობს.
გამომავალი პარამეტრები:
- ბიბლიოთეკის ბარათის ნომერი;
- მითითება, იყო თუ არა მკითხველი ადრე რეგისტრირებული ბიბლიოთეკაში (0 არ იყო, 1 იყო);
- წიგნების რაოდენობა, რომელსაც მკითხველი ფლობს.
პროცედურა 5. ჯარიმების გამოთვლა მოვალე მკითხველისთვის
პროცედურა მუშაობს კურსორით, რომელიც შეიცავს ყველა მოვალის ბიბლიოთეკის ბარათის ნომრების ჩამონათვალს. სამუშაო პროცესის დროს უნდა შეიქმნას გლობალური დროებითი ცხრილი ##DOLG, რომელშიც თითოეულ მოვალეზე ჩაიწერება მისი მთლიანი დავალიანება ფულადი თვალსაზრისით ყველა იმ წიგნზე, რომელიც დაბრუნების ვადაზე მეტ ხანს ფლობდა. ფულადი კომპენსაცია გამოითვლება თითო წიგნის ფასის 0,5%-ით დაგვიანების დღეზე.
სამუშაო შეკვეთა
- მონაცემთა ბაზებში განხორციელებული ცვლილებების დამადასტურებელი ეკრანების (სკრინშოტების) ასლები;
- მონაცემთა ბაზის ცხრილების შიგთავსი, რომელიც საჭიროა სწორი მუშაობის შესამოწმებლად;
- შენახული პროცედურის ტექსტი კომენტარებით;
- შენახული პროცედურის გაშვების პროცესი შედეგების გამოტანით.
დამატებითი დავალებები
შემდეგი დამატებითი შენახული პროცედურები განკუთვნილია ინდივიდუალური სამუშაოებისთვის.
პროცედურა 6. მოცემულ საგნობრივ სფეროზე წიგნების რაოდენობის დათვლა, რომლებიც ამჟამად ხელმისაწვდომია ბიბლიოთეკაში სულ მცირე ერთ ეგზემპლარად. საგნის არე გადაეცემა შეყვანის პარამეტრად.
პროცედურა 7. ახალი წიგნის შეყვანა ასლების რაოდენობის მითითებით. ახალი წიგნის ასლების შეყვანისას, დარწმუნდით, რომ შეიყვანეთ მათი სწორი ნომრები. იფიქრეთ იმაზე, თუ როგორ შეგიძლიათ ამის გაკეთება. შეგახსენებთ, რომ თქვენ გაქვთ Max და Min ფუნქციები, რომლებიც საშუალებას გაძლევთ იპოვოთ ნებისმიერი რიცხვითი ატრიბუტის მაქსიმალური ან მინიმალური მნიშვნელობა Select query-ის გამოყენებით.
პროცედურა 8. ცხრილის ფორმირება მოვალე მკითხველთა სიით, ანუ მათ, ვისაც უნდა დაებრუნებინა წიგნები ბიბლიოთეკაში, მაგრამ ჯერ არ დაუბრუნებია. მიღებულ ცხრილში თითოეული მოვალე მკითხველი უნდა გამოჩნდეს მხოლოდ ერთხელ, მიუხედავად იმისა, თუ რამდენი წიგნი აქვს მას. გარდა თქვენი სრული სახელისა და ბიბლიოთეკის ბარათის ნომრისა, თქვენ უნდა მიუთითოთ თქვენი მისამართი და ტელეფონის ნომერი მიღებულ ცხრილში.
პროცედურა 9. მოიძიეთ უფასო ასლი მოცემული წიგნის სათაურით. თუ არსებობს უფასო ასლი, პროცედურა აბრუნებს ასლის ინვენტარის ნომერს; თუ არა, მაშინ პროცედურა აბრუნებს მკითხველთა სიას, რომლებსაც აქვთ ეს წიგნი, სადაც მითითებულია წიგნის დაბრუნების თარიღი და მკითხველის ტელეფონის ნომერი.
პროცედურა 10. იმ მკითხველთა სიის ჩვენება, რომლებსაც ამჟამად არცერთი წიგნი არ აქვთ ხელში. გთხოვთ, სიაში მიუთითოთ თქვენი სახელი და ტელეფონის ნომერი.
პროცედურა 11. წიგნების სიის ჩვენება ბიბლიოთეკაში მოცემული წიგნის ასლების რაოდენობისა და ამ დროისთვის უფასო ასლების რაოდენობის მითითებით.
ბეჭდური ვერსია
Microsoft SQL Server-ში საკუთარი ალგორითმების დანერგვა და ავტომატიზაცია ( გამოთვლები) შეგიძლიათ გამოიყენოთ შენახული პროცედურები, ამიტომ დღეს ვისაუბრებთ იმაზე, თუ როგორ იქმნება, შეცვლილია და წაიშლება.
მაგრამ ჯერ ცოტა თეორია, რათა გაიგოთ, რა არის შენახული პროცედურები და რატომ არის ისინი საჭირო T-SQL-ში.
Შენიშვნა! დამწყები პროგრამისტებისთვის გირჩევთ შემდეგ სასარგებლო მასალებს T-SQL-ზე:
- T-SQL ენის უფრო დეტალური შესწავლისთვის ასევე გირჩევთ წაიკითხოთ წიგნი - The T-SQL Programmer's Path. ტუტორიალი Transact-SQL ენაზე;
- პროფესიონალური ონლაინ კურსები T-SQL-ზე
რა არის შენახული პროცედურები T-SQL-ში?
შენახული პროცედურები– ეს არის მონაცემთა ბაზის ობიექტები, რომლებიც შეიცავს ალგორითმს SQL ინსტრუქციების ნაკრების სახით. სხვა სიტყვებით რომ ვთქვათ, შეგვიძლია ვთქვათ, რომ შენახული პროცედურები არის პროგრამები მონაცემთა ბაზაში. შენახული პროცედურები გამოიყენება სერვერზე მრავალჯერადი გამოყენების კოდის შესანახად, მაგალითად, თქვენ დაწერეთ გარკვეული ალგორითმი, თანმიმდევრული გაანგარიშება ან მრავალსაფეხურიანი SQL განცხადება და იმისათვის, რომ არ შესრულდეს ამ ალგორითმში შემავალი ყველა ინსტრუქცია ყოველ ჯერზე, შეგიძლიათ მისი ფორმატირება. როგორც შენახული პროცედურა. ამავდროულად, როდესაც თქვენ ქმნით SQL პროცედურას, სერვერი ადგენს კოდს და შემდეგ, ყოველ ჯერზე, როდესაც თქვენ აწარმოებთ ამ SQL პროცედურას, სერვერი არ შეასრულებს მას.
SQL Server-ში შენახული პროცედურის გასაშვებად, თქვენ უნდა დაწეროთ EXECUTE ბრძანება მის სახელამდე, ასევე შესაძლებელია ამ ბრძანების შემოკლება როგორც EXEC. შენახული პროცედურის გამოძახება SELECT განცხადებაში, მაგალითად, ფუნქციის სახით აღარ იმუშავებს, ე.ი. პროცედურები ცალკე იწყება.
შენახულ პროცედურებში, ფუნქციებისგან განსხვავებით, უკვე შესაძლებელია მონაცემთა მოდიფიკაციის ოპერაციების შესრულება, როგორიცაა: UNSERT, UPDATE, DELETE. თქვენ ასევე შეგიძლიათ გამოიყენოთ თითქმის ნებისმიერი ტიპის SQL განცხადებები პროცედურებში, მაგალითად, CREATE TABLE ცხრილების შესაქმნელად ან EXECUTE, ე.ი. სხვა პროცედურების გამოძახება. გამონაკლისს წარმოადგენს რამდენიმე ტიპის ინსტრუქცია, როგორიცაა: ფუნქციების, ხედების, ტრიგერების შექმნა ან შეცვლა, სქემების შექმნა და სხვა მსგავსი ინსტრუქციები, მაგალითად, თქვენ ასევე არ შეგიძლიათ გადართოთ მონაცემთა ბაზის კავშირის კონტექსტი (USE) შენახულ პროცედურაში.
შენახულ პროცედურას შეიძლება ჰქონდეს შეყვანის პარამეტრები და გამომავალი პარამეტრები, მას შეუძლია დააბრუნოს ცხრილის მონაცემები, ან ვერ დააბრუნოს არაფერი, მხოლოდ შეასრულოს მასში შემავალი ინსტრუქციები.
შენახული პროცედურები ძალიან სასარგებლოა, ისინი გვეხმარება მრავალი ოპერაციის ავტომატიზაციაში ან გამარტივებაში, მაგალითად, თქვენ მუდმივად გჭირდებათ სხვადასხვა რთული ანალიტიკური ანგარიშების გენერირება pivot ცხრილების გამოყენებით, ე.ი. PIVOT ოპერატორი. ამ ოპერატორთან მოთხოვნების ფორმულირების გასაადვილებლად ( მოგეხსენებათ, PIVOT-ის სინტაქსი საკმაოდ რთულია), შეგიძლიათ დაწეროთ პროცედურა, რომელიც დინამიურად გამოიმუშავებს შემაჯამებელ ანგარიშებს, მაგალითად, მასალა „Dynamic PIVOT in T-SQL“ იძლევა ამ ფუნქციის განხორციელების მაგალითს შენახული პროცედურის სახით.
Microsoft SQL Server-ში შენახულ პროცედურებთან მუშაობის მაგალითები
წყაროს მონაცემები მაგალითებისთვის
ქვემოთ მოყვანილი ყველა მაგალითი გაშვებული იქნება Microsoft SQL Server 2016 Express-ში. იმისათვის, რომ ვაჩვენოთ, როგორ მუშაობს შენახული პროცედურები რეალურ მონაცემებთან, ჩვენ გვჭირდება ეს მონაცემები, მოდით შევქმნათ იგი. მაგალითად, შევქმნათ სატესტო ცხრილი და დავამატოთ რამდენიმე ჩანაწერი, ვთქვათ, რომ ეს იქნება პროდუქციის ჩამონათვალის შემცველი ცხრილი მათი ფასებით.
ცხრილის შექმნის ინსტრუქცია CREATE TABLE TestTable( INT IDENTITY(1,1) NOT NULL, INT NOT NULL, VARCHAR(100) NOT NULL, MONEY NULL) GO -- მონაცემების დამატების ინსტრუქცია INSERT INTO TestTable(CategoryId, ProductName, VALUES (1 , "მაუსი", 100), (1, "კლავიატურა", 200), (2, "ტელეფონი", 400) GO --აირჩიეთ მოთხოვნა SELECT * FROM TestTable
ჩვენ გვაქვს მონაცემები, ახლა გადავიდეთ შენახული პროცედურების შექმნაზე.
T-SQL-ში შენახული პროცედურის შექმნა - CREATE PROCEDURE განაცხადი
შენახული პროცედურები იქმნება განცხადების გამოყენებით პროცედურების შექმნა, ამ ინსტრუქციის შემდეგ თქვენ უნდა დაწეროთ თქვენი პროცედურის დასახელება, შემდეგ, საჭიროების შემთხვევაში, განსაზღვროთ შეყვანის და გამომავალი პარამეტრების ფრჩხილებში. ამის შემდეგ თქვენ წერთ საკვანძო სიტყვას AS და ხსნით ინსტრუქციების ბლოკს საკვანძო სიტყვით BEGIN, დახურეთ ეს ბლოკი სიტყვით END. ამ ბლოკის შიგნით თქვენ წერთ ყველა ინსტრუქციას, რომელიც ახორციელებს თქვენს ალგორითმს ან რაიმე სახის თანმიმდევრულ გამოთვლას, სხვა სიტყვებით რომ ვთქვათ, თქვენ დაპროგრამებთ T-SQL-ში.
მაგალითად, დავწეროთ შენახული პროცედურა, რომელიც დაამატებს ახალ ჩანაწერს, ე.ი. ახალი პროდუქტი ჩვენს სატესტო მაგიდაზე. ამისთვის განვსაზღვრავთ შეყვანის სამ პარამეტრს: @CategoryId – პროდუქტის კატეგორიის იდენტიფიკატორი, @ProductName – პროდუქტის სახელი და @Price – პროდუქტის ფასი იქნება სურვილისამებრ, ე.ი. არ იქნება საჭირო პროცედურაზე გადატანა ( მაგალითად, ჩვენ ჯერ არ ვიცით ფასი), ამ მიზნით ჩვენ დავაყენებთ ნაგულისხმევ მნიშვნელობას მის განმარტებაში. ეს პარამეტრები არის პროცედურის სხეულში, ე.ი. BEGIN...END ბლოკში შეიძლება გამოყენებულ იქნას, ისევე როგორც ჩვეულებრივი ცვლადები ( მოგეხსენებათ, ცვლადები აღინიშნება @ ნიშნით). თუ თქვენ გჭირდებათ გამომავალი პარამეტრების მითითება, მაშინ პარამეტრის სახელის შემდეგ მიუთითეთ საკვანძო სიტყვა OUTPUT ( ან მოკლედ OUT).
BEGIN...END ბლოკში ჩვენ დავწერთ ინსტრუქციას მონაცემების დამატების შესახებ, ასევე SELECT ინსტრუქციას პროცედურის დასასრულს, რათა შენახული პროცედურა დაგვიბრუნოს ტაბულურ მონაცემებს მითითებული კატეგორიის პროდუქტების შესახებ. დაარეგისტრირეთ ახალი, ახლახან დამატებული პროდუქტი. ასევე ამ შენახულ პროცედურაში, მე დავამატე შემომავალი პარამეტრის დამუშავება, კერძოდ, ტექსტის სტრიქონის დასაწყისში და ბოლოს ზედმეტი ადგილების ამოღება, რათა აღმოვფხვრა სიტუაციები, როდესაც რამდენიმე სივრცე შემთხვევით იყო შეყვანილი.
აქ არის კოდი ამ პროცედურისთვის ( მეც გავაკეთე კომენტარი).
შექმენით პროცედურა CREATE PROCEDURE TestProcedure (--შეყვანის პარამეტრები @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY = 0) დასაწყისიდან -- ინსტრუქციები, რომლებიც ახორციელებენ თქვენს ალგორითმს -- შემომავალი პარამეტრების დამუშავება -- თავიდან ზედმეტი ადგილების ამოღება და ტექსტის ხაზის ბოლოს SET @ProductName = LTRIM(RTRIM(@ProductName)); --დაამატე ახალი ჩანაწერი INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) --დააბრუნე მონაცემები SELECT * FROM TestTable WHERE CategoryId = @CategoryId END GO
შენახული პროცედურის გაშვება T-SQL - EXECUTE ბრძანებაში
შეგიძლიათ შენახული პროცედურის გაშვება, როგორც უკვე აღვნიშნე, EXECUTE ან EXEC ბრძანების გამოყენებით. შემომავალი პარამეტრები გადაეცემა პროცედურებს მათი უბრალოდ ჩამოთვლით და პროცედურის სახელის შემდეგ შესაბამისი მნიშვნელობების მითითებით ( გამომავალი პარამეტრებისთვის თქვენ ასევე უნდა მიუთითოთ OUTPUT ბრძანება). თუმცა, პარამეტრების სახელები შეიძლება არ იყოს მითითებული, მაგრამ ამ შემთხვევაში აუცილებელია მნიშვნელობების მითითების თანმიმდევრობის დაცვა, ე.ი. მიუთითეთ მნიშვნელობები იმ თანმიმდევრობით, რომლითაც განისაზღვრება შეყვანის პარამეტრები ( ეს ასევე ეხება გამომავალი პარამეტრებს).
პარამეტრებს, რომლებსაც აქვთ ნაგულისხმევი მნიშვნელობები, არ სჭირდებათ მითითება, ეს არის ე.წ.
აქ მოცემულია რამდენიმე განსხვავებული, მაგრამ ექვივალენტური გზა შენახული პროცედურების გასატარებლად, კონკრეტულად ჩვენი ტესტის პროცედურისთვის.
1. დარეკეთ პროცედურა ფასის მითითების გარეშე EXECUTE TestProcedure @CategoryId = 1, @ProductName = "სატესტო პროდუქტი 1" --2. დარეკეთ პროცედურას ფასის მითითებით EXEC TestProcedure @CategoryId = 1, @ProductName = "სატესტო პროდუქტი 2", @Price = 300 --3. პროცედურის გამოძახება პარამეტრების სახელის მითითების გარეშე EXEC TestProcedure 1, "Test product 3", 400
შენახული პროცედურის შეცვლა T-SQL - ALTER PROCEDURE განცხადებაში
თქვენ შეგიძლიათ ცვლილებები შეიტანოთ პროცედურის ალგორითმში ინსტრუქციის გამოყენებით შეცვალეთ პროცედურა. სხვა სიტყვებით რომ ვთქვათ, იმისათვის, რომ შეცვალოთ უკვე არსებული პროცედურა, თქვენ უბრალოდ უნდა დაწეროთ ALTER PROCEDURE ნაცვლად CREATE PROCEDURE და შეცვალოთ ყველაფერი საჭიროებისამებრ.
ვთქვათ, ჩვენ უნდა შევიტანოთ ცვლილებები ჩვენი ტესტირების პროცედურაში, ვთქვათ @Price პარამეტრი, ე.ი. ფასი, ჩვენ მას სავალდებულო გავხდით, ამისათვის ჩვენ წავშლით ნაგულისხმევ მნიშვნელობას და ასევე წარმოვიდგენთ, რომ აღარ გვჭირდება მიღებული მონაცემების ნაკრების მიღება, ამისათვის ჩვენ უბრალოდ ამოვიღებთ SELECT განცხადებას შენახული პროცედურისგან.
ჩვენ ვცვლით პროცედურას ALTER PROCEDURE TestProcedure (--შემომავალი პარამეტრები @CategoryId INT, @ProductName VARCHAR(100), @Price MONEY) როგორც დასაწყისიდან - ინსტრუქციები, რომლებიც ახორციელებს თქვენს ალგორითმს -შემავალი პარამეტრების დამუშავება -დასაწყისში ზედმეტი ადგილების ამოღება და ტექსტის ხაზების ბოლოს SET @ProductName = LTRIM(RTRIM(@ProductName)); --დაამატეთ ახალი ჩანაწერი INSERT INTO TestTable(CategoryId, ProductName, Price) VALUES (@CategoryId, @ProductName, @Price) END GO
შენახული პროცედურის წაშლა T-SQL - DROP PROCEDURE განცხადებაში
საჭიროების შემთხვევაში, შეგიძლიათ წაშალოთ შენახული პროცედურა, ეს კეთდება ინსტრუქციის გამოყენებით ჩამოშვების პროცედურა.
მაგალითად, წავშალოთ ჩვენ მიერ შექმნილი ტესტის პროცედურა.
ვარდნის პროცედურა ტესტიპროცედურა
შენახული პროცედურების წაშლისას უნდა გვახსოვდეს, რომ თუ პროცედურა მითითებულია სხვა პროცედურებით ან SQL განცხადებებით, მისი წაშლის შემდეგ ისინი შეცდომით ვერ შევლენ, რადგან პროცედურა, რომელსაც მათ მიმართავენ, აღარ არსებობს.
სულ ეს მაქვს, იმედია მასალა თქვენთვის საინტერესო და სასარგებლო იყო, ნახვამდის!
