oracle存过创建表并删除表的问题，请大神帮忙看看为啥我这段存过无法执行也就是无法创建表1和表2

Question

EXECUTE IMMEDIATE 'create  table invest_analysis_tmp1  as
                        (select a.cfg_id，
                                a.group_id,
                                sum(a.et_amt /
                                    (select b.fund_amt
                                       from evaluate_sum b
                                      where b.eval_date = i_EvalDate
                                        and b.group_id = a.group_id) * 100) s_et
                           from invest_analysis_record_tmp a
                          where a.eval_date =i_EvalDate
                          group by a.cfg_id, a.group_id)
                   union
                    (select a.cfg_id,"合计"  group_id ,
                                    sum(a.et_amt /
                                      (select sum(b.fund_amt)
                                       from evaluate_sum b
                                      where b.eval_date = i_EvalDate
                                        ) * 100) s_et
                           from invest_analysis_record_tmp a
                          where a.eval_date = i_EvalDate
                          group by a.cfg_id)' ;
EXECUTE IMMEDIATE 'create table invest_analysis_tmp2  as
                  (select * from invest_analysis_tmp1)
                   union
                  （select c.parent_id cfg_id , o.group_id, sum(to_number(o.s_et)) s_maket
                    from invest_analysis_tmp1 o, cfg_asset_category c
                   where o.cfg_id = c.id(+)
                   group by c.parent_id, o.group_id）
                   union 
                   (select 9999 cfg_id, o.group_id, sum(to_number(o.s_et)) s_maket
                      from invest_analysis_tmp1 o, cfg_asset_category c
                     where o.cfg_id = c.id(+) and  c.parent_id is not null
                      group by o.group_id) ';EXECUTE IMMEDIATE 'create table invest_analysis_tmp3  as
             select * from
                     (select o.cfg_id id ,decode(o.cfg_id,9999,9999,c.parent_id) parent_id,
                      case  when c.parent_id=1000 then c.cate_name||"小计"
                            when c.parent_id is null then "总计"
                            else  c.cate_name end cate_name,
       
                        o.*
                from invest_analysis_tmp2 o, cfg_asset_category c
                where o.cfg_id=c.id(+) )
         pivot(sum(to_char(s_et,"FM99990.999")) for group_id in( "1003",
                                               "1004",
                                               "1021",
                                               "1022",
                                               "1023",
                                               "1024",
                                               "1025",
                                               "1026",
                                               "1161",
                                               "1162",
                                               "合计"))';

Answer 1

说一报错信息。1、无权限（execute 建表 需要显式授权）。
2、动态语句有语法错误。
3、除零错误。很多很多

Answer 2

Procedure proc_invest_analysis_3(i_EvalDate   In Varchar2, --定价日
                              o_Flag       Out Varchar2, --返回编号
                              o_Msg        Out Varchar2, --返回信息
                              o_title      Out Varchar2, --标题
                              o_reportDate Out Varchar2, --报告日期
                              o_Ret        Out ref_cursor --导出游标
                              ) is
  
                                                   
  Begin
    
   EXECUTE IMMEDIATE 'create  table invest_analysis_tmp1  as
                        (select a.cfg_id，
                                a.group_id,
                                sum(a.et_amt /
                                    (select b.fund_amt
                                       from evaluate_sum b
                                      where b.eval_date = i_EvalDate
                                        and b.group_id = a.group_id) * 100) s_et
                           from invest_analysis_record_tmp a
                          where a.eval_date =i_EvalDate
                          group by a.cfg_id, a.group_id)
                   union 
                    (select a.cfg_id,"合计"  group_id ,
                                    sum(a.et_amt /
                                      (select sum(b.fund_amt)
                                       from evaluate_sum b
                                      where b.eval_date = i_EvalDate
                                        ) * 100) s_et
                           from invest_analysis_record_tmp a
                          where a.eval_date = i_EvalDate
                          group by a.cfg_id)' ;
