oracle 为什么我的查询会产生错误“ORA-00933：SQL 命令未正确结束”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/764065/
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
Why does my query produce error "ORA-00933: SQL command not properly ended"?
提问by
My query:
我的查询：
CREATE VIEW cambiodatos AS
SELECT
a.last_name||','||a.first_name AS "Nombre",
a.salary AS "Salario",
b.name AS "Nombre Departamento",
c.name AS "Nombre de Region"
FROM
s_emp a, s_dept b, s_region c
WHERE
a.dept_id = b.id AND b.region_id = c.id
UPDATE
cambiodatos
SET
name = 'North America'
WHERE
last_name = 'Biri'||','||first_name = 'Ben'
The error:
错误：
ORA-00933: SQL command not properly ended
回答by Lasse V. Karlsen
The exact reason for why you're getting that error is that you have this WHERE-clause:
你得到这个错误的确切原因是你有这个 WHERE 子句：
last_name = 'Biri'||','||first_name = 'Ben'
This is not legal syntax.
这不是合法的语法。
This would be:
这将是：
last_name = 'Biri' AND first_name = 'Ben'
Or something like this:
或者像这样：
name = 'Biri'||','||'Ben'
but then you could just write it like this:
但是你可以这样写：
name = 'Biri,Ben'
The problem is that it looks to me that you're using the second || there as an AND clause, but that doesn't fit in with the comma you're trying to add.
问题是在我看来你正在使用第二个 || 作为 AND 子句，但这不适合您尝试添加的逗号。
Perhaps you're trying to execute this?
也许您正在尝试执行此操作？
last_name || ',' || first_name = 'Biri,Ben'
In any case, as others have pointed out, if you fix that syntax problem, you'll just get other error message about missing column names.
在任何情况下，正如其他人指出的那样，如果您解决了该语法问题，您只会收到有关缺少列名的其他错误消息。
回答by Quassnoi
First, separate you queries with a semicolon and fix your SETconditions:
首先，用分号分隔您的查询并修复您的SET条件：
CREATE VIEW cambiodatos AS
SELECT
a.last_name||','||a.first_name AS "Nombre",
a.salary AS "Salario",
b.name AS "Nombre Departamento",
c.name AS "Nombre de Region"
FROM
s_emp a, s_dept b, s_region c
WHERE
a.dept_id = b.id AND b.region_id = c.id;
UPDATE
cambiodatos
SET
name = 'North America'
WHERE
last_name = 'Biri'
AND first_name = 'Ben'
That's the reason of your error ORA-00933
这就是你错误的原因 ORA-00933
Second, your UPDATEstatement will fail, as the view you created does not contain field name.
其次，您的UPDATE语句将失败，因为您创建的视图不包含 field name。
This query will compile:
此查询将编译：
UPDATE
cambiodatos
SET
"Nombre de Region" = 'North America'
WHERE
"Nombre" = 'Biri, Ben'
, but most probably will fail as s_regionis not key-preservedin this view.
，但很可能会失败，因为在此视图中s_region并非key-preserved如此。
To update, use this instead:
要更新，请改用它：
MERGE
INTO s_region c
USING (
SELECT b.region_id
FROM s_emp a, s_dept b
WHERE a.last_name || ',' || a.first_name = 'Biri, Ben'
AND b.id = a.dept_id
) q
ON c.id = q.region_id
WHEN MATCHED THEN
UPDATE
SET c.name = 'North America'
回答by Johnno Nolan
Looks like you want an AND in the update
看起来您想要更新中的 AND
UPDATE
cambiodatos
SET
name = 'North America'
WHERE
last_name = 'Biri' AND first_name = 'Ben'
回答by Fredrik
I haven't used Oracle at all during the last 7 years or so but don't you need a ; at the end of the statements?
在过去 7 年左右的时间里，我根本没有使用过 Oracle，但您不需要 ; 在声明的结尾？
回答by tvanfosson
First, I think that your UPDATE command is poorly formatted. Second, you are using fields from the underlying tables instead of the view that you are running the update against. Also, I don't think you can update a view that based on a join. See the response to this question. If you could might look like this.
首先，我认为您的 UPDATE 命令格式不正确。其次，您使用的是基础表中的字段，而不是运行更新所针对的视图。另外，我认为您无法更新基于连接的视图。请参阅对这个问题的回答。如果你可以看起来像这样。
UPDATE
cambiodatos
SET
[Nombre de Region] = 'North America'
WHERE
Nombre = 'Biro, Ben'
回答by blispr
CREATE VIEW cambiodatos AS SELECT a.last_name||','||a.first_name AS "Nombre", a.salary AS "Salario", b.name AS "Nombre Departamento", c.name AS "Nombre de Region" FROM s_emp a, s_dept b, s_region c WHERE a.dept_id = b.id AND b.region_id = c.id ; /* missing semicolon? */
CREATE VIEW cambiodatos AS SELECT a.last_name||','||a.first_name AS "Nombre", a.salary AS "Salario", b.name AS "Nombre Departamento", c.name AS "Nombre de Region" FROM s_emp a, s_dept b, s_region c WHERE a.dept_id = b.id AND b.region_id = c.id ; /* 缺少分号？*/
UPDATE cambiodatos SET name = 'North America' WHERE last_name = 'Biri'||','||first_name = 'Ben' /* missing an And last_name = <> AND first_name = <> */
UPDATE cambiodatos SET name = 'North America' WHERE last_name = 'Biri'||','||first_name = 'Ben' /* 缺少一个 And last_name = <> AND first_name = <> */
