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

Ä¿¼­¸¦ ÀÌ¿ëÇÑ ´ÙÁß Çà ¼±ÅÃ

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Çà ¼¼Æ®¸¦ °Ë»öÇϵµ·Ï Çϱâ À§ÇØ SQLÀº Ä¿¼­¶ó´Â ¸ÞÄ«´ÏÁòÀ» »ç¿ëÇÕ´Ï´Ù.

Ä¿¼­¶ó´Â °³³äÀÇ ÀÌÇØ¸¦ µ½±â À§ÇØ, µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº SELECT¹®À» ½ÇÇàÇÏ¿© °Ë»öµÇ´Â ¸ðµç ÇàµéÀ» º¸À¯ÇÒ °á°ú Å×À̺íÀ» ±¸ÃàÇÑ´Ù°í °¡Á¤ÇÕ´Ï´Ù. Ä¿¼­´Â ÀÌ Å×À̺íÀÇ ÇöÀç ÇàÀ» °¡¸®Å°°Å³ª ½Äº°ÇÔÀ¸·Î½á ÀÀ¿ëÇÁ·Î±×·¥ÀÌ »ç¿ëÇÒ ¼ö ÀÖ´Â °á°ú Å×À̺í·ÎºÎÅÍ ÇàÀ» ÀÛ¼ºÇÕ´Ï´Ù. Ä¿¼­°¡ »ç¿ëµÉ ¶§ ÀÀ¿ëÇÁ·Î±×·¥Àº µ¥ÀÌÅÍ Á¶°ÇÀÇ ³¡, Áï NOT FOUND Á¶°Ç, SQLCODE +100(SQLSTATE 02000)¿¡ µµ´ÞÇÒ ¶§±îÁö °á°ú Å×À̺í·ÎºÎÅÍ ¼øÂ÷ÀûÀ¸·Î °¢ ÇàÀ» °Ë»öÇÒ ¼ö ÀÖ½À´Ï´Ù. SELECT¹® ½ÇÇà °á°ú ȹµæµÈ Çà ¼¼Æ®µéÀº °Ë»ö Á¶°ÇÀ» ÃæÁ·½ÃŰ´Â Çà ¼ö¿¡ µû¶ó 0°³, 1°³ ¶Ç´Â ±× ÀÌ»óÀÇ Çàµé·Î ±¸¼ºµÉ ¼ö ÀÖ½À´Ï´Ù.

Ä¿¼­ ó¸® ´Ü°è´Â ´ÙÀ½°ú °°½À´Ï´Ù.

  1. DECLARE CURSOR¹®À» »ç¿ëÇÏ¿© Ä¿¼­¸¦ ÁöÁ¤ÇÕ´Ï´Ù.
  2. Á¶È¸¸¦ ¼öÇàÇϰí OPEN¹®À» »ç¿ëÇÑ °á°ú Å×À̺íÀ» ±¸ÃàÇÕ´Ï´Ù.
  3. FETCH¹®À» »ç¿ëÇÏ¿© ÇÑ ¹ø¿¡ ÇÑ ÇàÀ» °Ë»öÇÕ´Ï´Ù.
  4. (ÇÊ¿äÇÑ °æ¿ì) DELETE¹® ¶Ç´Â UPDATE¹®ÀÌ ÀÖ´Â ÇàÀ» ó¸®ÇÕ´Ï´Ù.
  5. CLOSE¹®À» »ç¿ëÇÏ¿© Ä¿¼­¸¦ Á¾·áÇÕ´Ï´Ù.

ÀÀ¿ëÇÁ·Î±×·¥Àº µ¿½Ã¿¡ ¿©·¯ Ä¿¼­¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. Ä¿¼­¸¶´Ù °íÀ¯ÀÇ DECLARE CURSOR, OPEN, CLOSE ¹× FETCH¹® ¼¼Æ®°¡ ÇÊ¿äÇÕ´Ï´Ù.

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Çà ¼¼Æ®¸¦ ¼±ÅÃÇϰí, Ä¿¼­¸¦ »ç¿ëÇÏ¿© ÇÑ ¹ø¿¡ ÇÑ Çà ¼³Á¤À» ó¸®ÇÏ´Â ¹æ¹ýÀÇ ¿¹´Â ¿¹: Ä¿¼­ ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

