DDL

Data Definition Language (DDL) statements are used to define, alter, and drop schema objects.

By using DDL statements, you can change the properties of objects without rewriting the application that accesses the objects. For example, you can add a column to a table accessed by the human resource management application without rewriting the application. You can also use DDL statements to change the structures of objects when database users perform work in the database.

To be specific, DDL statements allow you to perform the following operations:

• Create, alter, and drop schema objects and other database object structures, including the database itself and database users. Most DDL statements start with the keywords CREATE, ALTER, or DROP.

• Delete all data in schema objects without deleting the structure of these objects (using the TRUNCATE command).

• Enable and disable auditing options (keywords AUDIT and NOAUDIT).

• Add comments to database objects.

Before executing a DDL statement, the database performs an implicit commit. After the DDL statement is executed, a commit or rollback is performed immediately.

The following example uses a DDL statement to create the customer table, DML statements to insert two rows into the table, a DDL statement to alter the table structure, DCL statements to grant and revoke the read privilege on the table to users, and a DDL statement to drop the table.

CREATE TABLE customer(cust_id INT PRIMARY KEY,common_name VARCHAR(15));

# DML statements
INSERT INTO customer VALUES (1,'Tom');
INSERT INTO customer VALUES (2,'Mary');

# DDL statement
ALTER TABLE customer ADD ( cust_name VARCHAR(40) );

# DCL statements
GRANT SELECT ON customer to User2;
REVOKE SELECT ON customer from Users;

# DDL statement
DROP TABLE customer;

In the preceding example, the two INSERT statements are followed by an ALTER TABLE statement, so both INSERT statements are committed. The ALTER TABLE statement is committed only if it is successfully executed. Otherwise, it is rolled back. The INSERT statements are committed regardless of whether the ALTER TABLE statement is successfully executed.