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

µ¿Àû SQL·ÎÀÇ º¯¼ö ÀÔ·Â

ÀÌ Àý¿¡¼­´Â »ç¿ëÀÚÀÇ µ¿Àû SQL ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ »ç¿ëÇÏ¿© È£½ºÆ® º¯¼ö Á¤º¸¸¦ Ç¥½ÃÇÏ´Â ¹æ¹ýÀ» º¸¿© ÁÝ´Ï´Ù. ´ÙÀ½ Ç׸ñµéÀÌ Æ÷ÇԵ˴ϴÙ.

¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ »ç¿ë

È£½ºÆ® º¯¼ö Á¤º¸(µ¥ÀÌÅÍ À¯Çü ¹× ±æÀÌ)´Â ÀÀ¿ëÇÁ·Î±×·¥ »çÀü ó¸® ÄÄÆÄÀÏ Áß¿¡¸¸ »ç¿ë°¡´ÉÇϹǷΠµ¿Àû SQL¹®¿¡´Â È£½ºÆ® º¯¼ö°¡ Æ÷Ç﵃ ¼ö ¾ø½À´Ï´Ù. ½ÇÇà½Ã È£½ºÆ® º¯¼ö Á¤º¸´Â ¾ø½À´Ï´Ù. µû¶ó¼­, ÀÀ¿ëÇÁ·Î±×·¥ º¯¼ö¸¦ Ç¥½ÃÇÏ·Á¸é »õ·Î¿î ¹æ¹ýÀÌ ÇÊ¿äÇÕ´Ï´Ù. È£½ºÆ® º¯¼ö´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¶ó°í ÇÏ´Â Àǹ®ºÎÈ£(?)¿¡ ÀÇÇØ Ç¥½ÃµË´Ï´Ù. ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡´Â SQL¹® ³»ºÎ¿¡ È£½ºÆ® º¯¼ö°¡ À§Ä¡ÇÏ´Â Àå¼Ò¸¦ ³ªÅ¸³À´Ï´Ù. ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â SQL¹® ³»ºÎ¿¡¼­ÀÇ »ç¿ë ¹®¸Æ¿¡ Á¾¼ÓÀûÀÎ µ¥ÀÌÅÍ À¯Çü ¹× ±æÀ̸¦ ÃëÇÕ´Ï´Ù.

¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚÀÇ µ¥ÀÌÅÍ À¯ÇüÀÌ »ç¿ëµÇ´Â ¸í·É¹® ¹®¸Æ°ú ±¸ºÐÀÌ µÇÁö ¾Ê´Â °æ¿ì CAST¸¦ »ç¿ëÇÏ¿© À¯ÇüÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×·± ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ ÀÔ·ÂµÈ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ·Î ¿©°ÜÁý´Ï´Ù. ÀÔ·ÂµÈ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â Á¦°øµÈ À¯ÇüÀÇ È£½ºÆ® º¯¼öó·³ Ãë±ÞµË´Ï´Ù. ¿¹¸¦ µé¾î, SELECT ? FROM SYSCAT.TABLES¹®Àº DB2°¡ °á°ú Ä÷³ÀÇ À¯ÇüÀ» ¾ËÁö ¸øÇϹǷΠÀ¯È¿ÇÏÁö ¾Ê½À´Ï´Ù. ±×·¯³ª, À¯Çüº¯È¯ ÇÔ¼ö´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ INTEGER¸¦ Ç¥½ÃÇϵµ·Ï ¾à¼ÓÇϹǷΠSELECT CAST(? AS INTEGER) FROM SYSCAT.TABLES¹®ÀÌ À¯È¿ÇÕ´Ï´Ù. µû¶ó¼­ DB2´Â °á°ú Ä÷³ÀÇ À¯ÇüÀ» ¾Ð´Ï´Ù.

¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ Æ÷ÇÔµÈ ¹®ÀÚ¿­Àº ´ÙÀ½°ú °°½À´Ï´Ù.

     DELETE FROM TEMPL WHERE EMPNO = ?

ÀÌ ¸í·É¹®ÀÌ ½ÇÇàµÉ ¶§ È£½ºÆ® º¯¼ö ¶Ç´Â SQLDA ±¸Á¶´Â EXECUTE¹®ÀÇ USINGÀý¿¡ ÀÇÇØ ÁöÁ¤µË´Ï´Ù. È£½ºÆ® º¯¼öÀÇ ³»¿ëÀº ¸í·É¹®ÀÌ ½ÇÇàµÉ ¶§ »ç¿ëµË´Ï´Ù.

