如何修复PL/SQL中的PLS-00306错误？——关于生成客户-商品专属价格的函数执行异常问题

Let's work through this step by step—PLS-00306 is thrown when you call a function with the wrong number, type, or order of arguments, but there are a few other issues in your code that need fixing to get your price generation working as intended.

1. The Direct Cause of PLS-00306 #

Your PRICE_DATA function is defined to accept two parameters (P_CUST_DATA_ID and P_ITEMS_DATA_ID), but in your execution script, you call it with no arguments at all:

L_PRICE_ID := PRICE_DATA; -- Missing required parameters

Worse, looking at your function logic, you don't even use those parameters—you're trying to loop through all customers and all items. So the first fix is to remove those unnecessary parameters from the function definition entirely.

2. Fix Other Critical Code Issues #

Beyond the argument mismatch, there are a few more bugs breaking your logic:

• Duplicate loop variable: You use i for both nested loops, which will overwrite the outer loop's value and break your iteration.
• Typos: You reference P_ITEMS_PRIC_ID when calling ADD_PRICELIST, but your function parameter is named P_ITEMS_DATA_ID (and again, this parameter isn't needed anyway).
• Random seed placement: Resetting dbms_random.seed inside loops will generate the same random number repeatedly, defeating the purpose of random pricing.
• Return value logic: Your function only returns the last generated price, which might not be what you want, but we'll keep it consistent with your original code.

Corrected Function and Execution Script #

Here's the fixed version of your function, aligned with your goal of generating a unique price for every customer-item pair:

CREATE OR REPLACE FUNCTION PRICE_DATA RETURN NUMBER AS 
    L_PRICE NUMBER; 
    L_LAST_GENERATED_PRICE NUMBER;
BEGIN 
    -- Set seed once at the start to ensure consistent randomness across runs
    dbms_random.seed(1000);
    
    -- Loop through all customers
    FOR cust_rec IN (SELECT CUST_ID FROM CUSTOMERS) LOOP 
        -- Loop through all items for each customer
        FOR item_rec IN (SELECT ITEM_ID FROM ITEMS) LOOP 
            -- Generate random price between 0 and 10, rounded to 3 decimals
            L_PRICE := ROUND(dbms_random.value(0, 10), 3);
            -- Insert the price into your PRICE table via ADD_PRICELIST
            ADD_PRICELIST(cust_rec.CUST_ID, item_rec.ITEM_ID, L_PRICE);
            -- Track the last generated price to return
            L_LAST_GENERATED_PRICE := L_PRICE;
        END LOOP; 
    END LOOP; 
    
    RETURN L_LAST_GENERATED_PRICE;
END PRICE_DATA;
/

And the fixed execution script (now calling the function correctly, plus adding a commit for the delete):

DECLARE 
    L_LAST_PRICE NUMBER; 
BEGIN 
    DELETE FROM PRICE;
    COMMIT; -- Ensure delete is finalized before inserting new data
    L_LAST_PRICE := PRICE_DATA; -- No parameters needed now
    DBMS_OUTPUT.PUT_LINE('Last generated price: ' || L_LAST_PRICE);
END;
/

Notes to Match Your Example Data #

• If your CUSTOMERS table uses CUSTOMERS_ID instead of CUST_ID, or ITEMS uses ITEMS_ID instead of ITEM_ID, adjust the column names in the SELECT statements to match your actual schema.
• If ADD_PRICELIST is a function (not a procedure), you'll need to capture its return value (though procedures are standard for insert operations).

内容的提问来源于stack exchange，提问作者GEO