在 pl/sql 集合中选择多个列
实际上我已经创建了以下程序，运行良好。
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);
/
第三个
创建或替换 PACKAGE“TEST” 作为 TYPE RefCsr 是参考光标； 结束测试； /现在我想像这样更改我的查询
从 opr_test 中选择列 1、列 2 批量收集到 DemoTable，其中 call_date 位于firstDate AND secondaryDate AND id=test 之间 减 从 opr_test 中选择列 1、列 2，其中 call_date 介于 ThirdDate 和 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;
/
但这是不正确的，请告诉我程序将是什么样子
Actually i have created following procedure,which is working fine.
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. second
create or replace TYPE "CRITERIA_LIST_TABLE" as table of varchar2(20);
/
Third
create or replace PACKAGE "TEST" AS TYPE RefCsr IS REF CURSOR; END TEST; /Now i want to change my query like this
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;
so i changed the procedure like
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;
/
But this is incorrect,please tell me how the procedure will look like
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
user595014，帮助您解决遇到的问题，并在 StackOverflow 上提出了几个问题。阅读 ORACLE-BASE 中有关创建和返回 oracle REF_CURSOR 类型的文章。
如果您阅读了全部内容，它还会为您提供有关如何将 ref_cursor 返回到调用 Java 程序的演示（您在上一个问题中问过我这个问题）。
它将为您提供解决问题所需的一切。
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
user595014, to help you with the problem you are having and have several questions open on StackOverflow about. Read this article from ORACLE-BASE about creating and returning oracle REF_CURSOR types.
If you read it all it also gives you a demo on how to return a ref_cursor to a calling Java program (something you have asked me about in a previous question).
It will give you everything you need to sort out your issue.
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php