|
|
那个ins_sth我一开始没有看清楚，上面澄清过了。
"复杂点了吧？还能有我这个程序快？"
等下就告诉你怎么做。这些脚本一旦创建就可反复使用，在ETL中效率就是生命！
"INSERT INTO tabname （什么来着？我忘了，或者，根本不知道） VALUES （天哪，我哪知道是什么呀）；
只有你舒服。
ret＝DAU_insert(&_DAU,buf);
舒服吗？"
前面的DAU的初试化呢？你总得知道给什么对象插入吧(表名忘了,对象名呢？) 你总得把数据和字段对应上把（字段名忘了？你敢随便起一个？）
你就把最后一步拿出来说事，这就有点耍赖了。
我不愿意把INSERT分成很多步骤，一个INSERT最好，什么都看得到，明明白白的。
下面是用外部表进行MERGE INTO的步骤，最后MERGE只用了0.5秒！
准备工作：
先确定有创建目录对象的权限：
grant create any directory to my_user_name;
以my_user_name创建一个目录映射到一个操作系统路径, 比方说我想把数据放在数据库的e:\share：
create directory TJRB as 'e:\share';
把文件TJ01.TXT拷贝至该目录。
创建外部表（该脚本可用SQLLDR根据控制文件生成）:
CREATE TABLE TJ01_EXT
(
"TJDATE" DATE,
"UNIT" VARCHAR2(8),
"TABNAME" VARCHAR2(8),
"FLG" NUMBER(3),
"DAT1" NUMBER(15),
"DAT2" NUMBER(15),
"DAT3" NUMBER(15),
"DAT4" NUMBER(15),
"DAT5" NUMBER(15),
"DAT6" NUMBER(15),
"DAT7" NUMBER(15),
"DAT8" NUMBER(15),
"DAT9" NUMBER(15),
"DAT10" NUMBER(15),
"DAT11" NUMBER(15),
"DAT12" NUMBER(15),
"DAT13" NUMBER(15),
"DAT14" NUMBER(15),
"DAT15" NUMBER(15),
"DAT16" NUMBER(15),
"DAT17" NUMBER(15),
"DAT18" NUMBER(15),
"DAT19" NUMBER(15),
"DAT20" NUMBER(15),
"DAT21" NUMBER(15),
"DAT22" NUMBER(15),
"DAT23" NUMBER(15),
"DAT24" NUMBER(15),
"DAT25" NUMBER(15),
"DAT26" NUMBER(15),
"DAT27" NUMBER(15),
"DAT28" NUMBER(15),
"DAT29" NUMBER(15),
"DAT30" NUMBER(15),
"DAT31" NUMBER(15),
"DAT32" NUMBER(15),
"DAT33" NUMBER(15),
"DAT34" NUMBER(15),
"DAT35" NUMBER(15),
"DAT36" NUMBER(15),
"DAT37" NUMBER(15),
"DAT38" NUMBER(15),
"DAT39" NUMBER(15),
"DAT40" NUMBER(15),
"DAT41" NUMBER(15),
"DAT42" NUMBER(15),
"DAT43" NUMBER(15),
"DAT44" NUMBER(15),
"DAT45" NUMBER(15),
"DAT46" NUMBER(15),
"DAT47" NUMBER(15),
"DAT48" NUMBER(15),
"DAT49" NUMBER(15),
"DAT50" NUMBER(15)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY TJRB
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8MSWIN1252
BADFILE 'TJRB':'TJ01.bad'
LOGFILE 'tj01.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "|" LDRTRIM
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
"TJDATE" CHAR(255)
TERMINATED BY "|"
DATE_FORMAT DATE MASK "YYYY.MM.DD",
"UNIT" CHAR(255)
TERMINATED BY "|",
"TABNAME" CHAR(255)
TERMINATED BY "|",
"FLG" CHAR(255)
TERMINATED BY "|",
"DAT1" CHAR(255)
TERMINATED BY "|",
"DAT2" CHAR(255)
TERMINATED BY "|",
"DAT3" CHAR(255)
TERMINATED BY "|",
"DAT4" CHAR(255)
TERMINATED BY "|",
"DAT5" CHAR(255)
TERMINATED BY "|",
"DAT6" CHAR(255)
TERMINATED BY "|",
"DAT7" CHAR(255)
TERMINATED BY "|",
"DAT8" CHAR(255)
TERMINATED BY "|",
"DAT9" CHAR(255)
TERMINATED BY "|",
"DAT10" CHAR(255)
TERMINATED BY "|",
"DAT11" CHAR(255)
TERMINATED BY "|",
"DAT12" CHAR(255)
TERMINATED BY "|",
"DAT13" CHAR(255)
TERMINATED BY "|",
"DAT14" CHAR(255)
TERMINATED BY "|",
"DAT15" CHAR(255)
TERMINATED BY "|",
"DAT16" CHAR(255)
TERMINATED BY "|",
"DAT17" CHAR(255)
TERMINATED BY "|",
"DAT18" CHAR(255)
TERMINATED BY "|",
"DAT19" CHAR(255)
TERMINATED BY "|",
"DAT20" CHAR(255)
TERMINATED BY "|",
"DAT21" CHAR(255)
TERMINATED BY "|",
"DAT22" CHAR(255)
TERMINATED BY "|",
"DAT23" CHAR(255)
TERMINATED BY "|",
"DAT24" CHAR(255)
TERMINATED BY "|",
"DAT25" CHAR(255)
TERMINATED BY "|",
"DAT26" CHAR(255)
TERMINATED BY "|",
"DAT27" CHAR(255)
TERMINATED BY "|",
"DAT28" CHAR(255)
TERMINATED BY "|",
"DAT29" CHAR(255)
TERMINATED BY "|",
"DAT30" CHAR(255)
TERMINATED BY "|",
"DAT31" CHAR(255)
TERMINATED BY "|",
"DAT32" CHAR(255)
TERMINATED BY "|",
"DAT33" CHAR(255)
TERMINATED BY "|",
"DAT34" CHAR(255)
TERMINATED BY "|",
"DAT35" CHAR(255)
TERMINATED BY "|",
"DAT36" CHAR(255)
TERMINATED BY "|",
"DAT37" CHAR(255)
TERMINATED BY "|",
"DAT38" CHAR(255)
TERMINATED BY "|",
"DAT39" CHAR(255)
TERMINATED BY "|",
"DAT40" CHAR(255)
TERMINATED BY "|",
"DAT41" CHAR(255)
TERMINATED BY "|",
"DAT42" CHAR(255)
TERMINATED BY "|",
"DAT43" CHAR(255)
TERMINATED BY "|",
"DAT44" CHAR(255)
TERMINATED BY "|",
"DAT45" CHAR(255)
TERMINATED BY "|",
"DAT46" CHAR(255)
TERMINATED BY "|",
"DAT47" CHAR(255)
TERMINATED BY "|",
"DAT48" CHAR(255)
TERMINATED BY "|",
"DAT49" CHAR(255)
TERMINATED BY "|",
"DAT50" CHAR(255)
TERMINATED BY "|"
)
)
location
(
'TJ01.txt'
)
)REJECT LIMIT UNLIMITED
/
接下来你就可以直接访问数据了！不用加载！
SELECT COUNT(*) FROM TJ01_EXT;
COUNT(*)
----------
3776
Elapsed: 00:00:00.20
然后用MERGE INTO加载并处理数据。这只是一个SQL, 如果你愿意的话可以用系统字典生成动态SQL, 我只是COPY&PASTE.
MERGE INTO TJRB t USING TJ01_EXT e
ON (t.TJDATE = e.TJDATE AND t.UNIT=e.UNIT AND t.TABNAME = e.TABNAME AND t.FLG = e.FLG)
WHEN MATCHED THEN UPDATE
SET dat1 =e.dat1
,dat2 =e.dat2
,dat3 =e.dat3
,dat4 =e.dat4
,dat5 =e.dat5
,dat6 =e.dat6
,dat7 =e.dat7
,dat8 =e.dat8
,dat9 =e.dat9
,dat10 =e.dat10
,dat11 =e.dat11
,dat12 =e.dat12
,dat13 =e.dat13
,dat14 =e.dat14
,dat15 =e.dat15
,dat16 =e.dat16
,dat17 =e.dat17
,dat18 =e.dat18
,dat19 =e.dat19
,dat20 =e.dat20
,dat21 =e.dat21
,dat22 =e.dat22
,dat23 =e.dat23
,dat24 =e.dat24
,dat25 =e.dat25
,dat26 =e.dat26
,dat27 =e.dat27
,dat28 =e.dat28
,dat29 =e.dat29
,dat30 =e.dat30
,dat31 =e.dat31
,dat32 =e.dat32
,dat33 =e.dat33
,dat34 =e.dat34
,dat35 =e.dat35
,dat36 =e.dat36
,dat37 =e.dat37
,dat38 =e.dat38
,dat39 =e.dat39
,dat40 =e.dat40
,dat41 =e.dat41
,dat42 =e.dat42
,dat43 =e.dat43
,dat44 =e.dat44
,dat45 =e.dat45
,dat46 =e.dat46
,dat47 =e.dat47
,dat48 =e.dat48
,dat49 =e.dat49
,dat50 =e.dat50
WHERE 1=1 ------ 你可在这里加入时间戳判断
WHEN NOT MATCHED THEN INSERT
VALUES (e.tjdate
,e.unit
,e.tabname
,e.flg
,e.dat1
,e.dat2
,e.dat3
,e.dat4
,e.dat5
,e.dat6
,e.dat7
,e.dat8
,e.dat9
,e.dat10
,e.dat11
,e.dat12
,e.dat13
,e.dat14
,e.dat15
,e.dat16
,e.dat17
,e.dat18
,e.dat19
,e.dat20
,e.dat21
,e.dat22
,e.dat23
,e.dat24
,e.dat25
,e.dat26
,e.dat27
,e.dat28
,e.dat29
,e.dat30
,e.dat31
,e.dat32
,e.dat33
,e.dat34
,e.dat35
,e.dat36
,e.dat37
,e.dat38
,e.dat39
,e.dat40
,e.dat41
,e.dat42
,e.dat43
,e.dat44
,e.dat45
,e.dat46
,e.dat47
,e.dat48
,e.dat49
,e.dat50
);
3776 rows merged.
Elapsed: 00:00:00.56
|
|
楼主: yulihua49
IP卡
狗仔卡
发表于 2008-12-22 04:02
显身卡
楼主