Oracle 使用pl/sql将表中的数据读出到文件中

gtxyzz 监控 2023-02-26 821浏览 0

（1）在服务器上创建文件路径及文件

[oracle@redhat errormsg]$ touch test01.txt

（2）在数据库中创建路径及授权

（3）创建存储过程

CREATE OR REPLACE PROCEDURE export_test01 IS
export_handle UTL_FILE.file_type;
BEGIN
export_handle := UTL_FILE.FOPEN('LIJIAMAN_DIR', 'test01.txt', 'w');
FOR x IN (SELECT * FROM lijiaman.test01) LOOP
  UTL_FILE.PUT_LINE(export_handle,x.id || ',' || x.name);
END LOOP;
UTL_FILE.FCLOSE(export_handle);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(SQLERRM, 1, 2000));
END;

（4）创建job，1分钟跑一次

declare
    job1 number;
begin
    sys.dbms_job.submit(job1,'export_test01;',next_date => sysdate,interval =>'sysdate + 1/1440');            
end;

（5）观察job情况

SQL> select * from user_jobs;

       JOB LOG_USER    PRIV_USER   SCHEMA_USER   LAST_DATE   LAST_SEC THIS_DATE   THIS_SEC NEXT_DATE   NEXT_SEC TOTAL_TIME BROKEN INTERVAL            FAILURES   WHAT             
---------- ----------- ----------- ------------- ----------- -------- ----------- -------- ----------- -------- ---------- ------ ------------------  ---------- -----------------
         4 LIJIAMAN    LIJIAMAN    LIJIAMAN      2017/11/22  23:24:32                      2017/11/22  23:25:32          0 N      sysdate + 1/1440    0          export_test01;