• Oracle 查询,返回记录集,不是用游标,不用创建临时表完整版


    --首先自定义一种名为: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;
  • 相关阅读:
    jQuery选择器
    ViewState对象与Session对象、Cookie对象
    angularjs关于$scope的生命周期详解(转)
    了解AngularJS的$scope的工作原理和生命周期(转载)
    angularjs route 路由
    angularJS--依赖注入
    angularjs动画-ngAnimate--快速上手、使用
    深入浅出理解AngularJS模块
    Angular入门1-之-基础概览02
    Angular入门1-之-基础概览01
  • 原文地址:https://www.cnblogs.com/xgxhellboy/p/2707152.html
Copyright © 2020-2023  润新知