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

CREATE PROCEDURE¹® ½ÇÇà

CREATE PROCEDURE¹®À» DB2 ¸í·ÉÇà ÇÁ·Î¼¼¼­ (DB2 CLP) ½ºÅ©¸³Æ®·Î ½ÇÇàÇÏ·Á¸é ½ºÅ©¸³Æ®¿¡¼­ SQL¹®¿¡ ´ëÇØ ´ëü Á¾·á ¹®ÀÚ¸¦ »ç¿ëÇØ¾ß ÇÕ´Ï´Ù. DB2 CLP ½ºÅ©¸³Æ®ÀÇ ±âº» ¹®ÀÚÀÎ ¼¼¹ÌÄÝ·Ð(';') ¹®ÀÚ´Â SQL ÇÁ·Î½Ãµà¾î º»¹®¿¡¼­ SQL¹®À» Á¾·áÇÕ´Ï´Ù.

DV2 CLP ½ºÅ©¸³Æ®¿¡¼­ ´ëü Á¾·á ¹®ÀÚ¸¦ »ç¿ëÇÏ·Á¸é Ç¥ÁØ SQL¹®¿¡¼­ »ç¿ëµÇÁö ¾ÊÀº ¹®ÀÚ¸¦ ¼±ÅÃÇϽʽÿÀ. ´ÙÀ½ ¿¹¿¡¼­ ±âÈ£('@')´Â script.db2¶ó°í ÇÏ´Â DB2 CLP ½ºÅ©¸³Æ®ÀÇ Á¾·á ¹®ÀÚ·Î »ç¿ëµÇ¾ú½À´Ï´Ù.

    CREATE PROCEDURE UPDATE_SALARY_IF
    (IN employee_number CHAR(6), IN rating SMALLINT)
    LANGUAGE SQL
    BEGIN
      DECLARE not_found CONDITION FOR SQLSTATE '02000';
      DECLARE EXIT HANDLER FOR not_found
         SIGNAL SQLSTATE '20000' SET MESSAGE_TEXT = 'Employee not found';
 
      IF (rating = 1)
        THEN UPDATE employee
          SET salary = salary * 1.10, bonus = 1000
          WHERE empno = employee_number;
      ELSEIF (rating = 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 IF;
    END
@

¸í·ÉÇà¿¡¼­ DB2 CLP ½ºÅ©¸³Æ®¸¦ ó¸®ÇÏ·Á¸é ´ÙÀ½ ±¸¹®À» »ç¿ëÇϽʽÿÀ.

   db2 -tdterm-char -vf script-name

¿©±â¼­ term-char´Â Á¾·áÇÏ´Â ¹®ÀÚ¸¦ ³ªÅ¸³»°í script-nameÀº ó¸®ÇÒ DB2 CLP ½ºÅ©¸³Æ®ÀÇ À̸§À» ³ªÅ¸³À´Ï´Ù. ÀÌÀü ½ºÅ©¸³Æ®¸¦ ó¸®ÇÏ·Á¸é CLP¿¡¼­ ´ÙÀ½ ¸í·ÉÀ» ½ÇÇàÇϽʽÿÀ.

   db2 -td@ -vf script.db2


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