SQL¹®¿¡ Çϳª ÀÌ»óÀÇ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ Æ÷Ç﵃ ¼ö ÀÖÀ¸¸é, EXECUTE¹®ÀÇ USINGÀýÀº (°¢ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ ´ëÇØ) È£½ºÆ® º¯¼ö ¸ñ·ÏÀ» ÁöÁ¤Çϰųª °¢ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ ´ëÇØ SQLVAR Ç׸ñÀ» °¡Áø SQLDA¸¦ ½Äº°ÇØ¾ß ÇÕ´Ï´Ù.(LOBÀÇ °æ¿ì ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´ç µÎ °³ÀÇ SQLVARÀÌ ÀÖ½À´Ï´Ù.) È£½ºÆ® º¯¼ö ¸ñ·Ï ¶Ç´Â SQLVAR Ç׸ñÀº ¸í·É¹®ÀÇ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ ¼ø¼­¿¡ ÀÏÄ¡Çϸç, ȣȯ°¡´ÉÇÑ µ¥ÀÌÅÍ À¯ÇüÀÌ ÀÖ¾î¾ß ÇÕ´Ï´Ù.

µ¿Àû SQL¿¡¼­ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ »ç¿ëÇÏ´Â °ÍÀº Á¤Àû SQL¿¡¼­ È£½ºÆ® º¯¼ö¸¦ »ç¿ëÇÏ´Â °Í°ú °°½À´Ï´Ù. ¾î¶² °æ¿ìÀ̵ç, ÃÖÀûÈ­ ¾Ë°í¸®ÁòÀº ºÐ»ê Åë°è¸¦ »ç¿ëÇÏÁö ¾Ê°í ÃÖÀû ¾×¼¼½º Ç÷£À» ¼±ÅÃÇÏÁö ¾ÊÀ» ¼öµµ ÀÖ½À´Ï´Ù.

¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ Àû¿ëµÇ´Â ±ÔÄ¢Àº SQL ÂüÁ¶¼­ÀÇ PREPARE¹® ¾Æ·¡¿¡ ³ª¿­µË´Ï´Ù.

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

´ÙÀ½Àº °Ë»ö ¹× °»½Å Á¶°Ç¿¡¼­ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ »ç¿ëÇÏ´Â UPDATE ¿¹ÀÔ´Ï´Ù. »ùÇÃÀº ´ÙÀ½°ú °°Àº ÇÁ·Î±×·¡¹Ö ¾ð¾î·Î »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù:

C
varinp.sqc

Java
Varinp.java

COBOL
varinp.sqb

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

  1. SELECT SQL¹® Áغñ PREPARE¹®ÀÌ SQL¹®À» µ¿ÀûÀ¸·Î ÁغñÇϱâ À§ÇØ È£ÃâµË´Ï´Ù. ÀÌ SQL¹®¿¡¼­ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â ?¿¡ ÀÇÇØ Ç¥½ÃµË´Ï´Ù. staffÀÇ job Çʵå´Â °á°ú Å×ÀÌºí¿¡ ÁöÁ¤µÇÁö ¾Ê´õ¶óµµ °»½ÅÀÌ °¡´ÉÇϵµ·Ï Á¤Àǵ˴ϴÙ.
  2. Ä¿¼­ ¼±¾ð. DECLARE CURSOR¹®Àº c1 Ä¿¼­¸¦ (1)¿¡ ÁغñµÈ Á¶È¸¿¡ ¿¬°á½Ãŵ´Ï´Ù.
  3. Ä¿¼­ ¿­±â. Ä¿¼­ c1ÀÌ ¿­¸³´Ï´Ù. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥ÀÌ °á°ú Å×À̺íÀ» Á¶È¸ÇÏ°í ºôµåÇϵµ·Ï ÇÕ´Ï´Ù. Ä¿¼­´Â ù¹øÂ° Çà ¾Õ¿¡ À§Ä¡ÇÏ°Ô µË´Ï´Ù.
  4. UPDATE SQL¹® Áغñ PREPARE¹®ÀÌ SQL¹®À» µ¿ÀûÀ¸·Î ÁغñÇϱâ À§ÇØ È£ÃâµË´Ï´Ù. ÀÌ ¸í·É¹®ÀÇ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ´Â Clerk·Î ¼³Á¤µÇÁö¸¸, °»½Å ÁßÀÎ Ä÷³ µ¥ÀÌÅÍ À¯Çü¿¡ ÀÏÄ¡ÇÏ´Â ÇÑ ¾î¶² °ÍÀ¸·Îµç µ¿ÀûÀ¸·Î º¯°æµÉ ¼ö ÀÖ½À´Ï´Ù.
  5. Çà °Ë»ö. FETCH¹®Àº Ä¿¼­¸¦ ´ÙÀ½ Çà¿¡ À§Ä¡½Ã۰í ÇàÀÇ ³»¿ëÀ» È£½ºÆ® º¯¼ö·Î À̵¿½Ãŵ´Ï´Ù. ÀÌ ÇàÀº ÇöÀç ÇàÀÌ µË´Ï´Ù.
  6. ÇöÀç Çà °»½Å. ÇöÀç Çà°ú ÁöÁ¤µÈ Ä÷³ jobÀÌ Àü´ÞµÈ ¸Å°³º¯¼ö parm_varÀÇ ³»¿ë°ú ÇÔ²² °»½ÅµË´Ï´Ù.
  7. Ä¿¼­ ´Ý±â. 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¶ó°í ÇÏ´Â ¿ÜºÎ ÇÁ·Î±×·¥ÀÔ´Ï´Ù.

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

