DDL Statements - Oracle PL/SQL Tutorial

Oracle PL/SQL includes a set of Data Definition Language (DDL) statements that allow developers and database administrators to define and manage the structure of the database. DDL statements are essential for creating, altering, and deleting database objects like tables, indexes, and views. Here are some key Oracle PL/SQL DDL statements:

CREATE TABLE:
The CREATE TABLE is used to create a new table in the database. It defines the table’s structure, including column names, data types, and constraints.

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE
);

ALTER TABLE:
The ALTER TABLE statement is used to modify an existing table, such as adding or dropping columns, modifying data types, or adding constraints.

ALTER TABLE employees
ADD (salary NUMBER);

DROP TABLE:
The DROP TABLE statement is used to remove an existing table and all of its data from the database.

DROP TABLE employees;

CREATE INDEX:
The CREATE INDEX statement is used to create an index on one or more columns of a table. Indexes improve query performance by providing faster access to data.

CREATE INDEX emp_name_idx
ON employees (last_name, first_name);

ALTER INDEX:
The ALTER INDEX statement allows you to modify an existing index, such as renaming it or changing its storage characteristics.

ALTER INDEX emp_name_idx
RENAME TO employees_name_index;

DROP INDEX:
The DROP INDEX statement is used to remove an existing index from the database.

DROP INDEX employees_name_index;

CREATE VIEW:
The CREATE VIEW statement is used to create a virtual table based on the result of a SELECT query. Views are useful for simplifying complex queries.

CREATE VIEW emp_details AS
SELECT employee_id, first_name, last_name, hire_date, salary
FROM employees;

DROP VIEW:
The DROP VIEW statement is used to remove an existing view from the database.

DROP VIEW emp_details;

CREATE SEQUENCE:
The CREATE SEQUENCE statement is used to create a sequence, which is a database object that generates unique numbers in ascending or descending order.

CREATE SEQUENCE emp_seq
START WITH 1
INCREMENT BY 1
NOCACHE;

DROP SEQUENCE:
This statement is used to remove an existing sequence from the database.

DROP SEQUENCE emp_seq;

These DDL statements in Oracle PL/SQL provide the means to define and manage the structure of the database, ensuring efficient storage, retrieval, and manipulation of data. It’s essential to use these statements with caution, especially when altering or dropping database objects, as they can have a significant impact on the data and applications relying on that structure.

Learn PL/SQL