pl/sql读写文件 spool和UTL_FILE package

  1 --create a table for cursor
  2 drop table t_cursor;
  3 create table t_cursor(
  4 id integer primary key,
  5 content varchar2(20)
  6 );
  7 
  8 insert into t_cursor values(1,'first');
  9 insert into t_cursor values(2,'second');
 10 
 11 --my first cursor
 12 set timing off;
 13 set serveroutput on;
 14 set echo off            --是否显示执行的命令内容  
 15 set feedback off       --是否显示 *   rows   selected  
 16 set heading off        --是否显示字段的名称
 17 set verify off         --是否显示替代变量被替代前后的语句。fil
 18 set trimspool off      --去字段空格
 19 set pagesize 1000      --页面大小
 20 set linesize 50//linesize设定尽量根据需要来设定，大了生成的文件也大
 21 spool 'd:\test\123.txt';--spool 是一个sql plus命令，而不是一个pl.sql命令，所以只能在pl/sql块外部使用
 22 declare 
 23 v_out_name varchar2(20);
 24 v_out_id integer;
 25 CURSOR my_cursor(v_id integer) is--declare the cursor, please note here "is" was used.
 26 select t.id,t.content from t_cursor t where t.id=v_id;
 27 begin
 28        if my_cursor%isopen=false then
 29            open my_cursor(1);--open the cursor before using cursor
 30        end if;
 31        
 32        fetch my_cursor into v_out_id,v_out_name;
 33        close my_cursor;--close the cursor
 34        --chr(10)chr(13)一个是换行,一个是回车
 35        dbms_output.put_line('get name:'||v_out_name||chr(13)||'get id:'||v_out_id);
 36 end;
 37 /
 38 spool off;
 39 
 40 
 41 
 42 --cursor properties
 43 set echo off;
 44 set serveroutput on;
 45 declare
 46     v_id integer;
 47     v_content varchar2(20);
 48     cursor my_cursor_property IS
 49     select t.id,t.content from t_cursor t;
 50 begin
 51     if my_cursor_property%isopen=false then--cursor%isopen: to judge whether the cursor is open of not
 52         open my_cursor_property;
 53     end if;
 54     fetch my_cursor_property into v_id,v_content;
 55     while my_cursor_property%found--cursor%found: the current record is available or not?
 56     loop
 57         dbms_output.put_line('the '||v_id||' record: id='||v_id||' name='||v_content);
 58         fetch my_cursor_property into v_id,v_content; 
 59         
 60         if my_cursor_property%rowcount=2 then
 61             dbms_output.put_line('the '||v_id||' record: id='||v_id||' name='||v_content);
 62             dbms_output.put_line('the row count has reached 2, it will exit.');
 63             exit;
 64         end if;
 65     end loop;
 66     close my_cursor_property;
 67 end;
 68 /
 69 
 70 --cursor record type
 71 
 72 set serveroutput on;
 73 declare
 74     type record_type is record
 75     (
 76     v_id t_cursor.id%type,
 77     v_content t_cursor.content%type
 78     );
 79     
 80     cursor my_cursor_record is
 81     select * from t_cursor;
 82     
 83     v_info record_type;
 84 begin
 85     if my_cursor_record%isopen=false then
 86         open my_cursor_record;
 87     end if;
 88     
 89     loop
 90         fetch my_cursor_record into v_info;
 91         exit when my_cursor_record%notfound=true;
 92         
 93         dbms_output.put_line('info: '||' id='||v_info.v_id||chr(13)||'content: '||v_info.v_content);
 94         
 95     end loop;
 96     
 97     
 98     close my_cursor_record;
 99 end;
100 /
101 
102 --
103 To accomplish tasks related to reading and writing files to the host operating system, 
104 Oracle includes a pre-defined package called "UTL_FILE."
105 
106 there are two way to resolve the reading and writing files in current operation system
107 1,utl_file_dir-->init.ora,need to restart the database
108 2,create directory dir_name as 'c:sample'
109 
110 --below is a example for create directory
111 1,grant create any directory to scott;(dba)
112 grant create any directory to public;
113 2,create or replace directory test as 'D:\test';
114 3,GRANT read, write ON DIRECTORY test TO PUBLIC;
115 --view the registed directories of local file system
116 select * from all_directories;
117 
118 --http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003526
119 UTL_FILE.FOPEN (
120    location     IN VARCHAR2,
121    filename     IN VARCHAR2,
122    open_mode    IN VARCHAR2,
123    max_linesize IN BINARY_INTEGER) 
124   RETURN file_type;
125   
126 /*
127   location Directory location of file. This string is a directory object name and is case sensitive. 
128   The default is uppercase. 
129   Read privileges must be granted on this directory object for the UTL_FILE user to run FOPEN.
130 */
131 
132 4,run the below scripts: 
133 set serveroutput on;
134 declare
135 f utl_file.file_type;--utl_file package
136 s varchar2(200);
137 begin
138 
139 f := utl_file.fopen('TEST','sample.txt','R');--TEST should be upper here
140 utl_file.get_line(f,s);
141 /*
142 if s is null then--judge whether is '', we should use 'IS null'
143     dbms_output.put_line('successful');
144 else
145     dbms_output.put_line('failed');
146 end if;
147 */
148 loop
149 if s is null then--using "is null"
150 utl_file.get_line(f,s);
151 else
152 exit;
153 end if;
154 end loop;
155 utl_file.fclose(f);
156 dbms_output.put_line(s);
157 
158 
159 f := utl_file.fopen('TEST','sample.txt','a');
160 utl_file.put_line(f,s);
161 utl_file.put_line(f,'xxx');
162 utl_file.fclose(f);
163 end;
164 /