DB/Oracle/PLSQL/㳰

Top / DB / Oracle / PLSQL / 㳰

ܼ

㳰פ󡣤̤ݡ


Ū㳰

ȤѤʤ SELECT ... INTO ʸǡ֤꤬ʣԤä䡢0 ä㳰ʻѤߤ㳰ǡ줾 too_many_rowsno_data_foundˤȯ롣Τ褦ʤȤˡPL/SQL EXCEPTION ˽ܤ

  • declare
        ...
    begin
        -- 㳰ȯȤ
        select ... into ...;
    
    -- 㳰ε
    exception
        -- 㳰ϥɥ顣顼̾ȡбƤ򵭽
        -- SELECT ʸʣԤᤵƥ顼ˤʤäȤν
        when too_many_rows then
            ...;
        -- SELECT ʸǹԤᤵʤǥ顼ˤʤäȤν
        when no_data_found then
            ...;
        -- 嵭ʳΤ٤Ƥ㳰бϥɥ
        when others then
            ...;
    end;
    /
    

ջ

  • 顼ȯȤ㳰ǽǤС PL/SQL ֥åΤェλˤʤ롣դ곰Υ顼ȯϡƤӽФʤȤ SQL*Plusˤ˥顼᤹ƤӽФ˥顼᤹ΤϥץȤƤϤХǤʤΤǡ OTHERS 㳰ϥɥȤäۤ褤֤
  • 㳰ǥ顼Ȥ˼¹᤹ȤϤǤʤ̵ʤ顢顼ȯսͥȤƵҤФǤ
    begin
        ...
        -- 㳰ȯ
        begin
            -- 㳰ȯȤ
            select ... into ...;
        exception
            -- ҥ֥å㳰ϻҥ֥å㳰ˤ錄
            when ... then ...;
        end;
        -- ҥ֥å㳰ǥ顼λ
        -- ҥ֥åΤェλˤʤΤǿƤ˽
        ...
    exception
        -- ⤷ҥ֥å㳰㳰ȯ餳椬ư
        ...
    end;
    
  • ³Υǡ١ˤäȯ륨顼 PL/SQL 㳰νоݳȤ¸ߤʤɽФ SELECT ʸȯԻϡPL/SQL ǤϤʤ Oracle Database 饨顼롣Τ褦㳰 OTHERS ϥɥǤ⥭åǤʤ

㳰μ

Ѥߤ㳰Τ̤ۤ㳰桼㳰Ѳǽ

Ѥ㳰ȤϡPL/SQL ˤäƴѰդƤ㳰Τȡ

Ѥ㳰ˤϰʲΤ褦ʤΤ롣ܺ٤ Oracle Oracle Database PL/SQL ե٤򻲾ȡ

  • 㳰̣̾
    NO_DATA_FOUND1 ԤǤ٤䤤碌η̤ 0 äȤ
    TOO_MANY_ROWS1 ԤǤ٤䤤碌η̤ʣԤäȤ
    INVALID_CURSOR̵ʥԤäȤ
    ZERO_DIVIDEԤäȤ
    DUP_VAL_ON_INDEXͤʣƤȤ

Ȥ

Ū㳰 ޤˤ졣ä򸫤褦

Oracle Υ顼ֹϤ뤱ɡѤߤΤ褦˻Ȥ䤹̾դƤʤ㳰㳰ǽˤϡ顼ֹб̾ʬǤĤɬפ롣

Ȥ

ϰʲ̤ꡣ

  1. 㳰̾
  2. 㳰̾ȥ顼ֹδϢդ
  3. 㳰Ǥ
  • Ȥ
    declare
        -- 㳰̾EXCEPTION 
        e_hoge exception;
    
        -- 㳰̾ȡȤơ˥顼ֹ -01400 δϢդ
        pragma exception_init(e_hoge, -01400);
    begin
        ...
        -- 㳰ȯ
        insert into ... ;
        ...
    exception
        -- 顼̾ǥåǤ
        when e_hoge then
            ... ;
    end;
    /
    

桼㳰

