-- 给个例子给你: -- conn music/sztymusic@sztyoralf8;create or replace function is_date(v_char varchar2) return number is v_date date; begin v_date := to_date(v_char,'YYYY-MM-DD HH24:MI:SS'); return 1; exception when others then return 0; end; /-- 建表、序列: CREATE GLOBAL TEMPORARY TABLE music_click_stat_mem( vid NUMBER(18,0), p_cat_id NUMBER(18,0), -- 父类型 c_cat_id NUMBER(18,0), -- 子类型 pmd_str VARCHAR2(400), -- ID或歌曲名或网址 song_pcat_id NUMBER(18,0), -- 父song类型 song_ccat_id NUMBER(18,0), -- 子song类型 song_id NUMBER(18,0), -- song id times NUMBER(18,0) -- 次数 ) ON COMMIT PRESERVE ROWS;-- DROP TABLE music_click_stat PURGE; CREATE TABLE music_click_stat( vid NUMBER(18,0), p_cat_id NUMBER(18,0), c_cat_id NUMBER(18,0), pmd_str VARCHAR2(400), -- ID或歌曲名或网址 song_pcat_id NUMBER(18,0), song_ccat_id NUMBER(18,0), song_id NUMBER(18,0), times NUMBER(18,0), open_time DATE DEFAULT SYSDATE NOT NULL, close_time DATE DEFAULT SYSDATE NOT NULL, mobile VARCHAR2(20), imsi VARCHAR2(20), version VARCHAR2(50), model VARCHAR2(50), logid NUMBER(38,0) );INSERT INTO music_click_stat(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times,open_time,close_time,mobile,imsi,version,model,logid) SELECT vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times,open_time,close_time,mobile,imsi,version,model,logid from music_click_stat2_bak;COMMENT ON TABLE MUSIC_CLICK_STAT IS '播放数据统计明细表'; COMMENT ON COLUMN MUSIC_CLICK_STAT.VID IS 'VID'; COMMENT ON COLUMN MUSIC_CLICK_STAT.P_CAT_ID IS '父类型'; COMMENT ON COLUMN MUSIC_CLICK_STAT.C_CAT_ID IS '子类型'; COMMENT ON COLUMN MUSIC_CLICK_STAT.PMD_STR IS 'ID或歌曲名或网址'; COMMENT ON COLUMN MUSIC_CLICK_STAT.SONG_PCAT_ID IS '父song类型'; COMMENT ON COLUMN MUSIC_CLICK_STAT.SONG_CCAT_ID IS '子song类型'; COMMENT ON COLUMN MUSIC_CLICK_STAT.SONG_ID IS 'song id'; COMMENT ON COLUMN MUSIC_CLICK_STAT.TIMES IS '次数'; COMMENT ON COLUMN MUSIC_CLICK_STAT.OPEN_TIME IS '打开时间'; COMMENT ON COLUMN MUSIC_CLICK_STAT.CLOSE_TIME IS '关闭时间'; COMMENT ON COLUMN MUSIC_CLICK_STAT.MOBILE IS '手机号'; COMMENT ON COLUMN MUSIC_CLICK_STAT.IMSI IS 'IMSI'; COMMENT ON COLUMN MUSIC_CLICK_STAT.VERSION IS '版本号'; COMMENT ON COLUMN MUSIC_CLICK_STAT.MODEL IS '机型'; COMMENT ON COLUMN MUSIC_CLICK_STAT.LOGID IS 'LOGID(对应musicloguser_detail表的LOGID)'; music@SZTYORA> desc music_click_stat 名称 ----------------------------------------------------------------------------------------- VID -- 项ID P_CAT_ID -- 父类型 C_CAT_ID -- 子类型 PMD_STR -- ID或歌曲名或网址 SONG_PCAT_ID -- 父song类型 SONG_CCAT_ID -- 子song类型 SONG_ID -- 歌曲ID TIMES -- 次数 OPEN_TIME -- 打开时间 CLOSE_TIME -- 关闭时间 MOBILE -- 手机号 IMSI -- IMSI号 VERSION -- 版本号 MODEL -- 机型 LOGID -- LOGID(对应musicloguser_detail表的LOGID)COLUMN table_info FOR a100; SELECT 'COMMENT ON COLUMN '||t1.Table_Name||'.'||t1.Column_Name||' IS '''';' AS table_info FROM cols t1 left join user_col_comments t2 on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name left join user_tab_comments t3 on t1.Table_name=t3.Table_name WHERE t1.table_name=upper('music_click_stat') ORDER BY t1.Table_Name, t1.Column_ID;CREATE SEQUENCE music_click_stat_seq start with 1 nomaxvalue nocycle cache 1000; /
CREATE OR REPLACE PROCEDURE music_click_stat_inc_proc IS v_mobile VARCHAR2(20); v_imsi VARCHAR2(20); v_version VARCHAR2(50); v_model VARCHAR2(50); v_z VARCHAR2(4000); v_sub_z VARCHAR2(4000); v_sub_z1 VARCHAR2(4000); v_sub_z1a VARCHAR2(4000); v_sub_z1b VARCHAR2(4000); v_sub_z1c VARCHAR2(4000); v_sub_z1d VARCHAR2(4000); v_dot NUMBER(18,0); v_logid NUMBER(38,0); v_open_time DATE; v_close_time DATE; v_timed DATE; v_cnt NUMBER(18,0); v_loop1 NUMBER(18,0); v_loop2 NUMBER(18,0); v_yesterday VARCHAR2(10); v_yesterday1 VARCHAR2(10); v_yesterday2 VARCHAR2(10); v_befyesterday VARCHAR2(10); v_befyesterday1 VARCHAR2(10); v_befyesterday2 VARCHAR2(10); v_today VARCHAR2(10); v_today1 VARCHAR2(10); v_today2 VARCHAR2(10); v_min_logid NUMBER(18,0); v_max_logid NUMBER(18,0); CURSOR c_ur IS SELECT c1, -- version c2, -- model e1, -- 手机号 a1, -- IMSI z, logid, timed FROM musicloguser_detail WHERE timed>=trunc(sysdate-1) AND timed<trunc(sysdate) AND z IS NOT NULL;BEGIN
SELECT NVL(COUNT(1),0) INTO v_cnt FROM music_click_stat_inc WHERE cdate=trunc(sysdate-1); IF v_cnt=0 THEN -- 处理日期格式不一致的数据(将日期格式不一致的数据,更新成一致格式:YYYY-MM-DD): SELECT TO_CHAR(sysdate-1,'YYYY-MM-DD') c1, TO_CHAR(sysdate-1,'MM-DD-YYYY') c2, TO_CHAR(sysdate-1,'DD-MM-YYYY') c3, TO_CHAR(sysdate-2,'YYYY-MM-DD') c4, TO_CHAR(sysdate-2,'MM-DD-YYYY') c5, TO_CHAR(sysdate-2,'DD-MM-YYYY') c6, TO_CHAR(sysdate,'YYYY-MM-DD') c7, TO_CHAR(sysdate,'MM-DD-YYYY') c8, TO_CHAR(sysdate,'DD-MM-YYYY') c9 INTO v_yesterday, v_yesterday1, v_yesterday2, v_befyesterday, v_befyesterday1, v_befyesterday2, v_today, v_today1, v_today2 FROM DUAL; SELECT MIN(logid), MAX(logid) INTO v_min_logid, v_max_logid FROM musicloguser_detail WHERE timed>=trunc(sysdate-1) AND timed<trunc(sysdate); -- 昨天: UPDATE musicloguser_detail SET z=replace(replace(z,v_yesterday1,v_yesterday),v_yesterday2,v_yesterday) WHERE logid IN (SELECT logid FROM ( SELECT logid, z FROM musicloguser_detail WHERE logid>=v_min_logid AND logid<=v_max_logid ) t WHERE z like '%'||v_yesterday1||'%' or z like '%'||v_yesterday2||'%' ); -- 前天: UPDATE musicloguser_detail SET z=replace(replace(z,v_befyesterday1,v_befyesterday),v_befyesterday2,v_befyesterday) WHERE logid IN (SELECT logid FROM ( SELECT logid, z FROM musicloguser_detail WHERE logid>=v_min_logid AND logid<=v_max_logid ) t WHERE z like '%'||v_befyesterday1||'%' or z like '%'||v_befyesterday2||'%' ); -- 今天: UPDATE musicloguser_detail SET z=replace(replace(z,v_today1,v_today),v_today2,v_today) WHERE logid IN (SELECT logid FROM ( SELECT logid, z FROM musicloguser_detail WHERE logid>=v_min_logid AND logid<=v_max_logid ) t WHERE z like '%'||v_today1||'%' or z like '%'||v_today2||'%' ); COMMIT; FOR i_ur IN c_ur LOOP v_version := i_ur.c1; v_model := i_ur.c2; v_mobile := i_ur.e1; v_imsi := i_ur.a1; v_z := '|'||i_ur.z||'|'; v_logid := i_ur.logid; v_timed := i_ur.timed; -- 播放器安装数据统计 v_loop1 := LENGTH(v_z)-LENGTH(REPLACE(v_z,'|','')); FOR i IN 2..v_loop1 LOOP v_sub_z := SUBSTR(v_z,INSTR(v_z,'|',1,i-1)+1, INSTR(v_z,'|',1,i)-INSTR(v_z,'|',1,i-1)-1); -- 前7项 IF v_sub_z LIKE '1.%' OR v_sub_z LIKE '2.%' OR v_sub_z LIKE '3.%' OR v_sub_z LIKE '4.%' OR v_sub_z LIKE '5.%' OR v_sub_z LIKE '6.%' OR v_sub_z LIKE '7.%' THEN BEGIN INSERT INTO music_click_stat_mem(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times) VALUES(music_click_stat_seq.nextval,SUBSTR(v_sub_z,1,1),-1,-1,-1,-1,SUBSTR(v_sub_z,3)); END; -- 第8项 ELSIF v_sub_z LIKE '8.%' THEN BEGIN v_sub_z := SUBSTR(v_sub_z,2)||'.'; v_loop2 := LENGTH(v_sub_z)-LENGTH(REPLACE(v_sub_z,'.','')); FOR j IN 2..v_loop2 LOOP v_sub_z1 := SUBSTR(v_sub_z,INSTR(v_sub_z,'.',1,j-1)+1, INSTR(v_sub_z,'.',1,j)-INSTR(v_sub_z,'.',1,j-1)-1); INSERT INTO music_click_stat_mem(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times) VALUES(music_click_stat_seq.nextval,8,j-1,-1,-1,-1,v_sub_z1); END LOOP; END; -- 第9项 ELSIF v_sub_z LIKE '9.%' THEN BEGIN v_sub_z := SUBSTR(v_sub_z,2)||'.'; v_loop2 := LENGTH(v_sub_z)-LENGTH(REPLACE(v_sub_z,'.','')); FOR j IN 2..v_loop2 LOOP v_sub_z1 := SUBSTR(v_sub_z,INSTR(v_sub_z,'.',1,j-1)+1, INSTR(v_sub_z,'.',1,j)-INSTR(v_sub_z,'.',1,j-1)-1); INSERT INTO music_click_stat_mem(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times) VALUES(music_click_stat_seq.nextval,9,-1,-1,-1,v_sub_z1,1); END LOOP; END;
可以在package中定义自定义数组类型: type C_Type is varray(6) of varchar2(80); ... procudure ... is mType C_Type; begin mType := new C_Type(); mType.extend(2); mType(1):="test1"; mType(2):="test2"; end;
会用到substr,instr等函数
select substr(字符串,1,instr(字符串,',',1,1)-1) id,--
substr(字符串,instr(字符串,',',1,1)+1,instr(字符串,',',1,2)-1) name 以此类推;instr(字符串,',',1,1)-1 从左边第一位开始,第一次返回','的位置;
-- conn music/sztymusic@sztyoralf8;create or replace function is_date(v_char varchar2)
return number
is
v_date date;
begin
v_date := to_date(v_char,'YYYY-MM-DD HH24:MI:SS');
return 1;
exception when others then
return 0;
end;
/-- 建表、序列:
CREATE GLOBAL TEMPORARY TABLE music_click_stat_mem(
vid NUMBER(18,0),
p_cat_id NUMBER(18,0), -- 父类型
c_cat_id NUMBER(18,0), -- 子类型
pmd_str VARCHAR2(400), -- ID或歌曲名或网址
song_pcat_id NUMBER(18,0), -- 父song类型
song_ccat_id NUMBER(18,0), -- 子song类型
song_id NUMBER(18,0), -- song id
times NUMBER(18,0) -- 次数
) ON COMMIT PRESERVE ROWS;-- DROP TABLE music_click_stat PURGE;
CREATE TABLE music_click_stat(
vid NUMBER(18,0),
p_cat_id NUMBER(18,0),
c_cat_id NUMBER(18,0),
pmd_str VARCHAR2(400), -- ID或歌曲名或网址
song_pcat_id NUMBER(18,0),
song_ccat_id NUMBER(18,0),
song_id NUMBER(18,0),
times NUMBER(18,0),
open_time DATE DEFAULT SYSDATE NOT NULL,
close_time DATE DEFAULT SYSDATE NOT NULL,
mobile VARCHAR2(20),
imsi VARCHAR2(20),
version VARCHAR2(50),
model VARCHAR2(50),
logid NUMBER(38,0)
);INSERT INTO music_click_stat(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times,open_time,close_time,mobile,imsi,version,model,logid)
SELECT vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times,open_time,close_time,mobile,imsi,version,model,logid
from music_click_stat2_bak;COMMENT ON TABLE MUSIC_CLICK_STAT IS '播放数据统计明细表';
COMMENT ON COLUMN MUSIC_CLICK_STAT.VID IS 'VID';
COMMENT ON COLUMN MUSIC_CLICK_STAT.P_CAT_ID IS '父类型';
COMMENT ON COLUMN MUSIC_CLICK_STAT.C_CAT_ID IS '子类型';
COMMENT ON COLUMN MUSIC_CLICK_STAT.PMD_STR IS 'ID或歌曲名或网址';
COMMENT ON COLUMN MUSIC_CLICK_STAT.SONG_PCAT_ID IS '父song类型';
COMMENT ON COLUMN MUSIC_CLICK_STAT.SONG_CCAT_ID IS '子song类型';
COMMENT ON COLUMN MUSIC_CLICK_STAT.SONG_ID IS 'song id';
COMMENT ON COLUMN MUSIC_CLICK_STAT.TIMES IS '次数';
COMMENT ON COLUMN MUSIC_CLICK_STAT.OPEN_TIME IS '打开时间';
COMMENT ON COLUMN MUSIC_CLICK_STAT.CLOSE_TIME IS '关闭时间';
COMMENT ON COLUMN MUSIC_CLICK_STAT.MOBILE IS '手机号';
COMMENT ON COLUMN MUSIC_CLICK_STAT.IMSI IS 'IMSI';
COMMENT ON COLUMN MUSIC_CLICK_STAT.VERSION IS '版本号';
COMMENT ON COLUMN MUSIC_CLICK_STAT.MODEL IS '机型';
COMMENT ON COLUMN MUSIC_CLICK_STAT.LOGID IS 'LOGID(对应musicloguser_detail表的LOGID)';
music@SZTYORA> desc music_click_stat
名称
-----------------------------------------------------------------------------------------
VID -- 项ID
P_CAT_ID -- 父类型
C_CAT_ID -- 子类型
PMD_STR -- ID或歌曲名或网址
SONG_PCAT_ID -- 父song类型
SONG_CCAT_ID -- 子song类型
SONG_ID -- 歌曲ID
TIMES -- 次数
OPEN_TIME -- 打开时间
CLOSE_TIME -- 关闭时间
MOBILE -- 手机号
IMSI -- IMSI号
VERSION -- 版本号
MODEL -- 机型
LOGID -- LOGID(对应musicloguser_detail表的LOGID)COLUMN table_info FOR a100;
SELECT 'COMMENT ON COLUMN '||t1.Table_Name||'.'||t1.Column_Name||' IS '''';' AS table_info
FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3
on t1.Table_name=t3.Table_name
WHERE t1.table_name=upper('music_click_stat')
ORDER BY t1.Table_Name, t1.Column_ID;CREATE SEQUENCE music_click_stat_seq start with 1 nomaxvalue nocycle cache 1000;
/
IS
v_mobile VARCHAR2(20);
v_imsi VARCHAR2(20);
v_version VARCHAR2(50);
v_model VARCHAR2(50);
v_z VARCHAR2(4000);
v_sub_z VARCHAR2(4000);
v_sub_z1 VARCHAR2(4000);
v_sub_z1a VARCHAR2(4000);
v_sub_z1b VARCHAR2(4000);
v_sub_z1c VARCHAR2(4000);
v_sub_z1d VARCHAR2(4000);
v_dot NUMBER(18,0);
v_logid NUMBER(38,0);
v_open_time DATE;
v_close_time DATE;
v_timed DATE;
v_cnt NUMBER(18,0);
v_loop1 NUMBER(18,0);
v_loop2 NUMBER(18,0);
v_yesterday VARCHAR2(10);
v_yesterday1 VARCHAR2(10);
v_yesterday2 VARCHAR2(10);
v_befyesterday VARCHAR2(10);
v_befyesterday1 VARCHAR2(10);
v_befyesterday2 VARCHAR2(10);
v_today VARCHAR2(10);
v_today1 VARCHAR2(10);
v_today2 VARCHAR2(10);
v_min_logid NUMBER(18,0);
v_max_logid NUMBER(18,0);
CURSOR c_ur IS
SELECT c1, -- version
c2, -- model
e1, -- 手机号
a1, -- IMSI
z, logid, timed
FROM musicloguser_detail
WHERE timed>=trunc(sysdate-1)
AND timed<trunc(sysdate)
AND z IS NOT NULL;BEGIN
-- 处理日期格式不一致的数据(将日期格式不一致的数据,更新成一致格式:YYYY-MM-DD): SELECT TO_CHAR(sysdate-1,'YYYY-MM-DD') c1, TO_CHAR(sysdate-1,'MM-DD-YYYY') c2, TO_CHAR(sysdate-1,'DD-MM-YYYY') c3,
TO_CHAR(sysdate-2,'YYYY-MM-DD') c4, TO_CHAR(sysdate-2,'MM-DD-YYYY') c5, TO_CHAR(sysdate-2,'DD-MM-YYYY') c6,
TO_CHAR(sysdate,'YYYY-MM-DD') c7, TO_CHAR(sysdate,'MM-DD-YYYY') c8, TO_CHAR(sysdate,'DD-MM-YYYY') c9
INTO v_yesterday, v_yesterday1, v_yesterday2, v_befyesterday, v_befyesterday1, v_befyesterday2, v_today, v_today1, v_today2
FROM DUAL; SELECT MIN(logid), MAX(logid)
INTO v_min_logid, v_max_logid FROM musicloguser_detail WHERE timed>=trunc(sysdate-1) AND timed<trunc(sysdate); -- 昨天:
UPDATE musicloguser_detail SET z=replace(replace(z,v_yesterday1,v_yesterday),v_yesterday2,v_yesterday)
WHERE logid IN (SELECT logid
FROM ( SELECT logid, z FROM musicloguser_detail WHERE logid>=v_min_logid AND logid<=v_max_logid ) t
WHERE z like '%'||v_yesterday1||'%' or z like '%'||v_yesterday2||'%' );
-- 前天:
UPDATE musicloguser_detail SET z=replace(replace(z,v_befyesterday1,v_befyesterday),v_befyesterday2,v_befyesterday)
WHERE logid IN (SELECT logid
FROM ( SELECT logid, z FROM musicloguser_detail WHERE logid>=v_min_logid AND logid<=v_max_logid ) t
WHERE z like '%'||v_befyesterday1||'%' or z like '%'||v_befyesterday2||'%' );
-- 今天:
UPDATE musicloguser_detail SET z=replace(replace(z,v_today1,v_today),v_today2,v_today)
WHERE logid IN (SELECT logid
FROM ( SELECT logid, z FROM musicloguser_detail WHERE logid>=v_min_logid AND logid<=v_max_logid ) t
WHERE z like '%'||v_today1||'%' or z like '%'||v_today2||'%' );
COMMIT; FOR i_ur IN c_ur LOOP
v_version := i_ur.c1;
v_model := i_ur.c2;
v_mobile := i_ur.e1;
v_imsi := i_ur.a1;
v_z := '|'||i_ur.z||'|';
v_logid := i_ur.logid;
v_timed := i_ur.timed; -- 播放器安装数据统计
v_loop1 := LENGTH(v_z)-LENGTH(REPLACE(v_z,'|','')); FOR i IN 2..v_loop1 LOOP
v_sub_z := SUBSTR(v_z,INSTR(v_z,'|',1,i-1)+1,
INSTR(v_z,'|',1,i)-INSTR(v_z,'|',1,i-1)-1); -- 前7项
IF v_sub_z LIKE '1.%' OR v_sub_z LIKE '2.%' OR v_sub_z LIKE '3.%' OR v_sub_z LIKE '4.%' OR v_sub_z LIKE '5.%' OR v_sub_z LIKE '6.%' OR v_sub_z LIKE '7.%' THEN
BEGIN
INSERT INTO music_click_stat_mem(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times)
VALUES(music_click_stat_seq.nextval,SUBSTR(v_sub_z,1,1),-1,-1,-1,-1,SUBSTR(v_sub_z,3));
END;
-- 第8项
ELSIF v_sub_z LIKE '8.%' THEN
BEGIN
v_sub_z := SUBSTR(v_sub_z,2)||'.';
v_loop2 := LENGTH(v_sub_z)-LENGTH(REPLACE(v_sub_z,'.',''));
FOR j IN 2..v_loop2 LOOP
v_sub_z1 := SUBSTR(v_sub_z,INSTR(v_sub_z,'.',1,j-1)+1,
INSTR(v_sub_z,'.',1,j)-INSTR(v_sub_z,'.',1,j-1)-1);
INSERT INTO music_click_stat_mem(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times)
VALUES(music_click_stat_seq.nextval,8,j-1,-1,-1,-1,v_sub_z1);
END LOOP;
END;
-- 第9项
ELSIF v_sub_z LIKE '9.%' THEN
BEGIN
v_sub_z := SUBSTR(v_sub_z,2)||'.';
v_loop2 := LENGTH(v_sub_z)-LENGTH(REPLACE(v_sub_z,'.',''));
FOR j IN 2..v_loop2 LOOP
v_sub_z1 := SUBSTR(v_sub_z,INSTR(v_sub_z,'.',1,j-1)+1,
INSTR(v_sub_z,'.',1,j)-INSTR(v_sub_z,'.',1,j-1)-1);
INSERT INTO music_click_stat_mem(vid,p_cat_id,c_cat_id,song_pcat_id,song_ccat_id,song_id,times)
VALUES(music_click_stat_seq.nextval,9,-1,-1,-1,v_sub_z1,1);
END LOOP;
END;
将要插入的文件内容放在EXCEL里编辑下,按‘,’分列。
复制所有内容(前面包含一列空列,这个很重要)
修改表数据 把复制的内容粘贴就可以了
我是要写一个存储过程,函数传入要导入的表名和那个字符串,处理就是要把那个字符串按照表名的表加进去,还有一个难点就是要判断这个数据是不是已经存在了 存在的话就不insert 不存在才insert
楼主google下sqlldr就明白了
呃 估计我忘记说明了,这个是要做成存储过程的,所以不能用sqlldr
type C_Type is varray(6) of varchar2(80); ...
procudure ... is
mType C_Type;
begin
mType := new C_Type();
mType.extend(2);
mType(1):="test1";
mType(2):="test2";
end;
然后拼接成动态sql执行