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.
INSERT INTO tablename
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
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.
To populate table2 with data from table1 using a subquery, you would issue the following to Oracle:
INSERT INTO table2
SELECT name, birthday, income
To only insert only the name and income into table2 from table1:
INSERT INTO table2(name, income)
SELECT name, income
As things change, you may need to change the data that's stored in the database. Using the UPDATE
command accomplishes this.
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.
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.'
To delete data from the table the DELETE command can be issued.
DELETE FROM tablename
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
, 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:
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
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
are very straight forward commands:
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
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.