-----------------存储过程包体-----------


procedure GetComparativeAnalysisTB(p_StartTime                varchar2, ----开始时间

                                 p_EndTime                  varchar2, ----结束时间

                                 p_type                     varchar2, ----1：按全市2：按行业3、按企业

                                 p_id                       varchar2, ----1全部 、区县编码或企业名称

                                 p_id1                      varchar2, ----按企业查询p_id为区县p_id1为企业编码

                                 c_Select                   in out REF_WFData)

   is

cursor cursor_1 IS

   ---获取 时间段年份

   SELECT (to_number(to_char(TO_DATE(p_StartTime, 'yyyy-MM'), 'YYYY'))  + ROWNUM-1) AS Yearlist

   FROM DUAL

   CONNECT BY ROWNUM <= (to_char(to_date(p_EndTime, 'yyyy-MM'),'yyyy')-to_char(to_date(p_StartTime, 'yyyy-MM'),'yyyy'))+1

   order by Yearlist desc;

 V_SQL VARCHAR2(32767);

/* aYear varchar2(200);---获取年*/

 bMonth varchar2(200);---获取月

 v_StartTime varchar2(200); ---- 开始时间

 v_EndTime varchar2(200);   ----结束时间

  begin

    v_StartTime:=p_StartTime||'-01';

    v_EndTime:=p_EndTime||'-01';

       if  p_type=1 then    ---统计类别----  按全市

               V_SQL := 'select t1.ps_code,t1.ps_name';

        FOR V_XCLCK IN cursor_1

      LOOP

      SELECT to_char(substr(p_StartTime,6,instr(p_StartTime,'-')-3)) into bMonth FROM DUAL ;--获取月

        V_SQL := V_SQL ||','|| 'min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.Yearlist||'-'|| bMonth||''' then t1.pfl end) as ' || 'pfl'||V_XCLCK.Yearlist||bMonth||','

                            || 'min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.Yearlist||'-'|| bMonth||''' then t1.fqpfl end) as ' || 'fqpfl'||V_XCLCK.Yearlist||bMonth;

      END LOOP;

       V_SQL := V_SQL || '  from (

                                  select

                                  a.ps_month,

                                  a.c0008_pid ps_code,

                                  a.c0008_item_desc ps_name,

                                  b.ps_pfl fqpfl,

                                  sum(nvl(a.ps_pfl,0)) pfl

                                  from PSINFO_OUTPUTSUBPFYZ_SB a,PSINFO_OUTPUTSUB_SB b

                                  where a.c0003_stcode=b.c0003_stcode

                                  and a.c0070_enterprise_code=b.c0070_enterprise_code

                                  and a.c0007_pcode=b.c0007_pcode

                                  and a.ps_month=b.ps_month

                                  and a.ps_month>=to_date('''||v_StartTime||''',''yyyy-mm-dd'')

                                  and a.ps_month<=to_date('''||v_EndTime||''',''yyyy-mm-dd'')

                                  and a.c0008_pid in (201,203,207)

                                  and a.ps_state=1

                                  group by a.c0008_pid,a.c0008_item_desc,a.ps_month,b.ps_pfl

                                  ) t1

                                  group by ps_code,ps_name

                                  order by ps_code';

      DBMS_OUTPUT.PUT_LINE(V_SQL);

       open c_Select for V_SQL;

         end if;

end GetComparativeAnalysisTB;

cursor cursor_1 IS

   ---获取 时间段月份

    SELECT TO_CHAR(ADD_MONTHS(TO_DATE(p_StartTime, 'yyyy-MM'), ROWNUM - 1),  'yyyy-MM') as monthlist

    FROM DUAL

    CONNECT BY ROWNUM <= months_between(to_date(p_EndTime, 'yyyy-MM'),to_date(p_StartTime, 'yyyy-MM')) + 1 ;

 V_SQL VARCHAR2(32767);

 aYear varchar2(200);---获取年

 bMonth varchar2(200);---获取月

 v_StartTime varchar2(200); ---- 开始时间

 v_EndTime varchar2(200);   ----结束时间

  begin

    v_StartTime:=p_StartTime||'-01';

    v_EndTime:=p_EndTime||'-01';

       if  p_type=1 then    ---统计类别----  按全市

            if p_id='0' then

               V_SQL := 'select t1.ps_code,t1.ps_name';

        FOR V_XCLCK IN cursor_1

      LOOP

        SELECT to_char(substr(V_XCLCK.monthlist,1,instr(V_XCLCK.monthlist,'-')-1)),to_char(substr(V_XCLCK.monthlist,6,instr(V_XCLCK.monthlist,'-')-3)) into aYear,bMonth FROM DUAL ;

        V_SQL := V_SQL ||','|| 'nvl(min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.monthlist||''' then t1.pfl end),0) as ' || 'pfl'||aYear||bMonth||','

                            || 'nvl(min(case when to_char(t1.ps_month,''yyyy-mm'')=''' || V_XCLCK.monthlist||''' then t1.fqpfl end),0) as ' || 'fqpfl'||aYear||bMonth;

      END LOOP;

       V_SQL := V_SQL || '  from (

                                  select

                                  a.ps_month,

                                  a.c0008_pid ps_code,

                                  a.c0008_item_desc ps_name,

                                  b.ps_pfl fqpfl,

                                  sum(nvl(a.ps_pfl,0)) pfl

                                  from PSINFO_OUTPUTSUBPFYZ_SB a,PSINFO_OUTPUTSUB_SB b

                                  where a.c0003_stcode=b.c0003_stcode

                                  and a.c0070_enterprise_code=b.c0070_enterprise_code

                                  and a.c0007_pcode=b.c0007_pcode

                                  and a.ps_month=b.ps_month

                                  and a.ps_month>=to_date('''||v_StartTime||''',''yyyy-mm-dd'')

                                  and a.ps_month<=to_date('''||v_EndTime||''',''yyyy-mm-dd'')

                                  and a.c0008_pid in (201,203,207)

                                  and a.ps_state=1

                                  group by a.c0008_pid,a.c0008_item_desc,a.ps_month,b.ps_pfl

                                  ) t1

                                  group by ps_code,ps_name

                                  order by ps_code';

      DBMS_OUTPUT.PUT_LINE(V_SQL);

       open c_Select for V_SQL;