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

±¸Á¶È­ À¯ÇüÀ» Ä÷³ À¯ÇüÀ¸·Î ÀÛ¼º ¹× »ç¿ë

ÀÌ Àý¿¡¼­´Â »ç¿ëÀÚ Á¤ÀÇ ±¸Á¶È­ À¯ÇüÀ» Ä÷³ÀÇ À¯ÇüÀ¸·Î »ç¿ëÇÏ´Â ÀÛ¾÷¿¡ °ü·ÃµÈ ÁÖ¿ä Ÿ½ºÅ©¿¡ ´ëÇØ ¼³¸íÇÕ´Ï´Ù. ÀÌ ÀýÀ» Àбâ Àü¿¡ ±¸Á¶È­ À¯Çü °³¿ä¿¡ ÀÖ´Â Àç·á¿¡ Àͼ÷ÇØ ÀÖ¾î¾ß ÇÕ´Ï´Ù.

Ä÷³¿¡ ±¸Á¶È­ À¯Çü ÀνºÅϽº »ðÀÔ

±¸Á¶È­ À¯ÇüÀº Å×À̺í, ºä ¶Ç´Â Ä÷³ÀÇ ¹®¸Æ¿¡¼­ »ç¿ëµÉ ¼ö ÀÖ½À´Ï´Ù. ±¸Á¶È­ À¯ÇüÀ» ÀÛ¼ºÇÒ ¶§ »ç¿ëÀÚ Á¤ÀÇ À¯Çü µ¿Àû°ú À¯Çü ¼Ó¼ºÀ» µÑ´Ù ĸ½¶È­ÇÒ ¼ö ÀÖ½À´Ï´Ù. À¯ÇüÀÇ µ¿ÀÛÀ» Æ÷ÇÔÇÏ·Á¸é CREATE TYPE ¶Ç´Â ALTER TYPE¹®À¸·Î ¸Þ¼Òµå ¼­¸íÀ» ÁöÁ¤ÇϽʽÿÀ. ¸Þ¼Òµå ÀÛ¼º¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö(UDFs)¿Í ¸Þ¼Òµå¸¦ ÂüÁ¶ÇϽʽÿÀ.

±×¸² 15´Â ÀÌ Àý¿¡¼­ ¿¹·Î »ç¿ëµÈ À¯Çü °èÃþÀ» º¸¿©ÁÝ´Ï´Ù. ·çÆ® À¯ÇüÀº Address_tÀε¥ ±× ³ª¶ó¿¡¼­ ÁÖ¼Ò¸¦ ±¸¼ºÇÏ´Â ¹æ¹ýÀÇ ¸î °¡Áö Ãø¸éÀ» ¹Ý¿µÇÏ´Â Ãß°¡ ¼Ó¼ºÀÌ ÀÖ´Â 3°¡Áö ºÎ¼Ó À¯ÇüÀÌ ÀÖ½À´Ï´Ù.

