错误(4125):PL/SQL:ORA-00933:SQL命令未正确结束

Question

将此WHERE ods_workday_w.test_table.CUSTOMER_ID = 12子句添加到视图时出现语法错误，

CREATE OR REPLACE PROCEDURE TEST_POC IS
BEGIN
INSERT INTO ods_workday.test_table (CUSTOMER_ID, CUSTOMER_NAME, CITY)
SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY FROM ods_workday_w.test_table WHERE ods_workday_w.test_table.CUSTOMER_ID = 12
WHERE NOT EXISTS (SELECT * FROM ods_workday.test_table 
WHERE ods_workday.test_table.CUSTOMER_ID = ods_workday_w.test_table.CUSTOMER_ID AND 
ods_workday.test_table.CUSTOMER_NAME = ods_workday_w.test_table.CUSTOMER_NAME AND
ods_workday.test_table.CITY = ods_workday_w.test_table.CITY AND ROWNUM = 1);
END TEST_POC

错误(3,5(：PL/SQL:SQL语句被忽略错误(5,5(：PL/SQL:ORA-00933：SQL命令未正确结束

当ods_workday_w.test_table.CUSTOMER_ID=12(传递给存储过程的数字输入(时，我想从ods_workday _w.test_table中选择属性，并使用所选属性的值填充目标表。

Answer 1

不能有2个WHERE；另一个必须是例如CCD_ 3(参见注释(。另外，使用分号终止块。最后，考虑使用表别名而不是完整的表名(以提高可读性(。正如@a_house_with_no_name所评论的那样，用斜线/终止整个CREATE语句。

CREATE OR REPLACE PROCEDURE TEST_POC
IS
BEGIN
INSERT INTO ods_workday.test_table (CUSTOMER_ID, CUSTOMER_NAME, CITY)
SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY
FROM ods_workday_w.test_table
WHERE     ods_workday_w.test_table.CUSTOMER_ID = 12
AND NOT EXISTS                                             --> here
(SELECT *
FROM ods_workday.test_table
WHERE     ods_workday.test_table.CUSTOMER_ID =
ods_workday_w.test_table.CUSTOMER_ID
AND ods_workday.test_table.CUSTOMER_NAME =
ods_workday_w.test_table.CUSTOMER_NAME
AND ods_workday.test_table.CITY =
ods_workday_w.test_table.CITY
AND ROWNUM = 1);
END TEST_POC;

Answer 2

我不知道如何在无格式和有详细引用的情况下读取代码。直到我把它整理得干干净净，我才明白它的意思：

create or replace procedure test_poc is
begin
insert into ods_workday.test_table
( customer_id
, customer_name
, city )
select t1.customer_id
, t1.customer_name
, t1.city
from   ods_workday_w.test_table t1
where  t1.customer_id = 12
and    not exists
( select 1
from   ods_workday.test_table t2
where  t2.customer_id = t1.customer_id
and    t2.customer_name = t1.customer_name
and    t2.city = t1.city );
end test_poc;