




Remote DBA services Remote DBA Support Remote DBA RAC Remote DBA Reasons Remote Oracle Tuning Remote DBA Links Oracle DBA Support Oracle DBA Forum Oracle Disaster Oracle Training Oracle Tuning Oracle Training Remote DBA SQL Server Remote MSSQL Consulting Oracle DBA Hosting Oracle License Negotiation

EnterpriseDB: The Data Manipulation Language (DML)

Oracle Tips by Burleson Consulting

We are going to start our discussion of DML by discussing how to save or throw away database changes with the COMMIT, ROLLBACK and SAVEPOINT commands.

We will follow that up with the SELECT statement and all of its various parts and clauses. The SELECT statement will probably be the DML command that you most frequently write.

After a discussion on INSERT, UPDATE and DELETE, we will close with a discussion of prepared statements. Insert adds rows, update changes rows, delete removes rows and prepared statements are a performance helper.

You may want to follow along and run these commands yourself. I recommend that you run them and change them around to see what happens.

If you do want to follow along, you can run the PSQL utility discussed in Chapter 2. Probably the easiest way to use it is to run the SQL Terminal from the EnterpriseDB menu option on your PC. You can also run these in the SQL Interactive tool also discussed in Chapter 2.

COMMIT

Commit is the second most important command you can learn. Commit saves your data after you have run INSERTs, UPDATEs, and DELETEs. EnterpriseDB will auto-commit, unless you have changed the default configuration, any DDL changes such as CREATE TABLE or ALTER INDEX.

EnterpriseDB does not auto-commit DML statements. If you want to save your changes, you must manually issue a COMMIT command. This is a good thing. It helps performance in that you can run many statements before committing your changes and it ensures that you can choose to not save in the event that you make a typo or other mistake.

The syntax for COMMIT is very easy to remember:

COMMIT

If you ever run into a situation where you're positive that you have made a change but no one else can see it, make sure that you have issued a commit.

COMMIT;

ROLLBACK

Rollback is the #1 command that you should learn. Rollback tells the database to undo all changes since the last COMMIT or ROLLBACK. If you make a mistake and want to undo it, issue a rollback.

ROLLBACK;

SAVEPOINT

If you are running several commands where some commands are dependant on other commands, you may want to issue a savepoint. A savepoint is a label that allows you to selectively rollback your changes. A savepoint is only valid in a transaction block that begins with BEGIN TRANSACTION and ends with COMMIT or ROLLBACK.

For example, if I wanted to insert into two records into emp and then later undo the second insert, I could issue the following commands:

BEGIN TRANSACTION;
INSERT INTO emp (empno) VALUES (1);
SAVEPOINT emp1;
INSERT INTO emp (empno) VALUES (2);
ROLLBACK TO emp1;
COMMIT;

In this example, the command ROLLBACK to emp1 un-inserted the second record. Commit saved the first record.

SELECT

When you need to retrieve records from the database, SELECT is the command that you will use. The SELECT command has a lot of functionality and can be quite complex at times. However, the basic use of the SELECT command is not complex at all.

The SELECT command follows the basic format of:

Basic Select

The most basic syntax for the SELECT command is:

SELECT * FROM emp;

empno | ename |    job    | mgr |      hiredate      |   sal   | comm   | deptno
-------+--------+-----------+------+--------------------+---------+---------+--------
7369 | SMITH | CLERK     | 7902 | 17-DEC-80 00:00:00 | 800.00 |         |     20
7499 | ALLEN | SALESMAN | 7698 | 20-FEB-81 00:00:00 | 1600.00 | 300.00 |     30
7521 | WARD   | SALESMAN | 7698 | 22-FEB-81 00:00:00 | 1250.00 | 500.00 |     30
7566 | JONES | MANAGER   | 7839 | 02-APR-81 00:00:00 | 2975.00 |         |     20
7654 | MARTIN | SALESMAN | 7698 | 28-SEP-81 00:00:00 | 1250.00 | 1400.00 |     30
7698 | BLAKE | MANAGER   | 7839 | 01-MAY-81 00:00:00 | 2850.00 |         |     30
7782 | CLARK | MANAGER   | 7839 | 09-JUN-81 00:00:00 | 2450.00 |         |     10

7788 | SCOTT | ANALYST   | 7566 | 19-APR-87 00:00:00 | 3000.00 |         |     20
7839 | KING   | PRESIDENT |      | 17-NOV-81 00:00:00 | 5000.00 |         |     10
7844 | TURNER | SALESMAN | 7698 | 08-SEP-81 00:00:00 | 1500.00 |    0.00 |     30
7876 | ADAMS | CLERK     | 7788 | 23-MAY-87 00:00:00 | 1100.00 |         |     20
7900 | JAMES | CLERK     | 7698 | 03-DEC-81 00:00:00 | 950.00 |         |     30
7902 | FORD   | ANALYST   | 7566 | 03-DEC-81 00:00:00 | 3000.00 |         |     20
7934 | MILLER | CLERK     | 7782 | 23-JAN-82 00:00:00 | 1300.00 |         |     10
8000 | Lewis | CLERK     | 7782 | 04-OCT-06 00:00:00 | 950.00 |         |

(15 rows)

This query is selecting all columns (* is short hand for all columns) and all rows from the table named "emp". To select only the empno and ename columns we would change it to this:

SELECT empno, ename FROM emp;

(15 rows)

Indentation is not required but I use it to make the command easier to read. I capitalize keywords and use lower case text for columns and tables.

Each column you select is separated by a comma (,). You can put the entire command on one line as we have it above, or you can put all of the parts on its own line, or any combination of lines.

SELECT empno, ename
FROM emp;

The semi colon (;) tells the database that the command is complete and should be run. If you do not put the semi-colon, the command will not run. You can put the semi-colon on a line by itself after the fact if you choose to do so.

With only a single table, the select list is easy to read. When you have multiple tables, it is easier to read if you use a table alias. A table alias is required when two tables in a single select have a column with the same name. The SQL engine needs some way to tell them apart.

SELECT employee.empno, employee.ename
FROM emp employee;

(15 rows)

Notice the alias listed after the emp table, employee. We can call the alias anything we want. I use aliases to help me identify the table so I do not use one letter aliases such as a, b, c, etc. Unfortunately, you will probably see a lot of code that uses poor alias naming standards.

Like a table alias, a column can also be aliased. You may want to alias a column that has a duplicate name in another table or one that just makes more sense.

SELECT employee.empno AS employee_number,
employee.ename AS "Employee Name"
FROM emp employee;

(15 rows)

In the above query I aliased empno to the simple alias employee_number. I used a more complex alias name for ename, "Employee Name". Using double quotes around the alias preserves case and allows spaces in the name. This makes it more suitable for reporting purposes.

* Do not use spaces when you are creating a query that will require you to refer to individual columns. You can do it but it's painful. You're required to use the double quotes whenever you reference the column.

This is an excerpt from the book "EnterpriseDB: The Definitive Reference" by Rampant TechPress.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider. Get real Remote DBA experts, call
BC Remote DBA today.

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Oracle� is the registered trademark of Oracle Corporation.

Hit Counter