Stored Procedure - Variable flexibility

Database
N/A

Stored Procedure - Variable flexibility

Hi All,

I have assigned same value to 12 different variable implemeting 12 month column value.

var_1 = 50;

var_2 = 50;

var_3 = 50;

var_4 = 50;

var_5 = 50;

var_6 = 50;

var_7 = 50;

var_8 = 50;

var_9 = 50;

var_10 = 50;

var_11 = 50;

var_12 = 50;

Now as per my requirement, if value of yr will be equal to min_yr then I need to assign 0 value of all month which are less than min_mnth, something like below loop.

if(yr==min_yr)

{

  i=1;

  while(i<min_mnth)

  {

    var_{i} = 0;    -------------------here we need to provide 0 value for var_(i substituted)

    i=i+1;

  }

}

Similarly, if value of yr will be equal to max_yr then I need to assign 0 value of all month which are greater than max_mnth, something like below loop.

if(yr==max_yr)

{

  j=12;

  while(j>max_mnth)

  {

    var_{j} = 0;    -------------------here we need to provide 0 value for var_(j substituted)

    j=j-1;

  }

}

Above is pseudocode only, Here the main problem is that, I want to change variable value as per i and j which on combine with var_ should resemble with variable already exist..and should change those value. so, if I will call variable after all above condition, it should provide either 50 or 0 as per condition it met..

I am trying for the same solution, Any help from you all will ease my work a lot.. :-)


2 REPLIES
Enthusiast

Re: Stored Procedure - Variable flexibility

Unfortunately you are dealing with PL/SQL, if this would have been any other coding language then you could have easily used the arrays to fix your problem, but this won't be the case in SP world :)

Probably you can look for CURSORS and instead of using a set of variables, you can insert the values in a table and use the cursor dataset to manipulate them...

Senior Apprentice

Re: Stored Procedure - Variable flexibility

A temporary table is probably the best approach, but instead of a cursor it's probably a simple Update:

create volatile table vt (i int not null primary key, val int) on commit preserve rows;

Insert into vt select day_of_calendar, 50 from sys_calendar.calendar where day_of_calendar between 1 and 12;

update vt set val = 0

where i not between min_mnth and max_mnth;

Btw, in TD14 there are arrays, but i never tried if the are supported in a SP.

Dieter