图灵小队——Oracle存储过程创建（使用UTL_FILE工具进行文件输出）

图灵小队——Oracle存储过程创建（使用UTL_FILE工具进行文件输出）

一、使用UTL_FILE工具前提条件

1、创建存储过程前进行路径设置

SQL>create or replace directory "dir_dest" as '/home/oracle'; 
SQL>grant read on directory "dir_dest" to sys; 
SQL>grant write on directory "dir_dest" to sys;

二、创建存储过程3种方式

1、方式1—sql直接执行，不带变量

create or replace procedure p_output_test
is 
  p_total number(20);

  p_total_middle varchar(20);
  vFileName varchar2(100); 
  OutputFile UTL_FILE.FILE_TYPE; 
begin 
 OutputFile := utl_file.fopen('dir_dest','count_table.txt','a'); 

 select count(*) into p_total from test.test01;
 p_total_middle:=to_char(p_total)||chr(10);

 select count(*) into p_total from test.test02;
 p_total_middle:=p_total_middle||to_char(p_total)||chr(10);

 utl_file.put(OutputFile,p_total_middle); 
 utl_file.fflush(OutputFile); 
 utl_file.fclose(OutputFile); 
end ; 
/

2、方式2—sql赋值到字符串变量，再执行变量

create or replace procedure p_output_test
is 
  Num_Count number(20);
  str_Count_middle varchar(2000);
  str_Sql varchar(200);
  vFileName varchar2(100); 
  OutputFile UTL_FILE.FILE_TYPE; 
begin 
 OutputFile := utl_file.fopen('dir_dest','count_table.txt','a'); 
 
 str_Sql:='select count(*) from test.test01';
 execute IMMEDIATE  str_Sql  into Num_Count ;
 str_Count_middle:=str_Count_middle||str_Sql||chr(9)||to_char(Num_Count)||chr(10);
  
 str_Sql:='select count(*) from test.test02';
 execute IMMEDIATE  str_Sql  into Num_Count ;
 str_Count_middle:=str_Count_middle||str_Sql||chr(9)||to_char(Num_Count)||chr(10);
 
 utl_file.put(OutputFile,str_Count_middle); 
 utl_file.fflush(OutputFile); 
 utl_file.fclose(OutputFile); 
end ; 
/

3、方式3—sql赋值到字符串变量，并使用数组，一次性执行多个sql语句

create or replace procedure p_1
is 
  Num_Count number(20);
  str_Count_middle varchar(2000);
  str_Sql varchar(200);
  vFileName varchar2(100); 
  OutputFile UTL_FILE.FILE_TYPE; 
  
  -- 声明数组类型
  TYPE ts_varray IS VARRAY(20) OF varchar2(100); --20是最大的下标
  -- 声明数组变量
  vs_varray ts_varray := ts_varray();
  
begin 
  OutputFile := utl_file.fopen('dir_dest','count_table.txt','a'); 
  -- 数组赋值
  -- vs_varray.extend;被注释的话,会报下标越界的错误：ORA-06533: Subscript beyond count 
 vs_varray.extend;  vs_varray(1) := 'select count(*) from test.test01'; 
 vs_varray.extend;  vs_varray(2) := 'select count(*) from test.test02';

  FOR i IN 1 .. 2 LOOP  --输出数组的值
    -- 自增数组大小
    vs_varray.extend; --注释该行的话,会报下标越界的错误
    execute IMMEDIATE  vs_varray(i)  into Num_Count;
    str_Count_middle:=str_Count_middle||vs_varray(i)||chr(9)||to_char(Num_Count)||chr(10);  
 END LOOP;
 
 utl_file.put(OutputFile,str_Count_middle); 
 utl_file.fflush(OutputFile); 
 utl_file.fclose(OutputFile); 
end ; 
/

三、实际使用的脚本：

1、创建

5、执行存储过程

（1）方式1：SQL直接执行

SQL>exec p_output_test

（2）方式2：使用sql文件，将4中内容拷贝的1.sql中

sql>@1.sql

6、回车换行符

（1）回车换行符

chr(10)是换行符
chr(13)是回车

（2）增加换行符

SQL>select ' update ' || table_name ||' set VALID_STATE =''0A'';'||chr(13)||' commit;'from user_tables

（3）删除换行符

SQL>select id,replace(content,to_char(chr(13))||to_char(chr(10)),'_r_n') from fact_content order by content;