Ä¿¼­ »ç¿ë ¹× ¼±¾ð

DECLARE CURSOR¹®Àº SELECT¹®À» »ç¿ëÇÏ¿© °Ë»öÇÒ Çà ¼¼Æ®¸¦ ½Äº°ÇÏ¿© Ä¿¼­¸¦ Á¤ÀÇ ¹× ¸í¸íÇÕ´Ï´Ù.

ÀÀ¿ëÇÁ·Î±×·¥Àº Ä¿¼­¿¡ À̸§À» ÁöÁ¤ÇÕ´Ï´Ù. ÀÌ À̸§Àº ÈÄ¼Ó OPEN¹®, FETCH¹® ¹× CLOSE¹®¿¡¼­ ÂüÁ¶µË´Ï´Ù. Á¶È¸´Â À¯È¿ÇÑ ¼±Åà ¸í·É¹®ÀÔ´Ï´Ù.

Ä¿¼­¹® Á¤ÀÇ¿¡´Â Á¤Àû SELECT¹®°ú ¿¬°üµÈ DECLARE¹®ÀÌ ÀÖ½À´Ï´Ù.

¾ð¾î
¿¹Á¦ ¼Ò½º ÄÚµå

C/C++
 EXEC SQL DECLARE C1 CURSOR FOR
  SELECT PNAME, DEPT FROM STAFF
  WHERE JOB=:host_var;

Java (SQLJ)
#sql iterator cursor1(host_var data type);
#sql cursor1 = { SELECT PNAME, DEPT FROM STAFF
                 WHERE JOB=:host_var };

COBOL
 EXEC SQL DECLARE C1 CURSOR FOR
  SELECT NAME, DEPT FROM STAFF
    WHERE JOB=:host-var END-EXEC.

FORTRAN
 EXEC SQL DECLARE C1 CURSOR FOR
+  SELECT NAME, DEPT FROM STAFF
+  WHERE JOB=:host_var
ÁÖ:DECLARE¹®ÀÇ À§Ä¡´Â ÀÓÀÇÀûÀÌÁö¸¸, ÃÖÃÊÀÇ Ä¿¼­ »ç¿ë À§¿¡ À§Ä¡ÇØ¾ß ÇÕ´Ï´Ù.

Ä¿¼­ ¹× ÀÛ¾÷ ´ÜÀ§(UOW) °í·Á»çÇ×

COMMIT ¶Ç´Â ROLLBACK Á¶ÀÛÀÇ Á¶Ä¡´Â Ä¿¼­°¡ ¼±¾ðµÇ´Â ¹æ½Ä¿¡ µû¶ó Ä¿¼­¸¶´Ù ´Ù¸¨´Ï´Ù.

Àбâ Àü¿ë Ä¿¼­

Ä¿¼­°¡ Àбâ Àü¿ëÀÌ°í ¹Ýº¹ Àбâ(RR) ºÐ¸® ·¹º§À» »ç¿ëÇÏ´Â °æ¿ì, ÀÛ¾÷ ´ÜÀ§(UOW)¿¡ ÇÊ¿äÇÑ ½Ã½ºÅÛ Å×ÀÌºí¿¡¼­ ¹Ýº¹ Àбâ Àá±ÝÀÌ ¿©ÀüÈ÷ ¼öÁý ¹× À¯Áöº¸¼öµË´Ï´Ù. µû¶ó¼­ ÀÀ¿ëÇÁ·Î±×·¥Àº Àбâ Àü¿ë Ä¿¼­¿¡ ´ëÇØ¼­µµ COMMIT¹®À» Á¤±âÀûÀ¸·Î ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù.

WITH HOLD ¿É¼Ç

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ COMMIT¹®À» ¹ßÇàÇÏ¿© ÀÛ¾÷ ´ÜÀ§(UOW)¸¦ ¿Ï·áÇÏ´Â °æ¿ì, WITH HOLD ¿É¼Ç »ç¿ëÀ» Á¦¿ÜÇÏ°í ¸ðµç °³¹æ Ä¿¼­´Â µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥¿¡ ÀÇÇØ ÀÚµ¿À¸·Î ´ÝÈü´Ï´Ù.

