oracle常用1

--exampleByCmpp.txt 在/opt/oracle/proc_log 目录下生成

--先用oracle用户身份登录数据库所在机器创建/opt/oracle/proc_log目录

--再用sys用户登录数据库执行下面两条语句
create or replace directory UTL_FILE_DIR as '/opt/oracle/proc_log';
grant read, write on directory UTL_FILE_DIR to L2104;

--最后用websms用户登录数据库执行下面的存储过程
declare
cursor home is select * from homeuser where rownum <=300000 order by memberid desc ;
cursor corp is select * from corpmember where rownum <=600000 order by memberid desc ;

fileID utl_file.file_type;

rowhome homeuser%rowtype;
myrow corpmember%rowtype;

v_count number(30);
v_count_1 number(20);
v_count_2 number(20);
v_str varchar2(50);
begin

fileID := utl_file.fopen('UTL_FILE_DIR', 'exampleByCmpp.txt', 'w');
v_count :=0;
v_count_1 :=0;
v_count_2 :=0;
open corp;
open home;
loop fetch corp into myrow;
exit when corp%notfound;
v_count_1 := v_count_1 + 1;
v_str := myrow.memberid || chr(9)||chr(9)||chr(9) || myrow.shortnumber;

v_count := v_count + 1;

utl_file.put_line(fileID , v_str);

if v_count_1 >20 then
loop fetch home into rowhome;
exit when home%notfound;
v_count := v_count + 1;
v_count_2 := v_count_2 + 1;
v_str := rowhome.memberid ||chr(9)||chr(9)||chr(9) ||rowhome.shortnumber;
--v_rep := lpad(v_count,12,'0') ||chr(9)||chr(9)||chr(9)|| '86' || rowhome.memberid || chr(9)||chr(9)||chr(9) || lpad(v_count,12,'0');
utl_file.put_line(fileID , v_str);
--utl_file.put_line(fileReport , v_rep);
if v_count_2 > 10 then
v_count_2 :=0;
exit;
end if;

end loop;
v_count_1 := 0;
end if;

end loop;
close corp;
close home;

if utl_file.is_open(fileID) then
utl_file.fclose(fileID);
end if;


exception
when others then
dbms_output.put_line('已写 '||v_count||' 条数据，出现异常，操作结束！');
if utl_file.is_open(fileID) then
utl_file.fclose(fileID);
end if;
end;