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

qq_38049439 2017-11-30 02:09:18
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",
"合计"))';
...全文
282 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
引用 4 楼 qq_38049439 的回复:
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该表不存在

表前面加上用户名看看。
qq_38049439 2017-11-30
  • 打赏
  • 举报
 回复
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该表不存在
碧水幽幽泉 2017-11-30
  • 打赏
  • 举报
 回复
试试这个：


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;
qq_38049439 2017-11-30
  • 打赏
  • 举报
 回复
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该表不存在
卖水果的net 2017-11-30
  • 打赏
  • 举报
 回复
说一报错信息。 1、无权限（execute 建表 需要显式授权）。 2、动态语句有语法错误。 3、除零错误。 很多很多
oracle 19c 数据库基础和日常管理
Oracle 19c 数据库基础与日常管理 通过本课程使学员掌握在Linux环境下Oracle 19c 数据库的基础知识和日常管理。 课程主要包括以下主要内容: 1.数据库的启动和关闭 2.连接CDB和PDB数据库 3.PDB数据库的管理 4.命令行...
利用Navicat创建oracle数据库表做原数据库备份
在做项目时，为了给原来项目的数据库做备份，在网上查了很多资料，很感谢这为博主写的这篇博客，但是根据这个博客创建好表空间后发现数据库和原来的数据库串了，修改一个的数据，另一个也会修改。经过和大神讨论，是因为新建了这个ITEST的连接，原来的数据库也在这个连接里，这样不管修改哪个数据库的数据其实修改的都是同一个空间的数据。 现将方法总结下来： 其实在Oracle中的概念并不是创建数据库，而是创建一...
Oracle DB 使用DDL语句创建和管理表
Oracle DB 使用DDL语句创建和管理表
oracle延迟执行时间
问题描述：前段时间在运行存储过程的时候遇到一个情况，如果存储过程执行时间过长，会造成前台连接桥断，造成用户体验极差，对程序也很有影响，后来找个不是正式库环境进行测试，你总不能在正式库执行吧，这有肯呢个造成数据混乱的（最好不要操作正式环境的数据，自测最好找个环境），不在正式库执行会造成数据不够多，执行效果不明显。 问了大神，大神提供给一个函数，放到存储过程run就行了。下面我们来看看这个函数。
oracle pdb还原为no-cdb,Oracle12c，CDB和PDB创建表空间和用户，以及Pl/SQL连接PDB
今天上午刚安装的12c，用来一下午的时间总结出来的，不保猜测的对错，但保证SQL能执行成功。CDB给用户授权表空间的SQL是从别出整理过来的，因为我是在PDB中创建，没测试过下面代码是否可用。alter user C##truedata quota unlimited onTRUEDATA;这层目录应该是CDB，圈住的是PDB，他们都有各自的表空间。 下面这段话是看到一个大神的话，他应该看了不少o...
Oracle

17,082

社区成员

55,236

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章