The keywords that Manipulate data and control transactions are called Data Manipulation Language or
DML. With out the DML a database would be useless and a waste.
Inserting Data
Syntax
INSERT INTO tablename [(
columnname, ...)]
VALUES (
datavalue, ...);
When inserting data if you are inserting a full row of data, placing data in all columns then you can omit the column names as long as you follow the order of the columns in the table. If there are no constraints that require a value to be inserted into the column you can opt out those values but you must then name the columns you wish to place data in if you either omit a column or change the order of data. Another way of not naming the columns is to insert a NULL into the column.
When moving data from table to table, an easy way is to use a subquery on an
INSERT statement.
Syntax
INSERT INTO [(
columnname, ...)]
subquery;
In a subquery the values that you are inserting are going to be defined in the
subquery. The subquery does not need to be encased in parentheses. When copying data from another table and the columns are the same, you must be sure that they are in the same order as they appear in the table that you are copying it to. If you are copying specific tables from another table then you must make sure they line up accordingly to the columns in the table that you are copying them to.
table1
| name |
address |
phone |
birthday |
income |
table2
To populate table2 with data from table1 using a subquery, you would issue the following to Oracle:
INSERT INTO table2
SELECT name, birthday, income
FROM table1;
To only insert only the name and income into table2 from table1:
INSERT INTO table2(name, income)
SELECT name, income
FROM table1;
Updating Data
As things change, you may need to change the data that's stored in the database. Using the
UPDATE command accomplishes this.
Syntax
UPDATE tablename
SET columnname =
value
[
WHERE condition];
If the
WHERE clause is omitted then Oracle will set
ALL values in the
columnname. After inserting hundreds of records you're told that something has changed, instead of issuing hundreds of update commands you can use a substitute variable. When using a substitute variable Oracle will ask for the value of each substitute variable from the user and then do it's magic.
UPDATE customers
SET region = '&Region'
WHERE state = '&State';
When the user enters the value 'N' for '&Region' and 'WI' for '&State' Oracle will find all rows where 'WI' is listed under the state column and set the 'region' column to 'N.'
Deleting Data
To delete data from the table the
DELETE command can be issued.
Syntax
DELETE FROM tablename
[
WHERE condition];
Be very careful when deleting data from a table. If you omit the
WHERE clause all data in the row will be deleted.
Transaction Control Statements
Issuing any DML Commands (
INSERT,
UPDATE, and
DELETE) is not permanently saved. You can see the changes but they don't affect the database as a whole until you have committed the changes. The group of commands used to alter a table or database is called a transaction. For the changes to be permanent you must issue the
COMMIT. However, when using a Data Definition Language Command (
DDL) the
COMMIT is implicitly issued. Let's say that you inserted, deleted, and modified some rows. For this to take effect you must issue this command:
COMMIT;
If you do your modification and use a
CREATE or
ALTER TABLE then you will not need to issue the
COMMIT command because it's issued right after the
CREATE or
ALTER commands. Until the
COMMIT statement has been issued the changes can be rolled back by issuing a
ROLLBACK command. You can create a save point at anytime during the creation of a transaction. The
SAVEPOINT and
ROLLBACK are very straight forward commands:
SAVEPOINT savepointname;
ROLLBACK TO savepointname;
Locking the Table
Oracle protects against data being modified by two users at the same time by locking the row. When a user has a row locked, Oracle makes it known that a row has been locked so another user doesn't override that lock with a table lock. During the time that a row is locked another user can view row but can not modify it. This is known as a shared lock. A user can explicitly lock a table in share mode by issuing the
LOCK TABLE command.
LOCK TABLE tablename IN SHARE | EXCLUSIVE MODE;
During a DDL operation Oracle puts an exclusive lock on the table so no other user can add, modify, or delete data.