在打开游标之前，如何在存储过程中创建临时表? [英] How to create a temporary table inside Stored Procedure before opening a cursor?

问题描述

我试图在存储过程中创建一个临时表以对其执行多项操作，然后执行一个select语句.使用全局临时表还没有成功.当我执行下面的代码时，我得到一个ORA-00942 error(表或视图不存在).我必须返回游标才能在Spring中使用它.有什么想法吗?

I'm trying to create a temporary table within a Stored Procedure to perform multiple operations on it and then execute a select statement. I haven't had success using global temporary tables. When I execute the code below I get an ORA-00942 error (table or view does not exists). I must return the cursor to use it in Spring. Any ideas?

PROCEDURE DELETME (
          O_CURSOR         OUT tCursor,
          COD_ERROR        OUT NUMBER,
          MSM_ERROR        OUT VARCHAR2
          )
     AS
BEGIN
    execute immediate 'create global temporary table my_temp_table(column1 number) on commit delete rows';
     insert into my_temp_table(column1) values (1);
    COD_ERROR := 1;
    OPEN o_cursor FOR
           select * from my_temp_table;
EXCEPTION 
    WHEN OTHERS THEN
              COD_ERROR:=0;
              MSM_ERROR:=dbms_utility.format_error_backtrace ||' '||SQLERRM;
END DELETME;

推荐答案

您的过程无法编译，因为您对不存在的TEMP_TABLE有依赖性.因此，ora-00942.当然，如果它确实存在，则您的过程 将在运行时失败:create global temporary table调用将失败，因为该表已经存在.

Your procedure won't compile because you have dependencies on TEMP_TABLE which does not exist. Hence the ora-00942. Of course, if it does exist then your procedure will fail at runtime: the create global temporary table call will fail, because the table already exists.

基本上，您误解了Oracle中全局临时表的用途.它们是永久性结构，只是它们持有的数据是瞬态的.这是一个常见问题，特别是对于那些熟悉SQL Server并试图将T-SQL转换为Oracle的人而言. MSSQL中的临时表更像PL/SQL集合.

Basically you have misunderstood the purpose of global temporary tables in Oracle. They are permanent structures, it is just the data they hold which is transient. This is a common issue, especially for people who are familiar with SQL Server and are trying to convert T-SQL into Oracle. Temporary tables in MSSQL are more like PL/SQL collections.

很明显，您发布的代码是一个玩具，因此不清楚为什么您认为需要一个临时表.您很可能不会这样做，因为Oracle SQL非常强大.您可能只是打开一个用于复杂SELECT语句的ref游标.

Obviously your posted code is a toy so it's not clear why you think you need a temporary table. It's quite likely that you don't, as Oracle SQL is pretty powerful. Chances are you can just open a ref cursor for a complex SELECT statement.

但是您确实有一些真正的需要，这是怎么做的:

But it you do happen to have some real need this is how to do it:

首先，作为一次练习:

create global temporary table my_temp_table
   (column1 number) on commit delete rows   
    tablespace temporary_ts;

请注意tablespace子句:GTT写入磁盘而不是内存，这意味着它们的填充速度很慢，读取速度也很慢.如果您要使用GTT，最好为它们专门使用一个专用的临时表空间，因为与其他临时过程(如sort)相比，它们具有不同的使用情况.

Note the tablespace clause: GTTs write to disk not memory, which means they are slow to populate and slow to read. If you're going to use GTTs it's a good idea to have a dedicated temporary tablespace just for them, because they have a different usage profile compare to other temporary processes such as sorts.

无论如何，您的程序将变为

Anyway, your procedure becomes

PROCEDURE DELETME (
          O_CURSOR         OUT tCursor,
          COD_ERROR        OUT NUMBER,
          MSM_ERROR        OUT VARCHAR2
          )
     AS
BEGIN
    insert into my_temp_table(column1) values (1);
    COD_ERROR := 1;
    OPEN o_cursor FOR
           select * from my_temp_table;
EXCEPTION 
    WHEN OTHERS THEN
              COD_ERROR:=0;
              MSM_ERROR:=dbms_utility.format_error_backtrace ||' '||SQLERRM;
END DELETME;

请记住，您需要发出一个提交(或回滚)来清除表；如果您不妥善保管它，则当同一会话重新使用它时可能会产生问题.

Remember you need to issue a commit (or rollback) to clear your table; if you don't housekeep it that may create problems when the same session re-uses it.

或者，使用集合.收集要快得多，因为它们是内存结构.尽管内存来自会话分配，所以如果总行数太大，这不是最佳解决方案.

Alternatively, use a collection instead. Collections are much faster, because they are memory structures. Although the memory comes from session allocations so this is not the best solution if the total number of rows is too large.

类似这样的事情.再一次，作为一次性练习:

Something like this. Again, as a one-off exercise:

create or replace object num_nt as table of number;

然后您的过程将变为:

PROCEDURE DELETME (
          O_CURSOR         OUT tCursor,
          COD_ERROR        OUT NUMBER,
          MSM_ERROR        OUT VARCHAR2
          )
     AS
    local_nt num_nt;
BEGIN
    select 1 
    bulk collect into local_nt
    from dual;
    COD_ERROR := 1;
    OPEN o_cursor FOR
           select * from table(local_nt);
EXCEPTION 
    WHEN OTHERS THEN
              COD_ERROR:=0;
              MSM_ERROR:=dbms_utility.format_error_backtrace ||' '||SQLERRM;
END DELETME;

---

第三个解决方案"是对过程中的所有调用使用动态SQL.这是一个非常糟糕的方法(即使误解了使用全局临时表).动态代码比常规代码脆弱，仅应在真正必要时使用.执行DDL非常昂贵，并且不是标准业务处理的一部分.这也使交易复杂化.

---

There is a third "solution" which is to use dynamic SQL for all the calls in the procedure. This is a really bad approach (even apart from misunderstanding the use of global temporary tables). Dynamic code is flakier than regular code, and should only be used when really necessary. Executing DDL is expensive and not something to be done as part of standard business processing; also it complicates transactions.

这篇关于在打开游标之前，如何在存储过程中创建临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！