PLSQL_PLSQL读和写CSV文件方式(案例)

2012-01-06 Created By BaoXinjin

一、摘要


读写CSV的方式

1. 写CSV

Step1. 创建测试目录

Step2. 写CSV文件

Step3. 查看CSV文件

2. 读CSV

Step1. 创建测试表

Step2. 读CSV文件

Step3. 查看表

 

二、写CSV文件


Step1. 创建测试目录

--创建测试目录
CREATE OR REPLACE DIRECTORY bxjcsv AS '/home/oracle/bxjcsv'; 

--目录权限分配
GRANT READ, WRITE ON DIRECTORY bxjcsv TO public;

Step2. 写CSV文件

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;

Step3. 查看CSV文件

 

三、读CSV文件


 Step1. 创建测试表

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

Step2. 读CSV文件

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;

Step3. 查看表

 

Thanks and Regards

posted on 2014-06-24 13:43  东方瀚海  阅读(1904)  评论(0编辑  收藏  举报