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屋!