C ¿¹: VARINP.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];
      char st[255];
      char parm_var[6];
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: VARINP \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: varinp [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   strcpy (st, "SELECT name, dept FROM staff ");
   strcat (st, "WHERE job = ? FOR UPDATE OF job");
   EXEC SQL PREPARE s1 FROM :st; (1)
   EMB_SQL_CHECK("PREPARE");
 
   EXEC SQL DECLARE c1 CURSOR FOR s1; (2)
 
   strcpy (parm_var, "Mgr");
   EXEC SQL OPEN c1 USING :parm_var; (3)
   EMB_SQL_CHECK("OPEN");
 
   strcpy (parm_var, "Clerk");
   strcpy (st, "UPDATE staff SET job = ? WHERE CURRENT OF c1");
   EXEC SQL PREPARE s2 from :st; (4)
 
   do
   {
      EXEC SQL FETCH c1 INTO :pname, :dept; (5)
      if (SQLCODE != 0) break;
 
      printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
              pname, dept );
      EXEC SQL EXECUTE s2 USING :parm_var; (6)
      EMB_SQL_CHECK("EXECUTE");
   } while ( 1 );
 
   EXEC SQL CLOSE c1;  (7)
   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 : VARINP.SQC */

Java ¿¹: Varinp.java

import java.sql.*;
 
class Varinp
{   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 Varinp Sample");
      // Connect to Sample database
 
      Connection con = null;
      // URL is jdbc:db2:dbname
      String url = "jdbc:db2:sample";
 
      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 Varinp [username password]\n");
      } 
 
      // Enable transactions
      con.setAutoCommit(false);
 
      // Perform dynamic SQL using JDBC
      try
      {   PreparedStatement pstmt1 = con.prepareStatement(
          "SELECT name, dept FROM staff WHERE job = ? FOR UPDATE OF job"); (1)
      // set cursor name for the positioned update statement
      pstmt1.setCursorName("c1");  (2)
      pstmt1.setString(1, "Mgr");
      ResultSet rs = pstmt1.executeQuery();  (3)
 
      PreparedStatement pstmt2 = con.prepareStatement(
          "UPDATE staff SET job = ? WHERE CURRENT OF c1");  (4)
      pstmt2.setString(1, "Clerk");
 
      System.out.print("\n");
      while( rs.next() )  (5)
      {   String name = rs.getString("name");
        short  dept = rs.getShort("dept");
        System.out.println(name + " in dept. " + dept
          + " will be demoted to Clerk");
 
        pstmt2.executeUpdate();  (6)
      };
 
      rs.close();
      pstmt1.close();  (7)
      pstmt2.close();
      }
      catch( Exception e )
      {   throw e;
      } 
      finally
      {   // Rollback the transaction
        System.out.println("\nRollback the transaction...");
        con.rollback();
        System.out.println("Rollback done.");
      }
    } 
    catch( Exception e )
    {   System.out.println(e);
    } 
  }
}

COBOL ¿¹: VARINP.SQB

       Identification Division.
       Program-ID. "varinp".
 
       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 st                pic x(127).
       01 parm-var          pic x(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: VARINP".
 
      * 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.
 
           move "SELECT name, dept FROM staff
      -         "   WHERE job = ? FOR UPDATE OF job" to st.
           EXEC SQL PREPARE s1 FROM :st END-EXEC.  (1)
           move "PREPARE" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC.   (2)
 
           move "Mgr" to parm-var.
 
           EXEC SQL OPEN c1 USING :parm-var END-EXEC   (3)
           move "OPEN" to errloc.
           call "checkerr" using SQLCA errloc.
 
           move "Clerk" to parm-var.
           move "UPDATE staff SET job = ? WHERE CURRENT OF c1" to st.
 
           EXEC SQL PREPARE s2 from :st END-EXEC.   (4)
           move "PREPARE S2" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * call the FETCH and UPDATE loop.
           perform Fetch-Loop thru End-Fetch-Loop
              until SQLCODE not equal 0.
 
           EXEC SQL CLOSE c1 END-EXEC.    (7)
           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.   (5)
           if SQLCODE not equal 0
              go to End-Fetch-Loop.
           display pname, " in dept. ", dept,
              " will be demoted to Clerk".
 
           EXEC SQL EXECUTE s2 USING :parm-var END-EXEC.   (6)
           move "EXECUTE" to errloc.
           call "checkerr" using SQLCA errloc.
 
       End-Fetch-Loop. exit.
 
       End-Prog.
           stop run.


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