oracle中function能否这样使用临时表

jenry_leon 2008-10-20 03:11:15
大家好。 我现在实现一个function。 在function中, 先插入2个临时表数据, 然后根据这2个临时表数据查询得到结果, 返回出去。 下面是代码。

--创建 session有效的表
create global temporary table Temp_Stat_Result(
qujian_id number,
min_v number,
max_v number
) ON COMMIT PRESERVE ROWS ;


--创建 session有效的表
create global temporary table temp_ACCESSs(
access_amount number,
user_name VARCHAR2(100)
) ON COMMIT PRESERVE ROWS ;


使用临时表的原因是, 因为 每个用户操作的数据, 要求相互不能影响。
--function
create or replace function Stat_Result_FUC(P_ROWCOUNT IN NUMBER,
P_BEGINDATE in date,
P_ENDDATE in date,
P_ROWSIZE IN NUMBER) return Stat_Result_INT is
pragma autonomous_transaction;
Result Stat_Result_INT;
bucket integer;

begin

--execute immediate 'truncate table Temp_Stat_Result';
--execute immediate 'truncate table temp_ACCESSs';

--begin fromdate end enddate
insert into temp_ACCESSs
(select sum(access_amount) as access_amount, user_name
from OTA_ACCESS_calendar_AMOUNT
where OPERATION_DATE >= TRUNC(P_BEGINDATE)
and OPERATION_DATE <= TRUNC(P_ENDDATE)
group by user_name);

--qujian maxsize
select ceil(trunc(max(access_amount)/P_ROWSIZE)/P_ROWCOUNT) into bucket
from temp_ACCESSs;

dbms_output.put_line(bucket) ;

--for i in 1..bucket loop
for i in 1 .. bucket loop
insert into Temp_Stat_Result(qujian_id,min_v,max_v)
values( i,(i-1)*P_ROWCOUNT, i*P_ROWCOUNT);
end loop;

select Stat_Result(a.qujian_id , count(b.user_name)) bulk collect into Result
from Temp_Stat_Result a,temp_ACCESSs b
where trunc(b.access_amount/P_ROWSIZE)>a.min_v
and trunc(b.access_amount/P_ROWSIZE) <=a.max_v
group by qujian_id;
commit;
return(Result);
end Stat_Result_FUC;

--object
create type Stat_Result as object(
pid int,
pcount int
)

--object interface
create type Stat_Result_INT is table of Stat_Result;

----------------
然后在command中执行 select pid, pcount from table(Stat_Result_FUC(10,sysdate - 10, sysdate -1, 1048576));
发现,每次的结果不一样。(按道理应该是一样才对)。


原因是: function中, 插入2次表, 然后根据表查询得到结果返回。 我现在发现, 应该是返回了数据, 临时表的数据没有清楚, 不知道是commit 没有以作用呢 还是 function 的session 一直没有关闭。(估计是一直没有关闭)。 因为根据 ON COMMIT PRESERVE ROWS ;( 我在存储过程中, 用游标返回以上的结果,临时表是起作用的, 现在就是游标太慢, 且不知道function 怎么使用游标, 才没有用。)

大家给我好的答案吧。

附上我用游标的 存储过程例子。


create or replace procedure OTA_ACCESS_CALENDAR_STAT(P_ROWCOUNT IN NUMBER,
P_BEGINDATE in date,
P_ENDDATE in date,
P_CALENDARRESULT OUT SYS_REFCURSOR,
P_ROWSIZE IN NUMBER) AS
v_rowcount INTEGER;
v_maxcount INTEGER;
v_for INTEGER;
i INTEGER;
bg INTEGER;
ed INTEGER;
v_usercount INTEGER;
v_countsize INTEGER;
str varchar2(10000);

BEGIN

insert into temp_ACCESS_CALENDAR_SUM
(select sum(access_amount) as access_amount, user_name
from OTA_ACCESS_calendar_AMOUNT
where OPERATION_DATE >= TRUNC(P_BEGINDATE)
and OPERATION_DATE <= TRUNC(P_ENDDATE)
group by user_name);

select max(access_amount) into v_maxcount from temp_ACCESS_CALENDAR_SUM;

--按照 k来分类
v_rowcount := P_ROWCOUNT * P_ROWSIZE;
bg := 0;
ed := 0;
v_for := Trunc(v_maxcount / v_rowcount);
if Mod(v_maxcount, v_rowcount) <> 0 then
v_for := v_for + 1;
end if;

if v_for = 0 then
v_for :=1;
end if;

for i in 0 .. v_for - 1 loop
bg := i * v_rowcount + 1;
if bg = 1 then
bg := 0;

end if;
ed := (i + 1) * v_rowcount;

insert into temp_ACCESS_CALENDAR_STAT
(select ed as count_size, count(user_name) as user_count
from temp_ACCESS_CALENDAR_SUM
where access_amount >= bg
and access_amount <= ed);

end loop;

commit;

OPEN P_CALENDARRESULT FOR
SELECT * FROM temp_ACCESS_CALENDAR_STAT;

END;
/
表结构 就不看了, 因为能够执行, 就是效率慢了点。
...全文
396 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
jenry_leon 2008-10-20
  • 打赏
  • 举报
回复
这个我也想到了。 try 后, 发现结果是 空的。

由此, 我想到,在fuction中, 怎么做到事物控制。
hebo2005 2008-10-20
  • 打赏
  • 举报
回复
你这样是会话级别的临时表,要SESSION断了才会清除,可以用事务级别的
ON COMMIT DELETE ROWS
这样一commit就会清除
hebo2005 2008-10-20
  • 打赏
  • 举报
回复
你建临时表时用
ON COMMIT DELETE ROWS试试
hebo2005 2008-10-20
  • 打赏
  • 举报
回复
我仔细看一下
jenry_leon 2008-10-20
  • 打赏
  • 举报
回复
这个我也知道 所以是
select Stat_Result(a.qujian_id , count(b.user_name)) bulk collect into Result
from Temp_Stat_Result a,temp_ACCESSs b
where trunc(b.access_amount/P_ROWSIZE)>a.min_v
and trunc(b.access_amount/P_ROWSIZE) <=a.max_v
group by qujian_id;
commit;

但是运行2次, 结果不一样。 是第一次的 累加。 这个就不知道是什么原因了。

确认, 数据都是历史数据, 不会增加。
hebo2005 2008-10-20
  • 打赏
  • 举报
回复
临时表,一COMMIT就清数据的
jenry_leon 2008-10-20
  • 打赏
  • 举报
回复
请大家指点, function中临时表问题。 或者有更好的解决方案。 请大家提供。
jenry_leon 2008-10-20
  • 打赏
  • 举报
回复
请大家指点, function中临时表问题。 或者有更好的解决方案。 请大家提供。

17,140

社区成员

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

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