ÀÀ¿ëÇÁ·Î±×·¥ °³¹ß ¾È³»¼­


ÀúÀå ÇÁ·Î½Ãµà¾î ÀÛ¼º

ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ Æ÷ÇÔÇÏ´Â ÀÀ¿ëÇÁ·Î±×·¥ ¼³°è´Â º°µµÀÇ Å¬¶óÀÌ¾ðÆ®¿Í ¼­¹ö ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ±¸¼ºµË´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¶ó´Â ¼­¹ö ÀÀ¿ëÇÁ·Î±×·¥Àº ¼­¹öÀÇ °øÀ¯ ¶óÀ̺귯¸® ¶Ç´Â Ŭ·¡½º ¶óÀ̺귯¸®¿¡ µé¾î ÀÖ½À´Ï´Ù. µ¥ÀÌÅͺ£À̽º°¡ »óÁÖÇÏ´Â ¼­¹ö ÀνºÅϽº¿¡¼­ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÄÄÆÄÀÏÇÏ°í ¾×¼¼½ºÇØ¾ß ÇÕ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡´Â ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ CALL¹®ÀÌ ÀÖ½À´Ï´Ù. CALL¹®Àº ¸Å°³º¯¼ö¸¦ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Àü´ÞÇϰí ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ ¸Å°³º¯¼ö¸¦ ¸®ÅÏÇÒ ¼ö ÀÖ½À´Ï´Ù. ´Ù¸¥ ¾ð¾î¸¦ »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¿Í Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿Í ´Ù¸¥ Ç÷§Æû¿¡¼­ ½ÇÇàµÉ ¼ö ÀÖ½À´Ï´Ù.

Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½ Ÿ½ºÅ©¸¦ ¼öÇàÇÕ´Ï´Ù.

  1. ¼±ÅÃÀû µ¥ÀÌÅÍ ±¸Á¶ ¹× È£½ºÆ® º¯¼ö¿¡ ´ëÇÑ ÀúÀ念¿ªÀ» ¼±¾ð, ÇÒ´ç ¹× ÃʱâÈ­ÇÕ´Ï´Ù.
  2. ³»ÀçµÈ ¿¬°áÀ» ¼öÇàÇϰųª CONNECT TO¹®À» ½ÇÇàÇÏ¿© µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇÕ´Ï´Ù. SQL ÂüÁ¶¼­¿¡¼­ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
  3. SQL CALL¹®À» ÅëÇØ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
  4. µ¥ÀÌÅͺ£À̽º·Î COMMIT ¶Ç´Â ROLLBACKÀ» ¹ßÇàÇÕ´Ï´Ù.

    ÁÖ:ÀúÀå ÇÁ·Î½Ãµà¾î°¡ COMMIT ¶Ç´Â ROLLBACK¹®À» ½ÇÇàÇÒ ¼ö ÀÖÁö¸¸ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ COMMIT ¶Ç´Â ROLLBACKÀ» ½ÇÇàÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. À̸¦ ÅëÇØ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ ¸®ÅÏµÈ µ¥ÀÌÅ͸¦ Æò°¡ÇÏ°í Æ®·£Àè¼ÇÀ» È®¾àÇÒ °ÍÀÎÁö ¾Æ´Ï¸é ±¸°£ º¹¿øÇÒ °ÍÀÎÁö¸¦ °áÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù.

  5. µ¥ÀÌÅͺ£À̽º·ÎºÎÅÍÀÇ ¿¬°áÀ» ÇØÁ¦ÇÕ´Ï´Ù.

À§ ´Ü°è¸¦ ÅëÇØ SQL¹®À» ÄÚµåÈ­ÇÒ ¼ö ÀÖ½À´Ï´Ù.

ÀúÀå ÇÁ·Î½Ãµà¾î°¡ È£ÃâµÇ¸é ´ÙÀ½ Ÿ½ºÅ©¸¦ ¼öÇàÇÏ°Ô µË´Ï´Ù.

  1. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·ÎºÎÅÍ ¸Å°³º¯¼ö¸¦ ¹Þ½À´Ï´Ù.
  2. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥°ú °°Àº Æ®·£Àè¼ÇÇÏ¿¡¼­ µ¥ÀÌÅͺ£À̽º ¼­¹ö¿¡¼­ ½ÇÇàµË´Ï´Ù.
  3. ¼±ÅÃÀûÀ¸·Î Çϳª ÀÌ»óÀÇ COMMIT ¶Ç´Â ROLLBACK¹®À» ½ÇÇàÇϽʽÿÀ.

    ÁÖ:ÀúÀå ÇÁ·Î½Ãµà¾î°¡ COMMIT ¶Ç´Â ROLLBACK¹®À» ½ÇÇàÇÒ ¼ö ÀÖÁö¸¸ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ COMMIT ¶Ç´Â ROLLBACKÀ» ½ÇÇàÇÏ´Â °ÍÀÌ ÁÁ½À´Ï´Ù. À̸¦ ÅëÇØ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ ¸®ÅÏµÈ µ¥ÀÌÅ͸¦ Æò°¡ÇÏ°í Æ®·£Àè¼ÇÀ» È®¾àÇÒ °ÍÀÎÁö ¾Æ´Ï¸é ±¸°£ º¹¿øÇÒ °ÍÀÎÁö¸¦ °áÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù.

  4. SQLCA Á¤º¸ ¹× ¼±ÅÃÀû Ãâ·Â µ¥ÀÌÅ͸¦ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏÇÕ´Ï´Ù.

ÀúÀå ÇÁ·Î½Ãµà¾î´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ ÀÇÇØ È£ÃâµÉ ¶§ ½ÇÇàµË´Ï´Ù. ¼­¹ö ÇÁ·Î½Ãµà¾î°¡ 󸮸¦ Á¾·áÇÒ ¶§ Ŭ¶óÀÌ¾ðÆ®·Î Á¦¾î°¡ ¸®Åϵ˴ϴÙ. ¿©·¯ °³ÀÇ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÇϳªÀÇ ¶óÀ̺귯¸®¿¡ µÑ ¼ö ÀÖ½À´Ï´Ù.

ÀÌ Àå¿¡¼­´Â ´ÙÀ½°ú °°Àº ¸Å°³º¯¼ö ½ºÅ¸ÀÏÀ» ÀÌ¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ´Â ¹æ¹ý¿¡ ´ëÇØ ¼³¸íÇÕ´Ï´Ù.

DB2SQL
ÀúÀå ÇÁ·Î½Ãµà¾î´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÇ CALL¹®À¸·ÎºÎÅÍ CREATE PROCEDURE¹®¿¡¼­ È£½ºÆ® º¯¼ö·Î ¼±¾ðµÇ´Â ¸Å°³º¯¼ö¸¦ ¹Þ½À´Ï´Ù. DB2´Â DB2SQL ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Ãß°¡ ¸Å°³º¯¼ö¸¦ ÇÒ´çÇÕ´Ï´Ù.

