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


SQL ÇÁ·Î½Ãµà¾î·ÎºÎÅÍ °á°ú ¼¼Æ® ¸®ÅÏ

SQL ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â °ÍÀº ¿ÜºÎ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â °Í°ú ºñ½ÁÇÕ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº CLI, JDBC ¶Ç´Â SQLJ ÀÀ¿ëÇÁ·Î±×·¥ ÀÎÅÍÆäÀ̽º¸¦ »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ½ÂÀÎÇØ¾ß ÇÕ´Ï´Ù. ´Ù¸¥ SQL ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î ¶ÇÇÑ ÀÌ·± ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ½ÂÀÎÇØ¾ß ÇÕ´Ï´Ù. SQL ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ·Á¸é ´ÙÀ½°ú °°ÀÌ SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇϽʽÿÀ.

  1. CREATE PROCEDURE¹®ÀÇ DYNAMIC RESULT SETSÀýÀ» »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î°¡ ¸®ÅÏÇÏ´Â °á°ú ¼¼Æ®ÀÇ ¼ö¸¦ ¼±¾ðÇϽʽÿÀ.
  2. DECLARE CURSOR¹®À» »ç¿ëÇÏ¿© Ä¿¼­¸¦ ¼±¾ðÇϽʽÿÀ.
  3. OPEN CURSOR¹®À» »ç¿ëÇÏ¿© Ä¿¼­¸¦ ¿©½Ê½Ã¿À.
  4. Ä¿¼­¸¦ ´ÝÁö ¾Ê°í SQL ÇÁ·Î½Ãµà¾î¸¦ Á¾·áÇϽʽÿÀ.

¿¹¸¦ µé¾î, ´ÙÀ½°ú °°ÀÌ threshold INOUT ¸Å°³º¯¼öÀÇ °ªÀ» ±â¹ÝÀ¸·Î ´ÜÀÏ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â SQL ÇÁ·Î½Ãµà¾î¸¦ ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.

   CREATE PROCEDURE RESULT_SET (INOUT threshold SMALLINT)
   LANGUAGE SQL
   DYNAMIC RESULT SETS 1
   BEGIN
      DECLARE cur1 CURSOR WITH RETURN TO CALLER FOR
          SELECT name, job, years
          FROM staff
          WHERE years < threshold;
      OPEN cur1;
   END

Ŭ¶óÀÌ¾ðÆ® ¹× È£ÃâÀÚ °á°ú ¼¼Æ® ¸®ÅÏ

ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Áßø SQL ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇϸé, DECLARE CURSOR¹®ÀÇ WITH RETURNÀýÀ» »ç¿ëÇÏ¿© DB2°¡ °á°ú ¼¼Æ®¸¦ ÀûÇÕÇÑ À§Ä¡·Î ¸®ÅÏÇÏ°Ô ÇØ¾ß ÇÕ´Ï´Ù. ¸ñÇ¥ SQL ÇÁ·Î½Ãµà¾î°¡ °á°ú ¼¼Æ®¸¦ È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î·Î ¸®ÅÏÇϸé È£ÃâÀÚ´Â ALLOCATE CURSOR¿Í ASSOCIATE RESULT SET LOCATOR¹®À» »ç¿ëÇÏ¿© °á°ú ¼¼Æ®¸¦ ¾×¼¼½ºÇÏ°í »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.

Ŭ¶óÀÌ¾ðÆ® °á°ú ¼¼Æ® ¸®ÅÏ

Ç×»ó SQL ÇÁ·Î½Ãµà¾î¿¡¼­ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ·Á¸é, °á°ú ¼¼Æ®¿¡ ¿¬°üµÈ DECLARE CURSOR¹®ÀÇ WITH RETURN TO CLIENTÀýÀ» »ç¿ëÇϽʽÿÀ. ´ÙÀ½ ¿¹¿¡¼­ "CLIENT_SET"°¡ Áßø SQL ÇÁ·Î½Ãµà¾î CALL¹®ÀÇ ¸ñÇ¥ÀÎ °æ¿ì¶óµµ SQL ÇÁ·Î½Ãµà¾î "CLIENT_SET"´Â DECLARE CURSOR¹®ÀÇ RETURN TO CLIENTÀýÀ» »ç¿ëÇÏ¿© °á°ú ¼¼Æ®¸¦ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¸®ÅÏÇÕ´Ï´Ù.

   CREATE PROCEDURE CLIENT_SET()
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   BEGIN
      DECLARE clientcur CURSOR WITH RETURN TO CLIENT
          FOR SELECT name, dept, job
          FROM staff
          WHERE salary > 20000;
      OPEN clientcur;
   END

È£ÃâÀÚ °á°ú ¼¼Æ® ¸®ÅÏ

