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

°í±Þ È­¸éÀ̵¿ ±â¼ú

°í±Þ È­¸éÀ̵¿ ±â¼ú¿¡ ´ëÇØ ´ÙÀ½°ú °°Àº ÁÖÁ¦°¡ ÀÌ Àý¿¡¼­ ³íÀǵ˴ϴÙ.

ÀÌ¹Ì °Ë»öµÈ µ¥ÀÌÅÍÀÇ È­¸éÀ̵¿

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ µ¥ÀÌÅͺ£À̽º·ÎºÎÅÍ µ¥ÀÌÅ͸¦ °Ë»öÇÒ ¶§ FETCH¹®Àº µ¥ÀÌÅÍ ¾ÕÀ¸·Î È­¸é À̵¿Çϵµ·Ï ÇÏÁö¸¸, µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº µ¥ÀÌÅÍ µÚ·Î È­¸é À̵¿Çϵµ·Ï ÇÏ´Â Embedded SQL¹®ÀÌ ¾ø½À´Ï´Ù(backward FETCH¿Í µ¿ÀÏ). ±×·¯³ª, DB2 CLI ¹× Java´Â Àбâ Àü¿ë È­¸é À̵¿ Ä¿¼­¸¦ ÅëÇØ backward FETCH¸¦ Áö¿øÇÕ´Ï´Ù. È­¸éÀ̵¿ Ä¿¼­¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº CLI Guide and Reference ¹× Java ÀÀ¿ëÇÁ·Î±×·¥ ¹× ¾ÖÇø´ ÀÛ¼ºÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. Embedded SQL ÀÀ¿ëÇÁ·Î±×·¥ÀÇ °æ¿ì, ´ÙÀ½ ±â¼úÀ» »ç¿ëÇÏ¿© °Ë»öµÈ µ¥ÀÌÅ͸¦ È­¸éÀ̵¿ÇÒ ¼ö ÀÖ½À´Ï´Ù.

  1. ÆäÄ¡µÈ µ¥ÀÌÅÍ »çº»À» º¸À¯ÇÑ Ã¤ ÀϺΠÇÁ·Î±×·¡¹Ö ±â¼úÀ» ÀÌ¿ëÇÏ¿© È­¸é À̵¿À» ÇÕ´Ï´Ù.
  2. ÀϹÝÀûÀ¸·Î µÎ ¹øÂ° SELECT¹®À¸·Î SQLÀ» »ç¿ëÇÏ¿© ´Ù½Ã µ¥ÀÌÅ͸¦ °Ë»öÇÕ´Ï´Ù.

ÀÌ ¿É¼Çµé¿¡ ´ëÇØ¼­´Â ´ÙÀ½¿¡¼­ º¸´Ù »ó¼¼È÷ ´Ù·ê °ÍÀÔ´Ï´Ù:

µ¥ÀÌÅÍ »çº» º¸°ü

ÀÀ¿ëÇÁ·Î±×·¥Àº ÆäÄ¡ÇÑ µ¥ÀÌÅ͸¦ °¡»ó ÀúÀ念¿ª¿¡ ÀúÀåÇÒ ¼ö ÀÖ½À´Ï´Ù. µ¥ÀÌÅͰ¡ °¡»ó ÀúÀ念¿ª¿¡ ¸ÂÁö ¾ÊÀ¸¸é ÀÀ¿ëÇÁ·Î±×·¥Àº µ¥ÀÌÅ͸¦ Àӽà ÆÄÀÏ¿¡ ±â·ÏÇÒ ¼ö ¾ø½À´Ï´Ù. Æ®·£Àè¼Ç¿¡ ÀÇÇØ Áß°£¿¡ µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â µ¥ÀÌÅͰ¡ º¯°æµÇ¾ú¾îµµ, ÀÌ Á¢±Ù ¹æ½ÄÀÇ °á°ú·Î »ç¿ëÀÚ´« µÚ·Î È­¸éÀ̵¿Çϸ鼭 Ç×»ó ÆäÄ¡µÈ °Í°ú °°Àº µ¥ÀÌÅ͸¦ º¼ ¼ö ÀÖ½À´Ï´Ù.

¹Ýº¹ Àбâ(RR)ÀÇ ºÐ¸® ·¹º§À» »ç¿ëÇÏ¿© Æ®·£Àè¼ÇÀ¸·ÎºÎÅÍ °Ë»öµÇ´Â µ¥ÀÌÅÍ´Â Ä¿¼­¸¦ ´Ý°í ¿­¾î ´Ù½Ã °Ë»öÇÒ ¼ö ÀÖ½À´Ï´Ù. ±âŸ ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­´Â »ç¿ëÀÚÀÇ °á°ú ¼¼Æ®¿¡ ÀÖ´Â µ¥ÀÌÅ͸¦ °»½ÅÇÏÁö ¸øÇÕ´Ï´Ù. ºÐ¸® ·¹º§ ¹× Àá±ÝÀº »ç¿ëÀÚÀÇ µ¥ÀÌÅÍ °»½Å ¹æ¹ý¿¡ ¿µÇâÀ» ÁÙ ¼ö ÀÖ½À´Ï´Ù.