GENERAL
ÀúÀå ÇÁ·Î½Ãµà¾î´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ CALL¹®À¸·ÎºÎÅÍ È£½ºÆ® º¯¼ö·Î¼­ ¸Å°³º¯¼ö¸¦ ¹Þ½À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î´Â ³Î(NULL) Ç¥½Ã±â¸¦ Á÷Á¢ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ Àü´ÞÇÏÁö ¾Ê½À´Ï´Ù. GENERALÀº OS/390¿ë DB2 Universal Database¿¡ ´ëÇÑ SIMPLE ÀúÀå ÇÁ·Î½Ãµà¾î¿Í °°½À´Ï´Ù.

GENERAL WITH NULLS
»ç¿ëÀÚ°¡ ¼±¾ðÇÏ´Â °¢ ¸Å°³º¯¼ö¿¡ ´ëÇØ DB2´Â ÇØ´ç INOUT ¸Å°³º¯¼ö ³Î Ç¥½Ã±â¸¦ ÇÒ´çÇÕ´Ï´Ù. GENERAL°ú ¸¶Âù°¡Áö·Î, ¸Å°³º¯¼ö´Â È£½ºÆ® º¯¼ö·Î¼­ Àü´ÞµË´Ï´Ù. GENERAL WITH NULLSÀº OS/390¿ë DB2 Universal Database¿¡ ´ëÇÑ SIMPLE WITH NULLS ÀúÀå ÇÁ·Î½Ãµà¾î¿Í °°½À´Ï´Ù.

JAVA
ÀúÀå ÇÁ·Î½Ãµà¾î´Â SQLJ ·çƾ ½ºÆå°ú ÀÏÄ¡Çϴ±ԾàÀ» Àü´ÞÇÏ´Â ¸Å°³º¯¼ö¸¦ »ç¿ëÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î´Â È£½ºÆ® º¯¼ö·Î¼­ IN ¸Å°³º¯¼ö¸¦ ¹Þ°í, ´ÜÀÏ Ç׸ñ ¹è¿­·Î¼­ OUT ¹× INOUT ¸Å°³º¯¼ö¸¦ ¹Þ½À´Ï´Ù.

»ç¿ëÀÚ´Â ¹Ýµå½Ã 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ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

È£½ºÆ® º¯¼ö ÇÒ´ç

ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ Å¬¶óÀ̾ðÆ®Ãø¿¡ ÇÊ¿äÇÑ ÀԷ ȣ½ºÆ® º¯¼ö¸¦ ÇÒ´çÇÏ·Á¸é ´ÙÀ½ ´Ü°è¸¦ »ç¿ëÇϽʽÿÀ.

  1. ÀúÀå ÇÁ·Î½Ãµà¾î·Î Àü´ÞµÉ ¸ðµç ÀÔ·Â º¯¼ö¿¡ ´ëÇØ ÃæºÐÇÑ È£½ºÆ® º¯¼ö¸¦ ¼±¾ðÇϽʽÿÀ.
  2. ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ Ŭ¶óÀÌ¾ðÆ®·Î °ªÀ» µÇµ¹¸®±â À§ÇØ ¾î¶² ÀԷ ȣ½ºÆ® º¯¼ö¸¦ »ç¿ëÇÒ ¼ö ÀÖ´ÂÁöµµ °áÁ¤ÇϽʽÿÀ.
  3. ÀúÀå ÇÁ·Î½Ãµà¾î·ÎºÎÅÍ Å¬¶óÀÌ¾ðÆ®·Î ¸®ÅϵǴ Ãß°¡ °ª¿¡ ´ëÇÑ È£½ºÆ® º¯¼ö¸¦ ¼±¾ðÇϽʽÿÀ.

ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ Å¬¶óÀÌ¾ðÆ® ºÎºÐÀ» ÀÛ¼ºÇÒ ¶§ ÀÔ·Â ¹× Ãâ·Â ¸ðµÎ¿¡ ´ëÇØ »ç¿ëÇÔÀ¸·Î½á °¡´ÉÇÑ ÇÑ ¸¹Àº È£½ºÆ® º¯¼ö¸¦ ¿À¹ö·ÎµåÇØ¾ß ÇÕ´Ï´Ù. ±× °á°ú ¿©·¯ È£½ºÆ® º¯¼ö¸¦ ó¸®ÇÏ´Â µ¥ ÀÖ¾î È¿À²¼ºÀÌ Áõ°¡µË´Ï´Ù. ¿¹¸¦ µé¾î, 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 ¿¹¸¦ ÂüÁ¶ÇϽʽÿÀ.

IN
Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·ÎºÎÅÍ ÀúÀå ÇÁ·Î½Ãµà¾î·Î °ªÀ» Àü´ÞÇÏÁö¸¸, Á¦¾î°¡ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®Å쵃 ¶§ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î °ªÀ» ¸®ÅÏÇÏÁö ¾Ê½À´Ï´Ù.

OUT
ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Á¾·áµÉ ¶§ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î Àü´ÞµÈ °ªÀ» ÀúÀåÇÕ´Ï´Ù.

INOUT
Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·ÎºÎÅÍ ÀúÀå ÇÁ·Î½Ãµà¾î·Î °ªÀ» Àü´ÞÇϰí, ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Á¾·áµÉ ¶§ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î °ªÀ» ¸®ÅÏÇÕ´Ï´Ù.

À§Ä¡

CREATE PROCEDURE¹®ÀÇ EXTERNALÀýÀº ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Æ÷ÇÔµÈ ¶óÀ̺귯¸®ÀÇ À§Ä¡¸¦ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡°Ô ¾Ë·Á ÁÝ´Ï´Ù. ¶óÀ̺귯¸®¿¡ Àý´ë °æ·Î¸¦ ÁöÁ¤ÇÏÁö ¾Ê°Å³ª, Java ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ jar À̸§À» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ÇÔ¼ö µð·ºÅ丮¸¦ °Ë»öÇÕ´Ï´Ù. ÇÔ¼ö µð·ºÅ丮´Â ´ÙÀ½°ú °°ÀÌ »ç¿ëÀÚ ¿î¿µ üÁ¦¿¡ ´ëÇØ Á¤ÀÇµÈ µð·ºÅ丮ÀÔ´Ï´Ù.

Unix ¿î¿µ üÁ¦
sqllib/function

OS/2 ¶Ç´Â Windows 32 ºñÆ® ¿î¿µ üÁ¦
instance_name\function, ¿©±â¼­ instance_nameÀº DB2INSTPROF ÀνºÅϽº °íÀ¯ÀÇ ·¹Áö½ºÆ®¸® ¼³Á¤ °ªÀ» Ç¥½ÃÇÕ´Ï´Ù. DB2INSTPROF°¡ ¼³Á¤µÇÁö ¾ÊÀ¸¸é instance_nameÀº %DB2PATH% ȯ°æ º¯¼öÀÇ °ªÀ» ³ªÅ¸³À´Ï´Ù. %DB2PATH% ȯ°æ º¯¼öÀÇ ±âº»°ªÀº DB2¸¦ ¼³Ä¡ÇÑ °æ·ÎÀÔ´Ï´Ù.

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 Ű¿öµå¸¦ Á¤ÀÇÇÕ´Ï´Ù.

