SAK }‘ŠÙ

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

Oracle PL/SQL •Ò7 - •W€ƒtƒ@ƒCƒ‹ I/O utl_file.fopenAutl_file.put_line

UTL_FILE ‚ðŽg—p‚·‚é‚ƁAƒeƒLƒXƒgƒtƒ@ƒCƒ‹“üo—Í‚ð PL/SQL ‚ōs‚¦‚éB
(Oracle9i - 9.0.1 ‚Å‹@”\Šg’£‚ ‚è)

¡init.ora Ý’è

  Ej:\oracle\admin\sak\pfile\init.ora ‚É‘Š“–‚·‚é init.ora ‚É“üo—Í‚ð
    ‹–‰Â‚·‚éƒfƒBƒŒƒNƒgƒŠ‚ðŽw’肵‚È‚¢‚Æ UTL_FILE ‚̓Aƒxƒ“ƒh‚·‚éB
    Ž„‚́uŽíXv‚̉ӏŠ‚ɒljÁ‚µ‚½B

###########################################
# ŽíX
###########################################
compatible=9.0.0
db_name=sak
utl_file_dir=g:\tmp,g:\tmp2

  Eutl_file_dir=* ‚Ɓu*v‚ðŽw’è‚·‚é‚Æ–³§ŒÀ‚̃AƒNƒZƒX‚Æ‚È‚éB
    ‚±‚ê‚́A‚«‚¿‚ñ‚ÆŽw’肵‚½•û‚ªˆÀ‘S‚©‚à‚µ‚ê‚È‚¢B
    •¡”‚̃fƒBƒŒƒNƒgƒŠ‚́AƒJƒ“ƒ}u,v‹æØ‚è‚ÅŽw’è‚·‚éB

¡ƒtƒ@ƒCƒ‹ƒI[ƒvƒ“

declare
  fno utl_file.file_type;
begin
  fno := utl_file.fopen('g:\tmp', 'test.txt', 'R', 4002);
  |                      |         |           |   |_ ƒŒƒR[ƒh’·
  |                      |         |           |      ‰üs‚àŠÜ‚ß‚½ƒoƒCƒg’·
  |                      |         |           |      1 ` 32767
  |                      |         |           |      ŒÃ‚¢ Oracle ‚Å‚Í
  |                      |         |           |      Žw’è‚·‚é‚ƃGƒ‰[
  |                      |         |           |_ R Input
  |                      |         |              W Output
  |                      |         |              A Append
  |                      |         |_ ƒtƒ@ƒCƒ‹–¼
  |                      |_ ƒpƒX
  |_ ƒtƒ@ƒCƒ‹”ԍ†

  ...
  ...
end;
/

¡ƒtƒ@ƒCƒ‹ƒNƒ[ƒY

declare
  fno utl_file.file_type;
begin
  fno := utl_file.fopen('g:\tmp', 'test.txt', 'R');
  ...
  ...
  utl_file.fclose(fno);
                  |_ ƒtƒ@ƒCƒ‹”ԍ†
end;
/

¡ƒtƒ@ƒCƒ‹ƒNƒ[ƒY ALL

declare
  fno utl_file.file_type;
begin
  fno := utl_file.fopen('g:\tmp', 'test.txt', 'R');
  ...
  ...
  utl_file.fclose_all;
           |_ ƒI[ƒvƒ“’†‚Ì‘Sƒtƒ@ƒCƒ‹ƒNƒ[ƒY
end;
/

¡ƒtƒ@ƒCƒ‹ƒŠ[ƒh

declare
  fno utl_file.file_type;
  rec varchar2(1000);
begin
  fno := utl_file.fopen('g:\tmp', 'test.txt', 'R');
  begin
    loop
      utl_file.get_line(fno, rec);
                        |    |_ ƒŒƒR[ƒhƒeƒLƒXƒg
                        |_ ƒtƒ@ƒCƒ‹”ԍ†
      ...
      ...
    end loop;
  exception
    when NO_DATA_FOUND then -- ƒŒƒR[ƒh AT END
      null;
    when OTHERS then
      dbms_output.put_line ('** ƒŠ[ƒhƒGƒ‰[ **');
  end;
  utl_file.fclose(fno);
                  |_ ƒtƒ@ƒCƒ‹”ԍ†
end;
/

¡ƒtƒ@ƒCƒ‹ƒ‰ƒCƒg

declare
  fno utl_file.file_type;
  rec varchar2(1000);
begin
  fno := utl_file.fopen('g:\tmp', 'test.txt', 'W');
  rec := 'aaaaaaaaaaaa';
  utl_file.put_line(fno, rec);
                    |    |_ ƒŒƒR[ƒhƒeƒLƒXƒg
                    |_ ƒtƒ@ƒCƒ‹”ԍ†
  utl_file.fclose(fno);
end;
/

¡ƒeƒLƒXƒgƒtƒ@ƒCƒ‹ƒŠ[ƒhƒTƒ“ƒvƒ‹

set serveroutput on;

declare
  fno utl_file.file_type;
  rec varchar2(1000);
begin
  dbms_output.put_line ('start...');
  fno := utl_file.fopen('g:\tmp', 'test.txt', 'R');
  begin
    loop
      utl_file.get_line(fno, rec);
      dbms_output.put_line (rec);
    end loop;
  exception
    when NO_DATA_FOUND then -- ƒŒƒR[ƒh AT END
      null;
    when OTHERS then
      dbms_output.put_line ('** ƒŠ[ƒhƒGƒ‰[ **');
  end;
  utl_file.fclose(fno);
  dbms_output.put_line ('end');
exception
  when OTHERS then
    dbms_output.put_line ('** ƒGƒ‰[ **');
end;
/

¡ƒŒƒR[ƒhƒOƒ‹[ƒuƒV[ƒPƒ“ƒX”ԍ†‚ð•t—^‚µ‚āACSV Œ`Ž®‚ŃeƒLƒXƒtƒ@ƒCƒ‹o—Í

set serveroutput on;

declare
  cursor c1 is select “¾ˆÓæCD, •i”Ô
    from Žó’m order by “¾ˆÓæCD, •i”Ô;
  ct number;
  svtkcd varchar2(20) := ' ';
  fno utl_file.file_type;
  rec varchar2(1000);
begin
  dbms_output.put_line ('start...');
  fno := utl_file.fopen('g:\tmp', 'test.txt', 'W');
  for c1_rec in c1 loop
    if svtkcd != c1_rec.“¾ˆÓæCD then
      ct := 0;
      svtkcd := c1_rec.“¾ˆÓæCD;
    end if;
    ct := ct + 1;
    rec := '"' || ct
      || '","' || c1_rec.“¾ˆÓæCD
      || '","' || c1_rec.•i”Ô
      || '"';
    utl_file.put_line(fno, rec);
  end loop;
  utl_file.fclose(fno);
  dbms_output.put_line ('end');
exception
  when OTHERS then
    dbms_output.put_line ('** ƒGƒ‰[ **');
end;
/

¡Oracle PL/SQL •ÒŽ‘—¿
¡SQL Šî‘bŽÀ’n•ÒŽ‘—¿
¡SQL Šî‘b•ÒŽ‘—¿
¡SQL ƒ`ƒ…[ƒjƒ“ƒO•ÒŽ‘—¿
¡PostgreSQL •ÒAJAVA ServletAJSP •ÒŽ‘—¿
¡MySQL •ÒŽ‘—¿
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ



Copyright (C) 1999-2017 Y.SAK