在 Oracle 过程中声明表变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32707112/
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
Declare Table Variable in Oracle Procedure
提问by Eraph
I'm having a heck of a time trying to find an example of this being done. I have a procedure, and as part of that procedure I want to store the results of a SELECT statement so that I can work against that set, and then use it as a reference to update the original records when it's all done.
我有很多时间试图找到一个这样做的例子。我有一个过程，作为该过程的一部分，我想存储 SELECT 语句的结果，以便我可以针对该集合进行操作，然后将其用作参考以在全部完成后更新原始记录。
The difficulty I'm having is in declaring the temporary table variable. Here's an example of what I'm trying to do:
我遇到的困难在于声明临时表变量。这是我正在尝试做的一个例子：
PROCEDURE my_procedure
IS
output_text clob;
temp_table IS TABLE OF MY_TABLE%ROWTYPE; -- Error on this line
BEGIN
SELECT * BULK COLLECT INTO temp_table FROM MY_TABLE WHERE SOME_DATE IS NULL;
-- Correlate results into the clob for sending to email (working)
-- Set the SOME_DATE value of the original record set where record is in temp_table
I get an error on the second occurrence of IS, saying that it is an unexpected symbol. This suggests to me that my table variable declaration is either wrong, or in the wrong place. I've tried putting it into a DECLAREblock after BEGIN, but I just get another error.
第二次出现 时出现错误IS，说这是一个意外的符号。这向我表明我的表变量声明要么错误，要么位置错误。我试过在DECLARE之后将它放入一个块中BEGIN，但我又遇到了另一个错误。
Where should this declaration go? Alternatively, if there is a better solution I'll take that too!
这个声明应该去哪里？或者，如果有更好的解决方案，我也会采纳！
回答by Robert Dupuy
CREATE OR REPLACE PROCEDURE PROCEDURE1 AS
output_text clob;
type temp_table_type IS TABLE OF MY_TABLE%ROWTYPE;
temp_table temp_table_type;
BEGIN
SELECT * BULK COLLECT INTO temp_table FROM MY_TABLE;
END PROCEDURE1;
or
或者
CREATE OR REPLACE PROCEDURE PROCEDURE1 ( output_text OUT clob ) IS
type temp_table_type IS TABLE OF MY_TABLE%ROWTYPE
INDEX BY BINARY_INTEGER;
temp_table temp_table_type;
BEGIN
SELECT * BULK COLLECT INTO temp_table FROM MY_TABLE;
FOR indx IN 1 .. temp_table.COUNT
LOOP
something := temp_table(indx).col_name;
END LOOP;
END PROCEDURE1;
回答by David Gausmann
I had a similiar problem and found this: Selecting Values from Oracle Table Variable / Array?
我有一个类似的问题，发现这个： 从 Oracle 表变量/数组中选择值？
The global temporary table can be used like a regular table, but its content is only temporary (deleted at end of session/transaction) and each session has its own table content. If you don't need dynamic SQL this can be used as good solution:
全局临时表可以像普通表一样使用，但它的内容只是临时的（在会话/事务结束时删除）并且每个会话都有自己的表内容。如果您不需要动态 SQL，这可以用作很好的解决方案：
CREATE GLOBAL TEMPORARY TABLE temp_table
(
column1 NUMBER,
column2 NUMBER
)
ON COMMIT DELETE ROWS;
PROCEDURE my_procedure
IS
output_text clob;
BEGIN
-- Clear temporary table for this session (to be sure)
DELETE FROM temp_table;
-- Insert data into temporary table (only for this session)
INSERT INTO temp_table SELECT * FROM MY_TABLE WHERE SOME_DATE IS NULL;
-- ...
END;
The only disadvantages are, in my opinion, that you got another table and that the temporary table is not dynamic.
在我看来，唯一的缺点是您有另一个表，并且临时表不是动态的。
