Oracle触发器报错PLS-00103：遇符号"("不符合预期，求助排查

Hey there, let's get that PLS-00103 error sorted out for you. That error usually pops up when Oracle hits syntax it doesn't expect, and in your case, the biggest culprit was probably using a reserved keyword as a variable name plus some minor subquery tweaks.

Here's the corrected trigger code that should resolve the error:

CREATE OR REPLACE TRIGGER "SUNSHINE_DEPT" 
BEFORE INSERT ON "PURCHASE" 
FOR EACH ROW 
DECLARE
    v_type VARCHAR2(7); -- Renamed from "type" to avoid conflict with reserved keyword
BEGIN
    SELECT SERVICETYPE 
    INTO v_type 
    FROM (
        SELECT P.SERVICETYPE, D.DNAME 
        FROM DEPT D 
        JOIN EMP E ON D.DEPTNO = E.DEPTNO 
        JOIN PURCHASE P ON E.EMPNO = P.SERVEDBY 
        WHERE D.DNAME = 'SALES - Sunshine' 
          AND P.SERVEDBY = :NEW.SERVEDBY -- Used = instead of IN for single value clarity
    );

    IF v_type = 'Data Recovery' THEN
        :NEW.AMOUNT := :NEW.AMOUNT * 0.7;
    END IF;

    :NEW.PAYMENTTYPE := 'Cash';
END;
/

Key Fixes Explained: #

• Reserved Keyword Conflict: type is a reserved keyword in PL/SQL, so using it as a variable name throws off the parser. Renaming it to v_type (a common prefix for local variables in PL/SQL) eliminates this syntax issue.
• Subquery Clarity: Changed IN (:NEW.SERVEDBY) to = :NEW.SERVEDBY since you're checking against a single value from the new row—this is more readable and avoids any potential parsing confusion with the IN clause.
• Single Row Guarantee: Make sure the subquery always returns exactly one row. If there's a chance it could return zero or multiple rows, you'll want to handle those cases with exception blocks (like NO_DATA_FOUND or TOO_MANY_ROWS) to prevent runtime errors.

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