آموزش کار با SP

[یوسف زالی]

سلام.
با اجازه این بار می خوام در مورد Stored Procedure ها کمی بنویسم:

Stored Procedure یا SP اصلا چی هست:
مجموعه چند دستور پشت سر هم هستند که در قالب یک شی ذخیره میشن. این دستورات تقریبا میتونن هرچیزی باشن. (دستوراتی که می تونن در قالب یک Batch باشن. مثلا مجاز نیستیم در یک SP یک SP دیگه بسازیم. همون دستوراتی که بدون گذاشتن GO بین اونها بشه ازشون استفاده کرد. اطلاعات بیشتر = سرچ کلمه GO در همین سایت)

کاربرد:
گاهی ما نیاز داریم که چند تا کار رو همیشه انجام بدیم. مثلا برای ثبت سفارش اول تست کنیم ببینیم شرایط خاصی وجود داره یا نه، بعد جدولی رو آپدیت کنیم، بعد سلکت کنیم...
خوبی SP اینه که در اون می تونیم شرط - حلقه - تراکنش - فراخوانی یک SP دیگه - ساخت گزارش وابسته به پارامتر و ... داشته باشیم.

فرقش با View چیه؟
در View فقط Select گذاشته می شه و بدون پارامتر از اون استفاده میشه.
در SP هر کاری میشه کرد. پارامتر هم میشه پاس داد. مثل پروسیجر های IDE هست.

SP ها و اصولا تمام دستورات حتی اونهایی که در کامند ها نوشته میشه همه روی سرور ران میشه.
موضوع SP با موضوع کامند هایی که در IDE ساخته میشه دو چیز متفاوته.
شما می تونی همون کارهایی که در SP می کنی در کامند کنی ولی SP این اجازه رو میده که کد شما خواناتر بشه و کوتاهتر..
یکی دیگه از مزیتهای SP هم اینه که روی سرور اس کیو ال اصطلاحا کش میشه. یعنی بعد از ایجاد اون به صورت کامپایل شده استفاده میشه و سرعت اجراش چندین برابر کامند میشه.
کش شدن یه داستان دیگه داره که خیلی واردش نمی شم (چون بلد نیستم! :لبخند:)

اما بریم سراغ تعریف SP.
مثال های سریع:
Create Procedure SPName as blabla
Alter Procedure SPName as blabla
Create Proc SPName (@Param1 as DataType, @Param2 as DataType ) as blabla
Create Procedure SPName @Param1 DataType, @Param2 DataType = DefaultValue as blabla

یک مثال ساده:
پروسیجری که سال مالی میگیره و داده های سال مورد نظر رو سلکت می کنه:
Create Procedure SelectWithYear @FiscalYear int as
select * from Accounts where AccountYear = @FiscalYear

پروسیجری که داده هایی رو به روز می کنه:
Create Proc P1 @price decimal, @amount decimal, @goodcode int as
begin
if @amount > 0
update TBL_Goods set GoodPrice = @Price , GoodAmnt = @Amount
where GoodCode = @GoodCode

else
begin
set @amount = 1
insert into TBL_Goods select @GoodCode, @amount, @Price
end

end

این فقط مثاله و در عمل ممکنه هیچ کاربردی نداشته باشه.

فراخوانی یک SP:
در خود SQL برای فراخوانی یک SP از دستور EXEC یا همون EXECUTE استفاده میشه.

مثال بالا:
Exec P1 4500, 20, 21003

همونطوری که می بینید پارامتر ها رو مقابل اسم SP نوشتیم.

اگر در UI از شی ADOStoredProc استفاده می کنین کافیه که اون رو ExecProc کنید.
ADOSP.ExecProc

اما اگر در SP یک سلکت داشتید و خواستید اون رو از UI با ADOStoredProc اجرا کنید اون رو Open کنید.
ADOSP.Open

اگر از کامند استفاده می کردید شکل فراخوانی در SQL رو به کار ببرید.
Exec P1 blabla

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

نکته ها:
اون DefaultValue چی بود جلوی تعریف SP بالاییه؟
مواقعی که برای پارامتر ورودی مقداری معین نکرده بودیم اون مقدار به SP فرستاده میشه.
نمی تونیم پارامتری به SP دارای پارامتر پاس ندیم اگر default نداشت.

آیا SP می تونه مقداری return کنه؟ خب به چه دردی می خوره؟
بله. اما این مقدار همیشه باید یک عدد صحیح باشه.
در مواقعی خصوصا در کنترل خطا ها به کار میاد.
مثلا تصور کنید اگر شرطی برقرار نبود ما 1- بر می گردونیم و تو UI تست می کنیم که خروجی اون اگر 1- بود پیغامی نمایش داده بشه.
یا مثلا پس از Insert در جدول می تونیم Identity@@ رو برگردونیم تا بفهمیم ID ردیفی که الان زدم چند شد.

create procedure P1 as
insert into TBL1 values(1, 2) -- dade haye nemune
return @@identity

