PL/SQL匿名块报错PLS-00103：移除邮箱@前内容的代码问题求助

Let’s break down your issue and resolve it step by step:

The Issue #

You wrote a PL/SQL block to remove all content before the @ symbol in the email column of the invoices table, but you’re hitting a PLS-00103 error with this message:

PLS-00103: Encountered the symbol "(" when expecting one of the following: := . ) , @ % default character The symbol ":=" was substituted for "(" to continue.

Your Original Code #

DECLARE v_id NUMBER(10); v_email VARCHAR2(30); i NUMBER(1) := 0; CURSOR cursor_name IS SELECT Id, (substr (email, instr (email,'@',1) + 1 )) AS Domain FROM invoices WHERE id=31232681; BEGIN OPEN cursor_name; LOOP v_email := '@' || v_email; FETCH cursor_name INTO v_id, v_email; UPDATE invoices SET email = v_email WHERE Id = v_id; EXIT WHEN cursor_name%NOTFOUND END LOOP; CLOSE cursor_name; END;

Root Cause #

As you identified, the error stems from a missing semicolon (;) at the end of the EXIT WHEN statement. PL/SQL requires every statement to terminate with a semicolon, and the parser gets confused without it—leading to that misleading symbol error message.

Corrected Code #

Here’s the fixed version, plus some formatting improvements to make the code easier to read (and avoid future syntax issues):

DECLARE
    v_id NUMBER(10);
    v_email VARCHAR2(30);
    i NUMBER(1) := 0; -- Note: This variable isn't used in your code, feel free to remove it
    CURSOR cursor_name IS
        SELECT Id, SUBSTR(email, INSTR(email, '@', 1) + 1) AS Domain
        FROM invoices
        WHERE id = 31232681;
BEGIN
    OPEN cursor_name;
    LOOP
        FETCH cursor_name INTO v_id, v_email;
        v_email := '@' || v_email; -- Moved this after fetch to avoid using an uninitialized value
        UPDATE invoices SET email = v_email WHERE Id = v_id;
        EXIT WHEN cursor_name%NOTFOUND; -- Added missing semicolon here
    END LOOP;
    CLOSE cursor_name;
END;
/

Quick Bonus Tips #

• I moved the v_email := '@' || v_email; line after the FETCH statement. In your original code, you were concatenating @ to an uninitialized v_email before fetching the actual domain value, which would have resulted in an invalid @null string for the first iteration.
• The i variable is declared but never used—clean it up if you don’t plan to use it later.
• Formatting your PL/SQL with line breaks and indentation makes syntax errors way easier to spot at a glance!

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