|
|
[ÀϹÝ] ¿À¶óŬÀÇ sequence ÃʱâÈ |
|
È£¼® ´Ô²²¼ ¾´ ±ÛÀÔ´Ï´Ù - 121.¢½.39.7 |
ÀÐÀ½:2252 |
|
|
¿À¶óŬÀÇ sequence´Â alter ±¸¹®À¸·Î ½ÃÀÛ°ªÀ» º¯°æ ÇÒ ¼ö ¾ø´Ù.
±×·¡¼ sequence ÀÇ start °ªÀ» ÃʱâÈÇϰųª ´Ù¸¥ °ªÀ¸·Á ÇÏ·Á°í Çϸé
sequence¸¦ drop ÇÑ ÈÄ »õ·Î »ý¼ºÇØ Áà¾ßÇÑ´Ù.
ÀÌ ¹æ¹ýÀÌ °¡Àå±ò²ûÇϱä Çѵ¥..°¡²û drop, create ±ÇÇÑÀÌ ¾øÀ» °æ¿ì ¹®Á¦°¡ µÈ´Ù.
±×·¡¼ ¾à°£ÀÇ ²Ç¼ö¸¦ ÀÌ¿ëÇؼ drop ÇÏÁö ¾Ê°í sequence¸¦ ÃʱâÈ ÇÏ´Â ¹æ¹ýÀÌ´Ù.
CREATE OR REPLACE PROCEDURE P_RESET_SEQ( SEQ_NAME IN VARCHAR2 )
IS
L_VAL NUMBER;
BEGIN
EXECUTE IMMEDIATE 'select ' || SEQ_NAME || '.nextval from dual' INTO L_VAL;
EXECUTE IMMEDIATE 'alter sequence ' || SEQ_NAME || ' increment by -' || L_VAL || ' minvalue 0';
EXECUTE IMMEDIATE 'select ' || SEQ_NAME || '.nextval from dual' INTO L_VAL;
EXECUTE IMMEDIATE 'alter sequence ' || SEQ_NAME || ' increment by 1 minvalue 0';
END;
Ãâó : http://sqler.pe.kr/web_board/view_list.asp?id=251&part=myboard8&block=0&gotopage=2&tip=
À§¿Í °°ÀÌ Çϸé sequenceÀÇ ½ÃÀÛ°ªÀÌ 0À¸·Î µÈ´Ù.
¸¸ÀÏ ½ÃÀÛ°ªÀ» 300À¸·Î ÇÏ°í ½Í´Ù¸é À§ ±¸¹® ÀûÀº ´ÙÀ½¿¡
for i 0..300 loop
EXECUTE IMMEDIATE 'select ' || SEQ_NAME || '.nextval from dual' INTO L_VAL;
end loop;
¿ä ±¸¹® Ãß°¡ÇØÁÖ¸é µÈ´Ù.
|
|
|
|
|
|