DB/Oracle/PLSQL/

Top / DB / Oracle / PLSQL /

ܼ


μ

äƤΤϡΰФݥ󥿡ŪˤϡSQL ʸ¹ԤȤη̥åȤФ롢ߤνоݤǤԤؤΥݥ󥿤ΤȡȰۥ뤬롣ɽ򤴤ˤ礴ˤ礹ȤˡɤιԤ򸫤Ƥ뤫ȤŪ˻äƤ褦ʴ

ۥ뤬롣

  • μ
    ۥDML SELECT ʸμ¹ԻˡPL/SQL 󤬾˺륫
    ץ񤯤ȤȤˤƤȻȤΥ롣 SELECT ʸη̤ʣԤˤʤȤ˻Ȥ

ۥ

DML SELECT ʸȯԻPL/SQL ˤäƾ˺ƻȤ˴륫롣ɬפ˱ƾΤǡưϰռɬפϤʤ

ľ SQL ʸμ¹Է̤˴ؤ SQL °ȤƤĤΤǡϳѤǤ롣򻲾Ȥ뤳Ȥǡľ SQL ʸɤΤ褦˽줿狼롣

°

  • SQL °μ
    °
    SQL%FOUND֡뷿ľ SQL ʸ 1 ԰ʾ֤äƤ TRUE
    SQL%NOTFOUND֡뷿ľ SQL ʸ 1 Ԥ֤äƤʤä TRUE
    SQL%ROWCOUNTľ SQL ʸDZƶԿ
  • SQL °
    begin
        update set ...
    
        -- ľ SQL ʸʤξ UPDATEˤDzԹ줿ɽ
        dbms_output.put_line(sql%rowcount);
    ...
    

ץޤŪ롢̥åȤФݥ󥿡

̾SQL ǤϹñ̤ǤǤʤΤǡʣԤ᤹䤤碌ʤɤˤŪ˽оݤȤԤꤷʤȤʤ

äȤȤϰʲ

    • CORSOR hoge IS SELECT ...;
  1. Υץ
    • OPEN hoge;
      • ˴Ϣդ줿 SQL ʸ¹
      • ̥åȤΰܤإå
  2. եå
    • FETCH hoge INTO fuga;
      • ΤԤΥǡѿسǼ
      • 򼡤ιԤذư
  3. Υ
    • CLOSE hoge;
      • ȷ̥åȤβ

Ūʽ

ǡǤդ SELECT ʸ򥫡ȴϢդǤ롣ѿǼѤ INTO ϴޤʤɬפ˱ ORDER BY ǥȤʷ̥åȤΥȤϤǤʤ༡ưǤʤˡ

  • declare
        -- ˼¹Ԥ SQL ʸޤ
        cursor c_hoge is
          select foo, bar, baz from fuga;
    
        -- ǼѤѿѰդƤ
        v_foo fuga.foo%type;
        v_bar fuga.bar%type;
        v_baz fuga.baz%type;
    
        -- η̤˹碌쥳ѿǽ
        v_record c_hoge%rowtype;
    begin
        -- Υץ
        open c_hoge;
    
        -- ̤٤ƤФƤΥ롼׽
        loop
            -- ΤԤΥǡѿ˳Ǽ
            fetch c_hoge into v_foo, v_bar, v_baz;
    
            -- ԤʤХ롼פλ
            exit when c_hoge%notfound;
            
            -- Ԥäν
            ...
        end loop;
    end;
    /
    

ѥ᡼ղ

˥ѥ᡼ղäơץ󤹤ȤͤϤ롣ץϤѥ᡼Ѥ뤳Ȥǡ¹Ԥ SQL ʸͤưŪѹǤ롣

WHERE Ϳͤʤɤ򥪡ץ󤹤뤿ӤưŪѹǤΤǡƱѤǤƸΨɤ

  • ѥ᡼ղ
    declare
        -- ˥ѥ᡼⵭Ҥ
        cursor c_hoge (p_hoge number, p_fuga varchar2) is
          select *
            from foo
            -- SQL ʸ˥ѥ᡼Ǥ
            where bar = p_hoge
            and baz = p_fuga;
    begin
        -- ץ˰äݤͤϤ
        -- Ǥ "where bar = 100 and baz = 'Hoge'" ȤƼ¹Ԥ
        open c_hoge (100, 'Hoge');
    
        -- ƺƥץ󤹤Фޤ㤦ͤϤ
        -- ٤ "where bar = 101 and baz = 'Fuga'" ȤƼ¹Ԥ
        close c_hoge;
        open c_hoge (101, 'Fuga');
    end;
    /
    

°

ۥˤ⤢ä°ˡISOPEN ɲäƤ롣

%NOTFOUND %ROWCOUNT 褯Ȥ%ISOPEN ϥκƥץξ֥åʤɤ˻Ѥ롣Ȥ⤢뤫⤷ʤ

  • °μ
    °
    %ISOPEN֡뷿뤬ץ󤷤Ƥ TRUE
    %FOUND֡뷿ǿΥեå 1 ԰ʾ֤äƤ TRUE
    %NOTFOUND֡뷿ǿΥեå 1 Ԥ֤äƤʤä TRUE
    %ROWCOUNTޤǤ˥եåäƤԤι׿
  • °
    begin
        ...
        loop
            ...
            -- 夫 10 Ȥ
            -- OR Ϸ̤ 10 ̤äȤΤΤ 
            exit c_hoge%rowcount > 10 or c_hoge%notfound;
            ...
        end loop;
        ...
    end;
    

FOR 롼

̤Ф FOR EACH ߤʤΡ

ץ󡢥եåλϰŪ˽Τǵס

  • FOR 롼
    declare
        -- ɬ
        cursor c_hoge is
          select ...;
    begin
        -- 쥳ѿ̾ľܻꡣ
        for r_hoge in c_hoge loop
            ...
        end loop;
    end;
    /
    

䤤碌ѤСάǤ롣ɤβΤȡ°Ȥʤʤա

  • 䤤碌Ѥ FOR 롼
    declare
        -- 
    begin
        -- 쥳ѿ̾ľܻꡣ
        for r_hoge in (select ...) loop
            ...
        end loop;
    end;
    /
    

Last-modified: 2011-11-08 () 16:59:58 (2361d)