jar-file-name
µ¥ÀÌÅͺ£À̽º¿¡ ¼³Ä¡µÈ jar ÆÄÀÏ¿¡ ÀúÀå ÇÁ·Î½Ãµà¾î ¸Þ¼Òµå°¡ µé¾î ÀÖÀ¸¸é ÀÌ °ªÀ» Æ÷ÇÔÇØ¾ß ÇÕ´Ï´Ù. Ű¿öµå´Â jar ÆÄÀÏÀÇ À̸§À» ³ªÅ¸³»°í ÄÝ·Ð(:)À¸·Î ±¸ºÐµË´Ï´Ù. jar ÆÄÀϸíÀ» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ÇÔ¼ö µð·ºÅ丮¿¡ Àִ Ŭ·¡½º¸¦ °Ë»öÇÕ´Ï´Ù. jar ÆÄÀÏ ¼³Ä¡¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº Java ÀúÀå ÇÁ·Î½Ãµà¾î ¹× UDFÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

class-name
ÀúÀå ÇÁ·Î½Ãµà¾î ¸Þ¼Òµå°¡ µé¾î Àִ Ŭ·¡½º À̸§. Ŭ·¡½º°¡ ÆÐŰÁöÀÇ ÀϺÎÀÌ¸é ¿ÏÀüÇÑ ÆÐŰÁö À̸§À» Á¢µÎ»ç·Î Æ÷ÇÔ½ÃÄÑ¾ß ÇÕ´Ï´Ù.

method-name
ÀúÀå ÇÁ·Î½Ãµà¾î ¸Þ¼Òµå À̸§

¿¹¸¦ µé¾î, MyPackage.MyClass.myMethod¸¦ ÁöÁ¤ÇÏ¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº MyPackage ÆÐŰÁöÀÇ MyClass Ŭ·¡½º¿¡¼­ myMethod ¸Þ¼Òµå¸¦ »ç¿ëÇÕ´Ï´Ù. DB2´Â MyPackage°¡ ÄÝ·Ð(:) ºÐ¸®¹®ÀÚ ´ë½Å ¸¶Ä§Ç¥(.) ºÐ¸®¹®ÀÚ¸¦ »ç¿ëÇϹǷΠjar ÆÄÀÏ ´ë½Å ÆÐŰÁö¸¦ ÂüÁ¶ÇÕ´Ï´Ù. DB2´Â MyPackage ÆÐŰÁö¿¡ ´ëÇØ ÇÔ¼ö µð·ºÅ丮¸¦ °Ë»öÇÕ´Ï´Ù.

ÇÔ¼ö µð·ºÅ丮¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº À§Ä¡ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

LANGUAGE

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ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

LANGUAGE C
µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ANSI C È£Ãâ ¹× ¿¬°á ±Ô¾àÀ» »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù. ´ëºÎºÐÀÇ C/C++ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ¼­ ÀÌ ¿É¼ÇÀ» »ç¿ëÇϽʽÿÀ.

LANGUAGE JAVA
µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº Java Ŭ·¡½ºÀÇ ¸Þ¼Òµå·Î¼­ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù. Java ÀúÀå ÇÁ·Î½Ãµà¾î¿¡´Â ÀÌ ¿É¼ÇÀ» »ç¿ëÇϽʽÿÀ.

LANGUAGE OLE
µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ OLE ÇÔ¼ö·Î È£ÃâÇÕ´Ï´Ù. Windows 32-ºñÆ® ¿î¿µ üÁ¦¿¡¼­ ¸ðµç OLE ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ÀÌ ¿É¼ÇÀ» »ç¿ëÇϽʽÿÀ. CREATE PROCEDURE¹®À» ½ÇÇàÇϱâ Àü¿¡ REGSVR32 ¸í·ÉÀ» »ç¿ëÇÏ¿© OLE ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ Æ÷ÇÔÇÏ´Â DLLÀ» µî·ÏÇØ¾ß ÇÕ´Ï´Ù. OLE ÀúÀå ÇÁ·Î½Ãµà¾î´Â FENCED ¸ðµå¿¡¼­ ½ÇÇàµÇ¾î¾ß ÇÕ´Ï´Ù. OLE ÀúÀå ÇÁ·Î½Ãµà¾î »ç¿ë¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ÀÀ¿ëÇÁ·Î±×·¥ ºôµå ¾È³»¼­À» ÂüÁ¶ÇϽʽÿÀ.

LANGUAGE COBOL
µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº COBOL È£Ãâ ¹× ¸µÅ© ±Ô¾àÀ» »ç¿ëÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù. COBOL ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ÀÌ ¿É¼ÇÀ» »ç¿ëÇϽʽÿÀ.

¼­ºê·çƾÀ¸·Î ¸Å°³º¯¼ö Àü´Þ

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 */

PARAMETER STYLE

Ç¥ 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

GENERAL
ÀúÀå ÇÁ·Î½Ãµà¾î´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ CALL¹®À¸·ÎºÎÅÍ È£½ºÆ® º¯¼ö·Î¼­ ¸Å°³º¯¼ö¸¦ ¹Þ½À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î´Â ³Î(NULL) Ç¥½Ã±â¸¦ Á÷Á¢ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ Àü´ÞÇÏÁö ¾Ê½À´Ï´Ù. LANGUAGE C ¶Ç´Â LANGUAGE COBOL ¿É¼ÇÀ» ÁöÁ¤ÇÒ ¶§¿¡µµ GENERAL¸¸ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

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 */

GENERAL WITH NULLS
»ç¿ëÀÚ°¡ ¼±¾ðÇÏ´Â °¢ ¸Å°³º¯¼ö¿¡ ´ëÇØ DB2´Â ÇØ´ç INOUT ¸Å°³º¯¼ö ³Î Ç¥½Ã±â¸¦ ÇÒ´çÇÕ´Ï´Ù. GENERAL°ú ¸¶Âù°¡Áö·Î, ¸Å°³º¯¼ö´Â È£½ºÆ® º¯¼ö·Î¼­ Àü´ÞµË´Ï´Ù. LANGUAGE C ¶Ç´Â LANGUAGE COBOL ¿É¼ÇÀ» ÁöÁ¤ÇÒ ¶§¿¡µµ GENERAL WITH NULLS¸¸ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

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 */

JAVA
ÀúÀå ÇÁ·Î½Ãµà¾î´Â SQLJ ·çƾ ½ºÆå°ú ÀÏÄ¡ÇÏ´Â ±Ô¾àÀ» Àü´ÞÇÏ´Â ¸Å°³º¯¼ö¸¦ »ç¿ëÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î´Â È£½ºÆ® º¯¼ö·Î¼­ IN ¸Å°³º¯¼ö¸¦ ¹Þ°í, ´ÜÀÏ Ç׸ñ ¹è¿­·Î¼­ OUT ¹× INOUT ¸Å°³º¯¼ö¸¦ ¹Þ½À´Ï´Ù. LANGUAGE JAVA ¿É¼ÇÀ» ÁöÁ¤ÇÒ ¶§¿¡µµ JAVA¸¸ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

DB2SQL
DB2SQL ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ C ÇÔ¼ö Á¤ÀÇ´Â ´ÙÀ½ ³»ÀçÀû ¸Å°³º¯¼ö¸¦ CREATE PROCEDURE¹®¿¡¼­ ¼±¾ðµÈ ¸Å°³º¯¼ö Á¤ÀÇ¿¡ Ãß°¡ÇØ¾ß ÇÕ´Ï´Ù.

     sqlint16 nullinds[n], (1)
     char sqlst[6],        (2)
     char qualname[28],    (3)
     char specname[19],    (4)
     char diagmsg[71],     (5)

DB2´Â ´ÙÀ½ Àμö¸¦ ÀúÀå ÇÁ·Î½Ãµà¾î·Î Àü´ÞÇÕ´Ï´Ù.

  1. DB2´Â ³»ÀçÀû SMALLINT INOUT ¸Å°³º¯¼ö ¹è¿­À» ¸í½ÃÀû ¸Å°³º¯¼öÀÇ ³Î Ç¥½Ã±â·Î ÇÒ´çÇÕ´Ï´Ù. ¹è¿­ Å©±â´Â nÀ¸·Î, ¿©±â¼­ nÀº ¸í½ÃÀû ¸Å°³º¯¼öÀÇ ¼ö¸¦ ³ªÅ¸³À´Ï´Ù.
  2. SQLSTATE °ªÀÇ ¾Ï½ÃÀû CHAR(5) OUT ¸Å°³º¯¼ö.
  3. ¿ÏÀüÇÑ ÀúÀå ÇÁ·Î½Ãµà¾î À̸§ÀÇ ¾Ï½ÃÀû CHAR(27) IN ¸Å°³º¯¼ö.
  4. ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °íÀ¯ À̸§¿¡ ´ëÇÑ ¾Ï½ÃÀû CHAR(18) IN ¸Å°³º¯¼ö.
  5. SQL Áø´Ü ¹®ÀÚ¿­¿¡ ´ëÇÑ ¾Ï½ÃÀû CHAR(70) OUT ¸Å°³º¯¼ö.

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;
}

DB2GENERAL
ÀúÀå ÇÁ·Î½Ãµà¾î´Â DB2 Java ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­¸¸ Áö¿øµÇ´Â ¸Å°³º¯¼ö Àü´Þ ±Ô¾àÀ» »ç¿ëÇÕ´Ï´Ù. LANGUAGE JAVA ¿É¼ÇÀ» ÁöÁ¤ÇÒ ¶§¿¡µµ DB2GENERAL¸¸ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

À̽ļºÀÇ Áõ°¡¸¦ À§Çؼ­´Â PARAMETER STYLE JAVA ±Ô¾àÀ» »ç¿ëÇÏ¿© Java ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. DB2GENERAL ¸Å°³º¯¼ö ½ºÅ¸ÀÏ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ´Â °Í¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDFÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

DB2DARI
ÀúÀå ÇÁ·Î½Ãµà¾î´Â C ¾ð¾î È£Ãâ ¹× ¸µÅ© ±Ô¾à°ú ÀÏÄ¡ÇÏ´Â ¸Å°³º¯¼ö Àü´Þ ±Ô¾àÀ» »ç¿ëÇÕ´Ï´Ù. ÀÌ ¿É¼ÇÀº DB2 Universal Database¿¡ ÀÇÇØ¼­¸¸ Áö¿øµÇ¸ç, LANGUAGE C ¿É¼ÇÀ» ÁöÁ¤ÇÒ ¶§¿¡µµ ÀÌ ¿É¼Ç¸¸ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

DB2 °è¿­¿¡¼­ÀÇ À̽ļºÀ» Áõ°¡½Ã۱â À§Çؼ­´Â GENERAL ¶Ç´Â GENERAL WITH NULLS ¸Å°³º¯¼ö ½ºÅ¸ÀÏÀ» »ç¿ëÇÏ¿© LANGUAGE C ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. DB2DARI ¸Å°³º¯¼ö ½ºÅ¸ÀÏ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÏ·Á¸é ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDFÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

DBINFO ±¸Á¶ Àü´Þ

PARAMETER TYPEÀÌ GENERAL, GENERAL WITH NULLS ¶Ç´Â DB2SQLÀÎ LANGUAGE C ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ °æ¿ì ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Ãß°¡ ¸Å°³º¯¼ö¸¦ Çã¿ëÇϵµ·Ï ÀÛ¼ºÇÒ ¼ö ÀÖ´Â ¿É¼ÇÀÌ ÀÖ½À´Ï´Ù. CREATE PROCEDURE¹®¿¡¼­ DBINFO¸¦ ÁöÁ¤ÇÏ¿© Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÌ DB2 Ŭ¶óÀÌ¾ðÆ®¿¡ ´ëÇÑ Á¤º¸°¡ Æ÷ÇÔµÈ DBINFO ±¸Á¶¸¦ È£Ã⠸Ű³º¯¼ö¿Í ÇÔ²² ÀúÀå ÇÁ·Î½Ãµà¾î·Î Àü´ÞÇϵµ·Ï Áö½ÃÇÒ ¼ö ÀÖ½À´Ï´Ù. DBINFO ±¸Á¶¿¡´Â ´ÙÀ½°ú °°Àº °ªµéÀÌ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù.

µ¥ÀÌÅͺ£À̽º À̸§
Ŭ¶óÀÌ¾ðÆ®°¡ ¿¬°áµÈ µ¥ÀÌÅͺ£À̽ºÀÇ À̸§.

ÀÀ¿ëÇÁ·Î±×·¥ ±ÇÇÑ ºÎ¿© ID
ÀÀ¿ëÇÁ·Î±×·¥ ·±Å¸ÀÓ ±ÇÇÑ ºÎ¿© ID.

ÄÚµå ÆäÀÌÁö
µ¥ÀÌÅͺ£À̽ºÀÇ ÄÚµå ÆäÀÌÁö.

½ºÅ°¸¶ À̸§
ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Àû¿ëµÇÁö ¾Ê½À´Ï´Ù.

Å×À̺í À̸§
ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Àû¿ëµÇÁö ¾Ê½À´Ï´Ù.

Ä÷³ À̸§
ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Àû¿ëµÇÁö ¾Ê½À´Ï´Ù.

µ¥ÀÌÅͺ£À̽º ¹öÀü ¹× ¸±¸®½º
ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÏ´Â µ¥ÀÌÅͺ£À̽º ¼­¹öÀÇ ¹öÀü, ¸±¸®½º ¹× ¼öÁ¤ ·¹º§.

Ç÷§Æû
µ¥ÀÌÅͺ£À̽º ¼­¹öÀÇ Ç÷§Æû.

Å×À̺í ÇÔ¼ö °á°ú Ä÷³ ¹øÈ£
ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ Àû¿ëµÇÁö ¾Ê½À´Ï´Ù.

DBINFO ±¸Á¶¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº DBINFO ±¸Á¶ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

º¯¼ö ¼±¾ð ¹× CREATE PROCEDURE ¿¹

