PL/SQL过程和水晶报告

Question

我正在尝试使用以下过程作为我的水晶报告的数据源。查询按我的预期工作，但问题是我无法弄清楚如何从这些虚拟表中取回数据 - IFS_PR_DUMMY_TAB 和 IFS_PR_DUMMY2_TAB

CREATE OR REPLACE procedure dummy9_IFS_FR2_Sales (cdate IN date)

as

acontract customer_order.contract%type;
ashowroom customer_order.district_code%type;
aorderno customer_order.order_no%type;
amount number(10);

bcontract customer_order.contract%type;
bshowroom customer_order.district_code%type;
borderno customer_order.order_no%type;
bamount number(10);

CURSOR c2 IS
select  contract, district_code ,count(order_no),
SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no))
from CUSTOMER_ORDER
where order_no IN (select distinct order_no from customer_order_line where state IN ('Released') )  AND state IN ('Released') and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
and contract IN
('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
group by contract,district_code, date_entered ; 

CURSOR c2 IS
select  contract, district_code ,count(order_no),
SUM(CUSTOMER_ORDER_API.Get_Total_Sale_Price__(order_no))
from CUSTOMER_ORDER
where order_no IN (select distinct order_no from customer_order_line where state IN ('Reserved') )  AND state IN ('Reserved') and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
and contract IN
('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
group by contract,district_code, date_entered ; 

begin

--For Released Orders

  OPEN c1;

  DELETE FROM IFS_PR_DUMMY_TAB;

  loop
      fetch c1 into acontract, ashowroom, aorderno, amount;
      exit when c1%notfound;

Insert into IFS_PR_DUMMY_TAB
(DCONTRACT ,DSHOWROOM ,DORDERNO,DAMOUNT) values (acontract,ashowroom,aorderno,amount);

end loop;
close c1;

--For Reserved Orders

 OPEN c2;

 DELETE FROM IFS_PR_DUMMY2_TAB;

loop

      fetch c2 into bcontract, bshowroom, borderno, bamount;
      exit when c2%notfound;

Insert into IFS_PR_DUMMY2_TAB
(ECONTRACT ,ESHOWROOM ,EORDERNO,EAMOUNT) values (bcontract,bshowroom,borderno,bamount);

end loop;
close c2; 

end;

Answer 1

解决问题的最佳方法是让您的过程返回结果集。在 Oracle 中，我们使用 REF CURSORS 来实现这一点。您不再需要填充临时表，但我们可以使用其中之一来定义 REF CURSOR 的签名。

create or replace package report_records as
    type order_recs is ref cursor 
        return IFS_PR_DUMMY_TAB%rowtype;
end;
/

此过程返回两个引用游标。

create or replace procedure dummy9_ifs_fr2_sales 
    (cdate in date
     , c_released_orders in out report_records.order_recs
     , c_reserved_orders in out report_records.order_recs
     )

begin
    open c_released_orders for
        select  contract
               , district_code 
               ,count(order_no)
               ,sum(customer_order_api.get_total_sale_price__(order_no))
        from customer_order
        where order_no 
        in (select distinct order_no 
               from customer_order_line 
               where state in ('Released') )  
        AND state in ('Released') 
        and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
        and contract in ('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
        group by contract,district_code, date_entered ; 
    open c_released_orders for
        select  contract
                 , district_code 
                 ,count(order_no)
                 ,sum(customer_order_api.get_total_sale_price__(order_no))
        from customer_order
        where order_no in (select distinct order_no 
                              from customer_order_line 
                              where state in ('Reserved') )
        AND state in ('Reserved') 
        and to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')
        and contract in ('CERA','SAN','WOD','QEM','PIP','COT','KIT','MAR','PROJ')
        group by contract,district_code, date_entered ; 
end;
/

有趣的是，如果您的 date_entered 列是 DATE 数据类型，那么您不应使用 TO_CHAR() 转换。如果您希望处理具有时间元素的行，则有更有效的处理方法。

《Oracle PL/SQL 用户指南》中详细解释了参考游标。 了解更多信息.

编辑

我不是 Crystal Reports 人员。谷歌似乎只扔掉了一些相当旧的文档（例如 这个）。但共识似乎是 CR 在与 Oracle 存储过程交互时受到很大限制。

显然 Crystal Reports 需要声明为 IN OUT 的参数。而且它似乎只能处理一个这样的参考光标参数。此外，引用游标必须是过程签名中的第一个参数。最后，在我看来完全令人难以置信的是，“存储过程不能调用另一个存储过程”。我们习惯于设计模式，这些模式规定调用程序不必了解有关被调用程序内部的任何信息，但在这里，我们似乎让被调用程序的内部工作方式由调用它的程序类型决定。这实在是太蹩脚了。

所以，无论如何，上述解决方案不适用于水晶报表。唯一的解决方案是将其分成两个过程，并带有如下签名：

create or replace procedure dummy9_ifs_fr2_sales_released 
    (c_released_orders in out report_records.order_recs
     , cdate in date
     )
 as ... 

create or replace procedure dummy9_ifs_fr2_sales_reserved 
    (c_reserved_orders in out report_records.order_recs
     , cdate in date
     )
as ...

这些过程可以捆绑到一个包中（假设 CR 可以处理该构造）。

如果两个过程的解决方案不可接受，那么我认为您只能采用 David 的方法：完全放弃存储过程，只在报告中使用原始 SQL。

Answer 2

你的代码很糟糕。

首先，为什么要使用显式游标？为什么不直接将行插入表中？

其次，当你可以更快地截断时为什么要删除？

第三，to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')将函数应用于列（因此索引不能使用并且优化器无法获得基数的良好估计），并且它将日期转换为愚蠢的字符格式，月份位于前导位置，因此它甚至无法进行正确的比较！在您的逻辑中，2009 年 11 月 2 日排序大于 2010 年 3 月 1 日。

第四，你到底为什么要使用存储过程呢？如果需要的话，只需运行该死的查询并将它们联合起来即可。

这让我想起了我在上一份工作的两年里从离岸报告开发人员那里看到的所有废话。完全无能。