آموزش

دانلود مطلب مربوط به backup

دانلود

دانلود مقاله مربوط به Trigger

دانلود

دستور Transaction :

تعریف: یکی از مهمترین دستورات و امکانات SQL جلوگیری از ثبت اطلاعات ناقص در دیتابیس میباشد. تعریف چندین دستور بصورت Transaction این امکان را بوجود خواهد آورد. چنانچه یک یا چندین دستورالعمل دارای چهار خاصیت زیر (مخفف آنها را ACID مینامند) باشند اصطلاحاً به آنها Transaction گویند :

- Atomicity

یعنی چند دستور بصورت بهم پیوسته در نظر گرفته شده و همانند یک دستور فرض میشود یعنی اگر یک یا چند دستور از آنها اجرا شده و الباقی اجرا نشوند در حقیقت مجموعه دستورات کامل انجام نشده و عملیات دستورات اجرا شده نیز برگشت داده خواهد شد.

- Consistency

یعنی همواره باید دیتابیس از حالت منطقی پیشین به حالت منطقی جدید منتقل شود.

- Isolation

- Durability

یعنی هر عملی کامل و موفق اجرا شده است که کلیه اطلاعات از حافظه موقت به حافظه دایم منتقل شوند.

انواع Transaction:

• Inplicit Transaction : هرکدام از دستورات SQL به تنهایی یک Transaction بوده و لذا کلیه خواص ACID را دارا میباشند اگر مثلاً وسط اجرای یک دستور پیچیده Select برق قطع شود یا سیستم با مشکل مواجه شود کلیه عملیات اجرا شده برگشت داده میشود. اصطلاحاً دستورات تکی SQL را  Inplicit Transaction نامند.
• Explicit Transaction : اگر چندین دستور SQL تشکیل یک Transaction داده باشند به آنها Explicit Transaction گویند و فرمت آن به شکل زیر است. در دستور زیر اگر شرط cond1 برقرار باشد باعث اتمام کار کلیه دستورات Transaction و ثبت اطلاعات مرتبط با آنها میگردد و در غیر اینصورت باعث بازگشت نتیجه عملیات کلیه دستورات Transaction خواهد شد. لازم به توضیح است چنانچه در وسط عملیات یک Transaction وقفه ای سخت افزاری از قبیل قطع برق بوجود آید SQL بصورت خودکار به قبل از دستورات آن Transaction برگشته و عمل Rollback را انجام خواهد داد.

Begin Transaction
  ....
  ....
    If cond1
       Commit Transaction
 Else   

   Rollback

• Nested Transaction : چنانچه چندین Explicit Trans داخل یکدیگر تعریف شوند به آن Nested Transaction گویند.

دستور Trigger:

کاربرد و خصوصیات Trigger ها دقیقاً مشابه Procedure هاست و تنها در دو مورد زیر با هم متفاوت میباشند:

Trigger ها برای جدول ها نوشته می شوند و در اثر اجرای سه دستور Insert و Delete و Update که Trigger به این رویدادها متصل شده است , اجرا میشوند. بنابراین Procedure ها در مکان و زمان مشخص صدا زده و اجرا میشوند ولی Trigger ها بطور اتوماتیک اجرا و فراخوانی میگردند.

Procedure ها پارامتر پذیرند ولی Trigger ها پارامتر نمی پذیرند.

فرمت کلی دستور Trigger به شکل زیر است:

Create Trigger Tr_name on Table_name
   For / After [Insert] , [Delete] , [Update]
   As
  Begin
      .....
  End
Go

نکته 1 : از لحظه اجرای یک Trigger تا پایان اجرای آن , دو جدول مجازی بنامهای Inserted و  Deleted ساخته شده و دراختیار برنامه نویس قرار می گیرد که این دو جدول حاوی اطلاعات زیر است:

