Example 1: Creating a Sequence

    SQL> -- This example creates a new sequence using the default
    SQL> -- values for NOMINVALUE, NOMAXVALUE, INCREMENT BY 1, NOCYCLE,
    SQL> -- and CACHE 20. The START WITH value is set to 147.
    SQL> -- Allyn Stuart will be assigned an EMPLOYEE_ID value of 147.
    SQL> -- Nick Jones will be assigned an EMPLOYEE_ID of 148.
    SQL> --
    SQL> CREATE SEQUENCE EMPID START WITH 00147;
    SQL> -- Use NEXTVAL to fetch a sequence number for the primary key column.
    SQL> INSERT INTO EMPLOYEES
    cont> (EMPLOYEE_ID, LAST_NAME, FIRST_NAME)
    cont> VALUES (EMPID.NEXTVAL, 'STUART', 'ALLYN')
    cont> RETURNING EMPLOYEE_ID;
     EMPLOYEE_ID
     147
    1 row inserted
    SQL> -- Use CURRVAL to reuse the EMPLOYEE_ID value for the foreign key columns
    SQL> -- in the associated tables.
    SQL> INSERT INTO SALARY_HISTORY
    cont> (EMPLOYEE_ID, SALARY_AMOUNT,SALARY_START, SALARY_END)
    cont> VALUES (EMPID.CURRVAL, 35000, '6-FEB-1998', NULL)
    cont> RETURNING EMPLOYEE_ID;
     EMPLOYEE_ID
     147
    1 row inserted
    SQL> INSERT INTO JOB_HISTORY
    cont> (EMPLOYEE_ID, DEPARTMENT_CODE, JOB_START, JOB_END)
    cont> VALUES (EMPID.CURRVAL, 'ENGR', '6-FEB-1998', NULL)
    cont> RETURNING EMPLOYEE_ID;
     EMPLOYEE_ID
     147
    1 row inserted
    SQL> INSERT INTO EMPLOYEES
    cont> (EMPLOYEE_ID, LAST_NAME, FIRST_NAME)
    cont> VALUES (EMPID.NEXTVAL, 'JONES ', 'NICK ')
    cont> RETURNING EMPLOYEE_ID;
     EMPLOYEE_ID
     148
    1 row inserted