WITH HOLD·Î ¼±¾ðµÈ Ä¿¼­´Â ¿©·¯ °³ÀÇ ÀÛ¾÷ ´ÜÀ§(UOW)¸¦ ÅëÇØ ¾×¼¼½ºÇÏ´Â ÀÚ¿øÀ» À¯Áöº¸¼öÇÕ´Ï´Ù. Ä¿¼­¸¦ WITH HOLD·Î ¼±¾ðÇÏ´Â °á°ú´Â ÀÛ¾÷ ´ÜÀ§(UOW)°¡ Á¾·áµÇ´Â ¹æ½Ä¿¡ µû¶ó ´Þ¶óÁý´Ï´Ù.

ÀÛ¾÷ ´ÜÀ§(UOW)°¡ COMMIT¹®À¸·Î Á¾·áµÉ °æ¿ì WITH HOLD·Î Á¤ÀÇµÈ °³¹æ Ä¿¼­´Â OPEN »óÅ·Π³²¾Æ ÀÖ½À´Ï´Ù. Ä¿¼­´Â °á°ú Å×À̺íÀÇ ´ÙÀ½ ³í¸® Çà ¾Õ¿¡ À§Ä¡ÇÕ´Ï´Ù. ¶ÇÇÑ, WITH HOLD·Î Á¤ÀÇµÈ OPEN Ä¿¼­¸¦ ÂüÁ¶ÇÏ´Â ÁغñµÈ ¸í·É¹®ÀÌ º¸À¯µË´Ï´Ù. ƯÁ¤ Ä¿¼­·Î ¿¬°üµÈ FETCH ¹× CLOSE ¿äû¸¸ÀÌ COMMIT Á÷ÈÄ¿¡ À¯È¿ÇÕ´Ï´Ù. UPDATE WHERE CURRENT OF¹® ¹× DELETE WHERE CURRENT OF¹®Àº µ¿ÀÏÇÑ ÀÛ¾÷ ´ÜÀ§(UOW) ³»¿¡¼­ ÆäÄ¡µÈ Çàµé¿¡ ´ëÇØ¼­¸¸ À¯È¿ÇÕ´Ï´Ù. ÆÐŰÁö°¡ ÀÛ¾÷ ´ÜÀ§(UOW) Áß¿¡ ¸®¹ÙÀεåµÇ´Â °æ¿ì º¸À¯µÈ ¸ðµç Ä¿¼­°¡ ´ÝÈü´Ï´Ù.

ÀÛ¾÷ ´ÜÀ§(UOW)°¡ ROLLBACK¹®À¸·Î Á¾·áµÇ´Â °æ¿ì, ¸ðµç °³¹æ Ä¿¼­´Â ´ÝÈ÷°í, ÀÛ¾÷ ´ÜÀ§ Áß¿¡ ȹµæµÈ ¸ðµç Àá±ÝÀÌ ÇØÁ¦µÇ¸ç, ÇØ´ç ÀÛ¾÷ ´ÜÀ§¿¡¼­ ¿Ï·áµÈ ÀÛ¾÷¿¡ Á¾¼ÓÀûÀÎ ÁغñµÈ ¸ðµç ¸í·É¹®µéÀÌ Á¦°ÅµË´Ï´Ù.

