步骤
*第一步--》建立临时表
--hyy FLDEX14-
CREATE TABLE tmp_hy(FLDEX2 VARCHAR2(32),FLDITYPE VARCHAR2(32),FLDEX14 varchar2(32),FLDEX13 VARCHAR2(32),FLDEX17 VARCHAR2(32),
FLD2GOPENDATE VARCHAR2(32),FLDEX18 VARCHAR2(32),FLDEX19 VARCHAR2(32),FLD3GOPENDATE VARCHAR2(32),FLDEX20 VARCHAR2(32));
-- 楼宇编号 楼宇类别1 对应OA表站号 对应OA表站名 2G室分是否开通 2G室分开通时间
--2G覆盖入网情况 3G室分是否开通 3G室分开通时间 3G覆盖入网情况
*第二步--》导入excel 数据、
select * from tmp_hy for update;
*第三步--》翻译字典项
--2G室分是否开通
UPDATE TMP_HY T SET T.FLDEX17 = CASE T.FLDEX17 WHEN '是' THEN '1' WHEN '否' THEN '2' END ;
--3G室分是否开通
UPDATE TMP_HY T SET T.FLDEX19 = CASE T.FLDEX19 WHEN '是' THEN '1' WHEN '否' THEN '2' END ;
--2G覆盖入网情况
UPDATE TMP_HY T SET T.FLDEX18 = CASE T.FLDEX18 WHEN '开通' THEN '1' WHEN'未开通' THEN '2' END ;
--3G覆盖入网情况
UPDATE TMP_HY T SET T.FLDEX20 = CASE T.FLDEX20 WHEN '开通' THEN '1' WHEN'未开通' THEN '2' END ;
--2G室分开通时间
UPDATE TMP_HY T SET T.FLD2GOPENDATE =
CASE T.FLD2GOPENDATE WHEN '2009' THEN '59' WHEN '2010' THEN '60' WHEN '2011' THEN '61' END ;
--3G室分开通时间
UPDATE TMP_HY T SET T.FLD3GOPENDATE =
CASE T.FLD3GOPENDATE WHEN '2009' THEN '59' WHEN '2010' THEN '60' WHEN '2011' THEN '61' END ;
*第四步--》更新数据
UPDATE TAR_STATION T
SET (T.FLDEX14,
T.FLDEX13,
T.FLDEX17,
T.FLD2GOPENDATE,
T.FLDEX18,
T.FLDEX19,
T.FLD3GOPENDATE,
T.FLDEX20,
FLDITYPE) =
(SELECT TRIM(T1.FLDEX14),
TRIM(T1.FLDEX13),
T1.FLDEX17,
T1.FLD2GOPENDATE,
T1.FLDEX18,
T1.FLDEX19,
T1.FLD3GOPENDATE,
T1.FLDEX20,
FLDITYPE
FROM TMP_HY T1
WHERE T.FLDEX2 = TRIM(T1.FLDEX2) AND ROWNUM<2)
WHERE EXISTS (SELECT 1 FROM TMP_HY T1 WHERE TRIM(T1.FLDEX2)=T.FLDEX2 );
=====================================================================================
查询语句
# 临时表
select * from TMP_HY;
#楼宇表
SELECT * FROM TAR_STATION;
#其他数据
select * from tsm_code t WHERE T.FLDITYPEID = '5001' ORDER BY T.FLDDICTORDER ASC;
select * from tmp_code;
select * from tsm_entity_config;
delete from tmp_hy;