À¯ÇüÈ Å×À̺íÀº Ư¼ºÀÌ CREATE TYPE¹®À¸·Î Á¤ÀÇµÈ ¿ÀºêÁ§Æ®ÀÇ ÀνºÅϽº¸¦ ½ÇÁ¦·Î ÀúÀåÇϱâ À§ÇØ »ç¿ëµË´Ï´Ù. CREATE TABLE¹®ÀÇ º¯ÇüÀ» »ç¿ëÇÏ¿© À¯ÇüÈ Å×À̺íÀ» ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù£®±¸Á¶ÈµÈ À¯ÇüÀÇ °ÔÃþ ±¸Á¶¸¦ ±â¹ÝÀ¸·Î ÀÔ·ÂµÈ Å×À̺íÀÇ °èÃþ ±¸Á¶¸¦ ÀÛ¼ºÇÒ ¼öµµ ÀÖ½À´Ï´Ù. µ¥ÀÌÅͺ£À̽º Å×ÀÌºí¿¡ ºÎ¼Ó À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀúÀåÇÏ·Á¸é ÇØ´ç Å×ÀÌºí °èÃþÀ» ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù.
´ÙÀ½ ¿¹´Â ±×¸² 9¿¡ ¼³¸íµÈ À¯Çü °èÃþÀ» ±â¹ÝÀ¸·Î Å×ÀÌºí °èÃþÀ» ÀÛ¼ºÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
¿©±â¿¡´Â BusinessUnit À¯ÇüÈ Å×À̺íÀ» ÀÛ¼ºÇÏ´Â SQLÀÌ ÀÖ½À´Ï´Ù.
CREATE TABLE BusinessUnit OF BusinessUnit_t (REF IS Oid USER GENERATED);
¿©±â¿¡´Â Person Å×ÀÌºí °èÃþ¿¡¼ Å×À̺íÀ» ÀÛ¼ºÇÏ´Â SQLÀÌ ÀÖ½À´Ï´Ù.
CREATE TABLE Person OF Person_t (REF IS Oid USER GENERATED); CREATE TABLE Employee OF Employee_t UNDER Person INHERIT SELECT PRIVILEGES (SerialNum WITH OPTIONS NOT NULL, Dept WITH OPTIONS SCOPE BusinessUnit ); CREATE TABLE Student OF Student_t UNDER Person INHERIT SELECT PRIVILEGES; CREATE TABLE Manager OF Manager_t UNDER Employee INHERIT SELECT PRIVILEGES; CREATE TABLE Architect OF Architect_t UNDER Employee INHERIT SELECT PRIVILEGES;
ÀÌÀü ¿¹¿¡¼ ÀÛ¼ºµÈ ù¹øÂ° À¯ÇüÈ Å×À̺íÀº BusinessUnitÀÔ´Ï´Ù. ÀÌ Å×À̺íÀº BusinessUnit_t À¯ÇüÀ¸·Î Á¤ÀǵǾúÀ¸¹Ç·Î, ÇØ´ç À¯ÇüÀÇ ÀνºÅϽº¸¦ º¸À¯ÇÕ´Ï´Ù. Áï, BusinessUnit_t ±¸Á¶È À¯ÇüÀÇ °¢ ¼Ó¼º¿¡ ÇØ´çÇÏ´Â Ä÷³ÀÌ ÀÖÀ¸¸ç, ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À̶ó´Â Ãß°¡ Ä÷³ÀÌ Çϳª ÀÖ½À´Ï´Ù.
À¯ÇüÈ Å×ÀÌºí¿¡´Â ´Ù¸¥ ¿ÀºêÁ§Æ®·Î ÂüÁ¶µÉ ¼ö ÀÖ´Â ¿ÀºêÁ§Æ®°¡ µé¾î ÀÖÀ¸¹Ç·Î, ¸ðµç À¯Çü¤È Å×À̺íÀº ù¹øÂ° Ä÷³À¸·Î ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» »ç¿ëÇÕ´Ï´Ù. ÀÌ ¿¹¿¡¼ ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³ÀÇ À¯ÇüÀº REF(BusinessUnit_t)ÀÔ´Ï´Ù. REF IS ... USER GENERATEDÀýÀ» »ç¿ëÇÏ¿© ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» ¸í¸íÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌ °æ¿ì Ä÷³ÀÇ À̸§Àº OidÀÔ´Ï´Ù. REF ISÀýÀÇ USER GENERATED ºÎºÐÀº »õ·Ó°Ô »ðÀÔµÈ ¸ðµç ÇàÀÇ ¿ÀºêÁ§Æ® ½Äº°ÀÚ¿¡ ´ëÇØ Ãʱ⠰ªÀ» Á¦°øÇØ¾ß ÇÔÀ» ³ªÅ¸³À´Ï´Ù. ¿ÀºêÁ§Æ® ½Äº°ÀÚ¸¦ »ðÀÔÇÑ ÈÄ ¿ÀºêÁ§Æ® ½Äº°ÀÚÀÇ °ªÀ» °»½ÅÇÒ ¼ö ¾ø½À´Ï´Ù. ¿ÀºêÁ§Æ® ½Äº°ÀÚ¸¦ ÀÚµ¿À¸·Î »ý¼ºÇϵµ·Ï DB2¸¦ ±¸¼º¿¡ ´ëÇØ¼´Â ½Ã½ºÅÛ »ý¼º ¿ÀºêÁ§Æ® ½Äº°ÀÚ Á¤ÀǸ¦ ÂüÁ¶ÇϽʽÿÀ.
Person À¯ÇüÈ Å×À̺íÀº Person_t À¯ÇüÀÔ´Ï´Ù. Á÷¿ø°ú ÇлýÀÇ ºÎ¼Ó À¯Çü ÀνºÅϽº¸¦ ÀúÀåÇÏ·Á¸é, Person Å×À̺í, Employee ¹× StudentÀÇ ¼ºêÅ×À̺íÀ» ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. µÎ °³ÀÇ Employee_t Ãß°¡ ºÎ¼Ó À¯Çüµµ Å×À̺íÀÌ ÇÊ¿äÇÕ´Ï´Ù. ÀÌ ¼ºêÅ×À̺íÀÇ À̸§Àº Manager¿Í ArchitectÀÔ´Ï´Ù. ºÎ¼Ó À¯ÇüÀÌ »óÀ§ À¯ÇüÀÇ ¼Ó¼ºÀ» °è½ÂÇÏ´Â °Íó·³ ¼ºêÅ×À̺íÀº ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» Æ÷ÇÔÇÑ ±× ¼öÆÛ Å×À̺íÀÇ Ä÷³À» °è½ÂÇÕ´Ï´Ù.
ÁÖ: | ¼ºêÅ×À̺íÀº ¼öÆÛ Å×À̺í°ú °°Àº ½ºÅ°¸¶¿¡ »óÁÖÇØ¾ß ÇÕ´Ï´Ù. |
±×·¯¹Ç·Î Employee ¼ºêÅ×À̺íÀÇ ÇàÀº Oid, Name, Age, Address, SerialNum, Salary ¹× Dept µî ÃÑ 7°³ÀÇ Ä÷³ÀÌ ÀÖ½À´Ï´Ù.
¼öÆÛ Å×ÀÌºí¿¡¼ Á¶À۵Ǵ SELECT, UPDATE ¶Ç´Â DELETE¹®Àº ¸ðµç ÇÏÀ§ Å×ÀÌºí¿¡¼µµ ÀÚµ¿À¸·Î Á¶À۵˴ϴÙ. ¿¹¸¦ µé¾î, Employee Å×ÀÌºí¿¡¼ UPDATE¹®Àº Employee, Manager ¹× Architect Å×ÀÌºí¿¡ ÀÖ´Â Çà¿¡ ¿µÇâÀ» ÁÙ ¼ö ÀÖÁö¸¸, Manager Å×À̺íÀÇ UPDATE¹®Àº Manager Çà¿¡¸¸ ¿µÇâÀ» ÁÙ ¼ö ÀÖ½À´Ï´Ù.
SELECT, INSERT ¶Ç´Â DELETE¹®ÀÌ Å×ÀÌºí¸¸ ÁöÁ¤Çϵµ·Ï Á¦ÇÑÇÏ·Á¸é ONLY¸¦ »ç¿ëÇÏ¿© ƯÁ¤ À¯ÇüÀÇ ¿ÀºêÁ§Æ® ¸®ÅÏ¿¡ ¼³¸íµÈ ´ë·Î ONLY ¿É¼ÇÀ» »ç¿ëÇϽʽÿÀ.
CREATE TABLE¹®ÀÇ INHERIT SELECT PRIVILEGESÀýÀº Employee¿Í °°ÀÌ °á°úÀÇ ¼ºêÅ×À̺íÀ» ÁöÁ¤Çϰí, Ãʱ⿡ UNDERÀýÀ» »ç¿ëÇÏ¿© ÀÛ¼ºµÈ Person°ú °°Àº ¼öÆÛ Å×À̺í°ú °°Àº »ç¿ëÀÚ¿Í ±×·ì¿¡¼ ¾×¼¼½ºµÉ ¼ö ÀÖ½À´Ï´Ù. ¼öÆÛ Å×ÀÌºí¿¡¼ ÇöÀç SELECT Ư±ÇÀ» º¸À¯ ÁßÀÎ »ç¿ëÀÚ³ª ±×·ì¿¡°Ô´Â »õ·Ó°Ô ÀÛ¼ºµÈ ¼ºêÅ×À̺íÀÇ SELECT Ư±ÇÀÌ ºÎ¿©µË´Ï´Ù. ¼ºêÅ×À̺í ÀÛ¼ºÀÚ´Â SELECT Ư±ÇÀÇ ±ÇÇÑ ÁØ »ç¿ëÀÚÀÔ´Ï´Ù. ¼ºêÅ×ÀÌºí¿¡¼ DELETE¿Í UPDATE¿Í °°Àº Ư±ÇÀ» ÁöÁ¤ÇÏ·Á¸é, ÀÏ¹Ý Å×ÀÌºí¿¡¼ Ư±ÇÀ» ÁöÁ¤ÇÒ ¶§ »ç¿ëÇÑ °Í°ú °°Àº ¸í½ÃÀû GRANT ¶Ç´Â REVOKE¹®À» ¹ßÇàÇØ¾ß ÇÕ´Ï´Ù. INHERIT SELECT PRIVILEGESÀý¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº SQL ÂüÁ¶¼¿¡¼ ÂüÁ¶ÇϽʽÿÀ.
Ư±ÇÀº Å×ÀÌºí °èÃþÀÇ ¸ðµç ·¹º§¿¡¼ º°µµ·Î ±ÇÇÑ ºÎ¿©µÇ°í ±ÇÇÑ Ãë¼ÒµÇ¾î¾ß ÇÕ´Ï´Ù£® ¼ºêÅ×À̺íÀ» ÀÛ¼ºÇÏ¸é ±× ¼ºêÅ×ÀÌºí¿¡¼ °è½ÂµÈ SELECT Ư±ÇÀ» ´Ù½Ã È£ÃâÇÒ ¼öµµ ÀÖ½À´Ï´Ù. ¼ºêÅ×ÀÌºí¿¡¼ °è½ÂµÈ SELECT Ư±ÇÀ» ÀçÈ£ÃâÇϸé, ¼öÆÛ Å×ÀÌºí¿¡ ´ëÇØ SELECT Ư±ÇÀÌ ÀÖ´Â »ç¿ëÀÚ°¡ ¼ºêÅ×ÀÌºí¿¡¸¸ ³ªÅ¸³ª´Â Ä÷³À» º¼ ¼ö ¾ø°Ô ¸·À» ¼ö ÀÖ½À´Ï´Ù. ¼ºêÅ×ÀÌºí¿¡¼ °è½ÂµÈ SELECT Ư±ÇÀ» ÀçÈ£ÃâÇϸé, ¼öÆÛ Å×ÀÌºí¿¡ ´ëÇØ SELECT Ư±Ç¸¸ ÀÖ´Â »ç¿ëÀÚ°¡ ¼ºêÅ×À̺í ÇàÀÇ ¼öÆÛ Å×À̺í Ä÷³À» º¼ ¼ö ¾øµµ·Ï Á¦ÇÑÇÒ ¼ö ÀÖ½À´Ï´Ù. »ç¿ëÀÚ´Â ¼öÆÛ Å×ÀÌºí¿¡ ÇÊ¿äÇÑ Æ¯±ÇÀ» °¡Áö°í ÀÖÀ¸¸é ±× ¼öÆÛ Å×ÀÌºí¿¡¼¸¸ Á÷Á¢ ÀÛ¾÷ÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×·¯¹Ç·Î, »ç¿ëÀÚ°¡ ¼ºêÅ×ÀÌºí¿¡¼ °ü¸®ÀÚÀÇ º¸³Ê½º¸¦ ¼±ÅÃÇÏÁö ¸øÇϵµ·Ï ¸·À¸·Á¸é, ±× Å×ÀÌºí¿¡ ´ëÇØ SELECT Ư±ÇÀ» ÀçÈ£ÃâÇϰí ÀÌ Á¤º¸°¡ ÇÊ¿äÇÑ »ç¿ëÀÚ¿¡°Ô¸¸ À̸¦ ºÎ¿©ÇϽʽÿÀ.
WITH OPTIONSÀýÀ» »ç¿ëÇϸé À¯ÇüÈ Å×ÀÌºí¿¡¼ °³º° Ä÷³¿¡ Àû¿ë½Ãų ¼ö ÀÖ´Â ¿É¼ÇÀ» Á¤ÀÇÇÒ ¼ö ÀÖ½À´Ï´Ù. WITH OPTIONSÀÇ Çü½ÄÀº ´ÙÀ½°ú °°½À´Ï´Ù.
column-name WITH OPTIONS column-options
¿©±â¼ column-nameÀº CREATE TABLE ¶Ç´Â ALTER TABLE¹®¿¡ ÀÖ´Â Ä÷³ÀÇ À̸§À» ³ªÅ¸³»°í, column-optionsÀº Ä÷³¿¡ ´ëÇØ Á¤ÀÇµÈ ¿É¼ÇÀ» ³ªÅ¸³À´Ï´Ù.
¿¹¸¦ µé¾î, »ç¿ëÀÚ°¡ SerialNum Ä÷³¿¡ ³Î(NULL)À» »ðÀÔÇÏÁö ¸øÇϵµ·Ï ¸·À¸·Á¸é ´ÙÀ½°ú °°ÀÌ NOT NULL Ä÷³ ¿É¼ÇÀ» ÁöÁ¤ÇϽʽÿÀ.
(SerialNum WITH OPTIONS NOT NULL)
WITH OPTIONS´Â Ä÷³ÀÇ SCOPE¸¦ ÁöÁ¤ÇÒ ¶§ »ç¿ëµÉ ¼öµµ ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, Employee Å×À̺í°ú ±× ¼ºêÅ×ÀÌºí¿¡¼ ÀýÀº ´ÙÀ½°ú °°½À´Ï´Ù.
Dept WITH OPTIONS SCOPE BusinessUnit
ÀÌ Å×À̺íÀÇ Dept Ä÷³°ú ±× ¼ºêÅ×ÀÌºí¿¡ BusinessUnitÀÇ ¹üÀ§°¡ ÀÖÀ½À» ¼±¾ðÇÕ´Ï´Ù. Áï, Employee Å×ÀÌºí¿¡¼ ÀÌ Ä÷³ÀÇ ÂüÁ¶ °ªÀº BusinessUnit Å×À̺íÀÇ ¿ÀºêÁ§Æ®¸¦ ÂüÁ¶Çϱâ À§ÇÑ °ÍÀÔ´Ï´Ù.
¿¹¸¦ µé¾î, Employee Å×ÀÌºí¿¡¼ ´ÙÀ½ Á¶È¸´Â ÂüÁ¶ ÇØÁ¦ ¿¬»êÀÚ¸¦ »ç¿ëÇÏ¿© DB2¿¡°Ô Dept Ä÷³¿¡¼ BusinessUnit Å×À̺í·ÎÀÇ °æ·Î¸¦ µû¸£µµ·Ï Áö½ÃÇÕ´Ï´Ù. ÂüÁ¶ ÇØÁ¦ ¿¬»êÀÚ´Â Name Ä÷³ÀÇ °ªÀ» ¸®ÅÏÇÕ´Ï´Ù.
SELECT Name, Salary, Dept->Name FROM Employee;
ÂüÁ¶ ¹× ÂüÁ¶ ¹üÀ§¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ÂüÁ¶ À¯Çü »ç¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ÀÌÀü ¿¹¿¡¼ ±¸Á¶È À¯ÇüÀ» ÀÛ¼ºÇϰí ÇØ´ç Å×À̺í°ú ¼ºêÅ×À̺íÀ» ÀÛ¼ºÇÑ ´ÙÀ½, µ¥ÀÌÅͺ£À̽ºÀÇ ±¸Á¶´Â ±×¸² 11°ú °°½À´Ï´Ù.
![]() |
°èÃþÀÌ ¼³Á¤µÇ¸é INSERT¹®À» »ç¿ëÇÏ¿© Å×ÀÌºí¿¡¼ µ¥ÀÌÅ͸¦ ó¸®ÇÒ ¼ö ÀÖ½À´Ï´Ù. ´Ü, ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³¿¡¼ µ¥ÀÌÅ͸¦ ó¸®ÇØ¾ß Çϸç, ¼±ÅÃÀûÀ¸·Î °¢ Å×À̺íÀ̳ª ¼ºêÅ×ÀÌºí¿¡ ÀÖ´Â ¿ÀºêÁ§Æ®ÀÇ Ãß°¡ ¼Ó¼º µ¥ÀÌÅ͸¦ ó¸®ÇØ¾ß ÇÕ´Ï´Ù. ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³Àº ¸í¹éÇÑ À¯Çü ÁöÁ¤ÀÎ REF À¯ÇüÀ̹ǷÎ, ±¸Á¶È À¯ÇüÀ» ÀÛ¼ºÇßÀ» ¶§ ½Ã½ºÅÛ¿¡¼ »ý¼ºÇÑ À¯Çüº¯È¯ ÇÔ¼ö¸¦ »ç¿ëÇÏ¿© »ç¿ëÀÚ°¡ Á¦°øÇÑ ¿ÀºêÁ§Æ® ½Äº°ÀÚ °ªÀ» À¯Çüº¯È¯ÇØ¾ß ÇÕ´Ï´Ù.
INSERT INTO BusinessUnit (Oid, Name, Headcount) VALUES(BusinessUnit_t(1), 'Toy', 15); INSERT INTO BusinessUnit (Oid, Name, Headcount) VALUES(BusinessUnit_t(2), 'Shoe', 10); INSERT INTO Person (Oid, Name, Age) VALUES(Person_t('a'), 'Andrew', 20); INSERT INTO Person (Oid, Name, Age) VALUES(Person_t('b'), 'Bob', 30); INSERT INTO Person (Oid, Name, Age) VALUES(Person_t('c'), 'Cathy', 25); INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000, BusinessUnit_t(1)); INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('e'), 'Eva', 31, 83, 45000, BusinessUnit_t(2)); INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('f'), 'Franky', 28, 214, 39000, BusinessUnit_t(2)); INSERT INTO Student (Oid, Name, Age, SerialNum, GPA) VALUES(Student_t('g'), 'Gordon', 19, '10245', 4.7); INSERT INTO Student (Oid, Name, Age, SerialNum, GPA) VALUES(Student_t('h'), 'Helen', 20, '10357', 3.5); INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus) VALUES(Manager_t('i'), 'Iris', 35, 251, 55000, BusinessUnit_t(1), 12000); INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus) VALUES(Manager_t('j'), 'Christina', 10, 317, 85000, BusinessUnit_t(1), 25000); INSERT INTO Manager (Oid, Name, Age, SerialNum, Salary, Dept, Bonus) VALUES(Manager_t('k'), 'Ken', 55, 482, 105000, BusinessUnit_t(2), 48000); INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption) VALUES(Architect_t('l'), 'Leo', 35, 661, 92000, BusinessUnit_t(2), 20000);
ÀÌÀü ¿¹¿¡¼´Â ÁÖ¼Ò¸¦ »ðÀÔÇÏÁö ¾Ê½À´Ï´Ù. ±¸Á¶È À¯Çü °ªÀ» Ä÷³¿¡ »ðÀÔÇÏ´Â ¹æ¹ý¿¡ ´ëÇÑ ³»¿ëÀº ±¸Á¶È À¯Çü °ªÀÌ ÀÖ´Â Çà »ðÀÔÀ» ÂüÁ¶ÇϽʽÿÀ.
ÇàÀ» À¯ÇüÈ Å×ÀÌºí¿¡ »ðÀÔÇÒ ¶§ »ðÀÔµÈ Çà¿¡¼ ù¹øÂ° °ªÀº Å×ÀÌºí¿¡ »ðÀԵǴ µ¥ÀÌÅÍÀÇ ¿ÀºêÁ§Æ® ½Äº°ÀÚ¿©¾ß ÇÕ´Ï´Ù. ¶ÇÇÑ, ºñÀ¯ÇüÈ Å×ÀÌºí¿¡¼¿Í °°ÀÌ NOT NULL·Î Á¤ÀÇµÈ ¸ðµç Ä÷³¿¡ µ¥ÀÌÅ͸¦ Á¦°øÇØ¾ß ÇÕ´Ï´Ù. ¸¶Áö¸·À¸·Î, ÀûÀýÇÑ À¯ÇüÀÇ ¸ðµç ÂüÁ¶ °ª Ç¥Çö½ÄÀ» »ç¿ëÇÏ¿© ÂüÁ¶ ¼Ó¼ºÀ» ÃʱâÈÇÒ ¼ö ÀÖÀ½¿¡ À¯ÀÇÇϽʽÿÀ. ÀÌÀü ¿¹¿¡¼ Á÷¿øÀÇ Dept ÂüÁ¶´Â ÀûÀýÇÑ À¯Çüº¯È¯ »ó¼ö·Î ÀԷµ˴ϴÙ. ±×·¯³ª ºÎ¼Ó Á¶È¸¸¦ »ç¿ëÇÏ¿© ´ÙÀ½ ¿¹¿¡¼¿Í °°ÀÌ ÂüÁ¶¸¦ ȹµæÇÒ ¼öµµ ÀÖ½À´Ï´Ù.
INSERT INTO Architect (Oid, Name, Age, SerialNum, Salary, Dept, StockOption) VALUES(Architect_t('m'), 'Brian', 7, 882, 112000, (SELECT Oid FROM BusinessUnit WHERE name = 'Toy'), 30000);
°¢°¢ÀÇ ±¸Á¶È À¯Çü¿¡ ´ëÇØ DB2´Â ÇØ´ç ÂüÁ¶ À¯ÇüÀ» Áö¿øÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, Person_t À¯ÇüÀ» ÀÛ¼ºÇϸé DB2´Â ÀÚµ¿À¸·Î REF(Person_t)ÀÇ À¯ÇüÀ» ÀÛ¼ºÇÕ´Ï´Ù. REF(Person_t) À¯Çü(¹× Person_tÀÇ ¸ðµç ºÎ¼Ó À¯ÇüÀÇ REF À¯Çü)Àº ±âº»ÀûÀ¸·Î VARCHAR (16) FOR BIT DATAÀÌÁö¸¸ CREATE TYPE¹®¿¡ ´ëÇØ REF USINGÀýÀ» »ç¿ëÇÏ¿© ´Ù¸¥ Ç¥Çö À¯ÇüÀ» ¼±ÅÃÇÒ ¼ö ÀÖ½À´Ï´Ù. ±× ÂüÁ¶ À¯ÇüÀº ±¸Á¶È À¯ÇüÀÇ ÀνºÅϽº¸¦ ÀúÀåÇϱâ À§ÇØ ÀÛ¼ºÇÏ´Â À¯ÇüÈ Å×À̺íÀÇ ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³ÀÇ ±âº»ÀÔ´Ï´Ù. ¿¹¸¦ µé¾î, ÂüÁ¶ À¯ÇüÀÇ ±âº» Ç¥Çö À¯ÇüÀ» »ç¿ëÇÏ¿© People_t ·çÆ® À¯ÇüÀ» ÀÛ¼ºÇÏ¸é ¿¬°üµÈ People Å×À̺íÀÇ ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³Àº VARCHAR(16) FOR BIT DATA¸¦ ±â¹ÝÀ¸·Î ÇÕ´Ï´Ù.
ÂüÁ¶ À¯ÇüÀº ¸í¹éÇÏ°Ô À¯Çü ÁöÁ¤µË´Ï´Ù. ÂüÁ¶¸¦ »ó¼ö¿¡ ºñ±³ÇÏ·Á¸é, »ó¼ö¸¦ ÀûÀýÇÑ ÂüÁ¶ À¯ÇüÀ¸·Î À¯Çüº¯È¯Çϰųª ÂüÁ¶ À¯ÇüÀ» ±âº» À¯ÇüÀ¸·Î À¯Çüº¯È¯ÇÑ ÈÄ ºñ±³¸¦ ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù. ÁÖ¾îÁø À¯Çü °èÃþ¿¡¼ ¸ðµç ÂüÁ¶´Â µ¿ÀÏÇÑ ÂüÁ¶ Ç¥Çö À¯ÇüÀÌ ÀÖ½À´Ï´Ù. S¿Í T¿¡ °øÅë »óÀ§ À¯ÇüÀÌ ÀÖ´Ù´Â Á¶°ÇÀ» ÅëÇØ REF(S)¿Í REF(T)¸¦ ºñ±³ÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³ÀÇ °íÀ¯ÇÔÀº Å×ÀÌºí¿¡¼¸¸ °¿äµÇ¹Ç·Î, ÇϳªÀÇ Å×ÀÌºí °èÃþ¿¡ ÀÖ´Â REF(T)ÀÇ °ª°ú ´Ù¸¥ Å×ÀÌºí °èÃþ¿¡ ÀÖ´Â REF(T)ÀÇ °ªÀº ¼·Î ´Ù¸¥ ÇàÀ» ÂüÁ¶Çصµ °°À» ¼ö ÀÖ½À´Ï´Ù.
CREATE TABLEÀÇ WITH OPTIONSÀýÀ» »ç¿ëÇÏ¿© Å×À̺í Çϳª¿¡ ÀÖ´Â Ä÷³°ú °°Àº Å×ÀÌºí ¶Ç´Â ´Ù¸¥ Å×ÀÌºí¿¡ ÀÖ´Â ¿ÀºêÁ§Æ®°£¿¡ Á¸ÀçÇÏ´Â °ü°è¸¦ Á¤ÀÇÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, ±×¸² 12¿¡¼¿Í °°ÀÌ BusinessUnit¿Í Person Å×ÀÌºí °èÃþ¿¡¼ °¢ Á÷¿øÀÇ ºÎ¼´Â ½ÇÁ¦·Î BusinessUnit¿¡ ÀÖ´Â ¿ÀºêÁ§Æ®¸¦ ÂüÁ¶ÇÕ´Ï´Ù. ÁÖ¾îÁø ÂüÁ¶ Ä÷³ÀÇ ¸ñÀûÁö ¿ÀºêÁ§Æ®¸¦ Á¤ÀÇÇÏ·Á¸é WITH OPTIONSÀý¿¡¼ SCOPE Ű¿öµå¸¦ »ç¿ëÇϽʽÿÀ.
±×¸² 12. BusinessUnit ¿ÀºêÁ§Æ®¸¦ ÂüÁ¶ÇÏ´Â Dept ¼Ó¼º
![]() |
°°Àº À¯ÇüÈ Å×ÀÌºí¿¡¼µµ ¿ÀºêÁ§Æ®¿¡ ´ëÇØ ¹üÀ§°¡ Á¤ÇØÁø ÂüÁ¶¸¦ Á¤ÀÇÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½ ¿¹¿¡ ÀÖ´Â ¸í·É¹®Àº ºÎºÐ¿¡ ´ëÇØ À¯ÇüÈ Å×À̺íÀ» Çϳª ÀÛ¼ºÇϰí, °ø±ÞÀÚ¿¡ ´ëÇØ À¯ÇüÈ Å×À̺íÀ» Çϳª ÀÛ¼ºÇÕ´Ï´Ù. ÂüÁ¶ À¯Çü Á¤ÀǸ¦ Ç¥½ÃÇϱâ À§ÇØ »ùÇÃÀº À¯ÇüÀ» ÀÛ¼ºÇÒ ¶§ »ç¿ëµÇ´Â ¸í·É¹®À» Æ÷ÇÔÇϱ⵵ ÇÕ´Ï´Ù.
CREATE TYPE Company_t AS (name VARCHAR(30), location VARCHAR(30)) MODE DB2SQL; CREATE TYPE Part_t AS (Descript VARCHAR(20), Supplied_by REF(Company_t), Used_in REF(part_t)) MODE DB2SQL; CREATE TABLE Suppliers OF Company_t (REF IS suppno USER GENERATED); CREATE TABLE Parts OF Part_t (REF IS Partno USER GENERATED, Supplied_by WITH OPTIONS SCOPE Suppliers, Used_in WITH OPTIONS SCOPE Parts);
![]() |
¹üÀ§ ÁöÁ¤µÈ ÂüÁ¶ ¾øÀÌ ¿ÜºÎ Á¶ÀÎÀ̳ª »ó°ü ºÎ¼Ó Á¶È¸·Î ÀÛ¼ºµÇ¾úÀ» ¶§, Á¶È¸´Â ¹üÀ§ ÁöÁ¤µÈ ÂüÁ¶¸¦ »ç¿ëÇÏ¿© ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÚ¼¼ÇÑ ³»¿ëÀº ÂüÁ¶¸¦ ÇØÁ¦ÇÏ´Â Á¶È¸¿¡¼ ÂüÁ¶ÇϽʽÿÀ.
ºñ·Ï ¹üÀ§ ÁöÁ¤µÈ ÂüÁ¶°¡ Å×ÀÌºí¿¡ ÀÖ´Â ¿ÀºêÁ§Æ®°£ÀÇ °ü°è¸¦ Á¤ÀÇÇØµµ ÂüÁ¶ ¹«°á¼º °ü°è¿Í´Â ´Ù¸¨´Ï´Ù. ¹üÀ§´Â ¸ñÇ¥ Å×ÀÌºí¿¡ ´ëÇÑ Á¤º¸¸¦ Á¦°øÇϱ⸸ ÇÕ´Ï´Ù. ±× Á¤º¸´Â ¸ñÇ¥ Å×ÀÌºí¿¡¼ ¿ÀºêÁ§Æ®¸¦ ÂüÁ¶ ÇØÁ¦ÇÒ ¶§ »ç¿ëµË´Ï´Ù. ¹üÀ§ ÁöÁ¤µÈ ÂüÁ¶´Â ´Ù¸¥ Å×ÀÌºí¿¡ ÀÖ´Â °ªÀ» ¿ä±¸Çϰųª °¿äÇÏÁö ¾Ê½À´Ï´Ù. ¿¹¸¦ µé¾î, Employee Å×ÀÌºí¿¡ ÀÖ´Â Dept Ä÷³¿¡´Â BusinessUnit Å×ÀÌºí¿¡ ¾ø´Â BusinessUnit ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³¿¡ ´ëÇÑ ÂüÁ¶°¡ ÀÖ½À´Ï´Ù. ÀÌ·± °ü°èÀÇ ¿ÀºêÁ§Æ®°¡ Á¸ÀçÇÏ·Á¸é Å×ÀÌºí°£¿¡ ÂüÁ¶ ¹«°á¼ºÀ» Ãß°¡ÇØ¾ß ÇÕ´Ï´Ù. ÀÚ¼¼ÇÑ ³»¿ëÀº ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³¿¡ ´ëÇÑ Á¦ÇÑÁ¶°Ç ÀÛ¼º¿¡¼ ÂüÁ¶ÇϽʽÿÀ.
CREATE VIEW¹®À» »ç¿ëÇÏ¿© À¯ÇüÈ ºä¸¦ ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. ¿¹¸¦ µé¾î, À¯ÇüÈ BusinessUnit Å×À̺íÀÇ ºä¸¦ ÀÛ¼ºÇÏ·Á¸é, ¿øÇÏ´Â ¼Ó¼ºÀÌ ÀÖ´Â ±¸Á¶È À¯ÇüÀ» Á¤ÀÇÇÏ°í ±× À¯ÇüÀ» »ç¿ëÇÏ¿© À¯ÇüÈ ºä¸¦ ÀÛ¼ºÇÕ´Ï´Ù.
CREATE TYPE VBusinessUnit_t AS (Name VARCHAR(20)) MODE DB2SQL; CREATE VIEW VBusinessUnit OF VBusinessUnit_t MODE DB2SQL (REF IS VObjectID USER GENERATED) AS SELECT VBusinessUnit_t(VARCHAR(Oid)), Name FROM BusinessUnit;
CREATE VIEW¹®ÀÇ OFÀýÀº DB2¿¡°Ô Ç¥½ÃµÈ ±¸Á¶È À¯ÇüÀÇ ¼Ó¼ºÀ» ºä Ä÷³ÀÇ ±âº»°ªÀ¸·Î »ç¿ëÇϵµ·Ï Áö½ÃÇÕ´Ï´Ù. ÀÌ °æ¿ì DB2´Â ºäÀÇ Ä÷³ÀÇ ±â¹ÝÀ¸·Î VBusinessUnit_t ±¸Á¶È À¯ÇüÀ» »ç¿ëÇÕ´Ï´Ù.
ºäÀÇ VObjectID Ä÷³¿¡´Â REF(VBusinessUnit_t)ÀÇ À¯ÇüÀÌ ÀÖ½À´Ï´Ù. REF(BusinessUnit_t)ÀÇ À¯Çü¿¡¼ REF(VBusinessUnit_t)ÀÇ À¯ÇüÀ¸·Î À¯Çüº¯È¯ÇÒ ¼ö ¾øÀ¸¹Ç·Î, ¸ÕÀú BusinessUnit Å×ÀÌºí¿¡¼ Oid Ä÷³ °ªÀ» VARCHAR µ¥ÀÌÅÍ À¯ÇüÀ¸·Î À¯Çüº¯È¯ÇÑ ÈÄ VARCHAR µ¥ÀÌÅÍ À¯Çü¿¡¼ REF(VBusinessUnit_t) µ¥ÀÌÅÍ À¯ÇüÀ¸·Î À¯Çüº¯È¯ÇÕ´Ï´Ù.
MODE DB2SQLÀýÀº À¯ÇüÈ ºäÀÇ ¸ðµå¸¦ ÁöÁ¤ÇÕ´Ï´Ù. ÀÌ ¸ðµå´Â ÇöÀç Áö¿øµÇ´Â À¯ÀÏÇÑ À¯È¿ ¸ðµåÀÔ´Ï´Ù£®
REF IS...ÀýÀº ÀÔ·ÂµÈ CREATE TABLE¹®ÀÇ ÇØ´ç Àý°ú µ¿ÀÏÇÕ´Ï´Ù. ÀÌ´Â ºäÀÇ Ã¹¹øÂ° Ä÷³ÀÎ ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³ÀÇ ºä(ÀÌ °æ¿ì VObjectID) À̸§À» Á¦°øÇÕ´Ï´Ù. ·çÆ® À¯Çü¿¡¼ À¯ÇüÈ ºä¸¦ ÀÛ¼ºÇÏ¸é ºä¿¡ ´ëÇØ ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» ÁöÁ¤ÇØ¾ß ÇÕ´Ï´Ù. ºÎ¼Ó À¯Çü¿¡¼ À¯ÇüÈ ºä¸¦ ÀÛ¼ºÇÏ¸é ºä´Â ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» °è½ÂÇÒ ¼ö ÀÖ½À´Ï´Ù.
USER GENERATEDÀýÀº ÇàÀ» »ðÀÔÇÒ ¶§ »ç¿ëÀÚ°¡ ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³ÀÇ Ãʱ⠰ªÀ» Á¦°øÇØ¾ß ÇÔÀ» ÁöÁ¤ÇÕ´Ï´Ù. ÀÏ´Ü »ðÀÔµÇ¸é ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» °»½ÅÇÒ ¼ö ¾ø½À´Ï´Ù.
AS Ű¿öµå ´ÙÀ½¿¡ ¿À´Â ºäÀÇ º»¹®Àº ºäÀÇ ³»¿ëÀ» ÆÇº°ÇÏ´Â SELECT¹®ÀÔ´Ï´Ù. ÀÌ SELECT¹®¿¡¼ ¸®ÅÏµÈ Ä÷³ À¯ÇüÀº Ãʱ⠿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» Æ÷ÇÔÇÏ¿© À¯ÇüÈ ºäÀÇ Ä÷³ À¯Çü°ú ȣȯµÉ ¼ö ÀÖ¾î¾ß ÇÕ´Ï´Ù.
À¯ÇüÈ ºä °èÃþÀÇ ÀÛ¼ºÀ» ¼³¸íÇϱâ À§ÇØ ´ÙÀ½ ¿¹´Â ÀϺΠ¹Î°¨ µ¥ÀÌÅ͸¦ »ý·«Çϰí, ÀÌÀü¿¡ À¯ÇüÈ Å×À̺í ÀÛ¼ºÇÏ¿¡¼ ÀÛ¼ºµÈ Person Å×ÀÌºí °èÃþ¿¡¼ ÀϺΠÀ¯Çü ±¸º°À» Á¦°ÅÇÏ´Â ºä °èÃþ¸¦ Á¤ÀÇÇÕ´Ï´Ù.
CREATE TYPE VPerson_t AS (Name VARCHAR(20)) MODE DB2SQL; CREATE TYPE VEmployee_t UNDER VPerson_t AS (Salary INT, Dept REF(VBusinessUnit_t)) MODE DB2SQL; CREATE VIEW VPerson OF VPerson_t MODE DB2SQL (REF IS VObjectID USER GENERATED) AS SELECT VPerson_t (VARCHAR(Oid)), Name FROM ONLY(Person); CREATE VIEW VEmployee OF VEmployee_t MODE DB2SQL UNDER VPerson INHERIT SELECT PRIVILEGES (Dept WITH OPTIONS SCOPE VBusinessUnit) AS SELECT VEmployee_t(VARCHAR(Oid)), Name, Salary, VBusinessUnit_t(VARCHAR(Dept)) FROM Employee;
µÎ CREATE TYPE¹®Àº ÀÌ ¿¹ÀÇ ¿ÀºêÁ§Æ® ºä °èÃþÀ» ÀÛ¼ºÇÏ´Â µ¥ ÇÊ¿äÇÑ ±¸Á¶È À¯ÇüÀ» ÀÛ¼ºÇÕ´Ï´Ù£®
À§¿¡¼ ù¹øÂ° À¯ÇüÈ CREATE VIEW¹®Àº °èÃþÀÇ ·çÆ® ºäÀÎ VPersonÀ» ÀÛ¼ºÇϸç, VBusinessUnit ºä Á¤ÀÇ¿Í ¸Å¿ì ºñ½ÁÇÕ´Ï´Ù£® Â÷ÀÌÁ¡Àº ONLY(Person)À» »ç¿ëÇÏ¿© ¼ºêÅ×À̺íÀÌ ¾Æ´Ñ Person Å×ÀÌºí¿¡ ÀÖ´Â Person Å×ÀÌºí °èÃþÀÇ Çุ VPerson ºä¿¡ Æ÷ÇÔ½ÃŲ´Ù´Â °ÍÀÔ´Ï´Ù. ±×·¯¸é VPersonÀÇ Oid °ªÀÌ VEmployeeÀÇ Oid °ª¿¡ ºñÇØ °íÀ¯ÇØÁý´Ï´Ù. µÎ ¹øÂ° CREATE VIEW¹®Àº VPerson ºäÀÇ VEmployee ¼ºêºä¸¦ ÀÛ¼ºÇÕ´Ï´Ù. CREATE TABLE...UNDER¹®¿¡¼ÀÇ UNDERÀý°ú °°ÀÌ UNDERÀýÀº ºä °èÃþÀ» ¼³Á¤ÇÕ´Ï´Ù. °°Àº ½ºÅ°¸¶¿¡¼ ÇØ´ç ¼öÆÛºä·Î ¼ºêºä¸¦ ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù. À¯ÇüÈ Å×À̺íó·³ ¼ºêºä´Â ¼öÆÛºä¿¡¼ Ä÷³À» °è½ÂÇÕ´Ï´Ù. VEmployee ºä¿¡ ÀÖ´Â ÇàÀº VPerson¿¡¼ VObjectID¿Í Name Ä÷³À» °è½ÂÇϰí, VEmployee_t À¯Çü¿¡ ¿¬°üµÈ Ãß°¡ Salary¿Í Dept Ä÷³ÀÌ ÀÖ½À´Ï´Ù.
INHERIT SELECT PRIVILEGESÀýÀº À¯ÇüÈ REATE TABLE¹®À» ¹ßÇàÇÒ ¶§¿Í °°ÀÌ CREATE VIEW¹®À» ¹ßÇàÇÒ ¶§ °°Àº È¿°ú¸¦ °¡Áö°í ÀÖ½À´Ï´Ù. INHERIT SELECT PRIVILEGESÀý¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº SELECT Ư±Ç °è½Â ÁöÁ¤¸¦ ÂüÁ¶ÇϽʽÿÀ. À¯ÇüÈ ºä Á¤ÀÇ¿¡¼ÀÇ WITH OPTIONSÀýÀº À¯ÇüÈ Å×À̺í Á¤ÀÇ¿¡¼¿Í °°Àº È¿°ú¸¦ °¡Áý´Ï´Ù. WITH OPTIONSÀýÀ» »ç¿ëÇϸé SCOPE¿Í °°Àº Ä÷³ ¿É¼ÇÀ» ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. READ ONLYÀýÀº ¼öÆÛºä Ä÷³À» Àбâ Àü¿ëÀ¸·Î °Á¦ Ç¥½ÃÇÏ¿© È÷ÈÄ¿¡ ¼ºêºä Á¤Àǰ¡ Àбâ Àü¿ëÀÎ °°Àº Ä÷³¿¡ ´ëÇÑ Ç¥Çö½ÄÀ» ¿Ã¹Ù·Î ÁöÁ¤ÇÒ ¼ö ÀÖ°Ô ÇÕ´Ï´Ù.
VEmployee ºäÀÇ Dept Ä÷³Ã³·³ ºä¿¡ ÂüÁ¶ Ä÷³ÀÌ ÀÖÀ¸¸é, SQL ÂüÁ¶ ÇØÁ¦ Á¶ÀÛ¿¡¼ Ä÷³À» »ç¿ëÇÒ ¼ö ÀÖµµ·Ï Ä÷³¿¡ ¹üÀ§¸¦ ¿¬°ü½ÃÄÑ¾ß ÇÕ´Ï´Ù. ºäÀÇ ÂüÁ¶ Ä÷³¿¡ ¹üÀ§¸¦ ÁöÁ¤ÇÏÁö ¾Ê°í ±âº» Å×À̺íÀ̳ª ºä Ä÷³¿¡ ¹üÀ§°¡ ÁöÁ¤µÇ¸é ±âº» Ä÷³ÀÇ ¹üÀ§´Â ºäÀÇ ÂüÁ¶ Ä÷³À¸·Î Àü´ÞµË´Ï´Ù. WITH OPTIONÀýÀ» »ç¿ëÇÏ¿© ºäÀÇ ÂüÁ¶ Ä÷³¿¡ ¹üÀ§¸¦ ¸í½ÃÀûÀ¸·Î ÁöÁ¤ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÌÀü ¿¹¿¡¼ VEmployee ºäÀÇ Dept Ä÷³Àº VBusinessUnit ºä¸¦ ÀÚü ¹üÀ§·Î ¼ö½ÅÇÕ´Ï´Ù. ±âº» Å×À̺íÀ̳ª ºä Ä÷³¿¡ ¹üÀ§°¡ ¾ø°í ºä Á¤ÀÇ¿¡ ¸í½ÃÀûÀ¸·Î ÁöÁ¤µÈ ¹üÀ§°¡ ¾ø°Å³ª ALTER VIEW¹®À¸·Î ÁöÁ¤µÈ ¹üÀ§°¡ ¾øÀ¸¸é, ÂüÁ¶ Ä÷³Àº ¹üÀ§°¡ ÁöÁ¤µÇÁö ¾ÊÀº »óÅ·Π³²½À´Ï´Ù.
À¯ÇüÈ ºä¸¦ ÀÛ¼ºÇÏ°í »ç¿ëÇϱâ Àü¿¡ À¯ÇüÈ ºäÀÇ Á¶È¸ Á¦ÇѰú ¿¬°üÇÏ¿© ¸î °¡Áö ÁÖ¿ä ±ÔÄ¢ÀÌ Àû¿ëµÇ´Â SQL ÂüÁ¶¼¸¦ ÁÖÀDZí°Ô Àо½Ê½Ã¿À.
»ç¿ëÀÚ´Â DROP¹®À» »ç¿ëÇÏ¿© »ç¿ëÀÚ Á¤ÀÇ À¯Çü(UDT) ¶Ç´Â À¯Çü ¸ÊÇÎÀ» Á¦°ÅÇÒ ¼ö ÀÖ½À´Ï´Ù. À¯Çü ¸ÊÇο¡ ´ëÇØ¼´Â µ¥ÀÌÅÍ À¯Çü ¸ÊÇο¡ ´ëÇÑ ÀÛ¾÷¿¡¼ ÂüÁ¶ÇϽʽÿÀ. UDT¸¦ ´ÙÀ½°ú °°ÀÌ »ç¿ëÇÑ °æ¿ì, UDT¸¦ Á¦°ÅÇÒ ¼ö ¾ø½À´Ï´Ù.
±âº» À¯Çü ¸ÊÇÎÀ» Á¦°ÅÇÒ ¼ö ¾ø½À´Ï´Ù. ¶Ç´Ù¸¥ ¸ÊÇÎÀ» ÀÛ¼ºÇÏ¿© À̸¦ ´ëü¸¸ ÇÒ ¼ö ÀÖ½À´Ï´Ù.
µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº ÀÌ UDT¿¡ Á¾¼ÓµÇ¾î ÀÖ´Â ¸ðµç »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö(UDF)¸¦ Á¦°ÅÇÏ·Á°í ÇÕ´Ï´Ù. ºä, Æ®¸®°Å, Å×À̺í Á¡°Ë Á¦ÇÑÁ¶°Ç ¶Ç´Â ´Ù¸¥ UDF°¡ ÀÌ¿¡ Á¾¼ÓÀûÀÎ °æ¿ì¿¡ UDF°¡ Á¦°ÅµÉ ¼ö ¾ø½À´Ï´Ù. DB2°¡ Á¾¼Ó UDF¸¦ Á¦°ÅÇÏÁö ¸øÇϸé DB2´Â UDT¸¦ Á¦°ÅÇÏÁö ¾Ê½À´Ï´Ù. UDT¸¦ Á¦°ÅÇϸé À̰ÍÀ» »ç¿ëÇÏ´Â ´Ù¸¥ ÆÐŰÁö³ª ij½¬ÈµÈ µ¿Àû SQL¹®À» ¹«È¿ÈÇÕ´Ï´Ù.
UDT¿¡ ´ëÇÑ º¯È¯À» ÀÛ¼ºÇÏ°í ±× UDT¸¦ Á¦°ÅÇÒ °èȹÀ̶ó¸é, ¿¬°ü º¯È¯À» Á¦°ÅÇÏ´Â °Í¿¡ ´ëÇØ °í·ÁÇØ º¸½Ê½Ã¿À. º¯È¯À» Á¦°ÅÇÏ·Á¸é DROP TRANSFORM¹®À» ¹ßÇàÇϽʽÿÀ. DROP TRANSFORM¹®ÀÇ Àüü ±¸¹®Àº SQL ÂüÁ¶¼¿¡ ³ª¿Í ÀÖ½À´Ï´Ù. »ç¿ëÀÚ Á¤ÀÇ º¯È¯¸¸ Á¦°ÅÇÒ ¼ö ÀÖ½À´Ï´Ù. ³»ÀåµÈ º¯È¯À̳ª ¿¬°üµÈ ±×·ì Á¤ÀǸ¦ Á¦°ÅÇÒ ¼ö ¾ø½À´Ï´Ù.
ALTER VIEW¹®Àº ÂüÁ¶ À¯Çü Ä÷³ÀÌ ¹üÀ§¸¦ Ãß°¡Çϵµ·Ï º¯°æÇÏ¿© ±âÁ¸ ºä¸¦ ¼öÁ¤ÇÕ´Ï´Ù. ºä¿¡ ´ëÇÑ ´Ù¸¥ ¸ðµç º¯°æÀ» ¼öÇàÇÒ ¶§´Â ÇØ´ç ºä¸¦ Á¦°ÅÇÑ ´ÙÀ½ ´Ù½Ã ÀÛ¼ºÇØ¾ß ÇÕ´Ï´Ù.
ºä¸¦ º¯°æÇÒ ¶§´Â ¾ÆÁ÷ ¹üÀ§°¡ Á¤ÀǵÇÁö ¾ÊÀº ±âÁ¸ ÂüÁ¶ À¯Çü Ä÷³¿¡ ¹üÀ§¸¦ Ãß°¡ÇØ¾ß ÇÕ´Ï´Ù. ±×¸®°í ¼öÆÛºä·ÎºÎÅÍ Ä÷³À» °è½ÂÇÒ ¼ö ¾ø½À´Ï´Ù£®
ALTER VIEW¹®ÀÇ Ä÷³ À̸§ ÀÚ·á À¯ÇüÀº REF(À¯ÇüÈ Å×À̺í À̸§ ¶Ç´Â À¯ÇüÈ ºä À̸§ À¯Çü)¿©¾ß ÇÕ´Ï´Ù.
ALTER VIEW¹®¿¡ ´ëÇØ¼´Â SQL ÂüÁ¶¼¿¡¼ ÂüÁ¶ÇϽʽÿÀ£®
´ÙÀ½ ¿¹´Â EMP_VIEW¸¦ Á¦°ÅÇÏ´Â ¹æ¹ýÀ» º¸¿©ÁÝ´Ï´Ù.
DROP VIEW EMP_VIEW;
Á¦°ÅµÈ ºä¿¡ Á¾¼ÓµÇ¾î ÀÖ´Â ¸ðµç ºä´Â ÀÛµ¿ ºÒ´É »óŰ¡ µË´Ï´Ù. ÀÛµ¿ ºÒ´É ºä¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº °ü¸® ¾È³»¼ÀÇ "ÀÛµ¿ ºÒ´É ºä º¹±¸"ÀýÀ» ÂüÁ¶ÇϽʽÿÀ.
ÆÐŰÁö ¹× ij½¬ÈµÈ µ¿Àû ¸í·É¹®¿¡ ¹«È¿ Ç¥½Ã°¡ µÇ¾î ÀÖ´õ¶óµµ, Å×ÀÌºí ¹× »öÀΰú °°Àº ±âŸ µ¥ÀÌŸº£À̽º ¿ÀºêÁ§Æ®¿¡´Â ¿µÇâÀ» ÁÖÁö ¾Ê½À´Ï´Ù. ´õ ÀÚ¼¼ÇÑ ³»¿ëÀº °ü¸® ¾È³»¼ÀÇ "¸í·É¹® Á¾¼Ó¼º"ÀýÀ» ÂüÁ¶ÇϽʽÿÀ.
Å×ÀÌºí °èÃþÀÇ °æ¿ì, ´ÙÀ½ ¿¹¿¡¼Ã³·³ °èÃþÀÇ ·çÆ® ºä¸¦ ¸í¸íÇÏ¿© ÇÑ ¸í·É¹®¿¡¼ Àüü ºä °èÃþÀ» Á¦°ÅÇÒ ¼ö ÀÖ½À´Ï´Ù.
DROP VIEW HIERARCHY VPerson;
ºä Á¦°Å ¹× ÀÛ¼º¿¡ ´ëÇØ¼´Â SQL ÂüÁ¶¼¿¡¼ ÂüÁ¶ÇϽʽÿÀ.
Çʼö SELECT ±ÇÇÑÀÌ ÀÖÀ¸¸é, À¯ÇüȵÇÁö ¾ÊÀº Å×À̺í Á¶È¸¿Í °°Àº ¹æ¹ýÀ¸·Î À¯ÇüÈ Å×À̺íÀ» Á¶È¸ÇÒ ¼ö ÀÖ½À´Ï´Ù. Á¶È¸´Â SELECTÀÇ ¸ñÇ¥¿¡¼ ÇàÀ» ±ÔÁ¤ÇÏ¿© ¿äûµÈ Ä÷³°ú ±× ¸ðµç ¼ºêÅ×À̺íÀ» ¸®ÅÏÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, Person Å×ÀÌºí °èÃþÀÇ µ¥ÀÌÅÍ¿¡ ´ëÇÑ ´ÙÀ½ Á¶È¸´Â ¸ðµç »ç¶÷ÀÇ À̸§°ú ³ªÀ̸¦ ¸®ÅÏÇÕ´Ï´Ù. Áï, Person Å×À̺í°ú ±× ¼ºêÅ×ÀÌºí¿¡ ÀÖ´Â ¸ðµç ÇàÀÔ´Ï´Ù. Ä÷³ Áß Çϳª°¡ ±¸Á¶È À¯Çü Ä÷³ÀÎ °æ¿ì, ºñ½ÁÇÑ Á¶È¸ÀÇ ÀÛ¼º¿¡ ´ëÇØ¼´Â ±¸Á¶È À¯Çü °ªÀÇ °Ë»ö ¹× ¼öÁ¤À» ÂüÁ¶ÇϽʽÿÀ.
SELECT Name, Age FROM Person;
Á¶È¸ °á°ú´Â ´ÙÀ½°ú °°½À´Ï´Ù.
NAME AGE -------------------- ----------- Andrew 29 Bob 30 Cathy 25 Dennis 26 Eva 31 Franky 28 Gordon 19 Helen 20 Iris 35 Christina 10 Ken 55 Leo 35 Brian 7 Susan 39
ÂüÁ¶ÀÇ ¹üÀ§¸¦ ÁöÁ¤ÇÒ ¶§¸¶´Ù, ÂüÁ¶ ÇØÁ¦ Á¶ÀÛÀ» »ç¿ëÇÏ¿© ¿ÜºÎ Á¶ÀÎÀ̳ª »ó°ü ºÎ¼Ó Á¶È¸°¡ ÇÊ¿äÇÒ ¼öµµ ÀÖ´Â Á¶È¸¸¦ ¹ßÇàÇÕ´Ï´Ù. Employee Å×À̺íÀÇ Dept ¼Ó¼º°ú BusinessUnit Å×À̺í·Î ¹üÀ§°¡ ÁöÁ¤µÈ EmployeeÀÇ ¼ºêÅ×À̺íÀ» °í·ÁÇϽʽÿÀ. ´ÙÀ½ ¿¹´Â ÀûÀýÇÑ °æ¿ì µ¥ÀÌÅͺ£À̽º¿¡ ÀÖ´Â ¸ðµç Á÷¿øÀÇ À̸§, ±Þ¿© ¹× ºÎ¼ À̸§ ¶Ç´Â ³Î(NULL) °ªÀ» ¸®ÅÏÇÕ´Ï´Ù. Áï, Á¶È¸´Â Employee Å×ÀÌºí¿¡ ÀÖ´Â ¸ðµç Çà°ú Employee ¼ºêÅ×À̺íÀÇ °ªÀ» ¸®ÅÏÇÕ´Ï´Ù. »ó°ü ºÎ¼Ó Á¶È¸³ª ¿ÜºÎ Á¶ÀÎÀ» »ç¿ëÇÏ¿© ºñ½ÁÇÑ Á¶È¸¸¦ ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù. ±×·¯³ª, ÂüÁ¶ ÇØÁ¦ ¿¬»êÀÚ(->)¸¦ »ç¿ëÇÏ¿© Employee Å×À̺í°ú ±× ¼ºêÅ×ÀÌºí¿¡ ÀÖ´Â ÂüÁ¶ Ä÷³¿¡¼ BusinessUnit Å×À̺í·Î °æ·Î¸¦ µû¶ó°¡°í, BusinessUnit Å×À̺íÀÇ Name Ä÷³¿¡¼ °á°ú¸¦ ¸®ÅÏÇÏ´Â °ÍÀÌ ´õ ½±½À´Ï´Ù.
ÂüÁ¶ ÇØÁ¦ Á¶ÀÛÀÇ °£´ÜÇÑ Çü½ÄÀº ´ÙÀ½°ú °°½À´Ï´Ù.
scoped-reference-expression -> column-in-target-typed-table
´ÙÀ½ Á¶È¸´Â ÂüÁ¶ ÇØÁ¦ ¿¬»êÀÚ¸¦ »ç¿ëÇÏ¿© BusinessUnit Å×ÀÌºí¿¡¼ Name Ä÷³À» È®º¸ÇÕ´Ï´Ù.
SELECT Name, Salary, Dept->Name FROM Employee
Á¶È¸ °á°ú´Â ´ÙÀ½°ú °°½À´Ï´Ù.
NAME SALARY NAME -------------------- ----------- -------------------- Dennis 30000 Toy Eva 45000 Shoe Franky 39000 Shoe Iris 55000 Toy Christina 85000 Toy Ken 105000 Shoe Leo 92000 Shoe Brian 112000 Toy Susan 37000.48 ---
ÀÚü ÂüÁ¶ÀÇ ÂüÁ¶µµ ÇØÁ¦ÇÒ ¼ö°¡ ÀÖ½À´Ï´Ù. ±×¸² 13¿¡ Á¤ÀǵǾî ÀÖ´Â ºÎǰ Å×À̺íÀ» °í·ÁÇϽʽÿÀ. ´ÙÀ½ Á¶È¸´Â ºÎǰ °ø±ÞÀÚÀÇ À§Ä¡¿Í ÇÔ²² ³¯°³¿¡ Á÷Á¢ »ç¿ëµÈ ºÎǰÀ» ³ª¿ÇÕ´Ï´Ù.
SELECT P.Descript, P.Supplied_by ->Location FROM Parts P WHERE P.Used_in -> Descript='Wing';
ÂüÁ¶¸¦ ÇØÁ¦ÇÏ¿© DEREF ³»Àå ÇÔ¼ö¸¦ ÅëÇØ Àüü ±¸Á¶È ¿ÀºêÁ§Æ®¸¦ ÇϳªÀÇ °ªÀ¸·Î È®º¸ÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½Àº DEREFÀÇ °£´ÜÇÑ Çü½ÄÀÔ´Ï´Ù.
DEREF (scoped-reference-expression)
DEREF´Â º¸Åë TYPE_NAME°ú °°Àº ´Ù¸¥ ³»Àå ÇÔ¼öÀÇ ÄÄÅØ½ºÆ®¿¡¼ »ç¿ëµÇ°Å³ª ÀÀ¿ëÇÁ·Î±×·¥ ¿ÜºÎ·Î ¹ÙÀεùÇϱâ À§ÇØ Àüü ±¸Á¶È ¿ÀºêÁ§Æ®¸¦ È®º¸ÇÕ´Ï´Ù.
DEREF ÇÔ¼ö´Â °¡²û TYPE_NAME, TYPE_ID ¶Ç´Â TYPE_SCHEMA ³»Àå ÇÔ¼öÀÇ ÀϺηΠȣÃâµË´Ï´Ù. ÀÌ·± ÇÔ¼öÀÇ ¸ñÀûÀº Ç¥Çö½ÄÀÇ µ¿Àû À¯ÇüÀÇ À̸§, ³»ºÎ ID ¹× ½ºÅ°¸¶ À̸§À» ¸®ÅÏÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, ´ÙÀ½ ¿¹¿¡¼´Â ResponsibleÀ̶ó´Â ¼Ó¼ºÀÇ Project À¯ÇüÈ Å×À̺íÀ» ÀÛ¼ºÇÕ´Ï´Ù.
CREATE TYPE Project_t AS (Projid INT, Responsible REF(Employee_t)) MODE DB2SQL; CREATE TABLE Project OF Project_t (REF IS Oid USER GENERATED, Responsible WITH OPTIONS SCOPE Employee);
Responsible ¼Ó¼ºÀº Employee Å×À̺íÀÇ ÂüÁ¶·Î Á¤ÀǵǹǷÎ, Á÷¿ø»Ó¸¸ ¾Æ´Ï¶ó °ü¸®ÀÚ¿Í ¾ÆÅ°ÅØÆ®ÀÇ ÀνºÅϽº¸¦ ÂüÁ¶ÇÒ ¼ö ÀÖ½À´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ ¸ðµç ÇàÀÇ µ¿Àû À¯ÇüÀÇ À̸§À» ¾Ë¾Æ¾ß µÇ´Â °æ¿ì, ´ÙÀ½°ú °°Àº Á¶È¸¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT Projid, Responsible->Name, TYPE_NAME(DEREF(Responsible)) FROM PROJECT;
ÀÌÀü ¿¹¿¡¼´Â ÂüÁ¶ ÇØÁ¦ ¿¬»êÀÚ¸¦ »ç¿ëÇÏ¿© Employee Å×ÀÌºí¿¡¼ NameÀÇ °ªÀ» ¸®ÅÏÇϰí DEREF ÇÔ¼ö¸¦ È£ÃâÇÏ¿© Employee_tÀÇ ÀνºÅϽº¿¡ ´ëÇÑ µ¿Àû À¯ÇüÀ» ¸®ÅÏÇÕ´Ï´Ù.
ÀÌ Àý¿¡¼ ¼³¸íµÈ ³»Àå ÇÔ¼ö¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº SQL ÂüÁ¶¼¸¦ ÂüÁ¶ÇϽʽÿÀ.
±ÇÇÑ ºÎ¿© ¿ä±¸»çÇ×: DEREF ÇÔ¼ö¸¦ »ç¿ëÇÏ·Á¸é, Å×ÀÌºí °èÃþ¿¡¼ ÂüÁ¶µÈ ºÎºÐ¿¡ ÀÖ´Â ¸ðµç Å×À̺í°ú ¼ºêÅ×ÀÌºí¿¡ ´ëÇØ SELECT ±ÇÇÑÀÌ ÀÖ¾î¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, À§ÀÇ Á¶È¸¿¡¼ Employee, Manager ¹× Architect À¯ÇüÈ Å×ÀÌºí¿¡ ´ëÇÑ SELECT Ư±ÇÀÌ ÀÖ¾î¾ß ÇÕ´Ï´Ù.
Á¶È¸¿¡¼ ±× ¼ºêÅ×À̺íÀ» Á¦¿ÜÇÑ Æ¯Á¤ À¯ÇüÀÇ ¿ÀºêÁ§Æ®¸¸ ¸®ÅÏÇÏ·Á¸é ONLY Ű¿öµå¸¦ »ç¿ëÇϽʽÿÀ. ¿¹¸¦ µé¾î, ´ÙÀ½ Á¶È¸´Â °ÇÃà°¡ ¶Ç´Â °ü¸®ÀÚ°¡ ¾Æ´Ñ Á÷¿øÀÇ À̸§¸¸ ¸®ÅÏÇÕ´Ï´Ù.
SELECT Name FROM ONLY(Employee);
ÀÌÀü Á¶È¸´Â ´ÙÀ½ °á°ú¸¦ ¸®ÅÏÇÕ´Ï´Ù.
NAME -------------------- Dennis Eva Franky Susan
µ¥ÀÌÅÍÀÇ º¸¾ÈÀ» º¸È£Çϱâ À§ÇØ ONLY¸¦ »ç¿ëÇÒ ¶§ EmployeeÀÇ ¸ðµç ¼ºêÅ×ÀÌºí¿¡¼ SELECT Ư±ÇÀÌ ÇÊ¿äÇÕ´Ï´Ù.
ONLYÀýÀ» »ç¿ëÇÏ¿© UPDATE ¶Ç´Â DELETE¹®ÀÇ Á¶ÀÛÀ» ¸í¸íµÈ Å×À̺í·Î Á¦ÇÑÇÒ ¼öµµ ÀÖ½À´Ï´Ù. Áï, ONLYÀýÀº Á¶ÀÛÀÌ ±× ¸í¸íµÈ Å×À̺íÀÇ ¸ðµç ¼ºêÅ×ÀÌºí¿¡¼ ¹ß»ýÇÏÁö ¾Ê°Ô ÇÕ´Ï´Ù.
Á»´õ ÀϹÝÀûÀÎ ¹æ¹ýÀ¸·Î SQL¹®¿¡ ÀÇÇØ ¸®Åϵǰųª ¿µÇâÀ» ¹Þ´Â ÇàÀ» Á¦ÇÑÇÏ·Á¸é Type ¼ú¾î¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. Type ¼ú¾î¸¦ ÅëÇØ Ç¥Çö½ÄÀÇ µ¿Àû À¯ÇüÀ» Çϳª ÀÌ»óÀÇ ¸í¸íµÈ À¯Çü°ú ºñ±³ÇÒ ¼ö ÀÖ½À´Ï´Ù. Type ¼ú¾îÀÇ °£´ÜÇÑ ¹öÀüÀº ´ÙÀ½°ú °°½À´Ï´Ù.
<expression> IS OF (<type_name>[, ...])
¿©±â¼ expressionÀº ±¸Á¶È À¯ÇüÀÇ ÀνºÅϽº¸¦ ¸®ÅÏÇÏ´Â SQL Ç¥Çö½ÄÀ» ³ªÅ¸³»°í type_nameÀº ÀνºÅϽº¸¦ ºñ±³ÇÏ´Â Çϳª ÀÌ»óÀÇ ±¸Á¶È À¯ÇüÀ» ³ªÅ¸³À´Ï´Ù.
¿¹¸¦ µé¾î, ´ÙÀ½ Á¶È¸´Â 35»ì ÀÌ»óÀÌ°í °ü¸®ÀÚ ¶Ç´Â °ËÃà°¡ÀÎ »ç¶÷À» ¸®ÅÏÇÕ´Ï´Ù.
SELECT Name FROM Employee E WHERE E.Age > 35 AND DEREF(E.Oid) IS OF (Manager_t, Architect_t);
ÀÌÀü Á¶È¸´Â ´ÙÀ½ °á°ú¸¦ ¸®ÅÏÇÕ´Ï´Ù.
NAME -------------------- Ken
DB2°¡ ±¸Á¶È À¯Çü Çà °ªÀ» ¸®ÅÏÇϸé, ÀÀ¿ëÇÁ·Î±×·¥Àº ƯÁ¤ ÀνºÅϽº¿¡ Æ÷ÇԵǾî Àְųª Æ÷Ç﵃ ¼ö ÀÖ´Â ¼Ó¼ºÀ» ¸ð¸£°í À־ µË´Ï´Ù. ¿¹¸¦ µé¾î, »ç¶÷À» ¸®ÅÏÇϸé, ±× »ç¶÷Àº »ç¶÷ÀÇ ¼Ó¼º¸¸ °¡Áö°í Àְųª Á÷¿ø, °ü¸®ÀÚ ¶Ç´Â »ç¶÷ÀÇ ±âŸ ºÎ¼Ó À¯ÇüÀÇ ¼Ó¼ºÀ» °¡Áö°í ÀÖÀ» ¼öµµ ÀÖ½À´Ï´Ù. ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ÇϳªÀÇ SQL Á¶È¸¿¡¼ °¡´ÉÇÑ ¸ðµç ¼Ó¼ºÀÇ °ªÀ» È®º¸ÇØ¾ß ÇÏ´Â °æ¿ì, Å×À̺í ÂüÁ¶¿¡¼ OUTER Ű¿öµå¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
OUTER (table-name)¿Í OUTER(view-name)Àº Å×À̺íÀÇ Ä÷³À̳ª ºä·Î ±¸¼ºµÈ °¡»ó Å×À̺í°ú ±× ¼ºêÅ×ÀÌºí¿¡¼ ¼Ò°³µÈ Ãß°¡ Ä÷³(ÀÖ´Â °æ¿ì)À» ¸®ÅÏÇÕ´Ï´Ù. Ãß°¡ Ä÷³Àº Å×ÀÌºí ¿À¸¥ÂÊ¿¡ Ãß°¡µÇ°í ±íÀÌÀÇ ¼ø¼·Î ¼ºêÅ×ÀÌºí °èÃþÀ» µû¶ó°©´Ï´Ù. °øµ¿ »óÀ§°¡ ÀÖ´Â ¼ºêÅ×À̺íÀº °¢°¢ÀÇ À¯ÇüÀÌ ÀÛ¼ºµÈ ¼ø¼´ë·Î Åë°úµË´Ï´Ù. Çà¿¡´Â table-nameÀÇ ¸ðµç Çà°ú table-nameÀÇ ¼ºêÅ×À̺íÀÇ ¸ðµç Ãß°¡ ÇàÀÌ µé¾î ÀÖ½À´Ï´Ù. ³Î °ªÀº ÇàÀÇ ¼ºêÅ×ÀÌºí¿¡ ¾ø´Â Ä÷³¿¡ ´ëÇØ ¸®Åϵ˴ϴÙ.
¿¹¸¦ µé¾î, Ç¥ÁØ ÀÌ»óÀ» ¼ºÃëÇÏ·Á´Â »ç¶÷µé¿¡ ´ëÇÑ Á¤º¸¸¦ º¸°í ½ÍÀ» ¶§ OUTER¸¦ »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù. ´ÙÀ½ Á¶È¸´Â ³ôÀº ±Þ¿© Salary ¶Ç´Â ³ôÀº ¼ºÀû Æò±Õ GPA°¡ ÀÌ´Â Person Å×ÀÌºí °èÃþ¿¡¼ Á¤º¸¸¦ ¸®ÅÏÇÕ´Ï´Ù.
SELECT * FROM OUTER(Person) P WHERE P.Salary > 200000 OR P.GPA > 3.95 ;
OUTER(Person)À» »ç¿ëÇϸé Person Á¶È¸¿¡¼ °¡´ÉÇÏÁö ¾ÊÀº ºÎ¼Ó À¯Çü ¼Ó¼ºÀ» ÂüÁ¶ÇÒ ¼ö ÀÖ½À´Ï´Ù.
OUTER¸¦ »ç¿ëÇϸé ÂüÁ¶µÈ Å×À̺íÀÇ ¸ðµç Á¤º¸°¡ ±× »ç¿ë¿¡ ÀÇÇØ ³ëÃâµÇ¹Ç·Î, ±× Å×À̺íÀÇ ¸ðµç ¼ºêÅ×ÀÌºí ¶Ç´Â ºä¿¡ ´ëÇÑ SELECT Ư±ÇÀÌ ÇÊ¿äÇÕ´Ï´Ù.
ÀÀ¿ëÇÁ·Î±×·¥¿¡¼ ÀÌ·¸°Ô ¼ºÃë¿åÀÌ °»ê »ç¶÷µéÀÇ ¼Ó¼º»Ó¸¸ ¾Æ´Ï¶ó, °¢°¢¿¡ ´ëÇØ °¡Àå °íÀ¯ÇÑ À¯ÇüÀÌ ¹«¾ùÀÎÁö ¾Ë¾Æ¾ß ÇÑ´Ù°í °¡Á¤ÇØ º¸½Ê½Ã¿À. ¿ÀºêÁ§Æ®ÀÇ ¿ÀºêÁ§Æ® ½Äº°ÀÚ¸¦ TYPE_NAME ³»Àå ÇÔ¼ö·Î Àü´ÞÇϰí, À̸¦ ´ÙÀ½°ú °°ÀÌ OUTER Á¶È¸¿Í °áÇÕÇÏ¿© À̸¦ ´ÜÀÏ Á¶È¸¿¡¼ ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.
SELECT TYPE_NAME(DEREF(P.Oid)), P.* FROM OUTER(Person) P WHERE P.Salary > 200000 OR P.GPA > 3.95 ;
Person À¯ÇüÈ Å×À̺íÀÇ Address Ä÷³¿¡´Â ±¸Á¶È À¯ÇüÀÌ ÀÖÀ¸¹Ç·Î, Ãß°¡ ÇÔ¼ö¸¦ Á¤ÀÇÇϰí Ãß°¡ SQLÀ» ¹ßÇàÇÏ¿© ±× Ä÷³¿¡¼ µ¥ÀÌÅ͸¦ ¸®ÅÏÇØ¾ß ÇÕ´Ï´Ù. ±¸Á¶È À¯Çü Ä÷³¿¡¼ µ¥ÀÌÅ͸¦ ¸®ÅÏÇÏ´Â °Í¿¡ ´ëÇÑ ÀÚ¼¼ÇÑ ³»¿ëÀº ±¸Á¶È À¯Çü °ªÀÇ °Ë»ö ¹× ¼öÁ¤À» ÂüÁ¶ÇϽʽÿÀ. Ãß°¡ ´Ü°è¸¦ ¼öÇàÇÑ´Ù°í °£ÁֵǸé, ÀÌÀü Á¶È¸´Â Additional Attributes¿¡ GPA¿Í Salary°¡ µé¾î ÀÖ´Â ´ÙÀ½°ú °°Àº Ãâ·ÂÀ» ¸®ÅÏÇÕ´Ï´Ù.
1 OID NAME Additional Attributes ------------------ ------------- -------------------- ... PERSON_T a Andrew ... PERSON_T b Bob ... PERSON_T c Cathy ... EMPLOYEE_T d Dennis ... EMPLOYEE_T e Eva ... EMPLOYEE_T f Franky ... MANAGER_T i Iris ... ARCHITECT_T l Leo ... EMPLOYEE_T s Susan ...
DB2¿¡¼ °íÀ¯ ¿ÀºêÁ§Æ® ½Äº°ÀÚ¸¦ ÀÚµ¿À¸·Î »ý¼ºÇÏ·Á¸é GENERATE_UNIQUE ÇÔ¼ö¸¦ »ç¿ëÇÕ´Ï´Ù. GENERATE_UNIQUE´Â CHAR (13) FOR BIT DATA °ªÀ» ¸®ÅÏÇϹǷΠCREATE TYPE¹®ÀÇ REF USINGÀýÀº ±× À¯ÇüÀÇ °ªÀ» ÅëÇÕÇÒ ¼ö ÀÖ½À´Ï´Ù. VARCHAR (16) FOR BIT DATAÀÇ ±âº»°ªÀÌ ÀÌ ¸ñÀû¿¡ ÀûÇÕÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, BusinessUnit_t À¯ÇüÀÌ ±âº» Ç¥Çö À¯Çü°ú ÇÔ²² ÀÛ¼ºµÇ¾ú´Ù°í °¡Á¤ÇϽʽÿÀ. Áï, ´ÙÀ½°ú °°ÀÌ REF USINGÀýÀÌ ÁöÁ¤µÇÁö ¾Ê¾Ò½À´Ï´Ù.
CREATE TYPE BusinessUnit_t AS (Name VARCHAR(20), Headcount INT) MODE DB2SQL;
À¯ÇüÈ Å×À̺í Á¤ÀÇ´Â ´ÙÀ½°ú °°½À´Ï´Ù.
CREATE TABLE BusinessUnit OF BusinessUnit_t (REF IS Oid USER GENERATED);
Ç×»ó USER GENERATEDÀýÀ» Á¦°øÇØ¾ß ÇÕ´Ï´Ù.
ÇàÀ» À¯ÇüÈ Å×ÀÌºí¿¡ »ðÀÔÇÏ´Â INSERT¹®Àº ´ÙÀ½°ú °°À» ¼ö ÀÖ½À´Ï´Ù.
INSERT INTO BusinessUnit (Oid, Name, Headcount) VALUES(BusinessUnit_t(GENERATE_UNIQUE( )), 'Toy' 15);
Toy ºÎ¼¿¡ ¼ÓÇÏ´Â Á÷¿øÀ» »ðÀÔÇÏ·Á¸é, ´ÙÀ½°ú °°ÀÌ ºÎ¼Ó ¼±ÅÃÀ» ¹ßÇàÇÏ¿© BusinessUnit Å×ÀÌºí¿¡¼ ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³ÀÇ °ªÀ» °Ë»öÇÏ°í °ªÀ» BusinessUnit_t À¯ÇüÀ¸·Î À¯Çüº¯È¯Çϸç, ±× °ªÀ» Dept Ä÷³¿¡ »ðÀÔÇÏ´Â ¸í·É¹®À» »ç¿ëÇÒ ¼ö ÀÖ½À´Ï´Ù.
INSERT INTO Employee (Oid, Name, Age, SerialNum, Salary, Dept) VALUES(Employee_t('d'), 'Dennis', 26, 105, 30000, BusinessUnit_t(SELECT Oid FROM BusinessUnit WHERE Name='Toy'));
¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³À» ¿ÜºÎ ŰÀÇ »óÀ§ Å×À̺íÀÇ Å° Ä÷³À¸·Î »ç¿ëÇÏ·Á¸é, ¸ÕÀú À¯ÇüÈ Å×À̺íÀ» º¯°æÇÏ¿© ¸í½ÃÀû °íÀ¯ ¶Ç´Â ±âº» Ű Á¦ÇÑ»çÇ×À» ¿ÀºêÁ§Æ® ½Äº°ÀÚ Ä÷³¿¡ Ãß°¡ÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, ±×¸² 14¿¡¼¿Í °°ÀÌ °¢ Á÷¿øÀÇ °ü¸®ÀÚ°¡ Ç×»ó Á÷¿ø Å×ÀÌºí¿¡ ÀÖ´Â Á÷¿øÀ¸·Î Á¸ÀçÇØ¾ß ÇÏ´Â Á÷¿ø¿¡ ´ëÇÑ ÀÚü ÂüÁ¶ °ü°è¸¦ ÀÛ¼ºÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ.
![]() |
ÀÚü ÂüÁ¶ °ü°è¸¦ ÀÛ¼ºÇÏ·Á¸é ´ÙÀ½ ´Ü°è¸¦ ¼öÇàÇϽʽÿÀ.
´Ü°è 1. | À¯Çü ÀÛ¼º
CREATE TYPE Empl_t AS (Name VARCHAR(10), Mgr REF(Empl_t)) MODE DB2SQL; |
´Ü°è 2. | À¯ÇüÈ Å×À̺í ÀÛ¼º
CREATE TABLE Empl OF Empl_t (REF IS Oid USER GENERATED); |
´Ü°è 3. | ±âº» ¶Ç´Â °íÀ¯ Á¦ÇÑÁ¶°ÇÀ» Oid Ä÷³¿¡ Ãß°¡ÇϽʽÿÀ.
ALTER TABLE Empl ADD CONSTRAINT pk1 UNIQUE(Oid); |
´Ü°è 4. | ¿ÜºÎ Ű Á¦ÇÑÁ¶°Ç Ãß°¡
ALTER TABLE Empl ADD CONSTRAINT fk1 FOREIGN KEY(Mgr) REFERENCES Empl (Oid);
|