±×¸² 15. Address_t À¯Çü¿¡ ´ëÇÑ ±¸Á¶È­ À¯Çü °èÃþ


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 À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀÛ¼ºÇÕ´Ï´Ù.

  1. US_addr_t()¿¡ ´ëÇÑ È£ÃâÀº US_addr_t À¯ÇüÀÇ ±¸¼ºÀÚ ÇÔ¼ö¸¦ È£ÃâÇÏ¿© ¸ðµç ¼Ó¼º ¼¼Æ®°¡ ³Î(NULL) °ªÀ¸·Î ¼³Á¤µÈ À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀÛ¼ºÇÕ´Ï´Ù.
  2. ..street('Bakely Avenue')¿¡ ´ëÇÑ È£ÃâÀº street ¼Ó¼º¿¡ ´ëÇØ º¯È¯ ¸Þ¼Òµå¸¦ È£ÃâÇÏ¿© ±× °ªÀ» 'Bakely Avenue'·Î ¼³Á¤ÇÕ´Ï´Ù.
  3. ..number('555')¿¡ ´ëÇÑ È£ÃâÀº number ¼Ó¼º¿¡ ´ëÇÑ º¯È¯ ¸Þ¼Òµå¸¦ È£ÃâÇÏ¿© ±× °ªÀ» '555'·Î ¼³Á¤ÇÕ´Ï´Ù.
  4. ..city('San Jose')¿¡ ´ëÇÑ È£ÃâÀº city ¼Ó¼º¿¡ ´ëÇÑ º¯È¯ ¸Þ¼Òµå¸¦ È£ÃâÇÏ¿© ±× °ªÀ» 'San Jose'·Î ¼³Á¤ÇÕ´Ï´Ù.
  5. ..state('CA')¿¡ ´ëÇÑ È£ÃâÀº state ¼Ó¼ºÀ» È£ÃâÇÏ¿© ±× °ªÀ» 'CA'·Î ¼³Á¤ÇÕ´Ï´Ù.
  6. ..zip('95141')¿¡ ´ëÇÑ È£ÃâÀº zip ¼Ó¼ºÀ» È£ÃâÇÏ¿© ±× °ªÀ» '95141'·Î ¼³Á¤ÇÕ´Ï´Ù.

ºñ·Ï 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¿¡ ´ëÇÑ º¯È¯ ±×·ì ¼³Á¤

µ¿Àû SQLÀ» »ç¿ëÇϸé CURRENT DEFAULT TRANSFORM GROUP Ư¼ö ·¹Áö½ºÅ͸¦ ¼³Á¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ Æ¯¼ö ·¹Áö½ºÅÍ´Â Á¤Àû SQL¹®À̳ª ¿ÜºÎ ÇÔ¼ö ¶Ç´Â ¸Þ¼Òµå¿ÍÀÇ ¸Å°³º¯¼ö ¹× °á°ú ±³È¯¿¡ »ç¿ëµÇÁö ¾Ê½À´Ï´Ù. SET CURRENT DEFAULT TRANSFORM GROUP¹®À» »ç¿ëÇÏ¿© µ¿Àû SQL¹®¿¡ ´ëÇØ ±âº» º¯È¯ ±×·ìÀ» ¼³Á¤ÇϽʽÿÀ.

   SET CURRENT DEFAULT TRANSFORM GROUP = client_group;

Á¤Àû SQL¿¡ ´ëÇÑ º¯È¯ ±×·ì ¼³Á¤

Á¤Àû 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. ¿ÜºÎ ·çƾ°úÀÇ ±¸Á¶È­ À¯Çü ¸Å°³º¯¼ö ±³È¯


