存储过程中创建临时表的注意事项和demo在ORACLE存储过程中创建临时表存储过程里不能直接使用DDL语句，所以只能使用动态SQL语句来执行--ONCOMMITDELETEROWS说明临时表是事务指定，每次提交后ORACLE将截断...SyntaxHighlighter.all(
存储过程中创建临时表的注意事项和demo
在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:=&＃39;drop table SETT_DAILYTEST&＃39;;
execute immediate str;
str:=&＃39;CREATE GLOBAL TEMPORARY TABLE SETT_DAILYTEST (
NACCOUNTID NUMBER not null,
NSUBACCOUNTID NUMBER not null)
ON COMMIT PRESERVE ROWS&＃39;;
execute immediate str; ----使用动态SQL语句来执行
str:=&＃39;insert into SETT_DAILYTEST (select naccountid,nsubaccountid from sett_dailyaccountbalance)&＃39;;
execute immediate str;
END temptest;
上面建立一个临时表的存储过程
www.2cto.com
下面是执行一些操作,向临时表写数据。
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(&＃39;日期不能为空&＃39;);
ELSE www.2cto.com
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;
www.2cto.com
需要创建一个临时表，请举例说明，谢谢！
---------------------------------------------------------------
是TEMPORARY
CREATE GLOBAL TEMPORARY TABLE flight_schedule (
startdate DATE,
enddate DATE,
cost NUMBER)
---------------------------------------------------------------
create proecdure name_pro
as
str varchar2(100);
begin
str:=&＃39;CREATE GLOBAL TEMPORARY TABLE TABLENAME ON COMMIT PRESERVE ROWS as select * from others_table&＃39;;
execute immediate str;
end;
/
可以把临时表指定为事务相关（默认）或者是会话相关：
ON COMMIT DELETE ROWS：指定临时表是事务相关的，Oracle在每次提交后截断表。
ON COMMIT PRESERVE ROWS：指定临时表是会话相关的，Oracle在会话中止后截断表。
=================
可以创建以下两种临时表：
1。会话特有的临时表
CREATE GLOBAL TEMPORARY
() ON COMMIT PRESERVE ROWS；
======== www.2cto.com
对全局临时表的总结
在临时表上的操作比在一般的表上的操作要快。因为：
1创建临时表不需要往编目表中插入条目，临时表的使用也不需要访问编目表，因此也没有对编目表的争用。
2仅有创建临时表的app才可存取临时表，所以在处理临时表时没有锁。
3如果指定NOT LOGGED选项，在处理临时表时不记日志。所以如果有仅在
数据库的一个会话中使用的大量临时数据，把这些数据存入临时表能大大提高性能。
DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20))；
在CONNECT RESET命令后，临时表不再存在。
建临时表是动态编译的，所以对临时表的使用也必须放在DECLARE CURSER 后面
CREATE PROCEDURE INSTT2(P1 INT, P2 CHAR(20))
BEGIN
DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20)) %
INSERT INTO SESSION.TT VALUES(P1, P2);
BEGIN
DECLARE C1 CURSOR WITH RETURN FOR SELECT * FROM SESSION.TT;
END;
END %
2。事务特有的临时表
CREATE GLOBAL TEMPORARY
() ON COMMIT DELETE ROWS；
在
Oracle中，全局临时表并不会删除，实际上你只需要建立一次，以后直接应用就行了，这与MS和
Sybase不一样。实际上在断开数据库连接时，临时表中数据自动清空，不同的Session之间是隔离的，不许要当心相互影响，不过如果起用了连接共享的话，你要用On Commit delete rows使数据仅在事物内部有效。
www.2cto.com
3建立临时表
临时表的定义对所有会话SESSION都是可见的,但是表中的数据只对当前的会话或者事务有效.
建立方法:
1) ON COMMIT DELETE ROWS 定义了建立事务级临时表的方法.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
EXAMPLE:
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
2 (startdate DATE,
3 enddate DATE,
4 class CHAR(20))
5 ON COMMIT DELETE ROWS;
SQL> create table permernate( a number);
SQL> insert into admin_work_area values(sysdate,sysdate,&＃39;temperary table&＃39;);
SQL> insert into permernate values(1);
SQL> commit;
SQL> select * from admin_work_area;
SQL> select * from permernate;
A
1
2)ON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法.
CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT PRESERVE ROWS;
EXAMPLE:
www.2cto.com
会话1:
SQL> drop table admin_work_area;
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
2 (startdate DATE,
3 enddate DATE,
4 class CHAR(20))
5 ON COMMIT PRESERVE ROWS;
SQL> insert into permernate values(2);
SQL> insert into admin_work_area values(sysdate,sysdate,&＃39;session temperary&＃39;);
SQL> commit;
SQL> select * from permernate;
A
----------
1
2
SQL> select * from admin_work_area;
STARTDATE ENDDATE CLASS
---------- ---------- --------------------
17-1ÔÂ -03 17-1ÔÂ -03 session temperary
www.2cto.com
会话2:
SQL> select * from permernate;
A
----------
1
2
SQL> select * from admin_work_area;
未选择行.
会话2看不见会话1中临时表的数据.
作者 赵莫言
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有