每月业务报告的生成程序

Question

我正在尝试写一个程序，生成一个每月的商业报告。我需要显示商业租金的总数和在那个月有业务租金的CarRentalSites。只应该显示带有业务租金的月份，从最早到最新的月份的排序应该是从最早到最新的，CarRentalSites的排序应该是通过CarRentalSiteName属性进行的。

我写了下面这篇文章

create or replace procedure MonthlyBusinessRentalsReport as
CURSOR d_cursor is
Select Extract(year from Rentals.RentalDate) as oYear, Extract(month from Rentals.RentalDate) as oMonth, Count(*) as t
    from Rentals where Rentals.Status = 'BUSINESS'
Group By Extract(year from Rentals.RentalDate), Extract(month from Rentals.RentalDate) 
    Order By Extract(year from Rentals.RentalDate), Extract(month from Rentals.RentalDate);
    d_res d_cursor%ROWTYPE;

CURSOR d_retail is  
    Select Extract(year from Rentals.RentalDate) as oYear, Extract(month from Rentals.RentalDate) as oMonth, CarRentalSite.CarRentalSiteName, numOfDays
    from Rentals INNER JOIN CarRentalSite on Rentals.CarRentalSiteId=CarRentalSite.CarRentalSiteId where Rentals.Status='BUSINESS'
    Group By Extract(year from Rentals.RentalDate), Extract(month from Rentals.RentalDate), CarRentalSite.CarRentalSiteName, numOfDays
    Order By Extract(year from Rentals.RentalDate), Extract(month from Rentals.RentalDate), CarRentalSite.CarRentalSiteName, numOfDays;
    d_res2 d_retail%ROWTYPE;

BEGIN
    OPEN d_retail;
    Fetch d_retail into d_res2;
    for d_res in d_cursor loop
    dbms_output.put_line('Total Business Rentals in ' || d_res.oYear || '-' || d_res.oMonth || ': ' || d_res.t);
    dbms_output.put_line('In Car Rental Sites:');
    loop
    dbms_output.put_line('- ' || d_res2.CarRentalSiteName || ': ' || d_res2.numOfDays || ' days');
    Fetch d_retail into d_res2;
    exit when d_retail%NOTFOUND or d_res2.oYear != d_res.oYear or d_res2.oMonth != d_res.oMonth;
    end loop;
    end loop;
    Close d_retail;

    END MonthlyBusinessRentalsReport;
    /   

    show errors;

    BEGIN
    MonthlyBusinessRentalsReport;
    End;
    /   

它给了我一个输出，但不是我所期望的，我需要帮助修复它。我的输出和预期的结果是

---- Result of Procedure 2:
Total Business Rentals in 2018-1: 2
In Car Rental Sites:
- Hertz: 15 days

Expected:
Total Business Rentals in 2018-1: 2
In Car Rental Sites:
- Hertz: 36 days

---- Result of Procedure 2:
- Hertz: 21 days
Total Business Rentals in 2018-2: 2
In Car Rental Sites:
- Alamo: 10 days

Expected:
Total Business Rentals in 2018-2: 2
In Car Rental Sites:
- Alamo: 10 days
- Hertz: 14 days

---- Result of Procedure 2:
- Hertz: 14 days
Total Business Rentals in 2018-4: 1
In Car Rental Sites:

Expected:
Total Business Rentals in 2018-4: 1
In Car Rental Sites:
- Enterprise: 2 days

---- Result of Procedure 2:
- Enterprise: 2 days
Total Business Rentals in 2018-5: 3
In Car Rental Sites:
- Avis: 2 days
- Budget: 3 days

Expected:
Total Business Rentals in 2018-5: 3
In Car Rental Sites:
- Avis: 2 days
- Budget: 3 days
- Hertz: 25 days

---- Result of Procedure 2:
- Hertz: 25 days
Total Business Rentals in 2018-6: 1
In Car Rental Sites:

Expected:
Total Business Rentals in 2018-6: 1
In Car Rental Sites:
- Alamo: 10 days

这些是要使用的表和数据文件。

数据文件 表文件

Answer 1

在我看来，这个问题是，您对fetch循环的手动控制意味着您正在丢失数据。让Oracle控制它更简单、更安全：

create or replace procedure MonthlyBusinessRentalsReport as
BEGIN

    for d_res in 
       (Select Extract(year from Rentals.RentalDate) as oYear
                , Extract(month from Rentals.RentalDate) as oMonth
                , Count(*) as t
        from Rentals 
        where Rentals.Status = 'BUSINESS'
        Group By Extract(year from Rentals.RentalDate), Extract(month from Rentals.RentalDate) 
        Order By Extract(year from Rentals.RentalDate), Extract(month from Rentals.RentalDate)
    )
    loop
        dbms_output.put_line('Total Business Rentals in ' || d_res.oYear || '-' || d_res.oMonth || ': ' || d_res.t);
        dbms_output.put_line('In Car Rental Sites:');
        for d_res2 in (
              Select CarRentalSite.CarRentalSiteName
                     , sum(numOfDays) as numOfDays
              from Rentals
              INNER JOIN CarRentalSite on Rentals.CarRentalSiteId=CarRentalSite.CarRentalSiteId 
              where Rentals.Status='BUSINESS'
              and  Extract(year from Rentals.RentalDate) = d_res.oYear
              and Extract(month from Rentals.RentalDate) = d_res.oMonth
              group by CarRentalSite.CarRentalSiteName
              Order By CarRentalSite.CarRentalSiteName
        )
        loop
            dbms_output.put_line('- ' || d_res2.CarRentalSiteName || ': ' || d_res2.numOfDays || ' days');
        end loop;
    end loop;

END MonthlyBusinessRentalsReport;
/   

关于您的代码，我会做一些不同的事情，但是我尝试只改变那些导致产生错误结果的事情。