使用存儲過程将資料批量導出為多個csv檔案(ORACLE)
資料庫有如下表結構：
user_info (
user_id NUMBER primary key,
user_name VARCHAR2(200) NOT NULL,
user_age VARCHAR2(80),
create_date DATE,
create_order NUMBER
)
現在有100萬條記錄，要求每1000條記錄導出為一個csv檔案，内容用逗号分隔，共計1000個檔案，
檔案名稱格式為output1.csv，output1.csv……output1000.csv。
CREATE TABLE USER_INFO (
USER_ID NUMBER PRIMARY KEY,
USER_NAME VARCHAR2(200) not null,
USER_AGE VARCHAR2(80),
CREATE_DATE DATE,
CREATE_ORDER NUMBER
)
declare i number;
v_age number;
begin
i:=0;
for i in 1..1000000 loop
–随機生成1~100的數字
select round(mod(dbms_random.value*100,100)) into v_age from dual;
insert into user_info(user_id,user_name,user_age,create_date,create_order)
values(SEQ_ALL_TABLE.NEXTVAL ,’翁林’||i,v_age,sysdate,i);
–每1000行送出一次
if mod(i,1000) = 0 then
commit;
end if;
end loop;
end;
create or replace procedure export_to_csv(
–1、入口參數部分
p_dir varchar2
)is
–2、内部變量部分
v_errorcode varchar2(30);
v_errormsg varchar2(100);
–顯示遊标、一次性将資料全部讀完
cursor mycur is select * from user_info order by user_id; –如果不排序，你将發現寫入結果順序将達不到你的期望,該懂得的。
–行記錄
myrecord user_info%rowtype;
csv_output utl_file.file_type;
out_file_name varchar2(20);
begin_time number;
end_time number;
count_num number;
begin
–3、主體部分
begin_time :=dbms_utility.get_time;
open mycur;
for i in 1..1000 loop
out_file_name := ‘output’||i||’.csv’;
csv_output := utl_file.fopen(p_dir,out_file_name,’W’);
count_num := 0;
while count_num < 1000 loop –每1000行寫入一個檔案
fetch mycur into myrecord;
utl_file.put_line(csv_output
,myrecord.user_id|| ‘,’ ||
myrecord.user_name||’,’||
myrecord.user_age||’,’||
to_char(myrecord.create_date,’YYYY-MM-DD HH24:MI:SS’)||’,’||
myrecord.create_order);
count_num := count_num + 1;
end loop;
utl_file.fclose(csv_output);
end loop;
close mycur;
end_time := dbms_utility.get_time;
dbms_output.put_line(‘total time=’ || (end_time-begin_time)*10 || ‘ms.’);
–4、異常處理
exception
when others then
v_errorcode:=sqlcode;
v_errormsg :=sqlerrm;
rollback;
–輸出異常資訊
dbms_output.put_line(v_errorcode||v_errormsg);
end export_to_csv;
CREATE or replace DIRECTORY MYDIR AS ‘d:\tmp\’;
GRANT READ,WRITE ON DIRECTORY MYDIR TO wenglin;
exec export_to_csv(‘MYDIR’);