Oracle存储过程的全局临时表的替代方法 [英] Alternate method to global temp tables for Oracle Stored Procedure

问题描述

我已经阅读并了解到，Oracle仅使用全局临时表，而不像MS SQL允许使用#temp表.我遇到的这种情况要求我创建数百个全局临时表，以完成我正在处理的从MS SQL到Oracle的数据库转换.我想知道在Oracle存储过程中是否还有另一种方法，除了创建必须在数据库中维护的所有这些表之外.

谢谢

解决方案

大多数情况下，临时表仅在 存储的proc，然后在末尾被截断.我们不断升级到 我们的应用程序和具有可比性的应用程序可以确保 在一个版本中进行了更改，可以轻松地将其合并到 其他."

T-SQL临时表本质上是内存结构.由于两种RDBMS体系结构的不同，它们在MSSQL中提供的好处在Oracle中不那么明显.因此，如果您要迁移，那么建议您采用一种更适合Oracle的方法.

但是，您的情况有所不同，显然，使两个代码库保持同步将使您的生活更加轻松.

您想使用的最接近临时表的是PL/SQL集合；具体来说就是嵌套表.

有两种声明方法.第一种是使用SQL模板-游标-并基于它定义一个嵌套表类型.第二种是声明一个记录类型，然后在其上定义一个嵌套表.无论哪种情况，都可以使用批量操作填充collection变量.

declare
    -- approach #1 - use a cursor
    cursor c1 is 
          select *
          from t23;
    type nt1 is table of c1%rowtype;
    recs1 nt1;

    -- approach #1a - use a cursor with an explicit projection
    cursor c1a is 
          select id, col_d, col_2 
          from t23;
    type nt1a is table of c1a%rowtype;
    recs1 nt1a;


    -- approach #2 - use a PL/SQL record
    type r2 is record (
        my_id number
        , some_date date
        , a_string varchar2(30)
    );
    type nt2 is table of r2;
    recs2 nt2;
begin
    select *
    bulk collect into recs1
    from t23;

    select id, col_d, col_2
    bulk collect into recs2
    from t23;
end;
/

使用游标具有自动在基础表中反映更改的优点.尽管RECORD在面对基础表的更改时具有稳定性的优点.这只取决于您想要什么:)

PL/SQL参考手册中有一整章. 阅读以了解更多信息.. >

I have read and understand that Oracle uses only global temp tables unlike MS SQL which allows #temp tables. The situation that I have would call for me to create hundreds of Global temp tables in order to complete the DB conversion I am working on from MS SQL to Oracle. I want to know if there is another method out there, within a Oracle Stored Procedure, other than creating all of these tables which will have to be maintained in the DB.

Thank You

解决方案

" Most of the time the only thing the temp tables are used within a stored proc and then truncated at the end. We do constant upgrades to our applications and having them somewhat comparable ensures that when a change is made in one version that it can be easily merged to the other."

T-SQL Temp tables are essentially memory structures. They provide benefits in MSSQL which are less obvious in Oracle, because of differences in the two RDBMS architectures. So if you were looking to migrate then you would be well advised to take an approach more fitted to Oracle.

However, you have a different situation, and obviously keeping the two code bases in sync will make your life easier.

The closest thing to temporary tables as you want to use them are PL/SQL collections; specifically, nested tables.

There are a couple of ways of declaring these. The first is to use a SQL template - a cursor - and define a nested table type based on it. The second is to declare a record type and then define a nested table on that. In either case, populate the collection variable with a bulk operation.

declare
    -- approach #1 - use a cursor
    cursor c1 is 
          select *
          from t23;
    type nt1 is table of c1%rowtype;
    recs1 nt1;

    -- approach #1a - use a cursor with an explicit projection
    cursor c1a is 
          select id, col_d, col_2 
          from t23;
    type nt1a is table of c1a%rowtype;
    recs1 nt1a;


    -- approach #2 - use a PL/SQL record
    type r2 is record (
        my_id number
        , some_date date
        , a_string varchar2(30)
    );
    type nt2 is table of r2;
    recs2 nt2;
begin
    select *
    bulk collect into recs1
    from t23;

    select id, col_d, col_2
    bulk collect into recs2
    from t23;
end;
/

Using a cursor offers the advantage of automatically reflecting changes in the underlying table(s). Although the RECORD provides the advantage of stability in the face of changes in the underlying table(s). It just depends what you want :)

There's a whole chapter in the PL/SQL reference manual. Read it to find out more.

这篇关于Oracle存储过程的全局临时表的替代方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！