مدیریت مالی

مقدمه :‌ 
کاربرد روز افزون بانک اطلاعاتی 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 
این دستور که دستوری مستقل نیست و حتما باید با اجزایی بکار رود جهت ساخت پرس و جو بر روی بانک اطلاعاتی بکار می‌رود و رکوردهایی که با شرایط این دستور همخوان باشد به عنوان نتیجه پرس و جو برمی‌گرداند . چهار کلمه کلیدی وجود دارند که بخشهای ارزشمند این دستور را تشکیل می‌دهند : 
۱- select 
۲- from 
۳- where 
۴- order by 

شکل کلی دستور : 
Select [*|distinct column1, column2,…] 
From table[,table2,…] 
Where شرط 
Order by نام فیلد یا شماره فیلد 
مثال : 
Select * from customers 
این دستور تمام رکوردهای جدول customers را برمی‌گرداند. 
که نتیجه ۹۱ سطر از اطلاعات این جدول خواهد بود 
حال اگر شرط Country =’uk’ اضافه کنیم ، فقط اطلاعات مشتریان انگلیس جواب خواهند بود که به ۷ سطر تقلیل می‌یابد. 

select * from customers 

where Country =’uk’ 

حال 
select City,Country from customers 
order by city 
فقط ستونهای نام شهر (city) و نام کشور (Country) را بر گردانده و بر اساس نام شهر مرتب میکند. دستور بالا با دستور پایین هردو یک جواب را میدهند : 
select City,Country from customers 
order by 1 
که ۹۱ سطر بازگردانده خواهد شد . در نتیجه پرس و جو تعدادی سطر تکراری وجود دارد مانند شهرLondon که اگر از کلمه Distinct‌ در Select استفاده کنیم این سطرهای تکراری حذف خواهد شد . 
select distinct City,Country from customers 
order by 1 
و جواب ۶۹ سطر خواهد بود. 
استفاده از توابع در Select 
۱- Count : تعداد سطرهای بازگردانده شده توسط select را میشمارد. 
Select Count(*) from Customers 
where Country =’uk’ 
در اصل تعداد مشتریانی را میشمارد که در کشور انگلیس هستند. که عدد ۷ جواب است. 
۲- Sum : مجموع یک فیلد عددی را برمی‌گرداند. 

Select sum(Quantity) from [Order Details] where productid = 11 مجموع فیلد Quantity را برای فیلدهایی که شماره محصول آنها ( Productid) برابر ۱۱ است را محاسبه میکند

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

 Select sum(Quantity) as Sum_QTY from [Order Details] where productid = 11 که Sum_QTY

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

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

 نکته ۳ : استفاده از group by : هنگامی که از توابع count ‌ و Sum به همراه یک فیلد دیگر در دستور select استفاده می‌شود از group by استفاده می‌کنیم . به عنوان مثال دستور زیر جمع مقادیر فیلد Quantity را برای هر شماره محصول محاسبه میکند .

 Select productid, sum(Quantity) as sum_qty from [Order Details] group by productid که نتیجه مانند زیر خواهد بود : productid sum_qty ———– ———– ۶۱ ۶۰۳ ۳ ۳۲۸ ۳۲ ۲۹۷ ۶ ۳۰۱ ۴۱ ۹۸۱ ۶۴ ۷۴۰ ۹ ۹۵ ۱۲ ۳۴۴ در صورتیکه دستور ordr by 1 بعد از group by استفاده کنیم نتیجه بر اساس کد محصول مرتب خواهد شد.

نکته ۴ : دستور 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 قابل به ذکر است که بیش از ۹۰% از کارهایی که ما برروی جداول انجام می دهیم با select و ترکیبات آن انجام می شود. لذا بدست آوردن تبحر در نوشتن select ها می تواند شما را در تهیه برنامه ها یاری کند.

