在pl/sql集合中选择多个列

Question

实际上我已经创建了以下程序，这是正常工作的

CREATE or REPLACE PROCEDURE GET_NOS(
firstDate IN DATE,
secondDate IN DATE,
thirdDate IN DATE,
fourthDate IN DATE,
test IN VARCHAR2,
Slnt_Entity OUT TEST.RefCsr
)
AS
 DemoTable CRITERIA_LIST_TABLE;
 BEGIN

SELECT column1 BULK COLLECT INTO DemoTable FROM opr_test where call_date between firstDate AND secondDate AND id=test
MINUS
SELECT column1 FROM opr_test where call_date between thirdDate AND fourthDate AND id=test;

OPEN Slnt_Entity FOR SELECT * FROM TABLE(
                                            CAST (
                                                    DemoTable AS CRITERIA_LIST_TABLE
                                                 )
                                    ) Nos;
END;
/

2。第二个

create or replace TYPE "CRITERIA_LIST_TABLE" as table of varchar2(20);
/

1. 第三

   create or replace PACKAGE "TEST"
   AS
   TYPE RefCsr IS REF CURSOR;
   END TEST;
    /
   

   现在我想改变我的查询，像这样

   SELECT column1,column2 BULK COLLECT INTO DemoTable FROM opr_test where call_date between firstDate AND secondDate AND id=test
   MINUS
   SELECT column1,column2 FROM opr_test where call_date between thirdDate AND fourthDate AND id=test;
   

所以我把程序改成了

CREATE or REPLACE PROCEDURE GET_NOS(
firstDate IN DATE,
secondDate IN DATE,
thirdDate IN DATE,
fourthDate IN DATE,
test IN VARCHAR2,
Slnt_Entity OUT TEST.RefCsr
 )
AS
CURSOR c1 IS SELECT column1,column2 FROM opr_test;
  create or replace TYPE "ABC" IS TABLE OF c1%ROWTYPE;
DemoTable ABC;
BEGIN

 SELECT column1 BULK COLLECT INTO DemoTable FROM opr_test where call_date between firstDate AND secondDate AND id=test
 MINUS
 SELECT column1 FROM opr_test where call_date between thirdDate AND fourthDate AND id=test;

  OPEN Slnt_Entity FOR SELECT * FROM TABLE(
                                            CAST (
                                                    DemoTable AS CRITERIA_LIST_TABLE
                                                 )
                                    ) Nos;
  END;
  /

但是这是不正确的，请告诉我这个过程是怎样的

Answer 1

user595014，以帮助您解决您所遇到的问题，并在StackOverflow上有几个问题。阅读这篇关于创建和返回oracle REF_CURSOR类型的文章。

如果你阅读了所有内容，它还提供了一个关于如何将ref_cursor返回给调用Java程序的演示(你在前面的问题中问过我)。

它会给你整理你的问题所需要的一切。

http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php