Oracle Data Manipulation Language

Data in the tables is maintained using Data Manipulation Language (DML).

DML consists of the following statements (although SELECT is sometimes included in this list too).

INSERT
UPDATE
DELETE

Insert

The INSERT statement takes two forms:

INSERT INTO table_name (column_list) VALUES (value_list);

INSERT INTO table_name VALUES (value_list);

Let's use the first option to add Spain to the COUNTRY table (with some deliberate mistakes).

INSERT INTO country (ctry_id, ctry_name, ctry_area, ctry_pop, pop_upd_on, currency)
  VALUES(28, 'pain', 195364, 5, '1-jul-2009', 'Norwegian Krone');

COMMIT;

SELECT *
FROM country
WHERE ctry_id = 28;

CTRY_ID	CTRY_NAME	CTRY_AREA	CTRY_POP	POP_UPD_ON	CURRENCY
28	pain	195364	5	1-jul-2009	Norwegian Krone

The column_list is optional so we could have used this SQL instead:

INSERT INTO country
  VALUES (28, 'pain', 195364, 5, '1-jul-2009', 'Norwegian Krone');

COMMIT;

It is good practice, however, to name the columns in the INSERT statement. It makes the SQL easier to follow and applications more resilient to change (if the column order is changed or new columns are added to the table, for example).

Naming the columns also allows us to omit optional values during the insert.

INSERT INTO country (ctry_id, ctry_name, ctry_area)
  VALUES(28, 'pain', 195364);

COMMIT;

In this case, POP, POP_UPD_ON and CURRENCY are set to null because no values have been provided for them.

Update

The UPDATE statement looks like this:

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE conditions;

We use this UPDATE statement to fix the mistakes we made when inserting the row.

UPDATE country
SET ctry_name = 'Spain',
    ctry_pop = 46661950,
    currency = 'Euro'
WHERE ctry_id = 28;

-- 1 row updated

COMMIT;

SELECT *
FROM country
WHERE ctry_id = 28;

CTRY_ID	CTRY_NAME	CTRY_AREA	CTRY_POP	POP_UPD_ON	CURRENCY
28	Spain	195364	46661950	30-nov-08	Euro

Several rows can be changed with a single UPDATE statement depending on the selection criteria in our WHERE clause. Here we increase the population by three percent and set the updated date to the 1st June 2009, for Spain, Germany and Greece.

UPDATE country
SET ctry_pop = trunc(ctry_pop * 1.03),
    pop_upd_on = '1-jun-09'
WHERE ctry_name IN ('Spain', 'Germany', 'Greece');

-- 3 rows updated

COMMIT;

SELECT *
FROM country
WHERE ctry_name IN ('Spain', 'Germany', 'Greece');

CTRY_ID	CTRY_NAME	CTRY_AREA	CTRY_POP	POP_UPD_ON	CURRENCY
14	Germany	137847	84507380	01-JUN-09	Euro
28	Spain	195364	48061808	01-JUN-09	Euro
73	Greece	50949	11595003	01-JUN-09	Euro

Delete

To delete rows from a table, use the DELETE statement.

DELETE FROM table_name
WHERE conditions;

This example removes any small countries.

DELETE FROM country
WHERE ctry_area < 100000;

-- 4 rows deleted

COMMIT;

SELECT *
FROM country;

CTRY_ID	CTRY_NAME	CTRY_AREA	CTRY_POP	POP_UPD_ON	CURRENCY
14	Germany	137882	82046000	30-nov-08	Euro
53	Australia	2969907	21884000	4-sep-09	Australian Dollar
123	New Zealand	104428	4320300	4-sep-09	New Zealand Dollar
149	Gabon	103347	1475000	null	CFA franc
149	Spain	195364	48061808	01-JUN-09	Euro

Ghana, Greece, Georgia and Gambia have been deleted.

Transactions

You will have noticed that a COMMIT statement followed each of the DML statements above.

A COMMIT makes permanent all DML changes made during a transaction. A ROLLBACK statement, on the other hand, undoes all DML changes made during a transaction.

A transaction is a logical unit of work that comprises one or more SQL statements. A transaction begins when the first SQL statement is executed and ends with a COMMIT or ROLLBACK, at which point ALL changes are made permanent or NONE of them are.

Let's take a look at transactions in action by mistakenly updating the currency for Australia to 'New Zealand Dollar' and removing the New Zealand row altogether.

UPDATE country
SET currency = 'New Zealand Dollar'
WHERE ctry_id = 53;

DELETE FROM country
WHERE ctry_name = 'New Zealand';

-- 1 row updated

SELECT ctry_name, currency
FROM country
WHERE ctry_name IN ('Australia', 'New Zealand');

CTRY_NAME	CURRENCY
Australia	New Zealand Dollar

The user who made the updates to the database will always see these changes immediately. However, other users of the database must wait until the transaction is committed before they can see the new values.

However, at this point, we realize our mistake and rollback the transaction instead.

ROLLBACK;

SELECT ctry_name, currency
FROM country
WHERE ctry_id IN ('Australia', 'New Zealand');

CTRY_NAME	CURRENCY
Australia	Australian Dollar
New Zealand	New Zealand Dollar

Our changes have been undone and the original data is restored.

Warning!

It may seem obvious, but always make sure you specify the correct condition in the WHERE clause when updating data. If we had mistakenly typed this UPDATE command instead of the one above:

UPDATE country
SET ctry_name = 'Spain',
    ctry_pop = 46661950,
    currency = 'Euro';

-- 8 rows updated

This would be the result:

SELECT *
FROM country;

CTRY_ID	CTRY_NAME	CTRY_AREA	CTRY_POP	POP_UPD_ON	CURRENCY
14	Spain	137882	46661950	30-nov-08	Euro
48	Spain	92100	46661950	null	Cedi
53	Spain	2969907	46661950	4-sep-09	Australian Dollar
73	Spain	50949	46661950	1-Jan-09	Euro
122	Spain	26900	46661950	1-Jan-09	Lari
123	Spain	104428	46661950	4-sep-09	New Zealand Dollar
147	Spain	4361	46661950	null	Dalasi
149	Spain	103347	46661950	null	CFA franc
28	Spain	195364	46661950	1-jul-2009	Euro

All CTRY_NAME and all CTRY_POP values have been updated because we didn't include any selection criteria to identify the correct row to update.

You can imagine what would happen if we accidentally did this:

DELETE FROM country;

-- 8 rows deleted

The 8 rows deleted message provides a clue (imagine seeing 76345663 rows deleted on your production database!).

SELECT *
FROM country;

-- 0 rows selected

Fortunately, we have not made the changes permanent by issuing a COMMIT statement. We can still rollback the transaction.

ROLLBACK;

SELECT *
FROM country;

The correct data has been restored.

CTRY_ID	CTRY_NAME	CTRY_AREA	CTRY_POP	POP_UPD_ON	CURRENCY
14	Germany	137882	82046000	30-nov-08	Euro
53	Australia	2969907	21884000	4-sep-09	Australian Dollar
123	New Zealand	104428	4320300	4-sep-09	New Zealand Dollar
149	Gabon	103347	1475000	null	CFA franc
149	Spain	195364	48061808	01-JUN-09	Euro

ddl