Ä¿¼°¡ ÂüÁ¶ÇÏ´Â ÇàÀ» °»½Å ¹× »èÁ¦ÇÏ´Â °ÍÀÌ °¡´ÉÇÕ´Ï´Ù. ÇàÀ» °»½Å °¡´ÉÇϵµ·Ï Çϱâ À§ÇØ Ä¿¼¿¡ ÇØ´çµÇ´Â Á¶È¸´Â Àбâ Àü¿ëÀ̾ ¾ÈµË´Ï´Ù. Á¶È¸¸¦ °»½Å °¡´ÉÇϰųª »èÁ¦°¡´ÉÇϵµ·Ï ÇÏ´Â °Í¿¡ ´ëÇÑ ¼³¸íÀº SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
Ä¿¼·Î °»½ÅÇÏ·Á¸é UPDATE¹®¿¡¼ WHERE CURRENT OFÀýÀ» »ç¿ëÇϽʽÿÀ. °á°ú Å×À̺íÀÇ ÀϺΠÄ÷³À» °»½ÅÇÒ ½Ã½ºÅÛÀ» ¾Ë¾Æº¸·Á¸é FOR UPDATEÀýÀ» »ç¿ëÇϽʽÿÀ. fullselect¿¡ ÀÖÁö ¾ÊÀº ä FOR UPDATE¿¡ Ä÷³À» ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. µû¶ó¼, Ä¿¼¿¡ ÀÇÇØ ¸í½ÃÀûÀ¸·Î °Ë»öµÇÁö ¾Ê´Â Ä÷³À» °»½ÅÇÒ ¼ö ÀÖ½À´Ï´Ù. FOR UPDATEÀýÀÌ Ä÷³ À̸§ ¾øÀÌ ÁöÁ¤µô °æ¿ì, ¿ÜºÎ fullselectÀÇ Ã¹¹øÂ° FROMÀý¿¡¼ ½Äº°µÇ´Â ºä ¶Ç´Â Å×À̺íÀÇ ¸ðµç Ä÷³µéÀÌ °»½Å °¡´ÉÇÏ´Ù°í °£Áֵ˴ϴÙ. FOR UPDATEÀý¿¡ ÇÊ¿äÇÑ °Íº¸´Ù ¸¹Àº Ä÷³À» ¸í¸íÇÏÁö ¸¶½Ê½Ã¿À. FOR UPDATEÀý¿¡¼ Ãß°¡ Ä÷³À» ¸í¸íÇϸé DB2°¡ µ¥ÀÌÅÍ¿¡ ¾×¼¼½ºÇÒ ¶§ È¿À²¼ºÀÌ ¶³¾îÁö´Â °æ¿ì°¡ ÀÖ½À´Ï´Ù.
Ä¿¼·Î »èÁ¦ÇÏ´Â °ÍÀº DELETE¹®¿¡¼ WHERE CURRENT OFÀýÀ» »ç¿ëÇÏ¿© ¼öÇàµË´Ï´Ù. ÀϹÝÀûÀ¸·Î FOR UPDATEÀýÀº Ä¿¼ÀÇ ÇöÀç Çà »èÁ¦¿¡ ÇÊ¿äÇÏÁö ¾Ê½À´Ï´Ù. À¯ÀÏÇÑ ¿¹¿Ü´Â, LANGLEVELÀÌ SAA1À¸·Î ¼³Á¤µÇ¾ú°í BLOCKING ALL·Î ¹ÙÀεåµÈ ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ SELECT¹®À̳ª DELETE¹®¿¡ ´ëÇØ µ¿Àû SQLÀ» »ç¿ëÇÒ ¶§(µ¿Àû SQL¿¡ ´ëÇØ¼´Â µ¿Àû SQL ÇÁ·Î±×·¥ ÀÛ¼º ÂüÁ¶) ¹ß»ýÇÕ´Ï´Ù. ÀÌ·± °æ¿ì FOR UPDATEÀýÀº SELECT¹®¿¡¼ ÇʼöÀûÀÔ´Ï´Ù. »çÀü ó¸® ÄÄÆÄÀÏ·¯ ¿É¼Ç¿¡ ´ëÇØ¼´Â Command ReferenceÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
DELETE¹®Àº »èÁ¦ÇÒ Ä¿¼¿¡ ÀÇÇØ ÂüÁ¶µÇ´Â ÇàÀ» »èÁ¦ÇÕ´Ï´Ù. ±× °á°ú Ä¿¼´Â ´ÙÀ½ Çà ¾Õ¿¡ ±×´ë·Î À§Ä¡Çϸç, FETCH¹®Àº Ãß°¡ WHERE CURRENT OF Á¶ÀÛÀÌ Ä¿¼¿¡ ´ëÇØ ¼öÇàµÇ±â Àü¿¡ ½ÇÇàµÇ¾î¾ß ÇÕ´Ï´Ù.
Ä¿¼´Â ´ÙÀ½ ¼¼ ¹üÁÖ·Î ³ª´¹´Ï´Ù.
Àбâ Àü¿ë Ä¿¼¿¡´Â ¼º´É»óÀÇ ÀÌÁ¡ÀÌ ÀÖÀ» ¼ö ÀÖ½À´Ï´Ù. Àбâ Àü¿ë Ä¿¼¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº °ü¸® ¾È³»¼: ±¸ÇöÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ºÒ¸íÈ®ÇÑ Ä¿¼´Â »çÀü ó¸® ÄÄÆÄÀÏ ¶Ç´Â ¹ÙÀεùµÉ ¶§ BLOCKING ALL ¿É¼ÇÀÌ ÁöÁ¤µÇ´Â °æ¿ì¿¡¸¸ Àбâ Àü¿ëÀ¸·Î Ãë±ÞµË´Ï´Ù. ±×·¸Áö ¾ÊÀ¸¸é °»½Å °¡´ÉÀ¸·Î °£Áֵ˴ϴÙ.
ÁÖ: | µ¿ÀûÀ¸·Î 󸮵Ǵ Ŀ¼´Â Ç×»ó ¸íÈ®ÇÕ´Ï´Ù. |
Ä¿¼°¡ Àбâ Àü¿ë, °»½Å°¡´É ¶Ç´Â ¸íÈ® Áß¿¡¼ ¾î´À °ÍÀÎÁö °áÁ¤ÇÏ´Â µ¥ »ç¿ëµÇ´Â ¿Ïº®ÇÑ ±âÁØ ¸ñ·ÏÀº SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ÀÌ ¿¹´Â Ä¿¼¸¦ »ç¿ëÇÏ¿© Å×À̺í·ÎºÎÅÍ Ä¿¼¸¦ ¼±ÅÃÇÏ¿© ¿°í, Å×À̺í·ÎºÎÅÍ ÇàÀ» ÆäÄ¡ÇÕ´Ï´Ù. ÆäÄ¡µÇ´Â °¢ Çà¿¡ ´ëÇØ¼ (°£´ÜÇÑ ±âÁØ¿¡ ÀÇÇØ) ÇàÀ» »èÁ¦ÇÒ °ÍÀÎÁö ¶Ç´Â °»½ÅÇÒ °ÍÀÎÁö °áÁ¤ÇÕ´Ï´Ù. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:
REXX ¾ð¾î´Â Á¤Àû SQLÀ» Áö¿øÇÏÁö ¾ÊÀ¸¹Ç·Î »ùÇÃÀº Á¦°øµÇÁö ¾Ê½À´Ï´Ù.
UPDATE°¡ ¼öÇàµÇ¸é UPDATE¹®ÀÌ ÇöÀç ÇàÀÇ À§Ä¡¸¦ º¯°æ½ÃŰÁö ¾ÊÀ¸¹Ç·Î, Ä¿¼´Â ÀÌ Çà¿¡ ³²¾Æ ÀÖ½À´Ï´Ù.
DELETE¹®ÀÌ ¼öÇàµÇ¸é, ÇöÀç ÇàÀÌ »èÁ¦µÇ¹Ç·Î ´Ù¸¥ »óȲÀÌ ¹ß»ýÇÕ´Ï´Ù. ÀÌ´Â ´ÙÀ½ Çà ¾Õ¿¡ À§Ä¡ÇÏ´Â °Í°ú °°À¸¸ç, FETCH¹®Àº Ãß°¡ WHERE CURRENT OF Á¶ÀÛÀÌ ¼öÇàµÇ±â Àü¿¡ ¹ßÇàµÇ¾î¾ß ÇÕ´Ï´Ù.
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: OPENFTCH\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: openftch [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; if (dept > 40) { printf( "%-10.10s in dept. %2d will be demoted to Clerk\n", pname, dept ); EXEC SQL UPDATE staff SET job = 'Clerk' (4) WHERE CURRENT OF c1; EMB_SQL_CHECK("UPDATE STAFF"); } else { printf ("%-10.10s in dept. %2d will be DELETED!\n", pname, dept); EXEC SQL DELETE FROM staff WHERE CURRENT OF c1; EMB_SQL_CHECK("DELETE"); } /* endif */ } while ( 1 ); EXEC SQL CLOSE c1; (5) 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 : OPENFTCH.SQC */
OpF_Curs.sqlj
// PURPOSE : This file, named OpF_Curs.sqlj, contains the definition // of the class OpF_Curs used in the sample program Openftch. import sqlj.runtime.ForUpdate; #sql public iterator OpF_Curs implements ForUpdate (String, short);
Openftch.sqlj
import java.sql.*; import sqlj.runtime.*; import sqlj.runtime.ref.*; class Openftch { 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 Openftch 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 Openftch [username password]\n"); } // if - else if - else // Set the default context DefaultContext ctx = new DefaultContext(con); DefaultContext.setDefaultContext(ctx); // Enable transactions con.setAutoCommit(false); // Executing SQLJ positioned update/delete statements. try { OpF_Curs forUpdateCursor; String name = null; short dept=0; #sql forUpdateCursor = { SELECT name, dept FROM staff WHERE job='Mgr' }; // #sql (1)(2) while (true) { #sql { FETCH :forUpdateCursor INTO :name, :dept }; // #sql (3) if (forUpdateCursor.endFetch()) break; if (dept > 40) { System.out.println ( name + " in dept. " + dept + " will be demoted to Clerk"); #sql { UPDATE staff SET job = 'Clerk' WHERE CURRENT OF :forUpdateCursor }; // #sql (4) } else { System.out.println ( name + " in dept. " + dept + " will be DELETED!"); #sql { DELETE FROM staff WHERE CURRENT OF :forUpdateCursor }; // #sql } // if - else } forUpdateCursor.close(); (5) } catch( Exception e ) { throw e; } finally { // Rollback the transaction System.out.println("\nRollback the transaction..."); #sql { ROLLBACK }; System.out.println("Rollback done."); } // try - catch - finally } catch( Exception e ) { System.out.println (e); } // try - catch } // main } // class Openftch
Identification Division. Program-ID. "openftch". Data Division. Working-Storage Section. copy "sqlca.cbl". EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 pname pic x(10). 01 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: OPENFTCH". * Get database connection information. 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" to errloc. call "checkerr" using SQLCA errloc. * call the FETCH and UPDATE/DELETE loop. perform Fetch-Loop thru End-Fetch-Loop until SQLCODE not equal 0. EXEC SQL CLOSE c1 END-EXEC. (5) move "CLOSE" 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. if dept greater than 40 go to Update-Staff. Delete-Staff. display pname, " in dept. ", dept, " will be DELETED!". EXEC SQL DELETE FROM staff WHERE CURRENT OF c1 END-EXEC. move "DELETE" to errloc. call "checkerr" using SQLCA errloc. go to End-Fetch-Loop. Update-Staff. display pname, " in dept. ", dept, " will be demoted to Clerk". EXEC SQL UPDATE staff SET job = 'Clerk' (4) WHERE CURRENT OF c1 END-EXEC. move "UPDATE" to errloc. call "checkerr" using SQLCA errloc. End-Fetch-Loop. exit. End-Prog. stop run.