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


SQL ÇÁ·Î½Ãµà¾î¿¡¼­ µ¿Àû SQL »ç¿ë

¿ÜºÎ ÀúÀå ÇÁ·Î½Ãµà¾î¿Í °°ÀÌ SQL ÇÁ·Î½Ãµà¾î´Â µ¿Àû SQL¹®À» ½ÇÇàÇÒ ¼ö ÀÖ½À´Ï´Ù. µ¿Àû SQL¹®¿¡ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ ¾ø°í À̸¦ Çѹø¸¸ ½ÇÇàÇÒ °èȹÀ̶ó¸é EXECUTE IMMEDIATE¹®À» »ç¿ëÇϽʽÿÀ.

µ¿Àû SQL¹®¿¡ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ ÀÖÀ¸¸é PREPARE¿Í EXECUTE¹®À» »ç¿ëÇØ¾ß ÇÕ´Ï´Ù. µ¿Àû SQL¹®À» ¿©·¯ ¹ø ½ÇÇàÇÒ °èȹÀ̶ó¸é ´ÜÀÏ PREPARE¹®À» ½ÇÇàÇϰí EXECUTE¹®À» ¿©·¯ ¹ø ½ÇÇàÇÏ´Â °ÍÀÌ ¸Å¹ø EXECUTE IMMEDIATE¹®À» ½ÇÇàÇÏ´Â °Íº¸´Ù ´õ È¿À²ÀûÀÏ °ÍÀÔ´Ï´Ù. PREPARE¿Í EXECUTE¹®À» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¿¡¼­ µ¿Àû SQLÀ» ½ÇÇàÇÏ·Á¸é ´ÙÀ½ ¸í·É¹®À» SQL ÇÁ·Î½Ãµà¾î º»¹®¿¡ Æ÷ÇÔ½ÃÄÑ¾ß ÇÕ´Ï´Ù.

´Ü°è  1.

DECLARE¹®À» »ç¿ëÇÏ¿© µ¿Àû SQLÀ» º¸°üÇÒ ¸¸Å­ Å« À¯Çü VARCHARÀÇ º¯¼ö¸¦ ¼±¾ðÇϽʽÿÀ.

´Ü°è  2.

SET¹®À» »ç¿ëÇÏ¿© ¸í·É¹® ¹®ÀÚ¿­À» º¯¼ö¿¡ ÇÒ´çÇϽʽÿÀ. ¸í·É¹® ¹®ÀÚ¿­¿¡ Á÷Á¢ º¯¼ö¸¦ Æ÷ÇÔ½Ãų ¼ö ¾ø½À´Ï´Ù. ´ë½Å ¹°À½Ç¥('?') ±âÈ£¸¦ ¸í·É¹®¿¡¼­ »ç¿ëµÈ ¸ðµç º¯¼öÀÇ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ·Î »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.

´Ü°è  3.

PREPARE¹®À» »ç¿ëÇÏ¿© ¸í·É¹® ¹®ÀÚ¿­¿¡¼­ ÁغñµÈ ¸í·É¹®À» ÀÛ¼ºÇÕ´Ï´Ù.

´Ü°è  4.

EXECUTE¹®À» »ç¿ëÇÏ¿© ÁغñµÈ ¸í·É¹®À» ½ÇÇàÇÕ´Ï´Ù. ¸í·É¹® ¹®ÀÚ¿­¿¡ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ µé¾î ÀÖÀ¸¸é USINGÀýÀ» »ç¿ëÇÏ¿© À̸¦ º¯¼ö °ªÀ¸·Î ¹Ù²Ù½Ê½Ã¿À.

ÁÖ:SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ PREPARE¹®¿¡ Á¤ÀÇµÈ ¸í·É¹® À̸§Àº ¹üÀ§°¡ Á¤ÇØÁø º¯¼ö·Î 󸮵˴ϴÙ. ÀÏ´Ü SQL ÇÁ·Î½Ãµà¾î°¡ ¸í·É¹® À̸§À» Á¤ÀÇÇÒ ¼ö ÀÖ´Â ¹üÀ§¿¡ Á¸ÀçÇϸé DB2´Â ´õ ÀÌ»ó ¸í·É¹® À̸§À» ¾×¼¼½ºÇÏÁö ¾Ê½À´Ï´Ù. º¹ÇÕ ÅØ½ºÆ® ¸í·É¹®¿¡¼­ °°Àº ¸í·É¹® À̸§À» »ç¿ëÇÏ´Â PREPARE¹®À» µÎ °³ ¹ßÇàÇÒ ¼ö ¾ø½À´Ï´Ù.

