代码之家 › 专栏 › 技术社区 › Lolly

Oracle存储过程合并结果

oracle11g plsql stored-procedures

0

Lolly · 技术社区 · 6 年前

我正在处理以下Oracle(PL/SQL)存储过程:

Procedure myProc(idParam IN Number, RESULT OUT SYS_REFCURSOR)
IS
BEGIN
    FOR myMetaData in (select status, idData from Table1 where id=idParam)
    LOOP
        IF myMetaData.status='test1'
           SELECT column1, column2, column3 from Table2 where cond1=cond2;
        ELSE
           SELECT column1, column2, column3 from Table2 where column4=  
            (select column4 from.....);
        END IF; 
    END LOOP;
END myProc;

假设上面是我的代码,现在我需要返回IF子句select语句和Else子句的组合结果。我尝试使用dbms_sql.return_result();但没用。

1 回复 | 直到 4 年前

1

XING 6 年前

有很多方法可以达到您的要求,但我更愿意使用 table

Create table Rslt (col1 number,col2 number, col3 number);
/

--使用 sysrefcursor 从表中得到最终结果

Procedure myProc(idParam IN Number, RESULT OUT SYS_REFCURSOR)
IS
BEGIN
    FOR myMetaData in (select status, idData from Table1 where id=idParam)
    LOOP
        IF myMetaData.status='test1'
          insert into rslt SELECT column1, column2, column3 from Table2 where cond1=cond2;
        ELSE
          insert into rslt SELECT column1, column2, column3 from Table2 where column4=  
            (select column4 from.....);
        END IF; 
    END LOOP;

    Open result for select * from rslt;

END myProc;

Create type rslt is object
                  (col1 number,
                   col2 number, 
                   col3 number
                  );

Create type var_rslt is table of rslt ;

Procedure myProc(idParam IN Number, V_RESULT OUT SYS_REFCURSOR)
IS

v_rslt1  var_rslt:=var_rslt();
v_rslt2  var_rslt:=var_rslt();
v_rslt3  var_rslt:=var_rslt();
v_rslt4  var_rslt:=var_rslt();

BEGIN
    FOR myMetaData in (select status, idData from Table1 where id=idParam)
    LOOP
        IF myMetaData.status='test1'
          SELECT rslt(column1, column2, column3) bulk collect into v_rslt1 from Table2 where cond1=cond2;

          v_rslt2:=v_rslt2 Multiset union all v_rslt1;

        ELSE
          SELECT rslt(column1, column2, column3) bulk collect into v_rslt13 from Table2 where column4= (select column4 from.....);
          v_rslt4:=v_rslt4 multiset union all v_rslt13;

        END IF; 
    END LOOP;

    v_rslt2 := v_rslt2 multiset union all v_rslt4; 

    OPEN V_RESULT FOR SELECT * FROM table( v_rslt2 );    

END myProc;

表格准备:

Create table Table1 (id number, status varchar2(10));
/

Insert into table1 values(1,'test1');
Insert into table1 values(2,'test2');

Create table Table2 (id number,column1 number, column2 number, column3 number);
/
insert into table2 values(1,10,20,30);
insert into table2 values(1,70,60,50);
insert into table2 values(1,20,40,30);
insert into table2 values(2,80,40,20);
insert into table2 values(2,60,20,10);


Create type rslt is object
                  (col1 number,
                   col2 number, 
                   col3 number
                  );

Create type var_rslt is table of rslt ;

程序:

CREATE OR REPLACE Procedure myProc(idParam IN Number, V_RESULT OUT sys_refcursor)
IS

v_rslt1  var_rslt:=var_rslt();
v_rslt2  var_rslt:=var_rslt();
v_rslt3  var_rslt:=var_rslt();
v_rslt4  var_rslt:=var_rslt();

BEGIN
    FOR myMetaData in (select status, id from Table1)
    LOOP
        IF myMetaData.status='test1' then
          SELECT rslt(column1, column2, column3) bulk collect into v_rslt1 from Table2 where id=myMetaData.id;

          v_rslt2:=v_rslt2 Multiset union all v_rslt1;

        ELSE
          SELECT rslt(column1, column2, column3) bulk collect into v_rslt3 from Table2 where id=myMetaData.id;

         v_rslt4:=v_rslt4 multiset union all v_rslt3;

        END IF; 
    END LOOP;

     v_rslt4 := v_rslt4 multiset union all v_rslt2;      

     open V_RESULT for Select * from table(v_rslt4);

END myProc;

DECLARE
  var sys_refcursor;
  var1 NUMBER;
  var2 NUMBER;
  var3 NUMBER;
BEGIN
  myProc(1, var);
  LOOP
    FETCH var INTO var1,var2,var3;
    EXIT  WHEN var%notfound;
    dbms_output.put_line(var1);
  END LOOP;
END;

输出:

SQL> /
anonymous block completed

80
60
10
70
20

注意:此解决方案适用于 Oracle 11g及更高版本 . 如果您正在使用较低版本的Oracle,则需要修改对象定义,如下所示:

Create  type rslt is object
                  (col1 number,
                   col2 number, 
                   col3 number,                   
                   map member function mem return number);

这是由于 甲骨文10g 使用时 MULTISET 接线员。