接口分析
效果图
存储过程
create or replace procedure sp_getcustomersum(i_oprtAcc in t_customer.oprt_acc%type, --操作账号 i_zoneId in t_zone.zone_id%type, --区域id i_fchsId in t_franchiser.fchs_id%type, --经销商id i_storeId in t_store.store_id%type, --门店id i_dateFlag in char, --日期标志 0:月份 1:年份 2:区间 i_date in char, --日期 i_startTime in char, --开始时间 i_endTime in char, --结束时间 o_resultCursor out sys_refcursor, --返回消息 o_errorNumber out integer) --错误码 is --当前年 l_currentYear char(4); --xx以前客户总量 l_allCounts integer; l_exception exception; --检查账号异常 l_exceptionAccount exception; --错误消息 l_error_msg varchar2(100); /********** *creater: lxl *craeateTime: 2015-07-15 *function: 获得客户信息明细 **********/begin pkg_utility.sp_writeLog(i_log_type => 'INFO', i_acc_id => i_oprtAcc, i_sp_name => 'sp_getcustomersum', i_log_desc => '开始'); --这里先调用下级联检查存储过程，看账号是否可用 sp_checkaccount(i_account => i_oprtAcc, o_errNumber => o_errorNumber); --账号不可用，直接退出 if o_errorNumber != 0 then --异常处理 raise l_exceptionAccount; end if; --初始化状态值 o_errorNumber := pkg_constants.C_RTN_SUCCESS; l_error_msg := ''; --取系统年份 select to_char(sysdate, 'YYYY') into l_currentYear from dual; --需要对dateFlag进行判空 --检测到异常时，需要对输出值进行设置 if i_dateFlag is null then raise l_exception; end if; --1.i_dateFlag = 0,初始化进来显示当年12个月的客户量 if i_dateFlag = 0 then begin --1.先根据i_date计算出i_date之前所有的客户量 select nvl(count(*),0) into l_allCounts from t_customer tc where tc.add_date < i_date || '0101'; --2.再将结果相加返回给前台 open o_resultCursor for select rownum, ny, sumCs, cstmAllNum, max(sumCs) over() as maxSumCs, min(sumCs) over() as minSumCs, max(cstmAllNum) over() as maxCstmAllNum, min(cstmAllNum) over() as minCstmAllNum from (select rownum, ny, sumCs, (sum(sumCs) over(order by ny) + l_allCounts) as cstmAllNum from (select nvl(substr(tc.add_date, 0, 6),0) as ny, nvl(count(tc.cstm_id),0) as sumCs from t_customer tc where substr(tc.add_date, 0, 4) = i_date --带上区域id and (trim(i_zoneId) is null or tc.zone_id = i_zoneId) --带上经销商id and (trim(i_fchsId) is null or tc.fchs_id = i_fchsId) --带上门店id and (trim(i_storeId) is null or tc.store_id = i_storeId) group by substr(tc.add_date, 0, 6)) order by ny); --加缺陷判断 exception when others then raise l_exception; end; end if; --end i_dateFlag = 0 --2.i_dateFlag = 1,查询当年前6年的数据 --按照年来分组 if i_dateFlag = 1 then begin --1.先计算出(l_currentYear - i_date + 1) || '0101之前的客户量 select nvl(count(*),0) into l_allCounts from t_customer tc where tc.add_date < (l_currentYear - i_date + 1) || '0101'; --2.再将结果相加返回给前台 open o_resultCursor for select rownum, ny, sumCs, cstmAllNum, max(sumCs) over() as maxSumCs, min(sumCs) over() as minSumCs, max(cstmAllNum) over() as maxCstmAllNum, min(cstmAllNum) over() as minCstmAllNum from (select rownum, ny, sumCs, (sum(sumCs) over(order by rownum) + l_allCounts) as cstmAllNum from (select nvl(substr(tc.add_date, 0, 4),0) as ny, nvl(count(tc.cstm_id),0) as sumCs from t_customer tc where substr(tc.add_date, 0, 4) between (l_currentYear - i_date + 1) and l_currentYear group by substr(tc.add_date, 0, 4)) order by ny); --加缺陷判断 exception when others then raise l_exception; end; end if; --end i_dateFlag = 1 --3.i_dateFlag = 2时，只需要开始时间和结束时间 if i_dateFlag = 2 then begin --1.先计算出i_startTime之前的客户量 select nvl(count(*),0) into l_allCounts from t_customer tc where tc.add_date < i_startTime; --2.再将结果相加返回给前台 open o_resultCursor for select rownum, ny, sumCs, cstmAllNum, max(sumCs) over() as maxSumCs, min(sumCs) over() as minSumCs, max(cstmAllNum) over() as maxCstmAllNum, min(cstmAllNum) over() as minCstmAllNum from (select rownum, ny, sumCs, (sum(sumCs) over(order by rownum) + l_allCounts) as cstmAllNum from (select nvl(substr(tc.add_date, 0, 6),0) as ny, nvl(count(tc.cstm_id),0) as sumCs from t_customer tc where substr(tc.add_date, 0, 6) between i_startTime and i_endTime group by substr(tc.add_date, 0, 6)) order by ny); --加缺陷判断 exception when others then raise l_exception; end; end if; --end i_dateFlag = 2 pkg_utility.sp_writeLog(i_log_type => 'INFO', i_acc_id => i_oprtAcc, i_sp_name => 'sp_getcustomersum', i_log_desc => '结束');exception when l_exceptionAccount then --给游标赋值 open o_resultCursor for select rownum, '' as ny, '' as sumCs, '' as cstmAllNum, '' as maxSumCs, '' as minSumCs, '' as maxCstmAllNum, '' as minCstmAllNum from dual where rownum = 1; when l_exception then o_errorNumber := PKG_CONSTANTS.C_RTN_FAILURE; when others then o_errorNumber := PKG_CONSTANTS.C_RTN_FAILURE; l_error_msg := '获取客户信息明细异常'; pkg_utility.sp_writeLog(i_log_type => 'ERROR', i_acc_id => i_oprtAcc, i_sp_name => 'sp_getcustomersum', i_log_desc => l_error_msg);end sp_getcustomersum;
结束