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 ʸ¤Ç±Æ¶Á¤ò¼õ¤±¤¿¹Ô¿ô
- SQL ¥«¡¼¥½¥ë°À
ÌÀ¼¨¥«¡¼¥½¥ë †
¥×¥í¥°¥é¥Þ¤¬ÌÀ¼¨Åª¤ËÀ¸À®¤·¤ÆÁàºî¤¹¤ë¡¢·ë²Ì¥»¥Ã¥È¤ËÂФ¹¤ë¥Ý¥¤¥ó¥¿¡£
Ä̾SQL ¤Ç¤Ï¹Ôñ°Ì¤Ç¤·¤«Áàºî¤Ç¤¤Ê¤¤¤Î¤Ç¡¢Ê£¿ô¹Ô¤òÌ᤹Ì䤤¹ç¤ï¤»¤ò½èÍý¤¹¤ë¾ì¹ç¤Ê¤É¤Ë¤ÏÌÀ¼¨Åª¤Ë½èÍýÂоݤȤ¹¤ë¹Ô¤ò»ØÄꤷ¤Ê¤¤¤È¤¤¤±¤Ê¤¤¡£
¤¶¤Ã¤¯¤ê¤È¤·¤¿»È¤¤Êý¤Ï°Ê²¼¡£
- ¥«¡¼¥½¥ë¤ÎÀë¸À
- CORSOR hoge IS SELECT ...;
- ¥«¡¼¥½¥ë¤Î¥ª¡¼¥×¥ó
- OPEN hoge;
- ¥«¡¼¥½¥ë¤Ë´ØÏ¢ÉÕ¤±¤é¤ì¤¿ SQL ʸ¤ò¼Â¹Ô
- ¥«¡¼¥½¥ë¤ò·ë²Ì¥»¥Ã¥È¤Î°ì¹ÔÌܤإ»¥Ã¥È
- OPEN hoge;
- ¥Õ¥§¥Ã¥Á
- FETCH hoge INTO fuga;
- ¥«¡¼¥½¥ë¤Î¤¢¤ë¹Ô¤Î¥Ç¡¼¥¿¤òÊÑ¿ô¤Ø³ÊǼ
- ¥«¡¼¥½¥ë¤ò¼¡¤Î¹Ô¤Ø°Üư
- FETCH hoge INTO fuga;
- ¥«¡¼¥½¥ë¤Î¥¯¥í¡¼¥º
- CLOSE hoge;
- ¥«¡¼¥½¥ë¤È·ë²Ì¥»¥Ã¥È¤Î²òÊü
- CLOSE hoge;
´ðËÜŪ¤Ê½ñ¤Êý †
¥«¡¼¥½¥ë¤ÎÀë¸À¤Ç¡¢Ç¤°Õ¤Î SELECT ʸ¤ò¥«¡¼¥½¥ë¤È´ØÏ¢ÉÕ¤±»ØÄê¤Ç¤¤ë¡£¤¿¤À¤·ÊÑ¿ô³ÊǼÍѤΠINTO ¤Ï´Þ¤á¤Ê¤¤¡£É¬Íפ˱þ¤¸¤Æ ORDER BY ¶ç¤Ç¥½¡¼¥È¤¹¤ë¡Ê·ë²Ì¥»¥Ã¥È¤Î¥½¡¼¥È¤Ï¤Ç¤¤Ê¤¤¤¦¤¨¡¢¥«¡¼¥½¥ë¤ÏÃ༡°Üư¤·¤«¤Ç¤¤Ê¤¤¤¿¤á¡Ë¡£
- ÌÀ¼¨¥«¡¼¥½¥ë
¥Ñ¥é¥á¡¼¥¿¤ÎÉղà †
¥«¡¼¥½¥ë¤Ë¥Ñ¥é¥á¡¼¥¿¤òÉղ䷤ơ¢¥ª¡¼¥×¥ó¤¹¤ë¤È¤¤ËÃͤòÅϤ»¤ë¡£¥ª¡¼¥×¥ó»þ¤ËÅϤ¹¥Ñ¥é¥á¡¼¥¿¤òÊѤ¨¤ë¤³¤È¤Ç¡¢¼Â¹Ô¤¹¤ë SQL Ê¸Ãæ¤ÎÃͤòưŪ¤ËÊѹ¹¤Ç¤¤ë¡£
WHERE ¶ç¤ËÍ¿¤¨¤ëÃͤʤɤò¥ª¡¼¥×¥ó¤¹¤ë¤¿¤Ó¤ËưŪ¤ËÊѹ¹¤Ç¤¤ë¤Î¤Ç¡¢Æ±¤¸¥«¡¼¥½¥ë¤òºÆÍøÍѤǤ¤Æ¸úΨ¤âÎɤ¤¡£
- ¥Ñ¥é¥á¡¼¥¿¤ÎÉÕ²Ã
¥«¡¼¥½¥ë¤Î°À †
°ÅÌÛ¥«¡¼¥½¥ë¤Ë¤â¤¢¤Ã¤¿Â°À¤Ë¡¢ISOPEN ¤¬Äɲäµ¤ì¤Æ¤¤¤ë¡£
%NOTFOUND ¤È %ROWCOUNT ¤ò¤è¤¯»È¤¦¡£%ISOPEN ¤Ï¥«¡¼¥½¥ë¤ÎºÆ¥ª¡¼¥×¥óÁ°¤Î¾õÂÖ¥Á¥§¥Ã¥¯¤Ê¤É¤Ë»ÈÍѤ¹¤ë¡£¤³¤È¤â¤¢¤ë¤«¤â¤·¤ì¤Ê¤¤¡£
- ÌÀ¼¨¥«¡¼¥½¥ë°À¤Î¼ïÎà
°À ÀâÌÀ %ISOPEN ¥Ö¡¼¥ë·¿¡£¥«¡¼¥½¥ë¤¬¥ª¡¼¥×¥ó¤·¤Æ¤¤¤ì¤Ð TRUE %FOUND ¥Ö¡¼¥ë·¿¡£ºÇ¿·¤Î¥Õ¥§¥Ã¥Á¤Ç 1 ¹Ô°Ê¾åÊ֤äƤ¤¿¾ì¹ç¤Ë TRUE %NOTFOUND ¥Ö¡¼¥ë·¿¡£ºÇ¿·¤Î¥Õ¥§¥Ã¥Á¤Ç 1 ¹Ô¤âÊ֤äƤ³¤Ê¤«¤Ã¤¿¾ì¹ç¤Ë TRUE %ROWCOUNT À°¿ô·¿¡£¤³¤ì¤Þ¤Ç¤Ë¥Õ¥§¥Ã¥Á¤ÇÌá¤Ã¤Æ¤¤¿¹Ô¤Î¹ç·×¿ô
- ÌÀ¼¨¥«¡¼¥½¥ë°À
¥«¡¼¥½¥ë FOR ¥ë¡¼¥× †
¸¡º÷·ë²Ì¤ËÂФ¹¤ë FOR EACH ¤ß¤¿¤¤¤Ê¤â¤Î¡£ÊØÍø¡£
¥ª¡¼¥×¥ó¡¢¥Õ¥§¥Ã¥Á¡¢½ªÎ»¡¢¥¯¥í¡¼¥º¤Ï°ÅÌÛŪ¤Ë½èÍý¤µ¤ì¤ë¤Î¤Çµ½ÒÉÔÍס£
- ¥«¡¼¥½¥ë FOR ¥ë¡¼¥×
ÉûÌ䤤¹ç¤ï¤»¤òÍøÍѤ¹¤ì¤Ð¡¢¥«¡¼¥½¥ëÀë¸À¤¹¤é¾Êά¤Ç¤¤ë¡£¥³¡¼¥É¤Î²ÄÆÉÀ¤¬Íî¤Á¤ë¤Î¤È¡¢¥«¡¼¥½¥ë°À¤¬»È¤¨¤Ê¤¯¤Ê¤ëÅÀ¤ÏÃí°Õ¡£
- ÉûÌ䤤¹ç¤ï¤»¤òÍøÍѤ·¤¿¥«¡¼¥½¥ë FOR ¥ë¡¼¥×
Last-modified: 2011-11-08 (²Ð) 16:59:58