http://blog.csdn.net/wuxiaokaixinguo/article/details/8016147
来源:互联网 发布:凌阳单片机语音识别 编辑:程序博客网 时间:2024/05/21 07:56
首先判断utl_file_dir的目录
SQL> show parameter utl_file_dir
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string C:\logmnr
修改utl_file_dir目录的方法:
在D:\oracle\product\10.2.0\db_1\database目录下的initSID.ora文件中添加utl_file_dir=C:\logmnr 即可(也可修改为其它目录)
在这里以utl_file_dir=c:\logmnr为列子进行测试;
使用Oracle编写存储过程导出数据的存储过程为:
CREATE OR REPLACE PROCEDURE "EXP_DATA"
(
P_QUERY IN VARCHAR2, -- sql语句 例子: 'select * from TEST'
P_DIR IN VARCHAR2, -- 目录 用这个命令查看目录show parameter utl_file_dir
P_FILENAME IN VARCHAR2 -- 要生成的文件名
)
IS
L_OUTPUT UTL_FILE.FILE_TYPE;
L_THECURSOR INTEGER DEFAULT DBMS_SQL.OPEN_CURSOR;
L_COLUMNVALUE VARCHAR2(4000);
L_STATUS INTEGER;
L_COLCNT NUMBER := 0;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;
P_MAX_LINESIZE NUMBER := 32000;
BEGIN
--OPEN FILE
L_OUTPUT := UTL_FILE.FOPEN(P_DIR, P_FILENAME, 'W', P_MAX_LINESIZE);
--DEFINE DATE FORMAT
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_FORMAT=''YYYY-MM-DD HH24:MI:SS''';
--OPEN CURSOR
DBMS_SQL.PARSE( L_THECURSOR, P_QUERY, DBMS_SQL.NATIVE );
DBMS_SQL.DESCRIBE_COLUMNS( L_THECURSOR, L_COLCNT, L_DESCTBL );
--DUMP TABLE COLUMN NAME
FOR I IN 1 .. L_COLCNT LOOP
-- UTL_FILE.PUT( L_OUTPUT, L_DESCTBL(I).COL_NAME || ' ' );
DBMS_SQL.DEFINE_COLUMN( L_THECURSOR, I, L_COLUMNVALUE, 4000 );
L_SEPARATOR := '';
END LOOP;
-- UTL_FILE.NEW_LINE( L_OUTPUT );
--EXECUTE THE QUERY STATEMENT
L_STATUS := DBMS_SQL.EXECUTE(L_THECURSOR);
--DUMP TABLE COLUMN VALUE
WHILE ( DBMS_SQL.FETCH_ROWS(L_THECURSOR) > 0 ) LOOP
L_SEPARATOR := ',';
FOR I IN 1 .. L_COLCNT LOOP
if i< L_COLCNT then
begin
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
UTL_FILE.PUT( L_OUTPUT,
TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE,',',' ')));
UTL_FILE.PUT(L_OUTPUT,',');
end;
else
begin
DBMS_SQL.COLUMN_VALUE( L_THECURSOR, I, L_COLUMNVALUE );
UTL_FILE.PUT( L_OUTPUT,
TRIM(BOTH ' ' FROM REPLACE(L_COLUMNVALUE,',',' ')));
end;
end if;
L_SEPARATOR := ',';
END LOOP;
UTL_FILE.NEW_LINE( L_OUTPUT );
END LOOP;
--CLOSE CURSOR
DBMS_SQL.CLOSE_CURSOR(L_THECURSOR);
--CLOSE FILE
UTL_FILE.FCLOSE( L_OUTPUT );
END;
运行存储过程:
SQL> exec exp_data('select * from A000067','c:\logmnr','A000067.txt');
PL/SQL procedure successfully completed
到C:\logmnr目录下可以发现生成了A000067.txt文件
SQL> exec exp_data('select * from A000067','c:\logmnr','A000067.csv');
PL/SQL procedure successfully completed
到C:\logmnr目录下可以发现生成了A000067.csv文件
使用Oracle编写存储过程导入数据的存储过程为:
A000067表结构为:
SQL> desc A000067;
Name Type Nullable Default Comments
------------- ------------- -------- ------- --------
ID NUMBER(38) Y
GETINFOTIME DATE Y
INSERTTIME DATE Y
WATERHEIGHT FLOAT Y
WATERSPEED FLOAT Y
LISTENERPOWER FLOAT Y
WEATHER NVARCHAR2(10) Y
TEMPERATURE NVARCHAR2(10) Y
WINDGRADE NVARCHAR2(10) Y
WINDDIRECTION NVARCHAR2(10) Y
HUMIDITY NVARCHAR2(10) Y
RAINSPEED NVARCHAR2(10) Y
RAINORNOT NUMBER(38) Y
使用Oracle编写存储过程导入数据的存储过程为
create or replace procedure IMPDATAA000067 (p_path varchar2,
p_filename varchar2) as
v_filehandle utl_file.file_type; --定义一个文件句柄
v_text varchar2(1000); --存放文本
V_ID A000067.ID%type;
V_GETINFOTIME A000067.GETINFOTIME%type;
V__INSERTTIME A000067.INSERTTIME%type;
V_WATERHEIGHT A000067.WATERHEIGHT%type;
V_WATERSPEED A000067.WATERSPEED%type;
V_LISTENERPOWER A000067.LISTENERPOWER%type;
V_WEATHER A000067.WEATHER%type;
V_TEMPERATURE A000067.TEMPERATURE%type;
V_WINDGRADE A000067.WINDGRADE%type;
V_WINDDIRECTION A000067.WINDDIRECTION%type;
V_HUMIDITY A000067.HUMIDITY %type;
V_RAINSPEED A000067.RAINSPEED%type;
V_RAINORNOT A000067.RAINORNOT%type;
v_location1 number;
v_location2 number;
v_location3 number;
v_location4 number;
v_location5 number;
v_location6 number;
v_location7 number;
v_location8 number;
v_location9 number;
v_location10 number;
v_location11 number;
v_location12 number;
v_totalinserted number;
begin
if (p_path is null or p_filename is null) then
goto to_end;
end if;
v_totalinserted := 0;
/*open specified file*/
v_filehandle := utl_file.fopen(p_path, p_filename, 'r');
loop
begin
utl_file.get_line(v_filehandle, v_text);
exception
when no_data_found then
exit;
end;
v_location1 := instr(v_text, ',', 1, 1);
v_location2 := instr(v_text, ',', 1, 2);
v_location3 := instr(v_text, ',', 1, 3);
v_location4 := instr(v_text, ',', 1, 4);
v_location5 := instr(v_text, ',', 1, 5);
v_location6 := instr(v_text, ',', 1, 6);
v_location7 := instr(v_text, ',', 1, 7);
v_location8 := instr(v_text, ',', 1, 8);
v_location9 := instr(v_text, ',', 1, 9);
v_location10 := instr(v_text, ',', 1,10);
v_location11:= instr(v_text, ',', 1, 11);
v_location12 := instr(v_text, ',', 1, 12);
V_ID := substr(v_text, 1, v_location1 - 1);
V_GETINFOTIME :=to_date(substr(v_text, v_location1 + 1,
v_location2 - v_location1 - 1),'yyyy-MM-dd HH24:MI:SS');
V__INSERTTIME :=to_date(substr(v_text,v_location2 + 1,v_location3-v_location2 - 1),'yyyy-MM-dd HH24:MI:SS');
V_WATERHEIGHT := to_number(substr(v_text, v_location3+1,v_location4-v_location3-1));
V_WATERSPEED := to_number(substr(v_text, v_location4+1,v_location5-v_location4-1));
V_LISTENERPOWER := to_number(substr(v_text, v_location5+1,v_location6-v_location5-1));
V_WEATHER := substr(v_text, v_location6+1,v_location7-v_location6-1);
V_TEMPERATURE := substr(v_text, v_location7+1,v_location8-v_location7-1);
V_WINDGRADE := substr(v_text, v_location8+1,v_location9-v_location8-1);
V_WINDDIRECTION := substr(v_text, v_location9+1,v_location10-v_location9-1);
V_HUMIDITY := substr(v_text, v_location10+1,v_location11-v_location10-1);
V_RAINSPEED := substr(v_text, v_location11+1,v_location12-v_location11-1);
V_RAINORNOT := to_number(substr(v_text,v_location12+1));
/*插入数据库操作*/
insert into A000067 values (V_ID,V_GETINFOTIME,V__INSERTTIME,V_WATERHEIGHT,V_WATERSPEED,V_LISTENERPOWER,V_WEATHER,V_TEMPERATURE,V_WINDGRADE,V_WINDDIRECTION,V_HUMIDITY,V_RAINSPEED,V_RAINORNOT);
commit;
end loop;
<<to_end>>
null;
end ;
测试存储过程
create or replace procedure IMP_DATATEST AS
v_path varchar2(200);
v_filename varchar2(200);
begin
v_path := 'C:\logmnr';
v_filename := 'A000067.txt';
IMPDATAA000067(v_path, v_filename);
end;
运行效果
SQL> select count(*) from A000067;
COUNT(*)
----------
312
SQL> exec imp_datatest;
PL/SQL procedure successfully completed
SQL> select count(*) from A000067;
COUNT(*)
----------
468
- http://blog.csdn.net/wuxiaokaixinguo/article/details/8016147
- http://blog.csdn.net/IBM_hoojo/article/details/5688947
- http://blog.csdn.net/chenlaic/article/details/6143235
- http://blog.csdn.net/eaglewood2005/article/details/4335052
- http://blog.csdn.net/mchp/article/details/3995970
- http://blog.csdn.net/v_july_v/article/details/6015165
- http://blog.csdn.net/masterz/article/details/6232585
- http://blog.csdn.net/perfectpdl/article/details/6442847
- http://blog.csdn.net/eroswang/article/details/1967243
- http://blog.csdn.net/zhvsby/article/details/5986645
- http://blog.csdn.net/dz45693/article/details/6183645
- http://blog.csdn.net/david_lv/article/details/5798003
- http://blog.csdn.net/zxingchao2009/article/details/6299313
- http://blog.csdn.net/zhanxinhang/article/details/6783766
- http://blog.csdn.net/ruanruoshi/article/details/935510
- http://blog.csdn.net/yming0221/article/details/6538527
- http://blog.csdn.net/yming0221/article/details/6528490
- http://blog.csdn.net/yming0221/article/details/6704079
- 7-1打印复数
- HOG方向梯度直方图
- 聚来宝 网购达人五大省钱秘笈
- JDBC 大二进制存取
- 设计模式之观察者模式
- http://blog.csdn.net/wuxiaokaixinguo/article/details/8016147
- 最小生成树——Kruskal算法 和 Prim算法
- 带缓存的HTTP代理服务器(五)
- 黑马程序员_【总结】_ Pa_银行业务调度系统
- 数据结构学习笔记4-二叉树搜索树
- 数据库批处理
- 异常:finally块 try catch表现形式
- 类与对象
- 8-3. 组个最小数 (20)