نوشتن Stored Procedure ي جهت Code Generation با استفاده از Dynamic Array

[saeed6162]

سلام به دوستان عزيز
قصد نوشتن يه روال ذخيره شده رو دارم در حالي كه آشنايي لازم رو هم ندارم.
از دوستان محترم و برنامه نويس خواهش مي كنم كه من رو براي نوشتن اين stored procedure راهنمايي كنن ، ممنون:

هدف روال generate كردن يك كد در كنار تمامي رديف هاي يك table هست، لازم به ذكره كه تعداد ركوردها تا يك ميليون ركورد هم مي تونه باشه و بهينگي كد مهمه كه در زمان حداقل انجام بشه.
اين كدي كه قراره generate بشه به داده ي اوليه اي نياز داره كه بايد در يك آرايه ديناميك قرار گرفته باشه و فرض كنيد بسته به باقيمانده تقسيم id ركورد بر يك عدد ثابتي انديس آرايه ي داده ها رو بدست مياريم و اون رو بعنوان كد مورد نظر تو ركوردمون ثبت مي كنيم و مقدار اون انديس رو xتا افزايش مي ديم.


مثال :
ارايه
انديس 0 1 2 3 4
مقادير اوليه 2 15 20 25 37

فيلدهاي جدول
id , code
id از يك تا يك ميليون هست
مقدار id ؛ mod ميشه با 5 كه انيس آرايه به دست بياد ، مقدار اون خونه ي آرايه در فيلد code اين رديف ميشينه ، مقدار اون خونه ارايه كه بدست اومد با شماره ي انديسش+1 جمع ميشه.

اگه توضيحم واضح نبود بگيد تا يشتربراتون توضيح بدم
ممنون

[Reza_Yarahmadi]

دقیقا متوجه خواسته شما نشدم. مطابق مثال زیر چندتا داده نمونه و همچنین خروجی مورد نظرتون رو بذارید تا بهتر بشه کمک کرد.

Declare @Tbl Table (ID int Identity(1, 1), DataValue)



Insert Into @Tbl Values(2)

Insert Into @Tbl Values(13)

Insert Into @Tbl Values(15)

Insert Into @Tbl Values(19)

Insert Into @Tbl Values(25)

Insert Into @Tbl Values(36)

Insert Into @Tbl Values(39)

Insert Into @Tbl Values(63)

Insert Into @Tbl Values(71)

Insert Into @Tbl Values(72)

Declare @STR nvarchar(max)

Set @STR = 'int[,] arr = new int[' + (Select Count(1)/5+1 From @Tbl) + ',5];'