¿ÜºÎ ·çƾ°úÀÇ ±¸Á¶È­ À¯Çü ¸Å°³º¯¼ö ±³È¯

  1. FROM SQL º¯È¯ ÇÔ¼ö´Â ±¸Á¶È­ ¿ÀºêÁ§Æ®¸¦ ¼ø¼­°¡ Á¤ÇØÁø ±âº» ¼Ó¼º ¼¼Æ®·Î ºÐÇØÇÕ´Ï´Ù. À̸¦ ÅëÇØ ·çƾÀº ¿ÀºêÁ§Æ®¸¦ ±âº» ³»Àå µ¥ÀÌÅÍ À¯ÇüÀÎ °£´ÜÇÑ ¸Å°³º¯¼ö ¸ñ·ÏÀ¸·Î ¼ö½ÅÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, ÁÖ¼Ò ¿ÀºêÁ§Æ®¸¦ ¿ÜºÎ ·çƾ¿¡ Àü´ÞÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. Address_tÀÇ ¼Ó¼ºÀº VARCHAR, CHAR, VARCHAR ¹× VARCHARÀ̸ç ÀÌ ¼ø¼­¸¦ À¯ÁöÇÕ´Ï´Ù. ÀÌ ¿ÀºêÁ§Æ®¸¦ ·çƾ¿¡ Àü´ÞÇÏ´Â FROM SQL º¯È¯Àº ÀÌ ¿ÀºêÁ§Æ®¸¦ ÀÔ·ÂÀ¸·Î ½ÂÀÎÇϰí VARCHAR, CHAR, VARCHAR ¹× VARCHAR¸¦ ¸®ÅÏÇØ¾ß ÇÕ´Ï´Ù. ÀÌ·± Ãâ·ÂÀº 4°³ÀÇ ÇØ´ç ³Î Ç¥½Ã±â ¸Å°³º¯¼ö¿Í ±¸Á¶È­ À¯Çü ÀÚü¿¡ ´ëÇÑ ³Î Ç¥½Ã±â°¡ ÀÖ´Â º°µµ ¸Å°³º¯¼ö 4°³·Î ¿ÜºÎ ·çƾ¿¡ Àü´ÞµË´Ï´Ù. Address_t À¯ÇüÀ» ¸®ÅÏÇÏ´Â ÇÔ¼ö°¡ °°Àº ¼ø¼­¸¦ »ç¿ëÇÏ´Â ÇÑ, FROM SQL ÇÔ¼ö¿¡¼­ ¸Å°³º¯¼öÀÇ ¼ø¼­´Â Áß¿äÇÏÁö ¾Ê½À´Ï´Ù. ÀÚ¼¼ÇÑ ³»¿ëÀº ¿ÜºÎ ·çƾ¿¡ ±¸Á¶È­ À¯Çü ¸Å°³º¯¼ö Àü´ÞÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
  2. ¿ÜºÎ ·çƾÀº ºÐÇØµÈ ÁÖ¼Ò¸¦ ÀÔ·Â ¸Å°³º¯¼ö·Î ½ÂÀÎÇϰí ÀÌ·± °ªÀ» ó¸®ÇÑ ÈÄ ¼Ó¼ºÀ» Ãâ·Â ¸Å°³º¯¼ö·Î ¸®ÅÏÇÕ´Ï´Ù.
  3. TO SQL º¯È¯ ÇÔ¼ö´Â MYUDF¿¡¼­ ¸®ÅÏµÈ VARCHAR, CHAR, VARCHAR ¹× VARCHAR ¸Å°³º¯¼ö¸¦ ´Ù½Ã Address_t À¯ÇüÀÇ ¿ÀºêÁ§Æ®·Î º¯È¯ÇØ¾ß ÇÕ´Ï´Ù. ´Ù½Ã ¸»Çؼ­ TO SQL ÇÔ¼ö´Â 4°³ÀÇ ¸Å°³º¯¼ö¿Í ÇØ´ç ³Î(NULL) Ç¥½Ã±â ¸Å°³º¯¼ö¸¦ ·çƾ¿¡¼­ Ãâ·Â ¸Å°³º¯¼ö·Î ÃëÇØ¾ß ÇÕ´Ï´Ù. TO SQL ÇÔ¼ö´Â ±¸Á¶È­ ¿ÀºêÁ§Æ®¸¦ ±¸¼ºÇÑ ÈÄ ¼Ó¼ºÀ» ÁÖ¾îÁø °ªÀ¸·Î º¯È¯ÇÕ´Ï´Ù.
ÁÖ:MYUDF°¡ ±¸Á¶È­ À¯ÇüÀ» ¸®ÅÏÇϸé, ´Ù¸¥ º¯È¯ ÇÔ¼ö´Â UDF°¡ SELECTÀý¿¡¼­ »ç¿ëµÇ´Â °æ¿ì °á°ú ±¸Á¶È­ À¯ÇüÀ» º¯È¯ÇØ¾ß ÇÕ´Ï´Ù. ´Ù¸¥ º¯È¯ ÇÔ¼ö¸¦ ÀÛ¼ºÇÏÁö ¾ÊÀ¸·Á¸é ´ÙÀ½ ¿¹¿¡¼­¿Í °°ÀÌ °üÂû ¸Þ¼Òµå¿¡¼­ SELECT¹®À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.

   SELECT Name
      FROM Employee
      WHERE MYUDF(Address)..city LIKE 'Tor%';

