قسمت سوم

منبع : از گروه کامپیوتر 4 آل

در این قسمت در ادامه مطالب قبلی مبحث SP خواهیم پرداخت و آن را کامل خواهیم کرد. در قسمت بعدی مطلب با View ها آشنا خواهیم شد.
ایجاد Stored Procedure با استفاده از ویزارد ها:
برای ایجاد SP های استاندارد جهت عملیات درج , حذف و ویرایش شما می توانید از ویزاردهای خود SQL استفاده نمایید. با این ابزار شما قادرید طی چند دقیقه تعداد زیادی SP جهت عملیاتهای گفته شده بر روی جداول خود بسازید. برای این منظور در Enterprise Manager بر روی کلید ویزارد کلیک نمایید مانند شکل زیر :



بعد در پنجره Select Wizard بر رویDatabase کلیک و گزینه Create Stored Procedure Wizard را انتخاب نمایید . مانند شکل زیر :




در پنجره بعدی به شما خوش آمد گویی مینمایید . برروی کلید Next کلیک نمایید.در پنجره بعدی نام Database ی را که قرار است بر روی جداول آن کار شود انتخاب نمایید و بروی Next کلیک نمایید. در پنجره بعدی جداول و عملیات مورد نظری که می خواهید انجام دهید انتخاب کنید. در پنجره بعدی نام SP هایی که سیستم ساخته شما خواهید دید. برای ویرایش نام و یا کد هرکدام از آنها میتوانید آن Sp را انتخاب و کلید Edit را فشار دهید. در اینصورت شما پنجره ای به شکل زیر خواهید داشت :




حال شما در قسمت Name می توانید نام Sp را عوض نمایید.
در قسمت Include in Set Clause شما فیلدهایی از جدول مربوطه که می خواهید مقادیر آن به شکل پارامتر برای Sp ارسال شود انتخاب نمایید. اگر Sp شما Update و یا Delete باشد در قسمت Include in Where Clause شما می توانید فیلدهایی که قرار است در شرط (دستور Where) قرار می گیرند انتخاب نمایید.

تذکر1 : اگر فیلدی از نوع Identity دارید در حالت Insertحتما از قسمت Include in Set Clause خارج کنید. در غیر اینصورت در زمان اجرا با خطا مواجه خواهید شد.
تذکر2 : SQL فیلد کلیدی جدول را در دستور Update در دستور Where خواهد آورد . همچنین شما این فیلد را از Include in Set Clause خارج کنید.

در نهایت شما با فشار کلید Finish همزمان این Sp را خواهید ساخت.
تا این قسمت شما با ساخت Sp آشنا شدید. حال برای ویرایش آن نیز کافی است بر روی Sp کلید نموده و در پنجره باز شده کد آن را ویرایش نمایید

قسمت دوم

آموزش Enterprise Manager

میتوان گفت قلب Sql Server است. در Enterprise Manager شما میتوانید یک اتصال به سرور Sql برقرار کنید . table بسازید. User تعریف کنید و.....

علامت فلش سبز نمایانگر فعال بودن سرور است.
سرور میتواند local باشد مانند بالا و یا یک Sqlserver باشد برروی یک سرور . برای ایجاد یک سرور جدید یا به عبارت دیگر وصل شدن client (ویندوز 98) به یک سرور دیگر بر روی یکی Microsoft Sql Server یا SQL Server Group و یا برروی سرور موجود کلیک سمت راست کرده و گزینه New Sql Server Registratin را انتخاب کنید. سپس کلید next را انتخاب کنید. سپس در منوی بعدی در available Server نام سرور خود را تایپ کنید . (نام سرور SQL خود را که برروی ویندوز 2000 خود نصب کرده اید) و بعد کلید add را فشار دهید.و گزینه next را انتخاب کنید.

