ÀÌ Àý¿¡¼´Â »ç¿ëÀÚ Á¤ÀÇ ±¸Á¶È À¯ÇüÀ» Ä÷³ÀÇ À¯ÇüÀ¸·Î »ç¿ëÇÏ´Â ÀÛ¾÷¿¡ °ü·ÃµÈ ÁÖ¿ä Ÿ½ºÅ©¿¡ ´ëÇØ ¼³¸íÇÕ´Ï´Ù. ÀÌ ÀýÀ» Àбâ Àü¿¡ ±¸Á¶È À¯Çü °³¿ä¿¡ ÀÖ´Â Àç·á¿¡ Àͼ÷ÇØ ÀÖ¾î¾ß ÇÕ´Ï´Ù.
±¸Á¶È À¯ÇüÀº Å×À̺í, ºä ¶Ç´Â Ä÷³ÀÇ ¹®¸Æ¿¡¼ »ç¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. ±¸Á¶È À¯ÇüÀ» ÀÛ¼ºÇÒ ¶§ »ç¿ëÀÚ Á¤ÀÇ À¯Çü µ¿Àû°ú À¯Çü ¼Ó¼ºÀ» µÑ´Ù ĸ½¶ÈÇÒ ¼ö ÀÖ½À´Ï´Ù. À¯ÇüÀÇ µ¿ÀÛÀ» Æ÷ÇÔÇÏ·Á¸é CREATE TYPE ¶Ç´Â ALTER TYPE¹®À¸·Î ¸Þ¼Òµå ¼¸íÀ» ÁöÁ¤ÇϽʽÿÀ. ¸Þ¼Òµå ÀÛ¼º¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö(UDFs)¿Í ¸Þ¼Òµå¸¦ ÂüÁ¶ÇϽʽÿÀ.
±×¸² 15´Â ÀÌ Àý¿¡¼ ¿¹·Î »ç¿ëµÈ À¯Çü °èÃþÀ» º¸¿©ÁÝ´Ï´Ù. ·çÆ® À¯ÇüÀº Address_tÀε¥ ±× ³ª¶ó¿¡¼ ÁÖ¼Ò¸¦ ±¸¼ºÇÏ´Â ¹æ¹ýÀÇ ¸î °¡Áö Ãø¸éÀ» ¹Ý¿µÇÏ´Â Ãß°¡ ¼Ó¼ºÀÌ ÀÖ´Â 3°¡Áö ºÎ¼Ó À¯ÇüÀÌ ÀÖ½À´Ï´Ù.
±×¸² 15. Address_t À¯Çü¿¡ ´ëÇÑ ±¸Á¶È À¯Çü °èÃþ
![]() |
CREATE TYPE Address_t AS (street VARCHAR(30), number CHAR(15), city VARCHAR(30), state VARCHAR(10)) MODE DB2SQL; CREATE TYPE Germany_addr_t UNDER Address_t AS (family_name VARCHAR(30)) MODE DB2SQL; CREATE TYPE Brazil_addr_t UNDER Address_t AS (neighborhood VARCHAR(30)) MODE DB2SQL; CREATE TYPE US_addr_t UNDER Address_t AS (zip CHAR(10)) MODE DB2SQL;
¾î¶»°Ô ±¸Á¶È À¯ÇüÀ» µ¥ÀÌÅÍ ·¹Äڵ忡 ¹èÄ¡ÇÏ´ÂÁö¿¡ ´ëÇØ ¿°·ÁÇÏÁö ¾Ê´Â ÇÑ, ±¸Á¶È À¯ÇüÀÇ Ä÷³À¸·Î Å×À̺íÀ» ÀÛ¼ºÇÏ´Â Ãß°¡ ±¸¹®ÀÌ ¾ø½À´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½ ¸í·É¹®Àº Address_t À¯ÇüÀÇ Ä÷³À» Customer_List À¯ÇüȵÇÁö ¾ÊÀº Å×ÀÌºí¿¡ Ãß°¡ÇÕ´Ï´Ù.
ALTER TABLE Customer_List ADD COLUMN Address Address_t;
ÀÌÁ¦ Address_tÀÇ ÀνºÅϽº ¶Ç´Â Address_tÀÇ ºÎ¼Ó À¯ÇüÀº ÀÌ Å×ÀÌºí¿¡ ÀúÀåµÉ ¼ö ÀÖ½À´Ï´Ù. ±¸Á¶È À¯Çü »ðÀÔ¿¡ ´ëÇÑ ³»¿ëÀº ±¸Á¶È À¯Çü °ªÀÌ ÀÖ´Â Çà »ðÀÔÀ» ÂüÁ¶ÇϽʽÿÀ.
±¸Á¶È À¯ÇüÀÌ µ¥ÀÌÅÍ ·¹Äڵ忡 ¹èÄ¡µÇ´Â ¹æ¹ý¿¡ ´ëÇØ °ÆÁ¤ÀÌ µÇ¸é CREATE TYPE¹®¿¡ ÀÖ´Â INLINE LENGTHÀýÀ» »ç¿ëÇÏ¿© ±¸Á¶È À¯Çü Ä÷³ÀÇ ÀνºÅϽº ÃÖ´ë Å©±â¸¦ Ç¥½ÃÇÏ¿© °ªÀÇ ³ª¸ÓÁö¿Í ÇÔ²² Çà¿¡ ÀζóÀÎÀ¸·Î ÀúÀåÇÒ ¼ö ÀÖ½À´Ï´Ù. INLINE LENGTHÀý¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ Á¤º¸´Â SQL ÂüÁ¶¼ÀÇ CREATE TYPE (Structured)¹®À» ÂüÁ¶ÇϽʽÿÀ.
±¸Á¶È À¯Çü ¼Ó¼ºÀ¸·Î À¯ÇüÀ» ÀÛ¼ºÇÒ ¼ö ÀÖÁö¸¸ À̸¦ º¯°æ(»ç¿ëµÇ±â Àü)ÇÏ¿© ÀÌ·± ¼Ó¼ºÀ» Ãß°¡Çϰųª Á¦°ÅÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½ CREATE TYPE¹®¿¡´Â Address_t À¯ÇüÀÇ ¼Ó¼ºÀÌ µé¾î ÀÖ½À´Ï´Ù.
CREATE TYPE Person_t AS (Name VARCHAR(20), Age INT, Address Address_t) REF USING VARCHAR(13) MODE DB2SQL;
Person_t´Â Å×À̺íÀÇ À¯Çü, ÀÏ¹Ý Å×À̺íÀÇ Ä÷³ À¯Çü ¶Ç´Â ´Ù¸¥ ±¸Á¶È À¯ÇüÀÇ ¼Ó¼ºÀ¸·Î »ç¿ëµÉ ¼ö ÀÖ½À´Ï´Ù.
±¸Á¶È À¯ÇüÀ» ÀÛ¼ºÇϸé, DB2´Â ÀÚµ¿À¸·Î ±× À¯ÇüÀÇ ±¸¼ºÀÚ ¸Þ¼Òµå¸¦ »ý¼ºÇϰí À¯ÇüÀÇ ¼Ó¼º¿¡ ´ëÇØ º¯È¯ ¹× °üÂû ¸Þ¼Òµå¸¦ »ý¼ºÇÕ´Ï´Ù. ÀÌ·± ¸Þ¼Òµå¸¦ »ç¿ëÇÏ¿© ±¸Á¶È À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀÛ¼ºÇϰí ÀÌ ÀνºÅϽº¸¦ Å×À̺í Ä÷³¿¡ »ðÀÔÇÒ ¼ö ÀÖ½À´Ï´Ù.
»õ ÇàÀ» Employee À¯ÇüÈ Å×ÀÌºí¿¡ Ãß°¡ÇÏ°í ±× Çà¿¡ ÁÖ¼Ò ¸¦ Æ÷ÇÔ½ÃŲ´Ù°í °¡Á¤ÇϽʽÿÀ. ³»Àå µ¥ÀÌÅÍ À¯Çüó·³ VALUESÀý°ú ÇÔ²² INSERT¸¦ »ç¿ëÇÏ¿© ÀÌ ÇàÀ» Ãß°¡ÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×·¯³ª, °ªÀ» ÁöÁ¤ÇÏ¿© ÁÖ¼Ò¿¡ »ðÀÔÇÏ¸é ½Ã½ºÅÛ¿¡¼ Á¦°øÇÏ´Â ±¸¼ºÀÚ ÇÔ¼ö¸¦ È£ÃâÇÏ¿© °ªÀ» ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù.
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept, Address) VALUES(Employee_t('m'), 'Marie', 35, 005, 55000, BusinessUnit_t(2), US_addr_t ( ) (1) ..street('Bakely Avenue') (2) ..number('555') (3) ..city('San Jose') (4) ..state('CA') (5) ..zip('95141')); (6)
ÀÌÀü ¸í·É¹®Àº ´ÙÀ½ Ÿ½ºÅ©¸¦ ¼öÇàÇÏ¿© US_addr_t À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀÛ¼ºÇÕ´Ï´Ù.
ºñ·Ï Employee Å×ÀÌºí¿¡ ÀÖ´Â Address Ä÷³ÀÇ À¯ÇüÀÌ Address_t À¯ÇüÀ¸·Î Á¤ÀǵǾî À־ ´ëü °¡´É¼ºÀÇ Æ¯¼ºÀº US_addr_t´Â Address_tÀÇ ºÎ¼Ó À¯ÇüÀ̹ǷÎ, ¿©±â¿¡ US_addr_tÀÇ ÀνºÅϽº¸¦ »óÁÖ½Ãų ¼ö ÀÖÀ½À» ÀǹÌÇÕ´Ï´Ù.
À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀÛ¼ºÇÒ ¶§¸¶´Ù ±¸Á¶È À¯ÇüÀÇ °¢ ¼Ó¼º¿¡ ´ëÇÑ º¯È¯ ¸Þ¼Òµå¸¦ ¸í½ÃÀûÀ¸·Î È£ÃâÇÏÁö ¾Êµµ·Ï ¸ðµç ¼Ó¼ºÀ» ÃʱâÈÇÏ´Â SQL º»¹® ±¸¼ºÀÚ ÇÔ¼ö¸¦ Á¤ÀÇÇϽʽÿÀ. ´ÙÀ½ ¿¹¿¡´Â US_addr_t À¯Çü¿¡ ´ëÇÑ SQL º»¹® ±¸¼ºÀÚ ÇÔ¼öÀÇ ¼±¾ðÀÌ µé¾î ÀÖ½À´Ï´Ù.
CREATE FUNCTION US_addr_t (street Varchar(30), number Char(15), city Varchar(30), state Varchar(20), zip Char(10)) RETURNS US_addr_t LANGUAGE SQL RETURN Address_t()..street(street)..number(number) ..city(city)..state(state)..zip(zip);
´ÙÀ½ ¿¹´Â ÀÌÀü ¿¹¿¡¼ SQL º»¹® ±¸¼ºÀÚ ÇÔ¼ö¸¦ È£ÃâÇÏ¿© US_addr_t À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀÛ¼ºÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
INSERT INTO Employee(Oid, Name, Age, SerialNum, Salary, Dept, Address) VALUES(Employee_t('m'), 'Marie', 35, 005, 55000, BusinessUnit_t(2), US_addr_t('Bakely Avenue', '555', 'San Jose', 'CA', '95141'));
±¸Á¶È À¯Çü Ä÷³¿¡¼ ÀÀ¿ëÇÁ·Î±×·¥°ú »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö°¡ µ¥ÀÌÅ͸¦ ¾×¼¼½ºÇÒ ¼ö ÀÖ´Â ¿©·¯ °¡Áö ¹æ¹ýÀÌ ÀÖ½À´Ï´Ù. ¿ÀºêÁ§Æ®¸¦ ´ÜÀÏ °ªÀ¸·Î Ãë±ÞÇÏ·Á¸é, ¸ÕÀú È£½ºÆ® ¾ð¾î ÇÁ·Î±×·¥¿¡ ´ëÇÑ ¸ÊÇÎ ÀÛ¼º: º¯È¯ ÇÔ¼ö¿¡ ¼³¸íµÇ¾î ÀÖ´Â º¯È¯ ÇÔ¼ö¸¦ Á¤ÀÇÇØ¾ß ÇÕ´Ï´Ù. ÀÏ´Ü ¿Ã¹Ù¸¥ º¯È¯ ÇÔ¼ö¸¦ Á¤ÀÇÇÏ¸é ´Ù¸¥ °ª¿¡¼ ¼±ÅÃÇÒ ¼ö ÀÖ´Â °Í¸¸Å ±¸Á¶È ¿ÀºêÁ§Æ®¸¦ ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT Name, Dept, Address FROM Employee WHERE Salary > 20000;
±×·¯³ª, ÀÌ Àý¿¡¼ DB2 ³»Àå °üÂû ¹× º¯È¯ ¸Þ¼Òµå¸¦ È£ÃâÇÏ¿© ¿ÀºêÁ§Æ®ÀÇ °³º°ÀûÀÎ ¼Ó¼ºÀ» ¸í½ÃÀûÀ¸·Î ¾×¼¼½ºÇÏ´Â ¹æ¹ýÀ» ¼³¸íÇÕ´Ï´Ù. ³»Àå ¸Þ¼Òµå¿¡¼´Â º¯È¯ ÇÔ¼ö¸¦ Á¤ÀÇÇÏÁö ¾Ê¾Æµµ µË´Ï´Ù.
¿ÀºêÁ§Æ®ÀÇ °³º°ÀûÀÎ ¼Ó¼ºÀ» ¸í½ÃÀûÀ¸·Î ¾×¼¼½ºÇÏ·Á¸é ÀÌ·± ¼Ó¼º¿¡ ´ëÇØ DB2 ³»Àå °üÂû ¸Þ¼Òµå¸¦ È£ÃâÇϽʽÿÀ. °üÂû ¸Þ¼Òµå¸¦ »ç¿ëÇÏ¸é ¿ÀºêÁ§Æ®¸¦ ´ÜÀÏ °ªÀ¸·Î Ãë±ÞÇÏ´Â ´ë½Å ¼Ó¼ºÀ» °³º°ÀûÀ¸·Î °Ë»öÇÒ ¼ö ÀÖ½À´Ï´Ù.
´ÙÀ½ ¿¹´Â Address Ä÷³¿¡ ´ëÇØ Á¤ÀÇµÈ Á¤Àû À¯ÇüÀÎ Address_t¿¡¼ °üÂû ¸Þ¼Òµå¸¦ È£ÃâÇÏ¿© Address Ä÷³¿¡ ÀÖ´Â µ¥ÀÌÅ͸¦ ¾×¼¼½ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT Name, Dept, Address..street, Address..number, Address..city, Address..state FROM Employee WHERE Salary > 20000;
ÁÖ: | DB2´Â <type-name>..<method-name>() ¶Ç´Â <type-name>..<method-name>À» »ç¿ëÇÏ¿© ¸Å°³º¯¼ö¸¦ ÃëÇÏÁö ¾Ê´Â ¸Þ¼Òµå¸¦ È£ÃâÇÒ ¼ö ÀÖ°Ô Çϴµ¥, ¿©±â¼ type-nameÀº ±¸Á¶È À¯ÇüÀÇ À̸§À» ³ªÅ¸³»°í attribute-nameÀº ¸Å°³º¯¼ö¸¦ ÃëÇÏÁö ¾Ê´Â ¸Þ¼ÒµåÀÇ À̸§À» ³ªÅ¸³À´Ï´Ù. |
°üÂû ¸Þ¼Òµå¸¦ »ç¿ëÇÏ¿© ´ÙÀ½°ú °°ÀÌ È£½ºÆ® º¯¼ö¿¡¼ °¢ ¼Ó¼ºÀ» ¼±ÅÃÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
SELECT Name, Dept, Address..street, Address..number, Address..city, Address..state INTO :name, :dept, :street, :number, :city, :state FROM Employee WHERE Empno = '000250';
Employee Å×ÀÌºí¿¡¼ ÁÖ¼Ò´Â Address_t, US_addr_t, Brazil_addr_t ±×¸®°í Germany_addr_tÀÇ 4 °¡Áö À¯ÇüÀ¸·Î µÇ¾î ÀÖ½À´Ï´Ù. ÀÌÀü ¿¹´Â Address_t Á¤Àû À¯ÇüÀÇ ¼Ó¼º¸¸ ¾×¼¼½ºÇÕ´Ï´Ù. Address_tÀÇ ºÎ¼Ó À¯Çü Áß Çϳª¿¡¼ °ªÀÇ ¼Ó¼ºÀ» ¾×¼¼½ºÇÏ·Á¸é TREAT Ç¥Çö½ÄÀ» »ç¿ëÇÏ¿© DB2¿¡°Ô ƯÁ¤ ¿ÀºêÁ§Æ®°¡ US_addr_t, Germany_addr_t ¶Ç´Â Brazil_addr_t À¯ÇüÀÌ µÉ ¼ö ÀÖÀ½À» ¾Ë·ÁÁÙ ¼ö ÀÖ½À´Ï´Ù. TREAT Ç¥Çö½ÄÀº ±¸Á¶È À¯Çü Ç¥Çö½ÄÀ» ´ÙÀ½ Á¶È¸¿¡¼¿Í °°ÀÌ ±× ºÎ¼Ó À¯Çü Áß Çϳª·Î À¯Çüº¯È¯ÇÕ´Ï´Ù.
SELECT Name, Dept, Address..street, Address..number, Address..city, Address..state, CASE WHEN Address IS OF (US_addr_t) THEN TREAT(Address AS US_addr_t)..zip WHEN Address IS OF (Germany_addr_t) THEN TREAT (Address AS Germany_addr_t)..family_name WHEN Address IS OF (Brazil_addr_t) THEN TREAT (Address AS Brazil_addr_t)..neighborhood ELSE NULL END FROM Employee WHERE Salary > 20000;
ÁÖ: | ÀÌÀü Á¢±Ù ¹æ¹ý¸¸ »ç¿ëÇÏ¿© ºÎ¼Ó À¯ÇüÀÇ ¼Ó¼ºÀÌ ¸ðµÎ °°Àº À¯ÇüÀ̰ųª °°Àº À¯ÇüÀ¸·Î À¯Çüº¯È¯ÇÒ ¼ö ÀÖÀ» ¶§ ±¸Á¶È À¯ÇüÀÇ ºÎ¼Ó À¯ÇüÀ» °áÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌÀü ¿¹¿¡¼ zip, family_name, neighborhood´Â ¸ðµç VARCHAR ¶Ç´Â CHAR À¯ÇüÀÌ°í °°Àº À¯ÇüÀ¸·Î À¯Çüº¯È¯ÇÒ ¼ö ÀÖ½À´Ï´Ù. |
TREAT Ç¥Çö½ÄÀ̳ª TYPE ¼ú¾îÀÇ ±¸¹®¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº SQL ÂüÁ¶¼¸¦ ÂüÁ¶ÇϽʽÿÀ.
±¸Á¶È Ä÷³ °ªÀÇ ¼Ó¼ºÀ» º¯°æÇÏ·Á¸é º¯°æÇÒ ¼Ó¼º¿¡ ´ëÇØ º¯È¯ ¸Þ¼Òµå¸¦ È£ÃâÇϽʽÿÀ. ¿¹¸¦ µé¾î, ÁÖ¼ÒÀÇ street ¼Ó¼ºÀ» º¯°æÇÏ·Á¸é street¿¡ ´ëÇØ º¯°æµÉ °ªÀ» °¡Áö°í º¯È¯ ¸Þ¼Òµå¸¦ È£ÃâÇÒ ¼ö ÀÖ½À´Ï´Ù. ¸®ÅÏ °ªÀº streetÀÇ »õ °ªÀÌ µé¾î ÀÖ´Â ÁÖ¼ÒÀÔ´Ï´Ù. ´ÙÀ½ ¿¹´Â street¶ó´Â ¼Ó¼º¿¡ ´ëÇØ º¯È¯ ¸Þ¼Òµå¸¦ È£ÃâÇÏ¿© Employee Å×ÀÌºí¿¡¼ ÁÖ¼Ò À¯ÇüÀ» °»½ÅÇÕ´Ï´Ù.
UPDATE Employee SET Address = Address..street('Bailey') WHERE Address..street = 'Bakely';
´ÙÀ½ ¿¹´Â ÀÌÀü ¿¹¿Í °°Àº °»½ÅÀ» ¼öÇàÇÏÁö¸¸ °»½Å¿¡ ´ëÇØ ±¸Á¶È Ä÷³À» ¸í¸íÇÏ´Â ´ë½Å SETÀýÀº street¶ó´Â ¼Ó¼º¿¡ ´ëÇØ º¯È¯ ¸Þ¼Òµå¸¦ Á÷Á¢ ¾×¼¼½ºÇÕ´Ï´Ù.
UPDATE Employee SET Address..street = 'Bailey' WHERE Address..street = 'Bakely';
±âŸ À¯Çü-°ü·Ã ³»Àå ÇÔ¼ö¿¡ ¼³¸íµÈ ´ë·Î ³»Àå ÇÔ¼ö¸¦ »ç¿ëÇÏ¿© ƯÁ¤ À¯ÇüÀÇ À̸§, ½ºÅ°¸¶ ¶Ç´Â ³»ºÎ À¯Çü ID¸¦ ¸®ÅÏÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½ ¸í·É¹®Àº 'Iris'¶ó°í ÇÏ´Â Á÷¿ø¿¡ ¿¬°üµÈ ÁÖ¼ÒÀÇ Á¤È®ÇÑ À¯ÇüÀ» ¸®ÅÏÇÕ´Ï´Ù.
SELECT TYPE_NAME(Address) FROM Employee WHERE Name='Iris';
º¯È¯ ÇÔ¼ö´Â º¸Åë ½ÖÀ¸·Î ÀϾ´Ï´Ù. Çϳª´Â FROM SQL º¯È¯ ÇÔ¼öÀÌ°í ´Ù¸¥ Çϳª´Â TO SQL º¯È¯ ÇÔ¼öÀÔ´Ï´Ù. FROM SQL ÇÔ¼ö´Â ±¸Á¶È À¯Çü ¿ÀºêÁ§Æ®¸¦ ¿ÜºÎ ÇÁ·Î±×·¥¿¡¼ ½ÇÇàµÉ ¼ö ÀÖ´Â À¯ÇüÀ¸·Î º¯È¯Çϰí TO SQL ÇÔ¼ö¸¦ ¿ÀºêÁ§Æ®¸¦ ±¸¼ºÇÕ´Ï´Ù. º¯È¯ ÇÔ¼ö¸¦ ÀÛ¼ºÇÒ ¶§ º¯È¯ ÇÔ¼öÀÇ °¢ ³í¸®Àû ½ÖÀ» ±×·ì¿¡ ³Ö½À´Ï´Ù. º¯È¯ ±×·ì À̸§Àº ÁÖ¾îÁø ±¸Á¶È À¯Çü¿¡ ´ëÇØ ÀÌ·± ÇÔ¼öÀÇ ½ÖÀ» ½Äº°ÇÕ´Ï´Ù.
º¯È¯ ÇÔ¼ö¸¦ »ç¿ëÇϱâ Àü¿¡ CREATE TRANSFORM¹®À» »ç¿ëÇÏ¿© º¯È¯ ÇÔ¼ö¸¦ ±×·ì À̸§ ¹× À¯Çü¿¡ ¿¬°ü½ÃÄÑ¾ß ÇÕ´Ï´Ù. CREATE TRANSFORM¹®Àº Çϳª ÀÌ»óÀÇ ±âÁ¸ ÇÔ¼ö¸¦ ½Äº°Çϰí À̸¦ º¯È¯ ÇÔ¼ö·Î »ç¿ëÇÕ´Ï´Ù. ´ÙÀ½ ¿¹´Â Address_t À¯Çü¿¡ ´ëÇØ º¯È¯ ÇÔ¼ö·Î »ç¿ëµÉ ÇÔ¼öÀÇ µÎ ½ÖÀ» ¸í¸íÇÕ´Ï´Ù. ¸í·É¹®Àº func_group°ú client_groupÀÇ µÎ º¯È¯ ±×·ìÀ» ÀÛ¼ºÇÏ´Â µ¥, °¢°¢ FROM SQL º¯È¯°ú TO SQL º¯È¯À¸·Î ±¸¼ºµË´Ï´Ù.
CREATE TRANSFORM FOR Address_t func_group ( FROM SQL WITH FUNCTION addresstofunc, TO SQL WITH FUNCTION functoaddress ) client_group ( FROM SQL WITH FUNCTION stream_to_client, TO SQL WITH FUNCTION stream_from_client ) ;
CREATE TRANSFORM¹®¿¡ ´õ ¸¹Àº ±×·ìÀ» Ãß°¡ÇÏ¿© Ãß°¡ ÇÔ¼ö¸¦ Address_t¿¡ ¿¬°ü½Ãų ¼ö ÀÖ½À´Ï´Ù. º¯È¯ Á¤ÀǸ¦ º¯°æÇÏ·Á¸é CREATE TRANSFORM¹®À» Ãß°¡ ÇÔ¼ö¿Í ÇÔ²² Àç¹ßÇàÇØ¾ß ÇÕ´Ï´Ù. ¿©·¯ È£½ºÆ® ¾ð¾î ÇÁ·Î±×·¥¿¡ ´ëÇØ Ŭ¶óÀÌ¾ðÆ® ÇÔ¼ö¸¦ »ç¿ëÀÚ Á¤ÀÇÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, C¿¡ ´ëÇØ Çϳª ±×¸®°í Java¿¡ ´ëÇØ Çϳª »ç¿ëÀÚ Á¤ÀÇÇÕ´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ÀÇ ¼º´ÉÀ» ÃÖÀûÈÇÏ·Á¸é ¿ÀºêÁ§Æ® ¼Ó¼ºÀÇ ºÎ¼Ó ÁýÇÕ¿¡ ´ëÇØ¼¸¸ º¯È¯ÀÌ Àû¿ëµÇ¾î¾ß ÇÕ´Ï´Ù. ¶Ç´Â VARCHAR¸¦ ¿ÀºêÁ§Æ®ÀÇ Å¬¶óÀÌ¾ðÆ® Ç¥ÇöÀ¸·Î »ç¿ëÇÏ´Â º¯È¯ Çϳª¿Í BLOB¸¦ »ç¿ëÇÏ´Â º¯È¯ Çϳª¸¦ ¿øÇÒ ¼ö ÀÖ½À´Ï´Ù.
SQL¹® DROP TRANSFORMÀ» »ç¿ëÇÏ¿© À¯Çü¿¡¼ º¯È¯ ÇÔ¼öÀÇ ¿¬°üÀ» ÇØÁ¦½ÃŰ½Ê½Ã¿À. DROP TRANSFORM¹®À» ½ÇÇàÇÑ ´ÙÀ½ ÇÔ¼ö´Â °è¼Ó Á¸ÀçÇÏÁö¸¸ ´õ ÀÌ»ó ÀÌ À¯Çü¿¡ ´ëÇØ º¯È¯ ÇÔ¼ö·Î »ç¿ëµÇÁö ¾Ê½À´Ï´Ù. ´ÙÀ½ ¿¹´Â Address_t À¯Çü¿¡ ´ëÇØ func_group º¯È¯ ÇÔ¼öÀÇ Æ¯Á¤ ±×·ìÀ» ¿¬°üÇØÁ¦Çϰí, Address_t À¯Çü¿¡ ´ëÇØ ¸ðµç º¯È¯ ÇÔ¼ö¸¦ ¿¬°üÇØÁ¦ÇÕ´Ï´Ù.
DROP TRANSFORMS func_group FOR Address_t; DROP TRANSFORMS ALL FOR Address_t;
º¯È¯ ±×·ì À̸§Àº ±ÔÁ¤µÇÁö ¾ÊÀº ½Äº°ÀÚÀÔ´Ï´Ù. Áï, À̴ ƯÁ¤ ½ºÅ°¸¶¿¡ ¿¬°üµÇ¾î ÀÖÁö ¾Ê½À´Ï´Ù. DB2¿¡¼ ºÎ¼Ó À¯Çü µ¥ÀÌÅÍ °Ë»ö(¹ÙÀÎµå¾Æ¿ô)¿¡ ¼³¸íµÈ ´ë·Î ºÎ¼Ó À¯Çü ¸Å°³º¯¼ö¸¦ ó¸®Çϱâ À§ÇØ º¯È¯À» ÀÛ¼ºÇÏÁö ¾Ê´Â ÇÑ, ¸ðµç ±¸Á¶È À¯Çü¿¡ ´Ù¸¥ º¯È¯ ±×·ì À̸§À» ÁöÁ¤ÇÏÁö ¸»¾Æ¾ß ÇÕ´Ï´Ù. °°Àº ÇÁ·Î±×·¥ ¶Ç´Â °°Àº SQL¹®¿¡¼ °ü·ÃµÇÁö ¾ÊÀº ¿©·¯ À¯ÇüÀ» »ç¿ëÇØ¾ß ÇϹǷÎ, º¯È¯ ±×·ìÀ» º¯È¯ ÇÔ¼ö¿¡¼ ¼öÇàµÇ´Â Ÿ½ºÅ©¿¡ µû¶ó ¸í¸íÇØ¾ß ÇÕ´Ï´Ù.
º¯È¯ ±×·ìÀÇ À̸§Àº ÀϹÝÀûÀ¸·Î À¯Çü À̸§¿¡ ÀÇÁ¸ÇÏÁö ¾Ê°í ¶Ç´Â ¼öÇàÇÏ´Â ÇÔ¼ö¸¦ ¹Ý¿µÇϰųª º¯È¯ ÇÔ¼öÀÇ ³í¸®(¼·Î ´Ù¸¥ À¯Çü°£¿¡´Â ¸Å¿ì ´Ù¸¦ ¼ö ÀÖÀ½)¸¦ ¹Ý¿µÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, TO¿Í FROM SQL ÇÔ¼ö º¯È¯ÀÌ Á¤ÀǵǴ ±×·ì¿¡ ´ëÇØ func_group ¶Ç´Â object_functions À̸§À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. TO¿Í FROM SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯ÀÌ µé¾î ÀÖ´Â ±×·ì¿¡ ´ëÇØ client_group ¶Ç´Â program_group À̸§À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
´ÙÀ½ ¿¹¿¡¼ Address_t¿Í Polygon À¯ÇüÀº ¸Å¿ì ´Ù¸¥ º¯È¯À» »ç¿ëÇÏÁö¸¸ °°Àº ÇÔ¼ö ±×·ì À̸§À» »ç¿ëÇÕ´Ï´Ù.
CREATE TRANSFORM FOR Address_t func_group (TO SQL WITH FUNCTION functoaddress, FROM SQL WITH FUNCTION addresstofunc ); CREATE TRANSFORM FOR Polygon func_group (TO SQL WITH FUNCTION functopolygon, FROM SQL WITH FUNCTION polygontofunc);
ÀÏ´Ü º¯È¯ ±×·ì ÁöÁ¤ À§Ä¡ÀÇ ¼³¸í´ë·Î ÀûÇÕÇÑ »óȲ¿¡¼ º¯È¯ ±×·ìÀ» func_groupÀ¸·Î ¼³Á¤Çϸé DB2´Â ÁÖ¼Ò ¶Ç´Â ´Ù°¢ÇüÀ» ¹ÙÀεåÀÎÇϰųª ¹ÙÀÎµå¾Æ¿ôÇÒ ¶§¸¶´Ù ¿Ã¹Ù¸¥ º¯È¯ ÇÔ¼ö¸¦ È£ÃâÇÕ´Ï´Ù.
Á¦ÇÑ»çÇ×: | 'SYS' ¹®ÀÚ¿°ú ÇÔ²² º¯È¯ ±×·ìÀ» ½ÃÀÛÇÏÁö ¸¶½Ê½Ã¿À. ÀÌ ±×·ìÀº DB2¿ëÀ¸·Î ¿¹¾àµÇ¾î ÀÖ½À´Ï´Ù. |
¿ÜºÎ ÇÔ¼ö³ª ¸Þ¼Òµå¸¦ Á¤ÀÇÇÏ°í º¯È¯ ±×·ì À̸§À» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é DB2´Â DB2_FUNCTION À̸§À» »ç¿ëÇÏ°í ±× ±×·ì À̸§ÀÌ ÁÖ¾îÁø ±¸Á¶È À¯Çü¿¡ ´ëÇØ ÁöÁ¤µÇ¾ú´Ù°í °£ÁÖÇÕ´Ï´Ù. ÁÖ¾îÁø ±¸Á¶È À¯ÇüÀ» ÂüÁ¶Çϴ Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥À» »çÀü ó¸® ÄÄÆÄÀÏÇÒ ¶§ ±×·ì À̸§À» ÁöÁ¤ÇÏÁö ¾ÊÀ¸¸é DB2´Â DB2_PROGRAMÀ̶ó°í ÇÏ´Â ±×·ì À̸§À» »ç¿ëÇÏ°í ´Ù½Ã ±×·ì À̸§ÀÌ ±× À¯Çü¿¡ ´ëÇØ Á¤ÀǵǾú´Ù°í °£ÁÖÇÕ´Ï´Ù.
ÀÌ ±âº» µ¿ÀÛÀº ¸î °æ¿ì Æí¸®ÇÏÁö¸¸ Á» ´õ º¹ÀâÇÑ µ¥ÀÌÅͺ£À̽º ½ºÅ°¸¶¿¡¼´Â º¯È¯ ±×·ì À̸§¿¡ ´ëÇØ ¾à°£ ´õ ±¤¹üÀ§ÇÑ ±ÔÄ¢ÀÌ ÇÊ¿äÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, À¯ÇüÀ» ¹ÙÀÎµå¾Æ¿ôÇÒ ´Ù¸¥ ¾ð¾î¿¡ ´Ù¸¥ ±×·ì À̸§À» »ç¿ëÇÏ´Â µ¥ µµ¿òÀ» ÁÝ´Ï´Ù.
ÁÖ¾îÁø ±¸Á¶È À¯Çü¿¡ ´ëÇØ Á¤ÀÇµÈ º¯È¯ ±×·ìÀÌ ¸¹À» ¶§ ÇÁ·Î±×·¥ ¶Ç´Â ƯÁ¤ SQL¹®¿¡¼ ±× À¯Çü¿¡ »ç¿ëÇÒ º¯È¯ ±×·ìÀ» ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. ´ÙÀ½°ú °°ÀÌ 3°¡Áö °æ¿ì¿¡ º¯È¯ ±×·ìÀ» ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù.
CREATE FUNCTION°ú CREATE METHOD¹®Àº LANGUAGEÀýÀÇ °ªÀÌ SQLÀÌ ¾Æ´Ò ¶§¸¸ À¯È¿ÇÑ TRANSFORM GROUPÀýÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ°Ô ÇÕ´Ï´Ù. SQL ¾ð¾î ÇÔ¼ö¿¡¼´Â º¯È¯ÀÌ ÇÊ¿ä¾øÁö¸¸ ¿ÜºÎ ÇÔ¼ö¿¡¼´Â º¯È¯ÀÌ ÇÊ¿äÇÕ´Ï´Ù. TRANSFORM GROUPÀýÀ» ÅëÇØ ÁÖ¾îÁø ÇÔ¼ö ¶Ç´Â ¸Þ¼Òµå¿¡ ´ëÇØ ±¸Á¶È À¯Çü ¸Å°³º¯¼ö ¹× °á°ú¿¡ »ç¿ëµÇ´Â TO SQL°ú FROM SQL º¯È¯ÀÌ µé¾î ÀÖ´Â º¯È¯ ±×·ìÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½ ¿¹¿¡¼ CREATE FUNCTION°ú CREATE METHOD¹®Àº TO SQL°ú FROM SQL º¯È¯¿¡ ´ëÇØ func_group º¯È¯ ±×·ìÀ» ÁöÁ¤ÇÕ´Ï´Ù.
CREATE FUNCTION stream_from_client (VARCHAR (150)) RETURNS Address_t ... TRANSFORM GROUP func_group EXTERNAL NAME 'addressudf!address_stream_from_client' ... CREATE METHOD distance ( point ) FOR polygon RETURNS integer : TRANSFORM GROUP func_group ;
µ¿Àû SQLÀ» »ç¿ëÇϸé CURRENT DEFAULT TRANSFORM GROUP Ư¼ö ·¹Áö½ºÅ͸¦ ¼³Á¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ Æ¯¼ö ·¹Áö½ºÅÍ´Â Á¤Àû SQL¹®À̳ª ¿ÜºÎ ÇÔ¼ö ¶Ç´Â ¸Þ¼Òµå¿ÍÀÇ ¸Å°³º¯¼ö ¹× °á°ú ±³È¯¿¡ »ç¿ëµÇÁö ¾Ê½À´Ï´Ù. SET CURRENT DEFAULT TRANSFORM GROUP¹®À» »ç¿ëÇÏ¿© µ¿Àû SQL¹®¿¡ ´ëÇØ ±âº» º¯È¯ ±×·ìÀ» ¼³Á¤ÇϽʽÿÀ.
SET CURRENT DEFAULT TRANSFORM GROUP = client_group;
Á¤Àû SQL¿¡ ´ëÇØ PRECOMPILE ¶Ç´Â BIND ¸í·É¿¡¼ TRANSFORM GROUP ¿É¼ÇÀ» »ç¿ëÇÏ¿© Á¤Àû SQL¹®¿¡¼ »ç¿ëµÇ´Â Á¤Àû º¯È¯ ±×·ìÀ» ÁöÁ¤ÇÔÀ¸·Î½á ´Ù¾çÇÑ À¯ÇüÀÇ °ªÀ» È£½ºÆ® ÇÁ·Î±×·¥°ú ±³È¯ÇϽʽÿÀ. Á¤Àû º¯È¯ ±×·ìÀº µ¿Àû SQL¹®¿¡ Àû¿ëµÇÁö ¾Ê°Å³ª ¿ÜºÎ ÇÔ¼ö ¶Ç´Â ¸Þ¼Òµå¿ÍÀÇ ¸Å°³º¯¼ö ¹× °á°ú ±³È¯¿¡ Àû¿ëµÇÁö ¾Ê½À´Ï´Ù. PRECOMPILE ¶Ç´Â BIND ¸í·É¿¡¼ Á¤Àû º¯È¯ ±×·ìÀ» ÁöÁ¤ÇÏ·Á¸é TRANSFORM GROUPÀýÀ» »ç¿ëÇϽʽÿÀ.
PRECOMPILE ... TRANSFORM GROUP client_group ... ;
PRECOMPILE ¹× BIND ¸í·É¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº Command Reference¸¦ ÂüÁ¶ÇϽʽÿÀ.
ºñ·Ï °Ë»ö ¼Ó¼º¿¡ ¼³¸íµÈ ´ë·Î ÀÀ¿ëÇÁ·Î±×·¥Àº Á÷Á¢ Àüü ¿ÀºêÁ§Æ®¸¦ ¼±ÅÃÇÒ ¼ö ¾ø¾îµµ ¿ÀºêÁ§Æ®ÀÇ °³º°ÀûÀÎ ¼Ó¼ºÀ¸·Î ÀÀ¿ëÇÁ·Î±×·¥¿¡ ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥Àº ±¸¼ºÀÚ ÇÔ¼öÀÇ È£Ãâ °á°ú¸¦ »ðÀÔÇÒ ¼ö À־ º¸Åë Àüü ¿ÀºêÁ§Æ®¸¦ Á÷Á¢ »ðÀÔÇÏÁö ¾Ê½À´Ï´Ù.
INSERT INTO Employee(Address) VALUES (Address_t());
¼¹ö¿Í Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥°£¿¡ Àüü ¿ÀºêÁ§Æ®¸¦ ±³È¯ÇÏ·Á¸é º¯È¯ ÇÔ¼ö¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù.
º¯È¯ ÇÔ¼ö´Â DB2°¡ ¿ÀºêÁ§Æ®¸¦ ±× ³»¿ëÀ» ¾×¼¼½ºÇϰųª ¿ÀºêÁ§Æ®¸¦ ¹ÙÀÎµå¾Æ¿ôÇÏ´Â Àß Á¤ÀÇµÈ Çü½ÄÀ¸·Î º¯È¯ÇÏ´Â ¹æ¹ýÀ» Á¤ÀÇÇÕ´Ï´Ù. ´Ù¸¥ º¯È¯ ÇÔ¼ö´Â DB2°¡ ¿ÀºêÁ§Æ®¸¦ µ¥ÀÌÅͺ£À̽º¿¡ ÀúÀåÇϵµ·Ï ¸®ÅÏÇϰųª ¿ÀºêÁ§Æ®¸¦ ¹ÙÀεåÀÎÇÏ´Â ¹æ¹ýÀ» Á¤ÀÇÇÕ´Ï´Ù. ¿ÀºêÁ§Æ®¸¦ ¹ÙÀÎµå¾Æ¿ôÇÏ´Â º¯È¯Àº FROM SQL º¯È¯ ÇÔ¼ö¶ó°í Çϰí Ä÷³À» ¹ÙÀεåÀÎÇÏ´Â º¯È¯Àº TO SQL º¯È¯À̶ó°í ÇÕ´Ï´Ù.
´ëºÎºÐÀÇ °æ¿ì ¿ÀºêÁ§Æ®¸¦ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î Àü´ÞÇÏ´Â °Íº¸°¡ ·çƾÀ̳ª ¿ÜºÎ UDF¿Í ¸Þ¼Òµå·Î Àü´ÞÇÏ´Â º¯È¯ÀÌ ¿©·¯ °¡Áö ÀÖ½À´Ï´Ù. ¿ÀºêÁ§Æ®¸¦ ¿ÜºÎ ·çƾÀ¸·Î Àü´ÞÇÏ¸é ¿ÀºêÁ§Æ®¸¦ ºÐÇØÇÏ¿© ·çƾÀ» ¸Å°³º¯¼ö ¸ñ·ÏÀ¸·Î Àü´ÞÇϱ⠶§¹®ÀÔ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À» »ç¿ëÇÏ¸é ¿ÀºêÁ§Æ®¸¦ BLOB¿Í °°Àº ´ÜÀÏ ³»Àå À¯ÇüÀ» º¯°æÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ ÇÁ·Î¼¼½º¸¦ ¿ÀºêÁ§Æ® ÀÎÄÚµùÀ̶ó°í ÇÕ´Ï´Ù. ÀÌ·± µÎ À¯ÇüÀÇ º¯È¯Àº ÇÔ²² »ç¿ëµË´Ï´Ù.
SQL¹® CREATE TRANSFORMÀ» »ç¿ëÇÏ¿© º¯È¯ ÇÔ¼ö¸¦ ƯÁ¤ ±¸Á¶È À¯Çü°ú ¿¬°ü½Ãų ¼ö ÀÖ½À´Ï´Ù. CREATE TRANSFORM¹®¿¡¼ ÇÔ¼ö´Â º¯È¯ ±×·ì À¸·Î ±×·ìÁö¾îÁú ¼ö ÀÖ½À´Ï´Ù. ƯÁ¤ º¯È¯¿ëÀ¸·Î »ç¿ëµÇ´Â ÇÔ¼ö¸¦ ´õ ½±°Ô ½Äº°ÇÒ ¼ö ÀÖ½À´Ï´Ù. °¢ º¯È¯ ±×·ì¿¡¼ ƯÁ¤ À¯Çü¿¡ ´ëÇØ FROM SQL º¯È¯°ú TO SQL º¯È¯Àº Çϳª ÀÌ»ó µÉ ¼ö ¾ø½À´Ï´Ù.
ÁÖ: | ´ÙÀ½ ÁÖÁ¦´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ Ç×»ó Address_t¿Í °°ÀÌ ¾Ë·ÁÁø Á¤È®ÇÑ À¯ÇüÀ» ¼ö½ÅÇÏ´Â °£´ÜÇÑ °æ¿ì¸¦ ¼³¸íÇÕ´Ï´Ù. ÀÌ·± ÁÖÁ¦´Â ¿ÜºÎ ·çƾ ¶Ç´Â Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥ÀÌ Address_t, Brazil_addr_t, Germany_addr_t ¶Ç´Â US_addr_t¸¦ ¼ö½ÅÇÏ´Â ½Ã³ª¸®¿À¸¦ ¼³¸íÇÏÁö ¾Ê½À´Ï´Ù. ±×·¯³ª, ±âº» ÇÁ·Î¼¼½º¸¦ Á» ´õ º¹ÀâÇÑ °æ¿ì¿¡ Àû¿ëÇϱâ Àü¿¡ ±âº» ÇÁ·Î¼¼½º¸¦ ÀÌÇØÇØ¾ß ÇÕ´Ï´Ù. ÀÌ·¸°Ô º¹ÀâÇÑ °æ¿ì´Â ¿ÜºÎ ·çƾÀ̳ª Ŭ¶óÀÌ¾ðÆ®°¡ À¯Çü ¶Ç´Â ±× ºÎ¼Ó À¯ÇüÀ» µ¿ÀûÀ¸·Î ó¸®ÇØ¾ß ÇÏ´Â °æ¿ìÀÔ´Ï´Ù. ºÎ¼Ó À¯Çü ÀνºÅϽº¸¦ µ¿ÀûÀ¸·Î ó¸®ÇÏ´Â ¹æ¹ý¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº DB2¿¡¼ ºÎ¼Ó À¯Çü µ¥ÀÌÅÍ °Ë»ö(¹ÙÀÎµå¾Æ¿ô)À» ÂüÁ¶ÇϽʽÿÀ. |
ÀÌ Àý¿¡¼´Â ÇÔ¼ö º¯È¯À̶ó´Â º¯È¯ÀÇ Æ¯Á¤ À¯ÇüÀ» ¼³¸íÇÕ´Ï´Ù. DB2´Â ÀÌ·± TO SQL°ú FROM SQL ÇÔ¼ö º¯È¯À» »ç¿ëÇÏ¿© ¿ÀºêÁ§Æ®¸¦ ¿ÜºÎ ·çƾ°ú »óÈ£ Àü´ÞÇÕ´Ï´Ù. SQL º»¹® ·çƾ¿¡¼´Â º¯È¯À» »ç¿ëÇÒ Çʿ䰡 ¾ø½À´Ï´Ù. ±×·¯³ª, ÇÁ·Î±×·¥°úÀÇ ¿ÀºêÁ§Æ® ±³È¯: Ŭ¶óÀÌ¾ðÆ® º¯È¯¿¡ ¼³¸íµÈ ´ë·Î DB2´Â ÀÌ·± ÇÔ¼ö¸¦ Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥°ú ¿ÀºêÁ§Æ®¸¦ »óÈ£ Àü´ÞÇÏ´Â ÇÁ·Î¼¼½ºÀÇ ÀϺηΠ»ç¿ëÇÕ´Ï´Ù.
´ÙÀ½ ¿¹´Â ÁÖ¼Ò¸¦ ÀÔ·Â ¸Å°³º¯¼ö·Î ÃëÇϰí ÁÖ¼Ò¸¦ ¼öÁ¤(¿¹¸¦ µé¾î, °Å¸® À̸§ÀÇ º¯°æÀ» ¹Ý¿µÇϱâ À§ÇØ)ÇÏ°í ¼öÁ¤µÈ ÁÖ¼Ò¸¦ ¸®ÅÏÇÏ´Â MYUDF¶ó´Â ¿ÜºÎ UDF¸¦ È£ÃâÇÕ´Ï´Ù.
SELECT MYUDF(Address) FROM PERSON;
±×¸² 16Àº DB2°¡ ÁÖ¼Ò¸¦ ó¸®ÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
±×¸² 16. ¿ÜºÎ ·çƾ°úÀÇ ±¸Á¶È À¯Çü ¸Å°³º¯¼ö ±³È¯
![]() |
ÁÖ: | MYUDF°¡ ±¸Á¶È À¯ÇüÀ» ¸®ÅÏÇϸé, ´Ù¸¥ º¯È¯ ÇÔ¼ö´Â UDF°¡
SELECTÀý¿¡¼ »ç¿ëµÇ´Â °æ¿ì °á°ú ±¸Á¶È À¯ÇüÀ» º¯È¯ÇØ¾ß ÇÕ´Ï´Ù.
´Ù¸¥ º¯È¯ ÇÔ¼ö¸¦ ÀÛ¼ºÇÏÁö ¾ÊÀ¸·Á¸é ´ÙÀ½ ¿¹¿¡¼¿Í °°ÀÌ
°üÂû ¸Þ¼Òµå¿¡¼ SELECT¹®À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT Name FROM Employee WHERE MYUDF(Address)..city LIKE 'Tor%'; |
¿ÜºÎ ·çƾ°ú ¿ÀºêÁ§Æ®¸¦ ±³È¯ÇÒ ¶§ ¿ÀºêÁ§Æ®¸¦ ºÐÇØÇÏ°í ±¸¼ºÇÏ·Á¸é, SQL·Î ÀÛ¼ºµÈ SQL º»¹® ·çƾÀ̶ó´Â »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. SQL º»¹® ·çƾÀ» ÀÛ¼ºÇÏ·Á¸é, LANGUAGE SQLÀý°ú ÇÔ²² CREATE FUNCTION¹®À» ¹ßÇàÇϽʽÿÀ.
SQL º»¹® ÇÔ¼ö¿¡¼ ±¸¼ºÀÚ, °üÂû ¹× º¯È¯À» »ç¿ëÇÏ¿© º¯È¯ÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×¸² 16¿¡¼¿Í °°ÀÌ ÀÌ SQL º»¹® º¯È¯Àº SQL¹®°ú ¿ÜºÎ ÇÔ¼ö »çÀÌ¿¡ ³¢¾îµì´Ï´Ù. FROM SQL º¯È¯Àº ¿ÀºêÁ§Æ®¸¦ SQL ¸Å°³º¯¼ö·Î ÃëÇÏ°í ±¸Á¶È À¯ÇüÀÇ ¼Ó¼ºÀ» ³ªÅ¸³»´Â °ªÀÇ ÇàÀ» ¸®ÅÏÇÕ´Ï´Ù. ´ÙÀ½ ¿¹¿¡´Â SQL º»¹® ÇÔ¼ö¸¦ »ç¿ëÇÏ´Â ÁÖ¼Ò ¿ÀºêÁ§Æ®ÀÇ FROM SQL º¯È¯ ÇÔ¼ö°¡ ÀÖ½À´Ï´Ù.
CREATE FUNCTION addresstofunc (A Address_t) (1) RETURNS ROW (Street VARCHAR(30), Number CHAR(15), City VARCHAR(30), State (VARCHAR(10)) (2) LANGUAGE SQL (3) RETURN VALUES (A..Street, A..Number, A..City, A..State) (4)
´ÙÀ½ ¸ñ·ÏÀº ÀÌÀü CREATE FUNCTION¹®ÀÇ ±¸¹®À» ¼³¸íÇÕ´Ï´Ù.
DB2´Â »ç¿ëÀÚ°¡ ÀÌ ÇÔ¼ö¸¦ º¯È¯ ÇÔ¼ö·Î »ç¿ëÇÏ·Á°í ÇÑ´Ù´Â °ÍÀ» ¸ð¸£°í ÀÖ½À´Ï´Ù. »ç¿ëÀÚ°¡ ÀÌ ÇÔ¼ö¸¦ »ç¿ëÇÏ´Â º¯È¯ ±×·ìÀ¸·Î ÀÛ¼ºÇϰí, ±× º¯È¯ ±×·ìÀ» ÀûÇÕÇÑ »óȲÀ¸·Î ÁöÁ¤ÇÒ ¶§±îÁö DB2´Â ÇÔ¼ö¸¦ º¯È¯ ÇÔ¼ö·Î »ç¿ëÇÒ ¼ö ¾ø½À´Ï´Ù. ÀÚ¼¼ÇÑ ³»¿ëÀº À¯Çü°ú º¯È¯ ¿¬°üÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
TO SQL º¯È¯Àº FROM SQL ÇÔ¼ö¿Í ¿ªÀ¸·Î ÀÛµ¿ÇÕ´Ï´Ù. ÀÌ´Â ·çƾ¿¡¼ ¸Å°³º¯¼ö ¸ñ·ÏÀ» ÀÔ·ÂÀ¸·Î ÃëÇÏ°í ±¸Á¶È À¯ÇüÀÇ ÀνºÅϽº¸¦ ¸®ÅÏÇÕ´Ï´Ù. ¿ÀºêÁ§Æ®¸¦ ±¸¼ºÇϱâ À§ÇØ ´ÙÀ½ FROM SQL ÇÔ¼ö´Â Address_t À¯Çü¿¡ ´ëÇØ ±¸¼ºÀÚ ÇÔ¼ö¸¦ È£ÃâÇÕ´Ï´Ù.
CREATE FUNCTION functoaddress (street VARCHAR(30), number CHAR(15), city VARCHAR(30), state VARCHAR(10)) (1) RETURNS Address_t (2) LANGUAGE SQL CONTAINS SQL RETURN Address_t()..street(street)..number(number) ..city(city)..state(state) (3)
´ÙÀ½ ¸ñ·ÏÀº ÀÌÀü ¸í·É¹®ÀÇ ±¸¹®À» ¼³¸íÇÕ´Ï´Ù.
ÁÖ¼Ò¸¦ ¸®ÅÏÇÏ´Â ¸ðµç ÇÔ¼ö°¡ °°Àº ¼ø¼¸¦ »ç¿ëÇÏ´Â ÇÑ FROM SQL ÇÔ¼ö¿¡¼ ¸Å°³º¯¼öÀÇ ¼ø¼´Â Áß¿äÇÏÁö ¾Ê½À´Ï´Ù.
±¸Á¶È À¯Çü ¸Å°³º¯¼ö¸¦ ¿ÜºÎ ·çƾ¿¡ Àü´ÞÇÒ ¶§ °¢ ¼Ó¼º¿¡ ´ëÇØ ¸Å°³º¯¼ö¸¦ Àü´ÞÇØ¾ß ÇÕ´Ï´Ù. °¢ ¸Å°³º¯¼ö¿¡ ³Î Ç¥½Ã±â¸¦ Àü´ÞÇÏ°í ±¸Á¶È À¯Çü ÀÚü¿¡ ³Î(NULL) Ç¥½Ã±â¸¦ Àü´ÞÇØ¾ß ÇÕ´Ï´Ù. ´ÙÀ½ ¿¹´Â Address_t ±¸Á¶È À¯ÇüÀ» ½ÂÀÎÇÏ°í ±âº» À¯ÇüÀ» ¸®ÅÏÇÕ´Ï´Ù.
CREATE FUNCTION stream_to_client (Address_t) RETURNS VARCHAR(150) ...
¿ÜºÎ ·çƾÀº Address_t À¯Çü(address_ind)ÀÇ ÀνºÅϽº¿¡ ´ëÇØ ³Î(NULL) Ç¥½Ã±â¸¦ ½ÂÀÎÇϰí, Address_t À¯ÇüÀÇ °¢ ¼Ó¼º¿¡ ´ëÇØ ³Î(NULL) Ç¥½Ã±â¸¦ Çϳª ½ÂÀÎÇØ¾ß ÇÕ´Ï´Ù. VARCHAR Ãâ·Â ¸Å°³º¯¼ö¿¡ ´ëÇØ¼µµ ³Î(NULL) Ç¥½Ã±â°¡ ÀÖ½À´Ï´Ù. ´ÙÀ½ ÄÚµå´Â UDF¸¦ ±¸ÇöÇÏ´Â C ¾ð¾î ÇÔ¼ö Çì´õ¸¦ ³ªÅ¸³À´Ï´Ù.
void SQL_API_FN stream_to_client( /*decomposed address*/ SQLUDF_VARCHAR *street, SQLUDF_CHAR *number, SQLUDF_VARCHAR *city, SQLUDF_VARCHAR *state, SQLUDF_VARCHAR *output, /*null indicators for type attributes*/ SQLUDF_NULLIND *street_ind, SQLUDF_NULLIND *number_ind, SQLUDF_NULLIND *city_ind, SQLUDF_NULLIND *state_ind, /*null indicator for instance of the type*/ SQLUDF_NULLIND *address_ind, /*null indicator for the VARCHAR output*/ SQLUDF_NULLIND *out_ind, SQLUDF_TRAIL_ARGS)
·çƾÀÌ ¼·Î ´Ù¸¥ µÎ ±¸Á¶È À¯Çü ¸Å°³º¯¼ö st1°ú st2¸¦ ½ÂÀÎÇϰí, st3ÀÇ ´Ù¸¥ ±¸Á¶È À¯ÇüÀ» ¸®ÅÏÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
CREATE FUNCTION myudf (int, st1, st2) RETURNS st3
ST1 | ST2 | ST3 |
---|---|---|
st1_att1 VARCHAR | st2_att1 VARCHAR | st3_att1 INTEGER |
st2_att2 INTEGER | st2_att2 CHAR | st3_att2 CLOB |
| st2_att3 INTEGER |
|
´ÙÀ½ ÄÚµå´Â UDF¸¦ ±¸ÇöÇÏ´Â C ¾ð¾î Çì´õ ·çƾÀ» ³ªÅ¸³À´Ï´Ù. Àμö¿¡´Â ´ÙÀ½°ú °°ÀÌ ºÐÇØµÈ ±¸Á¶È À¯ÇüÀÇ ¼Ó¼º¿¡ ´ëÇÑ º¯¼ö¿Í ³Î(NULL) Ç¥½Ã±â ¹× ±¸Á¶È À¯ÇüÀÇ °¢ ÀνºÅϽº¿¡ ´ëÇÑ ³Î(NULL) Ç¥½Ã±â°¡ ÀÖ½À´Ï´Ù.
void SQL_API_FN myudf( SQLUDF_INTEGER *INT, /* Decompose st1 input */ SQLUDF_VARCHAR *st1_att1, SQLUDF_INTEGER *st1_att2, /* Decompose st2 input */ SQLUDF_VARCHAR *st2_att1, SQLUDF_CHAR *st2_att2, SQLUDF_INTEGER *st2_att3, /* Decompose st3 output */ SQLUDF_VARCHAR *st3_att1out, SQLUDF_CLOB *st3_att2out, /* Null indicator of integer*/ SQLUDF_NULLIND *INT_ind, /* Null indicators of st1 attributes and type*/ SQLUDF_NULLIND *st1_att1_ind, SQLUDF_NULLIND *st1_att2_ind, SQLUDF_NULLIND *st1_ind, /* Null indicators of st2 attributes and type*/ SQLUDF_NULLIND *st2_att1_ind, SQLUDF_NULLIND *st2_att2_ind, SQLUDF_NULLIND *st2_att3_ind, SQLUDF_NULLIND *st2_ind, /* Null indicators of st3_out attributes and type*/ SQLUDF_NULLIND *st3_att1_ind, SQLUDF_NULLIND *st3_att2_ind, SQLUDF_NULLIND *st3_ind, /* trailing arguments */ SQLUDF_TRAIL_ARGS )
ÀÌ Àý¿¡¼´Â Ŭ¶óÀÌ¾ðÆ® º¯È¯À» ¼³¸íÇÕ´Ï´Ù. Ŭ¶óÀÌ¾ðÆ® º¯È¯Àº ±¸Á¶È À¯ÇüÀ» Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥°ú ±³È¯ÇÕ´Ï´Ù.
¿¹¸¦ µé¾î, ´ÙÀ½ SQL¹®À» ½ÇÇàÇÑ´Ù°í °¡Á¤ÇØ º¸½Ê½Ã¿À.
... SQL TYPE IS Address_t AS VARCHAR(150) addhv; ... EXEC SQL SELECT Address FROM Person INTO :addhv WHERE AGE > 25 END EXEC;
±×¸² 17Àº ±× ÁÖ¼Ò¸¦ Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥À¸·Î ¹ÙÀÎµå¾Æ¿ôÇÏ´Â ÇÁ·Î¼¼½º¸¦ º¸¿©ÁÝ´Ï´Ù.
±×¸² 17. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ±¸Á¶È À¯Çü ¹ÙÀÎµå¾Æ¿ô
![]() |
ÀÌ ÀÎÄÚµùÀº ¼Ó¼ºÀ» ¿¬¼Ó ÀúÀ念¿ª¿¡ º¹»ç(Çʼö Á¤·Ä¿¡ ´ëÇØ Á¦°ø)ÇÏ´Â °Íó·³ °£´ÜÇÒ ¼ö ÀÖ½À´Ï´Ù. ¼Ó¼ºÀÇ ÀÎÄÚµù°ú µðÄÚµùÀº ÀϹÝÀûÀ¸·Î SQL·Î ÀÌ·ç¾îÁöÁö ¾ÊÀ¸¹Ç·Î Ŭ¶óÀÌ¾ðÆ® º¯È¯Àº º¸Åë ¿ÜºÎ UDF·Î ÀÛ¼ºµË´Ï´Ù.
Ç÷§Æû°£ÀÇ µ¥ÀÌÅÍ Ã³¸®¿¡ ´ëÇØ µ¥ÀÌÅÍ º¯È¯ °í·Á»çÇ×À» ÂüÁ¶ÇϽʽÿÀ.
±×¸² 18Àº ÁÖ¼Ò¸¦ ´Ù½Ã µ¥ÀÌÅͺ£À̽º·Î Àü´ÞÇÏ´Â ¿ª ÇÁ·Î¼¼½º¸¦ º¸¿©ÁÝ´Ï´Ù.
±×¸² 18. Ŭ¶óÀÌ¾ðÆ®¿¡¼ ±¸Á¶È À¯ÇüÀ¸·Î ¹ÙÀεù
![]() |
Ŭ¶óÀÌ¾ðÆ® º¯È¯À» ´Ù¸¥ ¿ÜºÎ UDF¿Í °°Àº ¹æ¹ýÀ» µî·ÏÇϽʽÿÀ. ¿¹¸¦ µé¾î, ÁÖ¼Ò¿¡ ÀûÇÕÇÑ ÀÎÄÚµù°ú µðÄÚµùÀ» ¼öÇàÇÏ´Â ¿ÜºÎ UDF¸¦ ÀÛ¼ºÇß´Ù°í ÇϽʽÿÀ. FROM SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À» from_sql_to_client·Î ±×¸®°í TO SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À» to_sql_from_client·Î ¸í¸íÇß´Ù°í ÇϽʽÿÀ. ÀÌ·± °æ¿ì ÇÔ¼öÀÇ Ãâ·ÂÀº ÀûÇÕÇÑ FROM SQL°ú RO SQL ÇÔ¼ö º¯È¯¿¡¼ ÀÔ·ÂÀ¸·Î »ç¿ëµÉ ¼ö ÀÖ´Â Çü½ÄÀ¸·Î µÇ¾î ÀÖ½À´Ï´Ù.
CREATE FUNCTION from_sql_to_client (Address_t) RETURNS VARCHAR (150) LANGUAGE C TRANSFORM GROUP func_group EXTERNAL NAME 'addressudf!address_from_sql_to_client' NOT VARIANT NO EXTERNAL ACTION NOT FENCED NO SQL PARAMETER STYLE DB2SQL;
ÀÌÀü ¿¹¿¡ ÀÖ´Â DDLÀº ¸¶Ä¡ from_sql_to_client UDF°¡ Address_t À¯ÇüÀÇ ¸Å°³º¯¼ö¸¦ ½ÂÀÎÇÏ´Â °Íó·³ º¸ÀÌ°Ô ¸¸µì´Ï´Ù. ½ÇÁ¦·Î ÀÏ¾î³ °ÍÀº from_sql_to_client UDF°¡ È£ÃâµÈ °¢ Çà¿¡ ´ëÇØ Addresstofunc º¯È¯Àº Address¸¦ ´Ù¾çÇÑ ¼Ó¼ºÀ¸·Î ºÐÇØÇÏ´Â ÀÛ¾÷ÀÔ´Ï´Ù. from_sql_to_client UDF´Â °£´ÜÇÑ ¹®ÀÚ¿À» »ý»êÇϰí ÁÖ¼Ò ¼Ó¼ºÀ» Ç¥½ÃÇÒ ¼ö ÀÖµµ·Ï Çü½ÄÈÇÏ¿© ´ÙÀ½ ´Ü¼ø SQL Á¶È¸¸¦ ÅëÇØ Person Å×À̺íÀÇ °¢ Çà¿¡ ´ëÇØ Name°ú Address ¼Ó¼ºÀ» Ç¥½ÃÇÕ´Ï´Ù.
SELECT Name, from_sql_to_client (Address) FROM Person;
´ÙÀ½ DDLÀº VARCHAR-ÀÎÄÚµåµÈ ¿ÀºêÁ§Æ®¸¦ Ŭ¶óÀÌ¾ðÆ®¿¡¼ ÃëÇϰí À̸¦ ´Ù¾çÇÑ ±âº» À¯Çü ¼Ó¼ºÀ¸·Î ºÐÇØÇϸç TO SQL ÇÔ¼ö º¯È¯À¸·Î Àü´ÞÇÏ´Â ÇÔ¼ö¸¦ µî·ÏÇÕ´Ï´Ù.
CREATE FUNCTION to_sql_from_client (VARCHAR (150)) RETURNS Address_t LANGUAGE C TRANSFORM GROUP func_group EXTERNAL NAME 'addressudf!address_to_sql_from_client' NOT VARIANT NO EXTERNAL ACTION NOT FENCED NO SQL PARAMETER STYLE DB2SQL;
ºñ·Ï to_sql_from_client°¡ ÁÖ¼Ò¸¦ Á÷Á¢ ¸®ÅÏÇÏ´Â °Íó·³ º¸ÀÌÁö¸¸, ½ÇÁ¦·Î´Â to_sql_from_client°¡ VARCHAR (150)À» ±âº» À¯Çü ¼Ó¼º ¼¼Æ®·Î º¯È¯ÇÏ´Â °ÍÀÔ´Ï´Ù. ±×·± ÈÄ DB2´Â functoaddress TO SQL º¯È¯À» ¾Ï½ÃÀûÀ¸·Î È£ÃâÇÏ¿© µ¥ÀÌÅͺ£À̽º·Î ¸®ÅϵǴ ÁÖ¼Ò ¿ÀºêÁ§Æ®¸¦ ±¸¼ºÇÕ´Ï´Ù.
DB2´Â ¾î¶² ÇÔ¼ö º¯È¯À» È£ÃâÇØ¾ß ÇÏ´ÂÁö ¾Ð´Ï±î? to_sql_from_client¿Í from_sql_to_client ¾çÂÊ¿¡ ÀÖ´Â DDL¿¡´Â TRANSFORM GROUPÀ̶ó´Â ÀýÀÌ ÀÖ½À´Ï´Ù. ÀÌ ÀýÀº DB2¿¡°Ô ÀÌ·± ÇÔ¼ö¿¡¼ ÁÖ¼Ò À¯ÇüÀ» ó¸®ÇÒ ¶§ »ç¿ëÇÏ´Â º¯È¯ ¼¼Æ®¸¦ ¾Ë·ÁÁÝ´Ï´Ù. ÀÚ¼¼ÇÑ ³»¿ëÀº À¯Çü°ú º¯È¯ ¿¬°üÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
¼¹ö¿Í Ŭ¶óÀÌ¾ðÆ®°£¿¡ µ¥ÀÌÅÍ, ƯÈ÷ 2Áø µ¥ÀÌÅͰ¡ ±³È¯µÉ ¶§ ¿©·¯ °¡Áö µ¥ÀÌÅÍ º¯È¯ ¹®Á¦¸¦ °í·ÁÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, µ¥ÀÌÅͰ¡ ¿©·¯ ¹ÙÀÌÆ®-Á¤·Ä ½ºÅ´À¸·Î Ç÷§Æû°£¿¡ Àü¼ÛµÇ¸é ¼öÄ¡ µ¥ÀÌÅÍ´Â ¹ÙÀÌÆ® ¸®¹ö¼³ ÇÁ·Î¼¼½º¸¦ °ÅÃÄ ¿Ã¹Ù¸¥ ¼öÄ¡ °ªÀ» º¹¿øÇØ¾ß ÇÕ´Ï´Ù. ¿©·¯ ¿î¿µ üÁ¦¿¡´Â ¸Þ¸ð¸®¿¡¼ ¼öÄ¡ µ¥ÀÌÅ͸¦ ÂüÁ¶Çϴ ƯÁ¤ Á¤·Ä ¿ä±¸»çÇ×ÀÌ ÀÖ½À´Ï´Ù. ÀϺΠ¿î¿µ üÁ¦´Â ÀÌ·± ¿ä±¸»çÇ×ÀÌ ÃæÁ·µÇÁö ¾ÊÀ» ¶§ ÇÁ·Î±×·¥ ¿¹¿Ü¸¦ ÀÏÀ¸Åµ´Ï´Ù. ¹®ÀÚ µ¥ÀÌÅÍ À¯ÇüÀº µ¥ÀÌÅͺ£À̽º¿¡ ÀÇÇØ ÀÚµ¿À¸·Î º¯È¯µË´Ï´Ù. ´Ü, ¹®ÀÚ µ¥ÀÌÅͰ¡ BLOB ¶Ç´Â VARCHAR FOR BIT DATA¿Í °°Àº 2Áø µ¥ÀÌÅÍ À¯Çü¿¡ Æ÷Ç﵃ ¶§´Â ¿¹¿ÜÀÔ´Ï´Ù.
µ¥ÀÌÅÍ º¯È¯ ¹®Á¦¸¦ ÇÇÇÒ ¼ö ÀÖ´Â µÎ °¡Áö ¹æ¹ýÀÌ ÀÖ½À´Ï´Ù.
ÀÌ Á¢±Ù ¹æ¹ýÀº ÀáÀçÀû º¯È¯ÀÌ ¸¹ÀÌ ÇÊ¿äÇÑ´Ù´Â ¸é¿¡¼ÀÇ ¼º´É ÀúÇÏ¿Í Å¬¶óÀÌ¾ðÆ® ¶Ç´Â º¯È¯ ÇÔ¼ö ÀÚü¿¡¼ ÀÌ·± ¿ÀºêÁ§Æ®¸¦ ¾×¼¼½ºÇÏ´Â Äڵ尡 ´õ º¹ÀâÇØÁ³´Ù´Â ´ÜÁ¡ÀÌ ÀÖ½À´Ï´Ù.
ÁÖ: | °¡´ÉÇÑ ÇÑ ¼¹ö¿Í Ŭ¶óÀÌ¾ðÆ®°£¿¡ ÀϾ´Â µ¥ÀÌÅÍ Àü¼Û¿¡ ¿¬°üµÈ ¸ðµç º¹À⼺À» Á¦´ë·Î ó¸®ÇÒ ¼ö ÀÖµµ·Ï º¯È¯ ÇÔ¼ö¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥À» ¼³°èÇÒ ¶§ ȯ°æÀÇ Æ¯Á¤ ¿ä±¸»çÇ×À» °í·ÁÇÏ°í ¿ÏÀüÇÑ ÀϹݼº°ú ´Ü¼ø¼º °£ÀÇ Àå´ÜÁ¡À» Æò°¡ÇϽʽÿÀ. ¿¹¸¦ µé¾î, µ¥ÀÌÅͺ£À̽º ¼¹ö¿Í ±× ¸ðµç Ŭ¶óÀÌ¾ðÆ®°¡ AIX ȯ°æ¿¡¼ ½ÇÇàÇÏ°í °°Àº ÄÚµå ÆäÀÌÁö¸¦ »ç¿ëÇÑ´Ù´Â °ÍÀ» ¾Ë¸é ÇöÀç º¯È¯ÀÌ ÇÊ¿äÇÏÁö ¾ÊÀ¸¹Ç·Î ¾Õ¿¡¼ ¼³¸íµÈ °í·Á»çÇ×À» ¹«½ÃÇØ¾ß ÇÕ´Ï´Ù. ±×·¯³ª, ȯ°æÀÌ ¹Ì·¡¿¡ º¯ÇÏ¸é µ¥ÀÌÅÍ º¯È¯À» ¿Ã¹Ù·Î ó¸®ÇÒ ¼ö ÀÖµµ·Ï ¿ø·¡ ¼³°è¸¦ ¼öÁ¤ÇÏ´Â µ¥ »ó´çÇÑ ³ë·ÂÀ» ±â¿ï¿©¾ß ÇÒ °ÍÀÔ´Ï´Ù. |
Ç¥ 15´Â ¿ÜºÎ ·çƾ ¶Ç´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î
¹ÙÀÎµå¾Æ¿ôÇÏ´Â ¿©ºÎ¿¡ µû¶ó ÇÊ¿äÇÑ º¯È¯ ÇÔ¼ö¸¦ °áÁ¤ÇÒ ¶§ µµ¿òÀ» ÁÖ±â À§ÇÑ °ÍÀÔ´Ï´Ù.
Ư¼º | ¿ÜºÎ ·çƾ°úÀÇ °ª ±³È¯ | Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥°úÀÇ °ª ±³È¯ | ||
---|---|---|---|---|
º¯È¯ ¹æÇâ | FROM SQL | TO SQL | FROM SQL | TO SQL |
¾î´ø °ÍÀÌ º¯È¯µË´Ï±î | ·çƾ ¸Å°³º¯¼ö | ·çƾ °á°ú | Ãâ·Â È£½ºÆ® º¯¼ö | ÀԷ ȣ½ºÆ® º¯¼ö |
µ¿ÀÛ | ºÐÇØ | ±¸¼º | ÀÎÄÚµå | µðÄÚµå |
º¯È¯ ÇÔ¼ö ¸Å°³º¯¼ö | ±¸Á¶È À¯Çü | ³»Àå À¯ÇüÀÇ Çà | ±¸Á¶È À¯Çü | ÇϳªÀÇ ³»Àå À¯Çü |
º¯È¯ ÇÔ¼ö °á°ú | ³»Àå À¯ÇüÀÇ Çà(¼Ó¼ºÀÏ °ÍÀÓ) | ±¸Á¶È À¯Çü | ÇϳªÀÇ ³»Àå À¯Çü | ±¸Á¶È À¯Çü |
´Ù¸¥ º¯È¯¿¡ Á¾¼ÓµÇ¾î ÀÖ½À´Ï±î? | ¾Æ´Ï¿À | ¾Æ´Ï¿À | FROM SQL UDF º¯È¯ | TO SQL UDF º¯È¯ |
¾ðÁ¦ º¯È¯ ±×·ìÀÌ ÁöÁ¤µË´Ï±î? | UDF°¡ µî·ÏµÉ ¶§ |
Á¤Àû: »çÀü ó¸® ÄÄÆÄÀÏ ½Ã°£ µ¿Àû: Ư¼ö ·¹Áö½ºÅÍ | ||
µ¥ÀÌÅÍ º¯È¯ °í·Á»çÇ×ÀÌ ÀÖ½À´Ï±î? | ¾Æ´Ï¿À | ¿¹ |
ÁÖ: | ºñ·Ï ÀϹÝÀûÀÌÁö´Â ¾ÊÁö¸¸ ´ÙÀ½ÀÇ °æ¿ì°¡ ÂüÀÏ ¶§ Ŭ¶óÀÌ¾ðÆ® À¯Çüº¯È¯Àº ½ÇÁ¦·Î
SQL·Î ÀÛ¼ºµË´Ï´Ù.
ÀÌ·± °æ¿ì ÇÔ¼ö º¯È¯¿¡ ÀÇÁ¸ÇÏ¿© ±¸Á¶È À¯ÇüÀÇ °ªÀ» Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥°ú ±³È¯ÇÏÁö ¾Ê¾Æµµ µË´Ï´Ù. |
¾Õ Àý¿¡ ÀÖ´Â ´ëºÎºÐÀÇ Á¤º¸´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¾Ë·ÁÁø Á¤È®ÇÑ À¯ÇüÀ» Àü´ÞÇÑ´Ù°í °£ÁÖÇÕ´Ï´Ù. µ¥ÀÌÅÍ ¸ðµ¨ÀÌ ºÎ¼Ó À¯ÇüÀ» »ç¿ëÇϸé Ä÷³¿¡ ÀÖ´Â °ªÀÌ ¿©·¯ ºÎ¼Ó À¯Çü Áß ÇϳªÀÏ °ÍÀÔ´Ï´Ù. ÀÌ Àý¿¡¼´Â ½ÇÁ¦ ÀÔ·Â À¯ÇüÀ» ±â¹ÝÀ¸·Î ¿Ã¹Ù¸¥ º¯È¯ ÇÔ¼ö¸¦ µ¿ÀûÀ¸·Î ¼±ÅÃÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀ» ¼³¸íÇÕ´Ï´Ù.
´ÙÀ½ SELECT¹®À» ¹ßÇàÇÑ´Ù°í ÇϽʽÿÀ.
SELECT Address FROM Person INTO :hvaddr;
ÀÀ¿ëÇÁ·Î±×·¥¿¡´Â Address_tÀÇ ÀνºÅϽº, US_addr_t µîÀÌ ¸®ÅϵǴÂÁö ¾Ë ¼ö ÀÖ´Â ¹æ¹ýÀÌ ¾ø½À´Ï´Ù. ¿¹¸¦ ³Ê¹« º¹ÀâÇÏÁö ¾Ê°Ô À¯ÁöÇϱâ À§ÇØ Address_t³ª US_addr_t¸¸ ¸®Å쵃 ¼ö ÀÖ´Ù°í °¡Á¤ÇÕ´Ï´Ù. ÀÌ·± À¯ÇüÀÇ ±¸Á¶´Â ´Ù¸£¹Ç·Î ¼Ó¼ºÀ» ºÐÇØÇÏ´Â º¯È¯Àº ´Þ¶ó¾ß ÇÕ´Ï´Ù. ÀûÇÕÇÑ º¯È¯ÀÌ È£ÃâµÇµµ·Ï ´ÙÀ½ ´Ü°è¸¦ ¼öÇàÇϽʽÿÀ.
°¢°¢ÀÇ ÁÖ¼Ò º¯Çü¿¡ ´ëÇØ FROM SQL ÇÔ¼ö º¯È¯À» ÀÛ¼ºÇϽʽÿÀ.
CREATE FUNCTION addresstofunc(A address_t) RETURNS ROW (Street VARCHAR(30), Number CHAR(15), City VARCHAR(30), STATE VARCHAR (10)) LANGUAGE SQL RETURN VALUES (A..Street, A..Number, A..City, A..State) CREATE FUNCTION US_addresstofunc(A US_addr_t) RETURNS ROW (Street VARCHAR(30), Number CHAR(15), City VARCHAR(30), STATE VARCHAR (10), Zip CHAR(10)) LANGUAGE SQL RETURN VALUES (A..Street, A..Number, A..City, A..State, A..Zip) | |||
°¢ À¯Çü º¯Çü¸¶´Ù º¯È¯ ±×·ìÀ» Çϳª¾¿ ÀÛ¼ºÇϽʽÿÀ.
CREATE TRANSFORM FOR Address_t funcgroup1 (FROM SQL WITH FUNCTION addresstofunc) CREATE TRANSFORM FOR US_addr_t funcgroup2 (FROM SQL WITH FUNCTION US_addresstofunc) | |||
°¢ À¯Çü º¯Çü¸¶´Ù ¿ÜºÎ UDF¸¦ ÀÛ¼ºÇϽʽÿÀ.
Address_t À¯Çü¿¡ ´ëÇØ ¿ÜºÎ UDF¸¦ µî·ÏÇϽʽÿÀ.
CREATE FUNCTION address_to_client (A Address_t) RETURNS VARCHAR(150) LANGUAGE C EXTERNAL NAME 'addressudf!address_to_client' ... TRANSFORM GROUP funcgroup1
address_to_client UDF¸¦ ÀÛ¼ºÇϽʽÿÀ.
void SQL_API_FN address_to_client( SQLUDF_VARCHAR *street, SQLUDF_CHAR *number, SQLUDF_VARCHAR *city, SQLUDF_VARCHAR *state, SQLUDF_VARCHAR *output, /* Null indicators for attributes */ SQLUDF_NULLIND *street_ind, SQLUDF_NULLIND *number_ind, SQLUDF_NULLIND *city_ind, SQLUDF_NULLIND *state_ind, /* Null indicator for instance */ SQLUDF_NULLIND *address_ind, /* Null indicator for output */ SQLUDF_NULLIND *output_ind, SQLUDF_TRAIL_ARGS ) { sprintf (output, "[address_t] [Street:%s] [number:%s] [city:%s] [state:%s]", street, number, city, state); *output_ind = 0; }
US_addr_t À¯Çü¿¡ ´ëÇØ ¿ÜºÎ UDF¸¦ µî·ÏÇϽʽÿÀ.
CREATE FUNCTION address_to_client (A US_addr_t) RETURNS VARCHAR(150) LANGUAGE C EXTERNAL NAME 'addressudf!US_addr_to_client' ... TRANSFORM GROUP funcgroup2
US_addr_to_client UDF¸¦ ÀÛ¼ºÇϽʽÿÀ.
void SQL_API_FN US_address_to_client( SQLUDF_VARCHAR *street, SQLUDF_CHAR *number, SQLUDF_VARCHAR *city, SQLUDF_VARCHAR *state, SQLUDF_CHAR *zip, SQLUDF_VARCHAR *output, /* Null indicators */ SQLUDF_NULLIND *street_ind, SQLUDF_NULLIND *number_ind, SQLUDF_NULLIND *city_ind, SQLUDF_NULLIND *state_ind, SQLUDF_NULLIND *zip_ind, SQLUDF_NULLIND *us_address_ind, SQLUDF_NULLIND *output_ind, SQLUDF_TRAIL_ARGS ) { sprintf (output, "[US_addr_t] [Street:%s] [number:%s] [city:%s] [state:%s] [zip:%s]", street, number, city, state, zip); *output_ind = 0; } | |||
ÀνºÅϽº¸¦ ó¸®Çϱâ À§ÇØ ¿Ã¹Ù¸¥ ¿ÜºÎ UDF¸¦ ¼±ÅÃÇÏ´Â SQL º»¹® UDF¸¦ ÀÛ¼ºÇϽʽÿÀ. ´ÙÀ½ UDF´Â UNION ALLÀý¿¡ ÀÇÇØ Á¶ÇÕµÈ SELECT¹® ³»ÀÇ TREAT ½ºÆåÀ» »ç¿ëÇÏ¿© ¿Ã¹Ù¸¥ FROM SQL º¯È¯À» È£ÃâÇÏ°í °á°ú¸¦ Àӽà Å×ÀÌºí¿¡ ³Ö½À´Ï´Ù.
CREATE FUNCTION addr_stream (ab Address_t) RETURNS VARCHAR(150) LANGUAGE SQL RETURN WITH temp(addr) AS (SELECT address_to_client(ta.a) FROM TABLE (VALUES (ab)) AS ta(a) WHERE ta.a IS OF (ONLY Address_t) UNION ALL SELECT address_to_client(TREAT (tb.a AS US_addr_t)) FROM TABLE (VALUES (ab)) AS tb(a) WHERE tb.a IS OF (ONLY US_addr_t)) SELECT addr FROM temp;
ÀÌ ¶§ ÀÀ¿ëÇÁ·Î±×·¥Àº Addr_stream ÇÔ¼ö¸¦ È£ÃâÇÏ¿© ÀûÇÕÇÑ ¿ÜºÎ UDF¸¦ È£ÃâÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT Addr_stream(Address) FROM Employee; | |||
Addr_stream ¿ÜºÎ UDF¸¦ Address_tÀÇ FROM SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À¸·Î Ãß°¡ÇϽʽÿÀ.
CREATE TRANSFORM GROUP FOR Address_t client_group (FROM SQL WITH FUNCTION Addr_stream)
| |||
ÀÀ¿ëÇÁ·Î±×·¥À» TRANSFORM GROUP ¿É¼Ç ¼¼Æ®¿Í ÇÔ²² client_groupÀ¸·Î ¹ÙÀεåÇϽʽÿÀ.
PREP myprogram TRANSFORM GROUP client_group
|
DB2°¡ SELECT Address FROM Person INTO :hvar¹®ÀÌ µé¾î ÀÖ´Â ÀÀ¿ëÇÁ·Î±×·¥À» ¹ÙÀεåÇϸé DB2´Â FROM SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À» ã½À´Ï´Ù. DB2´Â ±¸Á¶È À¯ÇüÀÌ ¹ÙÀÎµå¾Æ¿ôµÇ¾úÀ½À» ÀνÄÇϰí client_group º¯È¯ ±×·ìÀ» »ìÆìº¾´Ï´Ù. ¿Ö³ÄÇϸé ÀÌ´Â 6ÀÇ ¹ÙÀÎµå ½Ã°£¿¡ ÁöÁ¤µÇ´Â º¯È¯ ±×·ìÀ̱⠶§¹®ÀÔ´Ï´Ù.
º¯È¯ ±×·ì¿¡´Â 5ÀÇ Address_t ·çÆ® À¯Çü¿¡ ¿¬°üµÈ Addr_stream º¯È¯ ÇÔ¼ö°¡ µé¾î ÀÖ½À´Ï´Ù. Addr_streamÀº 4¿¡ Á¤ÀÇµÈ SQL º»¹® ÇÔ¼öÀ̹ǷΠ´Ù¸¥ º¯È¯ ÇÔ¼ö¿¡ ´ëÇÑ Á¾¼Ó¼ºÀÌ ¾ø½À´Ï´Ù. Addr_stream ÇÔ¼ö´Â :hvaddr È£½ºÆ® º¯¼ö¿¡¼ ÇÊ¿äÇÑ µ¥ÀÌÅÍ À¯ÇüÀÎ VARCHAR(150)À» ¸®ÅÏÇÕ´Ï´Ù.
Addr_stream ÇÔ¼ö´Â ÀÌ ¿¹¿¡¼ US_addr_t·Î ´ëüµÉ ¼ö ÀÖ´Â Address_t À¯ÇüÀÇ ÀÔ·Â °ªÀ» ÃëÇÏ¿© ÀÔ·Â °ªÀÇ µ¿Àû À¯ÇüÀ» °áÁ¤ÇÕ´Ï´Ù. Addr_streamÀ̵¿Àû À¯ÇüÀ» °áÁ¤Çϸé ÀÌ´Â °ª¿¡ ´ëÇØ ÇØ´ç ¿ÜºÎ UDF¸¦ È£ÃâÇÕ´Ï´Ù. µ¿Àû À¯ÇüÀÌ Address_tÀ̸é address_to_client ¶Ç´Â µ¿Àû À¯ÇüÀÌ US_addr_tÀ̸é USaddr_to_clientÀÔ´Ï´Ù. ÀÌ·± µÎ UDF´Â 3¿¡¼ Á¤Àǵ˴ϴÙ. °¢ UDF´Â °¢°¢ÀÇ ±¸Á¶È À¯ÇüÀ» Addr_stream º¯È¯ ÇÔ¼ö¿¡¼ ÇÊ¿äÇÑ À¯ÇüÀÎ VARCHAR(150)·Î ºÐÇØÇÕ´Ï´Ù.
±¸Á¶È À¯ÇüÀ» ÀÔ·ÂÀ¸·Î ½ÂÀÎÇÏ·Á¸é °¢ UDF´Â FROM SQL º¯È¯ ÇÔ¼ö·Î ÀÔ·Â ±¸Á¶È À¯Çü ÀνºÅϽº¸¦ °³º° ¼Ó¼º ¸Å°³º¯¼ö·Î ºÐÇØÇØ¾ß ÇÕ´Ï´Ù. 3ÀÇ CREAYE FUNCTION¹®Àº ÀÌ·± º¯È¯ÀÌ µé¾î ÀÖ´Â TRANSFORM GROUPÀ» ¸í¸íÇÕ´Ï´Ù.
º¯È¯ ÇÔ¼öÀÇ CREATE FUNCTION¹®Àº 1¿¡¼ ¹ßÇàµË´Ï´Ù. º¯È¯ ÇÔ¼ö¸¦ ±× º¯È¯ ±×·ì¿¡ ¿¬°ü½ÃŰ´Â CREATE FUNCTION¹®Àº 2¿¡¼ ¹ßÇàµË´Ï´Ù.
¿ì¼± DB2¿¡¼ ºÎ¼Ó À¯Çü µ¥ÀÌÅÍ °Ë»ö(¹ÙÀÎµå¾Æ¿ô)¿¡¼ ¼³¸íµÈ ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ÁÖ¼Ò °ªÀ» ó¸®Çϸé, ÀÌ´Â º¯°æµÈ °ªÀ» ´Ù½Ã µ¥ÀÌÅͺ£À̽º¿¡ »ðÀÔÇØ¾ß ÇÕ´Ï´Ù. ´ÙÀ½ ±¸¹®À» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ ±¸Á¶È À¯ÇüÀ» DB2 µ¥ÀÌÅͺ£À̽º¿¡ »ðÀÔÇÑ´Ù°í °¡Á¤ÇØ º¸½Ê½Ã¿À.
INSERT INTO person (Oid, Name, Address) VALUES ('n', 'Norm', :hvaddr);
±¸Á¶È À¯Çü¿¡ ´ëÇØ INSERT¹®À» ½ÇÇàÇÏ·Á¸é ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½ ´Ü°è¸¦ ¼öÇàÇØ¾ß ÇÕ´Ï´Ù.
ÁÖ¼ÒÀÇ °¢ º¯Çü¿¡ ´ëÇØ TO SQL ÇÔ¼ö º¯È¯À» ÀÛ¼ºÇϽʽÿÀ. ´ÙÀ½ ¿¹´Â Address_t¿Í US_addr_t À¯ÇüÀ» º¯È¯ÇÏ´Â SQL-º»¹® UDF¸¦ º¸¿©ÁÝ´Ï´Ù.
CREATE FUNCTION functoaddress (str VARCHAR(30), num CHAR(15), cy VARCHAR(30), st VARCHAR (10)) RETURNS Address_t LANGUAGE SQL RETURN Address_t()..street(str)..number(num)..city(cy)..state(st); CREATE FUNCTION functoaddress (str VARCHAR(30), num CHAR(15), cy VARCHAR(30), st VARCHAR (10), zp CHAR(10)) RETURNS US_addr_t LANGUAGE SQL RETURN US_addr_t()..street(str)..number(num)..city(cy) ..state(st)..zip(zp); | |
°¢ À¯Çü º¯Çü¸¶´Ù º¯È¯ ±×·ìÀ» Çϳª¾¿ ÀÛ¼ºÇϽʽÿÀ.
CREATE TRANSFORM FOR Address_t funcgroup1 (TO SQL WITH FUNCTION functoaddress); CREATE TRANSFORM FOR US_addr_t funcgroup2 (TO SQL WITH FUNCTION functousaddr); | |
ÀÎÄÚµåµÈ ÁÖ¼Ò À¯ÇüÀ» °¢ À¯Çü º¯Çü¸¶´Ù Çϳª¾¿ ¸®ÅÏÇÏ´Â ¿ÜºÎ UDF¸¦ ÀÛ¼ºÇϽʽÿÀ.
Address_t À¯Çü¿¡ ´ëÇØ ¿ÜºÎ UDF¸¦ µî·ÏÇϽʽÿÀ.
CREATE FUNCTION client_to_address (encoding VARCHAR(150)) RETURNS Address_t LANGUAGE C TRANSFORM GROUP funcgroup1 ... EXTERNAL NAME 'address!client_to_address';
client_to_addressÀÇ Address_t ¹öÀü¿¡ ´ëÇØ ¿ÜºÎ UDF¸¦ ÀÛ¼ºÇϽʽÿÀ.
void SQL_API_FN client_to_address ( SQLUDF_VARCHAR *encoding, SQLUDF_VARCHAR *street, SQLUDF_CHAR *number, SQLUDF_VARCHAR *city, SQLUDF_VARCHAR *state, /* Null indicators */ SQLUDF_NULLIND *encoding_ind, SQLUDF_NULLIND *street_ind, SQLUDF_NULLIND *number_ind, SQLUDF_NULLIND *city_ind, SQLUDF_NULLIND *state_ind, SQLUDF_NULLIND *address_ind, SQLUDF_TRAIL_ARGS ) { char c[150]; char *pc; strcpy(c, encoding); pc = strtok (c, ":]"); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strcpy (street, pc); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strcpy (number, pc); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strcpy (city, pc); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strcpy (state, pc); *street_ind = *number_ind = *city_ind = *state_ind = *address_ind = 0; }
US_addr_t À¯Çü¿¡ ´ëÇØ ¿ÜºÎ UDF¸¦ µî·ÏÇϽʽÿÀ.:
CREATE FUNCTION client_to_us_address (encoding VARCHAR(150)) RETURNS US_addr_t LANGUAGE C TRANSFORM GROUP funcgroup1 ... EXTERNAL NAME 'address!client_to_US_addr';
client_to_addressÀÇ US_addr_t ¹öÀü¿¡ ´ëÇØ ¿ÜºÎ UDF¸¦ ÀÛ¼ºÇϽʽÿÀ.
void SQL_API_FN client_to_US_addr( SQLUDF_VARCHAR *encoding, SQLUDF_VARCHAR *street, SQLUDF_CHAR *number, SQLUDF_VARCHAR *city, SQLUDF_VARCHAR *state, SQLUDF_VARCHAR *zip, /* Null indicators */ SQLUDF_NULLIND *encoding_ind, SQLUDF_NULLIND *street_ind, SQLUDF_NULLIND *number_ind, SQLUDF_NULLIND *city_ind, SQLUDF_NULLIND *state_ind, SQLUDF_NULLIND *zip_ind, SQLUDF_NULLIND *us_addr_ind, SQLUDF_TRAIL_ARGS ) { char c[150]; char *pc; strcpy(c, encoding); pc = strtok (c, ":]"); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strcpy (street, pc); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strncpy (number, pc,14); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strcpy (city, pc); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strcpy (state, pc); pc = strtok (NULL, ":]"); pc = strtok (NULL, ":]"); strncpy (zip, pc, 9); *street_ind = *number_ind = *city_ind = *state_ind = *zip_ind = *us_addr_ind = 0; } | |
ÀνºÅϽº¸¦ ó¸®Çϱâ À§ÇØ ¿Ã¹Ù¸¥ ¿ÜºÎ UDF¸¦ ¼±ÅÃÇÏ´Â SQL-º»¹® UDF¸¦ ÀÛ¼ºÇϽʽÿÀ. ´ÙÀ½ UDF´Â TYPE ¼ú¾î¸¦ »ç¿ëÇÏ¿© ¿Ã¹Ù¸¥ Ŭ¶óÀÌ¾ðÆ® º¯È¯À» È£ÃâÇÕ´Ï´Ù. °á°ú´Â Àӽà Å×ÀÌºí¿¡ º¸°üµË´Ï´Ù.
CREATE FUNCTION stream_address (ENCODING VARCHAR(150)) RETURNS Address_t LANGUAGE SQL RETURN (CASE(SUBSTR(ENCODING,2,POSSTR(ENCODING,']')-2)) WHEN 'address_t' THEN client_to_address(ENCODING) WHEN 'us_addr_t' THEN client_to_us_addr(ENCODING) ELSE NULL END); | |
Address_t¿¡ ´ëÇØ stream_address UDF¸¦ TO SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À¸·Î Ãß°¡ÇϽʽÿÀ.
CREATE TRANSFORM FOR Address_t client_group (TO SQL WITH FUNCTION stream_address); | |
ÀÀ¿ëÇÁ·Î±×·¥À» TRANSFORM GROUP ¿É¼Ç ¼¼Æ®¿Í ÇÔ²² client_groupÀ¸·Î ¹ÙÀεåÇϽʽÿÀ.
PREP myProgram2 TRANSFORM GROUP client_group
|
±¸Á¶È À¯ÇüÀÇ INSERT¹®ÀÌ ÀÖ´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¹ÙÀεåµÇ¸é DB2´Â TO SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À» ã½À´Ï´Ù. DB2´Â client_group º¯È¯ ±×·ì¿¡¼ º¯È¯À» ã½À´Ï´Ù. ¿Ö³ÄÇϸé ÀÌ´Â 6ÀÇ ¹ÙÀÎµå ½Ã°£¿¡ ÁöÁ¤µÇ´Â º¯È¯ ±×·ìÀ̱⠶§¹®ÀÔ´Ï´Ù. DB2´Â ÇÊ¿äÇÑ º¯È¯ ÇÔ¼ö¸¦ ã½À´Ï´Ù. ÀÌ´Â 5ÀÇ Address_t ·çÆ® À¯Çü¿¡ ¿¬°üµÈ stream_addressÀÔ´Ï´Ù.
stream_address´Â 4¿¡¼ Á¤ÀÇµÈ SQL º»¹® ÇÔ¼öÀ̹ǷΠÀÌ´Â Ãß°¡ º¯È¯ ÇÔ¼ö¿¡ ´ëÇÑ Á¾¼Ó¼ºÀÌ ¾ø½À´Ï´Ù. ÀÔ·Â ¸Å°³º¯¼ö¿¡ ´ëÇØ stream_address´Â :hvaddr ÀÀ¿ëÇÁ·Î±×·¥ È£½ºÆ® º¯¼ö¿¡ ÇØ´çÇÏ´Â VARCHAR(150)À» ½ÂÀÎÇÕ´Ï´Ù. stream_address´Â ¿Ã¹Ù¸¥ ·çÆ® À¯ÇüÀÎ Address_t¿Í ¿Ã¹Ù¸¥ µ¿Àû À¯ÇüÀ¸·Î °ªÀ» ¸®ÅÏÇÕ´Ï´Ù.
stream_address´Â µ¿Àû À¯ÇüÀ» ¸í¸íÇÏ´Â ºÎ¼Ó ¹®ÀÚ¿¿¡ ´ëÇØ VARCHAR(150) ÀÔ·Â ¸Å°³º¯¼ö¸¦ ºÐ¼®ÇÕ´Ï´Ù. ÀÌ °æ¿ì 'Address_t' ¶Ç´Â 'US_addr_t' ÀÔ´Ï´Ù. ±×·¯¸é stream_address´Â ÇØ´ç ¿ÜºÎ UDF¸¦ È£ÃâÇÏ¿© VARCHAR(150)À» ºÐ¼®Çϰí ÁöÁ¤µÈ À¯ÇüÀÇ ¿ÀºêÁ§Æ®¸¦ ¸®ÅÏÇÕ´Ï´Ù. µÎ °¡Áö client_to_address() UDF¿Í °¡´ÉÇÑ °¢ À¯ÇüÀ» ¸®ÅÏÇϱâ À§ÇÑ °Í Çϳª°¡ ÀÖ½À´Ï´Ù. ÀÌ·± UDF´Â 3¿¡¼ Á¤Àǵ˴ϴÙ. °¢ UDF´Â ÀÔ·Â VARCHAR(150)À» ÃëÇÏ°í ³»ºÎÀûÀ¸·Î ÀûÇÕÇÑ ±¸Á¶È À¯ÇüÀÇ ¼Ó¼ºÀ» ±¸¼ºÇÏ¿© ±¸Á¶È À¯ÇüÀ» ¸®ÅÏÇÕ´Ï´Ù.
±¸Á¶È À¯ÇüÀ» ¸®ÅÏÇÏ·Á¸é °¢ UDF´Â Ãâ·Â ¼Ó¼º °ªÀ» ±¸Á¶È À¯ÇüÀÇ ÀνºÅϽº·Î ±¸¼ºÇÏ´Â TO SQL º¯È¯ ÇÔ¼ö°¡ ÇÊ¿äÇÕ´Ï´Ù. 3ÀÇ CREATE FUNCTION¹®Àº º¯È¯ÀÌ µé¾î ÀÖ´Â TRANSFORM GROUPÀ» ¸í¸íÇÕ´Ï´Ù.
1ÀÇ SQL º»¹® ÇÔ¼ö¿Í 2ÀÇ º¯È¯ ±×·ì°úÀÇ ¿¬°üÀº 3ÀÇ CREATE FUNCTION¹®¿¡¼ ¸í¸íµË´Ï´Ù.
Á¤Àû SQL¿¡¼ ±¸Á¶È À¯Çü È£½ºÆ® º¯¼ö¸¦ ¼ö½ÅÇϰųª Àü¼ÛÇÏ·Á¸é ±¸Á¶È À¯ÇüÀ» Ç¥ÇöÇÒ ¶§ »ç¿ëµÈ ³»Àå À¯ÇüÀ» ³ªÅ¸³»´Â SQL ¼±¾ðÀ» Á¦°øÇØ¾ß ÇÕ´Ï´Ù. ¼±¾ðÀÇ Çü½ÄÀº ´ÙÀ½°ú °°½À´Ï´Ù.
EXEC SQL BEGIN DECLARE SECTION ; SQL TYPE IS structured_type AS base_type host-variable-name ; EXEC SQL END DECLARE SECTION;
¿¹¸¦ µé¾î, Address_tÀ¯ÇüÀÌ Å¬¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î Àü´ÞµÉ ¶§ °¡º¯-±æÀÌÀÇ ¹®ÀÚ À¯ÇüÀ¸·Î º¯È¯µÇ¾î¾ß ÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. Address_t À¯Çü È£½ºÆ® º¯¼ö¿¡ ´ëÇÑ ´ÙÀ½ ¼±¾ðÀ» »ç¿ëÇϽʽÿÀ.
SQL TYPE IS Address_t AS VARCHAR(150) addrhv;
±¸Á¶È À¯Çü º¯¼ö°¡ ÀÖ´Â ¸í·É¹®ÀÇ DESCRIBE´Â DB2¿¡¼ FROM SQL º¯È¯ ÇÔ¼öÀÇ °á°ú À¯Çü ¼³¸íÀ» SQLDAÀÇ ±âº» SQLVARÀÇ SQLTYPE Çʵ忡 ³õ°Ô ÇÕ´Ï´Ù. ±×·¯³ª, CURRENT DEFAULT TRANSFORM GROUP Ư¼ö ·¹Áö½ºÅ͸¦ »ç¿ëÇÏ¿© ÁöÁ¤µÈ TRANSFORM GROUPÀÌ ¾ø°Å³ª ¸í¸íµÈ ±×·ì¿¡ Á¤ÀÇµÈ FROM SQL º¯È¯ ÇÔ¼ö°¡ ¾øÀ¸¹Ç·Î Á¤ÀÇµÈ FROM SQL º¯È¯ ÇÔ¼ö°¡ ¾øÀ¸¸é DESCRIBE´Â ¿À·ù¸¦ ¸®ÅÏÇÕ´Ï´Ù.
±¸Á¶È À¯ÇüÀÇ ½ÇÁ¦ À̸§Àº SQLVAR2¿¡ ¸®Åϵ˴ϴÙ. SQLDAÀÇ ±¸Á¶¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº SQL ÂüÁ¶¼¸¦ ÂüÁ¶ÇϽʽÿÀ.