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 •Ò3 - ƒRƒŒƒNƒVƒ‡ƒ“AtableAvarrayAéŒ¾AƒRƒŒƒNƒVƒ‡ƒ“ƒƒ\ƒbƒh

¡PL/SQL ƒRƒŒƒNƒVƒ‡ƒ“

  EƒRƒŒƒNƒVƒ‡ƒ“‚Æ‚µ‚āA
      table   ƒlƒXƒg‚µ‚½•\
      varray  ‰Â•ÏƒTƒCƒY”z—ñ
    ‚ª‚ ‚éB
    varray ‚Í“YŽš‚̏ãŒÀ‚ªŒˆ‚܂邪Atable ‚ɂ͏ãŒÀ‚ª‚È‚¢B
    ‚Ü‚½Atable ‚́A’†”²‚̏ó‘Ô‚É‚Å‚«‚éB

  Etable ‚́ASQL ‚Å‚àˆµ‚¦‚邪Aõˆø•t‚« table ‚͈µ‚¦‚È‚¢B
    (index by binary_integer Žw’è‚Ì‚à‚́B)

¡SQL ‚ł̃RƒŒƒNƒVƒ‡ƒ“éŒ¾

y’PƒŒ^z

create type ttel as table of varchar2(12)
/
create type tadre as object (
  name varchar2(40),
  adre varchar2(200),
  tel  ttel
)
/

