Oracle临时表应用
Oracle 里面创建临时表及其应用
--创建临时表
在Oracle8i或以上版本中，可以创建以下两种临时表：
1。会话特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT PRESERVE ROWS；
2。事务特有的临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specification>)
ON COMMIT DELETE ROWS；
CREATE GLOBAL TEMPORARY TABLE MyTempTable
所建的临时表虽然是存在的，但是你试一下insert 一条记录然后用别的连接登上去select，记录是空的，明白了吧，我把下面两句话再贴一下：
--ON COMMIT DELETE ROWS 说明临时表是事务指定，每次提交后ORACLE将截断表（删除全部行）
--ON COMMIT PRESERVE ROWS 说明临时表是会话指定，当中断会话时ORACLE将截断表。
冲突的问题更本不用考虑.
临时表只是保存当前会话(session)用到的数据，数据只在事务或会话期间存在。
通过CREATE GLOBAL TEMPORARY TABLE命令创建一个临时表，对于事务类型的临时表，
数据只是在事务期间存在，对于会话类型的临时表，数据在会话期间存在。
会话的数据对于当前会话私有。每个会话只能看到并修改自己的数据。DML锁不会加到
临时表的数据上。下面的语句控制行的存在性。
● ON COMMIT DELETE ROWS 表名行只是在事务期间可见
● ON COMMIT PRESERVE ROWS 表名行在整个会话期间可见
可以对临时表创建索引，视图，出发器，可以用export和import工具导入导出表的
定义，但是不能导出数据。表的定义对所有的会话可见。
Temporary Tables临时表
1简介
ORACLE数据库除了可以保存永久表外，还可以建立临时表temporary tables。这些临时表用来保存一个会话SESSION的数据，
或者保存在一个事务中需要的数据。当会话退出或者用户提交commit和回滚rollback事务的时候，临时表的数据自动清空，
但是临时表的结构以及元数据还存储在用户的数据字典中。
临时表只在oracle8i以及以上产品中支持。
2详细介绍
Oracle临时表分为 会话级临时表 和 事务级临时表。
会话级临时表是指临时表中的数据只在会话生命周期之中存在，当用户退出会话结束的时候，Oracle自动清除临时表中数据。
事务级临时表是指临时表中的数据只在事务生命周期中存在。当一个事务结束（commit or rollback），Oracle自动清除临时表中数据。
临时表中的数据只对当前Session有效，每个Session都有自己的临时数据，并且不能访问其它Session的临时表中的数据。因此，
临时表不需要DML锁.当一个会话结束(用户正常退出 用户不正常退出 ORACLE实例崩溃)或者一个事务结束的时候，Oracle对这个会话的
表执行 TRUNCATE 语句清空临时表数据.但不会清空其它会话临时表中的数据.
你可以索引临时表和在临时表基础上建立视图.同样,建立在临时表上的索引也是临时的,也是只对当前会话或者事务有效.
临时表可以拥有触发器.
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,'temperary table');
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:
会话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,'session temperary');
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
会话2:
SQL> select * from permernate;
A
----------
1
2
SQL> select * from admin_work_area;
未选择行.
会话2看不见会话1中临时表的数据.
工作样例：
--临时表
-- Create table
create global temporary table TEMP_TBL
(
MAIN_CONTRACT_ID VARCHAR2(10),
SUB_CONTRACT_ID VARCHAR2(10),
MAIN_CONTRACT_NAME VARCHAR2(100),
SUB_CONTRACT_NAME VARCHAR2(100),
FEE_NAME VARCHAR2(20),
MONTH_FEE VARCHAR2(20),
YEAR_FEE VARCHAR2(20)
)
on commit preserve rows;
-- Add comments to the columns
comment on column TEMP_TBL.MAIN_CONTRACT_ID
is '大合同编号';
comment on column TEMP_TBL.SUB_CONTRACT_ID
is '子合同编号';
comment on column TEMP_TBL.MAIN_CONTRACT_NAME
is '大合同名称';
comment on column TEMP_TBL.SUB_CONTRACT_NAME
is '子合同名称';
comment on column TEMP_TBL.FEE_NAME
is '费用名称';
comment on column TEMP_TBL.MONTH_FEE
is '本月费用';
comment on column TEMP_TBL.YEAR_FEE
is '年度累计';
--这里temporary 表示临时表
创建插入存储过程
CREATE OR REPLACE PROCEDURE Create_resultTable_V1_Insert(
p_MAIN_CONTRACT_ID IN VARCHAR2,
p_SUB_CONTRACT_ID IN VARCHAR2,
p_MAIN_CONTRACT_NAME IN VARCHAR2,
p_SUB_CONTRACT_NAME IN VARCHAR2,
p_FEE_NAME IN VARCHAR2,
p_MONTH_FEE IN VARCHAR2,
p_YEAR_FEE IN VARCHAR2
)
IS
BEGIN
insert into TEMP_TBL
(MAIN_CONTRACT_ID,
SUB_CONTRACT_ID,
MAIN_CONTRACT_NAME,
SUB_CONTRACT_NAME,
FEE_NAME,
MONTH_FEE,
YEAR_FEE)
values
(p_MAIN_CONTRACT_ID,
p_SUB_CONTRACT_ID,
p_MAIN_CONTRACT_NAME,
p_SUB_CONTRACT_NAME,
p_FEE_NAME,
p_MONTH_FEE,
p_YEAR_FEE);
END Create_resultTable_V1_Insert;
--存储过程调用插入存储过程<>
CREATE OR REPLACE PROCEDURE Create_resultTable_V1(
-- cur_result OUT contract_statistics.Getresult_Table,
p_contractID IN VARCHAR2, --合同编号
p_date_district in VARCHAR2) IS
p_number NUMBER;
p_contractType VARCHAR2(10); --合同类型
p_charge1 NUMBER(12, 2); --费用临时变量<月统计>
p_monthday_number NUMBER; --月统计的有效天数
p_yearday_number NUMBER; --年统计的有效天数
--p_charge2 NUMBER(12, 2); --费用临时变量<年统计>
--p_TERM_VALIDITY DATE; --合同有效开始日期
--p_TERM_VALIDITY_END DATE; --合同有效结束日期
CURSOR CUR_CAL IS
select *
from STOREHOUSE_LEASEHOLD_CONTRACT s
where s.term_validity < to_date(p_date_district, 'yyyy-MM')
and s.term_validity_end > to_date(p_date_district, 'yyyy-MM')
and (s.contract_type = 'a' or s.contract_type = 'b')
and ((s.autoid =
(select nvl(s.master_id, p_contractID)
from STOREHOUSE_LEASEHOLD_CONTRACT s
where (s.contract_type = 'a' or s.contract_type = 'b')
and ((s.autoid = p_contractID))) or
s.master_id =
(select nvl(s.master_id, p_contractID)
from STOREHOUSE_LEASEHOLD_CONTRACT s
where (s.contract_type = 'a' or s.contract_type = 'b')
and ((s.autoid = p_contractID)))));
CAL CUR_CAL%ROWTYPE;
BEGIN
delete from TEMP_TBL;
--判断当前选择月份的天数
--当月的有效天数 p_monthday_number:= 这里的天数可能是一个完整的月份 ，
--也可能是合同有效期的最后一个月
p_monthday_number := last_day(to_date(p_date_district, 'yyyy-MM')) -
to_date(p_date_district, 'yyyy-MM') + 1;
--
-- p_yearday_number:= last_day(to_date(p_date_district, 'yyyy-MM')) -
--to_date(p_date_district, 'yyyy') + 1;
p_yearday_number := last_day(to_date(p_date_district, 'yyyy-MM')) -
to_date(SUBSTR(p_date_district, 1, 4) || '-01-01',
'yyyy-MM-dd') + 1;
OPEN CUR_CAL;
LOOP
FETCH CUR_CAL
INTO CAL;
EXIT WHEN CUR_CAL%NOTFOUND;
CASE
WHEN CAL.CONTRACT_TYPE = 'a' THEN
p_contractType := '仓库合同';
WHEN CAL.CONTRACT_TYPE = 'b' THEN
p_contractType := '房屋合同';
END CASE;
--合同有效天数
p_number := abs(CAL.Term_Validity_End - CAL.Term_Validity + 1);
-- 当月的有效天数 ，可能不是当月的有效天数 ，因为合同有效期可能在当月的某一天结束
--例如查询2008 03 的 合同结束日期是2008 3-10
if (to_char(CAL.Term_Validity_End, 'yyyy-MM') = p_date_district) then
--Extract(MONTH from to_date('2008-11','yyyy-MM'))
p_monthday_number := CAL.Term_Validity_End -
to_date(p_date_district || '-01', 'yyyy-MM-dd') + 1;
--累积年份的天数
p_yearday_number := CAL.Term_Validity_End -
to_date(SUBSTR(p_date_district, 1, 4) || '-01-01',
'yyyy-MM-dd') + 1;
end if;
--例如查询2007 12 的 合同结束开始是2007-11-10
--例如查询2007 11月 合同开始日是2007-11-10
/*
if (to_char(CAL.Term_Validity, 'yyyy-MM') = p_date_district) then
--p_yearday_number:=CAL.Term_Validity_End - to_date(SUBSTR(p_date_district,1,4)||'-01-01','yyyy-MM-dd')+1;
p_yearday_number:= last_day(to_date(p_date_district, 'yyyy-MM')) -
CAL.Term_Validity_End+1;
p_monthday_number := last_day(to_date(p_date_district, 'yyyy-MM'))-
CAL.Term_Validity_End + 1;
end if;
*/
if (to_char(CAL.Term_Validity, 'yyyy') = SUBSTR(p_date_district, 1, 4)) then
--p_yearday_number:=CAL.Term_Validity_End - to_date(SUBSTR(p_date_district,1,4)||'-01-01','yyyy-MM-dd')+1;
p_yearday_number := last_day(to_date(p_date_district, 'yyyy-MM')) -
CAL.Term_Validity + 1;
/*
p_monthday_number := last_day(to_date(p_date_district, 'yyyy-MM'))-
CAL.Term_Validity + 1;
*/
end if;
if (to_char(CAL.Term_Validity, 'yyyy-MM') = p_date_district) then
p_monthday_number := last_day(to_date(p_date_district, 'yyyy-MM')) -
CAL.Term_Validity + 1;
end if;
--合同有效天数
--获得年份 累积的天数
--p_yearday_number := p_monthday_number;
--+ add_months (to_date(p_date_district, 'yyyy-MM'),-1);
--p_monthday_number加上前面所有月份的天数
if (CAL.Master_Id is null) then
/*
按照费用名称进行循环插入 主合同 判断各种费用，进行循环新增 1 费用名称 2本月费用 3 年度费用
*/
--获得合同天数
if CAL.PROPERTY_MANAGE_FEE != 0 THEN
--每天的费用 合同总
p_charge1 := (CAL.PROPERTY_MANAGE_FEE / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'物业管理费',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if CAL.BROADBAND_FEE != 0 THEN
p_charge1 := (CAL.BROADBAND_FEE / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'宽带费',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if CAL.INFO_DATA_TRANSFER_FEE != 0 THEN
p_charge1 := (CAL.INFO_DATA_TRANSFER_FEE / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'信息数据传输费',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
/*
if CAL.ELETRICITY_RATE!=0 THEN
Create_resultTable_V1_Insert(CAL.AUTOID,'',p_contractType,'','电费','本月费用', '年度费用');
end if;
if CAL.WATER_RATE!=0 THEN
Create_resultTable_V1_Insert(CAL.AUTOID,'',p_contractType,'','水费','本月费用', '年度费用');
end if;
*/
if (CAL.LEASE_AREA1 * CAL.STOREHOUSE_RENT1) != 0 THEN
p_charge1 := (CAL.LEASE_AREA1 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'面积1',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA2 * CAL.STOREHOUSE_RENT2) != 0 THEN
p_charge1 := (CAL.LEASE_AREA2 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'面积2',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA3 * CAL.STOREHOUSE_RENT3) != 0 THEN
p_charge1 := (CAL.LEASE_AREA3 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'面积3',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA4 * CAL.STOREHOUSE_RENT4) != 0 THEN
p_charge1 := (CAL.LEASE_AREA4 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'面积4',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA5 * CAL.STOREHOUSE_RENT5) != 0 THEN
p_charge1 := (CAL.LEASE_AREA5 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'面积5',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
--STOREHOUSE_LEASEHOLD_CONTRACT1
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT1) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT1 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'其它1',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT2) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT2 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'其它2',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT3) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT3 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'其它3',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT4) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT4 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'其它4',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT5) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT5 / p_number);
Create_resultTable_V1_Insert(CAL.AUTOID,
'',
p_contractType,
'',
'其它5',
p_charge1 * p_monthday_number, --每天的单价+ 天数
p_charge1 * p_yearday_number);
end if;
else
--子合同
if CAL.PROPERTY_MANAGE_FEE != 0 THEN
p_charge1 := (CAL.PROPERTY_MANAGE_FEE / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'物业管理费',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if CAL.BROADBAND_FEE != 0 THEN
p_charge1 := (CAL.BROADBAND_FEE / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'宽带费',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if CAL.INFO_DATA_TRANSFER_FEE != 0 THEN
p_charge1 := (CAL.INFO_DATA_TRANSFER_FEE / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'信息数据传输费',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
/*
if CAL.ELETRICITY_RATE!=0 THEN
Create_resultTable_V1_Insert('',CAL.AUTOID,'',p_contractType,'电费','本月费用', '年度费用');
end if;
if CAL.WATER_RATE!=0 THEN
Create_resultTable_V1_Insert('',CAL.AUTOID,'',p_contractType,'水费','本月费用', '年度费用');
end if;
*/
if (CAL.LEASE_AREA1 * CAL.STOREHOUSE_RENT1) != 0 THEN
p_charge1 := (CAL.LEASE_AREA1 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'面积1',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA2 * CAL.STOREHOUSE_RENT2) != 0 THEN
p_charge1 := (CAL.LEASE_AREA2 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'面积2',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA3 * CAL.STOREHOUSE_RENT3) != 0 THEN
p_charge1 := (CAL.LEASE_AREA3 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'面积3',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA4 * CAL.STOREHOUSE_RENT4) != 0 THEN
p_charge1 := (CAL.LEASE_AREA4 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'面积4',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.LEASE_AREA5 * CAL.STOREHOUSE_RENT5) != 0 THEN
p_charge1 := (CAL.LEASE_AREA5 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'面积5',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
--STOREHOUSE_LEASEHOLD_CONTRACT1
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT1) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT1 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'其它1',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT2) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT2 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'其它2',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT3) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT3 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'其它3',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT4) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT4 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'其它4',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
if (CAL.STOREHOUSE_LEASEHOLD_CONTRACT5) != 0 THEN
p_charge1 := (CAL.STOREHOUSE_LEASEHOLD_CONTRACT5 / p_number);
Create_resultTable_V1_Insert('',
CAL.AUTOID,
'',
p_contractType,
'其它5',
p_charge1 * p_monthday_number,
p_charge1 * p_yearday_number);
end if;
--Create_resultTable_V1_Insert('',CAL.AUTOID,'',p_contractType,'费用名称','本月费用','年度费用');
end if;
END LOOP;
--commit;
CLOSE CUR_CAL;
select count(*) into p_number from TEMP_TBL;
END Create_resultTable_V1;
浙公网安备 33010602011771号