Set @STR = @STR + (Select '

arr[' + Cast(ID/5 as Nvarchar) + ',' + Cast((ID-1)%5 as nvarchar) + '];'

From @Tbl For XML Path('')



Set @STR = Replace(@Str, '
', '')



Print @STR

[saeed6162]

كدي كه مي خواستم به stored procedure تبديل كنم دقيقا كد زيره:
حلقه اي كه براي پيمايش جدول و تغيير مقدار فيلدها به كار بردم براي جداول بزرگ خيلي طول مي كشه و فكر مي كنم بااستفاده از روال ذخيره شده سرعت افزايش پيدا كنه.
اگه ممكنه كمكم كنيد

var

  Form1: TForm1;

 Counter : Array of Integer;



implementation



{$R *.dfm}

function TForm1.GetRadMaj(DivCount,dav:integer):integer;

Const

 ColumnCount = 250;

var

i,imod:integer;

 MaxCount ,Index :integer;

begin

  MaxCount:= ColumnCount * DivCount;



  imod := dav mod MaxCount;

  if imod = 0 then imod := MaxCount;



  index := ((imod-1) DIV ColumnCount)+1;

  Result := Counter[Index];

  Counter[Index]:=Counter[Index]+DivCount;

end;



procedure TForm1.Button2Click(Sender: TObject);

var divcount , i:integer;

begin

 divcount:=8;

 SetLength(Counter,DivCount+1);

 for I := 1 to DivCount do

   Counter[I]:=I;





ADOQuery1.Close;

ADOQuery1.SQL.Text:='Select * from g order by [Column 0]';

ADOQuery1.Open;



ADOQuery1.DisableControls;

ADOQuery1.First;

while not ADOQuery1.Eof do

  begin

    ADOQuery1.Edit;

    ADOQuery1.FieldByName('radif').Value:=IntToStr(Get  RadMaj(divcount,ADOQuery1.FieldByName('Column 0').AsInteger));

    ADOQuery1.CheckBrowseMode;

    ADOQuery1.Next;

  end;

ADOQuery1.EnableControls;

end;

[محمد سلیم آبادی]

سلام سعید جان،
عرضم به حضورت که پست اولت رو خوندم و دقیق متوجه مسالت شدم، در SQL آرایه و ماتریس وجود نداره که ما بخواهیم باهاش کار کنیم. شما مجبورین که آرایه ای که معرفی کردین رو در قالب یک جدول بیارین (همانطور که در تصویر زیر دیده میشه).
اگر ماتریس(آرایه) خودتون رو به جدول تبدیل کنید. اون وقت با کوئری که در انتها نوشتم مشکلتون حل میشه.
Untitled.png

SELECT N.id,

       Code = M.value + N.[index] + 1

  FROM Numbers AS N

       INNER JOIN [Mod] AS M

       ON N.id % 5 = M.[index]; 

-- % = Mod Function

[محمد سلیم آبادی]

یا اگر این 5 مقدار که در آرایه هستند همیشه ثابت بوده و طول آرایه هم تغییر نمیکنه آنگاه خیلی ساده تر میشه مساله رو به این شکل حل کرد:

SELECT Id,

       Code = CASE id % 5 

                   WHEN 0 THEN 2

                   WHEN 1 THEN 15

                   WHEN 2 THEN 20

                   WHEN 3 THEN 25

                   ELSE 37

              END

FROM Numbrs

[saeed6162]

محمدجان ممنونم از راهنماييهات
اما خب نتونستم مشكلم رو حل كنم
يه طور ديگه مطرح مي كنم مساله رو :
فرض كن يك جدول با 2 فيلد داريم ؛ فيد اول ID كه از 1 شروع ميشه تا يك ميليون؛ يعني يك ميليون ركورد داريم.فيلد دوم Value و خاليه و با استفاده از يك كوئري يا stored procedure مي خواهيم مقدار دهيش كنيم.
8 تا شمارنده داريم كه اولي مقدارش يكه ؛ دومي 2 ؛ سومي 3 و ...
ابتدا جدول بايد بر اساس ID سورت باشه.
لازمه كه 250 تا 250 جدول رو جدا كنيم و هر 250 رديف از يك شمارنده استفاده مي كنه، بطوريكه 250 تاي اول از شمارنده ي اول استفاده مي كنه يعني رديف 1 تا 250 مقدار فيلد دومش ميشه
Counter1 = counter1 + 8

1.JPG

250 تاي دوم از شمارنده دوم استفاده مي كنه و مقدار فيلد دومش ميشه :
Counter2 = Counter2 + 8

2.JPG

و 250 تاي سوم از شمارنده سوم استفاده مي كنه و همينطور الي شمارنده هشتم
شمارنده هشتم تا رديف 2000 رو مي تونه مقدار دهي كنه ، رديف 2001 الي 2250 دوباره از شمارنده اول استفاده مي كنه و شمارش رو ادامه مي ده ؛ رديف 2251 الي 2500 ا شمارنده دوم استفاده مي كنه و الي آخر
و مساله اينطوري بايد انجام بشه
گفتم كه كدش رو نوشتم البته با دلفي و خيلي كنده نياز به كدي دارم تو Sql كه سرعت رو خيلي بالا ببره و بهينه هم باشه.
ممنون ميشم اگه كدي كه مي نويسي رو كامل بنويسي چون با زبون sql زياد آشنا نيستم.
ممنونم ازت ومنتظر جوابت هستم
مرسي؛ سعيد

[محمد سلیم آبادی]

سوال 1: این شمارنده هایی که در مثالتون آوردین همیشه مقادیرشون از 1 تا 8 هستند یا خیر؟ مثلا میتونه شمارنده 1 دارای مقدار 5 باشه بر فرض و مقدار شمارنده 2 باشه 7؟

سوال 2: مقدار فیلد دوم جدولی که دارای ستون ID هست از فرمول زیر محاسبه میشه دیگه درسته؟
مثلا ID (یا همون column0) اگه در رنج 1 تا 250 باشه مقدار ردیف های متناظر برابر خواهد بود با:

radif = (column0 - 1) * 8 + (مقدار شمارنده 1)

سوال 3: می تونه تعداد شمارنده ها متغیر باشه مثلا به جای 8 با شه 10؟

سوال 4:این مسأله جدیدی که مطرح کردین ساده شدۀ مساله واقعی است یا اینکه خود مسأله واقعی است؟ ضمن اینکه من از کد Delphi چیزی نمیتونم برداشت داشته باشم.

[saeed6162]

پاسخ سوال 1: هميشه مقدار اوليه شمارنده ها از يك شروع ميشه و انديس شمارندس؛ يعني شمارنده يك 1 شمارنده ي دو 2 شمارنده ي سه 3 و ...
پاسخ سوال 2: بله درسته ؛ فرمولي كه نوشتي درسته.
پاسخ سوال 3 : مي تونه باشه، ولي مهم نيست، اما خب ديناميك بودنش بهتره يعني طوري طراحي بشه كه با هر تعداد شمارنده كار بكنه ، همين جاي مساله بود كه آرايه هاي ديناميك رو مطرح كردم.
پاسخ سوال 4: خود مساله اي هست كه دنبال جوابشم. كد دلفي رو توجهي بهش نكن ؛ همه اونچيزي كه لازم بود رو تو پست#6 توضيح دادم.
مرسي

[محمد سلیم آبادی]

گرفتم مساله چیه. اما از اونجایی که تعداد ردیف ها(یک میلیون) فراتر از حاصل ضرب 8 در 250 هست (که ردیف های بالاتر از 2000 باید دوباره ریست بشن...)، بر اساس اون تکنیکی که قبلا بلد بودم به جوابی نرسیدم. از طرفی این عدد 8 هم میتونه متغیر باشه که مساله رو پیچیده تر میکنه.
میتونی الگوریتمی که خودتون برای حل این مساله استفاده کردین رو به زبان ساده بیان کنین؟ تا ازش الگوبرداری کنم؟

[saeed6162]

كدي كه با دلفي نوشتم اينطوره
يك آرايه پويا در نظر گرفتم كه طولش به تعداد شمارنده هاست
اول كار هم از يك تا n مقدار دهي مي شه
بعد جدول رو sort مي كنم بر اساس id
رديه به رديف پيمايش رو شروع مي كنم از ابتدا تا انتهاي جدول
سه رقم سمت راست id رو در نظر مي گرم ؛ با استفاده از باقيمانده تقسيم اون بر تعداد شمارنده ها مي فهمم كه از كدوم شمارنده بايد استفاده كنم
مقدار شمارنده رو براي رديف مورد نظر ذخيره مي كنم و شمارنده رو به تعداد شمارنده ها (كه در اين مثال 8 هست) افزايش مي دم.
ولي خب اين روش كنده متاسفانه

[saeed6162]

البته مقدار شمارنده ها براي رديف 2000 به بعد ريست نمي شه ها! ادامه پيدا ميكنه فقط انديس شمارنده ها ريست مي شه!

[محمد سلیم آبادی]

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

-- 1 to 1000000

declare @t table(i int)

insert into @t

select top(1000000) row_number()over(order by (select 1)) from sys.columns a,sys.columns b, sys.columns c



-- Query

select i,case(i%250)when 0 then 249 else(i%250-1)end *8+((rnk+249)/250) as radif

from (select i, row_number() over(partition by (i+(8*250)-1)/(8*250) order by i) rnk

from @t

)d

[saeed6162]

واي چه جالب!
چقدر خوبه كه ميشه همچين كدي زد و اينقدر سريع كدها رو توليد بكنه!
محمد جان تقريبا درسته
يعني درسته فقط بعد از ركورد 2000 ام مقدار شمارنده ها ريست ميشه كه نبايد اينطور باشه و بايد ادامه پيدا كنه!

[محمد سلیم آبادی]

فقط بعد از ركورد 2000 ام مقدار شمارنده ها ريست ميشه كه نبايد اينطور باشه و بايد ادامه پيدا كنه!

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

[saeed6162]

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

اينم يه نمونه
Database1.rar

[محمد سلیم آبادی]

اينم يه نمونه
Database1.rar

این چه فایلیه؟ Access هست؟ اگه آره من نصب ندارم.
میتونی خروجیت رو توی مثلا ورد، notepad++یا اکسل اکسپورت کنی؟

[saeed6162]

اينم فايل متنيش
1.rar

[محمد سلیم آبادی]

کاشکی حداقل 8 هزار سطر اول رو ارسال می کردید تا بیشتر بررسی میکردم. اگه اسکریپت زیر هم جواب نداد 8 هزار سطراول رو ارسال کن.
این اسکریپت 10 هزار سطراولیه رو تولید میکنه ببینید نتجیش چطور متور؟

-- 1 to 1000000

declare @t table(i int)

insert into @t

select top(10000) row_number()over(order by (select 1)) from sys.columns a,sys.columns b, sys.columns c



-- Query

select i,

case when i%2000 = 0 then i-2000 else (2000*(i/2000)) end

+case(i%250)when 0 then 249 else(i%250-1)end *8+((rnk+249)/250) as radif

from (select i, row_number() over(partition by (i+(8*250)-1)/(8*250) order by i) rnk

from @t

)d

[محمد سلیم آبادی]

شما نیازتون چیه؟ داشتن یک جدولی با داده هایی مشابه خروجی اسکریپت؟
اگه بله کافیه یک جدول ایجاد کنید که دارای دو ستون باشه به شکل زیر

CREATE TABLE Sample (ID integer NOT NULL PRIMARY KEY, Value integer NOT NULL);

سپس کافیه سطرهای خروجی اسکریپت رو یکجا در جدول درج کنیم شبیه این کد که 10 هزار سطر اول رو در جدول Sample ذخیره میکنه:

declare @t table(i int)

insert into @t

select top(10000) row_number()over(order by (select 1)) from sys.columns a,sys.columns b, sys.columns c



INSERT INTO Sample 

select i,

case when i%2000 = 0 then i-2000 else (2000*(i/2000)) end

+case(i%250)when 0 then 249 else(i%250-1)end *8+((rnk+249)/250)

from (select i, row_number() over(partition by (i+(8*250)-1)/(8*250) order by i) rnk

from @t

)d

چون ما در اینجا هم مقادیر یک تا یک میلیون رو میتونیم تولید کنیم نیازی نیست که از قبل داده ای در جدول Sample بوده باشد.

[saeed6162]

محمد جان با فرض اينكه table ي از قبل داريم كه ستون i اون پر شده و فقط ستون رو بايد بهش اضافه كنيم بايد چيكار كنم؟
چون مقدار ستون i اون جدولي كه از قبل وجود داره ممكنه از 1000001 شروع بشه و مثل همون تصاويري كه تو پست 6 گذاشتم.

[محمد سلیم آبادی]

محمد جان با فرض اينكه table ي از قبل داريم كه ستون i اون پر شده و فقط ستون رو بايد بهش اضافه كنيم بايد چيكار كنم؟
چون مقدار ستون i اون جدولي كه از قبل وجود داره ممكنه از 1000001 شروع بشه و مثل همون تصاويري كه تو پست 6 گذاشتم.

سوال 1: چرا باید به جای مقدار 1 مقدار 1000001 در جدول درج بشه؟ یعنی این جزئی از برنامتون هست و دلیل خاصی داره؟
سوال 2: آیا ممکن است بخواهین دائما مقادیر ستون جدید رو بروز رسانی کنید (بر اساس متغیر هایی چون 8، 250 و یک میلیون)؟

[saeed6162]

پاسخ 1) آره محمد جان همونطوري كه تصوير هم ديدي ، جزو مساله اصلي بود
پاسخ 2) اگه قرار باشه كه تغيير داشته باشه ،كد خيلي زياد تغيير مي كنه؟ چون query ي كه قرار اجرا بشه توسط برنامه توليد ميشه؛ ميشه از متغيرهاي همون زبان برنامه نويسي ، مثلا دلفي اعداد 8 و 250 رو تو query تغيير داد و اجرا كرد و قابل حل هست.

