目录
图灵小队——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;