ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ Æ÷ÇÔÇÏ´Â ÀÀ¿ëÇÁ·Î±×·¥ ¼³°è´Â º°µµÀÇ Å¬¶óÀÌ¾ðÆ®¿Í ¼¹ö ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ±¸¼ºµË´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¶ó´Â ¼¹ö ÀÀ¿ëÇÁ·Î±×·¥Àº ¼¹öÀÇ °øÀ¯ ¶óÀ̺귯¸® ¶Ç´Â Ŭ·¡½º ¶óÀ̺귯¸®¿¡ µé¾î ÀÖ½À´Ï´Ù. µ¥ÀÌÅͺ£À̽º°¡ »óÁÖÇÏ´Â ¼¹ö ÀνºÅϽº¿¡¼ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÄÄÆÄÀÏÇÏ°í ¾×¼¼½ºÇØ¾ß ÇÕ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡´Â ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ CALL¹®ÀÌ ÀÖ½À´Ï´Ù. CALL¹®Àº ¸Å°³º¯¼ö¸¦ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Àü´ÞÇϰí ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼ ¸Å°³º¯¼ö¸¦ ¸®ÅÏÇÒ ¼ö ÀÖ½À´Ï´Ù. ´Ù¸¥ ¾ð¾î¸¦ »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¿Í Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿Í ´Ù¸¥ Ç÷§Æû¿¡¼ ½ÇÇàµÉ ¼ö ÀÖ½À´Ï´Ù.
Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½ Ÿ½ºÅ©¸¦ ¼öÇàÇÕ´Ï´Ù.
ÁÖ: | ÀúÀå ÇÁ·Î½Ãµà¾î°¡ COMMIT ¶Ç´Â ROLLBACK¹®À» ½ÇÇàÇÒ ¼ö ÀÖÁö¸¸ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ COMMIT ¶Ç´Â ROLLBACKÀ» ½ÇÇàÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. À̸¦ ÅëÇØ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ ¸®ÅÏµÈ µ¥ÀÌÅ͸¦ Æò°¡ÇÏ°í Æ®·£Àè¼ÇÀ» È®¾àÇÒ °ÍÀÎÁö ¾Æ´Ï¸é ±¸°£ º¹¿øÇÒ °ÍÀÎÁö¸¦ °áÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. |
À§ ´Ü°è¸¦ ÅëÇØ SQL¹®À» ÄÚµåÈÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÀúÀå ÇÁ·Î½Ãµà¾î°¡ È£ÃâµÇ¸é ´ÙÀ½ Ÿ½ºÅ©¸¦ ¼öÇàÇÏ°Ô µË´Ï´Ù.
ÁÖ: | ÀúÀå ÇÁ·Î½Ãµà¾î°¡ COMMIT ¶Ç´Â ROLLBACK¹®À» ½ÇÇàÇÒ ¼ö ÀÖÁö¸¸ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ COMMIT ¶Ç´Â ROLLBACKÀ» ½ÇÇàÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. À̸¦ ÅëÇØ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ ¸®ÅÏµÈ µ¥ÀÌÅ͸¦ Æò°¡ÇÏ°í Æ®·£Àè¼ÇÀ» È®¾àÇÒ °ÍÀÎÁö ¾Æ´Ï¸é ±¸°£ º¹¿øÇÒ °ÍÀÎÁö¸¦ °áÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. |
ÀúÀå ÇÁ·Î½Ãµà¾î´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÇÇØ È£ÃâµÉ ¶§ ½ÇÇàµË´Ï´Ù. ¼¹ö ÇÁ·Î½Ãµà¾î°¡ 󸮸¦ Á¾·áÇÒ ¶§ Ŭ¶óÀÌ¾ðÆ®·Î Á¦¾î°¡ ¸®Åϵ˴ϴÙ. ¿©·¯ °³ÀÇ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÇϳªÀÇ ¶óÀ̺귯¸®¿¡ µÑ ¼ö ÀÖ½À´Ï´Ù.
ÀÌ Àå¿¡¼´Â ´ÙÀ½°ú °°Àº ¸Å°³º¯¼ö ½ºÅ¸ÀÏÀ» ÀÌ¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ´Â ¹æ¹ý¿¡ ´ëÇØ ¼³¸íÇÕ´Ï´Ù.
»ç¿ëÀÚ´Â ¹Ýµå½Ã CREATE PROCEDURE¹®À» ÀÌ¿ëÇÏ¿© ÀÌÀü¿¡ ³ª¿µÈ ¸Å°³º¯¼ö ½ºÅ¸ÀÏ¿¡ ´ëÇØ °¢ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇØ¾ß ÇÕ´Ï´Ù. CREATE PROCEDURE¹®Àº °¢ ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ÇÁ·Î½Ãµà¾î À̸§, Àμö, À§Ä¡ ¹× ¸Å°³º¯¼ö ½ºÅ¸ÀÏÀ» ÁöÁ¤ÇÕ´Ï´Ù. ÀÌ·¯ÇÑ ¸Å°³º¯¼ö ½ºÅ¸ÀÏÀº DB2 °è¿À» ÅëÇØ ÀúÀå ÇÁ·Î½Ãµà¾î ÄÚµåÀÇ À̽ļº ¹× È®À强À» Áõ°¡½Ãŵ´Ï´Ù.
DB2 Universal Database ¹öÀü 6 ÀÌÀüÀÇ DB2 ¹öÀü, Áï DB2DARI ¹× DB2GENERAL ¸Å°³º¯¼ö ½ºÅ¸ÀÏ·Î Áö¿øµÇ´Â ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ½ºÅ¸Àϸ¸ »ç¿ëÇÏ´Â °Í¿¡ ´ëÇÑ ³»¿ëÀº ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDF¸¦ ÂüÁ¶ÇϽʽÿÀ.
Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϱâ Àü¿¡ ¸î °¡Áö ´Ü°è¸¦ ¼öÇàÇÕ´Ï´Ù. ¿ì¼± µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇØ¾ß Çϰí, È£½ºÆ® º¯¼ö³ª SQLDA ±¸Á¶¸¦ ¼±¾ð, ÇÒ´ç ¹× ÃʱâÈÇØ¾ß ÇÕ´Ï´Ù. SQL CALL¹®Àº ÀÏ·ÃÀÇ È£½ºÆ® º¯¼ö ¶Ç´Â ÇϳªÀÇ SQLDA ±¸Á¶¸¦ ¼ö¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. SQL CALL¹® ¹× SQLDA ±¸Á¶¿¡ ´ëÇÑ ¼³¸íÀº SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ SQLDA ±¸Á¶¸¦ »ç¿ëÇÏ´Â °Í¿¡ ´ëÇØ¼´Â ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDFÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ Å¬¶óÀ̾ðÆ®Ãø¿¡ ÇÊ¿äÇÑ ÀԷ ȣ½ºÆ® º¯¼ö¸¦ ÇÒ´çÇÏ·Á¸é ´ÙÀ½ ´Ü°è¸¦ »ç¿ëÇϽʽÿÀ.
ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ Å¬¶óÀÌ¾ðÆ® ºÎºÐÀ» ÀÛ¼ºÇÒ ¶§ ÀÔ·Â ¹× Ãâ·Â ¸ðµÎ¿¡ ´ëÇØ »ç¿ëÇÔÀ¸·Î½á °¡´ÉÇÑ ÇÑ ¸¹Àº È£½ºÆ® º¯¼ö¸¦ ¿À¹ö·ÎµåÇØ¾ß ÇÕ´Ï´Ù. ±× °á°ú ¿©·¯ È£½ºÆ® º¯¼ö¸¦ ó¸®ÇÏ´Â µ¥ ÀÖ¾î È¿À²¼ºÀÌ Áõ°¡µË´Ï´Ù. ¿¹¸¦ µé¾î, SQLCODE¸¦ ÀúÀå ÇÁ·Î½Ãµà¾î·ÎºÎÅÍ Å¬¶óÀÌ¾ðÆ®·Î ¸®ÅÏÇÒ ¶§, INTEGER·Î¼ ¼±¾ðµÇ´Â ÀԷ ȣ½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ¿© SQLCODE¸¦ ¸®ÅÏÇØ º¸½Ê½Ã¿À.
ÁÖ: | µ¥ÀÌÅͺ£À̽º ¼¹ö¿¡¼ ÀÌµé ±¸Á¶¿¡ ´ëÇÑ ÀúÀ念¿ªÀ» ÇÒ´çÇÏÁö ¸¶½Ê½Ã¿À. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÇÇØ ÇÒ´çµÈ ÀúÀ念¿ª¿¡ ±âÃÊÇÏ¿© ÀÚµ¿À¸·Î ÀÌÁß ÀúÀ念¿ªÀ» ÇÒ´çÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾îÃøÀÇ ÀÔ·Â/Ãâ·Â ¸Å°³º¯¼ö¿¡ ´ëÇÑ ÀúÀ念¿ª Æ÷ÀÎÅ͸¦ º¯°æÇÏÁö ¸¶½Ê½Ã¿À. Áö¿ªÀûÀ¸·Î ÀÛ¼ºµÈ ÀúÀ念¿ª Æ÷ÀÎÅÍ·Î Æ÷ÀÎÅ͸¦ ´ëüÇϸé SQLCODE -1133 (SQLSTATE 39502) ¿À·ù°¡ ¹ß»ýÇÒ ¼ö ÀÖ½À´Ï´Ù. |
SQL CALL¹®À» »ç¿ëÇÏ¿© µ¥ÀÌÅͺ£À̽º À§Ä¡¿¡ ÀúÀåµÈ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¼ö ÀÖ½À´Ï´Ù. CALL¹®¿¡ ´ëÇÑ ¿ÏÀüÇÑ ¼³¸íÀº SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¶§ CALL¹®À» »ç¿ëÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù.
Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϱâ Àü¿¡ µ¥ÀÌÅͺ£À̽º ¿¬°áÀ» ÇØ¾ß ÇÕ´Ï´Ù. ±×·¸Áö ¾ÊÀ¸¸é ¿À·ù°¡ ¸®Åϵ˴ϴÙ. µ¥ÀÌÅͺ£À̽º ¿¬°á ¹× µ¥ÀÌÅÍ ±¸Á¶°¡ ÃʱâÈµÈ ÈÄ, Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϰí ÇÊ¿äÇÑ µ¥ÀÌÅ͸¦ Àü´ÞÇÕ´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥Àº µ¥ÀÌÅͺ£À̽º·ÎºÎÅÍ ¿¬°áÇØÁ¦µË´Ï´Ù. À§ ´Ü°è¸¦ ÅëÇØ SQL¹®À» ÄÚµåÈÇÒ ¼ö ÀÖ½À´Ï´Ù.
ÀúÀå ÇÁ·Î½Ãµà¾î´Â SQL CALL¹®¿¡ ÀÇÇØ È£ÃâµÇ¸ç, Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÇÇØ Àü´ÞµÈ µ¥ÀÌÅ͸¦ »ç¿ëÇÕ´Ï´Ù. CREATE PROCEDURE¹®À» ÀÌ¿ëÇÏ¿© µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÏ´Â ¸Å°³º¯¼ö ½ºÅ¸ÀÏÀº ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·ÎºÎÅÍ µ¥ÀÌÅ͸¦ ¹Þ´Â ¹æ½ÄÀ» °áÁ¤ÇÕ´Ï´Ù.
CREATE PROCEDURE¹®À» »ç¿ëÇÏ·Á¸é ¹Ýµå½Ã ´ÙÀ½À» ¼±¾ðÇØ¾ß ÇÕ´Ï´Ù.
CREATE PROCEDURE´Â ´ÙÀ½ »çÇ×µµ ¹Ýµå½Ã ¼±¾ðÇØ¾ß ÇÕ´Ï´Ù.
DB2 °è¿ÀÇ È£È¯¼ºÀ» À§ÇÑ Àüü ±¸¹® ¹× ¿É¼ÇÀ» Æ÷ÇÔÇÏ¿©, CREATE PROCEDURE¹®¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº SQL ÂüÁ¶¼¿¡ ÀÖ½À´Ï´Ù. CREATE PROCEDURE¹®ÀÇ ÀÏ¹Ý »ç¿ë¹ýÀº ´ÙÀ½°ú °°½À´Ï´Ù.
°íÀ¯ÇÑ ¼öÀÇ ¸Å°³º¯¼ö¸¦ ¼ö¿ëÇÏ´Â ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ µ¿ÀÏÇÑ À̸§À» »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ¿À¹ö·ÎµåÇÒ ¼ö ÀÖ½À´Ï´Ù. DB2°¡ µ¥ÀÌÅÍ À¯ÇüÀ» ±¸º°ÇÏÁö ¾ÊÀ¸¹Ç·Î ¸Å°³º¯¼ö µ¥ÀÌÅÍ À¯Çü¿¡ ±âÃÊÇÑ ¤Ð¸¦ ¿À¹ö·ÎµåÇÒ ¼ö ¾ø½À´Ï´Ù.
¿¹¸¦ µé¾î, ´ÙÀ½°ú °°Àº CREATE PROCEDURE¹®À» ¹ßÇàÇÏ´Â °ÍÀº °¢°¢ ÇÑ ¸Å°³º¯¼ö ¹× µÎ ¸Å°³º¯¼ö¸¦ Çã¿ëÇϹǷΠÀÛµ¿µË´Ï´Ù.u
CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER) ... CREATE PROCEDURE OVERLOAD (IN VAR1 INTEGER, IN VAR2 INTEGER) ...
±×·¯³ª, DB2´Â °°Àº À̸§À» °¡Áø ù¹øÂ° ÀúÀå ÇÁ·Î½Ãµà¾î·Î¼ °°Àº ¼öÀÇ ¸Å°³º¯¼ö¸¦ °¡Áö°í ÀÖÀ¸¹Ç·Î, ´ÙÀ½ ¿¹¿¡¼ µÎ ¹øÂ° ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÏÁö ¸øÇÕ´Ï´Ù.
CREATE PROCEDURE OVERLOADFAIL (IN VAR1 INTEGER) ... CREATE PROCEDURE OVERLOADFAIL (IN VAR2 VARCHAR(15)) ...
¸í½ÃÀû ¸Å°³º¯¼ö´Â CREATE PROCEDURE¹®ÀÇ ¸Å°³º¯¼ö ¸ñ·Ï¿¡¼ ¸í½ÃÀûÀ¸·Î ¼±¾ðµÈ ¸Å°³º¯¼öÀÔ´Ï´Ù. ¾Ï½ÃÀû ¸Å°³º¯¼ö´Â DB2¿¡¼ ÀÚµ¿À¸·Î Á¦°øµÇ´Â ¸Å°³º¯¼öÀÔ´Ï´Ù. ¿¹¸¦ µé¾î, PARAMETER STYLE GENERAL WITH NULLS ÀúÀå ÇÁ·Î½Ãµà¾î´Â ¸í½ÃÀû ¸Å°³º¯¼ö¿¡ ´ëÇØ ³Î Ç¥½Ã±âÀÇ ¹è¿À» ÀÚµ¿À¸·Î Á¦°øÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¶§ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¸í½ÃÀû ¹× ¾Ï½ÃÀû ¸Å°³º¯¼ö¸¦ µÑ´Ù °í·ÁÇØ¾ß ÇÕ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇÒ ¶§ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ¸í½ÃÀû ¸Å°³º¯¼ö¸¸ ó¸®ÇØ¾ß ÇÕ´Ï´Ù. ¸ðµç ¸í½ÃÀû ¸Å°³º¯¼ö¸¦ À̸§°ú SQL µ¥ÀÌÅÍ À¯ÇüÀ» °¡Áø IN, OUT ¶Ç´Â INOUT ¸Å°³º¯¼ö·Î ¼±¾ðÇØ¾ß ÇÕ´Ï´Ù. CREATE PROCEDURE¹® ¿¹¿¡ ´ëÇØ¼´Â º¯¼ö ¼±¾ð ¹× CREATE PROCEDURE ¿¹¸¦ ÂüÁ¶ÇϽʽÿÀ.
CREATE PROCEDURE¹®ÀÇ EXTERNALÀýÀº ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Æ÷ÇÔµÈ ¶óÀ̺귯¸®ÀÇ À§Ä¡¸¦ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡°Ô ¾Ë·Á ÁÝ´Ï´Ù. ¶óÀ̺귯¸®¿¡ Àý´ë °æ·Î¸¦ ÁöÁ¤ÇÏÁö ¾Ê°Å³ª, Java ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ jar À̸§À» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ÇÔ¼ö µð·ºÅ丮¸¦ °Ë»öÇÕ´Ï´Ù. ÇÔ¼ö µð·ºÅ丮´Â ´ÙÀ½°ú °°ÀÌ »ç¿ëÀÚ ¿î¿µ üÁ¦¿¡ ´ëÇØ Á¤ÀÇµÈ µð·ºÅ丮ÀÔ´Ï´Ù.
DB2rk instance_name\function¿¡¼ DB2°¡ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ãÁö ¾ÊÀ¸¸é PATH ¹× LIBPATH ȯ°æ º¯¼ö¿¡ ÀÇÇØ Á¤ÀÇµÈ µð·ºÅ丮¸¦ °Ë»öÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, DB2°¡ DB2INSTPROF ·¹Áö½ºÆ®¸® ¼³Á¤À» ¼³Á¤ÇÏÁö ¾ÊÀº C:\sqllib µð·ºÅ丮¿¡ ¼³Ä¡µÈ Windows 32-ºñÆ® ¿î¿µ üÁ¦ ¼¹öÀÇ ÇÔ¼ö µð·ºÅ丮´Â ´ÙÀ½°ú °°½À´Ï´Ù.
C:\sqllib\function
ÁÖ: | »ç¿ëÀÚ ¶óÀ̺귯¸®¿¡ ÀúÀå ÇÁ·Î½Ãµà¾î À̸§°ú ´Ù¸¥ À̸§À» Á¦°øÇØ¾ß ÇÕ´Ï´Ù. DB2°¡ °Ë»ö °æ·Î¿¡¼ ¶óÀ̺귯¸® À§Ä¡¸¦ ãÀ¸¸é DB2´Â FENCED DB2DARI ÇÁ·Î½Ãµà¾î·Î¼ ¶óÀ̺귯¸®¿Í °°Àº À̸§À» °¡Áø ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ½ÇÇàÇÕ´Ï´Ù. |
LANGUAGE C ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °æ¿ì ´ÙÀ½À» ÁöÁ¤ÇϽʽÿÀ.
¿¹¸¦ µé¾î, UNIX ±â¹Ý ½Ã½ºÅÛ¿¡¼ mymod!proc8Àº µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À» sqllib/function/mymod ¶óÀ̺귯¸®·Î ÁöÁ¤Çϰí, ÀÌ ¶óÀ̺귯¸® ³»¿¡¼ proc8 ÁøÀÔÁ¡À» »ç¿ëÇÕ´Ï´Ù. Windows 32ºñÆ® ¹× OS/2 ¿î¿µ üÁ¦¿¡¼ mymod!proc8Àº µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ ÇÔ¼ö µð·ºÅ丮·ÎºÎÅÍ mymod.dllÀ» ·ÎµåÇϵµ·Ï ÁöÁ¤Çϰí, µ¿Àû ¸µÅ© ¶óÀ̺귯¸®(DLL)¿¡ ÀÖ´Â proc8() ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
LANGUAGE JAVA ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °æ¿ì ´ÙÀ½À» »ç¿ëÇϽʽÿÀ.
[<jar-file-name>:]<class-name>.<method-name>
´ÙÀ½ ¸ñ·ÏÀº Java ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ EXTERNAL Ű¿öµå¸¦ Á¤ÀÇÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, MyPackage.MyClass.myMethod¸¦ ÁöÁ¤ÇÏ¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº MyPackage ÆÐŰÁöÀÇ MyClass Ŭ·¡½º¿¡¼ myMethod ¸Þ¼Òµå¸¦ »ç¿ëÇÕ´Ï´Ù. DB2´Â MyPackage°¡ ÄÝ·Ð(:) ºÐ¸®¹®ÀÚ ´ë½Å ¸¶Ä§Ç¥(.) ºÐ¸®¹®ÀÚ¸¦ »ç¿ëÇϹǷΠjar ÆÄÀÏ ´ë½Å ÆÐŰÁö¸¦ ÂüÁ¶ÇÕ´Ï´Ù. DB2´Â MyPackage ÆÐŰÁö¿¡ ´ëÇØ ÇÔ¼ö µð·ºÅ丮¸¦ °Ë»öÇÕ´Ï´Ù.
ÇÔ¼ö µð·ºÅ丮¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº À§Ä¡ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
C/C++ÀÇ °æ¿ì, CREATE PROCEDURE¹®¿¡ LANGUAGE C¸¦ ¼±¾ðÇϽʽÿÀ. Java ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °æ¿ì LANGUAGE JAVA¸¦ ¼±¾ðÇϽʽÿÀ. Windows 32-ºñÆ® ¿î¿µ üÁ¦¿¡¼ OLE ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ LANGUAGE OLE¸¦ ¼±¾ðÇϽʽÿÀ. COBOL ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °æ¿ì LANGUAGE COBOLÀ» ¼±¾ðÇϽʽÿÀ. Fortran ¶Ç´Â REXX ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °æ¿ì, DB2DARI ÀúÀå ÇÁ·Î½Ãµà¾î·Î¼ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. DB2DARI ÀúÀå ÇÁ·Î½Ãµà¾î ÀÛ¼º¿¡ ´ëÇØ¼´Â ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDFÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
PROGRAM TYPE SUBÀÇ C ÀúÀå ÇÁ·Î½Ãµà¾î´Â Àμö¸¦ ¼ºê·çƾÀ¸·Î ½ÂÀÎÇÕ´Ï´Ù. ¼öÄ¡ µ¥ÀÌÅÍ À¯Çü ¸Å°³º¯¼ö¸¦ Æ÷ÀÎÅÍ·Î Àü´ÞÇϽʽÿÀ. ¹®ÀÚ µ¥ÀÌÅÍ À¯ÇüÀ» ÇØ´ç ±æÀÌÀÇ ¹è¿·Î Àü´ÞÇϽʽÿÀ. ¿¹¸¦ µé¾î, ´ÙÀ½ C ÀúÀå ÇÁ·Î½Ãµà¾î ¼¸íÀº INTEGER, SMALLINT ±×¸®°í CHAR(3) À¯ÇüÀÇ ¸Å°³º¯¼ö¸¦ ½ÂÀÎÇÕ´Ï´Ù.
int storproc (sqlint32 *arg1, short *arg2, char arg[4])
Java ÀúÀå ÇÁ·Î½Ãµà¾î´Â Àμö¸¦ ¼ºê·çƾÀ¸·Î¸¸ ½ÂÀÎÇÒ ¼ö ÀÖ½À´Ï´Ù. IN ¸Å°³º¯¼ö¸¦ ´Ü¼ø Àμö·Î Àü´ÞÇϽʽÿÀ. OUT¿Í INOUT ¸Å°³º¯¼ö¸¦ ´ÜÀÏ ¿ä¼ÒÀÇ ¹è¿·Î Àü´ÞÇϽʽÿÀ. ¿¹¸¦ µé¾î, ´ÙÀ½ Java ÀúÀå ÇÁ·Î½Ãµà¾î ¼¸íÀº INTEGER, SMALLINT À¯ÇüÀÇ OUT ¸Å°³º¯¼ö ±×¸®°í CHAR(3) À¯ÇüÀÇ INOUT ¸Å°³º¯¼ö¸¦ ½ÂÀÎÇÕ´Ï´Ù.
int storproc (int arg1, short arg2[], String arg[])
C ÇÁ·Î±×·¥¿¡¼ ÁÖ ÇÔ¼ö¿Í °°Àº Àμö¸¦ Çã¿ëÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ·Á¸é CREATE PROCEDURE¹®¿¡ PROGRAM TYPE MAINÀ» ÁöÁ¤ÇϽʽÿÀ. PROGRAM TYPE MAINÀÇ ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ´ÙÀ½ ½ºÆå¿¡ ÀÏÄ¡Çϵµ·Ï ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù.
PROGRAM TYPE MAIN ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼ DB2´Â argv ¹è¿ÀÇ Ã¹¹øÂ° ¿ä¼Ò °ªÀ» (argv[0])¸¦ ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ À̸§À¸·Î ¼³Á¤ÇÕ´Ï´Ù. argv ¹è¿ÀÇ ³ª¸ÓÁö ¿ä¼Ò´Â ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ CREATE PROCEDURE¹®¿¡ ¼±¾ðµÈ ¸Å°³º¯¼ö¿¡ ÇØ´çÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½ embedded C ÀúÀå ÇÁ·Î½Ãµà¾î´Â IN ¸Å°³º¯¼ö Çϳª¸¦ argv[1]·Î Àü´ÞÇÏ°í µÎ OUT ¸Å°³º¯¼ö¸¦ argv[2]¿Í argv[3]À¸·Î ¸®ÅÏÇÕ´Ï´Ù.
PROGRAM TYPE MAINÀÇ CREATE PROCEDURE¹® ¿¹´Â ´ÙÀ½°ú °°½À´Ï´Ù.
CREATE PROCEDURE MAIN_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE, OUT errorcode INTEGER) DYNAMIC RESULT SETS 0 LANGUAGE C PARAMETER STYLE GENERAL NO DBINFO FENCED READS SQL DATA PROGRAM TYPE MAIN EXTERNAL NAME 'spserver!mainexample'
ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ´ÙÀ½ Äڵ带 argv[1]ÀÇ °ªÀ» CHAR(8) È£½ºÆ® º¯¼ö injobÀ¸·Î º¹»çÇϰí DOUBLE È£½ºÆ® º¯¼ö outsalaryÀÇ °ªÀ» argv[2]·Î º¹»çÇϸç SQLCODE¸¦ argv[3]À¸·Î ¸®ÅÏÇÕ´Ï´Ù.
EXEC SQL BEGIN DECLARE SECTION; char injob[9]; double outsalary; EXEC SQL END DECLARE SECTION; SQL_API_RC SQL_API_FN main_example (int argc, char **argv) { EXEC SQL INCLUDE SQLCA; /* argv[0] contains the procedure name, so parameters start at argv[1] */ strcpy (injob, (char *)argv[1]); EXEC SQL SELECT AVG(salary) INTO :outsalary FROM employee WHERE job = :injob; memcpy ((double *)argv[2], (double *)&outsalary, sizeof(double)); memcpy ((sqlint32 *)argv[3], (sqlint32 *)&SQLCODE, sizeof(sqlint32)); return (0); } /* end main_example function */
Ç¥ 9¿¡¼´Â DB2 ¹öÀü 7¿ë CREATE PROCEDURE¹®¿¡¼
Çã¿ëµÇ´Â PARAMETER STYLE(¼öÆòÃà) ¹× LANGUAGE(¼öÁ÷Ãà)ÀÇ Á¶ÇÕ¿¡ ´ëÇØ ¿ä¾àÇÕ´Ï´Ù.
Ç¥ 9. CREATE PROCEDURE: PARAMETER STYLE ¹× LANGUAGEÀÇ À¯È¿ Á¶ÇÕ
GENERAL, GENERAL WITH NULLS | JAVA | DB2SQL | DB2DARI | DB2GENERAL | |
---|---|---|---|---|---|
LANGUAGE C | Y | N | Y | Y | N |
LANGUAGE JAVA | N | Y | N | N | Y |
LANGUAGE OLE | N | N | Y | N | N |
LANGUAGE COBOL | Y | N | Y | N | N |
OS/390¿ë DB2 Universal Database ȣȯ¼º: GENERALÀº SIMPLE°ú °°½À´Ï´Ù.
PARAMETER STYLE GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î´Â PROGRAM TYPEÀýÀÇ °ªÀ¸·Î Ç¥½ÃµÈ ¹æ½ÄÀ¸·Î ¸Å°³º¯¼ö¸¦ ½ÂÀÎÇÕ´Ï´Ù. ´ÙÀ½ ¿¹´Â PROGRAM TYPE SUBROUTINEÀ» »ç¿ëÇÏ¿© µÎ ¸Å°³º¯¼ö¸¦ ½ÂÀÎÇÏ´Â PARAMETER STYLE GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ º¸¿©ÁÝ´Ï´Ù.
SQL_API_RC SQL_API_FN one_result_set_to_client (double *insalary, sqlint32 *out_sqlerror) { EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO return_error; EXEC SQL BEGIN DECLARE SECTION; double l_insalary; EXEC SQL END DECLARE SECTION; l_insalary = *insalary; *out_sqlerror = 0; EXEC SQL DECLARE c3 CURSOR FOR SELECT name, job, CAST(salary AS INTEGER) FROM staff WHERE salary > :l_insalary ORDER BY salary; EXEC SQL OPEN c3; /* Leave cursor open to return result set */ return (0); /* Copy SQLCODE to OUT parameter if SQL error occurs */ return_error: { *out_sqlerror = SQLCODE; EXEC SQL WHENEVER SQLERROR CONTINUE; return (0); } } /* end one_result_set_to_client function */
OS/390¿ë DB2 Universal Database ȣȯ¼º: GENERAL WITH NULLSÀº SIMPLE WITH NULLS°ú °°½À´Ï´Ù.
PARAMETER STYLE GENERAL WITH NULLS ÀúÀå ÇÁ·Î½Ãµà¾î´Â PROGRAM TYPEÀýÀÇ °ªÀ¸·Î ³ªÅ¸³ ¹æ½ÄÀ¸·Î ¸Å°³º¯¼ö¸¦ ½ÂÀÎÇÏ°í ¼±¾ðµÈ ¸Å°³º¯¼ö¸¶´Ù ¿ä¼Ò°¡ Çϳª ÀÖ´Â ³Î Ç¥½Ã±âÀÇ ¹è¿À» ÇÒ´çÇÕ´Ï´Ù. ´ÙÀ½ SQLÀº PROGRAM TYPE SUBÀ» »ç¿ëÇÏ¿© INOUT ¸Å°³º¯¼ö Çϳª¿Í OUT ¸Å°³º¯¼ö µÎ°³¸¦ Àü´ÞÇÏ´Â PARAMETER STYLE GENERAL WITH NULLS ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÕ´Ï´Ù.
CREATE PROCEDURE INOUT_PARAM (INOUT medianSalary DOUBLE, OUT errorCode INTEGER, OUT errorLabel CHAR(32)) DYNAMIC RESULT SETS 0 LANGUAGE C PARAMETER STYLE GENERAL WITH NULLS NO DBINFO FENCED MODIFIES SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'spserver!inout_param'
´ÙÀ½ C ÄÚµå´Â GENERAL WITH NULLS ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼ ÇÊ¿äÇÑ ³Î Ç¥½Ã±â¸¦ ¼±¾ðÇÏ°í »ç¿ëÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
SQL_API_RC SQL_API_FN inout_param (double *inoutMedian, sqlint32 *out_sqlerror, char buffer[33], sqlint16 nullinds[3]) { EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO return_error; if (nullinds[0] < 0) { /* NULL value was received as input, so return NULL output */ nullinds[0] = -1; nullinds[1] = -1; nullinds[2] = -1; } else { int counter = 0; *out_sqlerror = 0; medianSalary = *inoutMedian; strcpy(buffer, "DECLARE inout CURSOR"); EXEC SQL DECLARE inout CURSOR FOR SELECT CAST(salary AS DOUBLE) FROM staff WHERE salary > :medianSalary ORDER BY salary; nullinds[1] = 0; nullinds[2] = 0; strcpy(buffer, "SELECT COUNT INTO numRecords"); EXEC SQL SELECT COUNT(*) INTO :numRecords FROM staff WHERE salary > :medianSalary; if (numRecords != 0) /* At least one record was found */ { strcpy(buffer, "OPEN inout"); EXEC SQL OPEN inout USING :medianSalary; strcpy(buffer, "FETCH inout"); while (counter < (numRecords / 2 + 1)) { EXEC SQL FETCH inout INTO :medianSalary; *inoutMedian = medianSalary; counter = counter + 1; } strcpy(buffer, "CLOSE inout"); EXEC SQL CLOSE inout; } else /* No records were found */ { /* Return 100 to indicate NOT FOUND error */ *out_sqlerror = 100; } } return (0); /* Copy SQLCODE to OUT parameter if SQL error occurs */ return_error: { *out_sqlerror = SQLCODE; EXEC SQL WHENEVER SQLERROR CONTINUE; return (0); } } /* end inout_param function */
sqlint16 nullinds[n], (1) char sqlst[6], (2) char qualname[28], (3) char specname[19], (4) char diagmsg[71], (5)
DB2´Â ´ÙÀ½ Àμö¸¦ ÀúÀå ÇÁ·Î½Ãµà¾î·Î Àü´ÞÇÕ´Ï´Ù.
LANGUAGE C ¶Ç´Â LANGUAGE COBOL ¿É¼ÇÀ» ÁöÁ¤ÇÒ ¶§¿¡µµ DB2SQL¸¸ ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½ CREATE PROCEDURE¹®Àº PARAMETER STYLE DB2SQL ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÕ´Ï´Ù.
CREATE PROCEDURE DB2SQL_EXAMPLE (IN job CHAR(8), OUT salary DOUBLE) DYNAMIC RESULT SETS 0 LANGUAGE C PARAMETER STYLE DB2SQL NO DBINFO FENCED READS SQL DATA PROGRAM TYPE SUB EXTERNAL NAME 'spserver!db2sqlexample'
´ÙÀ½ ±ÔÄ¢À» »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇϽʽÿÀ.
¿¹¸¦ µé¾î, ´ÙÀ½ embedded C ÀúÀå ÇÁ·Î½Ãµà¾î´Â PARAMETER STYLE DB2SQL ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ÄÚµù ½ºÅ¸ÀÏÀ» º¸¿©ÁÝ´Ï´Ù.
SQL_API_RC SQL_API_FN db2sql_example ( char injob[9], /* Input - CHAR(8) */ double *salary, /* Output - DOUBLE */ sqlint16 nullinds[2], char sqlst[6], char qualname[28], char specname[19], char diagmsg[71] ) { EXEC SQL INCLUDE SQLCA; if (nullinds[0] < 0) { /* NULL value was received as input, so return NULL output */ nullinds[1] = -1; /* Set custom SQLSTATE to return to client. */ strcpy(sqlst, "38100"); /* Set custom message to return to client. */ strcpy(diagmsg, "Received null input on call to DB2SQL_EXAMPLE."); } else { EXEC SQL SELECT (CAST(AVG(salary) AS DOUBLE)) INTO :outsalary INDICATOR :outsalaryind FROM employee WHERE job = :injob; *salary = outsalary; nullinds[1] = outsalaryind; } return (0); } /* end db2sql_example function */
´ÙÀ½ embedded C Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº DB2SQL_EXAMPLE ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÏ´Â CALL¹®À» ½ÇÇàÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù. ¿¹¿¡´Â CALL¹®ÀÇ °¢ ¸Å°³º¯¼ö¿¡ ´ëÇÑ ³Î Ç¥½Ã±â°¡ µé¾î ÀÖ½À´Ï´Ù. ¿¹´Â ³Î Ç¥½Ã±â in_jobindÀ» 0À¸·Î ¼³Á¤ÇÏ¿© ³ÎÀÌ ¾Æ´Ñ °ªÀÌ È£½ºÆ® º¯¼ö in_jobÀ¸·Î Ç¥ÇöµÈ IN ¸Å°³º¯¼öÀÇ ÀúÀå ÇÁ·Î½Ãµà¾î·Î Àü´ÞµÊÀ» ³ªÅ¸³À´Ï´Ù. OUT ¸Å°³º¯¼öÀÇ ³Î Ç¥½Ã±â´Â ÀÌ·± ¸Å°³º¯¼öÀÇ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Àü´ÞµÈ ÀÔ·ÂÀÌ ¾øÀ½À» ³ªÅ¸³»µµ·Ï -1·Î ¼³Á¤µË´Ï´Ù.
int db2sqlparm(char out_lang[9], char job_name[9]) { int testlang; EXEC SQL BEGIN DECLARE SECTION; /* Declare host variables for passing data to DB2SQL_EXAMPLE */ char in_job[9]; sqlint16 in_jobind; double out_salary = 0; sqlint16 out_salaryind; EXEC SQL END DECLARE SECTION; /********************************************************\ * Call DB2SQL_EXAMPLE stored procedure * \********************************************************/ testlang = strncmp(out_lang, "C", 1); if (testlang != 0) { /* Only LANGUAGE C procedures can be PARAMETER STYLE DB2SQL, so do not call the DB2SQL_EXAMPLE stored procedure */ printf("\nStored procedures are not implemented in C.\n" "Skipping the call to DB2SQL_EXAMPLE.\n"); } else { strcpy(procname, "DB2SQL_EXAMPLE"); printf("\nCALL stored procedure named %s\n", procname); /* out_salary is an OUT parameter, so set the null indicator to -1 to indicate no input value */ out_salaryind = -1; strcpy(in_job, job_name); /* in_job is an IN parameter, so check to see if there is any input value */ if (strlen(in_job) == 0) { /* in_job is null, so set the null indicator to -1 to indicate there is no input value */ in_jobind = -1; printf("with NULL input, to return a custom SQLSTATE and diagnostic message\n"); } else { /* in_job is not null, so set the null indicator to 0 to indicate there is an input value */ in_jobind = 0; } /* DB2SQL_EXAMPLE is PS DB2SQL, so pass a null indicator for each parameter */ EXEC SQL CALL :procname (:in_job:in_jobind, :out_salary:out_salaryind); /* DB2SQL stored procedures can return a custom SQLSTATE and diagnostic message, so instead of using the EMB_SQL_CHECK macro to check the value of the returned SQLCODE, check the SQLCA structure for the value of the SQLSTATE and the diagnostic message */ /* Check value of returned SQLSTATE */ if (strncmp(sqlca.sqlstate, "00000", 5) == 0) { printf("Stored procedure returned successfully.\n"); printf("Average salary for job %s = %9.2f\n", in_job, out_salary); } else { printf("Stored procedure failed with SQLSTATE %s.\n", sqlca.sqlstate); printf("Stored procedure returned the following diagnostic message:\n"); printf(" \"%s\"\n", sqlca.sqlerrmc); } } return 0; }
À̽ļºÀÇ Áõ°¡¸¦ À§Çؼ´Â PARAMETER STYLE JAVA ±Ô¾àÀ» »ç¿ëÇÏ¿© Java ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. DB2GENERAL ¸Å°³º¯¼ö ½ºÅ¸ÀÏ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ´Â °Í¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDFÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
DB2 °è¿¿¡¼ÀÇ À̽ļºÀ» Áõ°¡½Ã۱â À§Çؼ´Â GENERAL ¶Ç´Â GENERAL WITH NULLS ¸Å°³º¯¼ö ½ºÅ¸ÀÏÀ» »ç¿ëÇÏ¿© LANGUAGE C ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. DB2DARI ¸Å°³º¯¼ö ½ºÅ¸ÀÏ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ·Á¸é ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDFÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
PARAMETER TYPEÀÌ GENERAL, GENERAL WITH NULLS ¶Ç´Â DB2SQLÀÎ LANGUAGE C ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °æ¿ì ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Ãß°¡ ¸Å°³º¯¼ö¸¦ Çã¿ëÇϵµ·Ï ÀÛ¼ºÇÒ ¼ö ÀÖ´Â ¿É¼ÇÀÌ ÀÖ½À´Ï´Ù. CREATE PROCEDURE¹®¿¡¼ DBINFO¸¦ ÁöÁ¤ÇÏ¿© Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÌ DB2 Ŭ¶óÀÌ¾ðÆ®¿¡ ´ëÇÑ Á¤º¸°¡ Æ÷ÇÔµÈ DBINFO ±¸Á¶¸¦ È£Ã⠸Ű³º¯¼ö¿Í ÇÔ²² ÀúÀå ÇÁ·Î½Ãµà¾î·Î Àü´ÞÇϵµ·Ï Áö½ÃÇÒ ¼ö ÀÖ½À´Ï´Ù. DBINFO ±¸Á¶¿¡´Â ´ÙÀ½°ú °°Àº °ªµéÀÌ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù.
DBINFO ±¸Á¶¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº DBINFO ±¸Á¶ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
´ÙÀ½ ¿¹¿¡¼´Â SAMPLE µ¥ÀÌÅͺ£À̽º¿¡¼ °¡»ó ½Ã³ª¸®¿À¿¡¼ »ç¿ëÇÏ´Â CREATE PROCEDURE¹® ¹× ÀúÀå ÇÁ·Î½Ãµà¾î ¼Ò½º Äڵ带 º¸¿©ÁÝ´Ï´Ù.
empno(SQL type VARCHAR)°¡ ÁÖ¾îÁ³À» ¶§ SAMPLE µ¥ÀÌÅͺ£À̽ºÀÇ EMPLOYEE Å×ÀÌºí¿¡¼ lastname(SQL type CHAR)À» ¸®ÅÏÇÏ´Â Java ÀúÀå ÇÁ·Î½Ãµà¾î GET_LASTNAMEÀ» ÀÛ¼ºÇÑ´Ù°í ÇϽʽÿÀ. myJar·Î¼ ¼³Ä¡µÈ JAR¿¡ Æ÷ÇÔµÈ StoredProcedure Java Ŭ·¡½ºÀÇ getname ¸Þ¼Òµå·Î¼ ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÕ´Ï´Ù. ¸¶Áö¸·À¸·Î C·Î ÄÚµåÈµÈ Å¬¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
String empid; String name; ... #sql { SELECT lastname INTO :empid FROM employee WHERE empno=:empid }
CREATE PROCEDURE GET_LASTNAME (IN EMPID CHAR(6), OUT NAME VARCHAR(15)) EXTERNAL NAME 'myJar:StoredProcedure.getname' LANGUAGE JAVA PARAMETER STYLE JAVA FENCED READS SQL DATA
EXEC SQL BEGIN DECLARE SECTION; struct name { short int; char[15] } char[7] empid; EXEC SQL END DECLARE SECTION; ... EXEC SQL CALL GET_LASTNAME (:empid, :name);
´ÙÀ½ ¿¹¿¡¼ deptnumb(SQL type SMALLINT)ÀÌ ÁÖ¾îÁ³À» ¶§ SAMPLE µ¥ÀÌÅͺ£À̽ºÀÇ ORG Å×ÀÌºí¿¡¼ manager(SQL type SMALLINT)¸¦ ¸®ÅÏÇÏ´Â C ÀúÀå ÇÁ·Î½Ãµà¾î GET_MANAGER¸¦ ÀÛ¼ºÇÑ´Ù°í ÇϽʽÿÀ.
EXEC SQL BEGIN DECLARE SECTION; short onevar = 0; EXEC SQL END DECLARE SECTION;
CREATE PROCEDURE GET_MANAGER (INOUT onevar SMALLINT) EXTERNAL NAME 'stplib!getman' LANGUAGE C PARAMETER STYLE GENERAL FENCED READS SQL DATA
short onevar = 0; ... #SQL { CALL GET_MANAGER (:INOUT onevar) };
ÀúÀå ÇÁ·Î½Ãµà¾î´Â SQL¹®À» Æ÷Ç﵃ ¼ö ÀÖ½À´Ï´Ù. CREATE PROCEDURE¹®À» ¹ßÇàÇÒ ¶§ ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Æ÷ÇÔÇϰí ÀÖ´Â SQL¹® À¯ÇüÀ» ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÒ ¶§ °ªÀ» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥´Â MODIFIES SQL DATA¸¦ »ç¿ëÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ »ç¿ëµÇ´Â SQL À¯ÇüÀ» Á¦ÇÑÇϱâ À§ÇØ ´ÙÀ½ ³× °³ÀÇ ¿É¼Ç Áß Çϳª¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
CREATE PROCEDURE¹®¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
Áßø ÀúÀå ÇÁ·Î½Ãµà¾î´Â ´Ù¸¥ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾îÀÔ´Ï´Ù. ÀÌ ±â¼úÀ» ´ÙÀ½°ú °°Àº Á¦ÇÑ»çÇ׿¡¼ DB2 ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
Áßø SQL ÇÁ·Î½Ãµà¾î´Â Çϳª ÀÌ»óÀÇ °á°ú ¼¼Æ®¸¦ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À̳ª È£ÃâÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î·Î ¸®ÅÏÇÒ ¼ö ¾ø½À´Ï´Ù. SQL ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏÇÏ·Á¸é, WITH RETURN TO CLIENTÀýÀ» »ç¿ëÇÏ¿© DECLARE CURSOR¹®À» ½ÇÇàÇϽʽÿÀ. SQL ÇÁ·Î½Ãµà¾î¿¡¼¼ È£ÃâÀÚ°¡ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À̰ųª È£ÃâÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾îÀΠȣÃâÀÚ·Î °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ·Á¸é, WITH RETURN TO CALLERÀýÀ» »ç¿ëÇÏ¿© DECLARE CURSOR¹®À» ½ÇÇàÇϽʽÿÀ.
C·Î ÀÛ¼ºµÈ Áßø embedd SQL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í ÁßøµÈ CLI ÀúÀå ÇÁ·Î½Ãµà¾î´Â °á°ú ¼¼Æ®¸¦ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À̳ª È£ÃâÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î·Î ¸®ÅÏÇÒ ¼ö ¾ø½À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ÀÖÀ» ¶§ Áßø embedd SQL ÀúÀå ÇÁ·Î½Ãµà¾î ¶Ç´Â Áßø CLI ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Ä¿¼¸¦ ¿¾î ³õÀ¸¸é DB2´Â Ä¿¼¸¦ ´Ý½À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â °Í¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ÀúÀå ÇÁ·Î½Ãµà¾î·ÎºÎÅÍ °á°ú ¼¼Æ® ¸®ÅÏÀ» ÂüÁ¶ÇϽʽÿÀ.
ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¶§ ´ÙÀ½°ú °°Àº Á¦ÇÑ»çÇ×ÀÌ ÀÖ½À´Ï´Ù.
OLE(¿ÀºêÁ§Æ® ¸µÅ© ¹× »ðÀÔ) ÀÚµ¿È´Â Microsoft CorporationÀÇ OLE 2.0 ±¸Á¶ÀÇ ÀϺÎÀÔ´Ï´Ù. DB2´Â OLE ÀÚµ¿È ¿ÀºêÁ§Æ® ¸Þ¼Òµå¸¦ ¿ÜºÎ ÀúÀå ÇÁ·Î½Ãµà¾î·Î¼ È£ÃâÇÒ ¼ö ÀÖ½À´Ï´Ù. OLE ÀÚµ¿È °³¿ä¿¡ ´ëÇÑ ³»¿ëÀº OLE ÀÚµ¿ UDF ÀÛ¼ºÀ» ÂüÁ¶ÇϽʽÿÀ.
OLE ÀÚµ¿È ¿ÀºêÁ§Æ®¸¦ ÄÚµåÈÇÑ ÈÄ CREATE PROCEDURE¹®À» »ç¿ëÇÏ¿© ¿ÀºêÁ§Æ® ¸Þ¼Òµå¸¦ ÀúÀå ÇÁ·Î½Ãµà¾î·Î µî·ÏÇØ¾ß ÇÕ´Ï´Ù. OLE ÀÚµ¿È ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÏ·Á¸é LANGUAGE OLEÀý°ú ÇÔ²² CREATE PROCEDURE¹®À» ½ÇÇàÇØ¾ß ÇÕ´Ï´Ù. ¿ÜºÎ À̸§Àº OLE ÀÚµ¿È ¿ÀºêÁ§Æ®¿Í !(´À³¦Ç¥)·Î ±¸ºÐµÈ ¸Þ¼Òµå À̸§À» ½Äº°ÇÏ´Â OLE progID·Î ±¸¼ºµË´Ï´Ù.
´ÙÀ½ CREATE PROCEDURE¹®Àº OLE ÀÚµ¿È ¿ÀºêÁ§Æ® "db2smpl.salary"ÀÇ "median" ¸Þ¼Òµå¿¡ ´ëÇØ "median"¶ó°í ÇÏ´Â OLE ÀÚµ¿È ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÕ´Ï´Ù.
CREATE PROCEDURE median (INOUT sal DOUBLE) EXTERNAL NAME 'db2smpl.salary!median' LANGUAGE OLE FENCED PARAMETER STYLE DB2SQL
OLE ¸Þ¼Òµå ±¸Çö¿¡ ´ëÇÑ È£Ãâ ±Ô¾àÀº C³ª C++·Î ÀÛ¼ºµÈ ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ ±Ô¾à°ú µ¿ÀÏÇÕ´Ï´Ù.
DB2´Â SQL À¯Çü°ú OLE ÀÚµ¿È À¯Çü°£ÀÇ ÀÚµ¿ À¯Çüº¯È¯À» ó¸®ÇÕ´Ï´Ù. Áö¿øµÇ´Â OLE ÀÚµ¿È À¯Çü°ú SQL À¯Çü°£ÀÇ DB2 ¸ÊÇÎ ¸ñ·Ï¿¡ ´ëÇØ Ç¥ 16À» ÂüÁ¶ÇϽʽÿÀ. SQL À¯Çü°ú BASIC ¶Ç´Â C/C++°ú °°Àº OLE ÇÁ·Î±×·¡¹Ö ¾ð¾îÀÇ µ¥ÀÌÅÍ À¯Çü°£ÀÇ DB2 ¸ÊÇÎ ¸ñ·Ï¿¡ ´ëÇØ Ç¥ 17À» ÂüÁ¶ÇϽʽÿÀ.
DB2¿Í OLE ÀÚµ¿È ÀúÀå ÇÁ·Î½Ãµà¾î°£¿¡ Àü´ÞµÈ µ¥ÀÌÅÍ´Â ÂüÁ¶¿¡ ÀÇÇÑ È£Ãâ·Î Àü´ÞµË´Ï´Ù. DB2´Â ÀÌÀü¿¡ ÂüÁ¶µÈ Å×ÀÌºí¿¡ Ç¥½ÃµÇ¾î ÀÖÁö ¾ÊÀº DECIMAL ¶Ç´Â LOCATORS¿Í °°Àº SQL ¶Ç´Â ºÎ¿ïÀ̳ª CURRENCY¿Í °°Àº OLE ÀÚµ¿È À¯ÇüÀ» Áö¿øÇÏÁö ¾Ê½À´Ï´Ù. BSTR¿¡ ¸ÊÇÎµÈ ¹®ÀÚ ¶Ç´Â ±×·¡ÇÈ µ¥ÀÌÅÍ´Â µ¥ÀÌÅͺ£À̽º ÄÚµå ÆäÀÌÁö¿¡¼ UCS-2(À¯´ÏÄÚµå¶ó°íµµ ÇÏ´Â IBM ÄÚµå ÆäÀÌÁö 13488) ½ºÅ´À¸·Î ¸ÊÇε˴ϴÙ. ¸®ÅϽà µ¥ÀÌÅÍ´Â ´Ù½Ã µ¥ÀÌÅͺ£À̽º ÄÚµå ÆäÀÌÁö·Î º¯È¯µË´Ï´Ù. ÀÌ·¯ÇÑ º¯È¯Àº µ¥ÀÌÅͺ£À̽º ÄÚµå ÆäÀÌÁö¿¡ »ó°ü¾øÀÌ ¹ß»ýÇÕ´Ï´Ù. µ¥ÀÌÅͺ£À̽º ÄÚµå ÆäÀÌÁö¿¡¼ UCS-2·Î ¶Ç´Â UCS-2¿¡¼ µ¥ÀÌÅͺ£À̽º ÄÚµå ÆäÀÌÁö·Î º¯È¯ÇÒ ÄÚµå ÆäÀÌÁö º¯È¯ Å×À̺íÀÌ ¼³Ä¡µÇ¾î ÀÖÁö ¾ÊÀº °æ¿ì, SQLCODE -332(SQLSTATE 57017)¸¦ ¹Þ°Ô µË´Ï´Ù.
´ÙÀ½Àº OUT È£½ºÆ® º¯¼ö »ç¿ëÀÇ »ùÇà ÇÁ·Î±×·¥ÀÔ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº SAMPLE µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â »ç¿øÀÇ Áß°£ ±Þ¿©¸¦ °áÁ¤ÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù. (Áß°£ÀÇ Á¤ÀÇ´Â ÇØ´ç °ªÀÇ ¹ÝÀÌ ±× ¾Æ·¡À§¿¡ ÀÖ´Ù´Â °ÍÀ» ÀǹÌÇÕ´Ï´Ù.) ±×·¯¸é Áß°£ ±Þ¿©°¡ OUT È£½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ¿© Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ´Ù½Ã Àü´ÞµË´Ï´Ù.
ÀÌ »ùÇà ÇÁ·Î±×·¥Àº SAMPLE µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â ¸ðµç »ç¿øÀÇ Áß°£ ±Þ¿©¸¦ °è»êÇÕ´Ï´Ù. Áß°£ ±Þ¿©¸¦ °è»êÇϱâ À§ÇÑ SQL Ä÷³ ÇÔ¼ö°¡ ¾øÀ¸¹Ç·Î, Áß°£ ±Þ¿©´Â ´ÙÀ½ ¾Ë°í¸®Áò¿¡ ÀÇÇØ ´ëȽÄÀ¸·Î ó¸®µÉ ¼ö ÀÖ½À´Ï´Ù.
ÀúÀå ÇÁ·Î½Ãµà¾î ±â¼ú°ú ºí·ÎÅ· Ä¿¼¸¦ »ç¿ëÇÏÁö ¾Ê´Â ÀÀ¿ëÇÁ·Î±×·¥Àº ±×¸² 5¿¡¼¿Í °°ÀÌ ³×Æ®¿öÅ©¸¦ ÅëÇØ °¢ ±Þ¿©¸¦ FETCHÇØ¾ß ÇÕ´Ï´Ù.
±×¸² 5. ÀúÀå ÇÁ·Î½Ãµà¾î ¾ø´Â Áß°£ »ùÇÃ
![]() |
n / 2 + 1 ÇàÀÇ ±Þ¿©¸¸ ÇÊ¿äÇϹǷÎ, ÀÀ¿ëÇÁ·Î±×·¥Àº ¸ðµç Ãß°¡ µ¥ÀÌÅ͸¦ ¹ö¸®Áö¸¸ ³×Æ®¿öÅ©¸¦ ÅëÇØ Àü¼ÛµÈÈÄ¿¡¸¸ °¡´ÉÇÕ´Ï´Ù.
Áß°£ ±Þ¿©¸¸ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏÇÏ¿©, ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ºÒÇÊ¿äÇÑ µ¥ÀÌÅ͸¦ ó¸® ¹× Á¦°ÅÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î ±â¼úÀ» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥À» ¼³°èÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×¸² 6¿¡ ÀÌ·¯ÇÑ ±â´ÉÀÌ ³ª¿Í ÀÖ½À´Ï´Ù.
±×¸² 6. ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ »ç¿ëÇÏ´Â OUT ¸Å°³º¯¼ö »ùÇÃ
![]() |
OUT Ŭ¶óÀÌ¾ðÆ® ¼³¸í¿¡´Â »ùÇà OUT È£½ºÆ® º¯¼ö Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ ¹× ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Ç¥½ÃµË´Ï´Ù. »ùÇà ÇÁ·Î±×·¥Àº ´ÙÀ½°ú °°ÀÌ Java·Î »ç¿ë °¡´ÉÇÕ´Ï´Ù.
»ùÇà ÇÁ·Î±×·¥Àº ´ÙÀ½°ú °°ÀÌ C·Î »ç¿ë °¡´ÉÇÕ´Ï´Ù.
CHECKERR ¸ÅÅ©·Î/ÇÔ¼ö´Â ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÔ´Ï´Ù. ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÇ À§Ä¡´Â »ç¿ëµÈ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡ µû¶ó ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù.
ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼¿¡ ´ëÇÑ ¼Ò½º Äڵ忡 ´ëÇØ¼´Â ¿¹Á¦ ÇÁ·Î±×·¥¿¡¼ GET ERROR MESSAGE »ç¿ë¿¡¼ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
import java.sql.*; // JDBC classes (1) import java.math.BigDecimal; // BigDecimal support for packed decimal type class Spclient { static String sql = ""; static String procName = ""; static String inLanguage = ""; static CallableStatement callStmt; static int outErrorCode = 0; static String outErrorLabel = ""; static double outMedian = 0; static { try { System.out.println(); System.out.println("Java Stored Procedure Sample"); Class.forName("COM.ibm.db2.jdbc.app.DB2Driver").newInstance(); } catch (Exception e) { System.out.println("\nError loading DB2 Driver...\n"); e.printStackTrace(); } } public static void main(String argv[]) { Connection con = null; // URL is jdbc:db2:dbname String url = "jdbc:db2:sample"; try { // connect to sample database // connect with default id/password con = DriverManager.getConnection(url); (2) // turn off autocommit con.setAutoCommit(false); (3) outLanguage(con); outParameter(con); inParameters(con); inoutParam(con, outMedian); resultSet(con); twoResultSets(con); allDataTypes(con); // rollback any changes to the database con.rollback(); (8) con.close(); } catch (Exception e) { try { con.close(); } catch (Exception x) { } e.printStackTrace (); } } // end main public static void outParameter(Connection con) throws SQLException { // prepare the CALL statement for OUT_PARAM procName = "OUT_PARAM"; sql = "CALL " + procName + "(?, ?, ?)"; callStmt = con.prepareCall(sql); // register the output parameter (4) callStmt.registerOutParameter (1, Types.DOUBLE); 0 callStmt.registerOutParameter (2, Types.INTEGER); callStmt.registerOutParameter (3, Types.CHAR); 0 // call the stored procedure (5) System.out.println ("\nCall stored procedure named " + procName); callStmt.execute(); // retrieve output parameters (6) outMedian = callStmt.getDouble(1); outErrorCode = callStmt.getInt(2); outErrorLabel = callStmt.getString(3); if (outErrorCode == 0) { (7) System.out.println(procName + " completed successfully"); System.out.println ("Median salary returned from OUT_PARAM = " + outMedian); } else { // stored procedure failed System.out.println(procName + " failed with SQLCODE " + outErrorCode); System.out.println(procName + " failed at " + outErrorLabel); } } }
#include <stdio.h> (1) #include <stdlib.h> #include <sql.h> #include <sqlda.h> #include <sqlca.h> #include <string.h> #include "utilemb.h" EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* Declare host variable for stored procedure name */ char procname[254]; /* Declare host variables for stored procedure error handling */ sqlint32 out_sqlcode; (4) char out_buffer[33]; EXEC SQL END DECLARE SECTION; int main(int argc, char *argv[]) { EXEC SQL CONNECT TO sample; (2) EMB_SQL_CHECK("CONNECT TO SAMPLE"); outparameter(); EXEC SQL ROLLBACK; EMB_SQL_CHECK("ROLLBACK"); printf("\nStored procedure rolled back.\n\n"); /* Disconnect from Remote Database */ EXEC SQL CONNECT RESET; (8) EMB_SQL_CHECK("CONNECT RESET"); return 0; } int outparameter() { /********************************************************\ * Call OUT_PARAM stored procedure * \********************************************************/ EXEC SQL BEGIN DECLARE SECTION; /* Declare host variables for passing data to OUT_PARAM */ double out_median; EXEC SQL END DECLARE SECTION; strcpy(procname, "OUT_PARAM"); printf("\nCALL stored procedure named %s\n", procname); /* OUT_PARAM is PS GENERAL, so do not pass a null indicator */ EXEC SQL CALL :procname (:out_median, :out_sqlcode, :out_buffer); (5) (6) EMB_SQL_CHECK("CALL OUT_PARAM"); /* Check that the stored procedure executed successfully */ if (out_sqlcode == 0) (7) { printf("Stored procedure returned successfully.\n"); /***********************************************************\ * Display the median salary returned as an output parameter * \***********************************************************/ printf("Median salary returned from OUT_PARAM = %8.2f\n", out_median); } else { /* print the error message, roll back the transaction */ printf("Stored procedure returned SQLCODE %d\n", out_sqlcode); printf("from procedure section labelled \"%s\".\n", out_buffer); } return 0; }
import java.sql.*; // JDBC classes import COM.ibm.db2.jdbc.app.*; // DB2 JDBC classes import java.math.BigDecimal; // Packed Decimal class public class Spserver { public static void outParameter (double[] medianSalary, int[] errorCode, String[] errorLabel) throws SQLException (1) { try { int numRecords; int counter = 0; errorCode[0] = 0; // SQLCODE = 0 unless SQLException occurs // Get caller's connection to the database Connection con = DriverManager.getConnection("jdbc:default:connection"); errorLabel[0] = "GET CONNECTION"; String query = "SELECT COUNT(*) FROM staff"; errorLabel[0] = "PREPARE COUNT STATEMENT"; PreparedStatement stmt = con.prepareStatement(query); errorLabel[0] = "GET COUNT RESULT SET"; ResultSet rs = stmt.executeQuery(); // move to first row of result set rs.next(); // set value for the output parameter errorLabel[0] = "GET NUMBER OF RECORDS"; numRecords = rs.getInt(1); (3) // clean up first result set rs.close(); stmt.close(); // get salary result set query = "SELECT CAST(salary AS DOUBLE) FROM staff " + "ORDER BY salary"; errorLabel[0] = "PREPARE SALARY STATEMENT"; PreparedStatement stmt2 = con.prepareStatement(query); errorLabel[0] = "GET SALARY RESULT SET"; ResultSet rs2 = stmt2.executeQuery(); (2) while (counter < (numRecords / 2 + 1)) { errorLabel[0] = "MOVE TO NEXT ROW"; rs2.next(); (4) counter++; } errorLabel[0] = "GET MEDIAN SALARY"; medianSalary[0] = rs2.getDouble(1); (5) // clean up resources rs2.close(); stmt2.close(); con.close(); (6) } catch (SQLException sqle) { errorCode[0] = sqle.getErrorCode(); } } }
#include <stdio.h> #include <string.h> #include <stdlib.h> #include <sqlda.h> #include <sqlca.h> #include <sqludf.h> #include <sql.h> #include <memory.h> /* Declare function prototypes for this stored procedure library */ SQL_API_RC SQL_API_FN out_param (double *, sqlint32 *, char *); (1) EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; /* Declare host variables for basic error-handling */ sqlint32 out_sqlcode; char buffer[33]; /* Declare host variables used by multiple stored procedures */ sqlint16 numRecords; double medianSalary; EXEC SQL END DECLARE SECTION; SQL_API_RC SQL_API_FN out_param (double *outMedianSalary, sqlint32 *out_sqlerror, char buffer[33]) { EXEC SQL INCLUDE SQLCA; EXEC SQL WHENEVER SQLERROR GOTO return_error; int counter = 0; *out_sqlerror = 0; strcpy(buffer, "DECLARE c1"); EXEC SQL DECLARE c1 CURSOR FOR (2) SELECT CAST(salary AS DOUBLE) FROM staff ORDER BY salary; strcpy(buffer, "SELECT"); EXEC SQL SELECT COUNT(*) INTO :numRecords FROM staff; (3) strcpy(buffer, "OPEN"); EXEC SQL OPEN c1; strcpy(buffer, "FETCH"); while (counter < (numRecords / 2 + 1)) { EXEC SQL FETCH c1 INTO :medianSalary; (4) /* Set value of OUT parameter to host variable */ *outMedianSalary = medianSalary; (5) counter = counter + 1; } strcpy(buffer, "CLOSE c1"); EXEC SQL CLOSE c1; return (0); (6) /* Copy SQLCODE to OUT parameter if SQL error occurs */ return_error: { *out_sqlerror = SQLCODE; EXEC SQL WHENEVER SQLERROR CONTINUE; return (0); } } /* end out_param function */
ÄÚµå ÆäÀÌÁö À¯ÀÇ»çÇ×Àº ¼¹ö¿¡ µû¶ó ´Ù¸¨´Ï´Ù.
(ÄÚµå ÆäÀÌÁö A¿Í °°Àº) Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥ÀÌ (ÄÚµå ÆäÀÌÁö Z¿Í °°Àº) ´Ù¸¥ ÄÚµå ÆäÀÌÁö¸¦ »ç¿ëÇÏ´Â µ¥ÀÌÅͺ£À̽º¿¡ ¾×¼¼½ºÇÏ´Â ¿ø°Ý ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¶§, ´ÙÀ½ À̺¥Æ®°¡ ¹ß»ýÇÕ´Ï´Ù.
µû¶ó¼, µ¥ÀÌÅͺ£À̽º¿Í °°Àº ÄÚµå ÆäÀÌÁö, ÀÌ ¿¹¿¡¼´Â ÄÚµå ÆäÀÌÁö Z¸¦ »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ¼öÇàÇØ¾ß ÇÕ´Ï´Ù. µ¥ÀÌÅͺ£À̽º¿Í °°Àº ÄÚµå ÆäÀÌÁö¸¦ »ç¿ëÇÏ¿© ¼¹ö ÇÁ·Î½Ãµà¾î¸¦ Áغñ, ÄÄÆÄÀÏ ¹× ¹ÙÀεåÇØ º¸´Â °ÍÀÌ ÁÁ½À´Ï´Ù.
ÁÖ: | ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ¸Å°³º¯¼ö°¡ ¼¹ö¿¡¼ FOR BIT DATA·Î¼ Á¤ÀǵǾî ÀÖ´Â °æ¿ì, SQLDA¿¡¼ ¸í½ÃÀûÀ¸·Î ÁöÁ¤µÇ¾ú´ÂÁö¿¡ °ü°è¾øÀÌ CALL¹®¿¡ ´ëÇØ OS/390¿ë DB2 Universal Database ¶Ç´Â AS/400¿ë DB2 Universal DatabaseÀ¸·ÎÀÇ º¯È¯ÀÌ ÀÌ·ç¾îÁöÁö ¾Ê½À´Ï´Ù. (¼¼ºÎ»çÇ×Àº SQL ÂüÁ¶¼¿¡ ÀÖ´Â SQLDAÀÇÀýÀ» ÂüÁ¶ÇϽʽÿÀ.) |
ÀÌ ÁÖÁ¦¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº ´Ù¸¥ ÄÚµå ÆäÀÌÁö °£ÀÇ º¯È¯ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
C++¿¡¼ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¶§ ´ÙÀ½ ¿¹¿¡¼¿Í °°ÀÌ extern "C"¸¦ »ç¿ëÇÏ¿© ÇÁ·Î½Ãµà¾î À̸§À» ¼±¾ðÇÒ ¼ö ÀÖ½À´Ï´Ù.
extern "C" SQL_API_RC SQL_API_FN proc_name( short *parm1, char *parm2)
extern "C"´Â C++ ÄÄÆÄÀÏ·¯¿¡ ÀÇÇØ ÇÔ¼ö À̸§ÀÇ À¯Çü µ¥ÄÚ·¹À̼Ç(¶Ç´Â mangling)À» ¹æÁöÇÕ´Ï´Ù. ÀÌ·¯ÇÑ µ¥ÄÚ·¹ÀÌ¼Ç ¾øÀÌ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¶§ ÇÔ¼ö À̸§¿¡ ´ëÇØ ¸ðµç À¯Çü µ¥ÄÚ·¹À̼ÇÀ» Æ÷ÇÔ½ÃÄÑ¾ß ÇÕ´Ï´Ù.
¸Å°³º¯¼ö ÀÔ·Â ¶Ç´Â Ãâ·ÂÀ» ÅëÇØ ±×·¡ÇÈ µ¥ÀÌÅ͸¦ ¹Þ°Å³ª ¸®ÅÏÇÏ´Â C ¶Ç´Â C++·Î ÀÛ¼ºµÈ ÀúÀå ÇÁ·Î½Ãµà¾î´Â ÀϹÝÀûÀ¸·Î WCHARTYPE NOCONVERT ¿É¼ÇÀ» »ç¿ëÇÏ¿© »çÀü ó¸® ÄÄÆÄÀϵ˴ϴÙ. ÀÌ´Â ÀÌ·¯ÇÑ ¸Å°³º¯¼ö¸¦ ÅëÇØ Àü´ÞµÈ ±×·¡ÇÈ µ¥ÀÌÅͰ¡ wchar_t ÇÁ·Î¼¼½º ÄÚµå Çü½ÄÀÌ ¾Æ´Ñ DBCS Çü½ÄÀ¸·Î °£ÁֵDZ⠶§¹®ÀÔ´Ï´Ù. NOCONVERT¸¦ »ç¿ëÇÑ´Ù´Â °ÍÀº, ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ SQL¹®¿¡¼ Á¶À۵Ǵ ±×·¡ÇÈ µ¥ÀÌÅÍ´Â ¸Å°³º¯¼ö µ¥ÀÌÅÍ Çü½Ä°ú ÀÏÄ¡ÇÏ´Â DBCS Çü½ÄÀ̱⵵ ÇÕ´Ï´Ù.
WCHARTYPE NOCONVERT¸¦ ÀÌ¿ëÇÏ¿© ±×·¡ÇÈ È£½ºÆ® º¯¼ö¿Í µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ »çÀÌ¿¡ ¹®ÀÚ ÄÚµå º¯È¯ÀÌ ÀÌ·ç¾îÁý´Ï´Ù. ±×·¡ÇÈ È£½ºÆ® º¯¼öÀÇ µ¥ÀÌÅͰ¡ ±³Ã¼µÇÁö ¾Ê´Â DBCS ¹®Àڷμ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À¸·Î Àü¼Û ¹× µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À¸·ÎºÎÅÍ ¼ö½ÅµË´Ï´Ù. WCHARTYPE NOCONVERT¸¦ »ç¿ëÇÏÁö ¾ÊÀ» °æ¿ì ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ wchar_t Çü½ÄÀ¸·Î ±×·¡ÇÈ µ¥ÀÌÅ͸¦ Á¶ÀÛÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×·¯³ª, ¼öµ¿À¸·Î ÀÔÃâ·Â º¯È¯À» ¼öÇàÇØ¾ß ÇÕ´Ï´Ù.
CONVERT´Â FENCED ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼ »ç¿ëÇÒ ¼ö ÀÖÀ¸¸ç, ÀúÀå ÇÁ·Î½Ãµà¾î ³»¿¡¼ SQL¹®¿¡ ÀÖ´Â ±×·¡ÇÈ µ¥ÀÌÅÍ¿¡ ¿µÇâÀ» ÁÖÁö¸¸ ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ÀÎÅÍÆäÀ̽º¸¦ ÅëÇÑ µ¥ÀÌÅÍ¿¡´Â ¿µÇâÀ» ÁÖÁö ¾Ê½À´Ï´Ù. NOT FENCED ÀúÀå ÇÁ·Î½Ãµà¾î´Â NOCONVERT ¿É¼ÇÀ» »ç¿ëÇÏ¿© ±¸ÃàÇØ¾ß ÇÕ´Ï´Ù.
¿ä¾àÇØ¼, ÀÔ·Â ¶Ç´Â Ãâ·Â ¸Å°³º¯¼ö¸¦ ÅëÇØ ÀúÀå ÇÁ·Î½Ãµà¾î·ÎºÎÅÍ ¸®Åϵǰųª ÀúÀå ÇÁ·Î½Ãµà¾î·Î Àü´ÞµÇ´Â ±×·¡ÇÈ µ¥ÀÌÅÍ´Â, WCHARTYPE ¿É¼Ç°ú ÇÔ²² »çÀü ó¸® ÄÄÆÄÀÏµÈ ¹æ½Ä¿¡ °ü°è¾øÀÌ, DBCS Çü½ÄÀ̾î¾ß ÇÕ´Ï´Ù.
C ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ÀÇ ±×·¡ÇÈ µ¥ÀÌÅÍ Ã³¸®¿¡ ´ëÇÑ ÁÖ¿ä Á¤º¸´Â C ¹× C++¿¡¼ÀÇ ±×·¡ÇÈ È£½ºÆ® º¯¼ö ó¸®ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. EUC ÄÚµå ¼¼Æ® ¹× ÀÀ¿ëÇÁ·Î±×·¥ ¾È³»¼¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº ÀϺ»¾î ¹× ´ë¸¸¾î EUC ¹× UCS-2 ÄÚµå ¼¼Æ® º¯È¯ÀÇ ³»¿ëÀ», º¸´Ù ÀÚ¼¼ÇÑ ³»¿ëÀº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ °í·Á»çÇ×ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
CONNECT TYPE 2¿Í ÇÔ²² ÀÀ¿ëÇÁ·Î±×·¥ÀÌ È£ÃâÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î´Â µ¿ÀûÀ¸·Î ¶Ç´Â Á¤ÀûÀ¸·Î COMMIT ¶Ç´Â ROLLBACKÀ» ¹ßÇàÇÒ ¼ö ¾ø½À´Ï´Ù.