ora-00933:SQL 命令未正确结束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1319992/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ora-00933:SQL command not properly ended
提问by taksIV
I have the following code:
我有以下代码：
begin
for i in 1..2 loop
insert into dba_xy.despatch
select desp_id_seq.nextval,
dbms_random.string('U',5),
trunc(dbms_random.value(0000,9999)),
prod_id from dba_xy.product
prod_name from dba_xy.product;
end loop;
end;
结尾;
When I run it, oracle gives me the following error message:
当我运行它时，oracle 给了我以下错误消息：
prod_name from dba_xy.product;
*
ERROR at line 8: ORA-06550: line 8, column 29: PL/SQL: ORA-00933: SQL command not properly ended ORA-06550: line 3, column 2: PL/SQL: SQL Statement ignored
第 8 行错误：ORA-06550：第 8 行，第 29 列：PL/SQL：ORA-00933：SQL 命令未正确结束 ORA-06550：第 3 行，第 2 列：PL/SQL：SQL 语句被忽略
What I'm trying to do is link the existing prod_id and prod_name with new data inserted into the despatch table. I have set prod_name as a unique key in the product table and prod_id as the primary key and have set both as foreign key constraints in the despatch table. I need to include the prod_name into the despatch table to allow readers of the table to have more understanding of what prod_name needs to be found etc, rather than just giving the prod_id which will make no sense to them at all. But maybe I was thinking that I don't need prod_id in the despatch table. Please help.
我想要做的是将现有的 prod_id 和 prod_name 与插入到调度表中的新数据相关联。我已将 prod_name 设置为产品表中的唯一键，将 prod_id 设置为主键，并将两者都设置为 despatch 表中的外键约束。我需要将 prod_name 包含到 despatch 表中，以使表的读者对需要找到什么 prod_name 等有更多的了解，而不是仅仅提供对他们来说毫无意义的 prod_id。但也许我在想我不需要发送表中的 prod_id 。请帮忙。
After dropping the prod_id column from the despatch table, i altered my code:
从发送表中删除 prod_id 列后，我更改了我的代码：
begin
for i in 1..2 loop
insert into dba_xy.despatch
select desp_id_seq.nextval,
dbms_random.string('U',5),
trunc(dbms_random.value(0000,9999)),
prod_name from dba_xy.product;
end loop;
end; /
结尾; /
and the following error message came up about the unique constraint: begin * ERROR at line 1: ORA-00001: unique constraint (DBA_XY.PROD_NAME_UC) violated ORA-06512: at line 3
并且出现了关于唯一约束的以下错误消息：在第 1 行开始 * 错误：ORA-00001：唯一约束（DBA_XY.PROD_NAME_UC）违反了 ORA-06512：在第 3 行
回答by OMG Ponies
Your ORA-00933 error is due to an incorrectly formatted SELECT statement:
您的 ORA-00933 错误是由于格式不正确的 SELECT 语句造成的：
SELECT desp_id_seq.nextval,
dbms_random.string('U',5),
TRUNC(dbms_random.value(0000,9999)),
prod_id from dba_xy.product
prod_name from dba_xy.product;
...when it should be:
...什么时候应该是：
SELECT DESP_ID_SEQ.nextval,
DBMS_RANDOM.string('U',5),
TRUNC(DBMS_RANDOM.value(0000,9999)),
t.prod_id,
t.prod_name
FROM dba_xy.product t;
You were missing the comma to separate the prod_idand prod_namecolumns, and additionally had a redundant FROM dba_xy.product declaration in the wrong location.
您缺少分隔prod_id和prod_name列的逗号，并且在错误的位置还有一个多余的 FROM dba_xy.product 声明。
That said, the dba_xy.despatchtable should only contain the prod_id. If you need to provide a human readable version of the data, I recommend you construct a view. Example:
也就是说，该dba_xy.despatch表应该只包含 prod_id。如果您需要提供人类可读的数据版本，我建议您构建一个视图。例子：
CREATE VIEW despatch_vw AS
SELECT t.prod_id,
p.prod_name
FROM dba_xy.despatch t
JOIN dba_xy.product p ON p.prod_id = t.prod_id
回答by Plasmer
Could you be getting the unique constraint violation because you are inserting the same rows twice? Is "i" supposed to be used in the where clause of the insert statement or do you really want the rows inserted twice?
您是否会因为两次插入相同的行而导致违反唯一约束？“i”应该用在 insert 语句的 where 子句中，还是您真的想要将行插入两次？
Your first statement has two FROM clauses, which is why you are getting a syntax error.
您的第一条语句有两个 FROM 子句，这就是您收到语法错误的原因。
select desp_id_seq.nextval,
dbms_random.string('U',5),
trunc(dbms_random.value(0000,9999)),
prod_id, --from dba_xy.product
prod_name from dba_xy.product;
