如何使用PL/SQL读取文本文件数据并逐行传入Oracle存储过程

注意：PL/SQL运行在Oracle数据库服务端，因此C:\file\Datafile.txt需要放在数据库所在的服务器的对应路径下，而非你本地客户端的路径。

首先使用有DBA权限的用户执行以下命令，创建目录对象并授权：

-- 创建目录对象，映射到服务器上的文件存储路径
CREATE OR REPLACE DIRECTORY EMP_FILE_DIR AS 'C:\file';
-- 给实际执行脚本的用户授予目录读写权限，替换为你的业务用户名
GRANT READ, WRITE ON DIRECTORY EMP_FILE_DIR TO 你的业务用户名;

脚本使用Oracle内置的UTL_FILE包读取文本文件，拆分字段后调用存储过程：

DECLARE
  -- 文件操作变量
  v_file UTL_FILE.FILE_TYPE;
  v_line VARCHAR2(1000);
  -- 存储过程入参变量
  v_employeeid NUMBER;
  v_employeename VARCHAR2(100);
  v_salary NUMBER;
  -- 字段分隔符
  v_separator CHAR := ',';
BEGIN
  -- 打开目标文本文件，读模式
  v_file := UTL_FILE.FOPEN('EMP_FILE_DIR', 'Datafile.txt', 'R');
  
  -- 循环逐行读取文件
  LOOP
    BEGIN
      UTL_FILE.GET_LINE(v_file, v_line);
      EXIT WHEN v_line IS NULL;
      
      -- 按逗号拆分每行的三个字段
      v_employeeid := TO_NUMBER(REGEXP_SUBSTR(v_line, '[^'||v_separator||']+', 1, 1));
      v_employeename := REGEXP_SUBSTR(v_line, '[^'||v_separator||']+', 1, 2);
      v_salary := TO_NUMBER(REGEXP_SUBSTR(v_line, '[^'||v_separator||']+', 1, 3));
      
      -- 调用你的存储过程，替换为实际的存储过程名称
      你的存储过程名(
        employeeid => v_employeeid,
        employeename => v_employeename,
        salary => v_salary
      );
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT; -- 文件读取完成，退出循环
      WHEN OTHERS THEN
        -- 异常处理：打印出错行信息，可根据需求调整是否中断执行
        DBMS_OUTPUT.PUT_LINE('处理行失败：'||v_line||'，错误信息：'||SQLERRM);
    END;
  END LOOP;
  
  -- 关闭文件
  UTL_FILE.FCLOSE(v_file);
  DBMS_OUTPUT.PUT_LINE('全部数据处理完成');
EXCEPTION
  WHEN OTHERS THEN
    -- 全局异常处理，避免文件未关闭泄露资源
    IF UTL_FILE.IS_OPEN(v_file) THEN
      UTL_FILE.FCLOSE(v_file);
    END IF;
    RAISE;
END;
/

• 执行脚本前先运行SET SERVEROUTPUT ON;开启日志输出，可查看处理进度和错误信息
• 如果文本文件编码和数据库默认编码不匹配，可以在UTL_FILE.FOPEN的第四个参数指定编码，例如指定GBK编码：UTL_FILE.FOPEN('EMP_FILE_DIR', 'Datafile.txt', 'R', 32767)
• 如果需要记录错误数据，可以自行新增日志表，在异常分支插入报错信息

内容的提问来源于stack exchange，提问作者Vlogs Bengali