How to do the calculation in the plsql loop?

Suriyaraj_Sudalaiappan used Ask the Experts™

I have table like below

      A      A-1            2.1
      A      A-2            3.1
      A      A-3            1.1
      A      A-4            4.5
      B      B-1            3.2
      B      B-2            5.3
      B      B-3            7.2
      C      C-1            3.9
      C      C-2            4.0
      C      C-3            9.9
I need to write a stored procedure with 2 input values. One is design_code and another one is model_name. I will pass the model name as comma
separated values to the procedure. Model name is nothing but A,B,C in the above table. Procedure has to split those model names and display
all the item_numbers and da_numbers for the model names.

Here the logic is i have to take all the model's item da_value and multiply.

For example for the above table, I have to multiply all A-1 da_values and B-1 da_value and C-1 da_value then stored in to one array that result.
Again A-1 da_value and B-2 da_values and C-2 da_values then stored into the same array.
Again A-1 da_value and B-3 da_values and C-3 da_values then stored into the same array.

After that come to A-2 da_value and start B-1 da_value and C-1 da_value then store into the same array.
Again A-2 da_value and B-2 da_values and C-2 da_values then stored into the same array.
Again A-2 da_value and B-3 da_values and C-3 da_values then stored into the same array.

So like this only the flow should go for all the models. Below is the sample example.

v_variable := 2.1 * 3.2 * 3.9;
       A-1   B-1   C-1
v_variable := 2.1 * 5.3 * 4.0;
      A-1   B-2   C-2           
v_variable := 2.1 * 7.2 * 9.9;
       A-1   B-3   C-3
v_variable := 3.1 * 3.2 * 3.9;
       A-2   B-1   C-1           
v_variable := 3.1 * 5.3 * 4.0;
       A-2   B-2   C-2
The calculation should happen like above example. Can anyone share with your thoughts?

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009
You are trying to code a procedure that takes a dynamic list of values and returns an array of all combinations. Combinations are commonly done in a programming language using nested loops. This will be more difficult to do in PL/SQL if you have an unknown number of keys being passed in the list.

If you always will pass in 3 in the list then you can use SQL's builtin join mechanism and allow a cartesian join to do the work for you.

You would only need to make the model_name the parameter to the below query, instead of hardcoding the values.
Notice the cartesian join does not join on any key, so you end up with all combinations where A is the first colum, B is the second, and C is the third.

This is not a PL/SQL solution and is likely not what you want, but I think coding it to support dynamic / variable number of model_names is going to be troublesome with PL/SQL.

I would suggest using dynamic SQL and returning a ref-cursor, so you can arbitrarily add the number of self-joins you need for the query, while letting Oracle do the actual combination work for you.

select a.item_number, b.item_number, c.item_number,
       a.da_value * b.da_value * c.da_value
 from test a, test b, test c
 where a.model_name = 'A' and b.model_name = 'B' and c.model_name = 'C'
 order by a.item_number, b.item_number, c.item_number
----- ----- ----- --------------------------------
A-1   B-1   C-1                             26.208
A-1   B-1   C-2                              26.88
A-1   B-1   C-3                             66.528
A-1   B-2   C-1                             43.407
A-1   B-2   C-2                              44.52
A-1   B-2   C-3                            110.187
A-1   B-3   C-1                             58.968
A-1   B-3   C-2                              60.48
A-1   B-3   C-3                            149.688
A-2   B-1   C-1                             38.688
A-2   B-1   C-2                              39.68
----- ----- ----- --------------------------------
A-2   B-1   C-3                             98.208
A-2   B-2   C-1                             64.077
A-2   B-2   C-2                              65.72
A-2   B-2   C-3                            162.657
A-2   B-3   C-1                             87.048
A-2   B-3   C-2                              89.28
A-2   B-3   C-3                            220.968
A-3   B-1   C-1                             13.728
A-3   B-1   C-2                              14.08
A-3   B-1   C-3                             34.848
A-3   B-2   C-1                             22.737
----- ----- ----- --------------------------------
A-3   B-2   C-2                              23.32
A-3   B-2   C-3                             57.717
A-3   B-3   C-1                             30.888
A-3   B-3   C-2                              31.68
A-3   B-3   C-3                             78.408
A-4   B-1   C-1                             51.168
A-4   B-1   C-2                              52.48
A-4   B-1   C-3                            129.888
A-4   B-2   C-1                             84.747
A-4   B-2   C-2                              86.92
A-4   B-2   C-3                            215.127
----- ----- ----- --------------------------------
A-4   B-3   C-1                            115.128
A-4   B-3   C-2                             118.08
A-4   B-3   C-3                            292.248
36 rows selected.

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial