Database Reference
In-Depth Information
PL/SQL block. This doesn't get saved to a named stored procedure but gets executed once to accomplish
our plan.
Note
We use
LAST_NUMBER
from the
user_sequences
view instead of the current value,
CURRVAL
of the
sequence. We do this because we may not have a
CURRVAL
in this session.
CURRVAL
only exists after we have
executed
NEXTVAL
on the sequence in this session. Then we can get the current value of the sequence.
See Listing 7-17. We have a
NUMBER
,
offset
into which we select the value (300 -
LAST_NUMBER
) from
our sequence. If our
LAST_NUMBER
is currently 207 for example, the value of
offset
will be 300 - 207 or 93.
We concatenate a command string,
alter_command
to
ALTER
the sequence to set the
INCREMENT BY
value to
that
offset
. We pass that
ALTER
command to
EXECUTE IMMEDIATE
. Then the next time we call
EMPLOYEES_SEQ.NEXTVAL
, we will get the value of 207 + 93 = 300. To finish this plan, we set the
INCREMENT
BY
value for the sequence back to 1.
Execute all of the commands in Listing 7-17 at this time
.
You will create our test user as
employee_id
= 300. Feel free to insert your own personal data in the
INSERT
command at the end.
Listing 7-17.
Anonymous PL/SQL Block to Reset Sequence
DECLARE
offset NUMBER;
alter_command VARCHAR2(100);
new_last_number NUMBER;
BEGIN
SELECT (300 - last_number) INTO offset
FROM user_sequences
WHERE sequence_name='EMPLOYEES_SEQ';
alter_command := '
ALTER SEQUENCE employees_seq INCREMENT BY
' ||
TO_CHAR(offset) || ' MINVALUE 0';
EXECUTE IMMEDIATE alter_command;
SELECT
employees_seq.NEXTVAL
INTO new_last_number FROM DUAL;
DBMS_OUTPUT.PUT_LINE( new_last_number );
EXECUTE IMMEDIATE 'ALTER SEQUENCE employees_seq INCREMENT BY 1';
END;
/
SELECT last_number FROM user_sequences WHERE sequence_name='EMPLOYEES_SEQ';
INSERT INTO employees
(employee_id, first_name, last_name, email, phone_number, hire_date,
job_id, salary, commission_pct, manager_id, department_id)
VALUES
(
employees_seq.NEXTVAL
, 'David', 'Coffin', 'DAVID.COFFIN',
'800.555.1212', SYSDATE, 'SA_REP', 5000, 0.20, 147, 80);
