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

SQL ÇÁ·Î½Ãµà¾î »ùÇÃ


Ç¥ 47. SQL ÇÁ·Î½Ãµà¾î »ùÇà ÇÁ·Î±×·¥
»ùÇà ÇÁ·Î±×·¥ À̸§ ÇÁ·Î±×·¥ ¼³¸í
basecase.db2 UPDATE_SALARY ÇÁ·Î½Ãµà¾î´Â "»ùÇÃ" µ¥ÀÌÅͺ£À̽ºÀÇ "staff" Å×ÀÌºí¿¡¼­ "empno" IN ¸Å°³º¯¼ö·Î ½Äº°µÇ´Â Á÷¿øÀÇ ±Þ¿©¸¦ ¿Ã·ÁÁÝ´Ï´Ù. ÇÁ·Î½Ãµà¾î´Â "rating" IN ¸Å°³º¯¼ö¸¦ »ç¿ëÇÏ´Â CASE¹®¿¡ µû¶ó ÀλóÀ» °áÁ¤ÇÕ´Ï´Ù.
basecase.sqc UPDATE_SALARY ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
baseif.db2 UPDATE_SALARY_IF ÇÁ·Î½Ãµà¾î´Â "»ùÇÃ" µ¥ÀÌÅͺ£À̽ºÀÇ "staff" Å×ÀÌºí¿¡¼­ "empno" IN ¸Å°³º¯¼ö·Î ½Äº°µÇ´Â Á÷¿øÀÇ ±Þ¿©¸¦ ¿Ã·ÁÁÝ´Ï´Ù. ÇÁ·Î½Ãµà¾î´Â "rating" IN ¸Å°³º¯¼ö¸¦ »ç¿ëÇÏ´Â IF¹®¿¡ µû¶ó ÀλóÀ» °áÁ¤ÇÕ´Ï´Ù.
baseif.sqc UPDATE_SALARY_IF ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
dynamic.db2 CREATE_DEPT_TABLE ÇÁ·Î½Ãµà¾î´Â µ¿Àû DDLÀ» »ç¿ëÇÏ¿© »õ Å×À̺íÀ» ÀÛ¼ºÇÕ´Ï´Ù. Å×À̺í À̸§Àº ÇÁ·Î½Ãµà¾î¿¡ ´ëÇÑ IN ¸Å°³º¯¼öÀÇ °ªÀ» ±â¹ÝÀ¸·Î ÇÕ´Ï´Ù.
dynamic.sqc CREATE_DEPT_TABLE ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
iterate.db2 ITERATOR ÇÁ·Î½Ãµà¾î´Â FETCH ·çÇÁ¸¦ »ç¿ëÇÏ¿© "department" Å×ÀÌºí¿¡¼­ µ¥ÀÌÅ͸¦ °Ë»öÇÕ´Ï´Ù. "deptno" Ä÷³ÀÇ °ªÀÌ "D11"ÀÌ ¾Æ´Ï¸é ¼öÁ¤µÈ µ¥ÀÌÅͰ¡ "department" Å×ÀÌºí¿¡ »ðÀԵ˴ϴÙ. "deptno" Ä÷³ÀÇ °ªÀÌ "D11"À̸é ITERATE¹®Àº Á¦¾î È帧À» ´Ù½Ã LOOP¹®ÀÇ ½ÃÀÛÀ¸·Î Àü´ÞÇÕ´Ï´Ù.
iterate.sqc ITERATOR ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
leave.db2 LEAVE_LOOP ÇÁ·Î½Ãµà¾î´Â "not_found" Á¶°Ç Çڵ鷯°¡ LEAVE¹®À» È£ÃâÇϱâ Àü¿¡ LOOP¹®¿¡¼­ ¼öÇàµÈ FETCH Á¶ÀÛÀÇ ¼ö¸¦ °è»êÇÕ´Ï´Ù. LEAVE¹®À¸·Î Á¦¾î È帧Àº ·çÇÁ¸¦ Á¾·á½Ã۰í ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ¿Ï·áÇÕ´Ï´Ù.
leave.sqc LEAVE_LOOP ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
loop.db2 LOOP_UNTIL_SPACE ÇÁ·Î½Ãµà¾î´Â Ä¿¼­°¡ "midinit" Ä÷³¿¡ ´ëÇØ °ø¹é(' ') °ªÀÇ ÇàÀ» °Ë»öÇÒ ¶§±îÁö LOOP¹®¿¡¼­ ¼öÇàµÈ FETCH Á¶ÀÛÀÇ ¼ö¸¦ °è»êÇÕ´Ï´Ù. ·çÇÁ ¹®À¸·Î Á¦¾î È帧Àº ·çÇÁ¸¦ Á¾·á½Ã۰í ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ¿Ï·áÇÕ´Ï´Ù.
loop.sqc LOOP_UNTIL_SPACE ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
nestcase.db2 BUMP_SALARY ÇÁ·Î½Ãµà¾î´Â ÁßøµÈ CASE¹®À» »ç¿ëÇÏ¿© "»ùÇÃ" µ¥ÀÌÅͺ£À̽ºÀÇ "staff" Å×ÀÌºí¿¡¼­ dept IN ¸Å°³º¯¼ö·Î ½Äº°µÇ´Â ºÎ¼­¿¡ ÀÖ´Â Á÷¿øÀÇ ±Þ¿©¸¦ ¿Ã·ÁÁÝ´Ï´Ù.
nestcase.sqc BUMP_SALARY ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
nestif.db2 BUMP_SALARY_IF ÇÁ·Î½Ãµà¾î´Â ÁßøµÈ IF¹®À» »ç¿ëÇÏ¿© "»ùÇÃ" µ¥ÀÌÅͺ£À̽ºÀÇ "staff" Å×ÀÌºí¿¡¼­ dept IN ¸Å°³º¯¼ö·Î ½Äº°µÇ´Â ºÎ¼­¿¡ ÀÖ´Â Á÷¿øÀÇ ±Þ¿©¸¦ ¿Ã·ÁÁÝ´Ï´Ù.
nestif.sqc BUMP_SALARY_IF ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
repeat.db2 REPEAT_STMT ÇÁ·Î½Ãµà¾î´Â Ä¿¼­°¡ ´õ ÀÌ»ó ÇàÀ» °Ë»öÇÒ ¼ö ¾øÀ» ¶§±îÁö ¹Ýº¹¹®¿¡¼­ ¼öÇàÇÑ FETCH Á¶ÀÛÀÇ ¼ö¸¦ °è»êÇÕ´Ï´Ù. Á¶°Ç Çڵ鷯·Î Á¦¾î È帧Àº ¹Ýº¹ ·çÇÁ¸¦ Á¾·á½Ã۰í ÀúÀå ÇÁ·Î½Ãµà¾î¸¦ ¿Ï·áÇÕ´Ï´Ù.
repeat.sqc REPEAT_STMT ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.
rsultset.c MEDIAN_RESULT_SET ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇϰí Áß¾Ó ±Þ¿©¸¦ Ç¥½ÃÇϸç, SQL ÇÁ·Î½Ãµà¾î¿¡¼­ »ý¼ºµÈ °á°ú ¼¼Æ®¸¦ Ç¥½ÃÇÕ´Ï´Ù. ÀÌ Å¬¶óÀÌ¾ðÆ®´Â °á°ú ¼¼Æ®¸¦ ½ÂÀÎÇÒ ¼ö ÀÖ´Â CLI API¸¦ »ç¿ëÇÏ¿© ÀÛ¼ºµË´Ï´Ù.
rsultset.db2 MEDIAN_RESULT_SET ÇÁ·Î½Ãµà¾î´Â "»ùÇÃ" µ¥ÀÌÅͺ£À̽ºÀÇ "staff" Å×ÀÌºí¿¡¼­ "dept" IN ¸Å°³º¯¼ö·Î ½Äº°µÈ ºÎ¼­¿¡¼­ Á÷¿øÀÇ ±Þ¿©¸¦ È®º¸ÇÕ´Ï´Ù. Áß¾Ó°ªÀº ±Þ¿© OUT ¸Å°³º¯¼ö¿¡ ÇÒ´çµÇ°í "rsultset" Ŭ¶óÀÌ¾ðÆ®·Î ¸®Åϵ˴ϴÙ. ÇÁ·Î½Ãµà¾î´Â WITH RETURN Ä¿¼­¸¦ ¿­¾î¼­ ±Þ¿©°¡ Áß¾Ó°ªº¸´Ù Å« Á÷¿øÀÇ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÕ´Ï´Ù. ÇÁ·Î½Ãµà¾î´Â °á°ú ¼¼Æ®¸¦ Ŭ¶óÀÌ¾ðÆ®¿¡°Ô ¸®ÅÏÇÕ´Ï´Ù.
spserver.db2 ÀÌ CLP¿¡ ÀÖ´Â SQL ÇÁ·Î½Ãµà¾î´Â ±âº» ¿À·ù ó¸®, Áßø ÀúÀå ÇÁ·Î½Ãµà¾î È£Ã⠱׸®°í Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À̳ª È£ÃâÇÏ´Â ÀÀ¿ëÇÁ·Î±×·¥¿¡ °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏ´Â ÀÛ¾÷À» º¸¿©ÁÝ´Ï´Ù. CLI »ùÇà µð·ºÅ丮¿¡¼­ "spcall" ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¿© ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¼ö ÀÖ½À´Ï´Ù. C ¹× CPP »ùÇà µð·ºÅ丮¿¡¼­ "spclient" ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¿© °á°ú ¼¼Æ®¸¦ ¸®ÅÏÇÏÁö ¾Ê´Â ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
whiles.db2 DEPT_MEDIAN ÇÁ·Î½Ãµà¾î´Â "»ùÇÃ" µ¥ÀÌÅͺ£À̽ºÀÇ "staff" Å×ÀÌºí¿¡¼­ "dept" IN ¸Å°³º¯¼ö·Î ½Äº°µÇ´Â ºÎ¼­¿¡¼­ Á÷¿øÀÇ Áß°£ ±Þ¿©¸¦ È®º¸ÇÕ´Ï´Ù. Áß¾Ó°ªÀº ±Þ¿© OUT ¸Å°³º¯¼ö¿¡ ÇÒ´çµÇ°í "whiles" Ŭ¶óÀÌ¾ðÆ®·Î ¸®Åϵ˴ϴÙ. whiles Ŭ¶óÀÌ¾ðÆ®´Â Áß°£ ±Þ¿©¸¦ ÀμâÇÕ´Ï´Ù.
whiles.sqc DEPT_MEDIAN ÇÁ·Î½Ãµà¾î¸¦ È£ÃâÇÕ´Ï´Ù.


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