dhrd.agri-jahad.ir
Download
Report
Transcript dhrd.agri-jahad.ir
جلسه یازدهم
Microsoft SQL Server 2005 - Implementation and Maintenance
زبان "کنترل جریان"
زبان کنترل جریان بخشی از دستورات T-SQLرا در بر می گیرد که جریان اجرای دستورات را در یک بلوک
دستورات ،یک User Defined Functionو یا یک Stored Procedureبر عهده می گیرد .
بدون استفاده از دستورات این زبان ،دستورات زبان T-SQLبه صورت ترتیبی و پشت سرهم اجرا می شوند.
با استفاده از این دستورات ،امکان تعریف شرط ،حلقه ،پرش از دستورات و نظایر آن فراهم می شود .
کنترل جریان از دستورات زیر تشکیل شده است :
BEGIN...END
BREAK
GOTO
CONTINUE
IF...ELSE
WHILE
RETURN
WAITFOR
Control-of-Flow Language
کار با متغیر ها در T-SQL
تعریف متغیر :یکی از اشیا در SQL Serverاست که برای حمل و نگهداری مقادیر مفرد (یکتا )Singleبه کار
می روند .
متغیر ها در T-SQLبه منظور های زیر به کار می روند:
-1به عنوان یک شمارنده که معموال ً تعداد دفعات اجرای یک حلقه را مشخص می کنند .
-2برای حمل داده های استفاده شده در یک ”کنترل جریان“ ).(Control-of-Flow
-3ذخیره داده ها به منظور بازگردادندن توسط یک Functionو یا Stored Procedure
از عالمت @ به منظور معرفی یک متغیر استفاده می شود .
برای تعریف یک متغیر از قالب زیر استفاده می شود :
@VariableName
Declare
برای مقدار دهی به متغیر ها از یکی از قالب های زیر استفاده می شود :
@VariableName = Value
Select
یا
@VariableName = Value
Control-of-Flow Language
Set
T-SQL کار با متغیر ها در
: مثال
Declare @Number
Set
@Number
Select
@Number
Use [Lab-EasyShop]
Declare @Title
@Titles
Set
Select
From
@Titles
@Title
Category
Where ID = 1
Select
@Titles
From
Category
Select
@Title
@Titles
Int
=
10
: مثال
nVarChar(100),
nVarChar(Max)
=
=
N''
Title
=
Title + N','+ @Titles
As
As
Title ,
Titles
Control-of-Flow Language
دستور IF … Else
برای تعریف شرط بکار می رود .
قالب کلی دستور به شکل زیر است :
عبارت شرطی If
کد دستورات مورد نظر
][ELSE
]کد دستورات مورد نظر[
مثال :
Int
= 15
@Number
@Number
=0
Control-of-Flow Language
Declare
Set
if @Number % 2
'Print 'Even
Else
'Print 'Odd
IF … Else دستور
: مثال
چنانچه متغیر اولی از دومی بزرگتر بود بدون استفاده از هیچ متغیر، مطلوبست جابجایی دو مقدار در متغیر
. واسط
Declare
Select
If
Select
@FirstNumber Int ,
@SecondNumber Int
@FirstNumber =
@SecondNumber =
100 ,
1
@FirstNumber >
@SecondNumber
Begin
Set
@FirstNumber
= @FirstNumber + @SecondNumber
Set
@SecondNumber = @FirstNumber - @SecondNumber
Set
@FirstNumber
= @FirstNumber - @SecondNumber
End
@FirstNumber
@SecondNumber
As FirstNumber,
As SecondNumber
Control-of-Flow Language
While دستور
. برای تعریف حلقه بکار می رود
: قالب کلی دستور به شکل زیر است
While عبارت شرطی حضور در حلقه
[Begin]
کد دستورات مورد نظر
[End]
: مثال
Declare
@Number Int
Set
@Number = 100
While
@Number > 0
Begin
Print
@Number
Set @Number
=@Number
End
-1
Control-of-Flow Language
While دستور
: مثال
مطلوبست اعداد زوج بین دو عدد
Declare
@FirstNumber Int ,
@SecondNumber Int
Select
@FirstNumber =
100 ,
@SecondNumber = 1
If
@FirstNumber >
@SecondNumber
Begin
Select
@FirstNumber
= @FirstNumber + @SecondNumber ,
@SecondNumber = @FirstNumber - @SecondNumber ,
@FirstNumber
= @FirstNumber - @SecondNumber
End
Set @FirstNumber
= @FirstNumber + @FirstNumber
% 2
While
@FirstNumber
<= @SecondNumber
Begin
Print
@FirstNumber
Set @FirstNumber = @FirstNumber + 2
End
Control-of-Flow Language
Stored Procedure
قطعه برنامه هایی هستند که عمدتاً از دستوراتی به زبان T-SQLتشکیل می شوند و می توانند هرگاه
توسط کاربر فراخوانی می شوند ،عملیات مورد نظر وی را بر روی اشیاء مختلف بانک اطالعاتی انجام دهند .
دقت کنید که Stored Procedureها توانایی بسیار در حل انواع مسائل مرتبط با بانک اطالعاتی دارند و در
معماری Fat Serverکمک شایانی به کاهش بار ترافیک شبکه می نمایند .
از مهمترین ویژگیهای Stored Procedureها می توان به موارد زیر اشاره کرد :
-1قابلیت پذیرفتن پارامترهای متعددی را به عنوان ورودی و خروجی دارند .
-2امکان فراخوانی سایر Procedureها را دارند و از یک مجموعه دستورات پشتیبانی می کنند .
-3هنگام فراخوانی ،موفقیت آمیز بودن و یا برخورد با اشکاالت را می توانند گزارش نمایند .
انواع مختلفی از Stored Procedureها وجود دارند :
System Stored Procedure -1
Standard Stored Procedure -2
Temporary Stored Procedure -3
Extended Stored Procedure -4
Stored Procedure
دسته اول Stored Procedureهایی هستند که توسط خود SQL Serverدر اختیار کاربر قرار می گیرند و
معموال ً دارای پیشوند " _ "Spمی باشند .
دسته دوم Stored Procedureتوسط کاربر و برای محقق سازی اهداف گوناگون ،مورد استفاده قرار می
گیرند .
دسته سوم Stored Procedureها نیز توسط کاربران و به منظور استفاده موقت به کارگرفته می شوند .
دسته چهارم Stored Procedureآنهایی هستند که با زبانی به غیر از T-SQLایجاد شده اند .
در نگارش SQL Server 2005عالوه بر قابلیت های توسعه ای که در نسخ قبلی فقط بر روی بانک اطالعاتی
Masterو زبان C++وجود داشت ،امکان بوجود آوردن Stored Procedureهایی به زبان های مختلف تحت
.NET Framework 2.0نیز نظیر C#و یا VB.Netوجود دارد.
این دسته از Stored Procedureها معموال ً دارای پیشوند " _ “Xpمی باشند .
اساس درس ما بر ایجاد و توسعه دسته دوم Stored Procedureانطباق دارد .
ایجاد Stored Procedure
برای ایجاد یک Stored Procedureاز قالب زیر استفاده می شود
نام مورد نظر
Create Procedure
)فهرست پارامترها(
As
Begin
لیست دستورات T-SQL
End
چند نکته :
Stored Procedure -1ها می توانند یک مقدار Intرا به عنوان نتیجه عملیات اجرایی خود ،با استفاده از
دستور Returnباز گردانند .
-2پارامتر ها در Stored Procedureها می توانند دارای مقادیر پیش فرض باشند .
Stored Procedure -3می توانند مقادیر متغییر های ورودی را تغییر داده و در صورت نیاز مقادیر تغییر یافته
فوق را با استفاده از عبارت Outputدر هنگام تعریف متغیر ،باز گردانند .
: مثال
برای جابجایی اعدادStored Procedure مطلوبست
Use [Lab-Inventory]
Go
Create Procedure Swap
(
@FirstNumber Int Output ,
@SecondNumber Int
Output
)
As
Begin
Select
@FirstNumber
= @FirstNumber + @SecondNumber ,
@SecondNumber
= @FirstNumber - @SecondNumber ,
@FirstNumber
= @FirstNumber - @SecondNumber
End
: مثال
برای بازگرداندن اعداد اول بین دو عددStored Procedure مطلوبست
حل کامل در فایل پیوست جلسه
Use [Lab-Inventory]
Go
Create Procedure PrimeNumber(
@FirstNumber Int
=1 ,
@SecondNumber
Int
=100
)
As
Begin
...
End
اجرای Stored Procedure
برای اجرای یک Stored Procedureبا استفاده از دستور Executeاز قالب زیر استفاده می شود :
])فهرست پارامترها[
نام SPمورد نظر
]=نام متغیر[
Execute
مثال :
3,1000
Execute
PrimeNumber
چند نکته :
-1چنانچه هنگام اجرای Stored Procedureها ،تمایل داریم از مقادیر پیش فرض استفاده کنیم ،می توانیم از
عبارت Defaultبه جای مقدار ورودی استفاده کنیم
مثال :
Default , 500
Execute
PrimeNumber
-2در صورتیکه پارامتری در Stored Procedureبصورت Outputتعریف شده ،هنگام فراخوانی آن نیز می بایست
از عبارت Outputدر کنار مقدار ورودی استفاده نمود و ضمناً مقدار ورودی بایستی حتماً یک متغیر باشد .
مثال :
Int ,
20
=
@Y Output
@Y
100 ,
Declare @X
Int
@Y
=
@X
@X Output ,
Select
Execute Swap
اجرای Stored Procedure
-3برای دریافت مقدار بازگشتی یک ، Stored Procedureابتدا بایستی یک متغیر از نوع Intتعریف کرده و در
زمان اجرای Stored Procedureمقدار خروجی را در آن قرار داد .
مثال :
6,250
Int
=PrimeNumber
]As [Count
Declare @Result
Execute @Result
Select @Result
-4امکان صدا زدن یک Stored Procedureدر عملیات و دستورالعمل های یک Stored Procedureدیگر وجود دارد
،تنها بایستی دقت کرد که تو در تو بودن عملیات ،تا 32مرحله قابل انجام است .این محدودیت شامل حال
Stored Procedureهایی که با زبان CLRدر .Netنوشته می شوند ،نمی شود .
برای پی بردن به مرحله تو رفتگی جاری می توان از تابع @@NestLevelاستفاده نمود .
اجرای پویای دستورات در T-SQL
با استفاده از دستور Executeو تعریف متغیرهای حاوی دستورات زبان ، T-SQLدستورات زبان T-SQLرا می توان
بصورت پویا ) (Dynamicاجرا نمود .
قالب کلی دستور به شکل زیر است :
)نام پارامتر حاوی دستورات@( Execute
مثال :
';Customer
برنامه نویسی T-SQL
]Use [Lab-EasyShop
)Declare @Command VarChar(Max
Select @Command = 'Select * From
)Execute (@Command
متغیر جدولی و خروجی
استفاده از متغیر جدولی در T-SQLامکان پذیر است و در واقع متغیر جدولی اساساً جدولی است که در حافظه
،هنگام اجرای دستورات یک برنامه ساخته می شود و پس از اتمام عملیات در خواستی برنامه ،از حافظه خارج
می شود .
دقت کنید چنانچه متغیر جدولی به عنوان خروجی یک زیر برنامه مد نظر است ،می بایست بصورت متغیر از نوع
جدولی در برنامه در نظر گرفته شود و نهایتاً خروجی زیر برنامه در آن ذخیره شود .توجه به این نکته که جدول
ساخته شده بایستی از لحاظ تعداد و نوع داده ای ستونها با جدولی که در برنامه ایجاد می شود ،متناظر
باشد.
مثال :
)Int
Declare @DataResult Table(Number
Insert Into @DataResult
Execute PrimeNumber 120,250
Select * From @DataResult
برنامه نویسی T-SQL
پیمایش خروجی Result Setها
استفاده از Cursorبرای پیمایش Result Setها و حرکت روی آنها در SQL Server 2005نیز امکان پذیر است .
Cursorدر واقع بر روی رکوردهای خروجی حرکت کرده و در حین حرکت خود امکاناتی نظیر قفل گذاری بر روی
رکورد را نیز داراست .دقت کنید که تعریف Cursorو باز کردن آن و پیمایش جداول توسط آن عملی نسبتاً کند و
حافظه بر است و بایستی در انتهای عملیات حتماً Cursorبسته شود تا حافظه اشغال شده توسط آن آزاد گردد.
برای تعریف Cursorاز قالب زیر استفاده می شود :
نام مورد نظر
Cursor
دستور
برای باز کردن Cursorاز قالب زیر استفاده می شود :
مثال :
Cursor
برنامه نویسی T-SQL
Select
Declare
For
نام مورد نظر
Open
CustomerCursor
Select
*
From
Customer
Declare
For
CustomerCursor
Open
پیمایش خروجی Result Setها
برای پیمایش رکوردها از قالب زیر استفاده می شود :
][NEXT | PRIOR | FIRST | LAST | ABSOLUTE RELATIVE
FETCH
نام FROM
]فهرست متغیر ها [ INTO
نکته :حالت Nextبه عنوان حالت پیش فرض می باشد ،و جنانجه نیاز به استفاده از سایر حالتها باشد ،
Cursorبایستی در حالت SCROLLتعریف شده باشد .برای آگاهی از انواع Cursorها به Books Online
مراجعه شود.
نتیجه دستور Fetchبا استفاده از تابع @@Fetch_Statusقابل دستیابی می باشد .این تابع سه مقدار حروجی
بر می گرداند :
: 0رکورد با موفقیت واکشی شد .
: -1واکشی دچار اشکال شد .
: -2رکورد مورد نظر پیدا نشد .
برنامه نویسی T-SQL
هاResult Set پیمایش خروجی
: و آزاد سازی حافظه از قالب زیر استفاده می شودCursor برای بستن
Close
مورد نظرCursor نام
Deallocate
Declare
مورد نظرCursor نام
@ID
@LastName
: مثال
Int ,
nVarChar(50)
Declare
CustomerCursor Cursor
For Select
ID ,
LastName
From
Customer
Open CustomerCursor
Fetch Next
From CustomerCursor
Into
@ID ,
@LastName
Declare
@Command VarChar(Max)
While @@Fetch_Status = 0
Begin
if @LastName
= 'Jafari'
Begin
Set @Command = 'Update Customer
RTrim(LTrim(Convert(VarChar(10) , @ID)))
Execute
(@Command)
Print 'Executed'
End
Print @LastName
Fetch Next
From CustomerCursor
Into
@ID ,
@LastName
End
Close
CustomerCursor
Deallocate CustomerCursor
Set LastName = ' + Char(39) + 'Jafari Namin' + + Char(39) + '
Where ID
= ' +
T-SQL برنامه نویسی
هاResult Set پیمایش خروجی
به نظر شما راه حل ساده تری وجود نداشت ؟
: مثال
Update Customer
Set
LastName = 'Jafari Namin'
Where
LastName = 'Jafari'
T-SQL برنامه نویسی
کنترل خطاها با SEH
در SQL Serverامکان کنترل خطاها به روش (Structured Exception Handling) ، SEHبا استفاده از بلوک Try-
Catchوجود دارد .هنگام بروز خطا در بلوک ، Tryاجرای دستورات به بلوک Catchمنتقل خواهد شد .در بلوک
Catchمی توان از دو تابع )( Error_Messageو )( Error_Numberبرای بررسی جزئیات خطا استفاده نمود .
قالب کلی استفاده از آن به شکل زیر است :
Try
Begin
…
Try
End
Catch
Begin
…
Catch
برنامه نویسی T-SQL
End
ایجاد خطا در SQL Server
از دستور RaisErrorبرای تولید خطا در SQL Serverاستفاده می شود .
قالب کلی استفاده از آن به شکل زیر است :
)محل بروز خطا,شدت خطا,پیام خطا(
RaisError
نکته :اعداد مربوط به شدت خطا و محل بروز خطا اعدادی دلبخواه هستند که توسط کاربر وارد می شوند اما
محدوده خاصی از آنها در SQL Serverتفسیر خاصی دارند .
مثال :
برنامه نویسی T-SQL
توابع رشته ای در SQL Server
مقدار کد Asciiمربوط به کاراکتر مورد نظر را برمی گرداند
یک کد Asciiمربوط را به یک کاراکتر معادل تبدیل می کند
مقدار کد Unicodeمربوط به کاراکتر مورد نظر را برمی گرداند
یک کد Unicodeمربوط را یک کاراکتر معادل تبدیل می کند
در یک رشته محل شروع یک عبارت را اعالم می کند
تعداد مشخص کاراکتر از سمت چپ رشته را بر می گرداند
تعداد مشخص کاراکتر از سمت راست رشته را بر می گرداند
تعداد مشخص کاراکتر از محل مشخص شده در رشته را بر می گرداند
رشته را به حروف کوچک تبدیل می کند
رشته را به حروف بزرگ تبدیل می کند
در کل رشته مورد نظر عبارت خاصی را با عبارت دیگری جایگزین می کند
کل فضا های خالی سمت چپ یک رشته را حذف می کند
کل فضا های خالی سمت راست یک رشته را حذف می کند
به تعداد مورد نظر ،فضای خالی بر می گرداند
یک رشته را بصورت برعکس بر می گرداند
طول یک رشته را بر می گرداند.
یک مقدار عددی را به یک رشته تبدیل می کند.
یک رشته را به تعداد مشخص تکرار می کند .
برنامه نویسی T-SQL
Ascii
Char
Unicode
NChar
CharIndex
Left
Right
Substring
Lower
Upper
Replace
LTrim
RTrim
Space
Reverse
Len
Str
Replicate
مطلوبست نوشتن زیر برنامه ای برای انتقال اعتبار بین
دو حساب در بانک اطالعاتی SimpleBankning
شرایط زیر بایستی محقق شود :
-1شماره حسابهای انتقال دهنده و انتقال گیرنده و مبلغ انتقال ورودی های زیر برنامه
هسنتد .
-2شماره ها بایستی از نظر معتبر بودن چک شوند .
-3انتقال بیش از 20000دالر در هر روز از یک حساب امکان پذیر نمی باشد.
-4انتقال وجه تنها بین حسابهای فعال امکان پذیر می باشد .
-5موجودی حساب پس از انتقال وجه نمی تواند از 200دالر کمتر شود .
-6در صورت انتقال اعتبار زیر برنامه ،مقدار صفر و در صورت عدم موفقیت در انتقال ،
می بایست عددی منفی بر حسب رخداد باز گرداند