[محمد سلیم آبادی]

پاسخ شما بدون در نظر گرفتن جواب سوالات پست قبلی:
بروز رسانی بر اساس جوین (update based on join):
your_table:جدولی که دارای یک ستون با مقادیر 1000001 تا 1000000 می باشد
column0: ستونی که در خط قبل توضیح داده شد
value: ستون محاسباتی که باید مقادیرش بروز رسانی بشه بر اساس ستون column0
Numbers: جدول اعداد دارای یک ستون (به نام i) با مقادیر 1 تا 1 میلیون

UPDATE YT

SET YT.value = C.radif

FROM your_table AS YT

INNER JOIN (select i,case when i%2000 = 0 then i-2000 else (2000*(i/2000)) end

+case(i%250)when 0 then 249 else(i%250-1)end *8+((rnk+249)/250) as radif



            from (select i, row_number() over(partition by (i+(8*250)-1)/(8*250) order by i) rnk

                  from Numbers

                  WHERE i <= 1000000

                  )d

            )c

ON CASE WHEN YT.column0 = '1000000' THEN 1000000 ELSE SUBSTRING(column0, 2, 6) END = c.i;

پاسخ 2) اگه قرار باشه كه تغيير داشته باشه ،كد خيلي زياد تغيير مي كنه؟

