--创建测试目录CREATE OR REPLACE DIRECTORY bxjcsv AS ‘/home/oracle/bxjcsv‘; --目录权限分配GRANT READ, WRITE ON DIRECTORY bxjcsv TO public;

DECLARE   f_emp     UTL_FILE.FILE_TYPE;   CURSOR cur_emp   IS      SELECT   first_name,last_name,email,phone_number,salary        FROM   HR.EMPLOYEES
       WHERE   ROWNUM<=10;   rec_emp   cur_emp%ROWTYPE;BEGIN   f_emp :=      UTL_FILE.FOPEN (‘BXJCSV‘,‘EMP_DEPT.CSV‘,‘W‘,32767);   FOR rec_emp IN cur_emp   LOOP      UTL_FILE.PUT (f_emp, rec_emp.first_name);      UTL_FILE.PUT (f_emp, ‘,‘ || rec_emp.last_name);      UTL_FILE.PUT (f_emp, ‘,‘ || rec_emp.email);      UTL_FILE.PUT (f_emp, ‘,‘ || rec_emp.phone_number);      UTL_FILE.PUT (f_emp, ‘,‘ || rec_emp.salary);      UTL_FILE.NEW_LINE (f_emp);   END LOOP;   UTL_FILE.FCLOSE (f_emp);END;

CREATE TABLE hr.bxj_employees (first_name VARCHAR2(50),last_name VARCHAR2(50),email VARCHAR2(100),phone_number VARCHAR2(50),salary NUMBER)

DECLARE   f_emp            UTL_FILE.FILE_TYPE;   v_line           VARCHAR2 (1000);   v_first_name     hr.bxj_employees.first_name%TYPE;   v_last_name      hr.bxj_employees.last_name%TYPE;   v_email          hr.bxj_employees.email%TYPE;   v_phone_number   hr.bxj_employees.phone_number%TYPE;   v_salary         hr.bxj_employees.salary%TYPE;BEGIN   f_emp := UTL_FILE.FOPEN (‘BXJCSV‘, ‘EMP_DEPT.CSV‘, ‘R‘);   IF UTL_FILE.IS_OPEN (f_emp)   THEN      LOOP         BEGIN            UTL_FILE.GET_LINE (f_emp, v_line, 1000);            IF v_line IS NULL            THEN               EXIT;            END IF;            v_first_name := REGEXP_SUBSTR (v_line,‘[^,]+‘,1,1);            v_last_name := REGEXP_SUBSTR (v_line,‘[^,]+‘,1,2);            v_email := REGEXP_SUBSTR (v_line,‘[^,]+‘,1,3);            v_phone_number := REGEXP_SUBSTR (v_line,‘[^,]+‘,1,4);                        v_salary := REGEXP_SUBSTR (v_line,‘[^,]+‘,1,5);            INSERT INTO hr.bxj_employees              VALUES   (v_first_name,v_last_name,v_email,v_phone_number,v_salary);            COMMIT;         EXCEPTION WHEN NO_DATA_FOUND THEN           EXIT;         END;      END LOOP;   END IF;   UTL_FILE.FCLOSE (f_emp);END;