【20150711】周总结【批量更新trick,UTF_FILE的文件操作,cur与循环

1、批量表更新的trick

使用拼接方法进行表结构转移

将表名存放在一个文本清单中每行一个
使用外表读取文本清单，设置外表名为table_list
使用语句
select 'drop table '||TABLE_NAME||';'||chr(10)
       ||'create table '||TABLE_NAME||' tablespace TEST_DATA'||' as select * from remote_user.'||TABLE_NAME||'@REMOTE_INSTANCE;'        
from table_list;
自动生成更新语句后
将语句复制入指令窗批量执行即可

2、ORACLE中执行文件操作——使用UTF_FILE包

UTL_FILE下的常用函数


使用方式UTL_FILE.function


a.定义文件句柄


v_filehandle     UTL_FILE.file_type;


b.打开文件与关闭文件


  v_filehandle := utl_file.fopen(v_path, v_filename, 'w');--注意PATH定义或输入时要大写！
  utl_file.fclose(v_filehandle);

path 需要预先在库中定义并赋予权限


期间使用v_filehandle文件句柄进行操作

上面的'w'为打开参数，常用的如下

'r' ——read方式打开

'w'——write方式打开，并清除此前的文件记录

'a'——append,write 方式打开，在此前的文件记录前附加信息 --当文件不存在时等同于'w'模式

c.文件读取


使用get_line可逐行读取


e.g. v_filehandle := utl_file.fopen(p_path, p_filename, 'r');
18   loop
19     begin
20       utl_file.get_line(v_filehandle, v_text);
21     exception
22       when no_data_found then
23         exit;
24     end;


每次执行一次读取位置转到下一行


d.文件输出


有put,put_line


其区别为
No line terminator is appended by PUT; 
use PUT_LINE to write a complete line with a line terminator.


UTL_FILE.PUT_line(v_filehandle,'Hello world\n');

3、Cur 与循环的联合应用

使用语句如

 cursor cur is
    select column_name, data_type
      from user_tab_columns
     where upper(table_name) = upper(sta_target)
     order by column_id asc;

定义cursor

使用

line cur%rowtype;

定义一个cursor cur 所读取的row的变量以将cur中的信息取出

使用

open cur;

开始使用cursor，此时从第一条记录开始，每次fetch 将会移动cursor到下一条记录并准备读取

 使用loop进行循环，其方式为执行loop .... end loop;中间的语句。其中需要注意的是队exit 的定义
  loop
    fetch cur into line;
    exit when cur%notfound;

.....

  end loop;

在使用完cursor后需要使用

close cur;

将cursor的状况重置

整个过程如下

 cursor cur is
    select column_name, data_type
      from user_tab_columns
     where upper(table_name) = upper(sta_target)
     order by column_id asc;

  line cur%rowtype;

  open cur;

  loop
    fetch cur into line;
    exit when cur%notfound;

.....（读取line.column进行操作）

  end loop;

close cur;