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


SQL ÇÁ·Î½Ãµà¾îÀÇ ¿¹

ÀÌ Àý¿¡´Â SQL ÇÁ·Î½Ãµà¾î º»¹®¿¡ ³ªÅ¸³ª´Â °¢ ¸í·É¹®À» »ç¿ëÇÏ´Â ¹æ¹ý¿¡ ´ëÇÑ ¿¹°¡ ÀÖ½À´Ï´Ù. SQL ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϴ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À» Æ÷ÇÔÇÏ¿© ÀÌ·± ¿¹¿Í ±âŸ SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ ´ÙÀ½ µð·ºÅ丮¸¦ ÂüÁ¶ÇϽʽÿÀ.

UNIX ¿î¿µ üÁ¦
$HOME/sqllib/samples/sqlproc, ¿©±â¼­ $HOMEÀº DB2 ÀνºÅϽº µð·ºÅ丮ÀÇ À§Ä¡¸¦ ³ªÅ¸³À´Ï´Ù.

Windows 32 ºñÆ® ¿î¿µ üÁ¦
%DRIVE%\sqllib\samples\sqlproc, ¿©±â¼­ %DRIVE%´Â DB2¸¦ ¼³Ä¡ÇÑ µå¶óÀ̺긦 ³ªÅ¸³À´Ï´Ù.

¿¹ 1: CASE¹®: ´ÙÀ½ SQL ÇÁ·Î½Ãµà¾î´Â CASE¹®À» »ç¿ëÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù. ÇÁ·Î½Ãµà¾î´Â Á÷¿øÀÇ ID ¹øÈ£¿Í µî±ÞÀ» ÀÔ·Â ¸Å°³º¯¼ö·Î ¹Þ½À´Ï´Ù. CASE¹®Àº °¡´ÉÇÑ µî±Þ¸¶´Ù ´Ù¸¥ UPDATE¹®À» »ç¿ëÇÏ¿© Á÷¿ø¿¡ ´ëÇØ ±Þ¿©¿Í º¸³Ê½º¸¦ ¼öÁ¤ÇÕ´Ï´Ù.

    CREATE PROCEDURE UPDATE_SALARY
    (IN employee_number CHAR(6), IN rating INT)
    LANGUAGE SQL
    BEGIN
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE EXIT HANDLER FOR not_found
        SIGNAL SQLSTATE '02444';
 
      CASE rating
        WHEN 1 THEN
          UPDATE employee
          SET salary = salary * 1.10, bonus = 1000
          WHERE empno = employee_number;
        WHEN 2 THEN
          UPDATE employee
          SET salary = salary * 1.05, bonus = 500
          WHERE empno = employee_number;
        ELSE
          UPDATE employee
          SET salary = salary * 1.03, bonus = 0
          WHERE empno = employee_number;
       END CASE;
    END

¿¹ 2: Áßø IF ¹× WHILE¹®ÀÇ º¹ÇÕ ÅØ½ºÆ® ¸í·É¹®: ´ÙÀ½ ¿¹´Â Áßø IF¹®, WHILE¹® ¹× ÁöÁ¤ ¸í·É¹®ÀÌ µé¾î ÀÖ´Â º¹ÇÕ ÅØ½ºÆ® ¸í·É¹®À» º¸¿©ÁÝ´Ï´Ù. ¿¹´Â SQL º¯¼ö, Ä¿¼­ ±×¸®°í ¿À·ù ÄÚµå Ŭ·¡½º¿¡ ´ëÇÑ Çڵ鷯µµ º¸¿©ÁÝ´Ï´Ù.

