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 ʸ¤Ç±Æ¶Á¤ò¼õ¤±¤¿¹Ô¿ô
  • SQL ¥«¡¼¥½¥ë°À­

ÌÀ¼¨¥«¡¼¥½¥ë

¥×¥í¥°¥é¥Þ¤¬ÌÀ¼¨Åª¤ËÀ¸À®¤·¤ÆÁàºî¤¹¤ë¡¢·ë²Ì¥»¥Ã¥È¤ËÂФ¹¤ë¥Ý¥¤¥ó¥¿¡£

Ä̾SQL ¤Ç¤Ï¹Ôñ°Ì¤Ç¤·¤«Áàºî¤Ç¤­¤Ê¤¤¤Î¤Ç¡¢Ê£¿ô¹Ô¤òÌ᤹Ì䤤¹ç¤ï¤»¤ò½èÍý¤¹¤ë¾ì¹ç¤Ê¤É¤Ë¤ÏÌÀ¼¨Åª¤Ë½èÍýÂоݤȤ¹¤ë¹Ô¤ò»ØÄꤷ¤Ê¤¤¤È¤¤¤±¤Ê¤¤¡£

¤¶¤Ã¤¯¤ê¤È¤·¤¿»È¤¤Êý¤Ï°Ê²¼¡£

  1. ¥«¡¼¥½¥ë¤ÎÀë¸À
    • CORSOR hoge IS SELECT ...;
  2. ¥«¡¼¥½¥ë¤Î¥ª¡¼¥×¥ó
    • OPEN hoge;
      • ¥«¡¼¥½¥ë¤Ë´ØÏ¢ÉÕ¤±¤é¤ì¤¿ SQL ʸ¤ò¼Â¹Ô
      • ¥«¡¼¥½¥ë¤ò·ë²Ì¥»¥Ã¥È¤Î°ì¹ÔÌܤإ»¥Ã¥È
  3. ¥Õ¥§¥Ã¥Á
    • FETCH hoge INTO fuga;
      • ¥«¡¼¥½¥ë¤Î¤¢¤ë¹Ô¤Î¥Ç¡¼¥¿¤òÊÑ¿ô¤Ø³ÊǼ
      • ¥«¡¼¥½¥ë¤ò¼¡¤Î¹Ô¤Ø°Üư
  4. ¥«¡¼¥½¥ë¤Î¥¯¥í¡¼¥º
    • 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