EXECUTE IMMEDIATE 'create table invest_analysis_tmp2  as
                  (select * from invest_analysis_tmp1)
                   union 
                  （select c.parent_id cfg_id , o.group_id, sum(to_number(o.s_et)) s_maket
                    from invest_analysis_tmp1 o, cfg_asset_category c
                   where o.cfg_id = c.id(+)
                   group by c.parent_id, o.group_id）
                   union 
                   (select 9999 cfg_id, o.group_id, sum(to_number(o.s_et)) s_maket
                      from invest_analysis_tmp1 o, cfg_asset_category c
                     where o.cfg_id = c.id(+) and  c.parent_id is not null
                      group by o.group_id) ';EXECUTE IMMEDIATE 'create   table invest_analysis_tmp3  as
             select * from
                     (select o.cfg_id id ,decode(o.cfg_id,9999,9999,c.parent_id) parent_id,
                      case  when c.parent_id=1000 then c.cate_name||"小计"
                            when c.parent_id is null then "总计"
                            else  c.cate_name end cate_name,
       
                        o.*
                from invest_analysis_tmp2 o, cfg_asset_category c
                where o.cfg_id=c.id(+) )
         pivot(sum(to_char(s_et,"FM99990.999")) for group_id in( "1003",
                                               "1004",
                                               "1021",
                                               "1022",
                                               "1023",
                                               "1024",
                                               "1025",
                                               "1026",
                                               "1161",
                                               "1162",
                                               "合计"))'; -----删除临时表
  EXECUTE IMMEDIATE 'drop table invest_analysis_tmp1';
 
  EXECUTE IMMEDIATE 'drop table invest_analysis_tmp2';  
   
  OPEN o_Ret for
    
-----第四步，返回游标
       select *
      from (select *
              from (select *
                      from invest_analysis_tmp3 o                     start with o.parent_id = 1000
                    connect by prior o.id = o.parent_id
                     order SIBLINGS by id)
            union all (select * from invest_analysis_tmp3 t where t.id = 9999));
       EXECUTE IMMEDIATE 'drop table invest_analysis_tmp3'; 
--标题
    o_title := '大类资产净值占比表';
    --定价日
    o_reportDate := '定价日：' || i_EvalDate;
    o_Flag       := '0';
    o_Msg        := '操作成功';
    Return;
  Exception
    When Others Then
      o_Flag := '9999'; -- 失败
      o_Msg  := '异常结束';
      rollback;
  End;    
   您好，   整个存过是这样的，执行会报invest_analysis_tmp3该表不存在

Answer 3

试试这个：create or replace procedure p_test
is
begin
execute immediate 'create table invest_analysis_tmp1  as
select a.cfg_id，
    a.group_id,
sum(a.et_amt /
(select b.fund_amt
   from evaluate_sum b
  where b.eval_date = i_EvalDate
and b.group_id = a.group_id) * 100) s_et
   from invest_analysis_record_tmp a
  where a.eval_date =i_EvalDate
  group by a.cfg_id, a.group_id
   union
   select a.cfg_id,"合计" group_id ,
sum(a.et_amt /
  (select sum(b.fund_amt)
   from evaluate_sum b
  where b.eval_date = i_EvalDate
) * 100) s_et
   from invest_analysis_record_tmp a
  where a.eval_date = i_EvalDate
  group by a.cfg_id';
execute immediate  'create table invest_analysis_tmp2 as
  select * from invest_analysis_tmp1
   union
  select c.parent_id cfg_id,o.group_id, sum(to_number(o.s_et)) s_maket
from invest_analysis_tmp1 o, cfg_asset_category c
   where o.cfg_id = c.id(+)
   group by c.parent_id, o.group_id
   union 
   select 9999 cfg_id, o.group_id, sum(to_number(o.s_et)) s_maket
  from invest_analysis_tmp1 o, cfg_asset_category c
 where o.cfg_id = c.id(+) and  c.parent_id is not null
  group by o.group_id'; execute immediate  'create table invest_analysis_tmp3  as
 select * from
 (select o.cfg_id id ,decode(o.cfg_id,9999,9999,c.parent_id) parent_id,
  case when c.parent_id=1000 then c.cate_name||"小计"
   when c.parent_id is null then "总计"
   else c.cate_name end cate_name,
o.*
from invest_analysis_tmp2 o, cfg_asset_category c
where o.cfg_id=c.id(+))
 pivot(sum(to_char(s_et,"FM99990.999")) for group_id in("1003",
   "1004",
   "1021",
   "1022",
   "1023",
   "1024",
   "1025",
   "1026",
   "1161",
   "1162",
   "合计"))';
