oracle 数据库 查询结构拼接输出到本地文件

--① 创建目录
create or replace directory ORACLE_TEST as 'd:\\testtmp';
--② 存储过程
create or replace procedure proc(stuid   tb_student.stuid%type,
                                 stuname tb_student.stuname%type,
                                 stuage  tb_student.stuage%type) as
  cou      number; -- 插入条数
  str      varchar2(255); -- 姓名拼接 
  output   varchar2(30000); -- 输出内容
  file     UTL_FILE.FILE_TYPE;
-- 此处  ORACLE_TEST 一定为大写
  location all_directories.directory_name%TYPE := 'ORACLE_TEST';

begin
  select count(1) into cou from tb_student;
  if cou < 5 then
    insert into tb_student
      (stuid, stuname, stuage)
    values
      (stuid, stuname, stuage);
  else
    dbms_output.put_line('已经插入五条数据了');
    --wmsys.wm_concat 函数的作用是以','连接字符
    select wmsys.wm_concat(stuname) stuname into str from tb_student;

    dbms_output.put_line(str);
    Select replace(str,',','') into str from dual;

    output := str;

    file := UTL_FILE.FOPEN(location, 'oracleTest.txt', 'w');
    UTL_FILE.PUT_LINE(file, output);
    UTL_FILE.FCLOSE(file);
  end if;
end;