--首先自定义一种名为：row_month_report类型type
create or replace type row_month_report as object
(
m_month varchar2(30),
m_SluiceName varchar2(30),
m_OneSluiceCount number,
m_TwoSluiceCount number,
m_OneUpEmptyCount number,
m_OneDownemptyCount number,
m_TwoUpemptyCount number,
m_TwoDownemptyCount number,
------------------------
y_OneSluiceCount number,
y_TwoSluiceCount number,
y_OneUpEmptyCount number,
y_OneDownemptyCount number,
y_TwoUpemptyCount number,
y_TwoDownemptyCount number,
---------------------------
m_TotalPassShipCount number,
m_UpRunCount number,
m_DownRunCount number,
m_OneSluiceUpCount number,
m_OneSluiceDownCount number,
m_TwoSluiceUpCount number,
m_TwoSluiceDownCount number,
m_DangerousShipCount number,
m_CustomerShipCount number,
m_SmallShipCount number,
m_NotProfitShipCount number,
----------------------------
y_TotalPassShipCount number,
y_UpRunCount number,
y_DownRunCount number,
y_OneSluiceUpCount number,
y_OneSluiceDownCount number,
y_TwoSluiceUpCount number,
y_TwoSluiceDownCount number,
y_DangerousShipCount number,
y_CustomerShipCount number,
y_SmallShipCount number,
y_NotProfitShipCount number,
----------月过闸船舶的货量------------------
m_CargoShipTotalCheckTonnage number,
m_CargoShipTotalTrueTonnnage number,
m_CustomerTotalCheckTonnage number,
m_CargoShipUpCheckTonnage number,
m_OneSluiceUpCheckTonnage number,
m_TwoSluiceUpCheckTonnage number,
m_CargoShipDownCheckTonnage number,
m_OneSluiceDownCheckTonnage number,
m_TwoSluiceDownCheckTonnage number,
m_CargoShipUpTrueTonnage number,
m_OneSluiceUpTrueTonnage number,
m_TwoSluiceUpTrueTonnage number,
m_CargoShipDownTrueTonnage number,
m_OneSluiceDownTrueTonnage number,
m_TwoSluiceDownTrueTonnage number,
m_UpTopFiveGoodNameAndCount varchar2(500),
m_DownTopFiveGoodNameAndCount varchar2(500),
-------------------------------------
y_CargoShipTotalCheckTonnage number,
y_CargoShipTotalTrueTonnnage number,
y_CustomerTotalCheckTonnage number,
y_CargoShipUpCheckTonnage number,
y_OneSluiceUpCheckTonnage number,
y_TwoSluiceUpCheckTonnage number,
y_CargoShipDownCheckTonnage number,
y_OneSluiceDownCheckTonnage number,
y_TwoSluiceDownCheckTonnage number,
y_CargoShipUpTrueTonnage number,
y_OneSluiceUpTrueTonnage number,
y_TwoSluiceUpTrueTonnage number,
y_CargoShipDownTrueTonnage number,
y_OneSluiceDownTrueTonnage number,
y_TwoSluiceDownTrueTonnage number,
y_UpTopFiveGoodNameAndCount varchar2(500),
y_DownTopFiveGoodNameAndCount varchar2(500)
)
--把类型当作表格使用
create or replace type table_month_report as table of row_month_report;
--方法
create or replace function fun_MontnReport(SLUICEID IN VARCHAR2,
D in VARCHAR2) return table_month_report
pipelined as
vv row_month_report;
m_SluiceName varchar2(30);
m_OneSluiceCount number := 0;
m_TwoSluiceCount number := 0;
m_OneUpEmptyCount number := 0;
m_OneDownemptyCount number := 0;
m_TwoUpemptyCount number := 0;
m_TwoDownemptyCount number := 0;
------------------------
y_OneSluiceCount number := 0;
y_TwoSluiceCount number := 0;
y_OneUpEmptyCount number := 0;
y_OneDownemptyCount number := 0;
y_TwoUpemptyCount number := 0;
y_TwoDownemptyCount number := 0;
---------------------------
m_TotalPassShipCount number := 0;
m_UpRunCount number := 0;
m_DownRunCount number := 0;
m_OneSluiceUpCount number := 0;
m_OneSluiceDownCount number := 0;
m_TwoSluiceUpCount number := 0;
m_TwoSluiceDownCount number := 0;
m_DangerousShipCount number := 0;
m_CustomerShipCount number := 0;
m_SmallShipCount number := 0;
m_NotProfitShipCount number := 0;
----------------------------
y_TotalPassShipCount number := 0;
y_UpRunCount number := 0;
y_DownRunCount number := 0;
y_OneSluiceUpCount number := 0;
y_OneSluiceDownCount number := 0;
y_TwoSluiceUpCount number := 0;
y_TwoSluiceDownCount number := 0;
y_DangerousShipCount number := 0;
y_CustomerShipCount number := 0;
y_SmallShipCount number := 0;
y_NotProfitShipCount number := 0;
----------月过闸船舶的货量------------------
m_CargoShipTotalCheckTonnage number := 0;
m_CargoShipTotalTrueTonnnage number := 0;
m_CustomerTotalCheckTonnage number := 0;
m_CargoShipUpCheckTonnage number := 0;
m_OneSluiceUpCheckTonnage number := 0;
m_TwoSluiceUpCheckTonnage number := 0;
m_CargoShipDownCheckTonnage number := 0;
m_OneSluiceDownCheckTonnage number := 0;
m_TwoSluiceDownCheckTonnage number := 0;
m_CargoShipUpTrueTonnage number := 0;
m_OneSluiceUpTrueTonnage number := 0;
m_TwoSluiceUpTrueTonnage number := 0;
m_CargoShipDownTrueTonnage number := 0;
m_OneSluiceDownTrueTonnage number := 0;
m_TwoSluiceDownTrueTonnage number := 0;
m_UpTopFiveGoodNameAndCount varchar2(500);
m_DownTopFiveGoodNameAndCount varchar2(500);
-------------------------------------
y_CargoShipTotalCheckTonnage number := 0;
y_CargoShipTotalTrueTonnnage number := 0;
y_CustomerTotalCheckTonnage number := 0;
y_CargoShipUpCheckTonnage number := 0;
y_OneSluiceUpCheckTonnage number := 0;
y_TwoSluiceUpCheckTonnage number := 0;
y_CargoShipDownCheckTonnage number := 0;
y_OneSluiceDownCheckTonnage number := 0;
y_TwoSluiceDownCheckTonnage number := 0;
y_CargoShipUpTrueTonnage number := 0;
y_OneSluiceUpTrueTonnage number := 0;
y_TwoSluiceUpTrueTonnage number := 0;
y_CargoShipDownTrueTonnage number := 0;
y_OneSluiceDownTrueTonnage number := 0;
y_TwoSluiceDownTrueTonnage number := 0;
y_UpTopFiveGoodNameAndCount varchar2(500);
y_DownTopFiveGoodNameAndCount varchar2(500);
SLUICE VARCHAR2(30);
daychar VARCHAR2(50); --日期
yearchar VARCHAR2(50); --年份
dd date;
BEGIN
if D is not null then
dd := to_date(D, 'YYYY-MM');
daychar := to_char(dd, 'YYYY-MM');
yearchar := to_char(dd, 'YYYY');
SLUICE := SLUICEID;
select s.sluicename
into m_SluiceName
from sluiceinfo s
where s.sluiceoid = SLUICE;
----------------月开闸情况----------------
select nvl(sum(t.SLUICECOUNT), 0)
into m_OneSluiceCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sonsluiceoid = '1#'
and t.sluiceoid = SLUICE; --1#闸运行闸次
select nvl(sum(t.SLUICECOUNT), 0)
into m_TwoSluiceCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sonsluiceoid = '2#'
and t.sluiceoid = SLUICE; --2#闸运行闸次
select nvl(sum(t.SLUICECOUNT), 0)
into m_OneUpEmptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sonsluiceoid = '1#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '0'; --1#闸上航空闸
select nvl(sum(t.SLUICECOUNT), 0)
into m_OneDownemptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sonsluiceoid = '1#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '1'; --1#闸下航空闸
select nvl(sum(t.SLUICECOUNT), 0)
into m_TwoUpemptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sonsluiceoid = '2#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '0'; --2#闸上航空闸
select nvl(sum(t.SLUICECOUNT), 0)
into m_TwoDownemptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sonsluiceoid = '2#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '1'; --2#闸下航空闸
---------------年开闸情况----------------
select nvl(sum(t.SLUICECOUNT), 0)
into y_OneSluiceCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sonsluiceoid = '1#'
and t.sluiceoid = SLUICE; --1#闸运行闸次
select nvl(sum(t.SLUICECOUNT), 0)
into y_TwoSluiceCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sonsluiceoid = '2#'
and t.sluiceoid = SLUICE; --@#闸运行闸次
select nvl(sum(t.SLUICECOUNT), 0)
into y_OneUpEmptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sonsluiceoid = '1#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '0'; --1#闸上航空闸
select nvl(sum(t.SLUICECOUNT), 0)
into y_OneDownemptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sonsluiceoid = '1#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '1'; --1#闸下航空闸
select nvl(sum(t.SLUICECOUNT), 0)
into y_TwoUpemptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sonsluiceoid = '2#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '0'; --2#闸上航空闸
select nvl(sum(t.SLUICECOUNT), 0)
into y_TwoDownemptyCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sonsluiceoid = '2#'
and t.sluiceoid = SLUICE
and t.isempty = '0'
and t.sluicedirection = '1'; --2#闸下航空闸
----------------月过闸船舶数量-----------------
--总过闸船舶(不含小机艇) m_TotalPassShipCount
select nvl(sum(t.OVERSHIPCOUNT), 0)
into m_UpRunCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sluicedirection = '0'; --上航船舶
select nvl(sum(t.OVERSHIPCOUNT), 0)
into m_DownRunCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sluicedirection = '1'; --下航船舶
select nvl(sum(t.OVERSHIPCOUNT), 0)
into m_OneSluiceUpCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '0'; --1#闸上航
select nvl(sum(t.OVERSHIPCOUNT), 0)
into m_OneSluiceDownCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '1'; --1#闸下航
select nvl(sum(t.OVERSHIPCOUNT), 0)
into m_TwoSluiceUpCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '0'; --2#闸上航
select nvl(sum(t.OVERSHIPCOUNT), 0)
into m_TwoSluiceDownCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '1'; --2#闸下航
--装危险品船舶
select nvl(sum(t.REPORTOID), 0)
into m_DangerousShipCount
from v_shipreport t
where t.overtime = daychar
and t.sluiceoid = SLUICE
and t.dangerousgood = 1
and t.LASTSTATUS = '过闸';
--客圩渡船
select nvl(sum(t.REPORTOID), 0)
into m_CustomerShipCount
from v_shipreport t
where t.overtime = daychar
and t.sluiceoid = SLUICE
and t.shiptypename like '%客%'
and t.LASTSTATUS = '过闸';
--小机艇船
select nvl(sum(t.REPORTOID), 0)
into m_SmallShipCount
from v_shipreport t
where t.overtime = daychar
and t.sluiceoid = SLUICE
and t.SHIPTYPENAME like '%小机艇船%'
and t.LASTSTATUS = '过闸';
--非营运船
m_NotProfitShipCount := 0;
m_TotalPassShipCount := m_UpRunCount + m_DownRunCount +
m_OneSluiceUpCount + m_OneSluiceDownCount +
m_TwoSluiceUpCount + m_TwoSluiceDownCount +
m_DangerousShipCount + m_CustomerShipCount +
m_NotProfitShipCount;
----------------年过闸船舶数量-----------------
--总过闸船舶(不含小机艇)
select nvl(sum(t.OVERSHIPCOUNT), 0)
into y_UpRunCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sluicedirection = '0'; --上航船舶
select nvl(sum(t.OVERSHIPCOUNT), 0)
into y_DownRunCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sluicedirection = '1'; --下航船舶
select nvl(sum(t.OVERSHIPCOUNT), 0)
into y_OneSluiceUpCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '0'; --1#闸上航
select nvl(sum(t.OVERSHIPCOUNT), 0)
into y_OneSluiceDownCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '1'; --1#闸下航
select nvl(sum(t.OVERSHIPCOUNT), 0)
into y_TwoSluiceUpCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '0'; --2#闸上航
select nvl(sum(t.OVERSHIPCOUNT), 0)
into y_TwoSluiceDownCount
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '1'; --2#闸下航
--装危险品船舶
select nvl(sum(t.REPORTOID), 0)
into y_DangerousShipCount
from v_shipreport t
where t.overtime = yearchar
and t.sluiceoid = SLUICE
and t.dangerousgood = 1
and t.LASTSTATUS = '过闸';
--客圩渡船
select nvl(sum(t.REPORTOID), 0)
into y_CustomerShipCount
from v_shipreport t
where t.overtime = yearchar
and t.sluiceoid = SLUICE
and t.SHIPTYPENAME like '%客%'
and t.LASTSTATUS = '过闸';
--小机艇船
select nvl(sum(t.REPORTOID), 0)
into y_SmallShipCount
from v_shipreport t
where t.overtime = yearchar
and t.sluiceoid = SLUICE
and t.SHIPTYPENAME like '%小机艇船%'
and t.LASTSTATUS = '过闸';
--非营运船
y_NotProfitShipCount := 0;
y_TotalPassShipCount := y_UpRunCount + y_DownRunCount +
y_OneSluiceUpCount + y_OneSluiceDownCount +
y_TwoSluiceUpCount + y_TwoSluiceDownCount +
y_DangerousShipCount + y_CustomerShipCount +
y_NotProfitShipCount;
------------------月过闸船舶的货量-----------------------------
--货船总核载- -货船上航核载=a25+a26 + --货船下航核载=a27+a28
--货船总实载---货船上航实载=a29+a30 + --货船下航实载
--客船总核载
--货船上航核载=a25+a26
select nvl(sum(t.LOADTONNAGE), 0)
into m_OneSluiceUpCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '0'; --1#闸上航核载
select nvl(sum(t.LOADTONNAGE), 0)
into m_TwoSluiceUpCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '0'; --2#闸上航核载
m_CargoShipUpCheckTonnage := m_OneSluiceUpCheckTonnage +
m_TwoSluiceUpCheckTonnage;
--货船下航核载=a27+a28
select nvl(sum(t.LOADTONNAGE), 0)
into m_OneSluiceDownCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '1'; --1#闸下航核载
select nvl(sum(t.LOADTONNAGE), 0)
into m_TwoSluiceDownCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '1'; --2#闸下航核载
m_CargoShipDownCheckTonnage := m_OneSluiceDownCheckTonnage +
m_TwoSluiceDownCheckTonnage;
--货船上航实载=a29+a30
select nvl(sum(t.actualtonnage), 0)
into m_OneSluiceUpTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '0'; --1#闸上航实载
select nvl(sum(t.actualtonnage), 0)
into m_TwoSluiceUpTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '0'; --2#闸上航实载
m_CargoShipUpTrueTonnage := m_OneSluiceUpTrueTonnage +
m_TwoSluiceUpTrueTonnage;
--货船下航实载
select nvl(sum(t.actualtonnage), 0)
into m_OneSluiceDownTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '1'; --1#闸下航实载
select nvl(sum(t.actualtonnage), 0)
into m_TwoSluiceDownTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY-MM') = daychar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '1'; --2#闸下航实载
m_CargoShipDownTrueTonnage := m_OneSluiceDownTrueTonnage +
m_TwoSluiceDownTrueTonnage;
--实载下航前五名货名及货量
select wm_concat(p.N)
into m_DownTopFiveGoodNameAndCount
from (select a.*, rownum rn
from (select v.GOOODTYPENAME || ':' || sum
(v.TRUETONNAGE) N
from v_shipreport v
where substr(v.REPORTTIME,0,7) = daychar
and
v.SLUICEOID = SLUICE
and
v.LASTSTATUS = '过闸'
and
v.SHIPDIRECTION = '下行'
and
v.TRUETONNAGE is not null
group by v.GOOODTYPENAME, v.TRUETONNAGE
order by sum
(v.TRUETONNAGE) desc) a
where rownum <= 5) p;
--实载上航前五名货名及货量
select wm_concat(p.N)
into m_UpTopFiveGoodNameAndCount
from (select a.*
from (select v.GOOODTYPENAME || ':' || sum
(v.TRUETONNAGE) N
from v_shipreport v
where
substr(v.REPORTTIME,0,7)= daychar
and
v.SLUICEOID = SLUICE
and
v.LASTSTATUS = '过闸'
and
v.SHIPDIRECTION = '上行'
and
v.TRUETONNAGE is not null
group by v.GOOODTYPENAME, v.TRUETONNAGE
order by sum
(v.TRUETONNAGE) desc) a
where rownum <= 5) p;
--货船总核载
m_CargoShipTotalCheckTonnage := m_CargoShipUpCheckTonnage +
m_CargoShipDownCheckTonnage;
--货船总实载
m_CargoShipTotalTrueTonnnage := m_CargoShipUpTrueTonnage +
m_CargoShipDownTrueTonnage;
--客船总核载
m_CustomerTotalCheckTonnage := 0;
------------------年过闸船舶的货量-----------------------------
--货船总核载- -货船上航核载=a25+a26 + --货船下航核载=a27+a28
--货船总实载---货船上航实载=a29+a30 + --货船下航实载
--客船总核载
--货船上航核载=a25+a26
select nvl(sum(t.LOADTONNAGE), 0)
into y_OneSluiceUpCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '0'; --1#闸上航核载
select nvl(sum(t.LOADTONNAGE), 0)
into y_TwoSluiceUpCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '0'; --2#闸上航核载
y_CargoShipUpCheckTonnage := y_OneSluiceUpCheckTonnage +
y_TwoSluiceUpCheckTonnage;
--货船下航核载=a27+a28
select nvl(sum(t.LOADTONNAGE), 0)
into y_OneSluiceDownCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '1'; --1#闸下航核载
select nvl(sum(t.LOADTONNAGE), 0)
into y_TwoSluiceDownCheckTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '1'; --2#闸下航核载
y_CargoShipDownCheckTonnage := y_OneSluiceDownCheckTonnage +
y_TwoSluiceDownCheckTonnage;
--货船上航实载=a29+a30
select nvl(sum(t.actualtonnage), 0)
into y_OneSluiceUpTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '0'; --1#闸上航实载
select nvl(sum(t.actualtonnage), 0)
into y_TwoSluiceUpTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '0'; --2#闸上航实载
y_CargoShipUpTrueTonnage := y_OneSluiceUpTrueTonnage +
y_TwoSluiceUpTrueTonnage;
--货船下航实载
select nvl(sum(t.actualtonnage), 0)
into y_OneSluiceDownTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '1#'
and t.sluicedirection = '1'; --1#闸下航实载
select nvl(sum(t.actualtonnage), 0)
into y_TwoSluiceDownTrueTonnage
from sluicedispatch t
where to_char(t.overtime, 'YYYY') = yearchar
and t.sluiceoid = SLUICE
and t.sonsluiceoid = '2#'
and t.sluicedirection = '1'; --2#闸下航实载
y_CargoShipDownTrueTonnage := y_OneSluiceDownTrueTonnage +
y_TwoSluiceDownTrueTonnage;
--实载下航前五名货名及货量
select wm_concat(p.N)
into y_DownTopFiveGoodNameAndCount
from (select a.*, rownum rn
from (select v.GOOODTYPENAME || ':' || sum
(v.TRUETONNAGE) N
from v_shipreport v
where substr(v.REPORTTIME,0,7) = yearchar
and
v.SLUICEOID = SLUICE
and
v.LASTSTATUS = '过闸'
and
v.SHIPDIRECTION = '下行'
and
v.TRUETONNAGE is not null
group by v.GOOODTYPENAME, v.TRUETONNAGE
order by sum
(v.TRUETONNAGE) desc) a
where rownum <= 5) p;
--实载上航前五名货名及货量
select wm_concat(p.N)
into y_UpTopFiveGoodNameAndCount
from (select a.*
from (select v.GOOODTYPENAME || ':' || sum
(v.TRUETONNAGE) N
from v_shipreport v
where substr(v.REPORTTIME,0,7) = yearchar
and
v.SLUICEOID = SLUICE
and
v.LASTSTATUS = '过闸'
and
v.SHIPDIRECTION = '上行'
and
v.TRUETONNAGE is not null
group by v.GOOODTYPENAME, v.TRUETONNAGE
order by sum
(v.TRUETONNAGE) desc) a
where rownum <= 5) p;
--货船总核载
y_CargoShipTotalCheckTonnage := y_CargoShipUpCheckTonnage +
y_CargoShipDownCheckTonnage;
--货船总实载
y_CargoShipTotalTrueTonnnage := y_CargoShipUpTrueTonnage +
y_CargoShipDownTrueTonnage;
--客船总核载
y_CustomerTotalCheckTonnage := 0;
--重要语句
vv:=row_month_report(daychar,
m_SluiceName,
m_OneSluiceCount,
m_TwoSluiceCount,
m_OneUpEmptyCount,
m_OneDownemptyCount,
m_TwoUpemptyCount,
m_TwoDownemptyCount,
------------------------
y_OneSluiceCount,
y_TwoSluiceCount,
y_OneUpEmptyCount,
y_OneDownemptyCount,
y_TwoUpemptyCount,
y_TwoDownemptyCount,
---------------------------
m_TotalPassShipCount,
m_UpRunCount,
m_DownRunCount,
m_OneSluiceUpCount,
m_OneSluiceDownCount,
m_TwoSluiceUpCount,
m_TwoSluiceDownCount,
m_DangerousShipCount,
m_CustomerShipCount,
m_SmallShipCount,
m_NotProfitShipCount,
----------------------------
y_TotalPassShipCount,
y_UpRunCount,
y_DownRunCount,
y_OneSluiceUpCount,
y_OneSluiceDownCount,
y_TwoSluiceUpCount,
y_TwoSluiceDownCount,
y_DangerousShipCount,
y_CustomerShipCount,
y_SmallShipCount,
y_NotProfitShipCount,
----------月过闸船舶的货量------------------
m_CargoShipTotalCheckTonnage,
m_CargoShipTotalTrueTonnnage,
m_CustomerTotalCheckTonnage,
m_CargoShipUpCheckTonnage,
m_OneSluiceUpCheckTonnage,
m_TwoSluiceUpCheckTonnage,
m_CargoShipDownCheckTonnage,
m_OneSluiceDownCheckTonnage,
m_TwoSluiceDownCheckTonnage,
m_CargoShipUpTrueTonnage,
m_OneSluiceUpTrueTonnage,
m_TwoSluiceUpTrueTonnage,
m_CargoShipDownTrueTonnage,
m_OneSluiceDownTrueTonnage,
m_TwoSluiceDownTrueTonnage,
m_UpTopFiveGoodNameAndCount,
m_DownTopFiveGoodNameAndCount,
-------------------------------------
y_CargoShipTotalCheckTonnage,
y_CargoShipTotalTrueTonnnage,
y_CustomerTotalCheckTonnage,
y_CargoShipUpCheckTonnage,
y_OneSluiceUpCheckTonnage,
y_TwoSluiceUpCheckTonnage,
y_CargoShipDownCheckTonnage,
y_OneSluiceDownCheckTonnage,
y_TwoSluiceDownCheckTonnage,
y_CargoShipUpTrueTonnage,
y_OneSluiceUpTrueTonnage,
y_TwoSluiceUpTrueTonnage,
y_CargoShipDownTrueTonnage,
y_OneSluiceDownTrueTonnage,
y_TwoSluiceDownTrueTonnage,
y_UpTopFiveGoodNameAndCount,
y_DownTopFiveGoodNameAndCount);
pipe row(vv);
return;
end if;
end;