´ÙÀ½ ¿¹¿¡¼­´Â SAMPLE µ¥ÀÌÅͺ£À̽º¿¡¼­ °¡»ó ½Ã³ª¸®¿À¿¡¼­ »ç¿ëÇÏ´Â CREATE PROCEDURE¹® ¹× ÀúÀå ÇÁ·Î½Ãµà¾î ¼Ò½º Äڵ带 º¸¿©ÁÝ´Ï´Ù.

IN ¹× OUT ¸Å°³º¯¼ö »ç¿ë

empno(SQL type VARCHAR)°¡ ÁÖ¾îÁ³À» ¶§ SAMPLE µ¥ÀÌÅͺ£À̽ºÀÇ EMPLOYEE Å×ÀÌºí¿¡¼­ lastname(SQL type CHAR)À» ¸®ÅÏÇÏ´Â Java ÀúÀå ÇÁ·Î½Ãµà¾î GET_LASTNAMEÀ» ÀÛ¼ºÇÑ´Ù°í ÇϽʽÿÀ. myJar·Î¼­ ¼³Ä¡µÈ JAR¿¡ Æ÷ÇÔµÈ StoredProcedure Java Ŭ·¡½ºÀÇ getname ¸Þ¼Òµå·Î¼­ ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÕ´Ï´Ù. ¸¶Áö¸·À¸·Î C·Î ÄÚµåÈ­µÈ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.

  1. ÀúÀå ÇÁ·Î½Ãµà¾î ¼Ò½º Äڵ忡 µÎ °³ÀÇ È£½ºÆ® º¯¼ö¸¦ ¼±¾ðÇÕ´Ï´Ù.
         String empid;
         String name;
         ...
         #sql { SELECT lastname INTO :empid FROM employee WHERE empno=:empid }
    
  2. ´ÙÀ½ CREATE PROCEDURE¹®À¸·Î ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÕ´Ï´Ù.
      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
    
  3. C·Î ÀÛ¼ºµÈ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·ÎºÎÅÍ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
      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);
    

INOUT ¸Å°³º¯¼ö »ç¿ë

´ÙÀ½ ¿¹¿¡¼­ deptnumb(SQL type SMALLINT)ÀÌ ÁÖ¾îÁ³À» ¶§ SAMPLE µ¥ÀÌÅͺ£À̽ºÀÇ ORG Å×ÀÌºí¿¡¼­ manager(SQL type SMALLINT)¸¦ ¸®ÅÏÇÏ´Â C ÀúÀå ÇÁ·Î½Ãµà¾î GET_MANAGER¸¦ ÀÛ¼ºÇÑ´Ù°í ÇϽʽÿÀ.

  1. deptnumb°ú manager´Â µÑ´Ù SQL µ¥ÀÌÅÍ À¯Çü SMALLINTÀ̹ǷΠŬ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ °©À» ¼ö½ÅÇÏ°í °ªÀ» ¸®ÅÏÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ ´ÜÀÏ º¯¼ö onevar¸¦ ¼±¾ðÇÒ ¼ö ÀÖ½À´Ï´Ù.
      EXEC SQL BEGIN DECLARE SECTION;
        short onevar = 0;
      EXEC SQL END DECLARE SECTION;
    
  2. ´ÙÀ½ CREATE PROCEDURE¹®À¸·Î ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÕ´Ï´Ù.
       CREATE PROCEDURE GET_MANAGER (INOUT onevar SMALLINT)
          EXTERNAL NAME 'stplib!getman'
          LANGUAGE C PARAMETER STYLE GENERAL FENCED
          READS SQL DATA
    
  3. Java·Î ÀÛ¼ºµÈ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·ÎºÎÅÍ ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
      short onevar = 0;
        ...
      #SQL { CALL GET_MANAGER (:INOUT onevar) };
    

ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ SQL¹®

ÀúÀå ÇÁ·Î½Ãµà¾î´Â SQL¹®À» Æ÷Ç﵃ ¼ö ÀÖ½À´Ï´Ù. CREATE PROCEDURE¹®À» ¹ßÇàÇÒ ¶§ ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Æ÷ÇÔÇϰí ÀÖ´Â SQL¹® À¯ÇüÀ» ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ µî·ÏÇÒ ¶§ °ªÀ» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥´Â MODIFIES SQL DATA¸¦ »ç¿ëÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ »ç¿ëµÇ´Â SQL À¯ÇüÀ» Á¦ÇÑÇϱâ À§ÇØ ´ÙÀ½ ³× °³ÀÇ ¿É¼Ç Áß Çϳª¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

NO SQL
ÀúÀå ÇÁ·Î½Ãµà¾î°¡ SQL¹®À» ½ÇÇàÇÒ ¼ö ¾øÀ½À» ³ªÅ¸³À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ SQL¹®À» ½ÇÇàÇÏ·Á°í ÇÏ¸é ¸í·É¹®Àº SQLSTATE 38001À» ¸®ÅÏÇÕ´Ï´Ù.

CONTAINS SQL
SQL µ¥ÀÌÅ͸¦ Àаųª ¼öÁ¤ÇÒ ¼ö ¾ø´Â SQL¹®ÀÌ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ ½ÇÇàµÉ ¼ö ÀÖÀ½À» ³ªÅ¸³À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î°¡ SQL µ¥ÀÌÅ͸¦ Àаųª ¼öÁ¤ÇÒ ¼ö ÀÖ´Â SQL¹®À» ½ÇÇàÇÏ·Á°í ÇÏ¸é ¸í·É¹®Àº SQLSTATE 38004¸¦ ¸®ÅÏÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ Áö¿øµÇÁö ¾Ê´Â ¸í·É¹®Àº SQLSTATE 38003À» ¸®ÅÏÇÕ´Ï´Ù.

READS SQL DATA
SQlÀ» ¼öÁ¤ÇÒ ¼ö ¾ø´Â ÀϺΠSQL¹®Àº ÀúÀå ÇÁ·Î½Ãµà¾î·Î ½ÇÇàµÉ ¼ö ÀÖÀ½À» ³ªÅ¸³À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î°¡ µ¥ÀÌÅ͸¦ ¼öÁ¤ÇÏ´Â SQL¹®À» ½ÇÇàÇÏ·Á°í ÇÏ¸é ¸í·É¹®Àº SQLSTATE 38002¸¦ ¸®ÅÏÇÕ´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ Áö¿øµÇÁö ¾Ê´Â ¸í·É¹®Àº SQLSTATE 38003À» ¸®ÅÏÇÕ´Ï´Ù.

MODIFIES SQL DATA
ÀúÀå ÇÁ·Î½Ãµà¾î´Â ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ Áö¿øµÇÁö ¾Ê´Â ¸í·É¹®À» Á¦¿ÜÇÑ ¸ðµç SQL¹®À» ½ÇÇàÇÒ ¼ö ÀÖÀ½À» ³ªÅ¸³À´Ï´Ù. ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ Áö¿øµÇÁö ¾Ê´Â SQL¹®À» ½ÇÇàÇÏ·Á°í ÇÏ¸é ¸í·É¹®Àº SQLSTATE 38003À» ¸®ÅÏÇÕ´Ï´Ù.

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 ÀÚµ¿È­ ÀúÀå ÇÁ·Î½Ãµà¾î ÀÛ¼º

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 ¸Å°³º¯¼ö ÀúÀå ÇÁ·Î½Ãµà¾î