۳- Min,max : بیشترین و کمترین مقدار فیلد را در بانک اطلاعاتی بدست می دهد. Select min (Quantity) from [Order Details] ۴- Top n : تعداد n سطر اول بانک اطلاعاتی را برمی گرداند. Select top 5 * from [Order Details] ۵ سطر اول بانک را برمی گرداند.

 نکته ۳ : در حالت بالا اگر مقدار سطر ۵ و ۶ یکی باشد فقط سطر ۵ جواب خواهد بود برای گریز از این حالت از شکل زیر در این دستور استفاده میکنیم : Select top n with ties * From table ۵- Into Select * from table1 into table2 اطلاعات table1 را به table2 کپی میکند. البته table2 باید از قبل وجود نداشته باشد. این دستور خود table2 را میسازد. دستور select قویترین و کاربردی ترین دستور در sql است که خود ماهها نیازمند تمرین و آموزش است . برای اطلاعات بیشتر  به books online خود Sql Server مراجع کنید. دستور Delete برای حذف اطلاعات از یک بانک اطلاعاتی استفاده میشود. شکل کلی دستور : Delete table1 Where شرط مثال : فرض کنید جدول authors موجود باشد و فیلد کلید آن au_id باشد.

 برای حذف ۱۰ سطر اول این جدول از دستور زیر استفاده می کنیم : 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 مقدار ۱۰ سطر اول را از table2 را در table1 درج میکند.

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

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

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

 به عبارت دیگر هر کار و پروژه ما بعنوان یک Database در سرور sql قرار میگرد. همه جداول و دستورات مربوط به آنها در این محل نگهداری می شود. با کلیک سمت راست برروی Databases و انتخاب New Database… میتوانید یک Database جدید برای خودتان بسازید. پس از انتخاب نام آن را تایید کنید. هر Database شامل موارد زیر است :

 ۱- Diagram : ارتباط جداول را نشان میدهد.

 ۲- Tables : جداول پروژه را نشان میدهد.

 ۳- Views : دیدهای پروژه را نشان میدهد.

 ۴- Stored Procedure : کدهای sql مربوط به عملیاتهای روی جداول را نگهداری میکند.

 ۵- Users : کاربران تعریف شده بر روی این database را نشان میدهد.

 ۶- Roles: قوانین دسترسی به جداول و پروسیجرها را نشان میدهد.

 ۷- Rules : قوانین مربوط به داده ها را در جداول نشان میدهد.

 گزینه های ۷ به بعد کاربرد آنچنانی برای کارهای ابتدایی ندارند

 ایجاد یک جدول جدید : برای ایجاد یک جدول جدید بروی 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 را وارد کنید.

 ۱- نام sp : ابتدا بجای [PROCEDURE NAME] یک نام برای SP خود در نظر بگیرید. من خود از ساختار زیر بدین منظور استفاده میکنیم :

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

Sale_INS . ۲- تعریف پارامترها : برای تعریف پارامترهای ورودی SP قبل از کلمه کلیدی As آنها را داخل پرانتز مشخص کنید. بدین شکل که ابتدا علامت @ سپس نام پارامتر بعد فاصله و نوع پارامتر. تذکر : تمامی متغییرها در SP از ساختار نام متغییر@ پیروی میکنند.

بعنوان مثال فرض کنید یک SP دارای دو پارامتر با نامهای Id از نوع int و Name از نوع Varchar(20) باشد , داریم 🙁 @id int,@name varchar(20)) حال بعد از As دستورات مورد نظر را تایپ میکنیم : مثال ۱ : 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 اضافه شده است .

مثال ۲ : تمام فیلدهای 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 ‘۰۱/۰۱/۱۹۴۰’ , ‘۱۵/۰۶/۱۹۵۵’ ۳- تعریف متغیرها : برای تعریف متغیرها می بایست از کلمه کلیدی Declare استفاده کنیم.

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

 ۴- برگرداندن کد خطا : بدین منظور از دستور return @@Error گرچه هرمقداری را که بخواهیم می توانیم با دستور return برگردانیم. ۵- تعریف پارامترها که مقداری را برمی گردانند : برای این منظور هنگامی که پارامتر را در ابتدای پروسیجر تعریف میکنیم بعد از نوع آن از کلمه کلیدی output می کنیم بعنوان مثال : create Stored Procedure Employees_BROWSE2 (@StartDate char(10) , @EndDate char(10) , @pp varchar(10) output) As ۶- مقدار دهی به متغییرها :

 به دو روش می توانید این کار را بکنید یکی با دستور Set و دیگری با دستور Select . ۷- دستورات شرطی :مانند دیگر زبانها شما می توانید در Sql دستورات شرطی را بکار ببرید. ساختار آن به شکل زیر است : If شرط then دستور If شرط then Begin دستور۱ دستور۲ … end مثال : If @Sdate > @Edate set @newDate = @Sdate ۸- دستور Set NOCOUNT on : این دستور از نوشت تعداد سطرهای برگردانده شده توسط دستورات جلوگیری میکند. کاربرد مهم آن زمانی است که شما چند کار را پشت سرهم در SP انجام میدهید.

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

 تذکر۱ : شما می توانید یک SP را در یک SP دیگر فراخوانی کنید. برای این منظور همانطور که قبلا گفته شد از دستور exec استفاده نمایید.

