EXECUTE IMMEDIATE 'create table invest_analysis_tmp1 as
(select a.cfg_id，
a.group_id,
sum(a.market_amt /
(select b.fund_amt
from evaluate_sum b
where b.eval_date = i_EvalDate
and b.group_id = a.group_id) * 100) s_market
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.market_amt /
(select sum(b.fund_amt)
from evaluate_sum b
where b.eval_date = i_EvalDate
) * 100) s_market
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_market)) 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_market)) 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_market,"FM99990.999")) for group_id in( "1003",
"1004",
"1021",
"1022",
"1023",
"1024",
"1025",
"1026",
"1161",
"1162",
"合計"))';
uj5u.com熱心網友回復：
說一報錯資訊。1、無權限（execute 建表 需要顯式授權）。
2、動態陳述句有語法錯誤。
3、除零錯誤。
很多很多
uj5u.com熱心網友回復：
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.market_amt /
(select b.fund_amt
from evaluate_sum b
where b.eval_date = i_EvalDate
and b.group_id = a.group_id) * 100) s_market
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.market_amt /
(select sum(b.fund_amt)
from evaluate_sum b
where b.eval_date = i_EvalDate
) * 100) s_market
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_market)) 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_market)) 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_market,"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該表不存在
uj5u.com熱心網友回復：
試試這個：
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.market_amt /
(select b.fund_amt
from evaluate_sum b
where b.eval_date = i_EvalDate
and b.group_id = a.group_id) * 100) s_market
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.market_amt /
(select sum(b.fund_amt)
from evaluate_sum b
where b.eval_date = i_EvalDate
) * 100) s_market
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_market)) 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_market)) 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_market,"FM99990.999")) for group_id in("1003",
"1004",
"1021",
"1022",
"1023",
"1024",
"1025",
"1026",
"1161",
"1162",
"合計"))';
end;
uj5u.com熱心網友回復：
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.market_amt /
(select b.fund_amt
from evaluate_sum b
where b.eval_date = i_EvalDate
and b.group_id = a.group_id) * 100) s_market
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.market_amt /
(select sum(b.fund_amt)
from evaluate_sum b
where b.eval_date = i_EvalDate
) * 100) s_market
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_market)) 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_market)) 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_market,"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該表不存在
uj5u.com熱心網友回復：
表前面加上用戶名看看。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/77952.html
標籤：開發
下一篇：Oracle新手求解決問題