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游标来实现这一点。您不再需要填充临时表，但是我们可以使用其中一个表来定义引用游标的签名。

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列是日期数据类型，则不应该使用TO_CHAR()转换。如果您希望处理具有time元素的行，则有更有效的处理方法。

参考游标在Oracle /SQL用户指南中有详细说明。了解更多信息。

编辑

我不是水晶报告的人。谷歌似乎只扔掉了一些相当古老的文档(比如这)。但人们的共识似乎是，当涉及到与Oracle存储过程的交互时，CR是非常有限的。

显然水晶报告需要声明为IN的参数。此外，它似乎只能处理一个这样的引用游标参数。此外，引用游标需要是过程签名中的第一个参数。最后，我完全相信，“存储过程不能调用另一个存储过程”。我们习惯于设计模式，即调用程序不应该知道被调用程序的内部结构，但是在这里，我们似乎有一个被调用程序的内部工作方式是由调用它的程序类型决定的。这太烂了。

因此，无论如何，上面的解决方案将不适用于水晶报告。唯一的解决方案是将其分解为两个过程，签名如下：

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')将函数应用于列(因此不能使用索引，优化器无法得到基数的好估计)，它将日期转换为以月份为主导位置的愚蠢字符格式，因此它甚至无法进行正确的比较！02-11-2009年11月-2009年排序大于01-3月-2010年在您的逻辑。

第四，你为什么要为此使用存储过程？如果需要的话，只需运行该死的查询并合并它们。

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