SQL º»¹® ·çƾÀ» »ç¿ëÇÑ ÇÔ¼ö º¯È¯ ±¸Çö

¿ÜºÎ ·çƾ°ú ¿ÀºêÁ§Æ®¸¦ ±³È¯ÇÒ ¶§ ¿ÀºêÁ§Æ®¸¦ ºÐÇØÇÏ°í ±¸¼ºÇÏ·Á¸é, 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¹®ÀÇ ±¸¹®À» ¼³¸íÇÕ´Ï´Ù.

  1. ÀÌ ÇÔ¼öÀÇ ¼­¸íÀº ¸Å°³º¯¼ö Çϳª¿Í Address_t À¯ÇüÀÇ ¿ÀºêÁ§Æ®¸¦ ½ÂÀÎÇÔÀ» ³ªÅ¸³À´Ï´Ù.
  2. RETURNS ROWÀýÀº ÇÔ¼ö°¡ Street, Number, City StateÀÇ 4°³ÀÇ Ä÷³À¸·Î ±¸¼ºµÈ ÇàÀ» ¸®ÅÏÇÔÀ» ³ªÅ¸³À´Ï´Ù.
  3. LANGUAGE SQLÀýÀº À̰ÍÀÌ ¿ÜºÎ ·çƾÀÌ ¾Æ´Ñ SQL º»¹® ÇÔ¼öÀÓÀ» ³ªÅ¸³À´Ï´Ù.
  4. RETURNÀýÀº ÇÔ¼ö º»¹®ÀÇ ½ÃÀÛÀ» Ç¥½ÃÇÕ´Ï´Ù. º»¹®Àº Address_t ¿ÀºêÁ§Æ®ÀÇ °¢ ¼Ó¼º¿¡ ´ëÇØ °üÂû ¸Þ¼Òµå¸¦ È£ÃâÇÏ´Â ´ÜÀÏ VALUESÀý·Î ±¸¼ºµË´Ï´Ù. °üÂû ¸Þ¼Òµå´Â ¿ÀºêÁ§Æ®¸¦ ÇÔ¼ö°¡ ÇàÀ¸·Î ¸®ÅÏÇÏ´Â ±âº» À¯Çü ¼¼Æ®·Î ºÐÇØÇÕ´Ï´Ù.

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)

´ÙÀ½ ¸ñ·ÏÀº ÀÌÀü ¸í·É¹®ÀÇ ±¸¹®À» ¼³¸íÇÕ´Ï´Ù.

  1. ÇÔ¼ö´Â ±âº» À¯Çü ¼Ó¼º ¼¼Æ®¸¦ ÃëÇÕ´Ï´Ù.
  2. ÇÔ¼ö´Â Address_t ±¸Á¶È­ À¯ÇüÀ» ¸®ÅÏÇÕ´Ï´Ù.
  3. ÇÔ¼ö´Â Address_t¿¡ ´ëÇØ ±¸¼ºÀÚ¸¦ È£ÃâÇϰí, °¢ ¼Ó¼º¿¡ ´ëÇØ º¯È¯À» È£ÃâÇÏ¿© ÀÔ·Â À¯Çü¿¡¼­ ¿ÀºêÁ§Æ®¸¦ ±¸¼ºÇÕ´Ï´Ù.

ÁÖ¼Ò¸¦ ¸®ÅÏÇÏ´Â ¸ðµç ÇÔ¼ö°¡ °°Àº ¼ø¼­¸¦ »ç¿ëÇÏ´Â ÇÑ 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