در پتجره بعد از شما سوال میکند که آیا از username ویندوز استفاده کند و یا اینکه از یک username مخصوص خود Sql Server استفاده کنید. گزینه دوم را انتخاب کرده و سپس Login Name و Password را وارد کنید. (در حالت پیش فرض sa بعنوان login name و فضای خالی بجای Password وارد کنید.) پس از چند بار فشار کلید next شروع به وصل شدن به Server میکند. در صورت موفقیت آمیز بودن با پیامی این کار را اطلاع میدهد.
از دیگر گزینه ها شما بیشترین استفاده را از Databases خواهید کرد.
به عبارت دیگر هر کار و پروژه ما بعنوان یک Database در سرور sql قرار میگرد. همه جداول و دستورات مربوط به آنها در این محل نگهداری می شود.
با کلیک سمت راست برروی Databases و انتخاب New Database... میتوانید یک Database جدید برای خودتان بسازید.


پس از انتخاب نام آن را تایید کنید.
هر Database شامل موارد زیر است :
1- Diagram : ارتباط جداول را نشان میدهد.
2- Tables : جداول پروژه را نشان میدهد.
3- Views : دیدهای پروژه را نشان میدهد.
4- Stored Procedure : کدهای sql مربوط به عملیاتهای روی جداول را نگهداری میکند.
5- Users : کاربران تعریف شده بر روی این database را نشان میدهد.
6- Roles: قوانین دسترسی به جداول و پروسیجرها را نشان میدهد.
7- Rules : قوانین مربوط به داده ها را در جداول نشان میدهد.
گزینه های 7 به بعد کاربرد آنچنانی برای کارهای ابتدایی ندارند

ایجاد یک جدول جدید :
برای ایجاد یک جدول جدید بروی tables کلیک سمت راست کرده و گزینه New Table را انتخاب کنید. سپس در کادر بعدی نام جدول را انتخاب کنید. حال فیلدها و نوع آنها را مشخص کنید .
بعد از مشخص کردن نوع و احتمالاً طول فیلد , باید مشخص کنید که آیا فیلد همیشه می بایست مقدار داشته باشد و یا می تواند null باشد. Allow Nulls اگر تیک داشته باشد یعنی اینکه این فیلد می تواند هیچ مقداری به خود اختصاص ندهد.

تذکر : مقدار null را با فضای خالی اشتباه نگیرید.

در قسمت Default Value مقدار اولیه برای فیلد وارد کنید. تا در صورتیکه هیچ مقداری درج نشد آن مقدار درج شود. (در دستور insert )
اگر Identity را تیک بزنید این فیلد بشکل خود افزا خواهد شد که اولا باید نوع فیلد عددی و ثانیا مقدار گامها در Identity increment مشخص شود. مقدار اولیه آن را هم می توانید در Identity Seed قرار دهید. بدین شکل با این مقدار شروع و با گامهای مشخص شده حرکت خواهد کرد.

تذکر : هیچ مقداری در این فیلد نمی توانید درج کنید , چراکه خود سیستم این مقدار را تولید می کند .

برای مشخص کردن فیلد کلید (یا فیلدهای کلیدی) فیلد(ها) را انتخاب و بر روی علامت کلید بر روی Toolbar کلیک کنید .

تذکر : فیلد کلیدی نمی تواند Allow Nulls باشد.
تذکر : برای تعریف index کلیک سمت راست کرده و index/keys را انتخاب کنید .
( در مورد index بطور مفصل صحبت خواهد شد )

ایجاد Stored Procedure :
مانند هر زبان دیگر رویه ها در sqlServer نیز موجود می باشند. و بکار میروند. سه رویه insert , Updaet و Delete را می توانید براحتی با ویزاردهای خود SqlServer بسازید. از اینجا به بعد از واژه SP بجای رویه استفاده خواهیم کرد.
ابتدا با ساختار کلی SP آشنا شده و سپس به ویزارد موجود خواهیم پرداخت.
برای ایجاد یک SP جدید ابتدا برروی گزینه Stored Procedure کلیک سمت راست کرده و گزینه new Stored Procedure... را انتخاب کنید. در پنچره بعدی شما می توانید متن SP را وارد کنید.

1- نام sp : ابتدا بجای [PROCEDURE NAME] یک نام برای SP خود در نظر بگیرید. من خود از ساختار زیر بدین منظور استفاده میکنیم : عملیات_نام جدول
عنوان مثال اگر نام جدول Sale و عملیات مورد نظر یک عملیات insert باشد نام SP را Sale_INSERT میگذاریم. بهتر است نام عملیات با حروف بزرگ تایپ شود . البته بعضی از دوستان از سه حرف عملیات استفاده می کنند. برای مثال بالا خواهیم داشت :Sale_INS .

