SQL ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â °ÍÀº ¿ÜºÎ ÀúÀå ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â °Í°ú ºñ½ÁÇÕ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº CLI, JDBC ¶Ç´Â SQLJ ÀÀ¿ëÇÁ·Î±×·¥ ÀÎÅÍÆäÀ̽º¸¦ »ç¿ëÇÏ¿© SQL ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ ½ÂÀÎÇØ¾ß ÇÕ´Ï´Ù. ´Ù¸¥ SQL ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÏ´Â SQL ÇÁ·Î½Ãµà¾î ¶ÇÇÑ ÀÌ·± ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ ½ÂÀÎÇØ¾ß ÇÕ´Ï´Ù. SQL ÇÁ·Î½Ãµà¾î¿¡¼ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ·Á¸é ´ÙÀ½°ú °°ÀÌ 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¹®À» »ç¿ëÇÏ¿© °á°ú ¼¼Æ®¸¦ ¾×¼¼½ºÇÏ°í »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.
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;
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;