´ÙÀ½Àº ±¸º° À¯Çü Á¶ÀÛÀÇ ¿¹ÀÔ´Ï´Ù.
1999³â 7¿ù(7/99) ¹ÌÈ $100 000.00 ÀÌ»óÀ¸·Î ÆÇ¸ÅµÈ Á¦Ç°À» ¾Ë·Á°í ÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
SELECT PRODUCT_ITEM FROM US_SALES WHERE TOTAL > US_DOLLAR (100000) AND month = 7 AND year = 1999
DECIMAL°ú °°Àº ¹ÌÈ ´Þ·¯ÀÇ ¼Ò½º À¯Çü ÀνºÅϽº¿Í ¹ÌÈ ´Þ·¯¸¦ Á÷Á¢ ºñ±³ÇÒ ¼ö ¾øÀ¸¹Ç·Î DB2°¡ Á¦°øÇÏ´Â À¯Çüº¯È¯(cast) ÇÔ¼ö¸¦ »ç¿ëÇÏ¿© DECIMAL¿¡¼ ¹ÌÈ ´Þ·¯·Î À¯Çüº¯È¯Çß½À´Ï´Ù. DB2°¡ Á¦°øÇÏ´Â ´Ù¸¥ À¯Çüº¯È¯(cast) ÇÔ¼ö(Áï, ¹ÌÈ ´Þ·¯¿¡¼ DECIMAL·Î À¯Çüº¯È¯½ÃŰ´Â ÇÔ¼ö)¸¦ »ç¿ëÇÒ ¼öµµ ÀÖ°í ¸ðµç Ä÷³À» DECIMAL·Î À¯Çüº¯È¯ÇÒ ¼ö ÀÖ½À´Ï´Ù. ±¸º° À¯ÇüÀ¸·Î À¯Çüº¯È¯Çϰųª ±¸º° À¯ÇüÀ» À¯Çüº¯È¯ÇÒ °æ¿ì¿¡µµ, À¯Çüº¯È¯ ½ºÆå Ç¥±â¹ýÀ» »ç¿ëÇÏ¿© À¯Çüº¯È¯ ¶Ç´Â ÇÔ¼ö Ç¥±â¸¦ ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù. Áï, Á¶È¸¸¦ ´ÙÀ½°ú °°ÀÌ ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT PRODUCT_ITEM FROM US_SALES WHERE TOTAL > CAST (100000 AS us_dollar) AND MONTH = 7 AND YEAR = 1999
ij³ª´Ù ´Þ·¯¸¦ ¹ÌÈ ´Þ·¯·Î º¯È¯½ÃŰ´Â UDF¸¦ Á¤ÀÇÇÏ·Á ÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. DB2 ¿ÜºÎ¿¡¼ °ü¸®µÇ´Â ÆÄÀÏ¿¡¼ ÇöÀç ȯÀ²À» ¾Ë¾Æº¼ ¼ö ÀÖ½À´Ï´Ù. ±×·¯¸é ij³ª´Ù ´Þ·¯·Î °ªÀ» Ç¥½ÃÇÏ¿© ȯÀ² ÆÄÀÏ¿¡ ¾×¼¼½ºÇϰí, ÇØ´ç °ªÀ» ¹ÌÈ ´Þ·¯·Î ¸®ÅÏÇÏ´Â UDF¸¦ Á¤ÀÇÇÏ°Ô µË´Ï´Ù.
óÀ½¿¡´Â ÀÌ·¯ÇÑ UDF°¡ ÀÛ¼ºÇϱ⠽¬¿ö º¸ÀÏ °ÍÀÔ´Ï´Ù. ±×·¯³ª, C´Â DECIMAL °ªÀ» Áö¿øÇÏÁö ¾Ê½À´Ï´Ù. ´Ù¸¥ Åëȸ¦ ³ªÅ¸³»´Â ±¸º° À¯ÇüÀÌ DECIMAL·Î¼ Á¤Àǵ˴ϴÙ. »ç¿ëÀÚÀÇ UDF´Â ½ÊÁø¼ö Á¤¹Ðµµ¸¦ À¯ÁöÇϰí DECIMAL °ª Ç¥½Ã°¡ Çã¿ëµÇ´Â C¿¡ ÀÇÇØ Á¦°øµÈ À¯ÀÏÇÑ µ¥ÀÌÅÍ À¯ÇüÀ̹ǷÎ, DOUBLE °ªÀ» ¹Þ¾Æ ¸®ÅÏÇØ¾ß ÇÕ´Ï´Ù. µû¶ó¼, »ç¿ëÀÚÀÇ UDF´Â ´ÙÀ½°ú °°ÀÌ Á¤ÀǵǾî¾ß ÇÕ´Ï´Ù.
CREATE FUNCTION CDN_TO_US_DOUBLE(DOUBLE) RETURNS DOUBLE EXTERNAL NAME '/u/finance/funcdir/currencies!cdn2us' LANGUAGE C PARAMETER STYLE DB2SQL NO SQL NOT DETERMINISTIC NO EXTERNAL ACTION FENCED
ij³ª´Ù ´Þ·¯¿Í ¹Ì´Þ·¯ »çÀÌÀÇ È¯À²Àº µÎ ¹øÀÇ UDF È£Ãâ »çÀÌ¿¡ º¯°æµÉ ¼ö ÀÖÀ¸¹Ç·Î, NOT DETERMINISTICÀ¸·Î ¼±¾ðÇÕ´Ï´Ù.
´ç¸é ¹®Á¦´Â ¾î¶»°Ô ij³ª´Ù ´Þ·¯¸¦ ÀÌ UDF·Î Àü´ÞÇϰí ÀÌ UDF¿¡¼ ¹ÌÈ ´Þ·¯¸¦ °¡Á®¿Ã °ÍÀÎÁöÀÔ´Ï´Ù. ij³ª´Ù ´Þ·¯´Â DECIMAL °ªÀ¸·Î À¯Çüº¯È¯µÇ¾î¾ß ÇÕ´Ï´Ù. DECIMAL °ªÀº DOUBLE·Î À¯Çüº¯È¯µÇ¾î¾ß ÇÕ´Ï´Ù. ¸®ÅÏµÈ DOUBLE °ªÀ» DECIMAL·Î, DECIMAL °ªÀ» ¹ÌÈ ´Þ·¯·Î À¯Çüº¯È¯Çϵµ·Ï ÇØ¾ß ÇÕ´Ï´Ù.
ÀÌ·¯ÇÑ À¯Çüº¯È¯Àº Àü·¡ UDF¸¦ Á¤ÀÇÇÒ ¶§¸¶´Ù DB2¿¡ ÀÇÇØ ÀÚµ¿À¸·Î ¼öÇàµÇ°í, Àü·¡ UDF ¸Å°³º¯¼ö ¹× ¸®ÅÏ À¯ÇüÀÌ ¸Å°³º¯¼ö¿Í Á¤È®ÇÏ°Ô ÀÏÄ¡ÇÏÁö ¾ÊÀ¸¸ç ¼Ò½º ÇÔ¼ö À¯ÇüÀ» ¸®ÅÏÇÕ´Ï´Ù. µû¶ó¼, µÎ °³ÀÇ Àü·¡ UDF¸¦ Á¤ÀÇÇØ¾ß ÇÕ´Ï´Ù. ù¹øÂ° À¯ÇüÀº DOUBLE °ªÀ» DECIMAL Ç¥½Ã·Î °¡Á®¿É´Ï´Ù. µÎ ¹øÂ° À¯ÇüÀº DECIMAL °ªÀ» ±¸º° À¯ÇüÀ¸·Î °¡Á®¿É´Ï´Ù. Áï, ´ÙÀ½°ú °°ÀÌ Á¤ÀÇÇÕ´Ï´Ù.
CREATE FUNCTION CDN_TO_US_DEC (DECIMAL(9,2)) RETURNS DECIMAL(9,2) SOURCE CDN_TO_US_DOUBLE (DOUBLE) CREATE FUNCTION US_DOLLAR (CANADIAN_DOLLAR) RETURNS US_DOLLAR SOURCE CDN_TO_US_DEC (DECIMAL())
US_DOLLAR(C1)¿¡¼Ã³·³ US_DOLLAR ÇÔ¼öÀÇ È£ÃâÀÌ ´ÙÀ½À» È£ÃâÇÏ´Â °Í°ú °°Àº È¿°úÀÔ´Ï´Ù. ¿©±â¼ C1Àº À¯ÇüÀÌ Ä³³ª´Ù ´Þ·¯ÀÎ Ä÷³ÀÔ´Ï´Ù.
US_DOLLAR (DECIMAL(CDN_TO_US_DOUBLE (DOUBLE (DECIMAL (C1)))))
Áï, C1(ij³ª´Ù ´Þ¶ó)Àº 10Áø¼ö·Î À¯Çüº¯È¯µÇ°í, ´Ù½Ã 2¹è °ªÀ¸·Î À¯Çüº¯ÈµÇ¾î CDN_TO_US_DOUBLE ÇÔ¼ö·Î Àü´ÞµË´Ï´Ù. ÀÌ ÇÔ¼ö´Â ȯÀ² ÆÄÀÏ¿¡ ¾×¼¼½ºÇÏ¿© 10Áø¼ö·Î, ´Ù½Ã ¹ÌÈ ´Þ·¯·Î À¯Çüº¯È¯µÇ´Â 2¹è °ª(¹ÌÈ ´Þ·¯ÀÇ ¾ç Ç¥½Ã)À» ¸®ÅÏÇÕ´Ï´Ù.
À¯·Îȸ¦ ¹ÌÈ ´Þ·¯·Î º¯È¯ÇÏ´Â ÇÔ¼ö´Â À§ ¿¹¿Í À¯»çÇÕ´Ï´Ù.
CREATE FUNCTION EURO_TO_US_DOUBL(DOUBLE) RETURNS DOUBLE EXTERNAL NAME '/u/finance/funcdir/currencies!euro2us' LANGUAGE C PARAMETER STYLE DB2SQL NO SQL NOT DETERMINISTIC NO EXTERNAL ACTION FENCED CREATE FUNCTION EURO_TO_US_DEC (DECIMAL(9,2)) RETURNS DECIMAL(9,2) SOURCE EURO_TO_US_DOUBL (DOUBLE) CREATE FUNCTION US_DOLLAR(EURO) RETURNS US_DOLLAR SOURCE EURO_TO_US_DEC (DECIMAL())
1999³â 7¿ù(7/1999) ÇÑ´Þ µ¿¾È ij³ª´Ù ¹× µ¶ÀÏ¿¡¼º¸´Ù ¹Ì±¹¿¡¼ ¾î¶² Á¦Ç°ÀÌ ´õ ¸¹ÀÌ ÆÈ·È´ÂÁö ¾Ë¾Æº¸·Á¸é ´ÙÀ½°ú °°ÀÌ ÇϽʽÿÀ.
SELECT US.PRODUCT_ITEM, US.TOTAL FROM US_SALES AS US, CANADIAN_SALES AS CDN, GERMAN_SALES AS GERMAN WHERE US.PRODUCT_ITEM = CDN.PRODUCT_ITEM AND US.PRODUCT_ITEM = GERMAN.PRODUCT_ITEM AND US.TOTAL > US_DOLLAR (CDN.TOTAL) AND US.TOTAL > US_DOLLAR (GERMAN.TOTAL) AND US.MONTH = 7 AND US.YEAR = 1999 AND CDN.MONTH = 7 AND CDN.YEAR = 1999 AND GERMAN.MONTH = 7 AND GERMAN.YEAR = 1999
¹ÌÈ ´Þ·¯¸¦ ij³ª´Ù ´Þ·¯³ª À¯·ÎÈ¿Í Á÷Á¢ ºñ±³ÇϹǷΠUDF¸¦ »ç¿ëÇÏ¿© ij³ª´Ù ´Þ·¯·Î µÈ ¼ö·®À» ¹ÌÈ ´Þ·¯·Î À¯Çüº¯È¯Çϰí, UDF¸¦ »ç¿ëÇÏ¿© ¼ö·®À» À¯·ÎÈ¿¡¼ ¹ÌÈ ´Þ·¯·Î À¯Çüº¯È¯ÇÕ´Ï´Ù. ¼ö·®ÀÌ ±Ý¾×¸é¿¡¼ ºñ±³°¡´ÉÇÏÁö ¾ÊÀ¸¹Ç·Î, ÀÌµé ¸ðµÎ¸¦ DECIMAL·Î À¯Çüº¯È¯ÇÏ°í º¯È¯µÈ DECIMAL °ªÀ» ºñ±³ÇÒ ¼ö ¾ø½À´Ï´Ù. Áï, ±Ý¾×Àº µ¿ÀÏÇÑ ÅëÈ·Î ÀÌ·ç¾îÁ® ÀÖÁö ¾Ê½À´Ï´Ù.
À¯·ÎÈ¿¡ ´ëÇØ SUMÀ» Áö¿øÇϵµ·Ï ³»Àå SUM ÇÔ¼ö¿¡ Àü·¡ UDF¸¦ Á¤ÀÇÇß´Ù°í °¡Á¤ÇϽʽÿÀ.
CREATE FUNCTION SUM (EUROS) RETURNS EUROS SOURCE SYSIBM.SUM (DECIMAL())
1994³â °¢ Á¦Ç°¿¡ ´ëÇØ µ¶ÀÏ¿¡¼ÀÇ ÃÑ ÆÇ¸Å¾×À» ¾Ë°íÀÚ ÇÕ´Ï´Ù. ¹ÌÈ ´Þ·¯·Î ÃÑ ÆÇ¸Å¾×À» ¾Ë¾Æº¼ ¼ö ÀÖ½À´Ï´Ù.
SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL)) FROM GERMAN_SALES WHERE YEAR = 1994 GROUP BY PRODUCT_ITEM
À§¿Í À¯»çÇÑ ¹æ½ÄÀ¸·Î ¹ÌÈ ´Þ·¯·Î SUM ÇÔ¼ö¸¦ Á¤ÀÇÇÏÁö ¾ÊÀº ÇÑ SUM (us_dollar (total))À» ÀÛ¼ºÇÒ ¼ö ¾ø½À´Ï´Ù.
»õ·Î¿î ±¸Á÷ÀÚ°¡ ÀÛ¼ºÇÑ ¾ç½ÄÀ» µ¥ÀÌÅͺ£À̽º¿¡ ÀúÀåÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. ÀÛ¼ºµÈ ¾ç½ÄÀ» Ç¥½ÃÇÏ´Â µ¥ »ç¿ëµÇ´Â ¹®ÀÚ¿ÀÌ Æ÷ÇÔµÈ È£½ºÆ® º¯¼ö¸¦ Á¤ÀÇÇÕ´Ï´Ù.
EXEC SQL BEGIN DECLARE SECTION; SQL TYPE IS CLOB(32K) hv_form; EXEC SQL END DECLARE SECTION; /* Code to fill hv_form */ INSERT INTO APPLICATIONS VALUES (134523, 'Peter Holland', CURRENT DATE, :hv_form)
DB2¿¡¼´Â ±¸º° À¯ÇüÀÇ ¼Ò½º À¯Çü ÀνºÅϽº¸¦ ±× ±¸º° À¯ÇüÀ» »ç¿ëÇÏ´Â ¸ñÇ¥¿¡ ÁöÁ¤ÇÒ ¼ö ÀÖÀ¸¹Ç·Î, À¯Çüº¯È¯ ÇÔ¼ö¸¦ ¸í½ÃÀûÀ¸·Î È£ÃâÇÏ¿© ¹®ÀÚ¿À» ±¸º° À¯Çü personal.application_formÀ¸·Î º¯È¯ÇÏÁö ¸¶½Ê½Ã¿À.
µ¿Àû SQL¿¡¼ ¿¹: ±¸º° À¯Çü°ú °ü·ÃµÈ ÁöÁ¤¿¡ Á¦°øµÈ °Í°ú °°Àº ¸í·É¹®À» »ç¿ëÇÏ·Á¸é ´ÙÀ½°ú °°ÀÌ ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
EXEC SQL BEGIN DECLARE SECTION; long id; char name[30]; SQL TYPE IS CLOB(32K) form; char command[80]; EXEC SQL END DECLARE SECTION; /* Code to fill host variables */ strcpy(command,"INSERT INTO APPLICATIONS VALUES"); strcat(command,"(?, ?, CURRENT DATE, CAST (? AS CLOB(32K)))"); EXEC SQL PREPARE APP_INSERT FROM :command; EXEC SQL EXECUTE APP_INSERT USING :id, :name, :form;
¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ À¯ÇüÀÌ ±¸º° À¯Çü Ä÷³¿¡ ÁöÁ¤ÇÒ ¼ö ÀÖ´Â À¯ÇüÀÎ CLOB(32K)¶ó´Â °ÍÀ» DB2¿¡°Ô ¾Ë¸®±â À§ÇØ DB2ÀÇ À¯Çüº¯È¯ ½ºÆåÀ» »ç¿ëÇß½À´Ï´Ù. È£½ºÆ® ¾ð¾î°¡ ±¸º° À¯ÇüÀ» Áö¿øÇÏÁö ¾ÊÀ¸¹Ç·Î, ±¸º° À¯ÇüÀÇ È£½ºÆ® º¯¼ö¸¦ ¼±¾ðÇÒ ¼ö ¾ø½À´Ï´Ù. µû¶ó¼, ¸Å°³º¯¼ö Ç¥½Ã¹®ÀÚ À¯ÇüÀ» ±¸º° À¯ÇüÀ¸·Î ÁöÁ¤ÇÒ ¼ö ¾ø½À´Ï´Ù.
¿¹: ±¸º° À¯Çü°ú °ü·ÃµÈ Àü·¡ UDF¿¡¼ À¯·ÎÈ¿¡¼ Àü·¡ÇÑ UDF¿Í À¯»çÇϰÔ, ¹ÌÈ ´Þ·¯ ¹× ij³ª´Ù ´Þ·¯·Î SUMÀ» Áö¿øÇÏ´Â ³»Àå SUM ÇÔ¼ö¿¡ µÎ °³ÀÇ Àü·¡ UDF¸¦ Á¤ÀÇÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
CREATE FUNCTION SUM (CANADIAN_DOLLAR) RETURNS CANADIAN_DOLLAR SOURCE SYSIBM.SUM (DECIMAL()) CREATE FUNCTION SUM (US_DOLLAR) RETURNS US_DOLLAR SOURCE SYSIBM.SUM (DECIMAL())
ÀÌÁ¦ °¨µ¶ÀÚ°¡ °¢ Á¦Ç°ÀÇ °¢ ±¹°¡¿¡¼ÀÇ ¿¬°£ ÃÑ ÆÇ¸Å¾×À» ¹ÌÈ ´Þ·¯·Î º°µµÀÇ Å×ÀÌºí¿¡¼ À¯Áöº¸¼öÇϵµ·Ï ¿äûÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
CREATE TABLE US_SALES_94 (PRODUCT_ITEM INTEGER, TOTAL US_DOLLAR) CREATE TABLE GERMAN_SALES_94 (PRODUCT_ITEM INTEGER, TOTAL US_DOLLAR) CREATE TABLE CANADIAN_SALES_94 (PRODUCT_ITEM INTEGER, TOTAL US_DOLLAR) INSERT INTO US_SALES_94 SELECT PRODUCT_ITEM, SUM (TOTAL) FROM US_SALES WHERE YEAR = 1994 GROUP BY PRODUCT_ITEM INSERT INTO GERMAN_SALES_94 SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL)) FROM GERMAN_SALES WHERE YEAR = 1994 GROUP BY PRODUCT_ITEM INSERT INTO CANADIAN_SALES_94 SELECT PRODUCT_ITEM, US_DOLLAR (SUM (TOTAL)) FROM CANADIAN_SALES WHERE YEAR = 1994 GROUP BY PRODUCT_ITEM
¿©·¯ ±¸º° À¯ÇüÀº ¼·Î Á÷Á¢ ÁöÁ¤µÉ ¼ö ¾øÀ¸¹Ç·Î ij³ª´Ù ´Þ·¯¿Í À¯·ÎÈ·Î µÈ ±Ý¾×À» ¹Ì±¹ ´Þ·¯·Î À¯Çüº¯È¯ÇÕ´Ï´Ù. ±¸º° À¯ÇüÀº ÀÚü ¼Ò½º À¯ÇüÀ¸·Î¸¸ À¯Çüº¯È¯µÉ ¼ö ÀÖÀ¸¹Ç·Î À¯Çüº¯È¯ ½ºÆå ±¸¹®À» »ç¿ëÇÏÁö ¸¶½Ê½Ã¿À.
ȸ»çÀÇ ¸ðµç Á¦Ç°¿¡ ´ëÇÑ ÃÑ ÆÇ¸Å¾×À» Æ÷ÇÔÇÏ¿© ¹Ì±¹ÀÎ »ç¿ëÀÚ¿¡°Ô ºä¸¦ Á¦°øÇϰíÀÚ ÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
CREATE VIEW ALL_SALES AS SELECT PRODUCT_ITEM, MONTH, YEAR, TOTAL FROM US_SALES UNION SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL) FROM CANADIAN_SALES UNION SELECT PRODUCT_ITEM, MONTH, YEAR, US_DOLLAR (TOTAL) FROM GERMAN_SALES
±¸º° À¯ÇüÀº °°Àº ±¸º° À¯Çü°ú¸¸ ȣȯµÇ¹Ç·Î, ij³ª´Ù ´Þ·¯¸¦ ¹Ì±¹ ´Þ·¯·Î À¯Çüº¯È¯Çϰí À¯·Îȸ¦ ¹Ì±¹ ´Þ·¯·Î À¯Çüº¯È¯ÇÕ´Ï´Ù. À¯Çüº¯È¯ ½ºÆåÀº ±¸º° À¯Çü°ú ±× ¼Ò½º À¯Çü°£¿¡¸¸ À¯Çüº¯È¯ÇÒ ¼ö ÀÖÀ¸¹Ç·Î, ±¸º° À¯Çü°£¿¡ À¯Çüº¯È¯ÇÒ ¶§ ÇÔ¼ö Ç¥±â¹ýÀ» »ç¿ëÇØ¾ß ÇÕ´Ï´Ù.