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 /
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 /