اصلا کد(کوئری) با متغیرها مشکلی نداره. منظور اینه که شما میتونید مقادیر 8 و 250 رو به عنوان پارامتر به کوئری بدین. یک SP بسازین که دارای این پارمتر ها باشه و بس.
فقط یادت نره قبلش باید یک جدول اعداد بسازین به این جا رجوع کنید:

[saeed6162]

محمد ممنون بابتي كمكي كه كردي براي نوشتن اين كوئري ، مشكلم رو حل كرد ، اما الان نياز دارم كه يخورده تغييرش بدم.
اين كوئري براي همه ركوردها با يك فرمول عمل مي كنه در حالي كه الان لازم دارم براي تعدادي از ركوردهاي جدول كه هميشه در انتهاي جدول هستند و تعدادشون كمتر از 2000 تا هم هست با مقادير ديگه اي حساب كنه يعني 2000 و 250 و 249 كه تو كد استفاده شده اعداد ديگه اي باشن
كدي كه الان من دارم استفاده مي كنم اينه :

declare @Numbers table(i int)

insert into @Numbers  

  select top 985977 row_number()over(order by (select 1)) from sys.columns a,sys.columns b, sys.columns c

    UPDATE YT SET YT.r2 = C.radif

    FROM (select * , ROW_NUMBER() OVER(ORDER BY [dav])AS ROWNUM 

      from [MakeRadifMajazi].[dbo].[rdf])AS YT    

        INNER JOIN(   select i,case when i%2000 = 0 then i-2000 else (2000*(i/2000)) end

      +case(i%250)when 0 then 249 else(i%250-1)end *8+((rnk+249)/250) as radif

                from (select i, row_number() over(partition by (i+(8*250)-1)/(8*250) order by i) rnk

                      from @NUMBERS

                      WHERE i <= 985977

                      )d

                )c  ON

              [YT].ROWNUM  = c.i