• اگر Trigger در اثر اجرای دستور Insert فراخوانی شده باشد یک کپی از همه رکوردهای اضافه شده در جدول Inserted قرار می گیرد.
• اگر Trigger در اثر اجرای دستور Delete فراخوانی شده باشد یک کپی از همه رکوردهای حذف شده در جدول Deleted قرار می گیرد.
• اگر Trigger در اثر اجرای دستور Update فراخوانی شده باشد یک کپی از همه مقادیر قبلی رکوردهای بروزرسانی شده در جدول Inserted و یک کپی از مقادیر جدید رکوردهای بروزرسانی شده در جدول Deleted قرار می گیرد.

نکته 2 : برای یک جدول میتوان بیش از یک For Trigger نوشت.

نکته 3 : زمانی عملیات Ins/Del/Upd روی یک جدول , موفق انجام میشوند که هم خود دستورات درست اجرا شوند و هم تمام Trigger های مرتبط با دستور روی جدول صحیح اجرا شوند , درغیر اینصورت تمام عملیات بازگشت (RollBack) داده میشوند.

نکته 4 : Trigger ها به ازای دستورات Ins/Del/Upd اجرا میشوند یعنی به ازای اجرای هر دستور Ins/Del/Upd دستور Trigger فقط یکبار اجرا میشود حتی اگر دستور Ins/Del/Upd بیش از یک رکورد را تحت تاثیر قرار دهد. به عبارتی دیگر دستوراتی که بیش از یک رکورد را تحت تاثیر قرار میدهد به ازای عمل روی هر رکورد Trigger فراخوانی نخواهد شد بلکه بعد از تحت تاثیر قرار گرفتن کل رکوردها, یک بار Trigger فراخوانی میشود. بدیهی است در هنگام فراخوانی Trigger همه رکوردهای تحت تاثیر دستور Ins/Del/Upd در جداول Inserted یا Deleted (با توجه به نوع دستور) قرار می گیرد.

مثال : فرض کنید جدول Test با دو فیلد Code int و (50)Name varchar و جدول TestLog با سه فیلد Code int و (50)Name varchar و DelDate datetime را داشته باشیم. می خواهیم به کمک Trigger در زمان حذف رکورد از Test یک کپی از رکوردهای حذف شده در جدول TestLog کپی شود.

Create Trigger Testlogtrg on Test
 for Delete
   As
     Begin
       Insert into TestLog Select Code,Name,Getdate()from Deleted
    End

روال ها (Procedure) :

تعریف : در ٢٠٠۵ SQL چهار نوع Procedure به شرح زیر وجود دارد:

1. User Defined Stored Procedure
2. System Stored Procedure
3. Extent Stored Procedure
4. Clr.net Stored Procedure

١- روالهای تعریف شده توسط کاربران User Defined Stored Procedure :

روالها نیز همانند توابع برای تعریف یک سری دستورالعمل می باشند که در بخشهای مختلف سیستم به دفعات استفاده می گردند ولیکن دو تفاوت عمده بین روالها و توابع وجود دارد:

با اجرای توابع یک مقدار مشخص برگردانده میشود ولی لزوماً Procedure مقداری را برنمی گرداند.

در توابع مجاز به استفاده از Insert و  Delete و Update نبوده و همچنین عملیات ایجاد جداول و ایندکسها نیز در داخل توابع امکان پذیر نیست ولیکن در داخل Procedure ها تمامی این عملیات مجاز و ممکن است.

فرمت ساخت Procedure به شکل زیر است:

Create Procedure Proc_name(Parameters)
As
Begin
.....
End
Go

مثال ۱: فرض کنید جدولی بنام Test شامل دو فیلد Code (بصورت Primary Key) و Name داشته باشیم. یک Procedure جهت اضافه نمودن رکورد به این جدول بنویسید.

Create Proc Testins1(@Code int,@name varchar(50))
As
Begin
Insert into Test values(@Code,@Name)
End
Go

و دستورات زیر روش استفاده از این روال را نشان میدهد:

Exec Testins1 1,'Reza'
Exec Testins1 2,'Hamid'

