ora-00933:SQL命令未正确结束 [英] ora-00933:SQL command not properly ended

问题描述

我有以下代码:

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;

当我运行它时，oracle给我以下错误消息:

When I run it, oracle gives me the following error message:

prod_name from dba_xy.product;
                        *

第8行出现错误: ORA-06550:第8行，第29列: PL/SQL:ORA-00933:SQL命令未正确结束 ORA-06550:第3行，第2列: PL/SQL:忽略了SQL语句

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

我想做的是将现有的prod_id和prod_name与插入调度表中的新数据链接.我在产品表中将prod_name设置为唯一键，在主表中将prod_id设置为主键，并在发货表中将这两者都设置为外键约束.我需要将prod_name包含在发送表中，以使表的读者对需要找到什么prod_name等有更多的了解，而不仅仅是提供对他们毫无意义的prod_id.但是也许我在想，我不需要在派遣表中使用prod_id. 请帮忙.

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列后，我更改了代码:

After dropping the prod_id column from the despatch table, i altered my 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_name from dba_xy.product;        
end loop;

end; /

，并出现以下有关唯一约束的错误消息: 开始 * 第1行发生错误: ORA-00001:唯一约束(DBA_XY.PROD_NAME_UC)被违反 ORA-06512:在第3行

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

推荐答案

您的ORA-00933错误是由于SELECT语句格式错误引起的:

Your ORA-00933 error is due to an incorrectly formatted SELECT statement:

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; 

您没有用逗号分隔prod_id和prod_name列，并且在错误的位置附加了多余的FROM dba_xy.product声明.

You were missing the comma to separate the prod_id and prod_name columns, and additionally had a redundant FROM dba_xy.product declaration in the wrong location.

也就是说， 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

这篇关于ora-00933:SQL命令未正确结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！