اموزش SQL SERVER

با سلام خدمت دوستان عزیز

امروز می خوام اموزش سه پروسیجر INSERT-UPDATE-DELETE رو براتون به صورت عملی اموزش بدم

اول از همه باید ساختار کلی دستورات را یاد بگیریم تا بتونیم از آ» ها استفاده کنیم پس به ترتیب شروع می کنم

دستور INSERT

(.....,مقدار2,مقدار1)VALUES نام جدول INSERT INTO

دوستان توجه داشته باشید که مقدار ها همان اطلاعاتی هستند که ما می خواهیم در جدول

مورد نظر ثبت کنیم

فرض کنید ما یک جدول با مشخصات زیر درست می کنیم

create table TBl_kala

)

, kala_code as int

,(kala_name as nvarchar(50

kala_ghymat as int

(

خوب حالا ما می خواهیم مقدار زیر را در جدول درج کنیم

1 = kola_code

code_kala = mouse

kala_ghymat = 1000

حال با توجه به شکل کلی دستور اینگونه می نویسیم

(INSERT INto TBL_kala values(1,'mouse',1000

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

دستورUPDATE:

شکل کلی دستور

UPDATE نام جدول

set مقدار2=نام فیلد2،مقدار1=نام فیلد1،............

where (مقدار مورد نظر=نام فیلد مورد نظر برای مقایسه)

حالا فرض کنید اطلاعاتی را که ما با دستورهinsert در جدول کالا درج کردیم قیمت ان را می خواهیم از 1000 به 3000تغییر بدهیم . پس با توجه با شکل کلی دستور اینگونه می نویسیم

update TBL_kala

set kala_code=1,kala_name='mouse',kala_ghymat=3000

where (kala_code=1)

تحلیل:

خوب دستور بالا مقدارهای مورد نظر را در جدول می گذارد و بر روی رکوردی این تغییرات را ثبت می کند که فیلد کد کالا ان برابر 1 باشد

دستور DELETE

شکل کلی دستور:

DELETE نام جدول

where (مقدار مورد نظر = فیلد مورد نظر)

حال فرض کنید می خواهیم اطلاعات که ما در جدول بالا درج کردیم را حذف کنیم . با توجه به شکل کلی دستور اینگونه می نویسیم

DELETE TBL_kala

( where(kala_code=1

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

خوب حالا ما می خواهیم عملیات های insert_update_delete را به وسیله پروسیجر ها انجام دهیم .

تا حالا با شکل کلی دستورات آشنا شدید حالا شکل کلی دستور پروسیجر را براتون می نویسم

شکل کلی دستور ساخت پروسیجر:

create procedure نام پروسیجر

(

@نام متغیر 1 as نوع متغیر ,

@نام متغیر 2 as نوع متغیر ,

@nنام متغیر as نوع متغیر

)

عملیات و دستورات مورد نظر

حال با توجه به شکل کلی دستور پروسیجر و دستور درج می خواهیم پروسیجری بنویسیم که رکوردئ جدیدی را در جدول کالا درج کند .پس اینطور می نویسیم

create procedure pr_insert_kala

(

@code as int,

@name as nvarchar (50),

@ghymat as int

)

INSERT INTO TBL_kala values(@code,@name,@ghymat)

دوستان توجه کنید همیشه اخرین متغیری که تعریف می کنید نباید ویرگول داشته باشد.همانطور که می بینید متغیر@ghymat که من تعریف کردم ویرگول ندارد

نکته دیگه اینه که متغیر هایی که می خواهید تعریف کنید باید دقیقا نوع انها با نوع فیلد مورد نظر در جدول یکی باشد

روال ها (Procedure) :

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

User Defined Stored Procedure
System Stored Procedure
Extent Stored Procedure
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 نامند و از طریق آدرس زیر میتوان با نام آنها آشنا شد.

توابع (Functions) :

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

T_SQL Functions
Clr.Net Functions

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

Scaler Valued user Define functions
Table Valued user Define functions Inline
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

سلام

- طراحی(UDF) User Define Function : یکی از قابلیت های پایگاه داده SQL Server ایجاد UDF است که کاربران قادرند متناسب با نیاز در هر پروژه به طراحی آن اقدام نمایند و دارای شکل کلی ذیل می باشد.

هرUDF طراحی شده بعنوان یک تابع در کلیه دستورات SQL قابل استفاده است از طرفی درصورتیکه از EXECute استفاده گردد هر UDF می تواند مانند یک SP اجرا گردد همچنین در یک تابع UDF خروجی می تواند بصورت یک جدول باشد در این حالت از کلمه Table به جای نوع مقدار برگشتی استفاده خواهد شد و مقدار بازگشتی یک دستور Select خواهد بود که در مقابل دستور Return بکار خواهد رفت و تعداد پارامترهای تعریف شده در هر UDF حداکثر 1024 عدد خواهد بود و مشابه SP درصورتیکه پارامتر موردنظر ارائه نگردد پیش فرض بعنوان مقدار ورودی پارامتر مدنظر قرار خواهد گرفت نکته قابل ذکر در این خصوص به شرح ذیل می باشد:

- درهنگام اجرای یک تابع نام Owner می بایستی ذکر گردد بعنوان مثال ( dbo )

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

- از (ESP) Extended Stored Procedure درون تابع می توانید استفاده نمائید.

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

Create Function Fact (@n TinyInt)

Returns BigInt

Begin

Declare @RtnValue BigInt

IF @n = 0 or @n = 1

Set @RtnValue = 1

ELSE

Set @RtnValue = dbo.Fact (@n – 1) *@n

Return @RtnValue

END

حال فرض کنید که قرار است تابع فوق در یک برنامه استفاده گردد و قرار باشد سری محاسبه گردد بدین منظور به شکل ذیل عمل خواهیم نمود:

Declare @S BigInt , @C TinyInt

Set @S = 0

Set @C = 0

While @C < 10 Begin

Set @C = @ C + 1

Set @S = @ S + dbo.Fact (@C )

END

Select @S

خب دوستان تا اینجا توابع به پایان رسید. در بحث بعدی سایر دستورات کاربردی در اس کیو ال رو بررسی می کنیم.

موفق باشید

دستور 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

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

- متغیر (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

دستور 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) خودبخود حذف میگردد.

دستور 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

دانلود

سلام بر همه

در این پست می خوام در مورد حلقه های تکرار بگم :

3 - حلقه های تکرار : به منظور پیاده سازی ساختار حلقه تکرار در پایگاه داده SQL Server تنها از حلقه While استفاده می گردد که در دو شکل ذیل قابل پیاده سازی است:

در شکل ساختار 2 تا زمانیکه عبارت شرطی درست باشد تک دستور اجرا می گردد. ولی در ساختار پیچیده تر 1 کلیه دستورات بلاک END , Begin درصورت صحیح بودن عبارت شرطی اجرا خواهند شد وکلمه کلیدی Break جهت قطع حلقه وContinue حرکت به سمت ابتدای حلقه تکرار خواهد بود.

بعنوان مثال:

Declare @x Int , @y BigInt

Set @x = 0

Set @y = 1

While @x < 10 Begin

Set @x = @x + 1

Set @y = @y * @x

END

و یا در مثال ذیل از دستور Break استفاده گردیده است.

Declare @x Int , @y BigInt

Set @x = 0

Set @y = 1

While 1=1 Begin

Set @x = @x + 1

IF @x>= 10 Break

Set @y = @y * @x

END

پست بعدی در مورد توابع داخلی اس کیو ال خواهد بود. همچنان با ما همراه باشید.

صفحه بعد

نرم افزار های اموزشی و کتاب های اموزشی

نرم افزار های اموزشی و کتاب های اموزشی