٢- روالهای سیستمی (System Stored Procedure) : شرکت عرضه کننده S.S یک سری روال سودمند را برای استفاده کاربران از پیش تهیه نموده و تحت دیتابیس Master قرارداده است. این روالها را System S.P نامند و از طریق آدرس زیر میتوان با نام آنها آشنا شد.

Database > master > Programmability > Stored Procedure > System S.P

3- روالهای گسترده (Extent Stored Procedure) :

برخی از مواقع نیاز به انجام کارهایی داریم که S.S توان انجام آن را ندارد. مثلاً اگر بخواهیم از داخل S.S یک Email را دریافت یا ارسال کنیم که دستوری توسط S.S برای این کار پیش بینی نشده است S.S برای حل این مشکل امکان صدا زدن و اجرای Procedure های نوشته شده بزبان ++C را پیش بینی نموده است. نام اینگونه روالها با _XP شروع شده و تعدادی از این روالها که توسط Microsoft تهیه شده است در همان بخش روالهای سیستمی وفوق الذکر قابل مشاهده میباشد.

4- روالهای زبان #C وVB.Net  و   (Clr.Net Stored Procedure) :

با توجه به کابرد مفید روالهای گسترده فوق الذکر , امکان تهیه روال به زبان #C و VB.Net

نیز در  S.S 2005 اضافه گردید.

توابع (Functions) :

تعریف : در SQL دو نوع تابع به شرح زیر داریم:

• T_SQL Functions
• Clr.Net Functions

توابع نوع دوم در ٢٠٠۵ SQL اضافه شده و با زبانهای #C و VB.NET قابل نوشتن میباشد. هر کدام از انواع توابع فوق به سه گروه زیر تقسیم میگردند:

1. Scaler Valued user Define functions
2. Table Valued user Define functions Inline
3. Table Valued user Define functions Multi statement

1- توابع Scaler Valued user Define functions:

در ابتدا توابع گروه اول مورد بحث قرار میگیرند. فرمت کلی این توابع به شکل زیر است:

Create Fumnction F_name (parameters)
    Returns Datatype as
        Begin
          ...
          ...
         Return V_Name
      End

مثال : تابع زیر حاصلضرب دو عدد را بر میگرداند:

Create Function mymul(@a int, @b int)
    returns int as
      Begin
         Declare @c int
         Set @c=@a*@b
        return @c
     end

با اجرای برنامه فوق , تابع mymul بعنوان بخشی از دیتابیس فعال تعریف و ذخیره شده و برای رؤیت آن میتوان از منوی سمت چپ صفحه , بر روی نام دیتابیس کلیک کرده و مسیر زیر انتخاب شود. پس از آن فایل dbo.mymul مشاهده خواهد شد.

Programmability>Functions>Scalar-Valued Functions

روشهای مختلفی برای استفاده از توابع وجود دارد که به اختصار سه روش آن با مثال نشان داده میشود.

مثال ۱ : در مثال زیر حاصلضرب برابر ۲۵۰ نشان داده میشود.

Select dbo.mymul(25,10)

مثال ۲ : در مثال زیر نیز حاصلضرب برابر ۲۵۰ نشان داده میشود.

Declare @x int
Set @x=dbo.mymul(25,10)
Print @x

مثال ۳ : در مثال زیر در ابتدا یک جدول شامل دو فیلد عددی ساخته شده و پس از ایجاد تعدادی رکورد , حاصلضرب آنها را چاپ می کند:

Create Table T(x int, y int)
insert into T Values(10,12)
insert into T Values(15,14)
insert into T Values(18,20)
Select x,y,dbo.mymul(x,y) as M from T

٢- جداول مجازی View : دستور View یک Virtual Table یا Stored Query است که بوسیله آن میتوان یک دستور Select را بشکل Object در SQL ذخیره کرد و فرمت آن بشکل زیر است:

Create View vw_name
As
Select statement
Go

مثال : دستور زیر پس از اجرا , یک فایل بنام dbo.AuthorofCA را در قسمت دیتابیس فعال (اگر در زمان اجرای دستور pubs فعال باشد در این دیتابیس) ایجاد مینماید.

Create View AuthorofCA
As
Select * from Authors where State='CA'
Go

پس از آن می توان برای دسترسی به نویسندگان کالیفرنیایی , از دستور زیر استفاده کرد:

Select * from AuthorofCA

یا می توان با دستور زیر کد کتابهای نویسندگان کالیفرنیایی را بدست آورد:

Select * from AuthorofCA inner join Titleauthor
on AuthorofCA.au_id=Titleauthor.au_id

نکته ۱ : چنانچه یک دستور Select در تعداد زیادی از دستورات مورد استفاده قرار می گیرد بهتر است به منظور Structured Programing بصورت View تعریف شود.

نکته ۲ : میتوان دسترسی برخی از کاربران را برای یک View خاص تعریف و مشخص کرد لذا دستور View میتواند به ایمن سازی سیستم کمک کند.

نکته ۳ : پشت سر View هیچ رکوردی ذخیره نمی شود بلکه با اجرای دستور View دستور Select داخل View اجرا شده و نتیجه آن بازگشت داده میشود. بنابراین فقط به اندازه دستور Select و Execution Plan مربوط به آن , فضای حافظه اشغال میگردد.

نکته ۴ : اگرچه برای تغییر یک دستور View میتوان ابتدا آنرا با دستور Drop حذف کرده و سپس به وسیله دستور Create ایجاد نمود ولیکن این روش مناسبی نیست چون با حذف آن , تمامی دسترسی های کاربران نیز حذف میگردد لذا توصیه میگردد اگر قبلاً View ایجاد شده باشد برای تغییر آن به جای Create از دستور Alter استفاده شود.

نکته ۵ : با توجه به احتمال تداخل و پیچیده شدن عملیات دستورات مختلف , استفاده از دستورات Insert و Delete و Update در داخل View به هیچ عنوان توصیه نمی گردد.

٣- توابع Table Valued user Define functions Inlin:

این توابع نیز شبیه View یک دستور Select را بصورت مجازی تعریف می نماید ولیکن چون پارامتر دریافت می کند جایگزین بسیار خوبی برای View می باشد. فرمت کلی ساخت این توابع به شکل زیر است:

Create Fumnction F_name (parameters)
Returns Table as
Return (Select Statement)

مثال : تابع زیر مشابه مثال View فوق میباشد با این تفاوت که میتوان کد ایالت را بصورت پارامتر مشخص نمود :

Create Function Authorof(@state char(2))
Returns Table As
Return(Select * from Authors where State=@state)

روش اجرای تابع نیز به شکل زیر است :

Select * from dbo.Authorof('CA')

برای نمایش کد کتابهای نویسندگان یک ایالت خاص از دستور زیر استفاده میشود:

Select * from dbo.Authorof('CA') as K
inner join Titleauthor on K.au_id=Titleauthor.au_id

۴- توابع Table Valued user Define functions Multi statement:

فرمت ساخت آن عبارتنداز:

Create Fumnction F_name (parameters)
Returns @t_name Table (field1 datatype, [, fieldn datatype])
As
Begin
…
Return
End

تمرین : تابعی از نوع multi statement بنویسید که نام همه نویسندگان , ناشرها و فروشگاههایی که در یک ایالت خاص که از طریق پارامتر دریافت میگردد را نمایش دهد.

Create Function x910(@State char(2))
 returns @H table (Name varchar(200),Type char(1))
  as
    Begin
      Insert into @H
      SELECT au_lname + ' ' + au_fname as Name,'A' as type
        FROM Authors
        Where State = @State
           Insert into @H
             SELECT Pub_name,'P' as type
             FROM Publishers
         Where State=@State
            Insert into @H
        SELECT Stor_name,'S' as Type
        FROM Stores
     Where State = @State
   return
 end

 SELECT * FROM dbo.x910('ca')

متغیرها و دستورات شرطی :

١- متغیر (Valriable) :تمامی متغیرها با علامت @ شروع شده و فرمت تعریف و مقدار دهی آنها به شکل زیر است:

Declare Var_name1 Datatype1 [, Var_namen Datatypen]
Set Var_name = Expression
Set Var_name = (Select.....)

مثال : در مثالهای زیر چند نمونه متغیر تعریف شده است :

Declare @x int, @y varchar(50)
Declare @t money, @z decimal(5,2)

Set @x=123
Set @y='Vahid'
Set @t=123.45
Set @z=999.99
Set @x=2*10-5
Set @x=(Select count(*) from titles)

٢- دستور شرطی IF :

این دستور برای اجرای شرطی برخی از دستورات مورد استفاده قرار میگیرد و فرمت آن به شکل زیر است:

IF condition
 Begin
   ...
 End
[else
 Begin
  ...
End]

٣- دستور شرطی While : این دستور یک سری از دستورات ر ا, مادامی که شرط موردنظر برقرار باشد , اجرا میکند. در حلقه While دستور Break کنترل را به خارج از حلقه و دستور Continue کنترل را به ابتدای حلقه برمیگرداند و فرمت آن به شکل زیر است:

While condition
Begin
...
IF condition2
Break
End
...
IF condition3
Continue
End
...
End

تمرین : برنامه ای بنویسید که اعداد را از ۱ تا ۲۰ چاپ کند:

Declare @i int
Set @i=1
While @i<=20
Begin
Print @i
Set @i=@i+1

دستورات cube و Rollup:این دستورات برای محاسبه جمع بر اساس فیلدهای مختلف استفاده میگردد. مثلاً فرض کنید جدول زیر را داشته باشیم. دستور cube در مثال زیر باعث میگردد که جمع Quantity برای color و item های مختلف نیز نمایش داده شود. ولیکن نتیجه دستور Rollup محاسبه جمع برای Color های مختلف بوده و بر روی Item که بلافاصله بعد از Group by قرار گرفته است جمع محاسبه نمیشود:

Select item,color,sum(quantity) as tp from inventory
Group by item,color with Cube

Select item,color,sum(quantity) as tp from inventory
Group by item,color with Rollup

توابع از پیش تعریف شده (Built in Functions) :

١- تابع Ascii : این تابع کد اسکی یک کاراکتر را برمیگرداند. فرمت آن بشکل زیر است:

ASCII (character)

٢- تابع Char : این تابع شکل حرفی یک کد اسکی را برمیگرداند. فرمت آن بشکل زیر است:

CHAR (value)

٣- تابع Charindex: این تابع اولین شماره ستون عبارت اول (Pattern) در عبارت دوم (Expression) را برمیگرداند. فرمت آن بشکل زیر است:

CHARINDEX (pattern, expression)

۴- تابع Left : این تابع به تعداد حروف عدد len از سمت چپ عبارت String را برمیگرداند. فرمت آن بشکل زیر است‌:

LEFT (string, len)

۵- تابع  Right :این تابع به تعداد حروف عدد len از سمت راست عبارت String را برمیگرداند. فرمت آن بشکل زیر است‌:

RIGHT (string, len)

۶- تابع Len : این تابع تعداد حروف عبارت String را برمیگرداند. فرمت آن بشکل زیر است:

LEN (string)

7 - تابع Datalength : این تابع طول اصلی فیلد Variable را برمیگرداند. فرمت آن بشکل زیر است:

DATALENGTH (variable)

8- تابع Lower : این تابع تمام حروف عبارت String را بصورت حروف کوچک برمیگرداند. فرمت آن بشکل زیر است:

LOWER (string)

9- تابع Upper : این تابع تمام حروف عبارت String را بصورت حروف بزرگ برمیگرداند. فرمت آن بشکل زیر است:

UPPER (string)

