多数程序只需要通过SQL和底层数据库进行交互
--有些情况，不可避免的还是会有一些场景，需要从PL/SQL给外部环境发送信息
--或是从一些外部的源读入信息
--这节课介绍下面这些内置包
dbms_output;--用于在屏幕上显示信息
utl_file ;-- 用于读取以及写入操作系统的文件
--显示信息
--oracle提供了dbms_output包，可以利用它来把信息从程序传到一个缓存中
--然后这个缓存可以被其它的PLSQL程序或者被宿主环境读取和操作
--每个用户的会话都会有一个预定大小的DBMS_OUTPUT缓存区，开发人员一般会把这个大小设置成UNLIMITED
--启用DBMS_OUTPUT
--dbms_output的缺省设置是禁用的，因此缺省情况下对put_line和PUT程序的调用会被忽略，缓存仍然是空的
--在SLQ*PLUS中启用 SET SERVEROUTPUT ON SIZE UNLIMITED; --这个命令除了启用控制台输出外，还有一个额外作用 BEGIN DBMS_OUTPUT.enable(buffer_size=> null); end ; / --向缓存中写入行
--PUT_LINE会在文本后面追加一个换行符
--PUT把文本放在缓存中但没有换行符
--如果只是用PUT，输出会留在缓存中，即便调用已经结束。
--可以使用DBMS_OUTPUT.NEW_LINE来刷新缓存
--只要数据库能够把输出隐式的转换成VARCHAR2字符串，这两个程序都能把数据直接传递进去 begin dbms_output.put_line('hi'); dbms_output.put_line(100); dbms_output.put_line(sysdate); dbms_output.put_line(true); end; /
--但是有些常见的PLSQL类型，DBMS_OUTPUT并不直到怎么处理，比如布尔类型
--如果要打印布尔类型的数据，则需要我们自己写一个工具
--工具包 create or replace package ma_utils is procedure put_line(i_bool in boolean); end ma_utils; / create or replace package body ma_utils is procedure put_line(i_bool in boolean) is begin dbms_output.put_line(case i_bool when true then 'true' when false then 'false' else 'null' end); end put_line; end ma_utils; /
--测试 begin -- dbms_output.put_line(true); ma_utils.put_line(true); end ; /
--另外，dbms_output.put_line一次性能够传递的最大字符串是32767个字节，有限
--如果传递的值超过了最大允许范围，就会抛出异常
--如果需要打印长字符串怎么处理呢，可以写个工具，将其折行处理
--从缓存中读取内容
--可以使用GET_LINE或这GET_LINES过程来获取缓存中内容
--GET_LINE过程按照先入先出模式从缓存中提取一行信息，然后返回一个状态值，0代表成功
create or replace package ma_utils is procedure put_line(i_bool in boolean); function next_line return varchar2; end ma_utils; / create or replace package body ma_utils is procedure put_line(i_bool in boolean) is begin dbms_output.put_line(case i_bool when true then 'true' when false then 'false' else 'null' end); end put_line; function next_line return varchar2 is return_value varchar2(32767); status integer; begin dbms_output.get_line(return_value, status); if status = 0 then return return_value else return null; end if; end next_line; end ma_utils; /
-- get_lines 这个过程在一个调用中从缓存中读取多行
--把从缓存中读取的内容放在一个PL/SQL的字符串集合中
--指明想要读取的行数，然后得到这些行
create table log_tab(buffer varchar2(1000)); declare v_buf dbms_output.chararr; v_num pls_integer; begin dbms_output.put_line('hi'); /* loop*/ v_num := 10; dbms_output.get_lines(v_buf, v_num); /* exit when v_buf.count = 0;*/ forall i in v_buf.first .. v_buf.last insert into log_tab values (v_buf(i)); commit; /* end loop;*/ end; / select * from log_tab
--文件的读写
--PL/SQL程序可以利用UTL_FILE这个包来读写操作系统的文件
--只要这些文件可以从数据库实例运行所在的服务器进行访问就行
--安全问题？
--ORACLE允许DBA通过以下两种方式限制哪些文件可以读写
--1.UTL_FILE可以读写由数据库初始化参数UTIL_FILE_DIR指定的目录中的文件
--2.UTL_FILE可以读写通过数据库的“目录”对象指定位置的文件
---UTIL_FILE_DIR
--最新版本的ORACLE这个参数已经很少使用了
--可以这样来指定 UTIL_FILE_DIR = directory; --比如 UTIL_FILE_DIR = /tmp; UTIL_FILE_DIR = /users/test_area; --以上例子启用了文件系统的2个目录 --如果想读写全部的目录 UTIL_FILE_DIR = *; --如果是生产环境，则不应该使用这个选项 --目录设置 --访问不能递归到子目录 UTIL_FILE_DIR = c:group1dev1; --无法打开一个位于c:group1dev1 est子目录下的文件的 --如果是在UNIX或者LINUX系统下不要使用这一条,这会让我们可以读写操作系统的当前目录 UTIL_FILE_DIR = . --不用把目录名字用单引号或者双引号围起来 --目录名不能用分隔符结尾 UTIL_FILE_DIR = c:group1dev1 --打开文件时指定的文件位置，比如 file_id := utl_file.fopen('c:group1dev1','trace.txt','R'); --打开文件时指定的位置必须跟UTIL_FILE_DIR的配置一致，且大小写一致
--使用ORACLE目录
--通过指定文件位置的方式打开文件，这种硬编码的方式应尽量避免
--如果通过声明一个常量或变量，然后把位置赋值给它，这种方式相对比较好
--比如说通过包定义常量，这样对包有执行权限的程序都可以使用这个常量
create or replace package file_util is c_file_location constant varchar2(100):= 'c:group1dev1'; end file_util; declare file_id utl_file.file_type; begin file_id := utl_file.fopen(file_util.c_file_location, 'trace.txt', 'R'); end; /
--更好的方法是使用一个定义在数据库中的模式级别的对象：目录对象
--要创建一个目录对象，需要DBA赋予我们CREATE ANY DIRECTORY的权限
CREATE OR replace DIRECTORY development_dir as 'D:plsql';
--当指定一个目录名字的时候，数据库并不会校验这个位置。只是把这个字符串
--和数据库对象关联在一起
--当在某个调用中，指定了目录名，这个名字不会作为ORACLE对象的名字对待，相反是作为一个大小写敏感
--的字符串对待，换句话说，如果不用大写字符串额形式指定目录名字，操作就会失败
declare v_file utl_file.file_type; begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test.txt', 'R'); --v_file := utl_file.fopen('development_dir', 'test.txt', 'R'); end; / --一旦对象创建成功，就可以赋予特定用户使用这个目录的权限 grant read on directory development_dir to xxxx --可以通过ALL_DIRECTORY来确定当前连接模式可以使用那些目录 SELECT * FROM ALL_DIRECTORIES; --打开文件 --使用UTL_FILE.FOPEN函数打开指定的文件，返回一个文件句柄，我们可以通过这个句柄操作文件 UTL_FILE.FOPEN(location IN VARCHAR2, --文件的位置 filename IN VARCHAR2, --文件名 open_mode IN VARCHAR2, --文件打开模式 max_linesize IN BINARY_INTEGER DEFAULT NULL --这个文件每行最大的字符数，包括换行符，最小值1，最大值32767 ) RETURN UTL_FILE.file_type; --存放管理文件所需要的全部信息 --文件打开模式 --R ： 以只读的方式打开文件 --W : 以替换的模式打开一个文件进行读和写，以这种方式打开文件，文件所有已有的行都会被删除 --A：以追加的模式打开一个文件进行读写，以这种方式打开文件，文件所有已有的行还保留在文件中不变 --新的行会追加在文件最后一行的后面 --使用W和A的模式，可以使用PUT,PUT_LINE，new_line,putf,fflush这些程序进行文件修改 declare v_file utl_file.file_type; begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test.txt', 'R'); end; / declare v_file utl_file.file_type; begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test6.txt', 'A'); end; / declare v_file utl_file.file_type; begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test5.txt', 'W'); end; /
--把文件位置和文件名拼接在一起必须能够代表一个合法的操作系统文件名
--指定的文件位置必须是可以访问的，而且必须是已经存在的；FOPEN不会因为写入一个新文件
--而创建目录或者子目录
--如果希望以读方式打开一个文件，这个文件必须已经存在。如果以写访问方式打开一个文件，
--如果这个文件还不存在会被创建，如果文件已存在，则所有内容会被清空
--没有指定最大的行尺寸，读取或写的最大行长度是1024个字节
--使用IS_OPEN函数判断文件是否已经打开，如果已经打开则返回TRUE否则返回False FUNCTION is_open(file IN file_type) RETURN BOOLEAN; --这个函数不会对文件的状态进行任何操作系统级别的检查，它只是检测ID字段或者文件句柄记录是不是为空 --关闭文件 --使用UTL_FILE.FCLOSE 和UTL_FILE.FCLOSE_ALL两个过程分别关闭指定的文件和我们会话打开的所有文件 PROCEDURE fclose(file IN OUT file_type); --入参是一个IN OUT模式的参数，这是因为文件关闭后，这个过程会把自己记录的ID字段设置成NULL PROCEDURE fclose_all; ---调用这个过程关闭文件，没有一个文件句柄会被标识成已关闭，这样使用IS_OPEN，返回值仍是TRUE --但不能对这些文件进行任何读写操作 ---读取文件 --如果文件已被打开，则可以使用GET_LINE读取一行数据到指定行缓存中 utl_file.get_line(file IN file_type, --文件句柄 buffer OUT VARCHAR2, --放置读出数据的缓存 len IN BINARY_INTEGER DEFAULT NULL); declare v_file utl_file.file_type; v_result varchar2(1024); begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test.txt', 'R'); utl_file.get_line(v_file, v_result); dbms_output.put_line(v_result); end; / --GET_LINE异常：如果读取操作超过了文件末尾，则会抛出no_data_found异常，所以 declare v_file utl_file.file_type; v_result varchar2(1024); begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test.txt', 'R'); loop utl_file.get_line(v_file, v_result); dbms_output.put_line(v_result); end loop; EXCEPTION WHEN NO_DATA_FOUND THEN UTL_FILE.fclose(v_file); end; /
--封装这个方法？
create or replace package file_util is procedure get_next_line(i_file in utl_file.file_type, o_result out varchar2, o_flag out boolean); end file_util; / create or replace package body file_util is procedure get_next_line(i_file in utl_file.file_type, o_result out varchar2, o_flag out boolean) is begin utl_file.get_line(i_file, o_result); o_flag := false; exception when no_data_found then o_result := null; o_flag := true; end get_next_line; end file_util; / --重写上面的例子，哪个方法好？ declare v_file utl_file.file_type; v_result varchar2(1024); flag boolean; begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test.txt', 'R'); loop file_util.get_next_line(v_file, v_result, flag); exit when flag; dbms_output.put_line(v_result); end loop; UTL_FILE.fclose(v_file); end; /
---向文件中写 --UTL_FILE提供了许多向文件中写的过程 utl_file.put PROCEDURE put(file IN file_type, buffer IN VARCHAR2); --向已经打开文件的当前行添加数据，但是不会追加一个行结束符，必须利用 --new_line过程结束当前行，或者PUT_LINE 写入一个带有行结束字符的完整行 utl_file.new_line PROCEDURE new_line(file IN file_type, lines IN NATURAL := 1); --在文件的当前位置插入一个或者多个换行符（缺省是1个） utl_file.put_line PROCEDURE put_line(file IN file_type, --文件句柄 buffer IN VARCHAR2, --要写到文件中的文本，最大尺寸可以是32767 autoflush IN BOOLEAN DEFAULT FALSE); --是否立即刷新到操作系统，是的话使用TRUE --向文件中写入一个文本串，后面跟着一个平台专用的行结束符 utl_file.putf procedure putf(file IN file_type, format IN VARCHAR2, arg1 IN VARCHAR2 DEFAULT NULL, arg2 IN VARCHAR2 DEFAULT NULL, arg3 IN VARCHAR2 DEFAULT NULL, arg4 IN VARCHAR2 DEFAULT NULL, arg5 IN VARCHAR2 DEFAULT NULL); --基于一个模板字符串的格式，向文件中写入最多5个字符串 utl_file.fflush PROCEDURE fflush(file IN file_type); --UTL_FILE通常是写到缓存，FFLUSH会立即把缓存中的文件写到文件系统中 --只有当用W或者A的模式打开文件后，才能使用这些过程 declare v_file utl_file.file_type; v_result varchar2(1024); flag boolean; begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test1.txt', 'W'); FOR V IN (SELECT * FROM MA_USERS) LOOP UTL_FILE.put_line(V_FILE, V.USER_NAME || ',' || V.USER_PHONE); END LOOP; UTL_FILE.fclose(v_file); end; / --向文件中写入格式化文本 procedure putf(file IN file_type, format IN VARCHAR2, arg1 IN VARCHAR2 DEFAULT NULL, arg2 IN VARCHAR2 DEFAULT NULL, arg3 IN VARCHAR2 DEFAULT NULL, arg4 IN VARCHAR2 DEFAULT NULL, arg5 IN VARCHAR2 DEFAULT NULL); --格式%s 告诉PUTF把对于的元素放到文件中，最多可以使用五个%s -- 告诉putf在文件中放一个换行符，可以使用多个，无限制
--看看效果
declare v_file utl_file.file_type; v_result varchar2(1024); flag boolean; begin v_file := utl_file.fopen('DEVELOPMENT_DIR', 'test1.txt', 'W'); FOR V IN (SELECT * FROM MA_USERS) LOOP UTL_FILE.putf(V_FILE, '姓名：%s ,手机号:%s', V.USER_NAME, V.USER_PHONE); UTL_FILE.putf(V_FILE, ' ,邮箱:%s ', V.User_Email); END LOOP; UTL_FILE.fclose(v_file); end; /
--拷贝文件
utl_file.fcopy PROCEDURE fcopy(src_location IN VARCHAR2, --源文件目录 src_filename IN VARCHAR2, --源文件名 dest_location IN VARCHAR2, --目标目录 dest_filename IN VARCHAR2, --目标文件名 start_line IN BINARY_INTEGER DEFAULT 1, --开始行号 end_line IN BINARY_INTEGER DEFAULT NULL); --结束行号 declare v_file utl_file.file_type; v_result varchar2(1024); flag boolean; begin UTL_FILE.fcopy('DEVELOPMENT_DIR', 'test1.txt', 'DEVELOPMENT_DIR', 'test2.txt'); UTL_FILE.fcopy('DEVELOPMENT_DIR', 'test1.txt', 'DEVELOPMENT_DIR', 'test2.txt', 1, 5); end; / --删除文件 utl_file.fremove --通过这个过程删除文件 PROCEDURE fremove(location IN VARCHAR2,filename IN VARCHAR2); --文件改名和文件移动 utl_file.frename PROCEDURE frename(src_location IN VARCHAR2, ---源目录 src_filename IN VARCHAR2, --源文件 dest_location IN VARCHAR2, --目标目录 dest_filename IN VARCHAR2, --目标文件 overwrite IN BOOLEAN DEFAULT FALSE); --是否覆盖 begin UTL_FILE.frename('DEVELOPMENT_DIR', 'test2.txt', 'DEVELOPMENT_DIR', 'test3.txt', false); end; /
--提取文件属性
utl_file.fgetattr PROCEDURE fgetattr(location IN VARCHAR2, --文件目录 filename IN VARCHAR2, --文件名 fexists OUT BOOLEAN, --文件是否存在 file_length OUT NUMBER, ---文件长度 block_size OUT BINARY_INTEGER); --块尺寸 declare v_exists boolean; v_file_length number; v_block_size BINARY_INTEGER; begin UTL_FILE.fgetattr('DEVELOPMENT_DIR', 'test3.txt', v_exists, v_file_length, v_block_size); ma_utils.put_line(v_exists); dbms_output.put_line(v_file_length); dbms_output.put_line(v_block_size); end; /
----------------------------------------------
--创建目录 CREATE OR replace DIRECTORY development_dir as '/s01/oracle/app/oracle/file_test'; --权限 grant read on directory development_dir to test1 --创建测试表 create table utifile_test( create_on date default sysdate, create_by varchar2(20) default 'utifile_test', test_id varchar2(32), test_name varchar2(32), test_message varchar2(32)); --创建文件并插入数据 [oracle@12crac1 file_test]$ cat test_file1.txt abc,123,test1 abd,111,test22 test,vvv,test3 test,222 --创建一个逗号分隔的包 CREATE OR REPLACE PACKAGE file_util_part IS PROCEDURE get_next_char (i_file VARCHAR2, i_position NUMBER, o_result OUT VARCHAR2); END file_util_part; / CREATE OR REPLACE PACKAGE BODY file_util_part IS PROCEDURE get_next_char (i_file VARCHAR2, i_position NUMBER, o_result OUT VARCHAR2) IS v_result_n VARCHAR2 (1024) := ',' || i_file || ','; BEGIN SELECT SUBSTR (v_result_n, INSTR (v_result_n, ',', 1, i_position) + 1, INSTR (v_result_n, ',', 1, i_position + 1) - INSTR (v_result_n, ',', 1, i_position) - 1) AS value_str INTO o_result FROM DUAL; DBMS_OUTPUT.PUT_LINE ('o_result==' || o_result); EXCEPTION WHEN NO_DATA_FOUND THEN o_result := NULL; END get_next_char; END file_util_part; / --测试执行 DECLARE v_file UTL_FILE.file_type; v_result VARCHAR2 (1024); flag BOOLEAN; v_1 utifile_test.test_id%TYPE; v_2 utifile_test.test_name%TYPE; v_3 utifile_test.test_message%TYPE; BEGIN v_file := UTL_FILE.fopen ('DEVELOPMENT_DIR', 'test_file1.txt', 'R'); LOOP file_util.get_next_line (v_file, v_result, flag); EXIT WHEN flag; DBMS_OUTPUT.put_line (v_result); TEST1.FILE_UTIL_PART.GET_NEXT_CHAR (v_result, 1, v_1); TEST1.FILE_UTIL_PART.GET_NEXT_CHAR (v_result, 2, v_2); TEST1.FILE_UTIL_PART.GET_NEXT_CHAR (v_result, 3, v_3); DBMS_OUTPUT.put_line ('v1=='||v_1); DBMS_OUTPUT.put_line ('v2=='||v_2); DBMS_OUTPUT.put_line ('v3=='||v_3); INSERT INTO utifile_test (test_id, test_name, test_message) VALUES (v_1, v_2, v_3); END LOOP; UTL_FILE.fclose (v_file); COMMIT; END; / --结果 select * from utifile_test; 2016/5/20 14:35:34 utifile_test abc 123 test1 2016/5/20 14:35:34 utifile_test abd 111 test22 2016/5/20 14:35:34 utifile_test test vvv test3 2016/5/20 14:35:34 utifile_test test 222