µ¥ÀÌÅÍ µÎ ¹øÂ° °Ë»ö

ÀÌ ±â¼úÀº ´Ù½Ã µ¥ÀÌÅ͸¦ º¸°íÀÚ ÇÏ´Â ¼ø¼­¿¡ µû¶ó ´Þ¶óÁý´Ï´Ù.

½ÃÀÛºÎÅÍ °Ë»ö

½ÃÀÛºÎÅÍ ´Ù½Ã µ¥ÀÌÅ͸¦ °Ë»öÇÏ·Á¸é Ȱµ¿ÁßÀÎ Ä¿¼­¸¦ ´Ý°í ´Ù½Ã ¿­±â¸¸ ÇÏ¸é µË´Ï´Ù. ÀÌ·¯ÇÑ Á¶Ä¡´Â Ä¿¼­¸¦ °á°ú Å×À̺íÀÇ ½ÃÀÛ ºÎºÐ¿¡ µÓ´Ï´Ù. ±×·¯³ª, ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Å×ÀÌºí¿¡ ´ëÇÑ Àá±ÝÀ» º¸À¯ÇÏÁö ¾Ê´Â ÇÑ, ´Ù¸¥ Å×À̺íµéÀº º¯°æµÇ¾úÀ» ¼ö ÀÖÀ¸¹Ç·Î °á°ú Å×À̺íÀÇ Ã¹¹øÂ° ÇàÀ̾ú´ø °ÍÀº ´õÀÌ»ó ù¹øÂ° ÇàÀÌ ¾Æ´Õ´Ï´Ù.

Áß°£ºÎÅÍ °Ë»ö

°á°ú Å×À̺í Áß°£ ºÎºÐ¿¡¼­ µ¥ÀÌÅ͸¦ µÎ ¹øÂ° °Ë»öÇÏ·Á¸é µÎ ¹øÂ° SELECT¹®À» ½ÇÇàÇÏ°í ¸í·É¹®¿¡ ´ëÇØ µÎ ¹øÂ° Ä¿¼­¸¦ ¼±¾ðÇϽʽÿÀ. ¿¹¸¦ µé¾î, ù¹øÂ° SELECT¹®ÀÌ ´ÙÀ½°ú °°¾Ò´Ù°í °¡Á¤ÇϽʽÿÀ.

     SELECT * FROM DEPARTMENT
       WHERE LOCATION = 'CALIFORNIA'
       ORDER BY DEPTNO

ÀÌÁ¦DEPTNO = 'M95'·Î ½ÃÀÛÇÏ´Â ÇàÀ¸·Î ¸®ÅÏÇÏ¿© ÀÌ ÁöÁ¡¿¡¼­ ¼øÂ÷ÀûÀ¸·Î ÆäÄ¡ÇÑ´Ù°í °¡Á¤ÇÕ´Ï´Ù. ´ÙÀ½À» ÄÚµåÈ­ÇϽʽÿÀ.

     SELECT * FROM DEPARTMENT
       WHERE LOCATION = 'CALIFORNIA'
       AND DEPTNO >= 'M95'
       ORDER BY DEPTNO

ÀÌ ¸í·É¹®Àº ¿øÇÏ´Â Àå¼Ò¿¡ Ä¿¼­¸¦ µÓ´Ï´Ù.

µÎ ¹øÂ° °á°ú Å×À̺íÀÇ Çà ¼ø¼­

µÎ ¹øÂ° °á°ú Å×À̺íÀÇ ÇàµéÀÌ Ã¹¹øÂ° °á°ú Å×ÀÌºí¿¡¼­¿Í °°Àº ¼ö¼­·Î Ç¥½ÃµÇÁö ¾ÊÀ» ¼öµµ ÀÖ½À´Ï´Ù. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº SELECT¹®ÀÌ ORDER BY¸¦ »ç¿ëÇÏÁö ¾Ê´Â ÇÑ, ÇàÀÇ ¼ø¼­¸¦ Áß¿äÇÏ°Ô ¿©±âÁö ¾Ê½À´Ï´Ù. µû¶ó¼­, µ¿ÀÏÇÑ DEPTNO °ªÀ» °¡Áø ÇàÀÌ ¿©·¯ °³ ÀÖÀ» °æ¿ì µÎ ¹øÂ° SELECT¹®Àº ù¹øÂ°¿Í´Â ´Ù¸¥ ¼ø¼­·Î °Ë»öÇÒ ¼ö ÀÖ½À´Ï´Ù. È®½ÇÇÑ ÇѰ¡Áö´Â ORDER BY DEPTNOÀý¿¡ ÀÇÇØ ¿ä±¸µÉ °æ¿ì ¸ðµÎ ºÎ¼­ ¹øÈ£¼øÀ¸·Î °Ë»öµÇ´Â °ÍÀÔ´Ï´Ù.

