基于SCHEDULE列值用LISTAGG填充DAYS列报错求助

Hey there, let's fix that PL/SQL error you're hitting. The "end-of-file" error is caused by several missing syntax elements in your code. Let's break down the issues and correct them step by step:

Key Syntax Issues in Your Original Code #

• You're using WHEN clauses without wrapping them in a CASE expression (PL/SQL requires CASE to start conditional logic like this)
• The UPDATE statement isn't properly closed: you need END CASE to terminate the conditional block, plus a semicolon at the end of the UPDATE
• Your PL/SQL block is incomplete—you're missing the closing END; for the BEGIN block

Corrected Basic Version #

Here's the fixed code that addresses all syntax errors:

BEGIN
  UPDATE schedules
  SET days = CASE
              WHEN schedule = 'Weekend' THEN (
                SELECT LISTAGG(day, ', ') WITHIN GROUP (ORDER BY day_order)
                FROM days
                WHERE schedule = 'Weekend'
              )
              WHEN schedule = 'Weekday' THEN (
                SELECT LISTAGG(day, ', ') WITHIN GROUP (ORDER BY day_order)
                FROM days
                WHERE schedule = 'Weekday'
              )
            END; -- End CASE and UPDATE statement
END; -- Close the PL/SQL block
/

Optimized Version (Avoid Redundant Queries) #

Your original code runs two separate subqueries for each row, which is inefficient. We can simplify this by using a correlated subquery that matches the schedule value from each row in schedules to the days table directly:

BEGIN
  UPDATE schedules s
  SET days = (
    SELECT LISTAGG(d.day, ', ') WITHIN GROUP (ORDER BY d.day_order)
    FROM days d
    WHERE d.schedule = s.schedule
  );
END;
/

This version does the same job but is cleaner and more efficient—it dynamically pulls the correct day list for each row's schedule value without needing explicit CASE logic.

How This Works #

• The correlated subquery links each row in schedules to the matching rows in days using the schedule column
• LISTAGG concatenates the day values into a comma-separated string, ordered by day_order to ensure the days are in the right sequence
• The PL/SQL block is properly wrapped with BEGIN and END; to execute the update as a single transaction

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