چه شکلی کد برگشتی رو میگیریم؟
در SQL:
declare @ret int
Exec @ret = SPName params

در ADOStoredProc :
ADOSP.Parameters.ParamByName('@Return_Value').Valu e

آیا در SP میشه هم Select کرد هم Insert کرد؟
بله اما توصیه میشه که SP های Select جدا از SP های دستکاری داده ها نوشته بشن.

آیا میشه ترتیب پارامتر ها رو جابجا پاس کرد؟
بله. با آوردن مستقیم نام پارامتر در فراخوانی:
Execute SPName @Amountt = 250, @GoodCode = 21003, @Price = 5500

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

[ali190]

فراخوانی یک SP:
در خود SQL برای فراخوانی یک SP از دستور EXEC یا همون EXECUTE استفاده میشه.

این دستور رو باید در کجا اجرا نمود؟

[یوسف زالی]

وقتی تو SQL داخل یک SP می خواهی یک SP دیگه ران کنی.
دقیقا جاهایی که می تونی Select یا Insert بنویسی. اما در View نمی شه.

[ali190]

سلام
ممنون از توضیحات جامع شما
بذارید دریافتی های مغزیم رو از sp بگم ببینید درست متوجه شدم:
1-یک sp بصورت غریزی چیزی فراتر از یک view هست و اعمالی بمراتب بیشتر از یک select رو میتونه انجام بده ، در ضمن پارامتر پذیر هم هست
2-شاید بطور ناخود آگاه و بدون اینکه بدونیم بسیاری از اعمال sp ها رو در IDE هامون در قالب SQL Command ها اجرا کردیم منتها در IDE هامون متغیرهامون همون تکست باکسها و کمبوباکسها و ... بودند که باهاشون در واقع یک جدول یا یک VIEW رو در قالب دستورات SQL Command فیلتر و محدود میکردیم
یادمه که VIEW اصلاً پارامتر پذیر نبود و ما اون رو در IDE در قالب SQL Command همون محدود میکردیم
اما الان با وجود SP ها میتوان بسیاری از عملیاتهایی که بر روی دیتابیسمون میخواستیم در قالب SQL Command اجراشون کنیم میشه تو یه SP نوشت و فقط از طریق IDE متغیرها رو درش پاس داد و نتیجه کار رو دید، اینطوری در کدنویسی محیط IDE مون هم صرفه جویی کردیم ، بسیاری از عملیات محاسباتی رو در سرور و با سرعت بلاتری انجام میدیم و...
توهمات:
آیا میشه نتیجه خروجی یک SP رو در IDE در یک دیتاگرید نمایش داد ، مثل یک VIEW؟
ممنون از لطفتون
یاعلی

[یوسف زالی]

بله.
نتیجه گیری هاتون درست هست.
همون طور که گفتم SP می تونه خروجی Select هم داشته باشه.
منتها نکتش اینجاست که اگر چند تا Select پشت سر هم داشته باشیم در UI اولین Select میشه نتیجه ما.
نکته جالبی رو بگذارید براتون بگم؛ شما می تونین خروجی یک SP رو مستقیم Insert کنید:
Insert into Table1
Exec P1

اما نمی تونین خروجی رو مستقیم در SQL و خارج از SP محدود کنید.

[ali190]

ممنون از توضیحاتتون
شما با زبان vb6 هم آشنایی دارید؟

[یوسف زالی]

متاسفانه در حد چهار تا دکمه رو فرم

[Galawij]