¿¹¸¦ µé¾î, TEMPL Å×ÀÌºí¿¡´Â 1000°³ Ç׸ñÀÌ µé¾î ÀÖ½À´Ï´Ù. ¸ðµç »ç¿øÀÇ ±Þ¿© Ä÷³À» °»½ÅÇϰí, 100°³ ÇàÀ» °»½ÅÇÒ ¶§¸¶´Ù COMMIT¹®À» ¹ßÇàÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.

  1. WITH HOLD ¿É¼ÇÀ» »ç¿ëÇÏ¿© Ä¿¼­¸¦ ¼±¾ðÇÕ´Ï´Ù.
    EXEC SQL DECLARE EMPLUPDT CURSOR WITH HOLD FOR
      SELECT EMPNO, LASTNAME, PHONENO, JOBCODE, SALARY
      FROM TEMPL FOR UPDATE OF SALARY
    
  2. Ä¿¼­¸¦ ¿­°í °á°ú Å×ÀÌºí¿¡¼­ ÇÑ ¹ø¿¡ ÇÑ ÇàÀÇ µ¥ÀÌÅ͸¦ °¡Á®¿É´Ï´Ù.
    EXEC SQL OPEN EMPLUPDT
      .
      .
      .
     
    EXEC SQL FETCH EMPLUPDT
      INTO :upd_emp, :upd_lname, :upd_tele, :upd_jobcd, :upd_wage,
    
  3. ÇàÀ» °»½ÅÇϰųª »èÁ¦ÇÏ·Á¸é, WHERE CURRENT OF ¿É¼Ç°ú ÇÔ²² UPDATE¹® ¶Ç´Â DELETE¹®À» »ç¿ëÇϽʽÿÀ. ¿¹¸¦ µé¾î, ÇöÀç ÇàÀ» °»½ÅÇϱâ À§ÇØ ÇÁ·Î±×·¥ÀÌ ´ÙÀ½À» ½ÇÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.
    EXEC SQL UPDATE TEMPL SET SALARY = :newsalary
      WHERE CURRENT OF EMPLUPDT
    
  4. COMMIT°¡ ¹ßÇàµÈ ÈÄ ´Ù¸¥ ÇàÀ» °»½ÅÇÏ·Á¸é ¿ì¼± FETCH¸¦ ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù.

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ´ÙÀ½°ú °°Àº °æ¿ì FETCH¹® ¶Ç´Â CLOSE¹®¿¡¼­ ¸®Å쵃 ¼ö ÀÖ´Â SQLCODE -501(SQLSTATE 24501)À» °ËÃâ ¹× Ã³¸®ÇÏ·Á¸é »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥¿¡ Äڵ带 Æ÷ÇÔ½ÃÄÑ¾ß ÇÕ´Ï´Ù.

Á¾¼ÓµÈ Å×À̺íÀ» Á¦°ÅÇÔÀ¸·Î½á ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ÆÐŰÁö¸¦ ¹«È¿È­ÇÒ °æ¿ì ÆÐŰÁö´Â µ¿ÀûÀ¸·Î ¸®¹ÙÀεåµË´Ï´Ù. ÀÌ·± °æ¿ì, µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ Ä¿¼­¸¦ ´ÝÀ¸¹Ç·Î, FETCH¹® ¶Ç´Â CLOSE¹®¿¡ ´ëÇØ SQLCODE -501(SQLSTATE 24501)ÀÌ ¸®Åϵ˴ϴÙ. ÀÌ·± »óȲ¿¡¼­ SQLCODE -501(SQLSTATE 24501)À» ó¸®ÇÏ´Â ¹æ¹ýÀº Ä¿¼­·ÎºÎÅÍ ÇàÀ» ÆäÄ¡ÇÒ °ÍÀÎÁö¿¡ µû¶ó ´Þ¶óÁý´Ï´Ù.

WITH RELEASE ¿É¼Ç

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ WITH RELEASE ¿É¼ÇÀ» »ç¿ëÇÏ¿© Ä¿¼­¸¦ ´ÝÀ» ¶§ DB2´Â Ä¿¼­°¡ º¸À¯Çϰí ÀÖ´Â ¸ðµç READ Àá±ÝÀ» ÇØÁ¦ÇÏ·Á ÇÕ´Ï´Ù. Ä¿¼­´Â °è¼ÓÇØ¼­ WRITE Àá±Ý¸¸ º¸À¯ÇÕ´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ÀÌ RELEASE ¿É¼ÇÀ» »ç¿ëÇÏÁö ¾Ê°í Ä¿¼­¸¦ ´ÝÀ¸¸é, ÀÛ¾÷ ´ÜÀ§(UOW)°¡ ¿Ï·áµÉ ¶§ READ ¹× WRITE Àá±ÝÀÌ ÇØÁ¦µË´Ï´Ù.

¿¹: Ä¿¼­ ÇÁ·Î±×·¥