ÇÁ·Î½Ãµà¾î´Â ºÎ¼­ ¹øÈ£¸¦ ÀÔ·Â ¸Å°³º¯¼ö·Î ¼ö½ÅÇÕ´Ï´Ù. ÇÁ·Î½Ãµà¾î º»¹®¿¡ ÀÖ´Â WHILE¹®Àº ºÎ¼­ÀÇ °¢ Á÷¿ø¿¡ ´ëÇØ ±Þ¿©¿Í º¸³Ê½º¸¦ ÆÐÄ¡ÇÕ´Ï´Ù. WHILE¹®¿¡ ÀÖ´Â IF¹®ÀÌ ÀÏÇÑ ¿¬¼ö¿Í ÇöÀç ±Þ¿©¿¡ µû¶ó °¢ Á÷¿ø¿¡ ´ëÇØ ±Þ¿©¸¦ °»½ÅÇÕ´Ï´Ù. ºÎ¼­¿¡ ÀÖ´Â ¸ðµç Á÷¿ø ·¹Äڵ尡 󸮵Ǹé Á÷¿ø ·¹Äڵ带 °Ë»öÇÏ´Â FETCH¹®Àº SQLSTATE 20000À» ¼ö½ÅÇÕ´Ï´Ù. not_found Á¶°Ç Çڵ鷯´Â WHILE¹®¿¡ ´ëÇØ °Ë»ö Á¶°ÇÀ» °ÅÁþÀ¸·Î ¸¸µå¹Ç·Î WHILE¹®ÀÇ ½ÇÇàÀÌ Á¾·áµË´Ï´Ù.

   CREATE PROCEDURE BUMP_SALARY_IF (IN deptnumber SMALLINT)
   LANGUAGE SQL
   BEGIN
      DECLARE v_salary DOUBLE;
      DECLARE v_years SMALLINT;
      DECLARE v_id SMALLINT;
      DECLARE at_end INT DEFAULT 0;
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
 
      -- CAST salary as DOUBLE because SQL procedures do not support DECIMAL
      DECLARE C1 CURSOR FOR
        SELECT id, CAST(salary AS DOUBLE), years
        FROM staff;
      DECLARE CONTINUE HANDLER FOR not_found
        SET at_end = 1;
 
      OPEN C1;
      FETCH C1 INTO v_id, v_salary, v_years;
      WHILE at_end = 0 DO
        IF (v_salary < 2000 * v_years)
          THEN UPDATE staff
            SET salary = 2150 * v_years
            WHERE id = v_id;
          ELSEIF (v_salary < 5000 * v_years)
            THEN IF (v_salary < 3000 * v_years)
              THEN UPDATE staff
                SET salary = 3000 * v_years
                WHERE id = v_id;
            ELSE UPDATE staff
              SET salary = v_salary * 1.10
              WHERE id = v_id;
            END IF;
          ELSE UPDATE staff
            SET job = 'PREZ'
            WHERE id = v_id;
        END IF;
        FETCH C1 INTO v_id, v_salary, v_years;
      END WHILE;
      CLOSE C1;
   END

¿¹ 3: Àü¿ª Àӽà Å×ÀÌºí ¹× °á°ú ¼¼Æ®°¡ ÀÖ´Â Áßø SQL ÇÁ·Î½Ãµà¾î »ç¿ë:

´ÙÀ½ ¿¹´Â ASSOCIATE RESULT SET LOCATOR¿Í ALLOCATE CURSOR¹®À» »ç¿ëÇÏ¿© È£ÃâµÈ SQL ÇÁ·Î½Ãµà¾îÀÎ temp_table_insert¿¡¼­ °á°ú ¼¼Æ®¸¦ È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾îÀÎ temp_table_create·Î ¸®ÅÏÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù. ¿¹´Â ¶ÇÇÑ È£ÃâµÈ SQL ÇÁ·Î½Ãµà¾î°¡ È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ ÀÛ¼ºµÈ Àü¿ª Àӽà Å×À̺íÀ» »ç¿ëÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.

¿¹¿¡¼­ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ ¶Ç´Â ´Ù¸¥ SQL ÇÁ·Î½Ãµà¾î´Â SESSION.TTT Àü¿ª Àӽà Å×À̺íÀ» ÀÛ¼ºÇϰí, temp_table_insert¸¦ È£ÃâÇÏ´Â temp_table_create¸¦ È£ÃâÇÕ´Ï´Ù.