µ¿ÀÏÇÑ È£½ºÆ® º¯¼ö¸¦ °¡Áö°í µ¿ÀÏÇÑ SQL¹®À» µÎ ¹ø ½ÇÇàÇÒ °æ¿ì¿¡µµ ¼ø¼­°¡ ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, ½ÇÇà »çÀÌ¿¡ īŻ·Î±×ÀÇ Åë°è°¡ °»½ÅµÇ°Å³ª, »öÀÎÀÌ ÀÛ¼º ¶Ç´Â Á¦°ÅµÉ ¼ö ÀÖ½À´Ï´Ù. ±× ´ÙÀ½¿¡´Â ´Ù½Ã SELECT¹®À» ½ÇÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

ù¹øÂ° SELECT¿¡´Â ¾ø¾ú´ø ¼ú¾î°¡ µÎ ¹øÂ° SELECT¿¡ ÀÖÀ» °æ¿ì ¼ø¼­°¡ º¯°æµÉ °¡´É¼ºÀÌ ´õ ³ô¾ÆÁý´Ï´Ù. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº »õ ¼ú¾î¿¡¼­ »öÀÎÀ» »ç¿ëÇϵµ·Ï ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, À§ ¿¹¿¡¼­ ù¹øÂ° ¸í·É¹®ÀÇ °æ¿ì LOCATION¿¡ ´ëÇØ, µÎ ¹øÂ° ¸í·É¹®ÀÇ °æ¿ì DEPTNO¿¡ ´ëÇØ »öÀÎÀ» ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù. ÇàÀº »öÀΠŰ ¼ø¼­·Î ÆäÄ¡µÇ¹Ç·Î µÎ ¹øÂ° ¼ø¼­´Â ù¹øÂ° ¼ø¼­¿Í °°Áö ¾Ê¾Æµµ µË´Ï´Ù.

¶Ç´Ù½Ã, µÎ °³ÀÇ À¯»çÇÑ SELECT¹®À» ½ÇÇàÇÒ ¶§ Åë°è°¡ º¯°æµÇÁö ¾Ê°í »öÀÎÀÌ ÀÛ¼ºµÇ°Å³ª Á¦°ÅµÇÁö ¾Ê¾Æµµ Çà ¼ø¼­°¡ ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù. ÀÌ ¿¹¿¡¼­ LOCATION¿¡ ´ëÇØ ´Ù¸¥ °ªµéÀÌ ¸¹ÀÌ ÀÖÀ» °æ¿ì µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº µÎ ¸í·É¹® ¸ðµÎ¿¡ ´ëÇØ LOCATION¿¡¼­ »öÀÎÀ» ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×·¯³ª µÎ ¹øÂ° ¸í·É¹®ÀÇ DEPTNO °ªÀ» ´ÙÀ½°ú °°ÀÌ º¯°æÇÒ °æ¿ì µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº DEPTNO¿¡ ´ëÇÑ »öÀÎÀ» ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù.

     SELECT * FROM DEPARTMENT
       WHERE LOCATION = 'CALIFORNIA'
       AND DEPTNO >= 'Z98'
       ORDER BY DEPTNO

SQL¹®ÀÇ Çü½Ä°ú ÀÌ ¸í·É¹®ÀÇ °ªµé »çÀÌ¿¡´Â ¹Ì¹¦ÇÑ °ü°è°¡ ÀÖÀ¸¹Ç·Î, µÎ °³ÀÇ ¼ø¼­°¡ ORDER BYÀý¿¡ ÀÇÇØ °íÀ¯ÇÏ°Ô °áÁ¤µÇÁö ¾Ê´Â ÇÑ µÎ °³ÀÇ ¼­·Î ´Ù¸¥ SQL¹®ÀÌ °°Àº ¼ø¼­·Î ÇàÀ» ¸®ÅÏÇÑ´Ù°í °¡Á¤Çؼ­´Â ¾È µË´Ï´Ù.

¿ª¼ø °Ë»ö

ÇàµéÀÌ ¿À¸§Â÷¼øÀ¸·Î Á¤·ÄµÇ´Â °ÍÀÌ ±âº»°ªÀÔ´Ï´Ù. DEPTNOÀÇ °¢ °ª¿¡ ´ëÇØ ÇÑ Çุ ÀÖ´Â °æ¿ì ´ÙÀ½ ¸í·É¹®Àº Çà¿¡ °íÀ¯ÇÑ ¿À¸§Â÷¼øÀ» ÁöÁ¤ÇÕ´Ï´Ù.

     SELECT * FROM DEPARTMENT
       WHERE LOCATION = 'CALIFORNIA'
       ORDER BY DEPTNO

°°Àº ÇàµéÀ» ¿ª¼øÀ¸·Î °Ë»öÇÏ·Á¸é ´ÙÀ½ ¸í·É¹®¿¡¼­Ã³·³ ³»¸²Â÷¼øÀ¸·Î ÁöÁ¤ÇϽʽÿÀ.

     SELECT * FROM DEPARTMENT
       WHERE LOCATION = 'CALIFORNIA'
       ORDER BY DEPTNO DESC

µÎ ¹øÂ° ¸í·É¹®ÀÇ Ä¿¼­´Â ù¹øÂ° ¸í·É¹®ÀÇ Ä¿¼­¿Í Á¤¹Ý´ë·Î ÇàÀ» °Ë»öÇÕ´Ï´Ù. °Ë»ö ¼ø¼­´Â ù¹øÂ° ¸í·É¹®ÀÌ °íÀ¯ÇÑ ÀýÂ÷¸¦ ÁöÁ¤ÇÒ °æ¿ì¿¡¸¸ È¿·ÂÀ» ¹ßÈÖÇÕ´Ï´Ù.

ÇàÀ» ¿ª¼øÀ¸·Î °Ë»öÇÏ·Á¸é DEPTNO Ä÷³¿¡ µÎ °³ÀÇ »öÀÎ, Áï Çϳª´Â ¿À¸§Â÷¼øÀ¸·Î ´Ù¸¥ Çϳª´Â ³»¸²Â÷¼øÀ¸·Î ÇÏ´Â °ÍÀÌ À¯¿ëÇÒ °ÍÀÔ´Ï´Ù.

Å×ÀÌºí ³¡¿¡ À§Ä¡ ¼³Á¤

µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº Å×ÀÌºí¿¡ ÀúÀåµÈ µ¥ÀÌÅÍ¿¡ ´ëÇÑ ¼ø¼­¸¦ º¸ÀåÇÏÁö ¸øÇϹǷΠÅ×À̺íÀÇ ³¡Àº Á¤ÀǵÇÁö ¾Ê½À´Ï´Ù. ±×·¯³ª, ¼ø¼­´Â SQL¹®ÀÇ °á°ú¿¡ Á¤Àǵ˴ϴÙ.

     SELECT * FROM DEPARTMENT
       ORDER BY DEPTNO DESC

ÀÌ ¿¹¿¡¼­ ´ÙÀ½ ¸í·É¹®Àº ÃÖ°íÀÇ DEPTNO °ªÀ» °¡Áø Çà¿¡ Ä¿¼­¸¦ µÓ´Ï´Ù.

     SELECT * FROM DEPARTMENT
       WHERE DEPTNO =
       (SELECT MAX(DEPTNO) FROM DEPARTMENT)

±×·¯³ª, ¿©·¯ ÇàÀÇ °ªÀÌ °°À» °æ¿ì Ä¿¼­´Â ù¹øÂ° Çà¿¡ À§Ä¡ÇÕ´Ï´Ù.

ÀÌÀü¿¡ °Ë»öµÈ µ¥ÀÌÅÍ °»½Å

µÚ·Î È­¸éÀ̵¿Çϰųª ÀÌÀü¿¡ °Ë»öµÈ µ¥ÀÌÅ͸¦ °»½ÅÇÏ·Á¸é ÀÌ¹Ì °Ë»öµÈ µ¥ÀÌÅÍÀÇ È­¸éÀ̵¿ ¹× °Ë»öµÈ µ¥ÀÌÅÍ °»½Å¿¡ ¼³¸íµÇ¾î ÀÖ´Â ±â¼úµéÀ» Á¶ÇÕÇÏ¿© »ç¿ëÇØ º¸½Ê½Ã¿À. ´ÙÀ½ Áß Çϳª¸¦ ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

  1. µÎ ¹øÂ° Ä¿¼­°¡ °»½ÅµÉ µ¥ÀÌÅÍ¿¡ ÀÖ°í SELECT¹®ÀÌ Á¦ÇÑµÈ ¿ä¼Ò¸¦ »ç¿ëÇÏÁö ¾Ê´Â °æ¿ì Ä¿¼­¿¡ ÀÇÇØ Á¦¾îµÇ´Â UPDATE¹®À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. WHERE CURRENT OFÀý¿¡¼­ µÎ ¹øÂ° Ä¿¼­¸¦ ¸í¸íÇϽʽÿÀ.
  2. ´Ù¸¥ °æ¿ì, ÇàÀÇ ¸ðµç °ªµéÀ» ¸í¸íÇÏ´Â WHEREÀý¿¡¼­ UPDATE¸¦ »ç¿ëÇϰųª, Å×À̺íÀÇ ±âº» ۸¦ ÁöÁ¤ÇϽʽÿÀ. ¼­·Î ´Ù¸¥ º¯¼ö °ªµé·Î ÇÑ ¸í·É¹®À» ¿©·¯ ¹ø ½ÇÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

¿¹: UPDAT ÇÁ·Î±×·¥

UPDAT ÇÁ·Î±×·¥Àº µ¿Àû SQLÀ» »ç¿ëÇÏ¿© SAMPLE µ¥ÀÌÅͺ£À̽ºÀÇ STAFF Å×ÀÌºí¿¡ ¾×¼¼½ºÇÑ ÈÄ, ¸ðµç °ü¸®ÀÚ¸¦ »ç¿øÀ¸·Î º¯°æÇÕ´Ï´Ù. ±×·¯¸é ÇÁ·Î±×·¥Àº ÀÛ¾÷ ´ÜÀ§(UOW)¸¦ ±¸°£ º¹¿øÇÏ¿© º¯°æ»çÇ×À» º¹¿ø½Ãŵ´Ï´Ù. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:

C
updat.sqc

Java
Updat.sqlj

COBOL
updat.sqb

REXX
updat.cmd

UPDAT ÇÁ·Î±×·¥ ÀÛ¾÷ ¹æ¹ý

  1. SQLCA ±¸Á¶ Á¤ÀÇ INCLUDE SQLCA¹®Àº SQLCA ±¸Á¶¸¦ Á¤ÀÇ ¹× ¼±¾ðÇϰí, ±¸Á¶ ³»ÀÇ ¿ä¼Ò·Î¼­ SQLCODE¸¦ Á¤ÀÇÇÕ´Ï´Ù. SQLCAÀÇ SQLCODE Çʵå´Â SQL¹®°ú µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ API È£ÃâÀÌ ½ÇÇàµÈ ÈÄ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡ ÀÇÇØ ¿À·ù Á¤º¸·Î °»½ÅµË´Ï´Ù.

    Java ÀÀ¿ëÇÁ·Î±×·¥Àº SQLException ¿ÀºêÁ§Æ®¿¡ ´ëÇØ Á¤ÀÇµÈ ¸Þ¼Òµå¸¦ ÅëÇØ SQLCODE ¹× SQLSTATE¿¡ ¾×¼¼½ºÇϹǷΠµ¿µîÇÑ "include SQLCA"¹®ÀÌ ÇÊ¿äÇÏÁö ¾Ê½À´Ï´Ù.

    REXX ÀÀ¿ëÇÁ·Î±×·¥¿¡´Â ÀÀ¿ëÇÁ·Î±×·¥ »ç¿ëÀ» À§ÇØ »çÀü Á¤ÀǵÈ, SQLCA¶ó´Â SQLCA ±¸Á¶ÀÇ ¹ß»ýÀÌ ÀÖ½À´Ï´Ù. ÀÌ ¹ß»ýÀº ÀÀ¿ëÇÁ·Î±×·¥ÀÇ Á¤ÀÇ ¾øÀÌ ÂüÁ¶µÉ ¼ö ÀÖ½À´Ï´Ù.

  2. È£½ºÆ® º¯¼ö Á¤ÀÇ. BEGIN DECLARE SECTION ¹× END DECLARE SECTION¹®Àº È£½ºÆ® º¯¼ö ¼±¾ðÀ» ºÐ¸®ÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ¿© µ¥ÀÌÅ͸¦ µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À¸·Î Àü´ÞÇϰųª µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥À¸·ÎºÎÅÍ µ¥ÀÌÅ͸¦ Àü´ÞÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â SQL¹®¿¡¼­ ÂüÁ¶µÉ ¶§ ÄÝ·Ð(:)À¸·Î ½ÃÀ۵˴ϴÙ.

    Java ¹× REXX ÀÀ¿ëÇÁ·Î±×·¥Àº LOB ÆÄÀÏ ÂüÁ¶ º¯¼ö ¹× À§Ä¡ ÁöÁ¤ÀÚÀÇ °æ¿ì(REXX)¸¦ Á¦¿ÜÇϰí, È£½ºÆ® º¯¼ö¸¦ ¼±¾ðÇÒ Çʿ䰡 ¾ø½À´Ï´Ù. È£½ºÆ® º¯¼ö µ¥ÀÌÅÍ À¯Çü ¹× Å©±â´Â º¯¼ö°¡ ÂüÁ¶µÉ ¶§ ·±Å¸Àӽà °áÁ¤µË´Ï´Ù.

  3. µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°á. ÇÁ·Î±×·¥Àº sample µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇÏ¿© ÀÌ µ¥ÀÌÅͺ£À̽º¿¡ ´ëÇÑ °øÀ¯ ¾×¼¼½º¸¦ ¿äûÇÕ´Ï´Ù. (START DATABASE MANAGER API È£ÃâÀ̳ª db2start ¸í·ÉÀÌ ¹ßÇàµÇ¾ú´Ù°í ÀüÁ¦ÇÕ´Ï´Ù.) °øÀ¯ ¾×¼¼½º¸¦ »ç¿ëÇÏ¿© µ¿ÀÏÇÑ µ¥ÀÌÅͺ£À̽º¿¡ ¿¬°áÇÏ´Â ´Ù¸¥ ÇÁ·Î±×·¥µé¿¡°Ôµµ ¾×¼¼½º°¡ ±ÇÇÑ ºÎ¿©µË´Ï´Ù.
  4. UPDATE SQL¹® ½ÇÇà. SQL¹®Àº È£½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ¿© Á¤ÀûÀ¸·Î ½ÇÇàµË´Ï´Ù. staff Å×À̺íÀÇ job Ä÷³Àº È£½ºÆ® º¯¼ö °ªÀ¸·Î ¼³Á¤µË´Ï´Ù. ¿©±â¼­ job Ä÷³¿¡´Â Mgr °ªÀÌ ÀÖ½À´Ï´Ù.
  5. DELETE SQL¹® ½ÇÇà SQL¹®Àº È£½ºÆ® º¯¼ö »ç¿ë°ú ÇÔ²² Á¤ÀûÀ¸·Î ½ÇÇàµË´Ï´Ù. ÁöÁ¤µÈ È£½ºÆ® º¯¼ö¿Í °°Àº job Ä÷³ °ª(jobUpdate/job-update/job_update)ÀÌ ÀÖ´Â ¸ðµç ÇàµéÀº »èÁ¦µË´Ï´Ù.
  6. INSERT SQL¹® ½ÇÇà ÇàÀÌ STAFF Å×ÀÌºí¿¡ »ðÀԵ˴ϴÙ. ÀÌ »ðÀÔÀ¸·Î ÀÌ SQL¹®ÀÇ ½ÇÇà Àü¿¡ ¼³Á¤µÈ È£½ºÆ® º¯¼ö »ç¿ëÀÌ ±¸ÇöµË´Ï´Ù.
  7. Æ®·£Àè¼Ç Á¾·á. ROLLBACK¹®À¸·Î ÀÛ¾÷ ´ÜÀ§(UOW)¸¦ Á¾·áÇÕ´Ï´Ù. ÀÌÀü¿¡ ½ÇÇàµÈ SQL¹® °á°ú´Â COMMIT¹®À» »ç¿ëÇÏ¿© ¿µ±¸ÀûÀ¸·Î µÇ°Å³ª, ROLLBACK¹®À» »ç¿ëÇÏ¿© ½ÇÇà Ãë¼ÒµË´Ï´Ù. ÀÛ¾÷ ´ÜÀ§(UOW) ³»ÀÇ ¸ðµç SQL¹®ÀÌ ¿µÇâÀ» ¹Þ½À´Ï´Ù.

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¶ó°í ÇÏ´Â ¿ÜºÎ ÇÁ·Î±×·¥ÀÔ´Ï´Ù.

