Oracle 1Z0-007: Manipulating Data

Valid XHTML 1.0!

Manipulating Data

Describe each DML statement.

Insert
An INSERT statement creates a row or rows and stores them in the named table.
Update
The UPDATE statement is used to modify the data in a table.
Delete
The DELETE statement is used to delete rows in a table.
Merge
The MERGE statement, which combines the INSERT, DELETE, and UPDATE operations, allows you to move data from a transaction table to a master table with a single pass over the transaction table. Rows in the transaction table that match entries in the master table are used to update or delete rows from the master table, while those rows where there is no match can be inserted. Since the qualifying rows from the transaction table are available in the SET clause of the UPDATE operation, the MERGE statement provides a simple. Primarily of value when moving large amounts of data in data warehouse situations.
Select
The SELECT statement is used to query the database and retrieve selected data that match the criteria that you specify.

Insert rows into a table

insert into state_city
(state_abbrev, city_name)
values
('CA', 'San Francisco');

COMMIT;

Update rows in a table

UPDATE <table_name>
SET <column_name> = <value>
WHERE <column_name> = <value>
SELECT DISTINCT object_name
FROM test;

UPDATE test
SET object_name = 'LOAD'
WHERE object_name = 'DUAL';

Delete rows in a table

delete person where last_name = 'Jeremy';

Merge rows in a table

CREATE TABLE employee (
employee_id number(5),
first_name varchar2(20),
last_name varchar2(20),
dept_no number(2),
salary number(10))
tablespace data_sml;

insert into employee values (1, 'Paris', 'Hilton', 10, 100000);
insert into employee values (2, 'Nicky', 'Hilton', 20, 100000);
insert into employee values (3, 'Suzanna', 'Spears', 20, 50000);
insert into employee values (4, 'Maria', 'Sharpanova', 20, 40000);
commit;

create table bonuses (
employee_id number, bonus number default 100)
tablespace data_sml;

insert into bonuses (employee_id) values (1);
insert into bonuses (employee_id) values (2);
insert into bonuses (employee_id) values (4);
insert into bonuses (employee_id) values (6);
insert into bonuses (employee_id) values (7);
commit;

merge into bonuses B
using (
select employee_id, salary
from employee
where dept_no = 20) E
on (B.employee_id = E.employee_id)
when matched then
update set B.bonus = E.salary * 0.1
when not matched then
insert (B.employee_id, B.bonus)
values (E.employee_id, E.salary * 0.05);

Control Transactions

/*commit and rollback savepoint; when a savepoint is defined within a statement, it can be rolledback up to that particular point*/
insert into nomina(sueldo) values(1000000);
savepoint excelente;
insert into nomina(sueldo) values(100000);
savepoint bueno;
insert into nomina(sueldo) values(10000);
savepoint salario_mexicano;
commit;

/*eventhough the commit was already issued, we can still rollaback up to a savepoint location with:
ROLLBACK to bueno
That would rollback the insert with a value of 10000*/



Updated: July 30, 2005; Joe Gakenheimer