🔢 Oracle SQL: Create, Alter & Drop Sequence

🔢 Oracle SQL SEQUENCE: CREATE, ALTER, and DROP Tutorial

#OracleSQL #Sequence #CreateSequence #AlterSequence #DropSequence #AutoID #SQLTutorial #OraclePractice

In Oracle SQL, a SEQUENCE is a database object that generates unique numeric values, often used to auto-populate primary key columns. Below is a complete guide with syntax, examples, and explanation.

🛠️ 1. CREATE SEQUENCE

Syntax:

CREATE SEQUENCE sequence_name
START WITH start_value
INCREMENT BY step
MAXVALUE max_value
MINVALUE min_value
CYCLE | NOCYCLE
CACHE n;

Example:

CREATE SEQUENCE emp_seq
START WITH 1001
INCREMENT BY 1
MAXVALUE 9999
NOCACHE
NOCYCLE;

This creates a sequence named emp_seq starting from 1001, incrementing by 1 for each next value.

Use it in INSERT:

INSERT INTO EMPLOYEE (EMP_ID, EMP_NAME)
VALUES (emp_seq.NEXTVAL, 'Taslima Akter');

---

🔧 2. ALTER SEQUENCE

Syntax:

ALTER SEQUENCE sequence_name
INCREMENT BY new_step
MAXVALUE new_max
MINVALUE new_min
CYCLE | NOCYCLE
CACHE new_cache;

Example:

ALTER SEQUENCE emp_seq
INCREMENT BY 5
MAXVALUE 50000
CACHE 20;

This modifies the sequence to increment by 5 and cache 20 values in memory.

---

🗑️ 3. DROP SEQUENCE

Syntax:

DROP SEQUENCE sequence_name;

Example:

DROP SEQUENCE emp_seq;

This removes the sequence from the database. Make sure no application depends on it before dropping.

---

🔁 Sequences are great for generating auto-increment IDs in Oracle. Practice creating and using them for consistent, scalable data entry!