¿ÜºÎ ÀúÀå ÇÁ·Î½Ãµà¾î¿Í °°ÀÌ 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