2- تعریف پارامترها : برای تعریف پارامترهای ورودی SP قبل از کلمه کلیدی As آنها را داخل پرانتز مشخص کنید. بدین شکل که ابتدا علامت @ سپس نام پارامتر بعد فاصله و نوع پارامتر.
تذکر : تمامی متغییرها در SP از ساختار نام متغییر@ پیروی میکنند.
بعنوان مثال فرض کنید یک SP دارای دو پارامتر با نامهای Id از نوع int و Name از نوع Varchar(20) باشد , داریم :( @id int,@name varchar(20))
حال بعد از As دستورات مورد نظر را تایپ میکنیم :

مثال 1 : SP بنویسید که چهار حرف اول فیلد LastName و فیلد FirstName را از جدول Employees انتخاب کند به شرطی که فیلد LastName با حرف A شروع شود ؟
ابتدا برروی Stored procedure کلیک سمت راست کرده و گزینه New stored procedure را انتخاب کرده و خطوط زیر را تایپ کنید. بعضی از دستورات پایین در کادر باز شده موجود هستند که نیازی به تایپ مجدد آنها نیست .
create Stored Procedure Employees_BROWSE
As
Select substring(Lastname,1,4) as LastNmae,FirstName
From Employees
Where LastName Like ‘%A’

حال بر روی دکمه ok کلیک نمایید. حال SP با نام Employees_BROWSE در لیست SP اضافه شده است .
مثال 2 : تمام فیلدهای Employees را انتخاب کنید که فیلد BirthDate در یک بازه تاریخی که به شکل پارامتر وارد میشود قرار گیرد ؟
create Stored Procedure Employees_BROWSE2
(@StartDate char(10) , @EndDate char(10))
As
Select *
From Employees
Where BirthDate between @StartDate and @EndDate

حال برای اجرای SP ها در Query Analyzer کافی است بنویسیم : نام Exec SP
بعنوان مثال :Exec Employees_browse
اگر پارامتر داشت مقدار پارامترها را هم می آوریم :
Exec Employees_Browse2 ‘01/01/1940’ , ‘15/06/1955’

3- تعریف متغیرها : برای تعریف متغیرها می بایست از کلمه کلیدی Declare استفاده کنیم. بعنوان مثال : Declare @myname varchar(50)
متغییر @myname از نوع کارکتری پویا تعریف میکند.

4- برگرداندن کد خطا : بدین منظور از دستور return @@Error
گرچه هرمقداری را که بخواهیم می توانیم با دستور return برگردانیم.

5- تعریف پارامترها که مقداری را برمی گردانند : برای این منظور هنگامی که پارامتر را در ابتدای پروسیجر تعریف میکنیم بعد از نوع آن از کلمه کلیدی output می کنیم بعنوان مثال :
create Stored Procedure Employees_BROWSE2
(@StartDate char(10) , @EndDate char(10) , @pp varchar(10) output)
As
6- مقدار دهی به متغییرها : به دو روش می توانید این کار را بکنید یکی با دستور Set و دیگری با دستور Select .
7- دستورات شرطی :مانند دیگر زبانها شما می توانید در Sql دستورات شرطی را بکار ببرید. ساختار آن به شکل زیر است :
If شرط then دستور
If شرط then
Begin
دستور1
دستور2
...
end
مثال :

If @Sdate > @Edate set @newDate = @Sdate

8- دستور Set NOCOUNT on : این دستور از نوشت تعداد سطرهای برگردانده شده توسط دستورات جلوگیری میکند. کاربرد مهم آن زمانی است که شما چند کار را پشت سرهم در SP انجام میدهید. مثلا یک جدوی موقت میسازید و سپس از آن یک تعدادی از فیلدها را با دستور Select انتخاب می کنید. اگر این دستور را در ابتدای SP استفاده نکتید , هنگامی که میخواهید از آن SP در یک زبان برنامه نویسی استفاده کنید با خطای SP هیچ dataset ی برنمی گرداند مواجه خواهید شد.

