在存储过程中使用临时表

[Marcoseu]

如何在存储过程中使用临时表？
存储过程中需要使用临时表缓存若干查询数据，以用于和其它用户表的联合查询、操作
采用动态SQL可以创建出临时表，但不能在程序其它部分进行显式SQL操作
请问有何解决方案？

[Marcoseu]

各位，请看清我的问题
问题是使用动态SQL 不能显式操作新创建的表（因为动态执行，在编译期无法访问，编译报错）
使用collection也是一种方法，但只能绑定一列数据
不知还有什么更好的方法？

[beckhambobo]

create procedure pro(p_table in varchar2)
as
num number;
str varchar2(50);
begin
select count(1) inot num from user_tables where table_name=upper(p_table);
if num=0 then
str:='create table '||p_table||'....';
execute immediate str;
else
str:='drop table '||p_table;
execute immediate str;
end if;
str:='insert into '||p_table||' (...) values(...);
execute immediate str;
end;
/

[jiezhi]

就像使用固定表那樣使用。
動態sql。
sql:='select * from '||tableName||' where ....';
execute immediate sql;

[bzszp]

其实使用临时表和固定表没有多大区别
只是临时表可以自动清空数据
完全可以用固定表来替代

[ThomasLin]

建表时先一并建好临时表 在存储过程中直接使用

[l2g32003]

create or replace procedure lg_p
authid current_user // 调用者权限
as
begin
execute immediate 'create table t (n number)';
execute immediate 'insert into t values(1)'; // insert 进同一过程创建的表
end;
/

[JCC0128]

建表时先一并建好临时表 在存储过程中直接使用
---------

完全同意!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[JCC0128]

假设你创建临时表a ,实际上是每个session都存在一个一个临时表a,每个session下a的数据不一样(甚至结构不一样)

它的缺陷也很清楚了，建一个临时表a，实际上每个session都有一个临时表，相当于建了n个临时表,如果session多了，a大了，哈哈，数据库就奇慢无比了。我就在复杂报表里面用临时表

[JCC0128]

//WhHanRep_LineAuthenStat 为临时表，在创建包之前必须创建好，创建语句如下：

'Create global temporary table WhHanRep_LineAuthenStat
(序号 varchar2(20)
,线路编号 varchar2(100)
,线路名称 varchar2(100)
,起始开关编号 varchar2(100)
,终止开关编号 varchar2(100)
,电压等级 varchar2(50)
,线路长度 varchar2(50)
,评定等级 varchar2(50)
,存在问题及措施 varchar2(500)
)
ON COMMIT preserve ROWS ;
----------------------------------


CREATE OR REPLACE PACKAGE BODY "KXD"."PKG_WHREP_LINE_AUTHEN_STAT"
as
PROCEDURE GET_WHREP_MATE_AUTHEN_STAT( p_DTBegin varchar2 , P_DTEnd varchar2 ,
p_RCTYPE OUT RCTYPE_WHREP_MATE_AUTHEN_STAT)
IS
sqlstr varchar2(4000);
cursor cur1 is select MATE_LINE_CODE AS "线路编号" from MATE_LINE; --配电线路游标
cursor cur2 is select LINE_CODE AS "线路编号" from LINE; --输电线路游标
LineCode varchar(50);
Level varchar2(50) ; --评定等级
Level3Count number(10,0) ;--线路上三级设备个数
Level2Count number(10,0);--线路上二级设备个数
Level1Count number(10,0);--线路上一级设备个数
BEGIN
/*
if KXD.CHECK_TEMPTBL_Exist('KXD','WhHanRep_LineAuthenStat')<>0 then
sqlstr := 'TRUNCATE table WhHanRep_LineAuthenStat';
execute immediate sqlstr ;
end if ;
if KXD.CHECK_TEMPTBL_Exist('KXD','WhHanRep_LineAuthenStat')=0 then
sqlstr :='Create global temporary table WhHanRep_LineAuthenStat
(序号 varchar2(20)
,线路编号 varchar2(100)
,线路名称 varchar2(100)
,起始开关编号 varchar2(100)
,终止开关编号 varchar2(100)
,电压等级 varchar2(50)
,线路长度 varchar2(50)
,评定等级 varchar2(50)
,存在问题及措施 varchar2(500)
)
ON COMMIT preserve ROWS
';
execute immediate sqlstr ;
end if ;
*/
delete from WhHanRep_LineAuthenStat ;
insert into WhHanRep_LineAuthenStat
SELECT
ROWNUM AS "序号", b.* from
(select
A.MATE_LINE_CODE AS "线路编号",
A.LINE_NAME AS "线路名称",
A.START_SWITCH AS "起始开关编号",
A.END_SWITCH AS "终止开关编号",
A.LINE_VOL_LEVEL "电压等级",
A.LINE_LENGTH AS "线路长度",
null AS "评定等级",
'存在内容' AS "存在问题及措施"
FROM MATE_LINE a order by a.MATE_LINE_CODE) b
;

