oracle Oracle中如何将select语句的结果存入临时表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/48982165/
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
How to store the result of select statement into the temporary table in Oracle?
提问by sandesh jogi
We can write select column1,column2 into #temp from tableNamein SQL Server. But I am unable to write the same query in an Oracle database.
我们可以select column1,column2 into #temp from tableName在 SQL Server 中编写。但是我无法在 Oracle 数据库中编写相同的查询。
I want to store the result of select/insert/delete/update or any result set into a local temporary table in oracle database. How I can do this?
我想将选择/插入/删除/更新的结果或任何结果集存储到 oracle 数据库中的本地临时表中。我怎么能做到这一点？
I am executing below query in my Oracle sql developer tool:
我在我的 Oracle sql 开发人员工具中执行以下查询：
select * into #temp
from bmi;
but I am getting the error as follow please help to find this error.
但我收到如下错误，请帮助查找此错误。
when I execute the same query in Microsoft SQL Server it get executed & #temp table get created which is not present in the database but it can hold the data for that particular session. so i want same scenario in ORACLE database.
当我在 Microsoft SQL Server 中执行相同的查询时，它会被执行并创建 #temp 表，该表不存在于数据库中，但它可以保存该特定会话的数据。所以我想在 ORACLE 数据库中使用相同的场景。
ORA-00911: invalid character 00911. 00000 - "invalid character" *Cause: identifiers may not start with any ASCII character other than letters and numbers. $#_ are also allowed after the first character. Identifiers enclosed by doublequotes may contain any character other than a doublequote. Alternative quotes (q'#...#') cannot use spaces, tabs, or carriage returns as delimiters. For all other contexts, consult the SQL Language Reference Manual. *Action: Error at Line: 1 Column: 15
ORA-00911：无效字符 00911。00000 - “无效字符” *原因：标识符不能以字母和数字以外的任何 ASCII 字符开头。$#_ 也允许在第一个字符之后。双引号括起来的标识符可以包含双引号以外的任何字符。替代引号 (q'#...#') 不能使用空格、制表符或回车作为分隔符。对于所有其他上下文，请参阅 SQL 语言参考手册。*操作：行错误：1 列：15
回答by APC
I want to store the result of select/insert/delete/update or any result set into a local temporary table in oracle database,How I can Do This?
我想将选择/插入/删除/更新的结果或任何结果集存储到oracle数据库的本地临时表中，我该怎么做？
You can't. Oracle doesn't have local temporary tables, it doesn't work like that. But it doesn't need to. Oracle has a very different internal model from SQL Server which means a lot of SQL Server practices are unnecessary in Oracle. (To be fair SQL Server has neat things which Oracle doesn't, like ANSI 92 Joins for DML.)
你不能。Oracle 没有本地临时表，它不会那样工作。但它不需要。Oracle 的内部模型与 SQL Server 非常不同，这意味着在 Oracle 中不需要许多 SQL Server 实践。（公平地说，SQL Server 具有 Oracle 没有的简洁功能，例如 DML 的 ANSI 92 连接。）
The key insight is: you don't want to store the result of select/insert/delete/update or any result set into a local temporary table. That is something you had to do in T-SQL to achieve the end goal of implementing some business logic. But what you actually wanted to doin SQL Server and what you want to doin Oracle is write some code which delivers value to your organisation.
关键的见解是：您不想将选择/插入/删除/更新的结果或任何结果集存储到本地临时表中。这是您在 T-SQL 中必须做的事情才能实现实现某些业务逻辑的最终目标。但是，您实际上想在 SQL Server 中和在 Oracle 中做的是编写一些代码，为您的组织带来价值。
So, with that mindset in place, what do you need to do?
那么，有了这种心态，你需要做什么？
If you want to loop round a result set then perhaps a Cursor Loopis what you're looking for?
如果你想循环一个结果集，那么也许你正在寻找一个 Cursor Loop？
for rec in ( select * from some_table
where the_date = date '2018-02-01' )
loop
...
If you want to work on some data prior to inserting it into a data then perhaps you should use a PL/SQL collection:
如果您想在将某些数据插入数据之前对其进行处理，那么也许您应该使用PL/SQL 集合：
type l_recs is table of some_table%rowtype;
But maybe you just need to understand Oracle's Transaction Management model. A lot of things are possible in pure SQL without any need for procedural framework.
但也许您只需要了解Oracle 的事务管理模型。很多事情都可以在纯 SQL 中实现，而无需任何过程框架。
回答by user7294900
Create temporary table:
创建临时表：
create global temporary table
results_temp (column1, column2)
on commit preserve rows;
and then insert to it from your table:
然后从你的表插入它：
insert into results_temp (column1, column2 )
SELECT column1,column2
FROM source_table
回答by venkatesh
create global temporary table temp_table_name
on commit preserve rows as select column1,column2,columnN from your_table;
