´ÙÀ½ Àý¿¡ ¼³¸íµÈ ´ëºÎºÐÀÇ Å¸½ºÅ©¸¦ ¼öÇàÇÏ·Á¸é Å×½ºÆ® ȯ°æÀ» ¼³Á¤ÇØ¾ß ÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, ÀÀ¿ëÇÁ·Î±×·¥ÀÇ SQL Äڵ带 Å×½ºÆ®ÇÏ·Á¸é µ¥ÀÌÅͺ£À̽º°¡ ÇÊ¿äÇÕ´Ï´Ù.
Å×½ºÆ® ȯ°æ¿¡´Â ´ÙÀ½ »çÇ×ÀÌ Æ÷ÇԵǾî¾ß ÇÕ´Ï´Ù.
Å×½ºÆ® µ¥ÀÌÅͺ£À̽º¸¦ ÀÛ¼ºÇØ¾ß ÇÒ °æ¿ì CREATE DATABASE API¸¦ È£ÃâÇÏ´Â ÀÛÀº ¼¹ö ÀÀ¿ëÇÁ·Î±×·¥À» ÀÛ¼ºÇϰųª ¸í·ÉÇà 󸮱âÀ» »ç¿ëÇϽʽÿÀ. ¸í·ÉÇà 󸮱⿡ ´ëÇÑ Á¤º¸´Â Command ReferenceÀÇ ³»¿ëÀ», CREATE DATABASE API¿¡ ´ëÇØ¼´Â Administrative API ReferenceÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
ÇÊ¿äÇÑ Å×½ºÆ® Å×ÀÌºí ¹× ºä¸¦ ¼³°èÇÏ·Á¸é ¿ì¼± ÀÀ¿ëÇÁ·Î±×·¥ÀÇ µ¥ÀÌÅÍ Çʿ並 ºÐ¼®ÇϽʽÿÀ. Å×À̺íÀ» ÀÛ¼ºÇÏ·Á¸é ½ºÅ°¸¶¿¡ ´ëÇÑ CREATETAB ±ÇÇÑ ¹× CREATEIN Ư±ÇÀÌ ÇÊ¿äÇÕ´Ï´Ù. ´ëü ±ÇÇÑ¿¡ ´ëÇØ¼´Â SQL ÂüÁ¶¼¿¡ ÀÖ´Â CREATE TABLE¹®¿¡ ´ëÇÑ Á¤º¸¸¦ ÂüÁ¶ÇϽʽÿÀ.
ÀÀ¿ëÇÁ·Î±×·¥ÀÌ ¾×¼¼½ºÇÏ´Â µ¥ÀÌÅ͸¦ ³ª¿ÇÏ°í °¢ µ¥ÀÌÅÍ Ç׸ñÀÌ ¾×¼¼½ºµÇ´Â
¹æ½ÄÀ» ¼³¸íÇÕ´Ï´Ù. ¿¹¸¦ µé¾î, °³¹ß ÁßÀÎ ÀÀ¿ëÇÁ·Î±×·¥ÀÌ TEST.TEMPL,
TEST.TDEPT ¹× TEST.TPROJ Å×ÀÌºí¿¡ ¾×¼¼½ºÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. Ç¥ 1¿¡
Ç¥½ÃµÈ ´ë·Î ¾×¼¼½º À¯ÇüÀ» ±â·ÏÇÒ ¼ö ÀÖ½À´Ï´Ù.
Ç¥ 1. ÀÀ¿ëÇÁ·Î±×·¥ µ¥ÀÌÅÍ ¼³¸í
Å×ÀÌºí ¶Ç´Â ºä À̸§ | Çà »ðÀÔ | Çà »èÁ¦ | Ä÷³ À̸§ | µ¥ÀÌÅÍ À¯Çü | ¾×¼¼½º °»½Å |
---|---|---|---|---|---|
TEST.TEMPL | ¾Æ´Ï¿À | ¾Æ´Ï¿À |
EMPNO LASTNAME WORKDEPT PHONENO JOBCODE |
CHAR(6) VARCHAR(15) CHAR(3) CHAR(4) DECIMAL(3) |
¿¹ ¿¹ ¿¹ |
TEST.TDEPT | ¾Æ´Ï¿À | ¾Æ´Ï¿À |
DEPTNO MGRNO |
CHAR(3) CHAR(6) |
|
TEST.TPROJ | ¿¹ | ¿¹ |
PROJNO DEPTNO RESPEMP PRSTAFF PRSTDATE PRENDATE |
CHAR(6) CHAR(3) CHAR(6) DECIMAL(5,2) DECIMAL(6) DECIMAL(6) |
¿¹ ¿¹ ¿¹ ¿¹ ¿¹ |
ÀÀ¿ëÇÁ·Î±×·¥ µ¥ÀÌÅÍ ¾×¼¼½º¿¡ ´ëÇÑ ¼³¸íÀÌ ¿Ï·áµÇ¸é ÀÀ¿ëÇÁ·Î±×·¥ Å×½ºÆ®¿¡ ÇÊ¿äÇÑ Å×½ºÆ® Å×ÀÌºí ¹× ºä¸¦ ±¸¼ºÇϽʽÿÀ.
ÀÌ ¿¹¿¡¼´Â CREATE VIEW SQL¹®À» »ç¿ëÇÏ¿© TDEPT Å×À̺íÀÇ Å×½ºÆ® ºä¸¦ ÀÛ¼ºÇϽʽÿÀ.
µ¥ÀÌÅͺ£À̽º ½ºÅ°¸¶°¡ ÀÀ¿ëÇÁ·Î±×·¥À» µû¶ó °³¹ßÁßÀÎ °æ¿ì Å×½ºÆ® Å×À̺íÀÇ Á¤ÀÇ´Â °³¹ß °úÁ¤ Áß¿¡ ¹Ýº¹ÀûÀ¸·Î Á¤Á¦µÉ ¼ö ÀÖ½À´Ï´Ù. ÀϹÝÀûÀ¸·Î 1Â÷ ÀÀ¿ëÇÁ·Î±×·¥Àº Å×À̺íÀ» ÀÛ¼ºÇϰųª ¾×¼¼½ºÇÒ ¼ö ¾ø½À´Ï´Ù. µ¥ÀÌÅͺ£À̽º °ü¸® ÇÁ·Î±×·¥Àº Á¸ÀçÇÏÁö ¾Ê´Â Å×ÀÌºí ¹× ºä¸¦ ÂüÁ¶ÇÏ´Â ¸í·É¹®À» ¹ÙÀεåÇÒ ¼ö ¾ø½À´Ï´Ù. Å×À̺í ÀÛ¼º ¹× º¯°æ ÇÁ·Î¼¼½º ½Ã°£ÀÌ ´ú °É¸®µµ·Ï ÇÏ·Á¸é º°µµÀÇ ÀÀ¿ëÇÁ·Î±×·¥À» °³¹ßÇÏ¿© Å×À̺íÀ» ÀÛ¼ºÇØ º¸½Ê½Ã¿À. ¹°·Ð ¸í·ÉÇà 󸮱â(CLP)¸¦ »ç¿ëÇÏ¿© Å×½ºÆ® Å×À̺íÀ» ´ëȽÄÀ¸·Î ÀÛ¼ºÇÒ ¼ö ÀÖ½À´Ï´Ù.
µ¥ÀÌÅ͸¦ Å×ÀÌºí¿¡ »ðÀÔÇÏ·Á¸é ´ÙÀ½ ¹æ¹ý Áß Çϳª¸¦ »ç¿ëÇϽʽÿÀ.
INSERT¹®¿¡ ´ëÇØ¼´Â SQL ÂüÁ¶¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ. IMPORT, LOAD, RESTORE À¯Æ¿¸®Æ¼¿¡ ´ëÇØ¼´Â °ü¸® ¾È³»¼ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.
´ÙÀ½ SQL¹®Àº ÀÓÀÇ·Î »ý¼ºµÈ Å×½ºÆ® µ¥ÀÌÅÍ·Î »ç¿ëÀÚ Å×À̺íÀ» »óÁÖ½ÃŰ´Â µ¥ »ç¿ëÇÒ ¼ö ÀÖ´Â ±â¼úÀ» º¸¿©ÁÝ´Ï´Ù. ´ÙÀ½ CREATE TABLE¹®¿¡¼Ã³·³ EMP Å×ÀÌºí¿¡ ³× °³ÀÇ Ä÷³, Áï ENO(»ç¿ø ¹øÈ£), LASTNAME(¼º), HIREDATE(°í¿ëÀÏ), SALARY(»ç¿ø ±Þ¿©¾×)°¡ Æ÷ÇԵǾî ÀÖ½À´Ï´Ù.
CREATE TABLE EMP (ENO INTEGER, LASTNAME VARCHAR(30), HIREDATE DATE, SALARY INTEGER);
ÀÌ Å×À̺íÀ» 1¿¡¼ ¾î¶² ¼ýÀÚ, ¿¹¸¦ µé¸é 100±îÁöÀÇ »ç¿ø ¹øÈ£·Î ä¿ì°í ³ª¸ÓÁö Ä÷³Àº ÀÓÀÇÀÇ µ¥ÀÌÅͷΠä¿î´Ù°í °¡Á¤ÇϽʽÿÀ. ´ÙÀ½ SQL¹®À» »ç¿ëÇÏ¿© À̸¦ ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.
INSERT INTO EMP -- generate 100 records WITH DT(ENO) AS (VALUES(1) UNION ALL SELECT ENO+1 FROM DT WHERE ENO < 100 ) (1) -- Now, use the generated records in DT to create other columns -- of the employee record. SELECT ENO, (2) TRANSLATE(CHAR(INTEGER(RAND()*1000000)), (3) CASE MOD(ENO,4) WHEN 0 THEN 'aeiou' || 'bcdfg' WHEN 1 THEN 'aeiou' || 'hjklm' WHEN 2 THEN 'aeiou' || 'npqrs' ELSE 'aeiou' || 'twxyz' END, '1234567890') AS LASTNAME, CURRENT DATE - (RAND()*10957) DAYS AS HIREDATE, (4) INTEGER(10000+RAND()*200000) AS SALARY (5) FROM DT; SELECT * FROM EMP;
´ÙÀ½Àº À§ ¸í·É¹®¿¡ ´ëÇÑ ¼³¸íÀÔ´Ï´Ù.
ÀÓÀÇÀÇ Å×½ºÆ® µ¥ÀÌÅ͸¦ »ý¼ºÇÏ´Â µ¥ µµ¿òÀÌ µÇ´Â »ùÇà ÇÁ·Î±×·¥À» º¸·Á¸é sqllib/samples/c ¼ºêµð·ºÅ丮¿¡ ÀÖ´Â fillcli.sqc ¹× fillsrv.sqc »ùÇà ÇÁ·Î±×·¥À» ÂüÁ¶ÇϽʽÿÀ.
Å×½ºÆ® µ¥ÀÌÅÍ¿¡ ´ëÇØ °³¹ß ÁßÀÎ »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö(UDF)ÀÇ ÇÁ·ÎÅäŸÀÔÀ» °í·ÁÇØ º¼ ¼öµµ ÀÖ½À´Ï´Ù. UDF ÀÛ¼º ÀÌÀ¯ ¹× ¹æ¹ý¿¡ ´ëÇÑ ¼¼ºÎ»çÇ×Àº »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö(UDF) ¹× »ç¿ëÀÚ Á¤ÀÇ ÇÔ¼ö(UDFs)¿Í ¸Þ¼ÒµåÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.