open cur1;
fetch cur1 into LineCode;--into ...;
while cur1%found loop


--求线路编号的等级
--三级设备的个数
select Count(*)
into Level3Count
from MATE_BUG_REC
where trim(LINE_NAME) = trim(LineCode)
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') >= TO_DATE(p_DTBegin,'YYYY-MM-DD')
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') <= TO_DATE(p_DTEnd,'YYYY-MM-DD')
and BUG_LEVENT = 'Ⅲ' ;
--二级设备的个数

select Count(*)
into Level2Count
from MATE_BUG_REC
where trim(LINE_NAME) = trim(LineCode)
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') >= TO_DATE(p_DTBegin,'YYYY-MM-DD')
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') <= TO_DATE(p_DTEnd,'YYYY-MM-DD')
and BUG_LEVENT = 'Ⅱ';
--一级设备的个数
select Count(*)
into Level3Count
from MATE_BUG_REC
where trim(LINE_NAME) = trim(LineCode)
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') >= TO_DATE(p_DTBegin,'YYYY-MM-DD')
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') <= TO_DATE(p_DTEnd,'YYYY-MM-DD')
and BUG_LEVENT = 'Ⅰ' ;

--求线路编号的等级
select decode (sign(Level3Count), 1,'Ⅲ',
decode (sign(Level3Count), 1,'Ⅱ','Ⅰ'
)
)
into level from dual;

--刷新等级
update WhHanRep_LineAuthenStat set 评定等级=level
where 线路编号=LineCode ;
fetch cur1 into LineCode ;--into ...;
end loop;
close cur1 ;

---------------------------------------------
--以下为配电路的数据

insert into WhHanRep_LineAuthenStat
SELECT
ROWNUM AS "序号", b.* from
(select
A.LINE_CODE AS "线路编号",
A.LINE_NAME AS "线路名称",
A.START_SWITCH AS "起始开关编号",
A.END_SWITCH AS "终止开关编号",
A.LINE_VOL_LEVEL "电压等级",
A.LINE_LENGTH AS "线路长度",
null AS "评定等级",
'存在内容' AS "存在问题及措施"
FROM LINE a order by a.LINE_CODE) b
;



open cur2;
fetch cur2 into LineCode;--into ...;
while cur2%found loop


--求线路编号的等级
--三级设备的个数
select Count(*)
into Level3Count
from SEND_BUG_REC
where trim(LINE_NAME) = trim(LineCode)
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') >= TO_DATE(p_DTBegin,'YYYY-MM-DD')
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') <= TO_DATE(p_DTEnd,'YYYY-MM-DD')
and BUG_LEVENT = 'Ⅲ' ;
--二级设备的个数

select Count(*)
into Level2Count
from SEND_BUG_REC
where trim(LINE_NAME) = trim(LineCode)
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') >= TO_DATE(p_DTBegin,'YYYY-MM-DD')
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') <= TO_DATE(p_DTEnd,'YYYY-MM-DD')
and BUG_LEVENT = 'Ⅱ';
--一级设备的个数
select Count(*)
into Level3Count
from SEND_BUG_REC
where trim(LINE_NAME) = trim(LineCode)
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') >= TO_DATE(p_DTBegin,'YYYY-MM-DD')
and TO_DATE(BUG_FIND_DATE,'YYYY-MM-DD') <= TO_DATE(p_DTEnd,'YYYY-MM-DD')
and BUG_LEVENT = 'Ⅰ' ;

--求线路编号的等级
select decode (sign(Level3Count), 1,'Ⅲ',
decode (sign(Level3Count), 1,'Ⅱ','Ⅰ'
)
)
into level from dual;

--刷新等级
update WhHanRep_LineAuthenStat set 评定等级=level
where 线路编号=LineCode ;

fetch cur2 into LineCode ;--into ...;
end loop;
close cur2 ;




------------返回结果
OPEN p_RCTYPE FOR
select WhHanRep_LineAuthenStat.* from WhHanRep_LineAuthenStat ;
END;

end ;