تذکر ۲ : با دستور 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در این قسمت در ادامه مطالب قبلی مبحث 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) قرار می گیرند انتخاب نمایید.

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

 تذکر۲ : SQL فیلد کلیدی جدول را در دستور Update در دستور Where خواهد آورد . همچنین شما این فیلد را از Include in Set Clause خارج کنید. در نهایت شما با فشار کلید Finish همزمان این Sp را خواهید ساخت. تا این قسمت شما با ساخت Sp آشنا شدید. حال برای ویرایش آن نیز کافی است بر روی Sp کلید نموده و در پنجره باز شده کد آن را ویرایش نمایید در ادامه مطالب آموزش SQL حال به بحث دیدها (view) می پردازیم.

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

می توان به موارد زیر به عنوان مزایای دید اشاره کرد : دیدها به کاربران اجازه میدهند تا بر روی داده هایی که نیاز دارند متمرکز شوند. بنابر این داده های غیر ضروری میتوان از دید خارج کرد. دیدها امنیت داده ها را نیز افزایش میدهند چراکه کاربر فقط داده هایی را می بیند که در دید وجود دارند. دیدها به کاربران اجازه میدهند تا داده ها را به روشهای متفاوت مشاهد نمایند.

دیدها میتوانند برای مبادله داده ها با سایر برنامه های کاربردی بکار روند. و …. ایجاد دید : وقتی دیدی را ایجاد میکنید نام آن می بایست در بین نام جداول و دیگر دیدهایی که کاربر مورد نظر آنها را ساخته , یکتا باشد.در SQL Server 7.0 شما امکان ایجاد شاخص بر روی دیدها را نداشتید , لیکن این امکان در SQL ۲۰۰۰ اضافه شده است. مراحل ایجاد یک دید :

 ۱- erprise Manager را باز کرده و بر روی Databases کلیک کرده و پایگاه داده ای را که می خواهید دید در آن ایجاد کنید . باز کنید.

۲- روی Views کلیک راست کرده و سپس گزینه New View… را انتخاب کنید.

 ۳- در پنجره بعدی کلیک سمت راست کرده و گزینه Add Table… را انتخاب کنید.

 ۴- دکمه های Table و یا Views جدول و یا دید های مورد نظر را انتخاب نمایید. و بر روی دکمه Addکلیک نمایید. این کار را برای تمام جداول و یا دیدهای مورد نظر تکرار کنید و سپس بر روی دکمه Closeکلیلک نمایید.

در قسمت Column از پانل مشبک , ستونهایی را که میخواهید در دید به آنها ارجاع نمایید انتخاب کنید. اگر میخواهید ستونی در مجموعه نتیجه دیده شود گزینه Output متناظر با آن می بایستی حتما تیک داشته باشد. در ستون Criteria شرط را بنویسید.

چند شرط را میتوانید در ستونهای OR تکرار کنید. برای گروه بندی برروی ستون Criteria کلیک سمت راست کرده و گزینه Group By را انتخاب نمایید. در اینصورت شما قادر خواهید بود از توابعی همچون Sum استفاده کنید.

 تذکر۱ : اگر Group by را انتخاب کرده باشید. تمام شرایط که در ستون Criteria بنویسید به عنوان شرایطHaving در نظر گرفته میشوند. برای اینکه این محدودیتها به شرط Where اضافه گردند , بر روی پانل مشبک متناظر کلیک کرده و از لیست مورد نظر گزینه where را بجای Group by انتخاب کنید.

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