lishman levelup
«previous  next»


SQL Basics
Joins
Constraints



Oracle Data Definition Language


To create, change and destroy tables in the database we use the Data Definition Language (DDL).

These are the three main commands:
  • CREATE TABLE
  • ALTER TABLE
  • DROP TABLE

Create Table

To create the COUNTRY table used in the previous examples, we execute this command:
CREATE TABLE country (
  ctry_id         NUMBER,
  ctry_name       VARCHAR2(50),
  ctry_area       NUMBER,
  ctry_pop        NUMBER,
  pop_upd_on      DATE,
  currency        VARCHAR2(50)
);
Each column must include a data type. The most common data types are:
  • VARCHAR2(n) for variable length character strings up to 4000 bytes.
  • NUMBER for positive and negative, integer and decimal numbers.
  • DATE for date and time with second granularity.
  • TIMESTAMP for date and time with time zone and fractions of a second granularity (to 9 decimal places).
  • CLOB and BLOB to store character large objects and binary large objects.
Depending on the data type used, Oracle will only allow appropriate values to be entered into the column. For example, DATE columns can only hold valid dates, NUMBER columns can only contain valid numbers and VARCHAR2 columns can contain any characters but only up to the size specified in brackets.

Describe Table

To see the definition of a table use the DESC[RIBE] command.
 
DESC country;
 
Which produces this output:
Name            Null     Type
--------------- -------- ------------------
CTRY_ID                  NUMBER
CTRY_NAME                VARCHAR2(50)
CTRY_AREA                NUMBER
CTRY_POP                 NUMBER
POP_UPD_ON               DATE
CURRENCY                 VARCHAR2(50)

6 rows selected

Alter Table

We can change the structure of a table using the ALTER TABLE statement. Here are a few examples:
-- Remove a column
ALTER TABLE country DROP COLUMN currency;

-- Add a single column
ALTER TABLE country ADD crncy VARCHAR(50);

-- Add multiple columns
ALTER TABLE country ADD (
  calling_code NUMBER,
  gdp NUMBER
);

-- Rename a column
ALTER TABLE country RENAME COLUMN crncy TO currency;

-- Rename a table
ALTER TABLE country RENAME TO ctry;

Drop Table

Finally, we need a way to remove unwanted tables from the database. For this we use the DROP TABLE command.
 
DROP TABLE country;
 

DDL and Transactions

One important point to remember is that Oracle will do an implicit COMMIT before and after every DDL statement. In other words DDL statements do NOT execute in a transactional context.

For example, let's go back to our COUNTRY table.
SELECT *
FROM country;
CTRY_ID CTRY_NAME CTRY_AREA CTRY_POP POP_UPD_ON CURRENCY
14 Germany 137882 82046000 30-nov-08 Euro
48 Ghana 92100 23837000 null Cedi
53 Australia 2969907 21884000 4-sep-09 Australian Dollar
73 Greece 50949 11257285 1-Jan-09 Euro
122 Georgia 26900 4382100 1-Jan-09 Lari
123 New Zealand 104428 4320300 4-sep-09 New Zealand Dollar
147 Gambia 4361 1705000 null Dalasi
149 Gabon 103347 1475000 null CFA franc

All the data in the table is accidentally deleted.
DELETE FROM country;

CREATE TABLE t1 (c1 NUMBER);

ROLLBACK;

SELECT *
FROM country;

-- 0 rows selected

We rolback in an attempt to salvage the data, but a DDL statement (CREATE TABLE) has been issued in the mean time and an implicit COMMIT statement has already been executed.
»