y’Pƒ—á varray Œ^ + oo4o ƒtƒB[ƒ‹ƒhŽQÆz

create type ttadre as varray(3) of varchar2(20)
/
create table test (
  item1 char(10),
  item2 char(10),
  adre  ttadre
)
/
insert into test values ('a001', 'b002',
  ttadre('Ž–¼1', 'Ž–¼2', 'Ž–¼3')
)
/

'** oo4o varray Œ^ŽQÆ(“YŽš)
  Dim item As OraCollection

  Do Until rs.EOF
    Print rs(0)
    Print rs(1)
    Set item = rs.Fields("adre").Value
    For i = 1 To item.Size
      Print i & " - " & item(i)
    Next
    rs.MoveNext
  Loop

y’Pƒ—á object Œ^ + oo4o ƒtƒB[ƒ‹ƒhŽQÆz

create type tadre as object (
  name varchar2(40),
  adre varchar2(200),
  tel  varchar2(12)
)
/
create table test (
  item1 char(10),
  item2 char(10),
  adre  tadre
)
/
insert into test values ('a001', 'b002',
    tadre('Ž–¼1', 'ZŠ1', '“d˜b1')
)
/

'** oo4o object Œ^ŽQÆ(–¼‘O)
  Dim item As OraObject

  Do Until rs.EOF
    Print rs(0)
    Print rs(1)
    Set item = rs.Fields("adre").Value
    Print "name - " & item.Name
    Print "adre - " & item.adre
    Print "tel  - " & item.tel
    rs.MoveNext
  Loop

'** oo4o object Œ^ŽQÆ(“YŽš)
  Do Until rs.EOF
    Print rs(0)
    Print rs(1)
    Set item = rs.Fields(2).Value
    For i = 1 To item.Count
      Print i & " - " & item(i)
    Next
    rs.MoveNext
  Loop

y•¡ŽG—á object Œ^A varray Œ^ + oo4o ƒtƒB[ƒ‹ƒhŽQÆz

create type tadre as object (
  name varchar2(40),
  adre varchar2(200),
  tel  varchar2(12)
)
/
create type ttadre as varray(3) of tadre
/
create table test (
  item1 char(10),
  item2 char(10),
  adre  ttadre
)
/
insert into test values ('a001', 'b002',
  ttadre(
    tadre('Ž–¼1', 'ZŠ1', '“d˜b1'),
    tadre('Ž–¼2', 'ZŠ2', '“d˜b2'),
    tadre('Ž–¼3', 'ZŠ3', '“d˜b3')
  )
)
/

'** oo4o varray Œ^Aobject Œ^ŽQÆ(–¼‘O)
  Do Until rs.EOF
    Print rs(0)
    Print rs(1)
    Set item = rs.Fields("adre").Value
    For i = 1 To item.Size
      Set item2 = item(i)
      For j = 1 To item2.Count
        Print i & " - name - " & item2.Name
        Print i & " - adre - " & item2.adre
        Print i & " - tel  - " & item2.tel
      Next
    Next
    rs.MoveNext
  Loop

'** oo4o varray Œ^Aobject Œ^ŽQÆ(“YŽš)
  Do Until rs.EOF
    Print rs(0)
    Print rs(1)
    Set item = rs.Fields(2).Value
    For i = 1 To item.Size
      Set item2 = item(i)
      For j = 1 To item2.Count
        Print i & ", " & j & " - " & item2(j)
      Next
    Next
    rs.MoveNext
  Loop

¡ƒRƒŒƒNƒVƒ‡ƒ“éŒ¾

declare
  type ttel is table of varchar2(12);
  a ttel;
begin
  a := ttel('123-456-7890', '123-456-7890', '123-456-7890');
  a(1) := '100';
end;
/

declare
  type ttel is varray(3) of varchar2(12);
  a ttel;
begin
  a := ttel('123-456-7890', '123-456-7890', '123-456-7890');
  a(1) := '100';
end;
/

declare
  type tjcno is table of Žó’m.Žó’”ԍ†%type;
  jcno tjcno;
  cursor cu is select * from “¾ˆÓæm;
  type tkrec is table of cu%rowtype;
begin
  jcno := tjcno('123456-001', '123456-001', '123456-001');
  jcno(1) := '100';
end;
/

declare
  type ttest is record (
    item1 varchar2(20),
    item2 varchar2(20),
    item3 varchar2(20)
  );
  type tttest is varray(2) of ttest;
  a tttest;
begin
  a := tttest();
end;
/

declare
  type ta is varray(10) of varchar2(10);
  type ttest is record (
    item1 varchar2(20),
    item2 varchar2(20),
    item3 ta
  );
  a ttest;
begin
  a.item3 := ta();
end;
/

¡•s‰Â”\‚Ȑ錾

  EtableA‚Ü‚½‚́Avarray ‚ðX‚É tableA‚Ü‚½‚́Avarray ‚Ɋ܂߂邱‚Ƃ͏o
    —ˆ‚È‚¢B(SQL ‚Ì create type ... as object ‚ÍŠÜ‚ß‚ç‚ê‚éB)

declare
  type ttest is table of varchar2(10);
  type tttest is varray(2) of ttest;
  a tttest;
begin
  a := tttest();
end;
/

declare
  type ta is varray(10) of varchar2(10);
  type ttest is record (
    item1 varchar2(20),
    item2 varchar2(20),
    item3 ta
  );
  type tttest is varray(2) of ttest;
  a tttest;
begin
  a := tttest();
end;
/

¡ƒRƒŒƒNƒVƒ‡ƒ“ƒƒ\ƒbƒh

  ƒRƒŒƒNƒVƒ‡ƒ“–¼.exists(“YŽš)          “YŽš‚ª‘¶Ý‚·‚ê‚Î TRUE
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.count                 —v‘f”
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.limit                 Å‘å—v‘f” (table ‚́ANULL ‚ª•Ô‚é)
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.first                 Å¬“YŽš
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.last                  Å‘å“YŽš
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.prior(n —v‘f)         n —v‘f‘O‚Ì“YŽš
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.next(n —v‘f)          n —v‘fŽŸ‚Ì“YŽš
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.extend(n —v‘f)        n —v‘f’ljÁ (table ‚Ì‚Ý)
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.extend(n —v‘f, “YŽš)  “YŽš‚Ì—v‘f‚ð n —v‘fƒRƒs[ (table ‚Ì‚Ý)
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.trim(n —v‘f)          ÅŒã‚©‚ç n —v‘fíœ (table ‚Ì‚Ý)
  ƒRƒŒƒNƒVƒ‡ƒ“–¼.delete(“YŽš)          “YŽš‚Ì—v‘f‚ðíœ (table ‚Ì‚Ý)

¡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