RESET SEQUENCE

I'm using the sequence to generate an identifier in the format YYYY<sequence value> and the sequence value has to be reset to 0 every year.

   DROP SEQUENCE MY_SEQ;
    CREATE SEQUENCE MY_SEQ START WITH 1 INCREMENT BY 1 MINVALUE 0;

CREATE OR REPLACE PROCEDURE my_seq_reset AS
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE my_seq';
    EXECUTE IMMEDIATE
      'CREATE SEQUENCE my_seq' ||
      '  MINVALUE 1 ' ||
      '  MAXVALUE 999999 ' ||
      '  START WITH 1 ' ||
      '  INCREMENT BY 1 ' ||
      '  NOCACHE';
END;

http://stackoverflow.com/questions/942844/how-do-i-automatically-reset-a-sequences-value-to-0-every-year-in-oracle-10g

http://stackoverflow.com/questions/5817545/how-to-change-the-oracle-sequence-using-loop

在java會有TRANSECTION的問題，最後要加PRAGMA AUTONOMOUS_TRANSACTION;

最後的寫法:

JUC6306


PROCEDURE RESETSEQ   -- 每次進來重新RESET SEQUENCE
IS
PRAGMA AUTONOMOUS_TRANSACTION;


    S VARCHAR2(500);
BEGIN    
    BEGIN
        EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ';
    EXCEPTION
      WHEN OTHERS THEN NULL;
    END;


     EXECUTE IMMEDIATE
      'CREATE SEQUENCE SEQ START WITH 1'
         ||' INCREMENT BY 1 NOCACHE';


END RESETSEQ;