Oracle SQL in 10 Minutes - Asim Abbasi
CH1: SQL Basics | CH2: SQL Operators | CH3: SQL Built-in Functions | CH4: SQL Joins | CH5: UPDATE, INSERT & DELETE Statements | CH6: CREATE, ALTER & DROP Statements | CH7: Constraints | CH8: Linking Tables vs Joining Tables | CH9: SQL Statements for Other Database Objects | CH10: SQL Statements for Database Security
Chapter 9: SQL Statements for Other Database Objects
Sequence is also a logical structure in the database. Just like tables and views, they are also called as objects. Basically Sequence generates sequence number so you would have to remember what id number should we give to newly hired employee. Just ask the sequence for next value and it provide you one. How the sequence generates numbers, it all depends on the way you create them.
Example:
CREATE SEQUENCE emp_seq
START WITH 1000
MAXVALUE 99999
INCREMENT BY 1
NOCYCLE;
This statement will create sequence emp_seq which will start with 1000
and increment by 1 i.e. each time you ask for new value from this
sequence it will give you a number 1 higher than the previous one.
Maximum value would be 99999 and if you want you can use NOMAXVALUE.
CYCLE clause we use if you want to force the sequence to start all over
again once it hits the maximum value otherwise use NOCYCLE.
Example:
SELECT emp_seq.NEXTVAL
FROM dual;
We use NEXTVAL method to retrieve the next value from the sequence so
that we don’t have to worry about what id to allot to a newly hired
employee.
INSERT INTO emp_info (id, name, email)
VALUES (emp_seq.NEXTVAL, 'Gregory', ‘gregory@oracle.com'
);
Example:
SELECT emp_seq.CURRVAL
FROM dual;
You can also find out the current value of the sequence using CURRVAL
method.
Example:
ALTER SEQUENCE emp_seq
MAXVALUE 1500;
We can also alter the structure of the already exisiting sequence in the
database using the ALTER SEQUENCE statement.
Example:
DROP SEQUENCE emp_seq;
We can also drop a sequence from the database using the DROP SEQUENCE
statement.
Chapter 9: SQL Statements for Other Database Objects
CH1: SQL Basics | CH2:
SQL Operators |
CH3: SQL Built-in Functions | CH4:
SQL Joins | CH5:
UPDATE, INSERT & DELETE Statements |
CH6: CREATE, ALTER & DROP Statements | CH7:
Constraints | CH8:
Linking Tables vs Joining Tables | CH9:
SQL
Statements for Other Database Objects | CH10:
SQL Statements for
Database Security Share with others: