Tài liệu Other Database Objects docx

LINK DOWNLOAD MIỄN PHÍ TÀI LIỆU "Tài liệu Other Database Objects docx": http://123doc.vn/document/1043610-tai-lieu-other-database-objects-docx.htm

12-5
Copyright © Oracle Corporation, 2001. All rights
reserved.
The CREATE SEQUENCE Statement Syntax
Define a sequence to generate sequential numbers
automatically:
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
12-6
Copyright © Oracle Corporation, 2001. All rights
reserved.
Creating a Sequence
•
Create a sequence named DEPT_DEPTID_SEQ to be
used for the primary key of the DEPARTMENTS
table.
•
Do not use the CYCLE option.
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
Sequence created.
Sequence created.
CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
Sequence created.
Sequence created.
12-7
Copyright © Oracle Corporation, 2001. All rights
reserved.
Confirming Sequences
•
Verify your sequence values in the
USER_SEQUENCES data dictionary table.
•
The LAST_NUMBER column displays the next
available sequence number if NOCACHE is
specified.
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
12-8
Copyright © Oracle Corporation, 2001. All rights
reserved.
NEXTVAL and CURRVAL Pseudocolumns
•
NEXTVAL returns the next available sequence
value. It returns a unique value every time it is
referenced, even for different users.
•
CURRVAL obtains the current sequence value.
•
NEXTVAL must be issued for that sequence before
CURRVAL contains a value.
12-10
Copyright © Oracle Corporation, 2001. All rights
reserved.
Using a Sequence
•
Insert a new department named “Support” in
location ID 2500.
•
View the current value for the DEPT_DEPTID_SEQ
sequence.
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);
1 row created.
1 row created.
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);
1 row created.
1 row created.
SELECT dept_deptid_seq.CURRVAL
FROM dual;
SELECT dept_deptid_seq.CURRVAL
FROM dual;
12-11
Copyright © Oracle Corporation, 2001. All rights
reserved.
Using a Sequence
•
Caching sequence values in memory gives faster
access to those values.
•
Gaps in sequence values can occur when:
–
A rollback occurs
–
The system crashes
–
A sequence is used in another table
•
If the sequence was created with NOCACHE, view
the next available value, by querying the
USER_SEQUENCES table.
12-12
Copyright © Oracle Corporation, 2001. All rights
reserved.
Modifying a Sequence
Change the increment value, maximum value,
minimum value, cycle option, or cache option.
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
Sequence altered.
Sequence altered.
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
Sequence altered.
Sequence altered.
12-13
Copyright © Oracle Corporation, 2001. All rights
reserved.
Guidelines for Modifying
a Sequence
•
You must be the owner or have the ALTER privilege
for the sequence.
•
Only future sequence numbers are affected.
•
The sequence must be dropped and
re-created to restart the sequence at a different
number.
•
Some validation is performed.
12-14
Copyright © Oracle Corporation, 2001. All rights
reserved.
Removing a Sequence
•
Remove a sequence from the data dictionary by
using the DROP SEQUENCE statement.
•
Once removed, the sequence can no longer be
referenced.
DROP SEQUENCE dept_deptid_seq;
Sequence dropped.
Sequence dropped.
DROP SEQUENCE dept_deptid_seq;
Sequence dropped.
Sequence dropped.
12-15
Copyright © Oracle Corporation, 2001. All rights
reserved.
What is an Index?
An index:
•
Is a schema object
•
Is used by the Oracle server to speed up the
retrieval of rows by using a pointer
•
Can reduce disk I/O by using a rapid path access
method to locate data quickly
•
Is independent of the table it indexes
•
Is used and maintained automatically by the
Oracle server