È£ÃâÀÚ°¡ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ÀÌµç ´Ù¸¥ SQL ÇÁ·Î½Ãµà¾îµçÁö °£¿¡ °á°ú ¼¼Æ®¸¦ SQL ÇÁ·Î½Ãµà¾îÀÇ Á÷Á¢ È£ÃâÀÚ¿¡°Ô ¸®ÅÏÇÏ·Á¸é, °á°ú ¼¼Æ®¿¡ ¿¬°üµÈ DECLARE CURSOR¹®ÀÇ WITH RETURN TO CALLERÀýÀ» »ç¿ëÇϽʽÿÀ. ´ÙÀ½ ¿¹¿¡¼­ SQL ÇÁ·Î½Ãµà¾î "CALLER_SET"´Â WITH RETURN TO CALLERÀýÀ» »ç¿ëÇÏ¿© °á°ú ¼¼Æ®¸¦ CALLER_SET È£ÃâÀÚ¿¡°Ô ¸®ÅÏÇÕ´Ï´Ù.

   CREATE PROCEDURE CALLER_SET()
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   BEGIN
      DECLARE clientcur CURSOR WITH RETURN TO CALLER
          FOR SELECT name, dept, job
          FROM staff
          WHERE salary > 15000;
      OPEN clientcur;
   END

È£ÃâÀÚ °á°ú ¼¼Æ® ¸®ÅÏ

È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î°¡ ¸ñÇ¥ SQL ÇÁ·Î½Ãµà¾î¿¡¼­ °á°ú ¼¼Æ®¸¦ ¼ö½ÅÇÒ °ÍÀ¸·Î ¿¹»óµÉ ¶§, ALLOCATE CURSOR¿Í ASSOCIATE RESULT SET LOCATOR¹®À» »ç¿ëÇÏ¿© °á°ú ¼¼Æ®¸¦ ¾×¼¼½ºÇÏ°í »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.

ASSOCIATE RESULT SET LOCATOR
È£ÃâÀÚ¿¡°Ô °á°ú ¼¼Æ®¸¦ Çϳª ÀÌ»ó ¸®ÅÏÇÏ´Â ¸ñÇ¥ SQL ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ CALL¹® ´ÙÀ½¿¡ È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î´Â ÀÌ ¸í·É¹®À» ¹ßÇàÇÏ¿© °á°ú ¼¼Æ® À§Ä¡ ÁöÁ¤ÀÚ º¯¼ö¸¦ ¸®ÅÏµÈ °¢ °á°ú ¼¼Æ®¸¶´Ù ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, ¸ñÇ¥ SQL ÇÁ·Î½Ãµà¾î¿¡¼­ ¼¼ °³ÀÇ °á°ú ¼¼Æ®¸¦ ¼ö½ÅÇÒ °ÍÀ¸·Î ¿¹»óµÇ´Â È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î¿¡´Â ´ÙÀ½°ú °°Àº SQLÀÌ µé¾î ÀÖ½À´Ï´Ù.

   DECLARE result1 RESULT_SET_LOCATOR VARYING;
   DECLARE result2 RESULT_SET_LOCATOR VARYING;
   DECLARE result3 RESULT_SET_LOCATOR VARYING;
 
   CALL targetProcedure();
   ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
      WITH PROCEDURE targetProcedure;

ALLOCATE CURSOR
È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î¿¡¼­ ALLOCATE CURSOR¹®À» »ç¿ëÇÏ¿© ¸ñÇ¥ SQL ÇÁ·Î½Ãµà¾î¿¡¼­ ¸®ÅÏµÈ °á°ú ¼¼Æ®¸¦ ¿©½Ê½Ã¿À. ALLOCATE CURSOR¹®À» »ç¿ëÇÏ·Á¸é °á°ú ¼¼Æ®´Â Ç×»ó ASSOCIATE RESULT SET LOCATORS¹®À» ÅëÇØ °á°ú ¼¼Æ® À§Ä¡ ÁöÁ¤ÀÚ¿¡ ¿¬°üµÇ¾î¾ß ÇÕ´Ï´Ù. ÀÏ´Ü SQL ÇÁ·Î½Ãµà¾î°¡ ALLOCATE CURSOR¹®À» ¹ßÇàÇϸé, ALLOCATE CURSOR¹®¿¡¼­ ¼±¾ðµÈ Ä¿¼­ À̸§À» »ç¿ëÇÏ¿© °á°ú ¼¼Æ®¿¡¼­ ÇàÀ» ÆäÄ¡ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌÀü¿¡ ¼³¸íµÈ ASSOCIATE LOCATORS ¿¹¸¦ È®ÀåÇÏ·Á¸é, SQL ÇÁ·Î½Ãµà¾î´Â ´ÙÀ½ SQLÀ» »ç¿ëÇÏ¿© ¸®ÅÏµÈ °á°ú ¼¼Æ® Áß Ã¹¹øÂ° ¼¼Æ®¿¡¼­ ÇàÀ» ÆäÄ¡ÇÒ ¼ö ÀÖ½À´Ï´Ù.

   DECLARE result1 RESULT_SET_LOCATOR VARYING;
   DECLARE result2 RESULT_SET_LOCATOR VARYING;
   DECLARE result3 RESULT_SET_LOCATOR VARYING;
   CALL targetProcedure();
   ASSOCIATE RESULT SET LOCATORS(result1, result2, result3)
      WITH PROCEDURE targetProcedure;
   ALLOCATE rsCur CURSOR FOR result1;
   WHILE (at_end = 0) DO
      SET total1 = total1 + var1;
      SET total2 = total2 + var2;
      FETCH FROM rsCur INTO var1, var2;
   END WHILE;


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