´ÙÀ½Àº OUT È£½ºÆ® º¯¼ö »ç¿ëÀÇ »ùÇà ÇÁ·Î±×·¥ÀÔ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº SAMPLE µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â »ç¿øÀÇ Áß°£ ±Þ¿©¸¦ °áÁ¤ÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù. (Áß°£ÀÇ Á¤ÀÇ´Â ÇØ´ç °ªÀÇ ¹ÝÀÌ ±× ¾Æ·¡À§¿¡ ÀÖ´Ù´Â °ÍÀ» ÀǹÌÇÕ´Ï´Ù.) ±×·¯¸é Áß°£ ±Þ¿©°¡ OUT È£½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ¿© Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ´Ù½Ã Àü´ÞµË´Ï´Ù.

ÀÌ »ùÇà ÇÁ·Î±×·¥Àº SAMPLE µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â ¸ðµç »ç¿øÀÇ Áß°£ ±Þ¿©¸¦ °è»êÇÕ´Ï´Ù. Áß°£ ±Þ¿©¸¦ °è»êÇϱâ À§ÇÑ SQL Ä÷³ ÇÔ¼ö°¡ ¾øÀ¸¹Ç·Î, Áß°£ ±Þ¿©´Â ´ÙÀ½ ¾Ë°í¸®Áò¿¡ ÀÇÇØ ´ëÈ­½ÄÀ¸·Î ó¸®µÉ ¼ö ÀÖ½À´Ï´Ù.

  1. Å×ÀÌºí¿¡ ÀÖ´Â ·¹ÄÚµå ¼ö(n)¸¦ °áÁ¤ÇÕ´Ï´Ù.
  2. ±Þ¿©¿¡ ±âÃÊÇÑ ·¹Äڵ带 ¼ø¼­È­ÇÕ´Ï´Ù.
  3. n / 2 + 1 Çà À§Ä¡ÀÇ ·¹Äڵ带 ãÀ» ¶§±îÁö ·¹Äڵ带 ÆäÄ¡ÇÕ´Ï´Ù.
  4. ÀÌ ·¹ÄÚµå·ÎºÎÅÍ Áß°£ ±Þ¿©¸¦ ÀнÀ´Ï´Ù.

ÀúÀå ÇÁ·Î½Ãµà¾î ±â¼ú°ú ºí·ÎÅ· Ä¿¼­¸¦ »ç¿ëÇÏÁö ¾Ê´Â ÀÀ¿ëÇÁ·Î±×·¥Àº ±×¸² 5¿¡¼­¿Í °°ÀÌ ³×Æ®¿öÅ©¸¦ ÅëÇØ °¢ ±Þ¿©¸¦ FETCHÇØ¾ß ÇÕ´Ï´Ù.

±×¸² 5. ÀúÀå ÇÁ·Î½Ãµà¾î ¾ø´Â Áß°£ »ùÇÃ


ÀúÀå ÇÁ·Î½Ãµà¾î ¾ø´Â Áß°£ »ùÇÃ

n / 2 + 1 ÇàÀÇ ±Þ¿©¸¸ ÇÊ¿äÇϹǷÎ, ÀÀ¿ëÇÁ·Î±×·¥Àº ¸ðµç Ãß°¡ µ¥ÀÌÅ͸¦ ¹ö¸®Áö¸¸ ³×Æ®¿öÅ©¸¦ ÅëÇØ Àü¼ÛµÈÈÄ¿¡¸¸ °¡´ÉÇÕ´Ï´Ù.

Áß°£ ±Þ¿©¸¸ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏÇÏ¿©, ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ºÒÇÊ¿äÇÑ µ¥ÀÌÅ͸¦ ó¸® ¹× Á¦°ÅÇÏ´Â ÀúÀå ÇÁ·Î½Ãµà¾î ±â¼úÀ» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥À» ¼³°èÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×¸² 6¿¡ ÀÌ·¯ÇÑ ±â´ÉÀÌ ³ª¿Í ÀÖ½À´Ï´Ù.

±×¸² 6. ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ »ç¿ëÇÏ´Â OUT ¸Å°³º¯¼ö »ùÇÃ


ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ »ç¿ëÇÏ´Â OUT ¸Å°³º¯¼ö »ùÇÃ

OUT Ŭ¶óÀÌ¾ðÆ® ¼³¸í¿¡´Â »ùÇà OUT È£½ºÆ® º¯¼ö Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ ¹× ÀúÀå ÇÁ·Î½Ãµà¾î°¡ Ç¥½ÃµË´Ï´Ù. »ùÇà ÇÁ·Î±×·¥Àº ´ÙÀ½°ú °°ÀÌ Java·Î »ç¿ë °¡´ÉÇÕ´Ï´Ù.

Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥
Outcli.java

ÀúÀå ÇÁ·Î½Ãµà¾î
Outsrv.sqlj

»ùÇà ÇÁ·Î±×·¥Àº ´ÙÀ½°ú °°ÀÌ C·Î »ç¿ë °¡´ÉÇÕ´Ï´Ù.

Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥
spclient.sqc

ÀúÀå ÇÁ·Î½Ãµà¾î
spserver.sqc

