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 ¥«¡¼¥½¥ë°À
begin update set ... -- ľÁ°¤Î SQL ʸ¡Ê¤³¤Î¾ì¹ç UPDATE¡Ë¤Ç²¿¹Ô¹¹¿·¤µ¤ì¤¿¤«É½¼¨ dbms_output.put_line(sql%rowcount); ...
ÌÀ¼¨¥«¡¼¥½¥ë†
¥×¥í¥°¥é¥Þ¤¬ÌÀ¼¨Åª¤ËÀ¸À®¤·¤ÆÁàºî¤¹¤ë¡¢·ë²Ì¥»¥Ã¥È¤ËÂФ¹¤ë¥Ý¥¤¥ó¥¿¡£
Ä̾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 ¶ç¤Ç¥½¡¼¥È¤¹¤ë¡Ê·ë²Ì¥»¥Ã¥È¤Î¥½¡¼¥È¤Ï¤Ç¤¤Ê¤¤¤¦¤¨¡¢¥«¡¼¥½¥ë¤ÏÃ༡°ÜÆ°¤·¤«¤Ç¤¤Ê¤¤¤¿¤á¡Ë¡£
- ÌÀ¼¨¥«¡¼¥½¥ë
declare -- ¥«¡¼¥½¥ë¤ÎÀë¸À¤Ë¼Â¹Ô¤·¤¿¤¤ SQL ʸ¤ò´Þ¤á¤ë cursor c_hoge is select foo, bar, baz from fuga; -- ³ÊǼ¤¹¤ëÍѤÎÊÑ¿ô¤òÍÑ°Õ¤·¤Æ¤ª¤¯ v_foo fuga.foo%type; v_bar fuga.bar%type; v_baz fuga.baz%type; -- ¥«¡¼¥½¥ë¤Î·ë²Ì¤Ë¹ç¤ï¤»¤¿¥ì¥³¡¼¥ÉÊÑ¿ô¤ÎÀë¸À¤â²Äǽ v_record c_hoge%rowtype; begin -- ¥«¡¼¥½¥ë¤Î¥ª¡¼¥×¥ó open c_hoge; -- ¸¡º÷·ë²Ì¤¹¤Ù¤Æ¤ËÂФ·¤Æ¤Î¥ë¡¼¥×½èÍý loop -- ¥«¡¼¥½¥ë¤Î¤¢¤ë¹Ô¤Î¥Ç¡¼¥¿¤òÊÑ¿ô¤Ë³ÊǼ¤¹¤ë fetch c_hoge into v_foo, v_bar, v_baz; -- ¹Ô¤¬¤Ê¤±¤ì¤Ð¥ë¡¼¥×¤ò½ªÎ»¤¹¤ë exit when c_hoge%notfound; -- ¹Ô¤¬¤¢¤Ã¤¿¾ì¹ç¤Î½èÍý¤ò½ñ¤¯ ... end loop; end; /
¥Ñ¥é¥á¡¼¥¿¤ÎÉղÆ
¥«¡¼¥½¥ë¤Ë¥Ñ¥é¥á¡¼¥¿¤òÉղ䷤ơ¢¥ª¡¼¥×¥ó¤¹¤ë¤È¤¤ËÃͤòÅϤ»¤ë¡£¥ª¡¼¥×¥ó»þ¤ËÅϤ¹¥Ñ¥é¥á¡¼¥¿¤òÊѤ¨¤ë¤³¤È¤Ç¡¢¼Â¹Ô¤¹¤ë SQL ʸÃæ¤ÎÃͤòưŪ¤ËÊѹ¹¤Ç¤¤ë¡£
WHERE ¶ç¤ËÍ¿¤¨¤ëÃͤʤɤò¥ª¡¼¥×¥ó¤¹¤ë¤¿¤Ó¤ËưŪ¤ËÊѹ¹¤Ç¤¤ë¤Î¤Ç¡¢Æ±¤¸¥«¡¼¥½¥ë¤òºÆÍøÍѤǤ¤Æ¸úΨ¤âÎɤ¤¡£
- ¥Ñ¥é¥á¡¼¥¿¤ÎÉÕ²Ã
declare -- ¥«¡¼¥½¥ëÀë¸À»þ¤Ë¥Ñ¥é¥á¡¼¥¿¤âµ½Ò¤¹¤ë cursor c_hoge (p_hoge number, p_fuga varchar2) is select * from foo -- SQL ʸÃæ¤Ë¥Ñ¥é¥á¡¼¥¿¤òËä¤á¹þ¤ó¤Ç¤ª¤¯ where bar = p_hoge and baz = p_fuga; begin -- ¥ª¡¼¥×¥ó»þ¤Ë°ú¿ô¤Ã¤Ý¤¯ÃͤòÅϤ¹ -- ¤³¤ÎÎã¤Ç¤¤¤¨¤Ð "where bar = 100 and baz = 'Hoge'" ¤È¤·¤Æ¼Â¹Ô¤µ¤ì¤ë open c_hoge (100, 'Hoge'); -- ¥¯¥í¡¼¥º¤·¤ÆºÆ¥ª¡¼¥×¥ó¤¹¤ì¤Ð¤Þ¤¿°ã¤¦ÃͤòÅϤ»¤ë -- º£ÅÙ¤Ï "where bar = 101 and baz = 'Fuga'" ¤È¤·¤Æ¼Â¹Ô¤µ¤ì¤ë close c_hoge; open c_hoge (101, 'Fuga'); end; /
¥«¡¼¥½¥ë¤Î°À†
°ÅÌÛ¥«¡¼¥½¥ë¤Ë¤â¤¢¤Ã¤¿Â°À¤Ë¡¢ISOPEN ¤¬Äɲ䵤ì¤Æ¤¤¤ë¡£
%NOTFOUND ¤È %ROWCOUNT ¤ò¤è¤¯»È¤¦¡£%ISOPEN ¤Ï¥«¡¼¥½¥ë¤ÎºÆ¥ª¡¼¥×¥óÁ°¤Î¾õÂÖ¥Á¥§¥Ã¥¯¤Ê¤É¤Ë»ÈÍѤ¹¤ë¡£¤³¤È¤â¤¢¤ë¤«¤â¤·¤ì¤Ê¤¤¡£
- ÌÀ¼¨¥«¡¼¥½¥ë°À¤Î¼ïÎà
°À ÀâÌÀ %ISOPEN ¥Ö¡¼¥ë·¿¡£¥«¡¼¥½¥ë¤¬¥ª¡¼¥×¥ó¤·¤Æ¤¤¤ì¤Ð TRUE %FOUND ¥Ö¡¼¥ë·¿¡£ºÇ¿·¤Î¥Õ¥§¥Ã¥Á¤Ç 1 ¹Ô°Ê¾åÊ֤äƤ¤¿¾ì¹ç¤Ë TRUE %NOTFOUND ¥Ö¡¼¥ë·¿¡£ºÇ¿·¤Î¥Õ¥§¥Ã¥Á¤Ç 1 ¹Ô¤âÊ֤äƤ³¤Ê¤«¤Ã¤¿¾ì¹ç¤Ë TRUE %ROWCOUNT À°¿ô·¿¡£¤³¤ì¤Þ¤Ç¤Ë¥Õ¥§¥Ã¥Á¤ÇÌá¤Ã¤Æ¤¤¿¹Ô¤Î¹ç·×¿ô
- ÌÀ¼¨¥«¡¼¥½¥ë°À
begin ... loop ... -- ¾å¤«¤é 10 ·ï¤À¤±½èÍý¤·¤¿¤¤¤È¤¤ÎÎã -- OR ¾ò·ï¤Ï·ë²Ì¤¬ 10 ·ï̤Ëþ¤À¤Ã¤¿¤È¤¤Î¤¿¤á¤Î¤â¤Î exit c_hoge%rowcount > 10 or c_hoge%notfound; ... end loop; ... end;
¥«¡¼¥½¥ë FOR ¥ë¡¼¥×†
¸¡º÷·ë²Ì¤ËÂФ¹¤ë FOR EACH ¤ß¤¿¤¤¤Ê¤â¤Î¡£ÊØÍø¡£
¥ª¡¼¥×¥ó¡¢¥Õ¥§¥Ã¥Á¡¢½ªÎ»¡¢¥¯¥í¡¼¥º¤Ï°ÅÌÛŪ¤Ë½èÍý¤µ¤ì¤ë¤Î¤Çµ½ÒÉÔÍס£
- ¥«¡¼¥½¥ë FOR ¥ë¡¼¥×
declare -- ¥«¡¼¥½¥ë¤ÎÀë¸À¤ÏɬÍ× cursor c_hoge is select ...; begin -- ¥ì¥³¡¼¥ÉÊÑ¿ô̾¤òľÀÜ»ØÄê¡£Àë¸ÀÉÔÍ× for r_hoge in c_hoge loop ... end loop; end; /
ÉûÌ䤤¹ç¤ï¤»¤òÍøÍѤ¹¤ì¤Ð¡¢¥«¡¼¥½¥ëÀë¸À¤¹¤é¾Êά¤Ç¤¤ë¡£¥³¡¼¥É¤Î²ÄÆÉÀ¤¬Íî¤Á¤ë¤Î¤È¡¢¥«¡¼¥½¥ë°À¤¬»È¤¨¤Ê¤¯¤Ê¤ëÅÀ¤ÏÃí°Õ¡£
- ÉûÌ䤤¹ç¤ï¤»¤òÍøÍѤ·¤¿¥«¡¼¥½¥ë FOR ¥ë¡¼¥×
declare -- ¥«¡¼¥½¥ë¤ÎÀë¸À¤ÏÉÔÍ× begin -- ¥ì¥³¡¼¥ÉÊÑ¿ô̾¤òľÀÜ»ØÄê¡£Àë¸ÀÉÔÍ× for r_hoge in (select ...) loop ... end loop; end; /
Last-modified: 2011-11-08 (²Ð) 16:59:58