如何获取oracle过程中执行的所有选择查询的结果作为该过程的输出?

uelo1irk  于 2022-11-22  发布在  Oracle
关注(0)|答案(2)|浏览(211)

如何获取oracle过程中执行的所有选择查询的结果作为该过程的输出?
我的数据库中有n个表,其中表名以“test”开头(例如test1、test2等),且所有这些表都具有主键列,“id”,其中值从1开始。每个表都有10000条记录。我想获取表名以“test”开始的所有表的名称并且还将这些表中的所有记录作为该过程的输出。我已经编写了一个过程,但由于我使用的是游标,因此它只返回该过程执行的最后一个选择查询的结果。是否有其他替代方法
我当前的手术:

create or replace PROCEDURE TEST (tablenamecursor out sys_refcursor,mycursorresult out sys_refcursor
)
As
 maxx number;
 startindex number := 1;
 strcountquery clob;
 strquery clob;
 strquery1 clob;
 countt number;
 rowcountt number;
 batchsize number :=100;
 tablenamequery clob;
 test clob;
Begin
select count(*) into countt from user_tables where table_name like 'test_%';
loop
    exit when (countt=1);
        test:='test_'||countt;
        tablenamequery := 'SELECT table_name FROM user_tables where table_name = '''||test||'''';
        open tablenamecursor for tablenamequery;
        strcountquery := 'select count(*) from "'||test||'"';
        execute immediate strcountquery into maxx; 
        loop
        exit when (StartIndex>=maxx);
        strquery:='SELECT a.*, Row_number() OVER (ORDER BY "id") AS lookup FROM "'||test||'" 
        a WHERE "id" >='||StartIndex||' and rownum <='||batchsize;
        StartIndex:= StartIndex +batchsize;
        open mycursorresult for strquery;
        End loop;
        countt:=countt-1;
    End loop;
end;

假设我有两个表(比如test1、test2、test3),那么我只能在tablenamecursor游标中得到“test1”,在mycursorresult游标中得到“test1”的最后一组100条记录。但是我需要在过程的第19和25行中以相同的顺序将选择查询的所有结果作为过程的输出。

92dk7w1h

92dk7w1h1#

一个CURSOR实际上是一个指向数据库服务器上的内存区域的指针,该内存区域存储查询的详细信息以及您在结果集中的位置。同一游标中不能有多个结果集,如果多次打开一个游标,则它将关闭前一个游标,并使用新结果集打开新游标;这就是为什么你只得到最后一组结果(因为所有其他的结果都是打开的,然后在指针被下一个指针替换时关闭的)。
您的过程存在根本性的缺陷,因为它返回的结果集不能超过过程签名中定义的两个。
您可以更改签名以返回游标数组或返回包含嵌套游标得游标.
或者,如果所有的表都有相同的结构,那么你可以使用UNION ALL或使用PIPELINED函数,但是,这需要更多的细节来说明你要实现的目标。

o3imoua4

o3imoua42#

也许您可以创建一个函数,为所有(TEST_%)表生成一个单独的SQL Select命令,全部或逐个返回这些命令。下面的函数创建了一个SQL Select命令数组,并返回一个命令,这只是一个示例。为了使函数正常工作,创建了两种可变数组类型:

Create Or Replace Type TABS_ARRAY As VARRAY(32) OF VARCHAR2(32);
/
Create Or Replace Type TEST_CURSORS_ARRAY As VARRAY(32) OF VARCHAR2(1024);
/

--  ---------------------------------------------------------------------------------------------
create or replace Function Get_Test_Cursors(P_Cursor_Number NUMBER) RETURN VARCHAR2 AS
BEGIN
    Declare
        CURSOR cTabs IS
            Select      col.COLUMN_ID "COL_ID", col.OWNER "SCH_NAME", col.TABLE_NAME "TBL_NAME", col.COLUMN_NAME "COL_NAME", 
                        col.DATA_TYPE "COL_TYPE", col.DATA_LENGTH "COL_LEN", col.DATA_PRECISION "COL_PREC", 
                        col.DATA_SCALE "COL_SCALE", col.NULLABLE "COL_NULL",
                        Sum(1) OVER(Partition By col.TABLE_NAME Order By col.TABLE_NAME) "NUM_OF_COLS"
            From        sys.ALL_TAB_COLUMNS col
            Inner Join  sys.ALL_TABLES t ON(col.OWNER = t.OWNER And col.TABLE_NAME = t.TABLE_NAME)
            Where       col.OWNER = 'YOUR_OWNER' And col.TABLE_NAME Like('TEST_%')
            Order By    col.TABLE_NAME, col.COLUMN_ID;
        cSet       cTabs%ROWTYPE;
        --
        i         NUMBER := 0;
        j         NUMBER := 0;
        tbl       VARCHAR2(32) := 'xxx';
        clmn      VARCHAR2(32) := 'xxx';
        varrTabs  TABS_ARRAY := TABS_ARRAY();
        varrCols  TABS_ARRAY := TABS_ARRAY();
        varrCurs  TEST_CURSORS_ARRAY := TEST_CURSORS_ARRAY();
        cmd       VARCHAR2(512) := 'Select ';
        total_cols NUMBER(3) := 0;
    Begin
        Open cTabs;
        Loop
            FETCH cTabs Into cSet;
            EXIT WHEN cTabs%NOTFOUND;
            total_cols := cSet.NUM_OF_COLS;
            If i = 0 OR tbl != cSet.TBL_NAME Then
                tbl := cSet.TBL_NAME;
                j := 0;
                cmd := 'Select ';
                Begin
                    i := To_Number(Replace(cSet.TBL_NAME, 'TEST_', ''));
                    varrTabs.extend;
                    varrTabs(i) := cSet.TBL_NAME;
                    j := j + 1;
                    varrCols.extend;
                    varrCols(j) := cSet.COL_NAME;
                    If j = 1 Then
                        cmd := cmd || cSet.COL_NAME;
                    Else
                        cmd := cmd || ', ' ||cSet.COL_NAME;
                    End If;
                Exception 
                    When OTHERS Then Null;
                End;
            ElsIf i > 0 And tbl = cSet.TBL_NAME Then
                j := j + 1;
                varrCols.extend;
                varrCols(j) := cSet.COL_NAME;
                If j = 1 Then
                    cmd := cmd || cSet.COL_NAME;
                Else
                    cmd := cmd || ', ' ||cSet.COL_NAME;
                End If;
            End If;
                If total_cols <= j Then
                    varrCurs.extend;
                    varrCurs(i) := cmd || ' From ' || tbl; 
                End If;
        End Loop;
        Close cTabs;
        RETURN varrCurs(P_Cursor_Number);
    End;
END Get_Test_Cursors;

如果您的几个TEST表像这里一样...

Create Table TEST_1 (ID Number(6), DATE_OF_ID DATE, NAME_OF_ID VARCHAR2(32));
INSERT INTO TEST_1 VALUES(1, SYSDATE - 5, 'Name 1');
INSERT INTO TEST_1 VALUES(2, SYSDATE - 4, 'Name 2');
INSERT INTO TEST_1 VALUES(3, SYSDATE - 3, 'Name 3');
INSERT INTO TEST_1 VALUES(4, SYSDATE - 2, 'Name 4');
INSERT INTO TEST_1 VALUES(5, SYSDATE - 1, 'Name 5');

--  ---------------------------------------------------------------------------------------------
Create Table TEST_2 (ID Number(6), NAME_OF_ID VARCHAR(32), DESCRIPTION_OF_ID VARCHAR2(512));
INSERT INTO TEST_2 VALUES(10, 'Name 10', 'Description Of 10');
INSERT INTO TEST_2 VALUES(20, 'Name 20', 'Description Of 20');
INSERT INTO TEST_2 VALUES(30, 'Name 30', 'Description Of 30');

--  ---------------------------------------------------------------------------------------------
Create Table TEST_3 (ID Number(6), DATE_OF_ID DATE, NAME_OF_ID VARCHAR(32), REF_ID Number(6), DATE_OF_REF_ID DATE);
INSERT INTO TEST_3 VALUES(100, SYSDATE, 'Name 100', 1, SYSDATE - 5);

Commit;

...则可变数组将收集以下SQL选择:

--  
--  varrCurs containes 3 commands:
--  
--  Select ID, DATE_OF_ID, NAME_OF_ID From TEST_1
--  Select ID, NAME_OF_ID, DESCRIPTION_OF_ID From TEST_2
--  Select ID, DATE_OF_ID, NAME_OF_ID, REF_ID, DATE_OF_REF_ID From TEST_3
--

如何使用这些命令的一些选项,您可以在(Using Dynamic SQL for Multirow Queries)...
如果所有的TEST表都有相同的列,您可以执行UNION,但情况可能并非如此。
下面是一个非常简单的代码,它使用函数(表TEST_1)并输出数据。

SET SERVEROUTPUT ON
Declare
    c         SYS_REFCURSOR;
    cmd       VARCHAR2(512);
    mId       NUMBER(3);
    mDt       DATE;
    mNm       VARCHAR2(32);
Begin
    cmd := GET_TEST_CURSORS(1);
    DBMS_OUTPUT.PUT_LINE(cmd || Chr(10) || Chr(10) || 
                         LPAD('ID', 8, ' ') || '   ' || RPAD('DATE_OF_ID', 12, ' ') || '   ' || RPAD('NAME_OF_ID', 12, ' ') || Chr(10) || 
                         LPAD('-', 8, '-') || '   ' || RPAD('-', 12, '-') || '   ' || RPAD('-', 12, '-'));
    OPEN c FOR cmd;
    LOOP
        FETCH c INTO mId, mDt, mNm;
        EXIT WHEN c%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(LPAD(mId, 8, ' ') || '   ' || RPAD(mDt, 12, ' ') || '   ' || RPAD(mNm, 12, ' '));
    END LOOP;
    CLOSE c;
End;
--  
--  R e s u l t :
--
--        ID   DATE_OF_ID     NAME_OF_ID  
--  --------   ------------   ------------
--         1   08-NOV-22      Name 1      
--         2   09-NOV-22      Name 2      
--         3   10-NOV-22      Name 3      
--         4   11-NOV-22      Name 4      
--         5   12-NOV-22      Name 5

此致

相关问题