OUT Ŭ¶óÀÌ¾ðÆ® ¼³¸í

  1. Æ÷ÇÔ ÆÄÀÏ. C Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½ ÆÄÀϵéÀ» Æ÷ÇÔÇϰí ÀÖ½À´Ï´Ù.

    SQL
    SQL_TYP_FLOAT ±âÈ£¸¦ Á¤ÀÇÇÕ´Ï´Ù.

    SQLDA
    ¼³¸íÀÚ ¿µ¿ªÀ» Á¤ÀÇÇÕ´Ï´Ù.

    SQLCA
    ¿À·ù 󸮸¦ À§ÇØ Åë½Å ¿µ¿ªÀ» Á¤ÀÇÇÕ´Ï´Ù.
    JDBC Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½ ÆÐŰÁö¸¦ °¡Á®¿É´Ï´Ù.

    java.sql.*
    Ŭ¶óÀ̾ðÆ®ÀÇ JDBC ±¸Çö¿¡¼­ ³ª¿Â JDBC Ŭ·¡½º

    java.math.BigDecimal
    DB2 DECIMAL µ¥ÀÌÅÍ À¯Çü¿¡ ´ëÇÑ Java Áö¿øÀ» Á¦°øÇÕ´Ï´Ù.
  2. µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°á. ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϱâ Àü¿¡ µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áµÇ¾î¾ß ÇÕ´Ï´Ù.
  3. ÀÚµ¿ È®¾à ²ô±â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϱâ Àü¿¡ ÀÚµ¿ È®¾àÀ» ¸í½ÃÀûÀ¸·Î »ç¿ëÇÒ ¼ö ¾ø°Ô ¸¸µì´Ï´Ù. ÀÚµ¿ È®¾àÀ» »ç¿ëÇÒ ¼ö ¾ø°Ô ¸¸µé¸é Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ ¼öÇàµÈ ÀÛ¾÷ÀÌ ±¸°£ º¹¿øµÇ°Å³ª È®¾àµÇ´ÂÁö Á¦¾îÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ ¿¹Á¦ÀÇ ÀúÀå ÇÁ·Î½Ãµà¾î´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Á¶°Ç¹®À» »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ ¼öÇàµÈ ÀÛ¾÷À» ½±°Ô È®¾àÇϰųª ±¸°£ º¹¿øÇÒ ¼ö ÀÖµµ·Ï SQLCODE°¡ µé¾î ÀÖ´Â OUT ¸Å°³º¯¼ö¸¦ ¸®ÅÏÇÕ´Ï´Ù.
  4. È£½ºÆ® º¯¼ö ¼±¾ð ¹× ÃʱâÈ­ ÀÌ ´Ü°è´Â È£½ºÆ® º¯¼ö¸¦ ¼±¾ð ¹× ÃʱâÈ­ÇÕ´Ï´Ù. Java ÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϱâ Àü¿¡ °¢ INOUT ¶Ç´Â OUT ¸Å°³º¯¼öÀÇ µ¥ÀÌÅÍ À¯ÇüÀ» µî·ÏÇÏ°í ¸ðµç ¸Å°³º¯¼öÀÇ °ªÀ» ÃʱâÈ­ÇØ¾ß ÇÕ´Ï´Ù.
  5. ÀúÀå ÇÁ·Î½Ãµà¾î È£Ãâ. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº 3°³ÀÇ ¸Å°³º¯¼ö°¡ ÀÖ´Â CALL¹®À» »ç¿ëÇÏ¿© SAMPLE µ¥ÀÌÅͺ£À̽ºÀÇ OUTPARAM ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
  6. Ãâ·Â ¸Å°³º¯¼ö °Ë»ö. JDBC Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ ¸®ÅÏµÈ Ãâ·Â ¸Å°³º¯¼öÀÇ °ªÀ» ¸í½ÃÀûÀ¸·Î °Ë»öÇØ¾ß ÇÕ´Ï´Ù. C/C++ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ ´ëÇØ DB2´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ CALL¹®À» ½ÇÇàÇÒ ¶§ CALL¹®¿¡¼­ »ç¿ëµÈ È£½ºÆ® º¯¼öÀÇ °ªÀ» °»½ÅÇÕ´Ï´Ù.
  7. ¸®ÅÏµÈ SQLCODEÀÇ °ªÀ» È®ÀÎÇϽʽÿÀ. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº SQLCODE°¡ µé¾î ÀÖ´Â OUT ¸Å°³º¯¼öÀÇ °ªÀ» È®ÀÎÇÏ¿© Æ®·£Àè¼ÇÀ» ±¸°£ º¹¿øÇÒ °ÍÀÎÁö ¾Æ´Ï¸é È®¾àÇÒ °ÍÀÎÁö¸¦ °áÁ¤ÇÕ´Ï´Ù.
  8. µ¥ÀÌÅͺ£À̽º¿¡¼­ ¿¬°áÇØÁ¦. DB2¿¡¼­ °¢ ¿¬°á¿¡ ÇÒ´çµÈ ½Ã½ºÅÛ ÀÚ¿øÀ» ÇØÁ¦ÇÒ ¼ö ÀÖ°Ô ÇÏ·Á¸é Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À» Á¾·áÇϱâ Àü¿¡ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ ¿¬°áÀ» ¸í½ÃÀûÀ¸·Î ´Ý¾Æ¾ß ÇÕ´Ï´Ù.

CHECKERR ¸ÅÅ©·Î/ÇÔ¼ö´Â ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÔ´Ï´Ù. ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÇ À§Ä¡´Â »ç¿ëµÈ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡ µû¶ó ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù.

C
DB2 API¸¦ È£ÃâÇÏ´Â C ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilapi.c¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilapi.h¿¡¼­ API_SQL_CHECK·Î¼­ ÂüÁ¶µË´Ï´Ù. C embedded SQL ÇÁ·Î±×·¥¿¡ ´ëÇØ¼­´Â utilemb.sqc¿¡¼­ sqlInfoPrint ÇÔ¼ö´Â utilemb.h¿¡¼­ EMB_SQL_CHECK·Î¼­ ÂüÁ¶µË´Ï´Ù.

Java
SQL ¿À·ù´Â SQLExceptionÀ¸·Î¼­ µå·Î¿ìµÇ°í, ÀÀ¿ëÇÁ·Î±×·¥ÀÇ catch ºí·Ï¿¡¼­ 󸮵˴ϴÙ.

COBOL
CHECKERRÀº checkerr.cbl¶ó°í ÇÏ´Â ¿ÜºÎ ÇÁ·Î±×·¥ÀÔ´Ï´Ù.

ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼¿¡ ´ëÇÑ ¼Ò½º Äڵ忡 ´ëÇØ¼­´Â ¿¹Á¦ ÇÁ·Î±×·¥¿¡¼­ GET ERROR MESSAGE »ç¿ë¿¡¼­ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

¿¹Á¦ OUT Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥: Java

  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);
      }
    }
  }

¿¹Á¦ OUT Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥: C

  #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;
  }

OUT ÀúÀå ÇÁ·Î½Ãµà¾î ¼³¸í

  1. ½ÅÈ£ ¼±¾ð. ÇÁ·Î½Ãµà¾î´Â 3°¡Áö ¸Å°³º¯¼ö¸¦ ¸®ÅÏÇÕ´Ï´Ù. DOUBLEÀº Áß°£°ªÀ» À§ÇÑ °ÍÀ̰í INTEGER´Â SQLCODE¸¦ À§ÇÑ °ÍÀ̸ç CHAR´Â ¿À·ù ¸Þ½ÃÁö¸¦ À§ÇÑ °ÍÀÔ´Ï´Ù. °¢ ¾ð¾îÀÇ ÇÁ·Î±×·¡¹Ö Àå¿¡ ÁöÁ¤µÈ DB2 À¯Çü ¸ÅÇÎÀ» »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î ÇÔ¼ö Á¤ÀÇ¿¡¼­ µ¿µîÇÑ µ¥ÀÌÅÍ À¯ÇüÀ» Àμö·Î ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù.
  2. ±Þ¿©º°·Î Á¤·ÄµÈ CURSOR ¼±¾ðÇϽʽÿÀ. º¹¼ö µ¥ÀÌÅÍ Çà¿¡ ´ëÇØ ÀÛ¾÷ÇÏ·Á¸é C ÀúÀå ÇÁ·Î½Ãµà¾î´Â DECLARE CURSOR¹®À» ½ÇÇàÇϰí JDBC ÀúÀå ÇÁ·Î½Ãµà¾î´Â ResultSet ¿ÀºêÁ§Æ®¸¦ ÀÛ¼ºÇÕ´Ï´Ù. ORDER BY SALARYÀýÀº ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ÅëÇØ ±Þ¿©¸¦ ¿À¸§Â÷¼øÀ¸·Î °Ë»öÇÕ´Ï´Ù.
  3. Àüü »ç¿ø ¼ö °áÁ¤. ÀúÀå ÇÁ·Î½Ãµà¾î´Â COUNT ÇÔ¼ö¿Í ÇÔ²² °£´ÜÇÑ SELECT¹®À» »ç¿ëÇÏ¿© EMPLOYEE Å×ÀÌºí¿¡ ÀÖ´Â »ç¿ø ¼ö¸¦ °Ë»öÇÕ´Ï´Ù.
  4. Áß°£ ±Þ¿© FETCH. ÀúÀå ÇÁ·Î½Ãµà¾î´Â Áß°£ ±Þ¿©¾×À» º¯¼ö¿¡ ÁöÁ¤ÇÒ ¶§±îÁö ¿¬¼ÓÀûÀ¸·Î FETCH¹®À» ¹ßÇàÇÕ´Ï´Ù.
  5. Áß°£ ±Þ¿©¸¦ Ãâ·Â º¯¼ö¿¡ ÇÒ´ç. Áß°£ ±Þ¿©¸¦ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡ ¸®ÅÏÇÏ·Á¸é °ªÀ» ÀúÀå ÇÁ·Î½Ãµà¾î ÇÔ¼ö ¶Ç´Â OUT ¸Å°³º¯¼ö¿¡ ÇØ´çÇÏ´Â ¸Þ¼Òµå ¼±¾ð¿¡ ÇÒ´çÇÕ´Ï´Ù.
  6. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏ. PARAMETER STYLE DB2DARI ÀúÀå ÇÁ·Î½Ãµà¾î¸¸ Ŭ¶óÀÌ¾ðÆ®¿¡ °ªÀ» ¸®ÅÏÇÕ´Ï´Ù. DB2DARI ÀúÀå ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ¼­´Â ºÎ·Ï C, DB2DARI ¹× DB2GENERAL ÀúÀå ÇÁ·Î½Ãµà¾î¿Í UDF¿¡¼­ ÀÚ¼¼ÇÑ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

