Oracle 是否有等效于 SQL Server 的表变量？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/670461/
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
Does Oracle have an equivalent of SQL Server's table variables?
提问by Margaret
In SQL Server, you can declare a table variable (DECLARE @table TABLE), which is produced while the script is run and then removed from memory.
在 SQL Server 中，您可以声明一个表变量 ( DECLARE @table TABLE)，该变量在脚本运行时生成，然后从内存中删除。
Does Oracle have a similar function? Or am I stuck with CREATE/DROPstatements that segment my hard drive?
oracle有没有类似的功能？还是我坚持使用CREATE/DROP语句对我的硬盘进行分段？
采纳答案by Mitch Wheat
Yes.
是的。
Declare TABLE TYPE variables in a PL/SQL declare block. Table variables are also known as index-by table or array. The table variable contains one column which must be a scalar or record datatype plus a primary key of type BINARY_INTEGER. Syntax:
DECLARE TYPE type_name IS TABLE OF (column_type | variable%TYPE | table.column%TYPE [NOT NULL] INDEX BY BINARY INTEGER;
-- Then to declare a TABLE variable of this type: variable_name type_name;
-- Assigning values to a TABLE variable: variable_name(n).field_name := 'some text'; -- Where 'n' is the index value
在 PL/SQL 声明块中声明 TABLE TYPE 变量。表变量也称为索引表或数组。表变量包含一列，该列必须是标量或记录数据类型加上 BINARY_INTEGER 类型的主键。句法：
DECLARE TYPE type_name IS TABLE OF (column_type | variable%TYPE | table.column%TYPE [NOT NULL] INDEX BY BINARY INTEGER;
-- 然后声明一个这个类型的TABLE变量： variable_name type_name;
-- 为 TABLE 变量赋值： variable_name(n).field_name := 'some text'; -- 其中 'n' 是索引值
Ref: http://www.iselfschooling.com/syntax/OraclePLSQLSyntax.htm
参考：http: //www.iselfschooling.com/syntax/OraclePLSQLSyntax.htm
You might want to also take a look at Global Temporary Tables
您可能还想看看全局临时表
回答by Paulo Corrêa
The below solution is the closest from SQL Server I can do today.
下面的解决方案是我今天可以做的最接近 SQL Server 的解决方案。
Objects:
对象：
CREATE OR REPLACE TYPE T_NUMBERS IS TABLE OF NUMBER;
CREATE OR REPLACE FUNCTION ACCUMULATE (vNumbers T_NUMBERS)
RETURN T_NUMBERS
AS
vRet T_NUMBERS;
BEGIN
SELECT SUM(COLUMN_VALUE)
BULK COLLECT INTO vRet
FROM TABLE(CAST(vNumbers AS T_NUMBERS));
RETURN vRet;
END;
Queries:
查询：
--Query 1: Fixed number list.
SELECT *
FROM TABLE(ACCUMULATE(T_NUMBERS(1, 2, 3, 4, 5)));
--Query 2: Number list from query.
WITH cteNumbers AS
(
SELECT 1 AS COLUMN_VALUE FROM DUAL UNION
SELECT 2 AS COLUMN_VALUE FROM DUAL UNION
SELECT 3 AS COLUMN_VALUE FROM DUAL UNION
SELECT 4 AS COLUMN_VALUE FROM DUAL UNION
SELECT 5 AS COLUMN_VALUE FROM DUAL
)
SELECT *
FROM TABLE(
ACCUMULATE(
(SELECT CAST(COLLECT(COLUMN_VALUE) AS T_NUMBERS)
FROM cteNumbers)
)
);
回答by Martlark
Yes it does have a type that can hold the result set of a query (if I can guess what TABLE does). From ask Tom: your procedure may look like this:
是的，它确实有一种可以保存查询结果集的类型（如果我能猜到 TABLE 是做什么的）。从问汤姆：你的程序可能是这样的：
procedure p( p_state in varchar2, p_cursor in out ref_cursor_type )
is
begin
open p_cursor for select * from table where state = P_STATE;
end;
where p_cursor is like a table type. As has been already answered there are plenty of options for storing result sets in Oracle. Generally Oracle PL/SQL is far more powerful than sqlserver scripts.
其中 p_cursor 就像一个表类型。正如已经回答的那样，在 Oracle 中存储结果集有很多选择。通常，Oracle PL/SQL 远比 sqlserver 脚本强大。
回答by mahmoud
the table in variable in oracle not the same as table variables in MS SQLServer. in oracle it's like regular array in java or c#. but in MS SQLserver it is the same as any table, you can call it logical table. but if you want something in oracle that does exactly the same as table variable of SQLserver you can use cursor.
oracle 中的表变量与 MS SQLServer 中的表变量不同。在 oracle 中，它就像 java 或 c# 中的常规数组。但是在 MS SQLserver 中它和任何表一样，你可以称之为逻辑表。但是如果你想在 oracle 中做一些与 SQLserver 的表变量完全相同的东西，你可以使用游标。
regards
问候