Ç¥ 14. myudf ¸Å°³º¯¼ö ¼Ó¼º
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. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ±¸Á¶È­ À¯Çü ¹ÙÀÎµå¾Æ¿ô


Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ±¸Á¶È­ À¯Çü ¹ÙÀÎµå¾Æ¿ô

  1. ¿ÀºêÁ§Æ®¸¦ ¸ÕÀú FROM SQL ÇÔ¼ö º¯È¯À¸·Î Àü´ÞÇÏ¿© À̸¦ ±× ±âº» À¯Çü ¼Ó¼ºÀ¸·Î ºÐÇØÇØ¾ß ÇÕ´Ï´Ù.
  2. FROM SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯Àº °ªÀ» VARCHAR ¶Ç´Â BLOB°ú °°Àº ´ÜÀÏ ³»Àå À¯ÇüÀ¸·Î ÀÎÄÚµåÇØ¾ß ÇÕ´Ï´Ù. À̸¦ ÅëÇØ Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥Àº Àüü °ªÀ» ´ÜÀÏ È£½ºÆ® º¯¼ö·Î ¼ö½ÅÇÕ´Ï´Ù.

    ÀÌ ÀÎÄÚµùÀº ¼Ó¼ºÀ» ¿¬¼Ó ÀúÀ念¿ª¿¡ º¹»ç(Çʼö Á¤·Ä¿¡ ´ëÇØ Á¦°ø)ÇÏ´Â °Íó·³ °£´ÜÇÒ ¼ö ÀÖ½À´Ï´Ù. ¼Ó¼ºÀÇ ÀÎÄÚµù°ú µðÄÚµùÀº ÀϹÝÀûÀ¸·Î SQL·Î ÀÌ·ç¾îÁöÁö ¾ÊÀ¸¹Ç·Î Ŭ¶óÀÌ¾ðÆ® º¯È¯Àº º¸Åë ¿ÜºÎ UDF·Î ÀÛ¼ºµË´Ï´Ù.

    Ç÷§Æû°£ÀÇ µ¥ÀÌÅÍ Ã³¸®¿¡ ´ëÇØ µ¥ÀÌÅÍ º¯È¯ °í·Á»çÇ×À» ÂüÁ¶ÇϽʽÿÀ.

  3. Ŭ¶óÀÌ¾ðÆ® ÇÁ·Î±×·¥Àº °ªÀ» ó¸®ÇÕ´Ï´Ù.

±×¸² 18Àº ÁÖ¼Ò¸¦ ´Ù½Ã µ¥ÀÌÅͺ£À̽º·Î Àü´ÞÇÏ´Â ¿ª ÇÁ·Î¼¼½º¸¦ º¸¿©ÁÝ´Ï´Ù.

±×¸² 18. Ŭ¶óÀÌ¾ðÆ®¿¡¼­ ±¸Á¶È­ À¯ÇüÀ¸·Î ¹ÙÀεù


Ŭ¶óÀÌ¾ðÆ®¿¡¼­ ±¸Á¶È­ À¯ÇüÀ¸·Î ¹ÙÀεù

  1. Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥Àº ÁÖ¼Ò¸¦ TO SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯¿¡¼­ ¿¹»óÇÑ Çü½ÄÀ¸·Î ÀÎÄÚµåÇÕ´Ï´Ù.
  2. TO SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯Àº ´ÜÀÏ ³»Àå À¯ÇüÀ» TO SQL ÇÔ¼ö º¯È¯¿¡ ´ëÇÑ ÀÔ·ÂÀ¸·Î »ç¿ëµÇ´Â ±âº» À¯Çü ¼Ó¼º ¼¼Æ®·Î ºÐÇØµË´Ï´Ù.
  3. TO SQL ÇÔ¼ö´Â ÁÖ¼Ò¸¦ ±¸¼ºÇϰí À̸¦ µ¥ÀÌÅͺ£À̽º·Î ¸®ÅÏÇÕ´Ï´Ù.

¿ÜºÎ UDF¸¦ »ç¿ëÇÑ Å¬¶óÀÌ¾ðÆ® º¯È¯ ±¸Çö

