Manipulate, Commit, and Take Back: How Change Occurs

Part 4 in a second series on the basics of the relational database and SQL

July/August 2016

This article is the fourth in a series that helps you build on the fundamentals you learned in the 12-part SQL 101 series in Oracle Magazine.

The previous Beyond SQL 101 article, “Setting Yourself Up for Selective Results,” introduced you to the Oracle Database set operators—UNION, UNION ALL, MINUS, and INTERSECT—and the types of operations they perform on sets of data. You learned how to use parentheses to override the default execution order for set operations. You also discovered how the INTERSECT set operator is similar to and different from an equijoin and that the list of columns in set operation SELECT lists is important.

In this article, you’ll learn about data manipulation language (DML). DML statements are SQL statements that access and manipulate data in existing schema objects. You already know one DML statement, SELECT. Even though you don’t modify data via SELECT, you manipulate it, merely by retrieving and reading it. Now you’ll learn about three more DML statements: INSERT, UPDATE, and DELETE, as well as TRUNCATE, which is a data definition language (DDL) statement. (You will learn more about DDL statements in future articles in this series.) These statements modify data in tables by creating, changing, or deleting it. You’ll also learn about transaction control: how to make your changes permanent with the COMMIT statement and how to undo your changes with the ROLLBACK statement.

To try out the examples in this series, you need access to an Oracle Database instance. If necessary, download and install an Oracle Database edition for your operating system. I recommend installing Oracle Database, Enterprise Edition 12c Release 1 (12.1.0.2.0). If you install the Oracle Database software, choose the installation option that enables you to create and configure a database. A new database, including sample user accounts and their associated schemas, will be created for you. (Note that SQL_201 is the user account to use for the examples in this series; it’s also the schema in which you’ll create database tables and other objects.) When the installation process prompts you to specify schema passwords, enter and confirm passwords for the SYS and SYSTEM users and make a note of them.

Finally—whether you installed the database software from scratch or have access to an existing Oracle Database instance—download, unzip, and execute the SQL script to create the tables for the SQL_201 schema that are required for this article’s examples. (View the script in a text editor for execution instructions.)

An INSERT statement creates new data in a table. In its simplest form, INSERT inserts a single row of data. You can also insert multiple rows of data by using INSERT in conjunction with a subquery.

Suppose that your company is hiring new employees and one of the business requirements is to insert a record for each newly hired employee into the EMPLOYEE table. The INSERT statement in Listing 1 demonstrates one of the simplest methods you can use to create data for a table. The INSERT INTO keywords begin this single-row INSERT statement, followed by the name of the table in which you want to create new data rows. The VALUES keyword must precede a comma-delimited list of values, enclosed in parentheses, that you want inserted into the table.

Code Listing 1: Inserting a new employee record into the EMPLOYEE table