end;
   

Answer 4

Procedure proc_invest_analysis_3(i_EvalDate   In Varchar2, --定价日
                              o_Flag       Out Varchar2, --返回编号
                              o_Msg        Out Varchar2, --返回信息
                              o_title      Out Varchar2, --标题
                              o_reportDate Out Varchar2, --报告日期
                              o_Ret        Out ref_cursor --导出游标
                              ) is
  
                                                   
  Begin
    
   EXECUTE IMMEDIATE 'create  table invest_analysis_tmp1  as
                        (select a.cfg_id，
                                a.group_id,
                                sum(a.et_amt /
                                    (select b.fund_amt
                                       from evaluate_sum b
                                      where b.eval_date = i_EvalDate
                                        and b.group_id = a.group_id) * 100) s_et
                           from invest_analysis_record_tmp a
                          where a.eval_date =i_EvalDate
                          group by a.cfg_id, a.group_id)
                   union 
                    (select a.cfg_id,"合计"  group_id ,
                                    sum(a.et_amt /
                                      (select sum(b.fund_amt)
                                       from evaluate_sum b
                                      where b.eval_date = i_EvalDate
                                        ) * 100) s_et
                           from invest_analysis_record_tmp a
                          where a.eval_date = i_EvalDate
                          group by a.cfg_id)' ;
EXECUTE IMMEDIATE 'create table invest_analysis_tmp2  as
                  (select * from invest_analysis_tmp1)
                   union 
                  （select c.parent_id cfg_id , o.group_id, sum(to_number(o.s_et)) s_maket
                    from invest_analysis_tmp1 o, cfg_asset_category c
                   where o.cfg_id = c.id(+)
                   group by c.parent_id, o.group_id）
                   union 
                   (select 9999 cfg_id, o.group_id, sum(to_number(o.s_et)) s_maket
                      from invest_analysis_tmp1 o, cfg_asset_category c
                     where o.cfg_id = c.id(+) and  c.parent_id is not null
                      group by o.group_id) ';EXECUTE IMMEDIATE 'create   table invest_analysis_tmp3  as
             select * from
                     (select o.cfg_id id ,decode(o.cfg_id,9999,9999,c.parent_id) parent_id,
                      case  when c.parent_id=1000 then c.cate_name||"小计"
                            when c.parent_id is null then "总计"
                            else  c.cate_name end cate_name,
       
                        o.*
                from invest_analysis_tmp2 o, cfg_asset_category c
                where o.cfg_id=c.id(+) )
         pivot(sum(to_char(s_et,"FM99990.999")) for group_id in( "1003",
                                               "1004",
                                               "1021",
                                               "1022",
                                               "1023",
                                               "1024",
                                               "1025",
                                               "1026",
                                               "1161",
                                               "1162",
                                               "合计"))'; -----删除临时表
  EXECUTE IMMEDIATE 'drop table invest_analysis_tmp1';
 
  EXECUTE IMMEDIATE 'drop table invest_analysis_tmp2';  
   
  OPEN o_Ret for
    
-----第四步，返回游标
       select *
      from (select *
              from (select *
                      from invest_analysis_tmp3 o--执行会在改行报表或视图不存在
                     start with o.parent_id = 1000
                    connect by prior o.id = o.parent_id
                     order SIBLINGS by id)
            union all (select * from invest_analysis_tmp3 t where t.id = 9999));
       EXECUTE IMMEDIATE 'drop table invest_analysis_tmp3'; 
--标题
    o_title := '大类资产净值占比表';
    --定价日
    o_reportDate := '定价日：' || i_EvalDate;
    o_Flag       := '0';
    o_Msg        := '操作成功';
    Return;
  Exception
    When Others Then
      o_Flag := '9999'; -- 失败
      o_Msg  := '异常结束';
      rollback;
  End;    
   您好，这是我整个存过的语法，执行会报invest_analysis_tmp3该表不存在