DB/Oracle/PLSQL/

Top / DB / Oracle / PLSQL /

ܼ

¹ʸʤץΤν˴ؤäʸդʤ


Ȥε

ɤ˥Ȥ򵭽Ҥ롣

  • -- ԤΥȤϥϥեġԥɤޤǤȰ
    
    /* ʣԤΥȤϤ졣
       ԥɤ̵뤵ΤǤǤ⤫ */
    

Ȥ߹ߴؿ

Oracle ΤȤȻäƤؿϤ PL/SQL ǤȤ롣ñԴؿȤ

DECODE ؿȥ롼״ؿϻȤʤ

  • Ȥ߹ߴؿ
    declare
        hoge varchar2(10) := 'fuga';
        piyo number := length(hoge);
    ...
    

Ѵ

ͤ SQL ΤȻƤ롣ŪѴǤΡʸȿ͡ʸաˤ⤢СŪѴʤФʤΤ⤢롣

ѥեޥ󥹤ͤǤϡŪѴۤᤤ

ŪѴϷɤȤ߹ߤδؿѤʤΤǡǤ񤤤ɡSQL ƱΤȤ衢Ȥá

  • Ѵ
    declare
        -- ŪѴ
        hoge date := '11-11-11'
    
        -- ŪѴ
        fuga varchar2 := to_char(hoge);
    ...
    

11g ϥ󥹤ץʸľܸƤ٤褦ˤʤä餷Ф餷

  • declare
        hoge number;
    begin
        -- 󥹤ľͤ򽦤
        hoge := fuga.nextval;
    
        -- 10g Ǥ select Ѥɬפä餷
        select fuga.nextval into hoge from dual;
    end;
    /
    

֥åΥͥ

¹Ǥϡ֥åͥȤǤ롣ǤϤǤʤΤա

  • ͥ
    declare
        hoge number;
    begin
        -- ͥȤ
        declare
            fuga number;
        begin
            fuga := 100;
        end;
    end;
    /
    

  • ƥ֥å줿ѿϡҥ֥å黲ȡǤ
    • ƻҴ֤ϻϤΤ褦ʵưҥ֥åƤѿ򹹿Ǥ
  • ƥ֥å줿ѿƱ̾ѿ򡢻ҥ֥åǤǤ
    • Ṳ̄ѿȤưΤǡƥ֥åѿؤαƶϤʤ
    • ȻϤޤϥѿõ
    • ҥ֥åƥ֥åƱ̾ѿ򻲾Ȥϥ٥Ȥ
  • declare
        hoge number := 100;
    begin
        -- ͥȤ
        declare
            fuga number := 200;
        begin
            -- ƥ֥åѿϻҥ֥åǤ⻲ȤǤ
            dbms_output.put_line(hoge);
    
            -- ƥ֥åѿϻҥ֥å鹹Ǥ
            hoge := 300
        end;
    
        -- ҥ֥åѿϿƥ֥åǤϻȤǤʤ
        dbms_output.put_line(fuga);
    
        -- ƥ֥åƻҥ֥åѹѿϿƤǤѹ300 ϡ
        dbms_output.put_line(hoge);
    
    end;
    /
    
  • ٥
    -- ֥å˥٥ղä
    <<hoge>>
    declare
        foo number := 100;
    begin
        -- ҥ֥åˤ٥ղäϲǽ
        <<fuga>>
        declare
            -- ƤλѿƱ̾ѿǤ
            foo number := 200;
        begin
            -- ٥ʤǻȤȥõ200 ϡ
            dbms_output.put_line(foo);
    
            -- ƤΥ٥ղäƱ̾οƤѿ򻲾Ȳǽ100 ϡ
            dbms_output.put_line(hoge.foo);
        end;
    end;
    /
    

Ūˤ SQL Ʊؿ׻뤿 ** 餤ޤȤʤ롣

  • declare
        -- 3  2 Ȥ 9 
        hoge number := 3**2
    ...
    
    • SQL Ǥλؿλ power(n, m)

Last-modified: 2011-11-08 () 17:15:55 (2361d)