--LOOP 02 LOOP BEGIN utl_file.get_line( l_input, l_lastLine ); exception when NO_DATA_FOUND then exit; END; IF SUBSTR(L_LASTLINE,1,5) = 'pgPar' THEN l_pgPar := SUBSTR(l_lastLine,7); ELSIF SUBSTR(l_lastLine,1,7) = 'pgNpack' THEN l_pgNpack := SUBSTR(l_lastLine,9); ELSIF SUBSTR(l_lastLine,1,7) = 'pgNtest' THEN l_pgNtest := SUBSTR(l_lastLine,9); ELSIF SUBSTR(l_lastLine,1,6) = 'pgDate' THEN l_pgDate := SUBSTR(l_lastLine,8); ELSIF SUBSTR(l_lastLine,1,3) = 'pgH' AND SUBSTR(l_lastLine,6) IS NOT NULL THEN l_buffer := 'insert into group_header values ('; l_buffer := l_buffer || ':b1,:b2,:b3)'; dbms_sql.parse(l_theCursor, l_buffer, dbms_sql.native); dbms_sql.bind_variable( l_theCursor, ':b1',p_seq); l_buffer := SUBSTR(l_lastLine,3,3); l_buffer := REPLACE(l_buffer,'=',''); dbms_sql.bind_variable( l_theCursor, ':b2',l_buffer); l_buffer := SUBSTR(l_lastLine,6); l_buffer := REPLACE(l_buffer,'=',''); dbms_sql.bind_variable( l_theCursor, ':b3',l_buffer); BEGIN l_status := dbms_sql.execute(l_theCursor); exception when others then l_errmsg := sqlerrm; -- insert into badlog ( errm, data ) -- values ( l_errmsg, l_lastLine ); END; ELSIF SUBSTR(l_lastLine,1,3) = 'pgL' AND SUBSTR(l_lastLine,6) IS NOT NULL THEN l_buffer := 'insert into group_label values ('; l_buffer := l_buffer || ':b1,:b2,:b3)'; dbms_sql.parse(l_theCursor, l_buffer, dbms_sql.native); dbms_sql.bind_variable( l_theCursor, ':b1',p_seq); l_buffer := SUBSTR(l_lastLine,3,3); l_buffer := REPLACE(l_buffer,'=','');
dbms_sql.bind_variable( l_theCursor, ':b2',l_buffer); l_buffer := SUBSTR(l_lastLine,6); l_buffer := REPLACE(l_buffer,'=',''); dbms_sql.bind_variable( l_theCursor, ':b3',l_buffer); BEGIN l_status := dbms_sql.execute(l_theCursor); exception when others then l_errmsg := sqlerrm; -- insert into badlog ( errm, data ) -- values ( l_errmsg, l_lastLine ); END; END IF; END LOOP; --LOOP 02 END l_buffer := 'insert into group_master values ('; l_buffer := l_buffer || ':b1,:b2,:b3,:b4,:b5)'; dbms_sql.parse(l_theCursor, l_buffer, dbms_sql.native); dbms_sql.bind_variable( l_theCursor, ':b1',p_seq); dbms_sql.bind_variable( l_theCursor, ':b2',l_pgPar); dbms_sql.bind_variable( l_theCursor, ':b3',l_pgNpack); dbms_sql.bind_variable( l_theCursor, ':b4',l_pgNtest); dbms_sql.bind_variable( l_theCursor, ':b5',l_pgDate); BEGIN l_status := dbms_sql.execute(l_theCursor); exception when others then l_errmsg := sqlerrm; insert into badlog (errm,err_data) values ( l_errmsg, l_lastLine );; END; dbms_sql.close_cursor(l_theCursor); utl_file.fclose( l_input ); commit; EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN insert into badlog (errm,err_data) values ('20100','INVALID PATH'); WHEN UTL_FILE.INVALID_MODE THEN insert into badlog (errm,err_data) values ('20101','INVALID MODE'); WHEN UTL_FILE.INVALID_FILEHANDLE THEN insert into badlog (errm,err_data) values ('20102','INVALID FILEHANDLE'); WHEN UTL_FILE.INVALID_OPERATION THEN insert into badlog (errm,err_data) values ('20103','INVALID OPERATION -- MAY SIGNAL AFILE LOCKED BY THE OS'); WHEN UTL_FILE.READ_ERROR THEN insert into badlog (errm,err_data) values ('20104','READ ERROR'); WHEN UTL_FILE.WRITE_ERROR THEN insert into badlog (errm,err_data) values ('20105','WRITE ERROR'); WHEN UTL_FILE.INTERNAL_ERROR THEN insert into badlog (errm,err_data) values ('20106','INTERNAL ERROR'); WHEN NO_DATA_FOUND THEN insert into badlog (errm,err_data) values ('20107','NO DATA FOUND'); WHEN value_ERROR THEN insert into badlog (errm,err_data) values ('20108','value ERROR'); WHEN OTHERS THEN insert into badlog (errm,err_data) values ('20109','UNKNOWN UTL_FILE ERROR'); END load_group;
感谢回贴。 我可能问题没说仔细: 表t1 有字段 f1 varchar2(100), f2 number 其中f1是主键。里面已经有很多数据。文本文件2个字段与t1对应,空格分开 id1 2 id2 4 id3 5 id4 1 如果表t1中已有的数据要用文本文件更新f2的值,用f1关联, 如果表t1没有的忽略。数据巨多。 怎样搞最简洁高效?楼上给的感觉好复杂,稍后我仔细看一下我会及时揭帖。
123 ttt
234 rrr
345 yyy
请问,如何导入啊
给你一个文件供效仿。保证可以顺利进行任何复杂文本操作,呵呵create or replace procedure load_group(
p_dir in varchar2,
p_filename in varchar2,
p_seq in number)
is
l_input utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_status integer;
l_buffer varchar2(4000);
l_lastLine varchar2(4000);
l_sep char(1) default 0;
l_errmsg varchar2(4000);
l_pgPar varchar2(128) default '';
l_pgNpack varchar2(32) default '';
l_pgNtest varchar2(32) default '';
l_pgDate varchar2(64) default '';BEGIN
l_input := utl_file.fopen( p_dir, p_filename, 'r' );
--LOOP 02
LOOP
BEGIN
utl_file.get_line( l_input, l_lastLine );
exception
when NO_DATA_FOUND then
exit;
END; IF SUBSTR(L_LASTLINE,1,5) = 'pgPar' THEN l_pgPar := SUBSTR(l_lastLine,7); ELSIF SUBSTR(l_lastLine,1,7) = 'pgNpack' THEN l_pgNpack := SUBSTR(l_lastLine,9); ELSIF SUBSTR(l_lastLine,1,7) = 'pgNtest' THEN l_pgNtest := SUBSTR(l_lastLine,9); ELSIF SUBSTR(l_lastLine,1,6) = 'pgDate' THEN l_pgDate := SUBSTR(l_lastLine,8); ELSIF SUBSTR(l_lastLine,1,3) = 'pgH'
AND SUBSTR(l_lastLine,6) IS NOT NULL THEN l_buffer := 'insert into group_header values ('; l_buffer := l_buffer || ':b1,:b2,:b3)'; dbms_sql.parse(l_theCursor, l_buffer, dbms_sql.native); dbms_sql.bind_variable( l_theCursor, ':b1',p_seq); l_buffer := SUBSTR(l_lastLine,3,3);
l_buffer := REPLACE(l_buffer,'=',''); dbms_sql.bind_variable( l_theCursor, ':b2',l_buffer); l_buffer := SUBSTR(l_lastLine,6);
l_buffer := REPLACE(l_buffer,'=',''); dbms_sql.bind_variable( l_theCursor, ':b3',l_buffer);
BEGIN
l_status := dbms_sql.execute(l_theCursor);
exception
when others then
l_errmsg := sqlerrm;
-- insert into badlog ( errm, data )
-- values ( l_errmsg, l_lastLine );
END; ELSIF SUBSTR(l_lastLine,1,3) = 'pgL'
AND SUBSTR(l_lastLine,6) IS NOT NULL THEN l_buffer := 'insert into group_label values ('; l_buffer := l_buffer || ':b1,:b2,:b3)'; dbms_sql.parse(l_theCursor, l_buffer, dbms_sql.native); dbms_sql.bind_variable( l_theCursor, ':b1',p_seq); l_buffer := SUBSTR(l_lastLine,3,3);
l_buffer := REPLACE(l_buffer,'=','');
dbms_sql.bind_variable( l_theCursor, ':b2',l_buffer); l_buffer := SUBSTR(l_lastLine,6);
l_buffer := REPLACE(l_buffer,'=',''); dbms_sql.bind_variable( l_theCursor, ':b3',l_buffer); BEGIN
l_status := dbms_sql.execute(l_theCursor);
exception
when others then
l_errmsg := sqlerrm;
-- insert into badlog ( errm, data )
-- values ( l_errmsg, l_lastLine );
END;
END IF; END LOOP;
--LOOP 02 END l_buffer := 'insert into group_master values ('; l_buffer := l_buffer || ':b1,:b2,:b3,:b4,:b5)'; dbms_sql.parse(l_theCursor, l_buffer, dbms_sql.native); dbms_sql.bind_variable( l_theCursor, ':b1',p_seq);
dbms_sql.bind_variable( l_theCursor, ':b2',l_pgPar);
dbms_sql.bind_variable( l_theCursor, ':b3',l_pgNpack);
dbms_sql.bind_variable( l_theCursor, ':b4',l_pgNtest);
dbms_sql.bind_variable( l_theCursor, ':b5',l_pgDate); BEGIN
l_status := dbms_sql.execute(l_theCursor);
exception
when others then
l_errmsg := sqlerrm;
insert into badlog (errm,err_data)
values ( l_errmsg, l_lastLine );; END; dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_input );
commit;
EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
insert into badlog (errm,err_data) values
('20100','INVALID PATH');
WHEN UTL_FILE.INVALID_MODE THEN
insert into badlog (errm,err_data) values
('20101','INVALID MODE');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
insert into badlog (errm,err_data) values
('20102','INVALID FILEHANDLE');
WHEN UTL_FILE.INVALID_OPERATION THEN
insert into badlog (errm,err_data) values
('20103','INVALID OPERATION -- MAY SIGNAL AFILE LOCKED BY THE OS');
WHEN UTL_FILE.READ_ERROR THEN
insert into badlog (errm,err_data) values
('20104','READ ERROR');
WHEN UTL_FILE.WRITE_ERROR THEN
insert into badlog (errm,err_data) values
('20105','WRITE ERROR');
WHEN UTL_FILE.INTERNAL_ERROR THEN
insert into badlog (errm,err_data) values
('20106','INTERNAL ERROR');
WHEN NO_DATA_FOUND THEN
insert into badlog (errm,err_data) values
('20107','NO DATA FOUND');
WHEN value_ERROR THEN
insert into badlog (errm,err_data) values
('20108','value ERROR');
WHEN OTHERS THEN
insert into badlog (errm,err_data) values
('20109','UNKNOWN UTL_FILE ERROR');
END load_group;
我可能问题没说仔细:
表t1 有字段 f1 varchar2(100), f2 number 其中f1是主键。里面已经有很多数据。文本文件2个字段与t1对应,空格分开
id1 2
id2 4
id3 5
id4 1 如果表t1中已有的数据要用文本文件更新f2的值,用f1关联,
如果表t1没有的忽略。数据巨多。
怎样搞最简洁高效?楼上给的感觉好复杂,稍后我仔细看一下我会及时揭帖。
http://community.csdn.net/Expert/topic/3251/3251931.xml?temp=.1730921