¿¹: µ¿Àû SQL¹®: ´ÙÀ½ ¿¹´Â µ¿Àû SQL¹®ÀÌ µé¾î ÀÖ´Â SQL ÇÁ·Î½Ãµà¾î¸¦ º¸¿©ÁÝ´Ï´Ù.

ÇÁ·Î½Ãµà¾î´Â ºÎ¼­ ¹øÈ£(deptNumber)¸¦ ÀÔ·Â ¸Å°³º¯¼ö·Î ¼ö½ÅÇÕ´Ï´Ù. ÇÁ·Î½Ãµà¾î¿¡¼­ 3°³ÀÇ ¸í·É¹® ¹®ÀÚ¿­ÀÌ ±¸ÃàµÇ°í ÁغñµÇ¸ç ½ÇÇàµË´Ï´Ù. ù¹øÂ° ¸í·É¹® ¹®ÀÚ¿­Àº DROP¹®À» ½ÇÇàÇÏ¿© ÀÛ¼ºÇØ¾ß ÇÏ´Â Å×À̺íÀÌ ¾ÆÁ÷ ¾øÀ½À» È®ÀÎÇÕ´Ï´Ù. ÀÌ Å×À̺íÀº DEPT_deptno_T·Î ¸í¸íµË´Ï´Ù. ¿©±â¼­ deptno´Â deptNumber ÀÔ·Â ¸Å°³º¯¼öÀÇ °ªÀÔ´Ï´Ù. CONTINUE HANDLER¸¦ ÅëÇØ SQL ÇÁ·Î½Ãµà¾î´Â Å×À̺íÀÌ ¾ø´Â °æ¿ì DROP¹®¿¡¼­ DB2°¡ ¸®ÅÏÇÏ´Â SQLSTATE 42704("Á¤ÀǵÇÁö ¾ÊÀº ¿ÀºêÁ§Æ® À̸§")¸¦ °¡ÁöÇÒ ¶§ °è¼ÓÇÒ ¼ö ÀÖ°Ô ÇÕ´Ï´Ù. µÑ° ¸í·É¹® ¹®ÀÚ¿­Àº CREATE¹®À» ½ÇÇàÇÏ¿© DEPT_deptno_T¸¦ ÀÛ¼ºÇÕ´Ï´Ù. ¼Â° ¸í·É¹® ¹®ÀÚ¿­Àº deptno ºÎ¼­¿¡ ÀÖ´Â ÀÛ¿ø¿¡ ´ëÇÑ ÇàÀ» DEPT_deptno_T¿¡ »ðÀÔÇÕ´Ï´Ù. ¼Â° ¸í·É¹® ¹®ÀÚ¿­¿¡´Â deptNumberÀ» ³ªÅ¸³»´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ°¡ ÀÖ½À´Ï´Ù. ÁغñµÈ ¸í·É¹®ÀÌ ½ÇÇàµÉ ¶§ deptNumber ¸Å°³º¯¼ö´Â ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¿¡ ´ëÇØ ´ëüµË´Ï´Ù.

   CREATE PROCEDURE create_dept_table
   (IN deptNumber VARCHAR(3), OUT table_name VARCHAR(30))
   LANGUAGE SQL
     BEGIN
       DECLARE stmt VARCHAR(1000);
 
       -- continue if sqlstate 42704 ('undefined object name')
       DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
         SET stmt = '';
       DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
         SET table_name = 'PROCEDURE_FAILED';
 
       SET table_name = 'DEPT_'||deptNumber||'_T';
       SET stmt = 'DROP TABLE '||table_name;
       PREPARE s1 FROM stmt;
       EXECUTE s1;
       SET stmt = 'CREATE TABLE '||table_name||
        '( empno CHAR(6) NOT NULL, '||
        'firstnme VARCHAR(12) NOT NULL, '||
        'midinit CHAR(1) NOT NULL, '||
        'lastname VARCHAR(15) NOT NULL, '||
        'salary DECIMAL(9,2))';
       PREPARE s2 FROM STMT;
       EXECUTE s2;
       SET stmt = 'INSERT INTO '||table_name || ' ' ||
        'SELECT empno, firstnme, midinit, lastname, salary '||
        'FROM employee '||
        'WHERE workdept = ?';
      PREPARE s3 FROM stmt;
      EXECUTE s3 USING deptNumber;
   END


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