I want my function price data to takes the ids from the customers and for each item to make a special price.. example:

1. cust_id | item_id | price
2. 30 14 11
3. 30 25 4
4. 31 14 12
5. 31 25 6

CREATE OR REPLACE FUNCTION PRICE_DATA(
        P_CUST_DATA_ID CUSTOMERS.CUSTOMERS_ID%TYPE,
        P_ITEMS_DATA_ID ITEMS.ITEMS_ID%TYPE)
   RETURN NUMBER AS
        L_PRICE NUMBER;
BEGIN
    dbms_random.seed(1000);
      FOR i IN(SELECT CUSTOMERS_ID FROM CUSTOMERS) LOOP
        FOR i IN(SELECT ITEMS_ID FROM ITEMS) LOOP
           L_PRICE := ADD_PRICELIST(P_CUST_DATA_ID,
                                                P_ITEMS_PRIC_ID,
                                                ROUND(dbms_random.value(0,10),3)); 
        END LOOP;
      END LOOP;
 RETURN L_PRICE;    
END PRICE_DATA;

I run this script:

DECLARE
 L_PRICE_ID NUMBER;

BEGIN 
  DELETE FROM PRICE;
   L_PRICE_ID := PRICE_DATA;
END;

And i get a PLS-00306 ERROR in PRICE DATA

CodePudding user response：

In your function, you have P_ITEMS_PRIC_ID it should be P_ITEMS_DATA_ID. You probably don't want to call both row variables i either.

Assuming your ADD_PRICELIST function does not have side-effects (it should not), you can rewrite it as:

CREATE OR REPLACE FUNCTION PRICE_DATA(
  P_CUST_DATA_ID  IN CUSTOMERS.CUSTOMERS_ID%TYPE,
  P_ITEMS_DATA_ID IN ITEMS.ITEMS_ID%TYPE
)
  RETURN NUMBER
AS
  L_PRICE NUMBER;
BEGIN
  dbms_random.seed(1000);
  SELECT ADD_PRICELIST(
           p_cust_data_id,
           p_items_data_id,
           ROUND(dbms_random.value(0,10),3)
         )
  INTO   L_PRICE
  FROM   customers c
         CROSS JOIN items i
  ORDER BY c.customers_id DESC, i.items_id DESC
  FETCH FIRST ROW ONLY;
    
  RETURN L_PRICE;    
END PRICE_DATA;
/

But that seems overkill as you do not use any values from the tables and are effectively just checking that at least one row exists in each table (and inefficiently calling ADD_PRICELIST multiple times) then you can simplify it to just checking the rows exist and then calling the function once:

CREATE OR REPLACE FUNCTION PRICE_DATA(
  P_CUST_DATA_ID  IN CUSTOMERS.CUSTOMERS_ID%TYPE,
  P_ITEMS_DATA_ID IN ITEMS.ITEMS_ID%TYPE
)
  RETURN NUMBER
AS
  L_PRICE  NUMBER;
  l_exists NUMBER(1,0);
BEGIN
  dbms_random.seed(1000);
  SELECT CASE
         WHEN EXISTS(SELECT 1 FROM customers)
         AND  EXISTS(SELECT 1 FROM items)
         THEN 1
         ELSE 0
         END
  INTO   l_exists
  FROM  DUAL;

  IF l_exists = 1 THEN
    l_price := ADD_PRICELIST(
                 p_cust_data_id,
                 p_items_data_id,
                 ROUND(dbms_random.value(0,10),3)
               );
  END IF;
    
  RETURN L_PRICE;    
END PRICE_DATA;
/

Then you need to specify all the arguments when you call the function:

DECLARE
  L_PRICE_ID NUMBER;
BEGIN 
  --DELETE FROM PRICE;
  L_PRICE_ID := PRICE_DATA(3.14159, 2.71828);
  DBMS_OUTPUT.PUT_LINE(l_price_id);
END;
/

db<>fiddle here