stored-procedures - Oracle stored procedure combine results

Question

I am working on the following Oracle (PL/SQL) stored procedure:

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;

Assuming above is my code now I need to return combined results from both IF clause select statement and Else clause. I tried with dbms_sql.return_result(); but it didn't help.

How can I combine both result sets and return the value?

标签： stored-proceduresplsqloracle11g

Answer 1

有很多方法可以满足您的要求，但我更愿意使用 atable来满足您的要求。见下文：

--创建一个表来保存 if 子句的结果

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);

这是由于使用运算符时Oracle 10gMULTISET中的一个错误。

在http://raajeshwaran.blogspot.com/2010/07/pls-00801-internal-error-assert-at-file.html阅读有关该错误的更多信息