Ŭ¶óÀÌ¾ðÆ® º¯È¯À» ´Ù¸¥ ¿ÜºÎ 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´Â ¿ÜºÎ ·çƾ ¶Ç´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥À¸·Î ¹ÙÀÎµå¾Æ¿ôÇÏ´Â ¿©ºÎ¿¡ µû¶ó ÇÊ¿äÇÑ º¯È¯ ÇÔ¼ö¸¦ °áÁ¤ÇÒ ¶§ µµ¿òÀ» ÁÖ±â À§ÇÑ °ÍÀÔ´Ï´Ù.

Ç¥ 15. º¯È¯ ÇÔ¼öÀÇ Æ¯¼º
Ư¼º ¿ÜºÎ ·çƾ°úÀÇ °ª ±³È¯ Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥°úÀÇ °ª ±³È¯
º¯È¯ ¹æÇâ FROM SQL TO SQL FROM SQL TO SQL
¾î´ø °ÍÀÌ º¯È¯µË´Ï±î ·çƾ ¸Å°³º¯¼ö ·çƾ °á°ú Ãâ·Â È£½ºÆ® º¯¼ö ÀԷ ȣ½ºÆ® º¯¼ö
µ¿ÀÛ ºÐÇØ ±¸¼º ÀÎÄÚµå µðÄÚµå
º¯È¯ ÇÔ¼ö ¸Å°³º¯¼ö ±¸Á¶È­ À¯Çü ³»Àå À¯ÇüÀÇ Çà ±¸Á¶È­ À¯Çü ÇϳªÀÇ ³»Àå À¯Çü
º¯È¯ ÇÔ¼ö °á°ú ³»Àå À¯ÇüÀÇ Çà(¼Ó¼ºÀÏ °ÍÀÓ) ±¸Á¶È­ À¯Çü ÇϳªÀÇ ³»Àå À¯Çü ±¸Á¶È­ À¯Çü
´Ù¸¥ º¯È¯¿¡ Á¾¼ÓµÇ¾î ÀÖ½À´Ï±î? ¾Æ´Ï¿À ¾Æ´Ï¿À FROM SQL UDF º¯È¯ TO SQL UDF º¯È¯
¾ðÁ¦ º¯È¯ ±×·ìÀÌ ÁöÁ¤µË´Ï±î? UDF°¡ µî·ÏµÉ ¶§
Á¤Àû: »çÀü ó¸® ÄÄÆÄÀÏ ½Ã°£
µ¿Àû: Ư¼ö ·¹Áö½ºÅÍ

µ¥ÀÌÅÍ º¯È¯ °í·Á»çÇ×ÀÌ ÀÖ½À´Ï±î? ¾Æ´Ï¿À ¿¹
ÁÖ:ºñ·Ï ÀϹÝÀûÀÌÁö´Â ¾ÊÁö¸¸ ´ÙÀ½ÀÇ °æ¿ì°¡ ÂüÀÏ ¶§ Ŭ¶óÀÌ¾ðÆ® À¯Çüº¯È¯Àº ½ÇÁ¦·Î SQL·Î ÀÛ¼ºµË´Ï´Ù.
  • ±¸Á¶È­ À¯Çü¿¡ ¼Ó¼ºÀÌ Çϳª»ÓÀÔ´Ï´Ù.
  • ³»Àå À¯ÇüÀ¸·Î ¼Ó¼ºÀ» ÀÎÄÚµåÇÏ°í µðÄÚµåÇÏ´Â °ÍÀº SQL ¿¬»êÀÚ³ª ÇÔ¼öÀÇ Á¶ÇÕÀ¸·Î ÀÌ·ç¾îÁý´Ï´Ù.

ÀÌ·± °æ¿ì ÇÔ¼ö º¯È¯¿¡ ÀÇÁ¸ÇÏ¿© ±¸Á¶È­ À¯ÇüÀÇ °ªÀ» Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥°ú ±³È¯ÇÏÁö ¾Ê¾Æµµ µË´Ï´Ù.