SQL> set feedback on
SQL> set lines 32000
SQL> INSERT INTO employee (employee_id, first_name, last_name, hire_date, 
 2                         salary, manager, department_id)
 3   VALUES (6574, 'Marcy', 'Tamra', to_date('04-MAY-2016', 'DD-MON-YYYY 
 4           HH24:MI:SS'), null, 28, 10);

1 row created.

Note in Listing 1 that the table name is followed by a comma-delimited list of the columns into which the insertion is to be made. To help safeguard against INSERT statement errors—and to take account of future table changes—it is a good practice to include a column list.

In Listing 1, each value in the VALUES clause corresponds to a column in the column list, with matching datatypes. It is essential that the number, order, and datatypes of the columns in the column list correspond to the number, order, and datatypes of the values in the list of values. If you omit a column list altogether, you must list a value (even if it’s a NULL value) for every column in the table. Similarly, if you are not inserting data into every column of the table, you must explicitly list every column into which you want to insert data.

Listings 2, 3, and 4 show the kinds of INSERT errors that can occur if your column list is missing, incomplete, or incorrect. Listing 2 demonstrates what happens if you omit a list of columns and your INSERT statement does not list a value for every column in the table. Without an explicit list of column names with which to compare values, the INSERT statement tries to insert a value into every column of the table. Because no value is listed for the DEPARTMENT_ID column of the EMPLOYEE table, the statement in Listing 2 fails with a “not enough values” error message.

Code Listing 2: An INSERT statement with no column list and a short VALUES clause

SQL> INSERT INTO employee                                                                     
  2  VALUES (6575, 'Sasha', 'Meyer', to_date('04-MAY-2016', 
'DD-MON-YYYY HH24:MI:SS'), 85000, 1234);
INSERT INTO employee
            *
ERROR at line 1:
ORA-00947: not enough values

Listing 3 includes a column list, but one column name is missing. The VALUES clause lists a HIRE_DATE value, but the column list omits the HIRE_DATE column name. Like the error message returned in Listing 2, the error message in Listing 3 indicates that the number of columns being inserted into must match the number of values listed, for INSERT purposes.

Code Listing 3: An INSERT statement with a column missing from the column list

SQL> INSERT INTO employee (employee_id, first_name, last_name, salary, 
manager, department_id)
  2  VALUES (6575, 'Sasha', 'Meyer', <strong>to_date(‘04-MAY-2016’, 
‘DD-MON-YYYY HH24:MI:SS’)</strong>, 85000, 1234, 10);
INSERT INTO employee (employee_id, first_name, last_name, salary, manager, 
department_id)
            *
ERROR at line 1:
ORA-00913: too many values

The INSERT statement in Listing 4 has a column list, but the list of values does not correspond exactly to the columns in the column list. The INSERT statement tries to insert the value 1234 into the HIRE_DATE column and fails with an error message indicating that the datatypes are inconsistent.

Code Listing 4: A list of values that doesn’t match the column list order

SQL> INSERT INTO employee (employee_id, first_name, last_name, hire_date, 
                           salary, manager, department_id)
  2  VALUES (6575, 'Sasha', 'Meyer', 1234, to_date('04-MAY-2016', 
'DD-MON-YYYY HH24:MI:SS'), 85000, 10);
VALUES (6575, 'Sasha', 'Meyer', 1234, to_date('04-MAY-2016', 
'DD-MON-YYYY HH24:MI:SS'), 85000, 10)
                                *
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

All text literals in your list of values must be enclosed in single quotes. All dates require the TO_DATE conversion function (or the appropriate TO_TIMESTAMP conversion function when you’re inserting into time stamp columns) with a listed format mask, unless the listed date value already has the appropriate NLS_DATE_FORMAT. For more information, see the documentation for your Oracle Database version.

Although you can successfully create a record with an INSERT statement like the one in Listing 1, the record isn’t made permanent to other users of your system until you save it. A DML statement such as an INSERT statement is always executed in conjunction with either a COMMIT statement, which saves the change, or a ROLLBACK statement, which takes back or undoes the change. A DML statement executed in tandem with either a COMMIT or a ROLLBACK statement is known as a transaction. A transaction is composed of one or more DML statements that are logically executed together. This set of statements is also referred to as a logical unit of work.

The statement in Listing 5 permanently saves the record inserted in Listing 1 to the database. The COMMIT statement is powerful. Any previously uncommitted changes are committed to the database with that one statement and cannot be undone. The result of this save action is that other users, or sessions, connected to the database can see the new record. If you’re the session that issues a DML statement, you can always see your own changes, but other sessions can see your changes only after you issue the COMMIT statement.

Code Listing 5: Listing 1’s INSERT transaction concluded with a COMMIT statement

SQL> commit;
Commit complete.

SQL> select *
  2    from employee
  3   where employee_id = 6574;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE SALARY MANAGER DEPARTMENT_ID
———————————  ——————————  —————————  ————————— —————— ——————— —————————————
       6574  Marcy       Tamra      04-MAY-16             28            10

1 row selected.

A session is a private, or individual, connection to the Oracle Database server. When a user logs in and authenticates with a valid login ID and password, a session is started for that user. The session ends when the user explicitly disconnects. An individual user can open multiple concurrent sessions by invoking the client used to connect to Oracle Database multiple times without exiting the currently logged-in session(s).

Consider the example in Listing 6. In Listing 6, you establish a second, separate connection to the database without exiting your first session. In your first session, you insert a new employee record without committing the record to the database. When you query the EMPLOYEE table for the record in your first session, you retrieve the newly inserted record. However, your second session is unable to retrieve the same record. Only after you issue a COMMIT statement in your first session is your second session able to retrieve the new employee record.

Code Listing 6: Demonstration that data inserted in one session becomes visible to other sessions only when committed

<strong>-- While still logged into your first session, log onto the database again.
-- Session 2</strong>
SQL> connect sql_201@dbim
Enter password: 
Connected.
<strong>-- Without exiting your new, second session, go to your first session 
and insert a new employee record.
-- Session 1</strong>
SQL> insert into employee (employee_id, first_name, last_name, hire_date, 
                           salary, manager, department_id)
  2  values (6575, 'Sasha', 'Meyer', to_date('04-MAY-2016', 'DD-MON-YYYY 
             HH24:MI:SS'), 85000, 1234, 10);
1 row created.
<strong>-- Query the newly-inserted record. 
-- Session 1</strong>
SQL> select *
  2    from employee
  3   where employee_id = 6575;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE  SALARY MANAGER  DEPARTMENT_ID
———————————  ——————————  —————————  —————————— —————— ———————  —————————————
       6575  Sasha       Meyer      04-MAY-16   85000    1234             10
1 row selected.
<strong>-- Go to your second session and try to query the same record. 
-- Session 2</strong>
SQL> set feedback on
SQL> set lines 32000
SQL> select *
  2    from employee
  3   where employee_id = 6575;
<strong>no rows selected  -- Go to your first session and commit the newly-inserted
record. 
-- Session 1</strong>
SQL> commit;
Commit complete.
<strong>-- Return to your second session and try to query the same record again.  
-- Session 2</strong>
SQL> select *
  2    from employee
  3   where employee_id = 6575;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE  SALARY  MANAGER  DEPARTMENT_ID
———————————  ——————————  —————————  —————————— ——————  ———————  —————————————
       6575  Sasha       Meyer      04-MAY-16   85000     1234             10
<strong>1 row selected.</strong>

The action that’s the opposite of committing your changes to the database is to undo them. The command for reversing a change in the session that makes a change is ROLLBACK. The set of commands in Listing 7 demonstrates the ability to roll back a change as if it did not occur. After you successfully insert a record into the database and receive the “1 row created” success message, you can query the database to retrieve the record. If, after viewing your result, you don’t want the change you’ve just made to be permanent, you can undo the change by issuing a ROLLBACK statement. Then, upon requerying for the record, you can see that the inserted data no longer exists.

Code Listing 7: ROLLBACK undoing UNCOMMITTED changes

SQL> insert into employee (employee_id, first_name, last_name, hire_date, 
                           salary,  manager, department_id)
  2  values (6576, 'Mary', 'Streicher', to_date('05-MAY-2016', 'DD-MON-YYYY 
             HH24:MI:SS'), null, 1234, 10);
1 row created.

SQL> select *
  2    from employee
  3   where employee_id = 6576;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE  SALARY MANAGER  DEPARTMENT_ID
———————————  ——————————  —————————  —————————— —————— ———————  —————————————
       6576  Mary        Streicher  06-MAY-16            1234             10

1 row selected.

<strong>SQL> rollback;  Rollback complete.</strong>

SQL> select *
  2    from employee
  3   where employee_id = 6576;
<strong>no rows selected</strong>

A ROLLBACK statement can undo a DML statement, but it cannot undo a COMMIT statement. The example in Listing 8 illustrates this fact. The transaction inserts a new employee record into the EMPLOYEE table and then queries that table to retrieve the record it just inserted. Then it issues a COMMIT statement, followed by a ROLLBACK statement. The last query successfully retrieves the newly inserted employee record, demonstrating that a ROLLBACK statement has no effect on a COMMIT action.

Code Listing 8: ROLLBACK failing to undo committed changes

SQL> insert into employee (employee_id, first_name, last_name, hire_date, 
                           salary, manager, department_id)
  2                values (6576, 'Mary', 'Streicher', to_date('05-MAY-2016', 
                           'DD-MON-YYYY HH24:MI:SS'), null, 1234, 10);

1 row created.

SQL> select *
  2    from employee
  3  where employee_id = 6576;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE  SALARY MANAGER  DEPARTMENT_ID
———————————  ——————————  —————————  —————————  —————— ———————  —————————————
       6576  Mary        Streicher  05-MAY-16            1234             10

1 row selected.

<strong>SQL> commit;  Commit complete.  SQL> rollback;  Rollback complete.
</strong>
SQL> select *
  2    from employee
  3   where employee_id = 6576;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE  SALARY MANAGER  DEPARTMENT_ID
———————————  ——————————  —————————  —————————  —————— ———————  —————————————
       6576  Mary        Streicher  05-MAY-16            1234             10

1 row selected.

You can use an UPDATE statement to change existing data in a table. An UPDATE statement always refers to a single table. Consider the example in Listing 9. The employee record for Mary Streicher was inserted into the EMPLOYEE table in Listing 8 with no salary value. This record is changed in Listing 9 to include a new salary value, 95000.

Code Listing 9: Updating existing data in the EMPLOYEE table

SQL> <strong>update</strong> employee
  2     <strong>set</strong> salary      = 95000
  3   where employee_id = 6576;

1 row updated.

SQL> select *
  2    from employee
  3   where employee_id = 6576;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE  SALARY MANAGER  DEPARTMENT_ID
———————————  ——————————  —————————  —————————  —————— ———————  —————————————
       6576  Mary        Streicher  05-MAY-16   95000    1234             10

1 row selected.

SQL> commit;
Commit complete.

The UPDATE keyword precedes the name of the table to be updated, and the SET keyword precedes the name of the column (or set of comma-delimited columns) for which values are to be changed. An UPDATE statement can change one or more rows in a table simultaneously. If an UPDATE statement doesn’t include a WHERE clause, the statement will change all rows of the table it is updating. In Listing 9, the inclusion of a WHERE clause (“where employee_id = 6576”) restricts the UPDATE statement’s action to the record for Mary Streicher.

Note also that if you wanted to set the salary value for Mary Streicher back to its original value, NULL, your UPDATE statement would be

UPDATE employee
  SET salary      = NULL
WHERE employee_id = 6576;

When selecting data for retrieval and evaluating NULL values, you must use the IS NULL operator, but when setting data to NULL values during an update operation, you use the = operator.

A DELETE statement removes data rows from a table. You can remove a single row, multiple rows, or all rows in a table simultaneously. The example in Listing 10 demonstrates the removal of the record that was inserted and committed in Listing 8 and updated in Listing 9. (Recall that if the record had not been committed, a ROLLBACK statement would also have eliminated the record from the EMPLOYEE table.) A DELETE statement that doesn’t include a WHERE clause removes all rows from the table on which it is performing its action.

Code Listing 10: Deleting existing data from the EMPLOYEE table

SQL> <strong>delete</strong>
  2    <strong>from</strong> employee
  3   where employee_id = 6576;

1 row deleted.

SQL> select *
  2    from employee
  3   where employee_id = 6576;

no rows selected

SQL> commit;
Commit complete.

You can also remove all data rows from a table by issuing a TRUNCATE statement, which doesn’t allow the use of a WHERE clause. TRUNCATE also automatically issues a COMMIT statement, so its changes cannot be rolled back. Consider the example in Listing 11. A query against the EMP2 table (a copy of the EMPLOYEE table created in the previous series article, “Setting Yourself Up for Selective Results”) displays a result set with 11 records. The TRUNCATE statement is then issued against the table. When the table is requeried, you can see that all the records have been deleted. You can also see that issuing a ROLLBACK statement and requerying has no effect on the returned result, “no rows selected.” The COMMIT statement was issued alongside the TRUNCATE statement, without your explicitly having issued it.

Code Listing 11: Deleting data from the EMP2 table with the TRUNCATE statement

SQL> select *        
  2    from emp2;

EMPLOYEE_ID  FIRST_NAME  LAST_NAME  HIRE_DATE   SALARY  MANAGER  DEPARTMENT_ID
———————————  ——————————  —————————  ——————————  ——————  ———————  —————————————
         28  Emily       Eckhardt   07-JUL-04   100000                      10
         37  Frances     Newton     14-SEP-05    75000 
       1234  Donald      Newton     24-SEP-06    80000       28             10
       7895  Matthew     Michaels   16-MAY-07    70000       28             10
       6567  Roger       Friedli    16-MAY-07    60000       28             10
       6568  Betsy       James      16-MAY-07    60000       28             10
       6569  michael     peterson   03-NOV-08    90000                      20
       6570  mark        leblanc    06-MAR-09    65000     6569             20
       6571  Thomas      Jeffrey    27-FEB-10   300000                      30
       6572  Theresa     Wong       27-FEB-10    70000     6571             30
       6573  Lori        Dovichi    07-JUL-11                28             10

11 rows selected.

SQL> truncate table emp2;
Table truncated.

SQL> select *
  2    from emp2;

no rows selected

SQL> rollback;
Rollback complete.

SQL> select *
  2    from emp2;

no rows selected

This article has introduced you to DML and three of its statements: INSERT, UPDATE, and DELETE, as well as one DDL statement: TRUNCATE. You’ve learned what a session is, and you’ve seen how an UPDATE statement handles NULL values differently from a SELECT statement. You’ve learned about transaction control, how to make your changes permanent with the COMMIT statement, and how to undo your changes with the ROLLBACK statement. You’ve also discovered how a ROLLBACK statement has no effect on either a COMMIT statement or a TRUNCATE statement.

The next article in this series introduces the MERGE statement, Oracle’s locking and read-consistency features, and metadata and data dictionary views.

Photography by Aaron Burson, Unsplash