I have a procedure that adds data

add_price (cust_id customers.id%type,
           items_id items.id%type,
           price number);

and I want to create a function that for each combination of customers and items to create an additional one at random entry in the table price.

How can I do that?

CodePudding user response：

Don't use a function, create a procedure and use INSERT ... SELECT with the CROSS JOIN of the customers and the items tables:

CREATE PROCEDURE generate_random_prices
IS
BEGIN
  INSERT INTO prices (customer_id, item_id, price)
  SELECT c.customer_id,
         i.item_id,
         ROUND(DBMS_RANDOM.VALUE(0,100),2)
  FROM   customers c
         CROSS JOIN items i;
END generate_random_prices;
/

Which, if you have the sample data:

CREATE TABLE customers (customer_id PRIMARY KEY) AS
SELECT COLUMN_VALUE FROM TABLE(SYS.ODCINUMBERLIST(1,5,42));

CREATE TABLE items (item_id PRIMARY KEY) AS
SELECT COLUMN_VALUE FROM TABLE(SYS.ODCINUMBERLIST(1,3,61));

CREATE TABLE prices (
  customer_id REFERENCES customers(customer_id),
  item_id     REFERENCES items(item_id),
  price       NUMBER(4,2)
);

Then after:

BEGIN
  generate_random_prices();
END;
/

The prices table may (randomly) contain:

CUSTOMER_ID	ITEM_ID	PRICE
1	1	38.91
1	3	39.74
1	61	67.28
5	1	13.92
5	3	48.17
5	61	70.21
42	1	90.33
42	3	5.7
42	61	40.37

If you want to call your ADD_PRICE procedure then just take the same CROSS JOIN query and use a cursor loop:

CREATE PROCEDURE generate_random_prices
IS
BEGIN
  FOR rw IN (SELECT c.customer_id,
                    i.item_id
             FROM   customers c
                    CROSS JOIN items i)
  LOOP
    ADD_PRICE(rw.customer_id, rw.item_id, ROUND(DBMS_RANDOM.VALUE(0,100),2));
  END LOOP;
END generate_random_prices;
/

(But it will be more efficient to just use a single INSERT ... SELECT statement.)

db<.fiddle here

CodePudding user response：

UPD: Please note, I believe the idea from MT0 is better because you'll need only one insert statement. My solution is for the case when using add_price function is required

So, "each combination of customers and items" means you need a cartesian product:

select cust_id, item_id
  from customers
  cross join items;

For example if you had following data in "customers" and "items" table:

the query above would return:

Thus, all is left is to get random value. Use dbms_random.value for that

begin
  for q in (select cust_id, item_id from customers cross join items) loop
    add_price(q.cust_id, q.item_id, round(dbms_random.value(10, 1000), 2));
  end loop;
end;

The parameters for value are lowes_value and highest_value so the result will be between those numbers. You probably will need to set them somehow. And rounding will be needed too