时间戳

首页 > 解决方案 > 如何使用 jdbc oracle 插入和选择全局临时表？

java - 如何使用 jdbc oracle 插入和选择全局临时表？

问题描述

 String query = "INSERT INTO St (id) values ('Admin'); "  + 
            "select * from Student where id in (select id from St);";

如果尝试执行上述查询会出错

Exception in thread "main" java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:494)
at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:446)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:1054)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:623)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:252)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:612)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:226)
at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:59)
at oracle.jdbc.driver.T4CPreparedStatement.executeForRows(T4CPreparedStatement.java:910)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1119)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3780)
at oracle.jdbc.driver.T4CPreparedStatement.executeInternal(T4CPreparedStatement.java:1343)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3822)
at oracle.jdbc.driver.OraclePreparedStatementWrapper.executeQuery(OraclePreparedStatementWrapper.java:1165)
at com.prototype.service.DBConnection.main(DBConnection.java:167)

原因：错误：933，位置：36，Sql = INSERT INTO St (id) values ('Admin'); select * from Student where id in (select id from St);, OriginalSql = INSERT INTO St (id) values ('Admin'); select * from Student where id in (select id from St);, Error Msg = ORA-00933: SQL 命令未正确结束

标签： javaoracle

解决方案

您可以使用插入选择语句，这样可以避免多语句查询 

String query = "INSERT INTO St (id) 
            select your_col_for_id  from Student where id in (select id from St);";

并且您必须确保在 into 子句和 select 子句中有相应数量和类型的列

推荐阅读