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