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