فقط یک نکته ای به ذهنم رسید که در ادامه مطالب اضافه می کنم.
روال ها می توانند علاوه بر پارامتر های ورودی، پارامترهای خروجی نیز داشته باشند. فقط کافی است در تعریف پارامترها از کلمه کلیدی OUTPUT بعد از تعریف نوع پارامتر استفاده شود و در داخل دستورات روال این پارامتر مقداردهی شود.مثال:
Create Procedure SPName(@Parametrname Datetype OUTPUT)
As
Begin
Select @Parametrname=FieldsName From Tbname where ....
و علاوه بر روال های که کاربر برای برنامه خود می نویسد خو SQL نیز شامل تعداد زیادی روال سیستمی هست که به فراخور نیاز می توان آنها را فراخوانی کرد.
لیست روال های سیستمی SQL (http://msdn.microsoft.com/en-us/library/ms187961.aspx)

[یوسف زالی]

بله. درست می فرمایید.
و در ادامه اینکه اگر بخواهیم با دستور Exec خروجی این جور فیلد ها را بگیریم:

create procedure P1
@x int output
as
select*
from TBL1
where ID > @x

set @x = 100

در فراخوانی:

declare @i int
set @i = 50
exec P1 @i output
print @i

[khoshblagh]

نتیجه گیری هاتون درست هست.
همون طور که گفتم SP می تونه خروجی Select هم داشته باشه.
منتها نکتش اینجاست که اگر چند تا Select پشت سر هم داشته باشیم در UI اولین Select میشه نتیجه ما.
نکته جالبی رو بگذارید براتون بگم؛ شما می تونین خروجی یک SP رو مستقیم Insert کنید:
Insert into Table1
Exec P1
=================
با سلام خدمت دوستان
من یک SP در sql server 2005 ساختم که نتیجه جاصل از ترکیب 3 جدول بوده و نتیجه در یک dbgrid به درستی نشان داده میشود. حالا میخواهم این نتیجه به یک جدول که دقیقا ساختار آن از لحاظ ستونها با SP یکی است انتقال دهم . چطور باید اینکار را انجام دهم؟ متشکرم

[یوسف زالی]

دقیقا دستورش رو خودتون گذاشتید.
Insert into Table1
Exec P1

[khoshblagh]

دقیقا دستورش رو خودتون گذاشتید.
Insert into Table1
Exec P1
من اینو درsp ساخته شده در sql server بعد از مرحله پایان مرحله سلکت گذاشتم ولی انتقالی به جدول مورد نظر صورت نگرفت!

[یوسف زالی]

بعد از مرحله پایان مرحله سلکت گذاشتم

یعنی چی؟
باید کدتون رو کامل بگذارید. توضیحات ناقصه

[khoshblagh]

یعنی چی؟
باید کدتون رو کامل بگذارید. توضیحات ناقصه

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[Pro_KharidFasli_temp]
AS
BEGIN
SET NOCOUNT ON;
SELECT TOP (100) PERCENT dbo.tblAshkhas.Onvan, dbo.tblAshkhas.CodEghtesadi, dbo.tblAshkhas.Nam, dbo.tblAshkhas.Famil, dbo.tblAshkhas.CodMeli,
dbo.tblAshkhas.CodPosti, dbo.tblAshkhas.Adres, dbo.tblAshkhas.ShahrOstan, dbo.tblPayman.ShomarPayman, dbo.tblPayman.TarikhPayman,
dbo.tblGardeshPayman.Shar AS SharSorVaz, dbo.tblPayman.EblaghMablaghAkhar, dbo.tblPayman.MablaghPayman, dbo.tblPayman.MablaghAkhar,
dbo.tblAshkhas.Phon1, dbo.tblGardeshPayman.MablaghTax, dbo.tblGardeshPayman.MablaghTaxAfzodeh, dbo.tblGardeshPayman.TarikhSabtSorVaz,
dbo.tblAshkhas.PishPhon1, dbo.tblGardeshPayman.MablaghKolKar, dbo.tblGardeshPayman.MablaghMasalehPayKar,
dbo.tblGardeshPayman.MablaghSorVazGhabl, dbo.tblGardeshPayman.MablaghJaraem, dbo.tblPayman.Onvan AS OnvanPayman
FROM dbo.tblAshkhas INNER JOIN
dbo.tblPayman ON dbo.tblAshkhas.CodId = dbo.tblPayman.CodPaymankar INNER JOIN
dbo.tblGardeshPayman ON dbo.tblPayman.CodId = dbo.tblGardeshPayman.CodIdPayman
ORDER BY dbo.tblGardeshPayman.TarikhSabtSorVaz
END
Insert into [dbo].[tblKharidFasli_temp]
Exec [Pro_KharidFasli_temp]

[amir20611]

بله.
نتیجه گیری هاتون درست هست.
همون طور که گفتم SP می تونه خروجی Select هم داشته باشه.
منتها نکتش اینجاست که اگر چند تا Select پشت سر هم داشته باشیم در UI اولین Select میشه نتیجه ما.
نکته جالبی رو بگذارید براتون بگم؛ شما می تونین خروجی یک SP رو مستقیم Insert کنید:
Insert into Table1
Exec P1

با تشکر از You-See عزیز ، باید اضافه کنم که اگر Select های شما بیش از یکی بود به این معنی نیست که به غیر از اولی مابقی دردسترس نیستند در .net اگر یک select داشته باشید خروجی datatable هست اگر بیش از یکی داشته باشید dataset هست که تمام جدول ها رو به ترتیب با نام table,table1,table2,... به شما خروجی میدهد

[khoshblagh]

با سلام خدمت دوستان
SP زیر بعد از اجرا خطا پیوست را اعلام میکند . البته داده ها به جدول مورد نظر منتقل میشود ولی علت این خطا و طریقه رفع آن چگونه است؟
114465
INSERT INTO tblKharidFasli_temp
EXEC usp_ShowKharidFasli_temp

[یوسف زالی]

با تشکر از You-See عزیز ، باید اضافه کنم که اگر Select های شما بیش از یکی بود به این معنی نیست که به غیر از اولی مابقی دردسترس نیستند در .net اگر یک select داشته باشید خروجی datatable هست اگر بیش از یکی داشته باشید dataset هست که تمام جدول ها رو به ترتیب با نام table,table1,table2,... به شما خروجی میدهد

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

در خصوص دوست دوم، ارور شما ربطی به تالار اس کیو ال نداره.