تذکر1 : شما می توانید یک SP را در یک SP دیگر فراخوانی کنید. برای این منظور همانطور که قبلا گفته شد از دستور exec استفاده نمایید.
تذکر 2 : با دستور exec شما می توانید یک دستور sql را نیز اجرا کنید. این کار زمانی بکار می آید که دستور مورد نظر پویا و متغیر باشد.
مثال :
Exec (“ select * From Employees Where “+@Shart)
این شرط میتواند بر اساس فیلدهای بانک تولید گردد.
مثال : یک SP بنویسید که اختلاف تعداد سفارشاتی که فیلد ShipCountry آنها France یا German باشد. را برگرداند ؟
create Stored Procedure Order_France_German ( @Outp int output)
as
declare @Count_France int , @Count_German int

select @Count_France = Count(*)
from orders
where ShipCountry = 'France'

select @Count_German = Count(*)
from orders
where ShipCountry = 'German'

if @Count_France is null set @Count_France = 0
if @Count_German is null set @Count_German = 0

set @outp = @Count_France - @Count_German

قسمت اول

کاربرد روز افزون بانک اطلاعاتی SQL مرا بر آن داشت تا مطالبی هر چند کوتاه جهت خوانندگان محترم سایت تهیه نماییم. قبلا از هر چیز لازم به ذکر است که مطالب ذیل در حد آشنایی بوده و دوستا برای دستیابی به تکنیکهای بیشتر می بایست از کتابهای مرجع و Book online خود SQL Server استفاده نمایند. در مطالب زیر که سلسله وار مباحث SQL Server را مرور خواهیم کرد , سعی شده تا ابتدا مطالب مقدماتی جهت آشنایی آورده شود و سپس اگر عمری باقی بود به مطالب پیشرفته آن بپردازیم. همچنین برای یادآوری خدمت دوستان ابتدا مرور سریعی بر چند دستور SQL که کاربرد بیشتری دارند خواهیم پرداخت و سپس به SQL Server و مطالب آن خواهیم پرداخت . مطالب زیر اکثرا از کتاب Microsoft SQL Server 7.0 Database Implementation Training انتخاب گردیده است . این کتاب به همراه CD‌آموزش آن به عنوان یک مرجع برای امتحانات مایکروسافت استفاده می‌شود.

جداول بکار رفته نیز همگی در SQL Server 7.0 در Database Northwind موجود هستند.

دستور Select

این دستور که دستوری مستقل نیست و حتما باید با اجزایی بکار رود جهت ساخت پرس و جو بر روی بانک اطلاعاتی بکار می‌رود و رکوردهایی که با شرایط این دستور همخوان باشد به عنوان نتیجه پرس و جو برمی‌گرداند . چهار کلمه کلیدی وجود دارند که بخشهای ارزشمند این دستور را تشکیل می‌دهند :

1- select

2- from

3- where

4- order by


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

Select [*|distinct column1, column2,…]

From table[,table2,…]

Where شرط

Order by نام فیلد یا شماره فیلد

مثال :

Select * from customers

این دستور تمام رکوردهای جدول customers را برمی‌گرداند.

که نتیجه 91 سطر از اطلاعات این جدول خواهد بود

حال اگر شرط Country ='uk' اضافه کنیم ، فقط اطلاعات مشتریان انگلیس جواب خواهند بود که به 7 سطر تقلیل می‌یابد.


select * from customers

where Country ='uk'

حال

select City,Country from customers

order by city

فقط ستونهای نام شهر (city) و نام کشور (Country) را بر گردانده و بر اساس نام شهر مرتب میکند. دستور بالا با دستور پایین هردو یک جواب را میدهند :

select City,Country from customers

order by 1

که 91 سطر بازگردانده خواهد شد . در نتیجه پرس و جو تعدادی سطر تکراری وجود دارد مانند شهر London که اگر از کلمه Distinct‌ در Select استفاده کنیم این سطرهای تکراری حذف خواهد شد .

select distinct City,Country from customers

order by 1

و جواب 69 سطر خواهد بود.

استفاده از توابع در Select

1- Count : تعداد سطرهای بازگردانده شده توسط select را میشمارد.

Select Count(*) from Customers

where Country ='uk'

در اصل تعداد مشتریانی را میشمارد که در کشور انگلیس هستند. که عدد 7 جواب است.

