DB/Oracle/PLSQL/Îã³°½èÍý
Ìܼ¡†
Îã³°½èÍý¡£¤¨¤¯¤»¤×¤·¤ç¤ó¡£¤Ì¤ë¤Ý¡ª
´ðËÜŪ¤ÊÎã³°½èÍý†
¤¿¤È¤¨¤ÐÌÀ¼¨¥«¡¼¥½¥ë¤ò»ÈÍѤ·¤Ê¤¤ SELECT ... INTO ʸ¤Ç¡¢Ê֤꤬ʣ¿ô¹Ô¤À¤Ã¤¿¾ì¹ç¤ä¡¢0 ·ï¤À¤Ã¤¿¾ì¹ç¤ËÎã³°¡Ê»öÁ°¤ËÄêµÁºÑ¤ß¤ÎÎã³°¤Ç¡¢¤½¤ì¤¾¤ì too_many_rows¡¢no_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_FOUND 1 ¹Ô¤Ç¤¢¤ë¤Ù¤Ì䤤¹ç¤ï¤»¤Î·ë²Ì¤¬ 0 ·ï¤À¤Ã¤¿¤È¤ TOO_MANY_ROWS 1 ¹Ô¤Ç¤¢¤ë¤Ù¤Ì䤤¹ç¤ï¤»¤Î·ë²Ì¤¬Ê£¿ô¹Ô¤À¤Ã¤¿¤È¤ INVALID_CURSOR ̵¸ú¤Ê¥«¡¼¥½¥ëÁàºî¤ò¹Ô¤Ã¤¿¤È¤ ZERO_DIVIDE ¥¼¥í½ü»»¤ò¹Ô¤Ã¤¿¤È¤ DUP_VAL_ON_INDEX ÁÞÆþ¤¹¤ëÃͤ¬½ÅÊ£¤·¤Æ¤¤¤¿¤È¤
»È¤¤Êý†
¾å¤Î´ðËÜŪ¤ÊÎã³°½èÍý ¤¬¤Þ¤µ¤Ë¤½¤ì¡£¤½¤Ã¤Á¤ò¸«¤è¤¦¡£
Èó»öÁ°ÄêµÁÎã³°†
Oracle ¤Î¥¨¥é¡¼ÈÖ¹æ¤Ï¤¢¤ë¤±¤É¡¢ÄêµÁºÑ¤ß¤Î¤è¤¦¤Ë»È¤¤¤ä¤¹¤¤ÊÌ̾¤¬ÉÕ¤±¤é¤ì¤Æ¤¤¤Ê¤¤Îã³°¡£Îã³°½èÍýÉô¤Ç½èÍý¤¹¤ë¤Ë¤Ï¡¢¥¨¥é¡¼ÈÖ¹æ¤ËÂбþ¤·¤¿Ì¾Á°¤ò¼«Ê¬¤Ç¤Ä¤±¤ëɬÍפ¬¤¢¤ë¡£
»È¤¤Êý†
¼ê½ç¤Ï°Ê²¼¤ÎÄ̤ꡣ
- Î㳰̾¤ÎÀë¸À
- Î㳰̾¤È¥¨¥é¡¼ÈÖ¹æ¤Î´ØÏ¢ÉÕ¤±
- Îã³°½èÍýÉô¤Ç¤ÎÍøÍÑ
- »È¤¤Êý
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 ¥æ¡¼¥¶ÄêµÁÎã³° +100 NO_DATA_FOUND ¤ÎÎã³° -N Oracle ¤ÎÊÖ¤¹¥¨¥é¡¼ÈÖ¹æ
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