Using Stored Procedures to batch export data into multiple csv files (ORACLE)

Using Stored Procedures to batch export data into multiple csv files (ORACLE) databases has the following table structure: user_info (user_id NUMBER primary key, user_name VARCHAR2 (200) not null, user_age VARCHAR2 (80 ), create_date DATE, create_order hour. /* ============================== Step 1: CREATE a TABLE =================================*/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)/* ===================== Step 2: initialize the data and paste the data in the command line and execute ===================== */declare I number; v_age number; begin I: = 0; for I in 1 .. 1000000 loop -- random generation of 1 ~ 100 numeric 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, 'online' | I, v_age, sysdate, I); -- submit if mod (I, 1000) = 0 then commit; end if; end loop; end;/* =============================== Step 3: the specific process is to query all 1 million records at a time and put them into the cursor. Each 1000 records write a file. After testing, the data initialized in the table is used in the cursor mode, the execution duration is about 40 s. =============================*/Create or replace procedure export_to_csv (-- 1. Entry parameter section p_dir varchar2) is -- 2. The Internal Variable Section v_errorcode varchar2 (30); v_errormsg varchar2 (100); -- displays the cursor and reads all data at a time. cursor mycur is select * from user_info order by user_id; -- if it is not sorted, you will find that the Write result sequence does not meet your expectations. -- Myrecord user_info % rowtype; csv_output records; out_file_name varchar2 (20); begin_time number; end_time number; count_num number; begin -- 3. begin_time: = cursor; 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 -- write a file fetch mycur into myrecord for every 1000 rows; 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 Handling exception when others then v_errorcode: = sqlcode; v_errormsg: = sqlerrm; rollback; -- output exception information failed (v_errorcode | v_errormsg); end export_to_csv; /* =============================== Step 4: Create an output directory, and grant the permission to the relevant user ============================= */CREATE or replace directory mydir as 'd: \ tmp \ '; grant read, write on directory mydir to wenglin;/* ========================= Step 5: run the command line to view the output file. If you want to see the command line output result, run the following command: set serveroutput on ===============================*/exec export_to_csv ('mydir ');