oracle导入
如何将txt文件导入到oracle中，请求完整代码！谢谢
[解决办法]
参考
http://psoug.org/reference/utl_file.html
[解决办法]
Import a .txt file into Oracle database tables using utl.file.
my_data.txt:
2000Pizza1500Monday03/09/2007AdultsNO
2000Pizza1500Monday03/09/2007ChildrenYES
3000Broccoli1600Tuesday04/09/2007AdultsYES
3000Broccoli1600Tuesday04/09/2007CHildrenNO
SQL> create directory dir as 'C:\ ';
Directory created.
SQL> ed
Wrote file afiedt.buf
1 declare
2 fp utl_file.file_type;
3 type rec is record(food_id number,food_name varchar2(20),
4 start_time number,start_date date,
5 group_name varchar2(10),rate number);
6 rec1 rec;
7 str varchar2(200);
8 tab char(1) := chr(9);
9 begin
10 fp := utl_file.fopen( 'DIR ', 'mydata.txt ', 'r ');
11 loop
12 utl_file.get_line(fp,str,200);
13 rec1.food_id := regexp_substr(str, '[^ '||tab|| ']+ ',1,1);
14 rec1.food_name := regexp_substr(str, '[^ '||tab|| ']+ ',1,2);
15 rec1.start_time := regexp_substr(str, '[^ '||tab|| ']+ ',1,3);
16 rec1.start_date := to_date(regexp_substr(str, '[^ '||tab|| ']+ ',1,5),
17 'dd/mm/yyyy ');
18 rec1.group_name := regexp_substr(str, '[^ '||tab|| ']+ ',1,6);
19 rec1.rate := case regexp_substr(str, '[^ '||tab|| ']+ ',1,7)
20 when 'YES ' then 123
21 when 'NO ' then 124
22 else null
23 end;
24 dbms_output.put_line(rec1.food_id|| ', '||rec1.food_name||chr(10));
25 end loop;
26 exception
27 when no_data_found then
28 utl_file.fclose(fp);
29* end;
SQL> /
2000,Pizza
2000,Pizza
3000,Broccoli
3000,Broccoli
PL/SQL procedure successfully completed.
