PL/SQL脚本出现所有IF分支均执行异常，附相关执行代码

Let’s dive into why your script is running every IF branch, no matter what payment type you specify, and fix it step by step.

The Root Cause #

The problem comes down to how SQL*Plus substitution variables (&) work. These variables are resolved before your PL/SQL code is compiled. That means even if your IF condition says "only run this when paymentType is 'CARD'", the INSERT statement with &CType, &CCNum, etc., gets replaced with actual user input values and parsed by the database immediately—regardless of the condition. Same goes for the UPDATE in the CASH branch: it’s executed before the PL/SQL logic even checks the payment type.

Key Fixes & Revised Script #

Here’s the corrected version of your script, with explanations of the changes:

DECLARE
  givenExp       ExpMast.expNum%TYPE; -- Use %TYPE for type safety
  paymentType    VARCHAR2(10);        -- Oracle recommends VARCHAR2 over VARCHAR
  cType          VARCHAR2(20);        -- Define variables for all user inputs
  ccNum          VARCHAR2(16);
  amt            NUMBER;
  paymentAmount  NUMBER;
BEGIN
  -- Capture user input into PL/SQL variables first
  givenExp := &desiredExpNum;
  paymentType := '&Type_of_Payment'; -- Single quotes for string inputs to avoid syntax errors
  cType := '&CType';
  ccNum := '&CCNum';
  amt := &Amt;
  paymentAmount := &PaymentAmount;

  -- Validate the expense exists (fixed SELECT: PL/SQL requires INTO)
  DECLARE
    v_expense_exists NUMBER;
  BEGIN
    SELECT 1 INTO v_expense_exists 
    FROM ExpMast 
    WHERE expNum = givenExp;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Error: Expense number ' || givenExp || ' does not exist.');
      RETURN; -- Exit if expense isn't found
  END;

  -- Now conditional logic works as expected
  IF paymentType = 'CARD' THEN
    -- Use PL/SQL variables instead of substitution variables in INSERT
    INSERT INTO ExpByCC(expNum, card_type, cc_number, amount) -- Replace with actual column names
    VALUES (givenExp, cType, ccNum, amt);
  ELSIF paymentType = 'CASH' THEN
    -- Removed unnecessary TABLE keyword from UPDATE
    UPDATE ExpMast 
    SET CashAmt = paymentAmount 
    WHERE ExpNum = givenExp;
  ELSE
    DBMS_OUTPUT.PUT_LINE('Error: Invalid payment type - ' || paymentType);
  END IF;

  COMMIT; -- Commit changes if needed
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK; -- Rollback on error
    DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;
/

Breakdown of Critical Changes #

• Move substitution variables to PL/SQL variables: By capturing all user input into PL/SQL variables first, you ensure the conditional logic controls whether the INSERT or UPDATE runs—instead of the substitution variables forcing all statements to execute upfront.
• Fix the SELECT statement: PL/SQL doesn’t allow bare SELECT *; you need an INTO clause to store the result (here we just check if the expense exists).
• Remove TABLE from UPDATE: Oracle’s UPDATE syntax doesn’t require the TABLE keyword—it’s invalid here.
• Use VARCHAR2 instead of VARCHAR: Oracle’s VARCHAR is ANSI-compliant but VARCHAR2 is the standard for string types in Oracle databases, with more predictable behavior.
• Add error handling: Basic exception handling helps catch missing expenses or unexpected errors, and ensures transactions are rolled back if something goes wrong.

Final Notes #

Make sure to replace the placeholder column names in the INSERT (like card_type, cc_number) with the actual column names from your ExpByCC table. Also, if you’re running this in a tool other than SQL*Plus (like SQL Developer), substitution variables still work, but you might need to adjust how input is captured (e.g., using :variable for bind variables instead of &).

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