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 |

PostgreSQL •Ò22 - PL/pgSQLAƒJ[ƒ\ƒ‹ for ƒ‹[ƒvAƒJ[ƒ\ƒ‹§ŒäAFETCH

¡select into

  Eselect into ‚́AOracle ‚Ì PL/SQL ‚Æ“¯“™‚Å‚ ‚éB

drop function fnctest1();
create function fnctest1() returns int as
'
declare
  ct int;
begin
  select count(*) into ct from testm;
  raise debug ''count = %'', ct;
  return ct;
end;
'
language 'plpgsql'
;

select fnctest1();

DEBUG:  count = 4
 fnctest1
----------
        4

¡ƒJ[ƒ\ƒ‹ for ƒ‹[ƒv

  Efor ƒ‹[ƒv‚Å‚Ì select ‚ɂ́A’ʏí‚Ì‚à‚̂ƁAexecute ‚ðŽw’肵‚½“®“I‚È
    ‚à‚Ì‚ªŽw’è‚Å‚«‚éB
    ‚±‚ꂪˆê”ÔŠÈ’P‚ɃJ[ƒ\ƒ‹ƒ‹[ƒv‚ðì‚ê‚éB

drop function fnctest1();
create function fnctest1() returns int as
'
declare
  rec record;
begin
  for rec in select * from testm loop
    raise debug ''key = %  data1 = %'', rec.key, rec.data1;
  end loop;
  return 0;
end;
'
language 'plpgsql'
;

select fnctest1();

DEBUG:  key = a001      data1 = 1
DEBUG:  key = a011      data1 = 1
DEBUG:  key = b002      data1 = 10
DEBUG:  key = c003      data1 = 100

  Efor ƒ‹[ƒv‚Å‚Ì select ‚É execute ‚ðŽw’肵‚½ê‡‚́AŽŸ‚̂悤‚É‚È‚éB

drop function fnctest1();
create function fnctest1() returns int as
'
declare
  rec record;
  s varchar(20) := ''testm'';
begin
  for rec in execute ''select * from '' || s loop
    raise debug ''key = %  data1 = %'', rec.key, rec.data1;
  end loop;
  return 0;
end;
'
language 'plpgsql'
;

¡ƒJ[ƒ\ƒ‹§Œä FETCH

  EƒJ[ƒ\ƒ‹§Œä‚É‚àFX‚ȃpƒ^[ƒ“‚ª‚ ‚éB“®“I‚È‚à‚Ì‚Í execute ‚ðŽw’è
    ‚µ‚Ä—˜—p‚·‚éBŽŸ‚́Arefcursor ‚ðŽg—p‚µ‚½—á‚Å‚ ‚éB

drop function fnctest1();
create function fnctest1() returns int as
'
declare
  cu refcursor;
  rec record;
begin
  open cu for select * from testm;
  loop
    fetch cu into rec;
    if not found then
      exit;
    end if;
    raise debug ''key = %  data1 = %'', rec.key, rec.data1;
  end loop;
  close cu;
  return 0;
end;
'
language 'plpgsql'
;

select fnctest1();

DEBUG:  key = a001      data1 = 1
DEBUG:  key = a011      data1 = 1
DEBUG:  key = b002      data1 = 10
DEBUG:  key = c003      data1 = 100

  EŽŸ‚́Acursor for ‚ðŽg—p‚µ‚½—á‚Å‚ ‚éB

drop function fnctest1();
create function fnctest1() returns int as
'
declare
  cu cursor for select * from testm;
  rec record;
begin
  open cu;
  loop
    fetch cu into rec;
    if not found then
      exit;
    end if;
    raise debug ''key = %  data1 = %'', rec.key, rec.data1;
  end loop;
  close cu;
  return 0;
end;
'
language 'plpgsql'
;

  EŽŸ‚́Acursor is ‚ŁAƒJ[ƒ\ƒ‹ƒpƒ‰ƒƒ^‚ðŽg—p‚µ‚½—á‚Å‚ ‚éB

drop function fnctest1();
create function fnctest1() returns int as
'
declare
  cu cursor (sel varchar) is select * from testm where key like sel || ''%'';
  rec record;
begin
  open cu(''a'');
  loop
    fetch cu into rec;
    if not found then
      exit;
    end if;
    raise debug ''key = %  data1 = %'', rec.key, rec.data1;
  end loop;
  close cu;
  return 0;
end;
'
language 'plpgsql'
;

  EŽŸ‚́AƒJ[ƒ\ƒ‹‚Å“®“I SQL ‚ðŽg—p‚µ‚½—á‚Å‚ ‚éB

drop function fnctest1();
create function fnctest1() returns int as
'
declare
  cu refcursor;
  rec record;
  s varchar(20) := ''testm'';
begin
  open cu for execute ''select * from '' || s;
  loop
    fetch cu into rec;
    if not found then
      exit;
    end if;
    raise debug ''key = %  data1 = %'', rec.key, rec.data1;
  end loop;
  close cu;
  return 0;
end;
'
language 'plpgsql'
;

¡PostgreSQL •ÒAJAVA ServletAJSP •ÒŽ‘—¿
¡MySQL •ÒŽ‘—¿
¡SQL Šî‘b•ÒŽ‘—¿
¡SQL Šî‘bŽÀ’n•ÒŽ‘—¿
¡SQL ƒ`ƒ…[ƒjƒ“ƒO•ÒŽ‘—¿
¡Oracle PL/SQL •ÒŽ‘—¿
| 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