2- Sum : مجموع یک فیلد عددی را برمی‌گرداند.

Select sum(Quantity) from [Order Details]

where productid = 11

مجموع فیلد Quantity را برای فیلدهایی که شماره محصول آنها ( Productid) برابر 11 است را محاسبه میکند

نکته 1 : در دستور select می‌توان از اسم مستعار استفاده کرد ، یعنی نام جدیدی را برای یک ستون در نظر گرفت به عنوان مثال select قبل را به شکل زیر بکار برد :

Select sum(Quantity) as Sum_QTY

from [Order Details]

where productid = 11

که Sum_QTY یک اسم مستعار برای مجموع است. استفاده از کلمه کلیدیas ‌اختیاری است.

نکته 2 : در دستور select هرگاه اسم فیلدی اسم خاص باشد و یا فاصله بین اسم باشد مثل Order Details که فاصله بین اسم جدول است حتماُ از علامت براکت [] میبایست استفاده کرد.

نکته 3 : استفاده از group by :

هنگامی که از توابع count ‌ و Sum به همراه یک فیلد دیگر در دستور select استفاده می‌شود از group by استفاده می‌کنیم .

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

Select productid, sum(Quantity) as sum_qty

from [Order Details]

group by productid

که نتیجه مانند زیر خواهد بود :

productid sum_qty

----------- -----------

61 603

3 328

32 297

6 301

41 981

64 740

9 95

12 344

در صورتیکه دستور ordr by 1 بعد از group by استفاده کنیم نتیجه بر اساس کد محصول مرتب خواهد شد.

نکته 4 : دستور where می تواند خود شامل یک دستور select باشد :

select * from Products

where ProductID in

( select distinct ProductID from [order details] where Quantity >70)

order by ProductID

تنها نکته ای که می بایست توجه کرد این است که نام فیلدی که در شرط آورده می شود حتما در دستور select آورده شود, به عبارت دیگر select درون شرط تنها یک ستون را می بایست برگرداند .

تمرین : با فرض اینکه دو جدول Products و order details دارای ستون (فیلد) یکسان ProductID هستند , یک دستور Select بنویسید که تمام فیلدهایی از Products را نشان دهد که فیلد ProductID آن با ProductID جدول order details یکی باشد.؟

حل :

Select pr.*

From Products as pr , [order details] as od

Where pr. ProductID = od. ProductID


قابل به ذکر است که بیش از 90% از کارهایی که ما برروی جداول انجام می دهیم با select و ترکیبات آن انجام می شود. لذا بدست آوردن تبحر در نوشتن select ها می تواند شما را در تهیه برنامه ها یاری کند.


3- Min,max : بیشترین و کمترین مقدار فیلد را در بانک اطلاعاتی بدست می دهد.

Select min (Quantity)

from [Order Details]



4- Top n : تعداد n سطر اول بانک اطلاعاتی را برمی گرداند.

Select top 5 *

from [Order Details]

5 سطر اول بانک را برمی گرداند.


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

Select top n with ties *

From table

5- Into

Select * from table1 into table2

اطلاعات table1 را به table2 کپی میکند. البته table2 باید از قبل وجود نداشته باشد.

این دستور خود table2 را میسازد.


دستور select قویترین و کاربردی ترین دستور در sql است که خود ماهها نیازمند تمرین و آموزش است . برای اطلاعات بیشتر به books online خود Sql Server مراجع کنید.

دستور Delete

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

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

Delete table1

Where شرط

مثال :

فرض کنید جدول authors موجود باشد و فیلد کلید آن au_id باشد. برای حذف 10 سطر اول این جدول از دستور زیر استفاده می کنیم :


DELETE authors

FROM (SELECT TOP 10 * FROM authors) AS t1

WHERE authors.au_id = t1.au_id

دستور insert

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

Insert into table1 (f1,f2,…)

Values (v1,v2,…)

که f1,f2 نام فیلدها و v1,v2 مقادیر آنها میباشد.

البته میتوانید مقادیر را نتیجه یک select قرار داد.

مثال :

Insert into table1

Select top 10

From table2

مقدار 10 سطر اول را از table2 را در table1 درج میکند. البته باید تعداد فیلدها یکی باشد. در غیر اینصورت از values استفاده کنید.