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?
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:
| cust_id | cust_name |
|---|---|
| 1 | A |
| 2 | B |
| item_id | item_name |
|---|---|
| 1 | a |
| 2 | b |
the query above would return:
| cust_id | item_id |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
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
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
PLS-00402 and read up on what causes that problem and see if you can solve it. We cannot help you without knowing what you have done differently (and it is best if you learn how to debug your own code).