ÀÌ »ùÇà ÇÁ·Î±×·¥Àº Ä¿¼­¸¦ Á¤ÀÇ ¹× »ç¿ëÇÏ´Â SQL¹®À» º¸¿© ÁÝ´Ï´Ù. Ä¿¼­´Â Á¤Àû SQLÀ» »ç¿ëÇÏ¿© 󸮵˴ϴÙ. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:

C
cursor.sqc

Java
Cursor.sqlj

COBOL
cursor.sqb

REXX°¡ Á¤Àû SQLÀ» Áö¿øÇÏÁö ¾ÊÀ¸¹Ç·Î »ùÇÃÀº Á¦°øµÇÁö ¾Ê½À´Ï´Ù. Ä¿¼­¸¦ µ¿ÀûÀ¸·Î ó¸®ÇÏ´Â REXX ¿¹¿¡ ´ëÇØ¼­´Â ¿¹: µ¿Àû SQL ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

Ä¿¼­ ÇÁ·Î±×·¥ ÀÛ¾÷ ¹æ¹ý

  1. Ä¿¼­ ¼±¾ð. DECLARE CURSOR¹®Àº Ä¿¼­ c1À» Á¶È¸¿¡ ¿¬°ü½Ãŵ´Ï´Ù. Á¶È¸´Â FETCH¹®À» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥ÀÌ °Ë»öÇÏ´Â ÇàÀ» ½Äº°ÇÕ´Ï´Ù. staffÀÇ job Çʵå´Â °á°ú Å×ÀÌºí¿¡ ÁöÁ¤µÇÁö ¾Ê´õ¶óµµ °»½ÅÀÌ °¡´ÉÇϵµ·Ï Á¤Àǵ˴ϴÙ.
  2. Ä¿¼­ ¿­±â. Ä¿¼­ c1ÀÌ ¿­¸³´Ï´Ù. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ °á°ú Å×À̺íÀ» Á¶È¸ÇÏ°í ºôµåÇϵµ·Ï ÇÕ´Ï´Ù. Ä¿¼­´Â ù¹øÂ° Çà ¾Õ¿¡ À§Ä¡ÇÏ°Ô µË´Ï´Ù.
  3. Çà °Ë»ö. FETCH¹®Àº Ä¿¼­¸¦ ´ÙÀ½ Çà¿¡ À§Ä¡½Ã۰í ÇàÀÇ ³»¿ëÀ» È£½ºÆ® º¯¼ö·Î À̵¿½Ãŵ´Ï´Ù. ÀÌ ÇàÀº ÇöÀç ÇàÀÌ µË´Ï´Ù.
  4. Ä¿¼­ ´Ý±â. CLOSE¹®ÀÌ ¹ßÇàµË´Ï´Ù. Ä¿¼­¿Í ¿¬°üµÈ ÀÚ¿øµéÀ» ¸±¸®½ºÇÕ´Ï´Ù. ÇÏÁö¸¸ Ä¿¼­°¡ ¶Ç ´Ù½Ã ¿­¸± ¼ö ÀÖ½À´Ï´Ù.

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

FORTRAN
CHECKERRÀº util.f ÆÄÀÏ¿¡ ÀÖ´Â ¼­ºê·çƾÀÔ´Ï´Ù.

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

C ¿¹: CURSOR.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
 
EXEC SQL INCLUDE SQLCA;
 
