oracle PL/SQL 过程和水晶报告
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1803318/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL Procedure and a crystal report
提问by signil
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
我正在尝试使用以下过程作为我的水晶报告的数据源。查询按我的预期工作，但问题是我不知道如何从这些虚拟表中取回数据 - 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;
回答by APC
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.
解决您的问题的最佳方法是让您的过程返回结果集。在 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;
/
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_enteredcolumn 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.
有趣的是，如果您的date_entered列是 DATE 数据类型，则不应使用 TO_CHAR() 转换。如果您要处理具有时间元素的行，则有更有效的处理方法。
Ref Cursors are explained in detail in the Oracle PL/SQL User's Guide. Find out more.
参考游标在 Oracle PL/SQL 用户指南中有详细解释。 了解更多。
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.
我不是水晶报表的人。谷歌似乎只扔掉了一些相当旧的文档（像这样）。但共识似乎是 CR 在与 Oracle 存储过程交互时受到很大限制。
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 firstargument 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.
显然 Crystal Reports 需要声明为 IN OUT 的参数。此外，它似乎只能处理一个这样的引用游标参数。此外，引用游标需要是过程签名中的第一个参数。最后，在我看来完全难以置信的是，“存储过程不能调用另一个存储过程”。我们习惯于设计模式，这些模式声明调用程序不应该知道被调用程序的内部结构，但在这里我们似乎让被调用程序的内部工作由调用它的程序类型决定。真是太蹩脚了。
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:
因此，无论如何，上述解决方案不适用于 Crystal Reports。唯一的解决方案是将其分解为两个过程，签名如下：
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).
这些过程可以捆绑到一个包中（假设 CR 可以处理该构造）。
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.
如果这两个过程的解决方案是不可接受的，那么我认为您只能使用 David 的方法：完全放弃存储过程，并在报告中仅使用原始 SQL。
回答by David Aldridge
Your code sucks.
你的代码很烂。
Firstly, why are you using explicit cursors? Why wouldn't you just insert the rows into the tables?
首先，为什么要使用显式游标？为什么不直接将行插入表中？
Secondly, why delete when you could truncate much faster?
其次，当您可以更快地截断时，为什么要删除？
Thirdly, to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')applies a function to a column (so an index can't be used and the optimiser cannot get a good estimate of cardinality), andit converts the dates to a stupid character format with the month in the leading position so that it does not even do a correct comparison! 02-november-2009 sorts greater than 01-march-2010 in your logic.
第三，to_char(date_entered,'MM/DD/YYYY')>=to_char(cdate,'MM/DD/YYYY')应用一个函数列（这样的索引不能使用，优化器不能得到基数的一个很好的估计），并且它处于领先地位的日期与月份转换为一个愚蠢的字符格式，以便它甚至没有做正确的比较！在您的逻辑中，2009 年 11 月 2 日的排序大于 2010 年 3 月 1 日。
Fourthly, why on earth are you using a stored procedure for this? Just run the damn queries and Union All them together if you need to.
第四，你到底为什么要为此使用存储过程？如果需要，只需运行该死的查询和联合所有它们。
This reminds me of all of the crap I saw from offshore report developers for two years at my previous job. Complete incompetence.
这让我想起了我在上一份工作中两年来从离岸报告开发人员那里看到的所有废话。完全无能。
