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;