在ORACLE存储过程中创建临时表

在ORACLE存储过程中创建临时表 

存储过程里不能直接使用DDL语句，所以只能使用动态SQL语句来执行

--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行） 
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。

CREATE OR REPLACE PROCEDURE temptest 
(p_searchDate IN DATE) 
IS 
v_count INT; 
str varchar2(300); 
BEGIN 
v_count := 0; 
str:='drop table SETT_DAILYTEST'; 
execute immediate str; 
str:='CREATE GLOBAL TEMPORARY TABLE SETT_DAILYTEST ( 
NACCOUNTID NUMBER not null, 
NSUBACCOUNTID NUMBER not null) 
ON COMMIT PRESERVE ROWS'; 
execute immediate str; ----使用动态SQL语句来执行 
str:='insert into SETT_DAILYTEST (select naccountid,nsubaccountid from sett_dailyaccountbalance)'; 
execute immediate str; 
END temptest;

上面建立一个临时表的存储过程

下面是执行一些操作,向临时表写数据。

CREATE OR REPLACE PROCEDURE PR_DAILYCHECK 
( 
p_Date IN DATE, 
p_Office IN INTEGER, 
p_Currency IN INTEGER, 
P_Check IN INTEGER, 
p_countNum OUT INTEGER) 
IS 
v_count INT; 
BEGIN 
v_count := 0; 
IF p_Date IS NULL THEN 
dbms_output.put_line('日期不能为空'); 
ELSE 
IF P_Check = 1 THEN 
insert into SETT_DAILYTEST (select naccountid,nsubaccountid from sett_dailyaccountbalance 
where dtdate = p_Date); 
select 
count(sd.naccountid) into v_count 
from sett_subaccount ss,sett_account sa,sett_dailytest sd 
where sd.naccountid = sa.id and sd.nsubaccountid = ss.id and sa.id = ss.naccountid 
AND sa.nofficeid = p_Office AND sa.ncurrencyid = p_Currency 
and rownum < 2; 
COMMIT; 
p_countNum := v_count; 
dbms_output.put_line(p_countNum); 
END IF; 
IF P_Check = 2 THEN 
insert into SETT_DAILYTEST (select naccountid,nsubaccountid from sett_dailyaccountbalance 
where dtdate = p_Date); 
select 
count(sd.naccountid) into v_count 
from sett_cfsubaccount ss,sett_account sa,sett_dailytest sd 
where sd.naccountid = sa.id and sd.nsubaccountid = ss.id and sa.id = ss.naccountid 
AND sa.nofficeid = p_Office AND sa.ncurrencyid = p_Currency 
and rownum < 2; 
COMMIT; 
p_countNum := v_count; 
dbms_output.put_line(p_countNum); 
END IF; 
END IF; 
END PR_DAILYCHECK;