在Oracle过程中声明表变量 [英] Declare Table Variable in Oracle Procedure

问题描述

我想找一个例子来完成这项工作.我有一个过程，作为该过程的一部分，我想存储SELECT语句的结果，以便可以对该集合进行处理，然后在完成所有操作后将其用作更新原始记录的引用.

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.

我遇到的困难是声明临时表变量.这是我要执行的操作的示例:

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

第二次出现IS时出现错误，说这是一个意外的符号.这向我表明我的表变量声明是错误的，或者是在错误的位置.我试过将它放在BEGIN之后的DECLARE块中，但是我遇到另一个错误.

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 DECLARE block after BEGIN, but I just get another error.

此声明应该去哪里?另外，如果有更好的解决方案，我也会采取！

Where should this declaration go? Alternatively, if there is a better solution I'll take that too!

推荐答案

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;

或

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;

这篇关于在Oracle过程中声明表变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！