مثلا تو اين مثال براي 1977 تاي آخر لازمه كه عدد 2000 تغيير بكنه به 1977 و 255 تغيير بكنه 247 و 249 تغيير بكنه به 246 ؛ فكر مي كنم نياز به يك شرط هست كه وقتي مقدار i بيشتر از 984000 شد از مقاديري كه گفتم جهت محاسبه استفاده كنه اين شرط رو بايد كجا نوشت؟

[محمد سلیم آبادی]

از دوتا query استفاده کن. یکی برای 1977 تای آخر و یکی برای سایر سطرها. بعد این دو query را با هم اجتماع (union) کن.

[saeed6162]

محمد فكر ميكنم بعد از دستور inner join بايد شرط قرار داد فقط جاي شرط رو برام مشخص كن كه كجا باشه خودم مقدارگذارهاش رو مي تونم انجام بدم

[محمد سلیم آبادی]

در query دوم که بعد از union all اومده 1977 سطر آخر رو بدست بیاره و اعداد مورد نظر رو لحاظ کن، البته فراموش نکن که 1977 سطر آخر در query اول انتخاب نشن.

declare @Numbers table(i int)

insert into @Numbers 

  select top 985977 row_number()over(order by (select 1)) from sys.columns a,sys.columns b, sys.columns c



;with YT as

(

    select *, ROW_NUMBER() OVER(ORDER BY [dav])AS ROWNUM

      from [MakeRadifMajazi].[dbo].[rdf]

)

UPDATE YT 

SET YT.r2 = C.radif

FROM  YT   

     INNER JOIN(   select i,case when i%2000 = 0 then i-2000 else (2000*(i/2000)) end

                           +case(i%250)when 0 then 249 else(i%250-1)end *8+((rnk+249)/250) as radif

                     from (

                           select i, row_number() over(partition by (i+(8*250)-1)/(8*250) order by i) rnk

                             from @NUMBERS

                            WHERE i <= 985977

                           )d

                                

                   union all

                          

                    select i,case when i%2000 = 0 then i-2000 else (2000*(i/2000)) end

                                 +case(i%250)when 0 then 249 else(i%250-1)end *8+((rnk+249)/250) as radif

                     from (

                            select i, row_number() over(partition by (i+(8*250)-1)/(8*250) order by i) rnk

                             from @NUMBERS

                            WHERE i <= 985977

                           )d

                )c  ON

             [YT].ROWNUM  = c.i

[saeed6162]

كوئري دوم كه دقيقا عين كوئري اوله چطور 1977 تاي آخر رو محاسبه مي كنه ؟ آيا منظورت اين بود كه خودم شرط WHERE i <= 985977 رو تغيير بدم؟

[محمد سلیم آبادی]

من گفتم خودت ترتیبشو بده بله باید شرط رو تغییر بدین همچنین اعداد مورد نظر 2000 به 1977 ....