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
ERP技术讨论群: 288307890
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建
技术交流,技术讨论,欢迎加入
Technology Blog Created By Oracle ERP - 鲍新建