编写PL/SQL存储过程报错求助：ORA-00909参数数量无效

Hey there, let's break down what's going wrong with your procedure and get it working correctly. First, that ORA-00909: invalid number of arguments error is directly caused by misusing Oracle's CONCAT function—but there are a few other logic and syntax bugs we need to fix too.

What's Broken in Your Original Code #

• Incorrect CONCAT Usage: Oracle's CONCAT only accepts 2 arguments. You're trying to pass multiple strings to it, which triggers the argument count error. Use the || operator for multi-string concatenation instead.
• Unbound SELECT Statement: In PL/SQL, you can't run a SELECT without storing the result in a variable (or using a cursor). Your bare SELECT is invalid here.
• Wrong WHERE Condition: Your condition (StartDate-EndDate) = (p_start- p_end) doesn't match your requirement (updating reports where StartDate falls within the input date range). You need StartDate BETWEEN p_start AND p_end instead.
• Misplaced COMMIT: Your COMMIT is positioned as part of the SELECT statement, which is syntactically invalid.
• Implicit Date Conversion Risk: Passing string dates directly can fail if your session's NLS_DATE_FORMAT doesn't match the string format. Always use TO_DATE for explicit conversion.

Corrected Procedure Code #

CREATE OR REPLACE PROCEDURE updateweekly_sales_report (
    p_start IN WEEKLY_SALES_REPORT.StartDate%TYPE,
    p_end IN WEEKLY_SALES_REPORT.EndDate%TYPE
) IS
    -- Variables to hold updated report details
    v_report_id WEEKLY_SALES_REPORT.ReportID%TYPE;
    v_com_amount WEEKLY_SALES_REPORT.ComAmount%TYPE;
    v_com_rate WEEKLY_SALES_REPORT.ComRate%TYPE;
    v_sale_amount WEEKLY_SALES_REPORT.SaleAmount%TYPE;
    
    -- Cursor to fetch updated records for confirmation
    CURSOR c_updated_reports IS
        SELECT ReportID, ComAmount, ComRate, SaleAmount
        FROM WEEKLY_SALES_REPORT
        WHERE StartDate BETWEEN p_start AND p_end;
BEGIN
    -- Update commission amounts for matching reports
    -- Note: If ComRate is stored as a percentage (e.g., 5 = 5%), divide by 100
    UPDATE WEEKLY_SALES_REPORT
    SET ComAmount = SaleAmount * (ComRate / 100)
    WHERE StartDate BETWEEN p_start AND p_end;
    
    -- Loop through updated records to print confirmation messages
    OPEN c_updated_reports;
    LOOP
        FETCH c_updated_reports INTO v_report_id, v_com_amount, v_com_rate, v_sale_amount;
        EXIT WHEN c_updated_reports%NOTFOUND;
        
        DBMS_OUTPUT.PUT_LINE(
            'The commission amount for report ' || v_report_id || 
            ' has been updated to ' || v_com_amount || ' dollars, which is ' || 
            v_com_rate || '% of the total sale amount of ' || v_sale_amount || ' dollars.'
        );
    END LOOP;
    CLOSE c_updated_reports;
    
    -- Commit the transaction after successful updates
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- Rollback on error to avoid partial updates
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error encountered: ' || SQLERRM);
        RAISE;  -- Re-throw the error for upstream handling
END;
/

-- Execute the procedure with explicit date conversion
SET SERVEROUTPUT ON;  -- Enable output to see confirmation messages
BEGIN
    updateweekly_sales_report(
        TO_DATE('2018-04-02', 'YYYY-MM-DD'),
        TO_DATE('2018-04-08', 'YYYY-MM-DD')
    );
END;
/

Key Notes #

• Commission Calculation: I added (ComRate / 100) because it's common to store commission rates as whole numbers (e.g., 5 for 5%). If your ComRate is already a decimal (e.g., 0.05 for 5%), remove the division.
• Error Handling: The exception block ensures we roll back if anything goes wrong, preventing partial updates.
• Output: We use a cursor to fetch and print updated records with DBMS_OUTPUT.PUT_LINE—this replaces your invalid SELECT statement and gives clear feedback.
• Date Safety: Using TO_DATE with a format mask avoids issues with different session date settings.

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