ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Çà ¼¼Æ®¸¦ °Ë»öÇϵµ·Ï Çϱâ À§ÇØ SQLÀº Ä¿¼¶ó´Â ¸ÞÄ«´ÏÁòÀ» »ç¿ëÇÕ´Ï´Ù.
Ä¿¼¶ó´Â °³³äÀÇ ÀÌÇØ¸¦ µ½±â À§ÇØ, µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº SELECT¹®À» ½ÇÇàÇÏ¿© °Ë»öµÇ´Â ¸ðµç ÇàµéÀ» º¸À¯ÇÒ °á°ú Å×À̺íÀ» ±¸ÃàÇÑ´Ù°í °¡Á¤ÇÕ´Ï´Ù. Ä¿¼´Â ÀÌ Å×À̺íÀÇ ÇöÀç ÇàÀ» °¡¸®Å°°Å³ª ½Äº°ÇÔÀ¸·Î½á ÀÀ¿ëÇÁ·Î±×·¥ÀÌ »ç¿ëÇÒ ¼ö ÀÖ´Â °á°ú Å×À̺í·ÎºÎÅÍ ÇàÀ» ÀÛ¼ºÇÕ´Ï´Ù. Ä¿¼°¡ »ç¿ëµÉ ¶§ ÀÀ¿ëÇÁ·Î±×·¥Àº µ¥ÀÌÅÍ Á¶°ÇÀÇ ³¡, Áï NOT FOUND Á¶°Ç, SQLCODE +100(SQLSTATE 02000)¿¡ µµ´ÞÇÒ ¶§±îÁö °á°ú Å×À̺í·ÎºÎÅÍ ¼øÂ÷ÀûÀ¸·Î °¢ ÇàÀ» °Ë»öÇÒ ¼ö ÀÖ½À´Ï´Ù. SELECT¹® ½ÇÇà °á°ú ȹµæµÈ Çà ¼¼Æ®µéÀº °Ë»ö Á¶°ÇÀ» ÃæÁ·½ÃŰ´Â Çà ¼ö¿¡ µû¶ó 0°³, 1°³ ¶Ç´Â ±× ÀÌ»óÀÇ Çàµé·Î ±¸¼ºµÉ ¼ö ÀÖ½À´Ï´Ù.
Ä¿¼ ó¸® ´Ü°è´Â ´ÙÀ½°ú °°½À´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥Àº µ¿½Ã¿¡ ¿©·¯ Ä¿¼¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. Ä¿¼¸¶´Ù °íÀ¯ÀÇ DECLARE CURSOR, OPEN, CLOSE ¹× FETCH¹® ¼¼Æ®°¡ ÇÊ¿äÇÕ´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Çà ¼¼Æ®¸¦ ¼±ÅÃÇϰí, Ä¿¼¸¦ »ç¿ëÇÏ¿© ÇÑ ¹ø¿¡ ÇÑ Çà ¼³Á¤À» ó¸®ÇÏ´Â ¹æ¹ýÀÇ ¿¹´Â ¿¹: Ä¿¼ ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
DECLARE CURSOR¹®Àº SELECT¹®À» »ç¿ëÇÏ¿© °Ë»öÇÒ Çà ¼¼Æ®¸¦ ½Äº°ÇÏ¿© Ä¿¼¸¦ Á¤ÀÇ ¹× ¸í¸íÇÕ´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥Àº Ä¿¼¿¡ À̸§À» ÁöÁ¤ÇÕ´Ï´Ù. ÀÌ À̸§Àº ÈÄ¼Ó OPEN¹®, FETCH¹® ¹× CLOSE¹®¿¡¼ ÂüÁ¶µË´Ï´Ù. Á¶È¸´Â À¯È¿ÇÑ ¼±Åà ¸í·É¹®ÀÔ´Ï´Ù.
Ä¿¼¹® Á¤ÀÇ¿¡´Â Á¤Àû SELECT¹®°ú ¿¬°üµÈ DECLARE¹®ÀÌ ÀÖ½À´Ï´Ù.
EXEC SQL DECLARE C1 CURSOR FOR SELECT PNAME, DEPT FROM STAFF WHERE JOB=:host_var;
#sql iterator cursor1(host_var data type); #sql cursor1 = { SELECT PNAME, DEPT FROM STAFF WHERE JOB=:host_var };
EXEC SQL DECLARE C1 CURSOR FOR SELECT NAME, DEPT FROM STAFF WHERE JOB=:host-var END-EXEC.
EXEC SQL DECLARE C1 CURSOR FOR + SELECT NAME, DEPT FROM STAFF + WHERE JOB=:host_var
ÁÖ: | DECLARE¹®ÀÇ À§Ä¡´Â ÀÓÀÇÀûÀÌÁö¸¸, ÃÖÃÊÀÇ Ä¿¼ »ç¿ë À§¿¡ À§Ä¡ÇØ¾ß ÇÕ´Ï´Ù. |
COMMIT ¶Ç´Â ROLLBACK Á¶ÀÛÀÇ Á¶Ä¡´Â Ä¿¼°¡ ¼±¾ðµÇ´Â ¹æ½Ä¿¡ µû¶ó Ä¿¼¸¶´Ù ´Ù¸¨´Ï´Ù.
Ä¿¼°¡ Àбâ Àü¿ëÀÌ°í ¹Ýº¹ Àбâ(RR) ºÐ¸® ·¹º§À» »ç¿ëÇÏ´Â °æ¿ì, ÀÛ¾÷ ´ÜÀ§(UOW)¿¡ ÇÊ¿äÇÑ ½Ã½ºÅÛ Å×ÀÌºí¿¡¼ ¹Ýº¹ Àбâ Àá±ÝÀÌ ¿©ÀüÈ÷ ¼öÁý ¹× À¯Áöº¸¼öµË´Ï´Ù. µû¶ó¼ ÀÀ¿ëÇÁ·Î±×·¥Àº Àбâ Àü¿ë Ä¿¼¿¡ ´ëÇØ¼µµ COMMIT¹®À» Á¤±âÀûÀ¸·Î ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥ÀÌ 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¹®À» ¹ßÇàÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
EXEC SQL DECLARE EMPLUPDT CURSOR WITH HOLD FOR SELECT EMPNO, LASTNAME, PHONENO, JOBCODE, SALARY FROM TEMPL FOR UPDATE OF SALARY
EXEC SQL OPEN EMPLUPDT . . . EXEC SQL FETCH EMPLUPDT INTO :upd_emp, :upd_lname, :upd_tele, :upd_jobcd, :upd_wage,
EXEC SQL UPDATE TEMPL SET SALARY = :newsalary WHERE CURRENT OF EMPLUPDT
ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ´ÙÀ½°ú °°Àº °æ¿ì FETCH¹® ¶Ç´Â CLOSE¹®¿¡¼ ¸®Å쵃 ¼ö ÀÖ´Â SQLCODE -501(SQLSTATE 24501)À» °ËÃâ ¹× Ã³¸®ÇÏ·Á¸é »ç¿ëÀÚ ÀÀ¿ëÇÁ·Î±×·¥¿¡ Äڵ带 Æ÷ÇÔ½ÃÄÑ¾ß ÇÕ´Ï´Ù.
Á¾¼ÓµÈ Å×À̺íÀ» Á¦°ÅÇÔÀ¸·Î½á ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ÆÐŰÁö¸¦ ¹«È¿ÈÇÒ °æ¿ì ÆÐŰÁö´Â µ¿ÀûÀ¸·Î ¸®¹ÙÀεåµË´Ï´Ù. ÀÌ·± °æ¿ì, µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ Ä¿¼¸¦ ´ÝÀ¸¹Ç·Î, FETCH¹® ¶Ç´Â CLOSE¹®¿¡ ´ëÇØ SQLCODE -501(SQLSTATE 24501)ÀÌ ¸®Åϵ˴ϴÙ. ÀÌ·± »óȲ¿¡¼ SQLCODE -501(SQLSTATE 24501)À» ó¸®ÇÏ´Â ¹æ¹ýÀº Ä¿¼·ÎºÎÅÍ ÇàÀ» ÆäÄ¡ÇÒ °ÍÀÎÁö¿¡ µû¶ó ´Þ¶óÁý´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥ÀÌ WITH RELEASE ¿É¼ÇÀ» »ç¿ëÇÏ¿© Ä¿¼¸¦ ´ÝÀ» ¶§ DB2´Â Ä¿¼°¡ º¸À¯Çϰí ÀÖ´Â ¸ðµç READ Àá±ÝÀ» ÇØÁ¦ÇÏ·Á ÇÕ´Ï´Ù. Ä¿¼´Â °è¼ÓÇØ¼ WRITE Àá±Ý¸¸ º¸À¯ÇÕ´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ÀÌ RELEASE ¿É¼ÇÀ» »ç¿ëÇÏÁö ¾Ê°í Ä¿¼¸¦ ´ÝÀ¸¸é, ÀÛ¾÷ ´ÜÀ§(UOW)°¡ ¿Ï·áµÉ ¶§ READ ¹× WRITE Àá±ÝÀÌ ÇØÁ¦µË´Ï´Ù.
ÀÌ »ùÇà ÇÁ·Î±×·¥Àº Ä¿¼¸¦ Á¤ÀÇ ¹× »ç¿ëÇÏ´Â SQL¹®À» º¸¿© ÁÝ´Ï´Ù. Ä¿¼´Â Á¤Àû SQLÀ» »ç¿ëÇÏ¿© 󸮵˴ϴÙ. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:
REXX°¡ Á¤Àû SQLÀ» Áö¿øÇÏÁö ¾ÊÀ¸¹Ç·Î »ùÇÃÀº Á¦°øµÇÁö ¾Ê½À´Ï´Ù. Ä¿¼¸¦ µ¿ÀûÀ¸·Î ó¸®ÇÏ´Â REXX ¿¹¿¡ ´ëÇØ¼´Â ¿¹: µ¿Àû SQL ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
CHECKERR ¸ÅÅ©·Î/ÇÔ¼ö´Â ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÔ´Ï´Ù. ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼ÀÇ À§Ä¡´Â »ç¿ëµÈ ÇÁ·Î±×·¡¹Ö ¾ð¾î¿¡ µû¶ó ´Þ¶óÁú ¼ö ÀÖ½À´Ï´Ù.
ÀÌ ¿À·ù üũ À¯Æ¿¸®Æ¼¿¡ ´ëÇÑ ¼Ò½º Äڵ忡 ´ëÇØ¼´Â ¿¹Á¦ ÇÁ·Î±×·¥¿¡¼ GET ERROR MESSAGE »ç¿ëÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
#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 */
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); } } }
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.