[原]Oracle外部表结合游标完成统计一例
近日在邮件系统进行迁移的过程中遇到一个问题，我用Oracle的外部表结合游标将其解决，特记于此文。
问题是这样的，通过开发商的程序，得到一个操作文件 delete_file ，该文件记录了一些操作不步骤，其中的内容如下：
.... ..... ...... sm zh@______.com df /Draft df /Junkmail sm dzt@_____.com df /Draft df /Junkmail sm master@_______.com mm 345 /Drafts mm 344 /Drafts mm 343 /Drafts mm 342 /Drafts df /Draft df /Junkmail sm jtj_yxxf@_________.cn df /Draft df /Junkmail ...... ..... ....
其中 sm命令是指将当前上下文切换到哪个邮箱，mm 命令是移动邮件的命令，现在要统计一下，每个邮箱被移动的邮件各有多少封。譬如，master@_______.com 这个账号被移动的邮件就要4封。
我的思路是，将这个表导入数据库或者作为一个可读取的外部表，然后使用游标自上而下地读取，然后随着游标的移动做统计，算法很简单。
我将操作文件放在Linux上面处理：
[root@mailserver tmp]# pwd /tmp [root@mailserver tmp]# chmod 777 ./mail/ [root@mailserver tmp]# ll 总用量 12 drwxrwxrwx 2 root root 4096 10月 10 22:39 mail srwxr-xr-x 1 root root 0 7月 1 14:43 mapping-root drwxr-xr-x 3 root root 4096 9月 24 10:56 orion drwx------ 2 root root 4096 10月 10 22:24 ssh-coEvb25444 prw------- 1 root root 0 8月 19 17:49 vmware-root.0 [root@mailserver tmp]# cd mail/ [root@mailserver mail]# ls delete_file [root@mailserver mail]# chmod 666 delete_file [root@mailserver mail]# ll 总用量 252 -rw-rw-rw- 1 root root 244758 10月 10 22:58 delete_file
然后在Oracle中给我一个测试用户 dcb_user 给与一定的权限：
sys@mydb(10.168.0.202) SQL> grant create any directory to dcb_user 2 / Grant succeeded. Elapsed: 00:00:00.07
然后创建一个目录及基于文件的外部表：
create or replace directory mail_dir as '/tmp/mail/';
create table delete_file
(
line varchar2(200)
)
organization external
(
type oracle_loader
default directory mail_dir
access parameters
(
records delimited by newline
fields
reject rows with all null fields
)
location
(
'delete_file'
)
)
reject limit unlimited;
至此，就可以使用select 语句查询这个文件了。
我再建一个统计表，用于记录统计信息，很简单：
create table mail_stat ( account varchar2(50) , mail int );
以下是我的统计代码：
CREATE OR REPLACE PROCEDURE delete_state
IS
v_line varchar2(200);
v_account varchar2(50);
CURSOR cur is select line from delete_file;
BEGIN
open cur;
loop
fetch cur into v_line;
EXIT when cur%NOTFOUND;
if instr(v_line,'sm ')=1 then
v_account := REPLACE(v_line,'sm ','');
insert into mail_stat (account,mail) values ( v_account , 0 );
end if;
if instr(v_line,'mm ')=1 then
update mail_stat set mail=mail+1 where account=v_account;
end if;
END LOOP;
CLOSE cur;
COMMIT;
END delete_state ;
经过以上的准备工作之后，我就可以轻松统计我所要的统计信息了。
