oracle 动态SQL在存储过程中的使用

createorreplaceprocedure BIS_PROJECT_BUDGET(P_DATE inDATE) is

   v_date varchar2(10);

   v_year varchar2(10);

   v_month varchar2(10);

   v_month_name varchar2(10);

   v_sql varchar2(4000);

  begin

      --取当日的上个月的最后一天     每个月首日统计上个月

       select to_char((last_day(add_months(P_DATE,-1))),'yyyy-mm-dd') last_day ,

         to_char((last_day(add_months(P_DATE,-1))),'yyyy') years,

         to_char((last_day(add_months(P_DATE,-1))),'mm') months

       into v_date,v_year,v_month

       from dual;

       select

        case  when v_month='01'then  'bt.JAN_KPI'

              when v_month='02'then  'bt.FEB_KPI'

              when v_month='03'then  'bt.MAR_KPI'

              when v_month='04'then  'bt.APR_KPI'

              when v_month='05'then  'bt.MAY_KPI'

              when v_month='06'then  'bt.JUN_KPI'

              when v_month='07'then  'bt.JUL_KPI'

              when v_month='08'then  'bt.AUG_KPI'

              when v_month='09'then  'bt.SEP_KPI'

              when v_month='10'then  'bt.OCT_KPI'

              when v_month='11'then  'bt.NOV_KPI'

              else'DEC_KPI'endinto v_month_name

        from dual;

       delete BIS_SECOND_ZS_PRO  where PRO_MONTH=to_number(v_month) and PRO_YEAR = v_year;

       commit;

   v_sql:= ' 

     insert into BIS_SECOND_ZS_PRO bp(

        BIS_SECOND_ZS_PRO_ID ,

        bis_project_id,

        PRO_YEAR,

        PRO_MONTH,

        EMPTY_PRE,

        BUDGETYEAR_MONTH,

        CREATOR,

        CREATED_DATE,

        RECORD_VERSION

        )

        select

          sys_guid(),

          m.bis_project_id,

          '||v_year||' as years,

          to_number('||v_month||') as months,

          m.budget_month,

          y.budget_year,

          ''system'',

          sysdate,

          dbms_random.value(0,100000)

        from

        (

        select

          r.bis_project_id,

          nvl(decode(r.total_square,0,0,round(('||v_month_name||' - r.open_square/r.total_square)*r.total_square,2)),0)   budget_month

        from

          (select  pt.bis_project_id,

             pt.short_name,

             pt.total_square,

             nvl(sum(af.open_square_total),0) open_square

           from

              (select p.bis_project_id,

                   p.short_name,

                   nvl(sum(vf.rent_square_total),0) total_square

              from bis_project p left join vw_bis_mall_floor_summary vf on p.bis_project_id = vf.bis_project_id

              where  p.is_business_project = ''1''

                   and p.oper_status = ''2''

              group by p.bis_project_id,p.short_name) pt,

              rep_agg_floor_month af

            where 1 = 1

                and pt.bis_project_id = af.bis_project_id

                and af.store_type = ''1''

                and af.agg_year = '||v_year||'

                and af.agg_month = to_number('||v_month||')

                group by pt.bis_project_id,pt.short_name,pt.total_square) r,

           bis_project_target bt

          where 1=1

              and r.bis_project_id = bt.bis_project_id(+)

      ) m,

      (

      select r.bis_project_id,

             nvl(decode(r.total_square,0,0,round(('||v_month_name||' - r.open_square/r.total_square)*r.total_square,2)),0)+r.off_square   budget_year                                                 

      from

      (select  pt.bis_project_id,

             pt.short_name,

             pt.total_square,

             af.agg_month,

             af.agg_year,

             nvl(sum(af.open_square_total),0) open_square,

             nvl(sum(af.off_square_total),0) off_square

      from

      (select p.bis_project_id,

             p.short_name,

             nvl(sum(vf.rent_square_total),0) total_square

      from bis_project p left join vw_bis_mall_floor_summary vf on p.bis_project_id = vf.bis_project_id

      where  p.is_business_project = ''1''

             and p.oper_status = ''2''

      group by p.bis_project_id,p.short_name) pt,rep_agg_floor_month af

      where 1 = 1

            and pt.bis_project_id = af.bis_project_id

            and af.store_type = ''1''

            and af.agg_year = '||v_year||'

            and af.agg_month = ''1''

      group by pt.bis_project_id,pt.short_name,pt.total_square,af.agg_month,af.agg_year) r,bis_project_target bt

      where r.bis_project_id = bt.bis_project_id(+)) y

      where m.bis_project_id = y.bis_project_id

     ';

     executeimmediate v_sql;

   --  v_sql:='select 1 as a from dual;';

      commit;

    EXCEPTION

    -- 异常处理部份

    WHENOTHERSTHEN

      dbms_output.put_line('insertBisSecondZsPro 错误：' || SQLERRM);

      null;

  end BIS_PROJECT_BUDGET;