REXX
CHECKERRÀº ÇöÀç ÇÁ·Î±×·¥ÀÇ ³¡¿¡ ÀÖ½À´Ï´Ù.

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

C ¿¹: UPDAT.SQC

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlenv.h>
#include "utilemb.h"
 
EXEC SQL INCLUDE SQLCA;  (1)
 
int main(int argc, char *argv[])
{
 
   EXEC SQL BEGIN DECLARE SECTION;  (2)
      char statement[256];
      char userid[9];
      char passwd[19];
      char jobUpdate[6];
   EXEC SQL END DECLARE SECTION;
 
   printf( "\nSample C program:  UPDAT \n");
 
   if (argc == 1)
   {
      EXEC SQL CONNECT TO sample;
	  EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else if (argc == 3)
   { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd; (3)
	  EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else
   {
      printf ("\nUSAGE: updat [userid passwd]\n\n");
      return 1;
   } /* endif */
 
   strcpy (jobUpdate, "Clerk");
   EXEC SQL UPDATE staff SET job = :jobUpdate WHERE job = 'Mgr'; (4)
   EMB_SQL_CHECK("UPDATE STAFF");
   printf ("All 'Mgr' have been demoted to 'Clerk'!\n" );
 
   strcpy (jobUpdate, "Sales");
   EXEC SQL DELETE FROM staff WHERE job = :jobUpdate; (5)
   EMB_SQL_CHECK("DELETE FROM STAFF");
   printf ("All 'Sales' people have been deleted!\n");
 
   EXEC SQL INSERT INTO staff
      VALUES (999, 'Testing', 99, :jobUpdate, 0, 0, 0);  (6)
   EMB_SQL_CHECK("INSERT INTO STAFF");
   printf ("New data has been inserted\n");
 
   EXEC SQL ROLLBACK; (7)
   EMB_SQL_CHECK("ROLLBACK");
   printf( "On second thought -- changes rolled back.\n" );
 
   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : UPDAT.SQC */

Java ¿¹: Updat.sqlj

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
 
 
class Updat
{   static
  {   try
    {   Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
    }
    catch (Exception e)
    {   System.out.println ("\n  Error loading DB2 Driver...\n");
      System.out.println (e);
      System.exit(1);
    }
  }
 
 
  public static void main(String argv[])
  {   try
    {   System.out.println ("\n  Java Updat Sample");
 
      String url = "jdbc:db2:sample";       // URL is jdbc:db2:dbname
      Connection con = null;
 
      // Set the connection                  (3)
      if (argv.length == 0)
      {   // connect with default id/password
        con = DriverManager.getConnection(url);
      }
      else if (argv.length == 2)
      {   String userid = argv[0];
        String passwd = argv[1];
 
        // connect with user-provided username and password
        con = DriverManager.getConnection(url, userid, passwd);
      }
      else
      {   throw new Exception("\nUsage: java Updat [username password]\n");
      } 
 
      // Set the default context
      DefaultContext ctx = new DefaultContext(con);
      DefaultContext.setDefaultContext(ctx);
 
      // Enable transactions
      con.setAutoCommit(false);
 
      // UPDATE/DELETE/INSERT
      try
      {   String jobUpdate = null;
 
        jobUpdate="Clerk";
        #sql {UPDATE staff SET job = :jobUpdate WHERE job = 'Mgr'};  (4)
        System.out.println("\nAll 'Mgr' have been demoted to 'Clerk'!");
 
        jobUpdate="Sales";
        #sql {DELETE FROM staff WHERE job = :jobUpdate};
        System.out.println("All 'Sales' people have been deleted!"); (5)
 
        #sql {INSERT INTO staff
          VALUES (999, 'Testing', 99, :jobUpdate, 0, 0, 0)}; (6)
        System.out.println("New data has been inserted");
      }
      catch( Exception e )
      {   throw e;
      } 
      finally
      {   // Rollback the transaction
        System.out.println("\nRollback the transaction...");
        #sql { ROLLBACK };                                          (7)
        System.out.println("Rollback done.");
      }
    }
    catch (Exception e)
    {   System.out.println (e);
    }
  }
}

COBOL ¿¹: UPDAT.SQB

       Identification Division.
       Program-ID. "updat".
 
       Data Division.
       Working-Storage Section.
 
           copy "sql.cbl".
           copy "sqlenv.cbl".
           copy "sqlca.cbl".        (1)
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.         (2)
       01 statement         pic x(80).
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
       01 job-update        pic x(5).
           EXEC SQL END DECLARE SECTION END-EXEC.
 
      * Local variables
       77 errloc          pic x(80).
       77 error-rc        pic s9(9) comp-5.
       77 state-rc        pic s9(9) comp-5.
 
      * Variables for the GET ERROR MESSAGE API
      * Use application specific bound instead of BUFFER-SZ
       77 buffer-size     pic s9(4) comp-5 value 1024.
       77 line-width      pic s9(4) comp-5 value 80.
       77 error-buffer    pic x(1024).
       77 state-buffer    pic x(1024).
 
       Procedure Division.
       Main Section.
           display "Sample COBOL program:  UPDAT".
 
           display "Enter your user id (default none): "
                with no advancing.
           accept userid.
 
           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.
 
      * Passwords in a CONNECT statement must be entered in a VARCHAR format
      * with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial " ".
 
           EXEC SQL CONNECT TO sample USER :userid USING :passwd        (3)
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
 
           move "Clerk" to job-update.
           EXEC SQL UPDATE staff SET job=:job-update        (4)
                    WHERE job='Mgr' END-EXEC.
           move "UPDATE STAFF" to errloc.
           call "checkerr" using SQLCA errloc.
 
           display "All 'Mgr' have been demoted to 'Clerk'!".
 
           move "Sales" to job-update.
           EXEC SQL DELETE FROM staff WHERE job=:job-update END-EXEC.   (5)
           move "DELETE FROM STAFF" to errloc.
           call "checkerr" using SQLCA errloc.
 
           display "All 'Sales' people have been deleted!".
 
           EXEC SQL INSERT INTO staff VALUES (999, 'Testing', 99,       (6)
                    :job-update, 0, 0, 0) END-EXEC.
           move "INSERT INTO STAFF" to errloc.
           call "checkerr" using SQLCA errloc.
 
           display "New data has been inserted".
 
           EXEC SQL ROLLBACK END-EXEC.                                  (7)
           move "ROLLBACK" to errloc.
           call "checkerr" using SQLCA errloc.
 
           DISPLAY "On second thought -- changes rolled back."
 
           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
 
       End-Prog.
           stop run.

REXX ¿¹: UPDAT.CMD

ÁÖ:REXX ÇÁ·Î±×·¥¿¡´Â Á¤Àû SQLÀ» Æ÷ÇÔÇÒ ¼ö ¾ø½À´Ï´Ù. ÀÌ ÇÁ·Î±×·¥Àº µ¿Àû SQL·Î ÀÛ¼ºµË´Ï´Ù.
/* REXX program UPDAT.CMD */
 
parse version rexxType .
parse source platform .
 
if platform == 'AIX/6000' & rexxType == 'REXXSAA' then
do
  rcy = SysAddFuncPkg("db2rexx")
end
else
do
  if RxFuncQuery('SQLDBS') <> 0 then
    rcy = RxFuncAdd( 'SQLDBS',  'db2ar', 'SQLDBS'  )
 
  if RxFuncQuery('SQLEXEC') <> 0 then
    rcy = RxFuncAdd( 'SQLEXEC', 'db2ar', 'SQLEXEC' )
end
 
/* pull in command line arguments */
parse arg userid passwd .
 
/* check to see if the proper number of arguments have been passed in */
   PARSE ARG dbname userid password .
   if ((dbname = "" ) | ,
       (userid <> "" & password = "") ,
      ) then do
      SAY "USAGE: updat.cmd <dbname> [<userid> <password>]"
 
       exit -1
   end
 
   /* connect to database */
   SAY
   SAY 'Connect to' dbname
   IF password= "" THEN
      CALL SQLEXEC 'CONNECT TO' dbname
   ELSE
      CALL SQLEXEC 'CONNECT TO' dbname 'USER' userid 'USING' password
 
   CALL CHECKERR 'Connect to '
   SAY "Connected"
 
say 'Sample REXX program: UPDAT.CMD'
 
jobupdate = "'Clerk'"
st = "UPDATE staff SET job =" jobupdate "WHERE job = 'Mgr'"
call SQLEXEC 'EXECUTE IMMEDIATE :st' (4)
call CHECKERR 'UPDATE'
say "All 'Mgr' have been demoted to 'Clerk'!"
 
jobupdate = "'Sales'"
st = "DELETE FROM staff WHERE job =" jobupdate
call SQLEXEC 'EXECUTE IMMEDIATE :st' (5)
call CHECKERR 'DELETE'
say "All 'Sales' people have been deleted!"
 
st = "INSERT INTO staff VALUES (999, 'Testing', 99," jobupdate ", 0, 0, 0)"
call SQLEXEC 'EXECUTE IMMEDIATE :st' (6)
call CHECKERR 'INSERT'
say 'New data has been inserted'
 
call SQLEXEC 'ROLLBACK' (7)
call CHECKERR 'ROLLBACK'
say 'On second thought...changes rolled back.'
 
call SQLEXEC 'CONNECT RESET'
call CHECKERR 'CONNECT RESET'
 
 
CHECKERR:
  arg errloc
 
  if  ( SQLCA.SQLCODE = 0 ) then
    return 0
  else do
    say '--- error report ---'
    say 'ERROR occurred :' errloc
    say 'SQLCODE :' SQLCA.SQLCODE
 
    /******************************\
    * GET ERROR MESSAGE API called *
    \******************************/
    call SQLDBS 'GET MESSAGE INTO :errmsg LINEWIDTH 80'
    say errmsg
    say '--- end error report ---'
 
    if (SQLCA.SQLCODE < 0 ) then
      exit
    else do
      say 'WARNING - CONTINUING PROGRAM WITH ERRORS'
      return 0
    end
  end
return 0


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