oracle - PLS-000428 error Oracle stored procedure with CTE

Question

I am stuck in an issue while trying to create an Oracle stored procedure. I am providing the skeleton of the code below.

Create or replace procedure "SP_UPDATE_DATA" AS
BEGIN
WITH CTE1 AS
(
SELECT A.F1, A.F2 ,B.F3 FROM A, B WHERE A.X1=B.X1
),
CTE2 AS
(
SELECT A.F1,A.F2,C.F5
FROM
CTE1 A,C
WHERE A.F1=C.F1
)
UPDATE TABLE MAIN
SET XYZ=CTE2.F2
FROM
CTE2
WHERE
MAIN.F1.CTE2.F2

When I am trying to compile the procedure I am getting error

AN INTO CLAUSE IS EXPECTED IN SELECT

Though I am actually not using any variable, also not returning anything not selecting anything, still facing the error. Not sure if it has to do something with the CTE.

Need your input to reach a resolution.

Thanks in advance.

标签： oraclestored-proceduresplsql

Answer 1

它应该看起来像这样：

CREATE OR REPLACE PROCEDURE sp_update_data
AS
BEGIN
   
   UPDATE MAIN
      SET XYZ = ( WITH
                     CTE1 AS
                     ( SELECT A.F1, A.F2, B.F3
                         FROM A, B
                        WHERE A.X1=B.X1
                     ),
                     CTE2 AS
                     ( SELECT A.F1, A.F2, C.F5
                         FROM CTE1 A, C
                        WHERE A.F1=C.F1
                     )
                  SELECT CTE2.F2
                    FROM CTE2
                   WHERE MAIN.F1=CTE2.F2
                );

END sp_update_data;
/