DDL statements

DDL statements create, 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 insert a row into a table accessed by the human resource management application without rewriting the application. You can also use DDL statements to change the structures of the objects.

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

• Create, alter, and drop database objects such as schema objects, databases, and database users. Most DDL statements start with a keyword such as 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 by using the AUDIT and NOAUDIT keywords.

• Add comments to database objects.

DDL statements cause an implicit commit before they are executed and cause a commit or rollback after the execution.

The following example uses a DDL statement to create a table named customer, two DML statements to insert two rows into the table, a DDL statement to alter the structure of the table, a DCL statement to grant the SELECT privilege on the table to a user, a DCL statement to revoke the SELECT privilege on the table from the 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.