PL / SQL过程和水晶报告 [英] PL/SQL Procedure and a crystal report
问题描述
我想使用以下过程作为我的水晶报表的数据源。查询按照我的预期工作，但问题是我无法找出如何从这些虚拟表取回数据 - 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
选择合约，district_code，count（order_no），
SUM（CUSTOMER_ORDER_API.Get_Total_Sale_Price __（order_no））
来自CUSTOMER_ORDER
其中order_no IN（从customer_order_line中选择不同的order_no，其中state IN（'Released'））AND state IN（'Released'）and to_char（date_entered，'MM / DD / YYYY'）> = to_char（cdate，'MM / DD / YYYY '）
和合同IN
（'CERA'，'SAN'，'WOD'，'QEM'，'PIP'，'COT'，'KIT'，'MAR'，'PROJ'
group by contract，district_code，date_entered;
CURSOR c2 IS
选择合约，district_code，count（order_no），
SUM（CUSTOMER_ORDER_API.Get_Total_Sale_Price __（order_no））
来自CUSTOMER_ORDER
其中order_no IN（从customer_order_line中选择不同的order_no，其中state IN（'Reserved'））AND state IN（'Reserved'）and to_char（date_entered，'MM / DD / YYYY'）> = to_char（cdate，'MM / DD / YYYY '）
和合同IN
（'CERA'，'SAN'，'WOD'，'QEM'，'PIP'，'COT'，'KIT'，'MAR'，'PROJ'
group by contract，district_code，date_entered;
开始
- 发布订单
OPEN c1;
DELETE FROM IFS_PR_DUMMY_TAB;
loop
fetch c1 into acontract，ashowroom，aorderno，amount;
exit when c1％notfound;
插入IFS_PR_DUMMY_TAB
（DCONTRACT，DSHOWROOM，DORDERNO，DAMOUNT）值（acontract，ashowroom，aorderno，amount）;
end loop;
close c1;
- 对于预留订单
OPEN c2;
DELETE FROM IFS_PR_DUMMY2_TAB;
loop
fetch c2 into bcontract，bshowroom，borderno，bamount;
exit when c2％notfound;
插入IFS_PR_DUMMY2_TAB
（ECONTRACT，ESHOWROOM，EORDERNO，EAMOUNT）值（bcontract，bshowroom，borderno，bamount）;
end loop;
close c2;
end;
解决问题的最佳方法是过程返回结果集。在Oracle中，我们使用REF CURSORS来实现这一点。您不需要再填充临时表，但我们可以使用其中一个来定义REF CURSOR的签名。
创建或替换包report_records为
类型order_recs是ref游标
return IFS_PR_DUMMY_TAB％rowtype;
end;
/
此过程返回两个引用游标。
创建或替换过程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））
来自customer_order
其中order_no
in（select distinct order_no
from customer_order_line
其中'release'））
AND state（'Released'）
and to_char（date_entered，'MM / DD / YYYY'）> = to_char（cdate，'MM / DD / YYYY'）
和合同（'CERA'，'SAN'，'WOD'，'QEM'，'PIP'，'COT'，'KIT'，'MAR'，'PROJ'）
，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（'Reserved'）中的状态
和to_char（date_entered， MM / DD / YYYY'）> = to_char（cdate，'MM / DD / YYYY'）
并且签约（'CERA'，'SAN'，'WOD'，'QEM'，'PIP' 'COT'，'KIT'，'MAR'，'PROJ'）
按合同分组，district_code，date_entered;
end;感兴趣的是，如果你的 date_entered / code> column是一个DATE数据类型，那么你不应该使用TO_CHAR（）转换。如果您要处理具有时间元素的行，可以使用更有效的方式处理。
Ref游标在Oracle PL / SQL用户指南中有详细解释。 了解详情。
编辑
人。 Google似乎只会抛出一些很旧的文档（例如 this ）。但是一致似乎是，当涉及到与Oracle存储过程交互时，CR非常受限制。
显然，Crystal Reports需要声明为IN OUT的参数。它似乎只能处理一个这样的ref光标参数。此外，ref游标需要是过程签名中的 first 参数。最后，对我来说完全不可思议，存储过程不能调用另一个存储过程。我们习惯于设计模式，声明调用程序不必知道被调用程序的内部结构，但是在这里我们似乎有一个被调用程序的内部工作由调用它的程序类型决定。这很漂亮。
因此，无论如何，上述解决方案不适用于Crystal Reports。唯一的解决方案是将它分成两个过程，具有如下的签名：
创建或替换过程dummy9_ifs_fr2_sales_released
（c_released_orders in out report_records.order_recs
，cdate in date
）
as ...
创建或替换过程dummy9_ifs_fr2_sales_reserved
（c_reserved_orders in out report_records .order_recs
，cdate in date
）
as ...
这些过程可以捆绑到一个包中（假设CR可以处理该构造）。
如果两个过程解决方案不可接受，与David的方法：完全放弃存储过程，并在报告中使用原始SQL。
I am trying to use the following procedure as a datasource for my crystal report. The query works as I expected but the problem is I can't figure out how to fetch back the data from those dummy tables - IFS_PR_DUMMY_TAB and 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;
解决方案 The best way to solve your problem is to have your procedure return result sets. In Oracle we use REF CURSORS to achieve this. You don't need to populate the temporary tables any more, but we can use one of them to define the signature of the REF CURSOR.
create or replace package report_records as
type order_recs is ref cursor
return IFS_PR_DUMMY_TAB%rowtype;
end;
/
This procedure returns two ref cursors.
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;
/
As a matter of interest, if your date_entered column is a DATE datatype then you shouldn't use the TO_CHAR() conversion. If you are looking to handle rows which have a time element there are more efficient ways of handling that.
Ref Cursors are explained in detail in the Oracle PL/SQL User's Guide. Find out more.
edit
I'm not a Crystal Reports person. Google only seems to throw out some pretty old documentation (like this). But the consensus seems to be that CR is pretty restricted when it comes to interacting with Oracle stored procedures.
Apparently Crystal Reports needs the parameters declared as IN OUT. Also it appears it can only handle one such ref cursor parameter. Furthermore the ref cursor needs to be the first argument in the procedure's signature. Finally, and to my mind completely incredibly, the "stored procedure cannot call another stored procedure." We are used to design patterns which state that calling programs shouldn't have to know anything about the internals of the called program, but here we seem to have the internal workings of a called program being determined by the sort of program which calls it. That's pretty lame.
So, anyway, the above solution won't work for Crystal Reports. The only solution is to break it up into two procedures, with signatures like this:
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 ...
These procedures could be bundled into a package (assuming CR can cope with that construct).
If the two procedure solution is not acceptable then I think you're left with David's approach: abandon stored procedures altogether, and just use raw SQL in the report.
这篇关于PL / SQL过程和水晶报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
