oracle导入
如何将txt文件导入到oracle中，请求完整代码！谢谢

------解决方案--------------------
参考


http://psoug.org/reference/utl_file.html
------解决方案--------------------
Import a .txt file into Oracle database tables using utl.file.


my_data.txt:

2000 Pizza 1500 Monday 03/09/2007 Adults NO
2000 Pizza 1500 Monday 03/09/2007 Children YES
3000 Broccoli 1600 Tuesday 04/09/2007 Adults YES
3000 Broccoli 1600 Tuesday 04/09/2007 CHildren NO


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.