¿¹Á¦ OUT ¸Å°³º¯¼ö ÀúÀå ÇÁ·Î½Ãµà¾î: Java

  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();
        }
     }
   }

¿¹Á¦ OUT ¸Å°³º¯¼ö ÀúÀå ÇÁ·Î½Ãµà¾î: C

  #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¿Í °°Àº) ´Ù¸¥ ÄÚµå ÆäÀÌÁö¸¦ »ç¿ëÇÏ´Â µ¥ÀÌÅͺ£À̽º¿¡ ¾×¼¼½ºÇÏ´Â ¿ø°Ý ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¶§, ´ÙÀ½ À̺¥Æ®°¡ ¹ß»ýÇÕ´Ï´Ù.

  1. ÀÔ·Â ¹®ÀÚ¿­ ¸Å°³º¯¼ö(Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ SQLDA·Î Á¤ÀǵǾú´ÂÁö ¶Ç´Â È£½ºÆ® º¯¼ö·Î¼­ Á¤ÀǵǾú´ÂÁö ³ªÅ¸³»´Â ¸Å°³º¯¼ö)´Â ÀÀ¿ëÇÁ·Î±×·¥ ÄÚµå ÆäÀÌÁö(A)¿¡¼­ µ¥ÀÌÅͺ£À̽º¿Í ¿¬°üµÈ ÄÚµå ÆäÀÌÁö(Z)·Î º¯È¯µË´Ï´Ù. FOR BIT DATA·Î¼­ SQLDA¿¡ Á¤ÀÇµÈ µ¥ÀÌÅÍ¿¡ ´ëÇØ º¯È¯ÀÌ ¹ß»ýÇÏÁö ¾Ê½À´Ï´Ù.
  2. ÀÔ·Â ¸Å°³º¯¼ö°¡ º¯È¯µÇ¸é µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ´õ ¸¹Àº ÄÚµå ÆäÀÌÁö º¯È¯À» ¼öÇàÇÏÁö ¾Ê½À´Ï´Ù.

    µû¶ó¼­, µ¥ÀÌÅͺ£À̽º¿Í °°Àº ÄÚµå ÆäÀÌÁö, ÀÌ ¿¹¿¡¼­´Â ÄÚµå ÆäÀÌÁö Z¸¦ »ç¿ëÇÏ¿© ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ¼öÇàÇØ¾ß ÇÕ´Ï´Ù. µ¥ÀÌÅͺ£À̽º¿Í °°Àº ÄÚµå ÆäÀÌÁö¸¦ »ç¿ëÇÏ¿© ¼­¹ö ÇÁ·Î½Ãµà¾î¸¦ Áغñ, ÄÄÆÄÀÏ ¹× ¹ÙÀεåÇØ º¸´Â °ÍÀÌ ÁÁ½À´Ï´Ù.

  3. ÀúÀå ÇÁ·Î½Ãµà¾î°¡ ¿Ï·áµÉ ¶§ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº Ãâ·Â ¹®ÀÚ¿­ ¸Å°³º¯¼ö(È£½ºÆ® º¯¼ö·Î¼­ Á¤ÀǵǾú´ÂÁö ¶Ç´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÇ SQLDA¿¡¼­ Á¤ÀǵǾú´ÂÁö ³ªÅ¸³»´Â ¸Å°³º¯¼ö) ¹× SQLCA ¹®ÀÚ Çʵ带 µ¥ÀÌÅͺ£À̽º ÄÚµå ÆäÀÌÁö(Z)¸¦ ´Ù½Ã ÀÀ¿ëÇÁ·Î±×·¥ ÄÚµå ÆäÀÌÁö(A)·Î º¯È¯ÇÕ´Ï´Ù. FOR BIT DATA·Î¼­ SQLDA¿¡ Á¤ÀÇµÈ µ¥ÀÌÅÍ¿¡ ´ëÇØ º¯È¯ÀÌ ¹ß»ýÇÏÁö ¾Ê½À´Ï´Ù.

ÁÖ:ÀúÀå ÇÁ·Î½Ãµà¾îÀÇ ¸Å°³º¯¼ö°¡ ¼­¹ö¿¡¼­ FOR BIT DATA·Î¼­ Á¤ÀǵǾî ÀÖ´Â °æ¿ì, SQLDA¿¡¼­ ¸í½ÃÀûÀ¸·Î ÁöÁ¤µÇ¾ú´ÂÁö¿¡ °ü°è¾øÀÌ CALL¹®¿¡ ´ëÇØ OS/390¿ë DB2 Universal Database ¶Ç´Â AS/400¿ë DB2 Universal DatabaseÀ¸·ÎÀÇ º¯È¯ÀÌ ÀÌ·ç¾îÁöÁö ¾Ê½À´Ï´Ù. (¼¼ºÎ»çÇ×Àº SQL ÂüÁ¶¼­¿¡ ÀÖ´Â SQLDAÀÇÀýÀ» ÂüÁ¶ÇϽʽÿÀ.)

ÀÌ ÁÖÁ¦¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº ´Ù¸¥ ÄÚµå ÆäÀÌÁö °£ÀÇ º¯È¯ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

C++ °í·Á»çÇ×

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À» ¹ßÇàÇÒ ¼ö ¾ø½À´Ï´Ù.


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]