10- تابع Ltrim : این تابع تمامی حروف خالی (Space) سمت چپ عبارت String را حذف میکند. فرمت آن بشکل زیر است:

LTRIM (string)

11- تابع Rtrim : این تابع تمامی حروف خالی (Space) سمت راست عبارت String را حذف میکند. فرمت آن بشکل زیر است :

RTRIM (string)

12- تابع Space : این تابع به تعداد Value حروف خالی (Space) برمیگرداند. فرمت آن بشکل زیر است:

SPACE (value)

13 - تابع Str :این تابع یک عبارت اعشاری را بصورت حرفی تبدیل میکند Float عبارت عددی مورد نظر, Length  طول عبارت جدید و Decimal تعداد اعشار است. بعنوان مثال نتیجه STR(1.368,4,2(   برابر 1.37 میباشد. فرمت آن بشکل زیر است:

STR (float, length, decimal)

14- تابع Replace : این تابع تمام عبارات String2  را در عبارت String1 به عبارت String3 تبدیل میکند. فرمت آن بشکل زیر است:

REPLACE (string1, string2, string3)

15- تابع Reverse : این تابع عبارت String را وارونه یا برعکس مینماید. فرمت آن بشکل زیر است:

REVERSE (string)

16- تابع Substring : این تابع عبارتی بطول Length و از شماره ستون Start از عبارت String ایجاد میکند. فرمت آن بشکل زیر است:

SUBSTRING (string, start, and length)

دستور Delete :

این دستور برای حذف یک یا چند رکورد از یک جدول مورد استفاده قرار گرفته و فرمت آن بشکل زیر است :

Delete [from] Table_name [From Table_join]
[where Condition]

تمرین : برنامه ای بنویسید که تمام رکوردهای فروشهایی که نوع کتاب آنها تجاری بوده, حذف شود .

delete from sales where title_id IN
(select title_id from titles where type='Business')

تمرین : برنامه ای بنویسید که تمام نویسندگانی را حذف کند که بیش از یک کتاب نوشته اند.

delete from authors where au_id in
(select au_id from titleauthor
group by au_id having count(title_id)>1)

نکته مهم : اگرچه برنامه فوق صحیح بنظر میرسد ولی بدلیل اینکه رکوردهایی از فایل نویسندگان (پدر) حذف میگردد که در فایل کتب نویسندگان (فرزند) دارای سابقه میباشد لذا سیستم اشکال منطقی گرفته و دستور اجرا نمیشود چون اول باید سابقه فرزندان حذف شده و سپس نویسندگان مذکور در فایل پدر حذف شوند. برای حل این مشکل باید در ابتدا کد نویسندگان مورد نظر در یک جدول موقت نگهداری شده و سپس رکوردهای با کد نویسنده موجود در جدول موقت از جدول فرزند حذف و پس از آن  رکوردهای موردنظر ازجدول پدر حذف گردد. در  S.S دو نوع جدول موقت داریم:

Local Temporary Table
Create Table #Tablename

اعتبار این نوع جدول محدود بوده و یا به عبارتی Scope آن برای همان کاربر ایجاد کننده معتبر میباشد. ولذا سایر کاربران نیز میتوانند جدول موقت با همین نام ایجاد نمایند. این جدول با دستور Drop حذف شده و یا پس از خروج کاربر از سیستم (Disconnect) خودبه خود حذف میگردد. بهتر است بلافاصله پس از استفاده , این جداول با دستور Drop حذف گردد چون در زمان اجرای مجدد برنامه , اگر دستور Create اجرا شود , پیغام تکراری بودن داده خواهد شد.

Global Temporary Table
Create Table ##Tablename

اعتبار این نوع جدول برای تمام کاربران سیستم معتبر میباشد ولذا سایر کاربران نمیتوانند جدول موقت با همین نام ایجاد نمایند. این جدول نیز با دستور Drop  حذف شده و یا پس از خروج تمامی کاربران از سیستم (Disconnect) خودبخود حذف میگردد.