桼㳰롼ˤĤȤФͤ 100 ʾǤϤʤ ƤȤȤ

ñꤲƼƽδñʥ桼㳰뤷Oracle ǻȤƤʤ顼ֹղä Oracle ȯ顼Ǥ뤫Τ褦ˤդޤ碌뤳ȤǤ롣

Ȥ

ñϤ

  • ñʥ桼㳰
    declare
        -- 㳰̾
        e_hoge exception;
    begin
        ...
        -- 顼ꤲ뤫ݤȽ
        if sql%notfound then
            -- 顼ꤲ
            raise e_hoge;
        end if;
        ...
    exception
        when e_hoge then
            ... ;
    end;
    / 
    

Oracle Υ顼äݤդޤ碌뤳Ȥǽ顼ֹդơǡ١Υ顼ǽȤäꤲ롣ۤ Oracle 㳰Ʊˡǥ桼㳰ⰷΤǡ߷׾㳰ΤΤڤˤʤ롣

Ȥ륨顼ֹϡ-20000 -20999 Τ줫

  • ȥɥץѤ桼㳰
    declare
        -- ʬȤ㳰ΤǤ򤷤ֹȴϢդ
        e_hoge exception;
        pragma exception_init(e_hoge, -20999);
    begin
        ...
        if sql%notfound then
            -- ȥɥץ򥳡뤷㳰ȯ
            -- ǤդΥ顼ֹȥ顼åϤ
            raise_application_error(-20999, 'ʤ󤫥顼äơ');
        end if;
    exception
        -- ʬȤǽʤ饭åɬ
        when e_hoge then
            ... ;
    end;
    /
    

㳰дؿ

㳰ǻȤʴؿ

SQLCODE

㳰Υ顼ֹ֤ؿ

  • SQLCODE ֤ͤ
    0㳰ȯƤʤ
    1桼㳰
    +100NO_DATA_FOUND 㳰
    -NOracle ֤顼ֹ

SQLERRM

㳰Υ顼å֤ؿΤޤޡ

Ȥ

㳰Ѥ롣SQL ʸľܤδؿ뤳ȤϵĤʤΤǡȤмΥ顼ơ֥ʤɤϡ̤ѿ˳ǼƤȤɬפ롣

  • Ȥ
    declare
        -- 㳰ǻȤѿ
        v_errcode := number;
        v_errmsg  := varchar2(100);
    begin
        ...
    exception
        when others then
            -- 㳰ȯֹȥåϤ
            dbms_output.put_line('ErrCode: ' || sqlcode) ;
            dbms_output.put_line('ErrMsg : ' || sqlerrm) ;
    
            -- 顼ơ֥ͤ
            -- SQL ʸľܤʤΤǰѿ
            v_errcode := sqlcode;
            c_errmsg  := substr(sqlerrm, 1, 50);
            -- ơ֥ˤ֤
            insert into err_log values (v_errcode, v_errmsg);
    end;
    /
    

Ǥϡ顼åĹȤơѿΥХȿȾʬǤ 50 顼åڤФƤ롣VARCHAR2 ǻꤹΤϥХȿǡSUBSTR ǻꤹΤʸʤΤǡܸǤ 100 ХȤѿˤ 50 ʸʤ1 ʸ = 2 ХȡʸɤˤäƤ 3 ХȤʤΤ 33 ʸˡ

ܸĶȤäƤ٤ƤΥ顼åܸǤȤ櫓ǤʤϱѸΤޤޡΤ褦ڤФ򤹤ȡѸΥå 50 ʸ50 ХȡˤڤƤޤ

VARCHAR2 ϲĹʸʤΤ礭ͤꤷƤ򿩤櫓ǤϤʤѤƽʬ礭ͤȤäƤȤб뤫뤤ϥåΥѸˤʤɤƤбǤʤȤʤ

ޤǤ⥨顼Ѹ줬ȾʬǤڤ줿ȤƤ⡢٤Ĥˤ󤸤ʤʤȤϻפɡ


Last-modified: 2011-11-09 () 11:50:31 (2447d)