SESSION.TTT Àü¿ª Àӽà Å×À̺íÀ» »ç¿ëÇϱâ À§ÇØ temp_table_insert¿¡´Â temp_table_create°¡ SESSION.TTT¸¦ ÀÛ¼ºÇϱâ À§ÇØ ¹ßÇàÇÑ ¸í·É¹®°ú °°Àº DECLARE GLOBAL TEMPORARY TABLE¹®ÀÌ µé¾î ÀÖ½À´Ï´Ù. Â÷ÀÌÁ¡Àº temp_table_insert¿¡ DECLARE GLOBAL TEMPORARY TABLE¹®¿¡ Ç×»ó °ÅÁþÀÎ IF¹®ÀÌ ÀÖ´Ù´Â °ÍÀÔ´Ï´Ù. IF¹®Àº DB2°¡ ´Ù½Ã Àü¿ª Àӽà Å×À̺íÀ» ÀÛ¼ºÇÏÁö ¸øÇϵµ·Ï ¸·Áö¸¸, SQL ÇÁ·Î½Ãµà¾î¿¡¼­ Àü¿ª Àӽà Å×À̺íÀ» ÈÄ¼Ó ¸í·É¹®¿¡¼­ »ç¿ëÇÒ ¼ö ÀÖ°Ô ÇÕ´Ï´Ù.

´Ù¸¥ SQL ÇÁ·Î½Ãµà¾î¿¡ ÀÇÇØ ÀÛ¼ºµÈ Àü¿ª Àӽà Å×ÀÌºí¿¡¼­ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ·Á¸é, temp_table_insert´Â »õ ¹üÀ§¿¡¼­ DECLARE CURSOR¹®À» ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù. temp_table_insert´Â »õ ¹üÀ§ÀÇ ¿ä±¸»çÇ×À» ¸¸Á·½ÃŰ´Â º¹ÇÕ SQL ºí·Ï¿¡¼­ DECLARE CURSOR¿Í OPEN CURSOR¹®À» ¹ßÇàÇÕ´Ï´Ù. Ä¿¼­´Â SQL ÇÁ·Î½Ãµà¾î°¡ Á¸ÀçÇϱâ Àü¿¡´Â ´ÝÈ÷Áö ¾ÊÀ¸¹Ç·Î, DB2´Â °á°ú ¼¼Æ®¸¦ ´Ù½Ã È£ÃâÀÚ temp_table_create·Î Àü´ÞÇÕ´Ï´Ù.

È£ÃâµÈ SQL ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ½ÂÀÎÇÏ·Á¸é temp_table_create´Â temp_table_insert¸¦ °á°ú ¼¼Æ®ÀÇ °³½ÃÀÚ·Î ½Äº°ÇÏ´Â ASSOCIATE RESULT SET LOCATOR¹®À» ¹ßÇàÇÕ´Ï´Ù. ±×·¯¸é temp_table_create´Â °á°ú ¼¼Æ®¸¦ ¿­±â À§ÇØ °á°ú ¼¼Æ® À§Ä¡ ÁöÁ¤ÀÚ¿¡ ´ëÇØ ALLOCATE CURSOR¹®À» ¹ßÇàÇÕ´Ï´Ù. ALLOCATE CURSOR¹®ÀÌ ¼º°øÇϸé SQL ÇÁ·Î½Ãµà¾î´Â º¸Åë ¶§Ã³·³ °á°ú ¼¼Æ®¿¡ ´ëÇØ ÀÛ¾÷ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ ¿¹¿¡¼­ temp_table_create´Â °á°ú ¼¼Æ®¿¡¼­ ¸ðµç ÇàÀ» ÆäÄ¡ÇÏ¿© Ä÷³ÀÇ °ªÀ» Ãâ·Â ¸Å°³º¯¼ö¿¡ Ãß°¡ÇÕ´Ï´Ù.
ÁÖ:Àü¿ª Àӽà Å×À̺íÀ» »ç¿ëÇÏ´Â SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇØ CREATE PROCEDURE¹®À» ¹ßÇàÇϱâ Àü¿¡ »ç¿ëÀÚ Àӽà Å×ÀÌºí °ø°£À» ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. »ç¿ëÀÚ Àӽà Å×ÀÌºí °ø°£À» ÀÛ¼ºÇÏ·Á¸é ´ÙÀ½ SQL¹®À» ¹ßÇàÇϽʽÿÀ.

   CREATE USER TEMPORARY TABLESPACE ts1
      MANAGED BY SYSTEM USING ('ts1file');
