--① 创建目录
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;
oracle 数据库 查询结构拼接输出到本地文件
最新推荐文章于 2022-07-09 09:39:50 发布