|
|
[ÀϹÝ] ½ÃÄö½º »ç¿ë¹æ¹ý ( SEQUENCE ) |
|
È£¼® ´Ô²²¼ ¾´ ±ÛÀÔ´Ï´Ù - 121.¢½.39.7 |
ÀÐÀ½:2991 |
|
|
½ÃÄö½º¶õ?
¢Â À¯ÀÏ(UNIQUE)ÇÑ °ªÀ» »ý¼ºÇØÁÖ´Â ¿À¶óŬ °´Ã¼ÀÔ´Ï´Ù.
¢Â ½ÃÄö½º¸¦ »ý¼ºÇÏ¸é ±âº»Å°¿Í °°ÀÌ ¼øÂ÷ÀûÀ¸·Î Áõ°¡ÇÏ´Â Ä÷³À» ÀÚµ¿ÀûÀ¸·Î »ý¼ºÇÒ¼ö ÀÖ½À´Ï´Ù.
¢Â º¸Åë primary key °ªÀ» »ý¼ºÇϱâ À§ÇØ »ç¿ëÇÕ´Ï´Ù.
¢Â ¸Þ¸ð¸®¿¡ CacheµÇ¾úÀ» ¶§ Sequence °ªÀÇ ¾×¼¼½º È¿À²ÀÌ Áõ°¡ ÇÕ´Ï´Ù.
¢Â Sequence´Â Å×À̺í°ú´Â µ¶¸³ÀûÀ¸·Î ÀúÀåµÇ°í »ý¼ºµË´Ï´Ù. µû¶ó¼ ÇϳªÀÇ sequence¸¦
¿©·¯ Å×ÀÌºí¿¡¼ ¾µ ¼ö ÀÖ½À´Ï´Ù.
CREATE SEQUENCE [SEQUENCE_NAME]
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}]
- INCREMENT BY nÀº sequence ¹øÈ£°£ÀÇ °£°ÝÀ» Á¤¼ö n¸¸Å¾¿ Áõ°¡.
START WITH nÀº ù¹ø° sequence ¹øÈ£
MAXVALUE nÀº sequenceÀÇ ÃÖ´ë°ª.
NOMAXVALUE´Â sequenceÀÇ ÃÖ´ë°ªÀº 1027
MINVALUE nÀº sequenceÀÇ ÃÖ¼Ò°ª
NOMINVALUE´Â ÃÖ¼Ò°ª 1.
cycle Àº ÃÖ´ë°ªÀ» ³ÑÀ¸¸é ´Ù½Ã óÀ½¼ö·Î µ¹¾Æ¿È
´ÙÀ½ ¿¹Á¦¸¦ Âü°íÇϼ¼¿ä.
create sequence SEQ_TEST
increment by 1
start with 1
maxvalue 99999999
nocache nocycle;
sql > insert into table_name (auto_inc_field) values(SEQ_TEST.NEXTVAL);
»ç¿ë±ÔÄ¢
¢Â NEXTVAL, CURRVALÀ» »ç¿ëÇÒ ¼ö ÀÖ´Â °æ¿ì
- subquery°¡ ¾Æ´Ñ select¹®
- insert¹®ÀÇ selectÀý
- insert¹®ÀÇ valueÀý
- update¹®ÀÇ setÀý
¢Â NEXTVAL, CURRVALÀ» »ç¿ëÇÒ ¼ö ¾ø´Â °æ¿ì
- viewÀÇ selectÀý
- distinct Å°¿öµå°¡ ÀÖ´Â select¹®
- group by, having, order byÀýÀÌ ÀÖ´Â select¹®
- select, delete, updateÀÇ subquery
- create table, alter table ¸í·ÉÀÇ default°ª
CURRVAL : ÇöÀç °ªÀ» ¹Ýȯ ÇÕ´Ï´Ù. .
NEXTVAL : ÇöÀç ½ÃÄö½º°ªÀÇ ´ÙÀ½ °ªÀ» ¹Ýȯ ÇÕ´Ï´Ù.
½ÃÄö½ºÀÇ ¼öÁ¤ ¹× »èÁ¦
START WITH´Â ¼öÁ¤ÇÒ¼ö ¾ø½À´Ï´Ù.
START WITH ÀýÀÌ ¾ø´Ù´Â Á¡À» »©°í´Â CREATE SEQUENCE¿Í °°½À´Ï´Ù
SQL>ALTER SEQUENCE emp_seq
INCREMENT BY 2
CYCLE;
2¾¿ Áõ°¡ÇÏ°í, ÃÖ´ë°ªÀ» ³ÑÀ¸¸é ´Ù½Ã óÀ½ºÎÅÍ ¼øȯÇϵµ·Ï ¼öÁ¤ÇÏ¿´½À´Ï´Ù.
DROP ¹®À¸·Î ÇÊ¿äÇÏÁö ¾ÊÀº ½ÃÄö½º´Â »èÁ¦ ÇÒ¼ö ÀÖ½À´Ï´Ù
SQL>DROP SEQUENCE EMP_SEQ;
|
|
|
|
|
|