DB2¿¡¼­ ºÎ¼Ó À¯Çü µ¥ÀÌÅÍ °Ë»ö(¹ÙÀÎµå¾Æ¿ô)

¾Õ Àý¿¡ ÀÖ´Â ´ëºÎºÐÀÇ Á¤º¸´Â ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¾Ë·ÁÁø Á¤È®ÇÑ À¯ÇüÀ» Àü´ÞÇÑ´Ù°í °£ÁÖÇÕ´Ï´Ù. µ¥ÀÌÅÍ ¸ðµ¨ÀÌ ºÎ¼Ó À¯ÇüÀ» »ç¿ëÇϸé Ä÷³¿¡ ÀÖ´Â °ªÀÌ ¿©·¯ ºÎ¼Ó À¯Çü Áß ÇϳªÀÏ °ÍÀÔ´Ï´Ù. ÀÌ Àý¿¡¼­´Â ½ÇÁ¦ ÀÔ·Â À¯ÇüÀ» ±â¹ÝÀ¸·Î ¿Ã¹Ù¸¥ º¯È¯ ÇÔ¼ö¸¦ µ¿ÀûÀ¸·Î ¼±ÅÃÇÒ ¼ö ÀÖ´Â ¹æ¹ýÀ» ¼³¸íÇÕ´Ï´Ù.

´ÙÀ½ SELECT¹®À» ¹ßÇàÇÑ´Ù°í ÇϽʽÿÀ.

   SELECT Address
      FROM Person
      INTO :hvaddr;

ÀÀ¿ëÇÁ·Î±×·¥¿¡´Â Address_tÀÇ ÀνºÅϽº, US_addr_t µîÀÌ ¸®ÅϵǴÂÁö ¾Ë ¼ö ÀÖ´Â ¹æ¹ýÀÌ ¾ø½À´Ï´Ù. ¿¹¸¦ ³Ê¹« º¹ÀâÇÏÁö ¾Ê°Ô À¯ÁöÇϱâ À§ÇØ Address_t³ª US_addr_t¸¸ ¸®Å쵃 ¼ö ÀÖ´Ù°í °¡Á¤ÇÕ´Ï´Ù. ÀÌ·± À¯ÇüÀÇ ±¸Á¶´Â ´Ù¸£¹Ç·Î ¼Ó¼ºÀ» ºÐÇØÇÏ´Â º¯È¯Àº ´Þ¶ó¾ß ÇÕ´Ï´Ù. ÀûÇÕÇÑ º¯È¯ÀÌ È£ÃâµÇµµ·Ï ´ÙÀ½ ´Ü°è¸¦ ¼öÇàÇϽʽÿÀ.

´Ü°è  1.

°¢°¢ÀÇ ÁÖ¼Ò º¯Çü¿¡ ´ëÇØ 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)

´Ü°è  2.

°¢ À¯Çü º¯Çü¸¶´Ù º¯È¯ ±×·ìÀ» Çϳª¾¿ ÀÛ¼ºÇϽʽÿÀ.

   CREATE TRANSFORM FOR Address_t
      funcgroup1 (FROM SQL WITH FUNCTION addresstofunc)
 
   CREATE TRANSFORM FOR US_addr_t
      funcgroup2 (FROM SQL WITH FUNCTION US_addresstofunc)

´Ü°è  3.

°¢ À¯Çü º¯Çü¸¶´Ù ¿ÜºÎ 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;
   }

´Ü°è  4.

ÀνºÅϽº¸¦ ó¸®Çϱâ À§ÇØ ¿Ã¹Ù¸¥ ¿ÜºÎ 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;

´Ü°è  5.

Addr_stream ¿ÜºÎ UDF¸¦ Address_tÀÇ FROM SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À¸·Î Ãß°¡ÇϽʽÿÀ.

   CREATE TRANSFORM GROUP FOR Address_t
      client_group (FROM SQL
      WITH FUNCTION Addr_stream)

