java - Oracle异常: SQL command not properly ended

Question

我正在使用 JSP 和 Servlet 开发 Web 应用程序(IDE:Eclipse，容器:Tomcat7.0，DB:Oracle 10)

我想在单个查询中从两个表中获取数据

查询:

query = "select * from PROTOCOL as a, ACTIONS as b where a.PROTOCOL_ID = b.PROTOCOL_ID";

但是运行应用程序后，我收到以下异常:

java.sql.SQLException: ORA-00933: SQL command not properly ended

查询有问题吗？

Answer 1

您遇到的问题是关键字AS。这用于 SELECT 部分中的列。它对于您指定表的 FROM 无效。

你有

从协议(protocol)中选择 * 作为 a，从操作中选择 * 作为 b

应该是

从协议(protocol) a、操作 b...中选择 *

来自Oracle Docs

t_alias

Specify a correlation name, which is alias for the table, view, materialized view, or subquery for evaluating the query. This alias is required if the select list references any object type attributes or object type methods. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.

示例:

SELECT select_list 
    FROM table1 t_alias1 
    WHERE expr operator 
        (SELECT column_list 
            FROM table2 t_alias2 
            WHERE t_alias1.column 
               operator t_alias2.column);