¿©±â¼­ ts1Àº »ç¿ëÀÚ Àӽà Å×ÀÌºí °ø°£ÀÇ À̸§À» ³ªÅ¸³»°í ts1fileÀº Å×ÀÌºí °ø°£¿¡¼­ »ç¿ëµÈ ÄÁÅ×À̳ÊÀÇ À̸§À» ³ªÅ¸³À´Ï´Ù.

   CREATE PROCEDURE temp_table_create(IN parm1 INTEGER, IN parm2 INTEGER,
      OUT parm3 INTEGER, OUT parm4 INTEGER)
      LANGUAGE SQL
      BEGIN
         DECLARE loc1 RESULT_SET_LOCATOR VARYING;
         DECLARE total3,total4 INTEGER DEFAULT 0;
         DECLARE rcolumn1, rcolumn2 INTEGER DEFAULT 0;
         DECLARE result_set_end INTEGER DEFAULT 0;
         DECLARE CONTINUE HANDLER FOR NOT FOUND, SQLEXCEPTION, SQLWARNING
         BEGIN
           SET result_set_end = 1;
         END;
         --Create the temporary table that is used in both this SQL procedure
         --and in the SQL procedure called by this SQL procedure.
         DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
            NOT LOGGED;
         --Insert rows into the temporary table.
         --The result set includes these rows.
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+1, parm2+1);
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+2, parm2+2);
         --Make a nested call to the 'temp_table_insert' SQL procedure.
         CALL temp_table_insert(parm1, parm2);
         --Issue the ASSOCIATE RESULT SET LOCATOR statement to
         --accept a single result set from 'temp_table_insert'.
         --If 'temp_table_insert' returns multiple result sets,
         --you must declare one locator variable (for example,
         --ASSOCIATE RESULT SET LOCATOR(loc1, loc2, loc3) for each result set.
         ASSOCIATE RESULT SET LOCATOR(loc1) WITH PROCEDURE temp_table_insert;
         --The ALLOCATE statement is similar to the OPEN statement.
         --It makes the result set available in this SQL procedure.
         ALLOCATE cursor1 CURSOR FOR RESULT SET loc1;
         --Insert rows into the temporary table.
         --The result set does not include these rows.
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+5, parm2+5);
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+6, parm2+6);
         SET result_set_end = 0;
         --Fetch the columns from the first row of the result set.
         FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
         WHILE (result_set_end = 0) DO
           SET total3 = total3 + rcolumn1;
           SET total4 = total4 + rcolumn2;
           --Fetch columns from the result set for the
           --next iteration of the WHILE loop.
           FETCH FROM cursor1 INTO rcolumn1, rcolumn2;
         END WHILE;
         CLOSE cursor1;
         SET parm3 = total3;
         SET parm4 = total4;
      END @
 
   CREATE PROCEDURE temp_table_insert (IN parm1 INTEGER, IN parm2 INTEGER )
      LANGUAGE SQL
      BEGIN
         DECLARE result_set_end INTEGER DEFAULT 0;
         DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN
           SET result_set_end = 1;
         END;
         --To use a temporary table that is created by a different stored
         --procedure, include a DECLARE GLOBAL TEMPORARY TABLE statement
         --inside a condition statement that always evaluates to false.
         IF (1 = 0) THEN
           DECLARE GLOBAL TEMPORARY TABLE ttt(column1 INT, column2 INT)
              NOT LOGGED;
         END IF;
         --Insert rows into the temporary table.
         --The result set includes these rows.
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+3, parm2+3);
         INSERT INTO session.ttt(column1, column2) VALUES ( parm1+4, parm2+4);
         --To return a result set from the temporary table, issue
         --the DECLARE CURSOR statement inside a new scope, such as
         --a compound SQL statement (BEGIN...END block).
         --Issue the DECLARE CURSOR statement after the DECLARE
         --GLOBAL TEMPORARY TABLE statement.
         BEGIN
            --The WITH RETURN TO CALLER clause causes the SQL procedure
            --to return its result set to the calling procedure.
            DECLARE cur1 CURSOR WITH RETURN TO CALLER
               FOR SELECT * FROM session.ttt;
            --To return a result set, open a cursor without closing the cursor.
           OPEN cur1 ;
         END;
      END


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