DB/Oracle/PLSQL/½ñ¤Êý
Ìܼ¡†
¼Â¹Ôʸ¡¢¤¹¤Ê¤ï¤Á¥×¥í¥°¥é¥àËÜÂΤνñ¤Êý¤Ë´Ø¤¹¤ë½ô¡¹¡£½ô¡¹¤Ã¤ÆÊØÍø¤Ê¸ÀÍÕ¤À¤Ê¤¢¡£
¥³¥á¥ó¥È¤Îµ½Ò†
¥½¡¼¥¹¥³¡¼¥É¤ÎÃæ¤Ë¥³¥á¥ó¥È¤òµ½Ò¤¹¤ë¡£
- ¥³¥á¥ó¥È
-- °ì¹Ô¤Î¥³¥á¥ó¥È¤Ï¥Ï¥¤¥Õ¥óÆó¤Ä¡£²þ¹Ô¥³¡¼¥É¤Þ¤Ç¤¬¥³¥á¥ó¥È°·¤¤ /* Ê£¿ô¹Ô¤Î¥³¥á¥ó¥È¤Ï¤³¤ì¡£ ²þ¹Ô¥³¡¼¥É¤Ï̵»ë¤µ¤ì¤ë¤Î¤Ç¤¤¤¯¤é¤Ç¤â¤«¤±¤ë */
ÁȤ߹þ¤ß´Ø¿ô†
Oracle ¤Î¤â¤È¤â¤È»ý¤Ã¤Æ¤¤¤ë´Ø¿ô¤Ï¤À¤¤¤¿¤¤ PL/SQL ¤Ç¤â»È¤¨¤ë¡£Ã±°ì¹Ô´Ø¿ô¤È¤«¡£
¤¿¤À¤·¡¢DECODE ´Ø¿ô¤È¥°¥ë¡¼¥×´Ø¿ô¤Ï»È¤¨¤Ê¤¤¡£
- ÁȤ߹þ¤ß´Ø¿ô
declare hoge varchar2(10) := 'fuga'; piyo number := length(hoge); ...
·¿ÊÑ´¹†
¹Í¤¨Êý¤Ï SQL ¤Î¤È»÷¤Æ¤¤¤ë¡£°ÅÌÛŪ¤ËÊÑ´¹¤Ç¤¤ë¤â¤Î¡Êʸ»ú¤È¿ôÃÍ¡¢Ê¸»ú¤ÈÆüÉաˤ⤢¤ì¤Ð¡¢ÌÀ¼¨Åª¤ËÊÑ´¹¤·¤Ê¤±¤ì¤Ð¤¤¤±¤Ê¤¤¤â¤Î¤â¤¢¤ë¡£
¥Ñ¥Õ¥©¡¼¥Þ¥ó¥¹¤ò¹Í¤¨¤ë¾å¤Ç¤Ï¡¢ÌÀ¼¨Åª¤ËÊÑ´¹¤·¤¿¤Û¤¦¤¬Áᤤ¡£
ÌÀ¼¨Åª·¿ÊÑ´¹¤Ï·ë¶É¤ÏÁȤ߹þ¤ß¤Î´Ø¿ô¤ÎÍøÍѤʤΤǡ¢¾å¤Ç¤â½ñ¤¤¤¿¤±¤É¡¢SQL ¤ÈƱ¤¸¤Î¤¬»È¤¨¤ë¤è¡¢¤È¤¤¤¦Ïá£
- ·¿ÊÑ´¹
declare -- °ÅÌÛŪ·¿ÊÑ´¹ hoge date := '11-11-11' -- ÌÀ¼¨Åª·¿ÊÑ´¹ fuga varchar2 := to_char(hoge); ...
½ç½ø¤ÎÍøÍц
11g ¤«¤é¤Ï¥·¡¼¥±¥ó¥¹¤ò¥×¥í¥·¡¼¥¸¥ãʸ¤ÇľÀܸƤ٤ë¤è¤¦¤Ë¤Ê¤Ã¤¿¤é¤·¤¤¡£¤¹¤Ð¤é¤·¤¤¡£
- ½ç½ø
declare hoge number; begin -- ¥·¡¼¥±¥ó¥¹¤«¤éľÀÜÃͤò½¦¤¨¤ë hoge := fuga.nextval; -- 10g °ÊÁ°¤Ç¤Ï select ¤ò³èÍѤ¹¤ëɬÍפ¬¤¢¤Ã¤¿¤é¤·¤¤ select fuga.nextval into hoge from dual; end; /
¥Ö¥í¥Ã¥¯¤Î¥Í¥¹¥È†
¼Â¹ÔÉô¤Ç¤Ï¡¢¥Ö¥í¥Ã¥¯¤¬¥Í¥¹¥È¤Ç¤¤ë¡£Àë¸ÀÉô¤Ç¤Ï¤Ç¤¤Ê¤¤¤Î¤ÇÃí°Õ¡£
- ¥Í¥¹¥È
declare hoge number; begin -- ¥Í¥¹¥È¤¹¤ë declare fuga number; begin fuga := 100; end; end; /
¥¹¥³¡¼¥×†
- ¿Æ¥Ö¥í¥Ã¥¯¤ÇÀë¸À¤µ¤ì¤¿ÊÑ¿ô¤Ï¡¢»Ò¥Ö¥í¥Ã¥¯¤«¤é»²¾È¡¢¹¹¿·¤Ç¤¤ë
- ¿Æ»Ò´Ö¤Ï»²¾ÈÅϤ·¤Î¤è¤¦¤ÊµóÆ°¡£»Ò¥Ö¥í¥Ã¥¯¤«¤é¿Æ¤ÎÊÑ¿ô¤ò¹¹¿·¤Ç¤¤ë
- ¿Æ¥Ö¥í¥Ã¥¯¤ÇÀë¸À¤µ¤ì¤¿ÊÑ¿ô¤ÈƱ¤¸Ì¾Á°¤ò»ý¤ÄÊÑ¿ô¤ò¡¢»Ò¥Ö¥í¥Ã¥¯¤Ç¤âÀë¸À¤Ç¤¤ë
- ÆâÉôŪ¤ËÊ̤ÎÊÑ¿ô¤È¤·¤Æ°·¤ï¤ì¤ë¤Î¤Ç¡¢¿Æ¥Ö¥í¥Ã¥¯¤ÎÊÑ¿ô¤Ø¤Î±Æ¶Á¤Ï¤Ê¤¤
- »²¾È»þ¤Ï¤Þ¤º¤Ï¥í¡¼¥«¥ëÊÑ¿ô¤«¤éõ¤¹
- »Ò¥Ö¥í¥Ã¥¯¤«¤é¿Æ¥Ö¥í¥Ã¥¯¤ÎƱ̾ÊÑ¿ô¤ò»²¾È¤¹¤ë¾ì¹ç¤Ï¥é¥Ù¥ë¤ò»È¤¦
- ¥¹¥³¡¼¥×
declare hoge number := 100; begin -- ¥Í¥¹¥È¤¹¤ë declare fuga number := 200; begin -- ¿Æ¥Ö¥í¥Ã¥¯¤ÇÀë¸À¤·¤¿ÊÑ¿ô¤Ï»Ò¥Ö¥í¥Ã¥¯¤Ç¤â»²¾È¤Ç¤¤ë dbms_output.put_line(hoge); -- ¿Æ¥Ö¥í¥Ã¥¯¤ÇÀë¸À¤·¤¿ÊÑ¿ô¤Ï»Ò¥Ö¥í¥Ã¥¯¤«¤é¹¹¿·¤Ç¤¤ë hoge := 300 end; -- »Ò¥Ö¥í¥Ã¥¯¤ÇÀë¸À¤·¤¿ÊÑ¿ô¤Ï¿Æ¥Ö¥í¥Ã¥¯¤Ç¤Ï»²¾È¤Ç¤¤Ê¤¤ dbms_output.put_line(fuga); -- ¿Æ¥Ö¥í¥Ã¥¯¤ÇÀë¸À¤·¤Æ»Ò¥Ö¥í¥Ã¥¯¤ÇÊѹ¹¤·¤¿ÊÑ¿ô¤Ï¿Æ¤Ç¤âÊѹ¹¤µ¤ì¤ë¡Ê300 ¤¬½ÐÎÏ¡Ë dbms_output.put_line(hoge); end; /
- ¥é¥Ù¥ë
-- ¥Ö¥í¥Ã¥¯¤Ë¥é¥Ù¥ë¤òÉղ乤ë <<hoge>> declare foo number := 100; begin -- »Ò¥Ö¥í¥Ã¥¯¤Ë¤â¥é¥Ù¥ë¤ÎÉղäϲÄǽ <<fuga>> declare -- ¿Æ¤Î»ý¤ÄÊÑ¿ô¤ÈƱ̾¤ÎÊÑ¿ô¤òÄêµÁ¤Ç¤¤ë foo number := 200; begin -- ¥é¥Ù¥ë¤Ê¤·¤Ç»²¾È¤¹¤ë¤È¥í¡¼¥«¥ë¤òõ¤¹¡Ê200 ¤¬½ÐÎÏ¡Ë dbms_output.put_line(foo); -- ¿Æ¤Î¥é¥Ù¥ë¤òÉղ乤ë¤ÈƱ̾¤Î¿Æ¤ÎÊÑ¿ô¤ò»²¾È²Äǽ¡Ê100 ¤¬½ÐÎÏ¡Ë dbms_output.put_line(hoge.foo); end; end; /
±é»»»Ò†
´ðËÜŪ¤Ë¤Ï SQL ¤ÈƱ¤¸¡£»Ø¿ô¤ò·×»»¤¹¤ë¤¿¤á¤Î ** ¤¬Áý¤¨¤¿¤¯¤é¤¤¡£¤¢¤ó¤Þ¤ê»È¤ï¤Ê¤¤µ¤¤¬¤¹¤ë¡£
- ±é»»»Ò
declare -- 3 ¤Î 2 ¾è¤È¤·¤Æ 9 ¤¬Æþ¤ë hoge number := 3**2 ...
- SQL ¤Ç¤Î»Ø¿ô¤Î»ØÄê¤Ï power(n, m) ¤À¤è
Last-modified: 2011-11-08 (²Ð) 17:15:55