DB/Oracle/PLSQL/¥×¥í¥·¡¼¥¸¥ã¤È¥Õ¥¡¥ó¥¯¥·¥ç¥ó
Ìܼ¡†
»È¤¤¼Î¤Æ¤Î̵̾¤Ê¤Î¤¸¤ã¤Ê¤¯¤Æ¡¢Ì¾Á°¤òÉÕ¤±¤ÆÊݸ¤·¤Æ¤ª¤¯¥×¥í¥·¡¼¥¸¥ã¤â¤·¤¯¤Ï¥Õ¥¡¥ó¥¯¥·¥ç¥ó¤Î¤ªÏᣥµ¥Ö¥×¥í¥°¥é¥à¡£
¥×¥í¥·¡¼¥¸¥ã¤È¥Õ¥¡¥ó¥¯¥·¥ç¥ó†
̾Á°¤òÉÕ¤±¤ÆÊݸ¤·¤Æ¤ª¤¯¥µ¥Ö¥×¥í¥°¥é¥à¤Î¤³¤È¡£Êݸ¾ì½ê¤Ï¥í¡¼¥«¥ë¤À¤Ã¤¿¤ê¥µ¡¼¥Ð¤À¤Ã¤¿¤ê¤¹¤ë¡£Ì¾Á°¤òÉÕ¤±¤ÆÊݸ¤·¤Æ¤¢¤ë¤Î¤Ç¡¢Ì¾Á°¤ò»ØÄꤷ¤Æ¤Û¤«¤Î¥¢¥×¥ê¥±¡¼¥·¥ç¥ó¤«¤é¥³¡¼¥ë¡Ê¼Â¹Ô¡Ë¤Ç¤¤ë¡£
ÃͤòÊÖ¤µ¤Ê¤¤¤Î¤¬¥×¥í¥·¡¼¥¸¥ã¤Ç¡¢ÃͤòÊÖ¤¹¤Î¤¬¥Õ¥¡¥ó¥¯¥·¥ç¥ó¡£¥×¥í¥·¡¼¥¸¥ã¤ÎÃ椫¤é¥Õ¥¡¥ó¥¯¥·¥ç¥ó¤ò¸Æ¤Ó½Ð¤¹¡¢¤È¤¤¤¦·Á¤¬Â¿¤¤¡£¤Î¤«¤Ê¤¢¡£
¥×¥í¥·¡¼¥¸¥ã†
´ðËܤι½Ê¸†
¤À¤¤¤¿¤¤ÌµÌ¾¥Ö¥í¥Ã¥¯¤È°ì½ï¡£
- ¥¹¥È¥¢¥É¥×¥í¥·¡¼¥¸¥ã¤Î¹½Ê¸
-- CREATE ¤Ç¥Ç¡¼¥¿¥Ù¡¼¥¹¤Ë¥ª¥Ö¥¸¥§¥¯¥È¤È¤·¤Æ³ÊǼ¤¹¤ë -- OR REPLACE ¤Ï´û¸¤Î¾ì¹ç¤Ë¾å½ñ¤¤¹¤ë»ØÄê create [or replace] -- DECLARE ¤ÎÂå¤ï¤ê¤Ë PROCEDURE ¤ò»È¤¦ -- °ú¿ô¤È¤·¤Æ¼õ¤±¼è¤ë¥Ñ¥é¥á¡¼¥¿¤â»ØÄê²Ä¡£Ç¤°Õ¤Ê¤Î¤Ç¤Ê¤¯¤Æ¤â¤¤¤¤ -- ¥Ñ¥é¥á¡¼¥¿¤Ï [ ¥Ñ¥é¥á¡¼¥¿Ì¾ ¥â¡¼¥É ¥Ç¡¼¥¿·¿ ] ¤Î½ç¡£¥â¡¼¥É¤Ë¤Ä¤¤¤Æ¤Ï¸å½Ò -- ¥Ç¡¼¥¿·¿¤Ë·å¿ô¤ÏÉÔÍ× procedure hoge (v_arg1 out number, v_arg2 in out varchar2) is -- Àë¸ÀÉô ... begin -- ¼Â¹ÔÉô ... exception -- Îã³°½èÍýÉô ... end;
- ¥Ñ¥é¥á¡¼¥¿¤Î¥â¡¼¥É
¥â¡¼¥É ÀâÌÀ in ¸Æ¤Ó½Ð¤·¸µ¤«¤é¼õ¤±¼è¤ë¥Ñ¥é¥á¡¼¥¿¡£¥â¡¼¥É¤ò¾Êά¤¹¤ë¤È¤³¤ì¤Ë¤Ê¤ë out ¸Æ¤Ó½Ð¤·¸µ¤ËÊÖ¤¹¥Ñ¥é¥á¡¼¥¿ in out ¸Æ¤Ó½Ð¤·¸µ¤«¤é¼õ¤±¼è¤Ã¤ÆÊÖ¤¹¥Ñ¥é¥á¡¼¥¿
¼Â¹ÔÊýË¡†
¾¤Î̵̾¥Ö¥í¥Ã¥¯¤ä¥×¥í¥·¡¼¥¸¥ã¤«¤é¼Â¹Ô¤¹¤ë¾ì¹ç¤Ï¡¢¥×¥í¥·¡¼¥¸¥ã̾¤òñ¤Ëµ½Ò¤¹¤ë¤À¤±¡£
- HOGE ¥×¥í¥·¡¼¥¸¥ã¤ò¾¤Î¥µ¥Ö¥×¥í¥°¥é¥à¤ä̵̾¥Ö¥í¥Ã¥¯¤«¤é¸Æ¤Ó½Ð¤¹
declare v_hoge number; begin -- °ú¿ô¤¬¤¤¤é¤Ê¤±¤ì¤Ð¥×¥í¥·¡¼¥¸¥ã̾¤À¤± hoge; -- °ú¿ô¡ÊIN ¥Ñ¥é¥á¡¼¥¿¡Ë¤¬É¬ÍפʤéÅϤ¹ hoge(10); -- OUT ¥Ñ¥é¥á¡¼¥¿¤Î¾ì¹ç¤Ï¥×¥í¥·¡¼¥¸¥ã¤«¤é¤Î³ÊǼÀè¤È¤Ê¤ëÊÑ¿ô¤òÍ¿¤¨¤ë hoge(v_hoge); end; /
SQL*Plus ¤«¤éľÀÜÆ°¤«¤¹¾ì¹ç¤Ï EXECUTE ʸ¤òÍѤ¤¤ë¡£°ú¿ô¤Î»ØÄêÊýË¡¤Ï¾å¤È°ì½ï¡£
- HOGE ¥×¥í¥·¡¼¥¸¥ã¤ò SQL*Plus ¤«¤é¸Æ¤Ó½Ð¤¹
-- ñ¤Ê¤ë¸Æ¤Ó½Ð¤· execute hoge -- °ú¿ô¤Î»ØÄê¡Ê¤¿¤È¤¨¤Ð¥Ð¥¤¥ó¥ÉÊÑ¿ô¡Ë execute hoge(:v_hoge)
¥Õ¥¡¥ó¥¯¥·¥ç¥ó†
¤¤¤ï¤æ¤ë´Ø¿ô¡£¹Í¤¨Êý¤Ï¾¸À¸ì¤ÈƱ¤¸¡£ÃͤòÂåÆþ¤¹¤ë¤¿¤á¤À¤Ã¤¿¤ê¡¢¾ò·ïȽÄê¤Ë»ÈÍѤ·¤¿¤ê¡¢¥Õ¥£¥ë¥¿¤Ë»ÈÍѤ·¤¿¤ê¡¢¤¤¤í¤¤¤í¤ÊÌÜŪ¤Ë»È¤¨¤ë¡£
´ðËܤι½Ê¸†
¥×¥í¥·¡¼¥¸¥ã¤È¤Û¤È¤ó¤É°ì½ï¡£PROCEDURE ¤¬ FUNCTION ¤Ë¤Ê¤Ã¤¿¤Î¤È¡¢ÊÖ¤êÃͤΤ¿¤á¤Î RETURN ¤¬Áý¤¨¤¿ÅÀ¤À¤±¤¬°ã¤¦¡£
RETURN ¤ÇÊÖ¤»¤ë¥Ç¡¼¥¿¤Ï°ì¤Ä¤À¤±¡£
- ¥¹¥È¥¢¥É¥Õ¥¡¥ó¥¯¥·¥ç¥ó¤Î¹½Ê¸
create [or replace] -- FUNCTION ¤òÀë¸À -- ¥Ñ¥é¥á¡¼¥¿¤ÏǤ°Õ¡£IN ¥Ñ¥é¥á¡¼¥¿¤Î¤ß¤ò¿ä¾© function hoge (v_arg1 number, v_arg2 varchar2) -- ÊÖ¤êÃͤη¿¤ò»ØÄê return number is -- Àë¸ÀÉô ... begin -- ¼Â¹ÔÉô -- ɬ¤º¤É¤³¤«¤Ç RETURN ¤¬É¬Í× return v_hoge; ... exception -- Îã³°½èÍýÉô ... end;
¼Â¹ÔÊýË¡†
- HOGE ¥Õ¥¡¥ó¥¯¥·¥ç¥ó¤ò¾¤Î¥µ¥Ö¥×¥í¥°¥é¥à¤ä̵̾¥Ö¥í¥Ã¥¯¤«¤é¸Æ¤Ó½Ð¤¹
declare v_hoge number; v_fuga varchar2(10); begin -- °ú¿ô¤¬¤¤¤é¤Ê¤±¤ì¤Ð¥×¥í¥·¡¼¥¸¥ã̾¤À¤± v_hoge := hoge; -- °ú¿ô¡ÊIN ¥Ñ¥é¥á¡¼¥¿¡Ë¤¬É¬ÍפʤéÅϤ¹ v_hoge := hoge(10); -- ¾ò·ïȽÄê¤Ë»È¤Ã¤¿¤ê¡¢SELECT ʸ¤Ë»È¤Ã¤¿¤ê if hoge(v_hoge) > 100 then select hoge(120) into v_fuga from fuga; end if; end; /
SQL*Plus ¤«¤éľÀÜÆ°¤«¤¹¾ì¹ç¤Ã¤Æ¤¢¤ó¤Þ¤ê¤Ê¤¤¤È»×¤¦¤±¤É¡¢DUAL ɽ¤«¤é¤Ò¤¤¤Æ»È¤¦¤È¤«¤«¤Ê¤¢¡£
- HOGE ¥Õ¥¡¥ó¥¯¥·¥ç¥ó¤ò SQL*Plus ¤«¤é¸Æ¤Ó½Ð¤¹
select hoge from dual; select hoge(10) from dual;
¥³¥ó¥Ñ¥¤¥ë¤È¥¨¥é¡¼¤Î¾ÜºÙ†
CREATE PROCEDURE ¤·¤¿¤é¡Ø·Ù¹ð: ¥×¥í¥·¡¼¥¸¥ã¤¬ºîÀ®¤µ¤ì¤Þ¤·¤¿¤¬¡¢¥³¥ó¥Ñ¥¤¥ë¡¦¥¨¥é¡¼¤¬¤¢¤ê¤Þ¤¹¡£¡Ù¤Ã¤ÆÅܤé¤ì¤¿¡£¤Ç¤â¤É¤³¤¬¥¨¥é¡¼¤Ê¤ó¤À¤«²¿¤â¶µ¤¨¤Æ¤¯¤ì¤Ê¤¤¡Ä¡Ä ¤È¤¤¤¦Ê¢Î©¤¿¤·¤¤»öÂÖ¤ËÁø¶ø¤·¤¿¤È¤¤Ï¤ª¤Þ¤¸¤Ê¤¤¤ò¾§¤¨¤ë¤È¹¬¤»¤Ë¤Ê¤ì¤ë¡£
ÆüËܸì¤Ï¤ª¤«¤·¤¤¤ó¤À¤±¤É¤Þ¤¢¡Ä¡Ä ¤Ê¤¤¤è¤ê¤Þ¤·¡Ä¡Ä¡©
- SHOW ERROR
-- ¥µ¡¼¥Ð¤Ë³ÊǼ¤·¤è¤¦¤È¤·¤¿¤é¥¨¥é¡¼¤¬½Ð¤ë¤±¤É¾ÜºÙÉÔÌÀ SQL> @hoge.sql ·Ù¹ð: ¥×¥í¥·¡¼¥¸¥ã¤¬ºîÀ®¤µ¤ì¤Þ¤·¤¿¤¬¡¢¥³¥ó¥Ñ¥¤¥ë¡¦¥¨¥é¡¼¤¬¤¢¤ê¤Þ¤¹¡£ -- ¤¤¤é¤¤¤é¤·¤Ê¤¬¤é¤ª¤Þ¤¸¤Ê¤¤¤ò¾§¤¨¤ë SQL> show error PROCEDURE HOGE¤Î¥¨¥é¡¼¤Ç¤¹¡£ LINE/COL ERROR -------- ----------------------------------------------------------------- 2/5 PLS-00103: µ¹æ"V_HOGE"¤¬¸«¤Ä¤«¤ê¤Þ¤·¤¿¡£ ¼¡¤Î¤¦¤Á¤Î1¤Ä¤¬Æþ¤ë¤È¤: ( ; is with authid as cluster compress order using compiled wrapped external deterministic parallel_enable pipelined result_cache µ¹æ"is" ¤Ï³¹Ô¤Î¤¿¤á¤Ë"V_HOGE"¤ËÂå¤ï¤ê¤Þ¤·¤¿¡£ 5/1 PLS-00103: µ¹æ"END"¤¬¸«¤Ä¤«¤ê¤Þ¤·¤¿¡£ ¼¡¤Î¤¦¤Á¤Î1¤Ä¤¬Æþ¤ë¤È¤: := . ( % ; LINE/COL ERROR -------- ----------------------------------------------------------------- µ¹æ";" ¤Ï³¹Ô¤Î¤¿¤á¤Ë"END"¤ËÂå¤ï¤ê¤Þ¤·¤¿¡£
Last-modified: 2011-11-09 (¿å) 14:21:42