int main(int argc, char *argv[])
{
 
   EXEC SQL BEGIN DECLARE SECTION;
      char   pname[10];
      short  dept;
      char userid[9];
      char passwd[19];
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: CURSOR \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;
      EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else
   {
      printf ("\nUSAGE: cursor [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   EXEC SQL DECLARE c1 CURSOR FOR  (1)
            SELECT name, dept FROM staff WHERE job='Mgr'
            FOR UPDATE OF job;
 
   EXEC SQL OPEN c1;  (2)
   EMB_SQL_CHECK("OPEN CURSOR");
 
   do
   {
      EXEC SQL FETCH c1 INTO :pname, :dept;  (3)
      if (SQLCODE != 0) break;
 
      printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
         pname, dept );
   } while ( 1 );
 
   EXEC SQL CLOSE c1;  (4)
   EMB_SQL_CHECK("CLOSE CURSOR");
 
   EXEC SQL ROLLBACK;
   EMB_SQL_CHECK("ROLLBACK");
   printf( "\nOn second thought -- changes rolled back.\n" );
 
   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : CURSOR.SQC */

Java ¿¹: Cursor.sqlj

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
 
#sql iterator CursorByName(String name, short dept) ;
#sql iterator CursorByPos(String, short ) ;
 
class Cursor
{   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 ("  Java Cursor Sample");
 
      String url = "jdbc:db2:sample";       // URL is jdbc:db2:dbname
      Connection con = null;
 
      // Set the connection
      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 Cursor [username password]\n");
      } 
 
      // Set the default context
      DefaultContext ctx = new DefaultContext(con);
      DefaultContext.setDefaultContext(ctx);
 
      // Enable transactions
      con.setAutoCommit(false);
 
      // Using cursors
      try
      {   CursorByName cursorByName;
        CursorByPos  cursorByPos;
 
        String name = null;
        short  dept=0;
 
        // Using the JDBC ResultSet cursor method
        System.out.println("\nUsing the JDBC ResultSet cursor method");
        System.out.println(" with a 'bind by name' cursor ...\n");
 
        #sql cursorByName = {
              SELECT name, dept FROM staff WHERE job='Mgr' }; (1)
        while (cursorByName.next()) (2)
        {   name = cursorByName.name(); (3)
          dept = cursorByName.dept();
 
          System.out.print (" name= " + name);
          System.out.print (" dept= " + dept);
          System.out.print ("\n");
        }
        cursorByName.close(); (4)
 
 
        // Using the SQLJ iterator cursor method
        System.out.println("\nUsing the SQLJ iterator cursor method");
        System.out.println(" with a 'bind by position' cursor ...\n");
 
        #sql cursorByPos = {
               SELECT name, dept FROM staff WHERE job='Mgr' }; (1) (2)
        while (true)
        {   #sql { FETCH :cursorByPos INTO :name, :dept }; (3)
          if (cursorByPos.endFetch()) break;
 
          System.out.print (" name= " + name);
          System.out.print (" dept= " + dept);
          System.out.print ("\n");
        }
        cursorByPos.close(); (4)
      }
      catch( Exception e )
      {   throw e;
      } 
      finally
      {   // Rollback the transaction
        System.out.println("\nRollback the transaction...");
        #sql { ROLLBACK };
        System.out.println("Rollback done.");
      }
    }
    catch( Exception e )
    {   System.out.println (e);
    }
  }
}

COBOL ¿¹: CURSOR.SQB

       Identification Division.
       Program-ID. "cursor".
 
       Data Division.
       Working-Storage Section.
 
           copy "sqlenv.cbl".
           copy "sql.cbl".
           copy "sqlca.cbl".
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 pname             pic x(10).
       77 dept              pic s9(4) comp-5.
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
           EXEC SQL END DECLARE SECTION END-EXEC.
 
       77 errloc          pic x(80).
 
       Procedure Division.
       Main Section.
           display "Sample COBOL program: CURSOR".
 
           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
              END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL DECLARE c1 CURSOR FOR             (1)
                    SELECT name, dept FROM staff
                    WHERE job='Mgr'
                    FOR UPDATE OF job END-EXEC.
 
           EXEC SQL OPEN c1 END-EXEC.           (2)
           move "OPEN CURSOR" to errloc.
           call "checkerr" using SQLCA errloc.
 
           perform Fetch-Loop thru End-Fetch-Loop
              until SQLCODE not equal 0.
 
           EXEC SQL CLOSE c1 END-EXEC.          (4)
           move "CLOSE CURSOR" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL ROLLBACK END-EXEC.
           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-Main.
           go to End-Prog.
 
       Fetch-Loop Section.
           EXEC SQL FETCH c1 INTO :PNAME, :DEPT END-EXEC.      (3)
           if SQLCODE not equal 0
              go to End-Fetch-Loop.
           display pname, " in dept. ", dept,
              " will be demoted to Clerk".
       End-Fetch-Loop. exit.
 
       End-Prog.
           stop run.


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