ÁÖ:ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ type ¼ú¾î¸¦ »ç¿ëÇÏ¿© Á¶È¸¿¡¼­ ƯÁ¤ ÁÖ¼Ò À¯ÇüÀ» ÁöÁ¤Çϸé US_addr_t¿¡ ´ëÇØ Addr_streamÀ» FROM SQL·Î Ŭ¶óÀÌ¾ðÆ® º¯È¯¿¡ Ãß°¡ÇϽʽÿÀ. ÀÌ·¸°Ô Çϸé Á¶È¸°¡ Ưº°È÷ US_addr_tÀÇ ÀνºÅϽº¸¦ ¿äûÇÒ ¶§ Addr_streamÀÌ È£ÃâµË´Ï´Ù.

´Ü°è  6.

ÀÀ¿ëÇÁ·Î±×·¥À» 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¿¡¼­ ºÎ¼Ó À¯Çü µ¥ÀÌÅÍ °Ë»ö(¹ÙÀÎµå¾Æ¿ô)¿¡¼­ ¼³¸íµÈ ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ÁÖ¼Ò °ªÀ» ó¸®Çϸé, ÀÌ´Â º¯°æµÈ °ªÀ» ´Ù½Ã µ¥ÀÌÅͺ£À̽º¿¡ »ðÀÔÇØ¾ß ÇÕ´Ï´Ù. ´ÙÀ½ ±¸¹®À» »ç¿ëÇÏ¿© ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ ±¸Á¶È­ À¯ÇüÀ» DB2 µ¥ÀÌÅͺ£À̽º¿¡ »ðÀÔÇÑ´Ù°í °¡Á¤ÇØ º¸½Ê½Ã¿À.

   INSERT INTO person (Oid, Name, Address)
      VALUES ('n', 'Norm', :hvaddr);

±¸Á¶È­ À¯Çü¿¡ ´ëÇØ INSERT¹®À» ½ÇÇàÇÏ·Á¸é ÀÀ¿ëÇÁ·Î±×·¥Àº ´ÙÀ½ ´Ü°è¸¦ ¼öÇàÇØ¾ß ÇÕ´Ï´Ù.

´Ü°è  1.

ÁÖ¼ÒÀÇ °¢ º¯Çü¿¡ ´ëÇØ 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);

´Ü°è  2.

°¢ À¯Çü º¯Çü¸¶´Ù º¯È¯ ±×·ìÀ» Çϳª¾¿ ÀÛ¼ºÇϽʽÿÀ.

   CREATE TRANSFORM FOR Address_t
      funcgroup1 (TO SQL
      WITH FUNCTION functoaddress);
 
   CREATE TRANSFORM FOR US_addr_t
      funcgroup2 (TO SQL
      WITH FUNCTION functousaddr);

´Ü°è  3.

ÀÎÄÚµåµÈ ÁÖ¼Ò À¯ÇüÀ» °¢ À¯Çü º¯Çü¸¶´Ù Çϳª¾¿ ¸®ÅÏÇÏ´Â ¿ÜºÎ 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;
   }

´Ü°è  4.

ÀνºÅϽº¸¦ ó¸®Çϱâ À§ÇØ ¿Ã¹Ù¸¥ ¿ÜºÎ 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);

´Ü°è  5.

Address_t¿¡ ´ëÇØ stream_address UDF¸¦ TO SQL Ŭ¶óÀÌ¾ðÆ® º¯È¯À¸·Î Ãß°¡ÇϽʽÿÀ.

   CREATE TRANSFORM FOR Address_t
      client_group (TO SQL
      WITH FUNCTION stream_address);

´Ü°è  6.

ÀÀ¿ëÇÁ·Î±×·¥À» 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 ÂüÁ¶¼­¸¦ ÂüÁ¶ÇϽʽÿÀ.


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