在CURSOR中使用Insert语句中的where子句

I am running this code and am getting this error ORA-00933 SQL command not properly ended

我正在运行此代码并收到此错误ORA-00933 SQL命令未正确结束

How would you write this code so this error does not exist? member_addresses_to_remove is a CURSOR.

您将如何编写此代码,以便此错误不存在? member_addresses_to_remove是一个游标。

 FOR curr_element IN member_addresses_to_remove 
  LOOP
    INSERT INTO schema.tablename(ID,NAME, PHONE_NUMBER)
    VALUES (curr_element.ID, curr_element.NAME,curr_element.PHONE_NUMBER)
    WHERE NOT EXISTS (SELECT 1
                      FROM SCHEMA.OTHERTABLE OT
                      WHERE OT.ID = curr_element.ID);                        
  END LOOP;
COMMIT;

2 个解决方案

#1

Your syntax is incorrect because you are combining a values clause with a where clause.

您的语法不正确,因为您正在将values子句与where子句组合在一起。

Try this:

 FOR curr_element IN member_addresses_to_remove 
  LOOP
    INSERT INTO schema.tablename(ID,NAME, PHONE_NUMBER)
    SELECT curr_element.ID, curr_element.NAME,curr_element.PHONE_NUMBER
    FROM DUAL
    WHERE NOT EXISTS (SELECT 1
                      FROM SCHEMA.OTHERTABLE OT
                      WHERE OT.ID = curr_element.ID);                        
  END LOOP;
COMMIT;

#2

Insert statement syntax is wrong, pls check below example.

插入语句语法错误,请查看下面的示例。

INSERT INTO trg_tbl (col1, col2, col3)
SELECT col1,
       col2,
       col3
FROM src_tbl
WHERE col4 = myvale;

test SQL by executing single SQL statement in SQLPlus then add it procedure

通过在SQLPlus中执行单个SQL语句来测试SQL,然后添加它的过程

#1