Oracle数据库的临时表的创建:
1.会话级级临时表的创建:临时表的数据存在于会话的生命周期中,会话结束,临时表中的数据会自动清除。
drop table TMP_#ZSJJCCX_KHDA;
CREATE GLOBAL Temporary Table TMP_#ZSJJCCX_KHDA
(
KHNBID VARCHAR2(16),
QYRQ_KH NUMBER(8),
JLDJZMJ NUMBER(14,2),
JLDTNMJ NUMBER(14,2),
JLDJSGNMJ NUMBER(14,2),
JLDJSWYMJ NUMBER(14,2),
RZBZ NUMBER(2)
)
ON COMMIT PRESERVE ROWS;
2.事务级临时表的创建:临时表的数据存在于事务的生命周期中。
drop table TMP_#ZSJJCCX_KHDA;
CREATE GLOBAL Temporary Table TMP_#ZSJJCCX_KHDA
(
KHNBID VARCHAR2(16),
QYRQ_KH NUMBER(8),
JLDJZMJ NUMBER(14,2),
JLDTNMJ NUMBER(14,2),
JLDJSGNMJ NUMBER(14,2),
JLDJSWYMJ NUMBER(14,2),
RZBZ NUMBER(2)
)
ON COMMIT DELETE ROWS;
create index TMP_#ZSJJCCX_KHDA_001 on TMP_#ZSJJCCX_KHDA (khnbid, qyrq_kh);
-- 增加表描述
comment on table TMP_#ZSJJCCX_KHDA is '事务级临时表_主数据基础查询_客户档案';
-- 增加字段说明
comment on column TMP_#ZSJJCCX_KHDA.KHNBID is '客户内部ID';
comment on column TMP_#ZSJJCCX_KHDA.QYRQ_KH is '启用日期_客户';
comment on column TMP_#ZSJJCCX_KHDA.JLDJZMJ is '计量点建筑面积';
comment on column TMP_#ZSJJCCX_KHDA.JLDTNMJ is '计量点套内面积';
comment on column TMP_#ZSJJCCX_KHDA.JLDJSGNMJ is '计量点计算供暖面积';
comment on column TMP_#ZSJJCCX_KHDA.JLDJSWYMJ is '序号_降序';
comment on column TMP_#ZSJJCCX_KHDA.RZBZ is '入住标志';