2 Querying and Manipulating Data

This section shows how to explore the database, retrieve information from it, change the information in existing tables, and control transaction processing.

This chapter contains the following sections:

Exploring Database Objects

In addition to tables, Oracle Database has many other database objects types. While some objects have many extensive management options, most of them have similar properties. For example, every object in a database belongs to only one schema, and has a unique name with that schema. For that reason, Oracle recommends that your object naming conventions and practices support clear identification of new objects and object types with appropriate schemas. All objects that you will use here belong to the same hr schema. Generally, applications work with objects in the same schema.

When you create your own objects, remember that object names cannot exceed 30 characters, and must start with a letter.

Looking at Schema Object Types

In this section, you will further familiarize yourself with the hr sample schema and its attributes, or database objects. You will learn how you can view these objects by browsing with the Oracle SQL Developer.

Start by examining some of the types of objects that each schema has.

To browse the HR schema:

  1. Start Oracle SQL Developer.

  2. In the SQL Developer navigation hierarchy, under the Connections tab, click the 'plus' sign next to hr_conn.

  3. In the Connection Information dialog, authenticate the connection to the hr schema by providing the password. Click OK.

    Description of expl_hr_6.gif follows
    Description of the illustration expl_hr_6.gif

  4. In Connections navigation hierarchy, click the 'plus' sign next to hr_conn to expand the view on the hr schema database objects.

    Description of expl_hr_1.gif follows
    Description of the illustration expl_hr_1.gif

The schema contains many objects, including tables, views, indexes, packages, procedures, functions, triggers, types, sequences, and so on. Briefly, here is a definition of each type of database object that you are likely to use:

  • Tables are basic units of data storage in an Oracle Database, and hold all user-accessible data.

  • Views are customized presentations of data from one or more tables, or even other views.

  • Indexes are optional structures that are created to increase the performance of data retrieval on a table.

  • Functions are PL/SQL programming objects that can be stored and executed in the database. Functions return a value.

  • Procedures are PL/SQL programming objects that can be stored and executed in the database. Procedures do not return a value.

  • Packages contains procedures or functions that can be stored and executed in the database.

  • Triggers are stored procedures or functions that are associated with a table, view, or event. Triggers can be called before or after an event for follow-up action, to prevent erroneous operations, to modify new data so that it conforms to explicit business rules, or to log a record of an operation or an event.

  • Types associate a fixed set of properties with the values that can be used in a column of a table, or in an argument of a procedure or function. Oracle Database treats values of one data type differently from values of another data type.

  • Sequences are used to generate unique integers; you can use them to automatically generate primary key values.

Exploring Tables and Viewing Data

In this section, you will learn how to find out about the properties of database tables, and how to view the data these tables contain.

An Oracle Database table is its basic data container. All data that a user can access is inside one of the tables of the database schema. Each table is two-dimensional object that has rows, which are individual records, and columns, which represent the various fields of each record.

To view a table:

  1. In Connections navigation hierarchy, click the 'plus' sign next to Tables to expand the list of tables in the hr schema.

    The expanded list of tables includes the tables countries, departments, employees, job_history, jobs, locations, and regions.

    Description of expl_hr_2.gif follows
    Description of the illustration expl_hr_2.gif

  2. Click the employees table.

    On the right-hand side of the Oracle SQL Developer window, under the Columns tab, a listing of all columns of this table appears: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, and DEPARTMENT_ID. Each column of a table has an associated data type that defines it as character data, an integer, a floating-point number, a date, or time information. To see all properties of the column, move the horizontal scroll bar to the right.

    Description of expl_hr_3.gif follows
    Description of the illustration expl_hr_3.gif

  3. Click the Constraints tab.

    You will see all the constraints that are used on this table including the type of constraint, the constraint's referenced table, whether the constraint is enabled, and other properties.

    Description of expl_hr_4.gif follows
    Description of the illustration expl_hr_4.gif

  4. Similarly, you can explore the various table properties by clicking on the appropriate tabs:

    • Grants describes the privileges for the table

    • Statistics describes the properties of the data in the table, such as number of records, the number of blocks in memory used by the table, average row length, and so on.

    • Column Statistics lists the number of distinct entries for each column, the low and high values, and so on.

    • Triggers lists the triggers associated with the table together with the type of trigger, the triggering event, and so on.

    • Dependencies lists all the objects that are dependent on this table, such as triggers and views.

    • Details lists other details of the table, such as creation date, owner (hr), name, partitioning information, and so on.

    • Indexes lists the indexes that are defined on the table columns, together with their status, type, and so on.

    • SQL summarizes the preceding information in the definition of the table employees; it includes column definition, indexes, and so on.

To view data in a table:

On the right-hand side of the Oracle SQL Developer window, click the Data tab.

You will see a listing of all records of this table. Each column of a table has an associated data type that defines it as character data, an integer, a floating-point number, a date, or time information. To see all properties of the column, move the horizontal scroll bar to the right.

Description of expl_hr_5.gif follows
Description of the illustration expl_hr_5.gif

Retrieving Data with Queries

A query is an operation that retrieves data from one or more tables or views. A top-level SELECT statement returns results of a query, and a query nested within another SQL statement is called a subquery.

This section introduces some types of queries and subqueries.

Selecting Data from a Table

A simple query form looks like this:

SELECT select_list FROM source_list

Here, select_list specifies the columns from which the data is retrieved, and the source_list specifies the tables or views where these columns are found. The number of columns, as well as the data type and length of each column, is determined by the elements of the select list. Note also that the select list can use SQL functions.

To see all columns in a table, use * for select_list.

Example 2-1uses the SELECT statement to return the information you previously saw by viewing the employees table in the Data window.

Example 2-1 Selecting All Columns in a Table

  1. In the SQL Worksheet pane, enter:

    SELECT * FROM employees;
    
  2. Above the SQL Worksheet pane, click the Run Script icon. Alternatively, you can use the F5 shortcut key.

    Description of select_simple_1.gif follows
    Description of the illustration select_simple_1.gif

  3. Click the Script Output tab, below the SQL Worksheet pane, to see the results of the query.

    EMPLOYEE_ID            FIRST_NAME           LAST_NAME                 ...
    ---------------------- -------------------- ------------------------- ...
    100                    Steven               King                      ...
    101                    Neena                Kochhar                   ...
    102                    Lex                  De Haan                   ...
    ...
    107 rows selected
    

Between running different queries, you can clear both the SQL Worksheet and Script Output panes by clicking the Eraser icon in the toolbar.

Example 2-2 shows how to use the SELECT statement to return only the columns you requested in your query, namely first_name, last_name, and hire_date.

Example 2-2 Selecting Specific Columns from a Table

SELECT first_name, last_name, hire_date FROM employees;

These are the results of the query.

FIRST_NAME             LAST_NAME           HIRE_DATE                 
---------------------- ------------------- ------------------------- 
Steven                 King                17-JUN-87                
Neena                  Kochhar             21-SEP-89                 
Lex                    De Haan             13-JAN-93                 
...
107 rows selected

Using Column Aliases

To display a column with a new heading, you can rename a column within your report by using an alias immediately after the correct name of the column. This alias effectively renames the item for the duration of the query.

In Example 2-3, the SELECT statement returns the columns you request in your query, but with the column headings that you specified as aliases: name1, name2, and hired.

Example 2-3 Using a Simple Column Alias

SELECT first_name name1, last_name name2, hire_date hired FROM employees;

The results of the query follow:

NAME1                  NAME2                 HIRED                     
---------------------  --------------------  ------------------------- 
Steven                 King                  17-JUN-87                 
Neena                  Kochhar               21-SEP-89                 
Lex                    De Haan               13-JAN-93                 
...
107 rows selected

If the alias that you want to use contains uppercase and lowercase characters or spaces, or a combination, you must use double quotation marks (").

Example 2-4 uses a SELECT statement to return the columns with column heading aliases that you specify: First, Last, and Date Started.

Example 2-4 Using Quoted Alias Columns

SELECT first_name "First", last_name "Last", hire_date "Date Started" 
FROM employees;

The results of the query follow.

First                  Last                 Date Started              
---------------------- -------------------- ------------------------- 
Steven                 King                 17-JUN-87                 
Neena                  Kochhar              21-SEP-89                 
Lex                    De Haan              13-JAN-93                 
...
107 rows selected

Restricting Data to Match Specific Conditions

In addition to the SELECT and FROM keywords, other common clauses are used in queries. The WHERE clause uses comparison operators to select the rows that should be retrieved, instead of returning all the rows in the tables.

This table lists the comparison operators that can be used in the WHERE clause.

Comparison Operator Definition
= Tests for equality
!=, <> Tests for inequality
> Tests for greater than
>= Tests for greater than or equal
< Tests for less than
<= Tests for less than or equal
BETWEEN a AND b Tests for a fit in the range between two values, inclusive
LIKE Tests for a match in a string, using the wildcard symbols (%) for zero or multiple characters, or underscore (_) for a single character
IN() Tests for a match in a specified list of values
NOT IN() Tests that there is no match in a specified list of values
IS NULL Tests that the value is null
IS NOT NULL Tests that the value is not null

The WHERE clause can test a single condition at a time, or combine multiple tests using the AND clause.

Example 2-5 shows how to use the WHERE clause to return the column values that are restricted to a single department, which has 90 for its department_id.

Example 2-5 Testing for a Single Condition

SELECT first_name "First", last_name "Last"
FROM employees
WHERE department_id=90;

The results of the query appear.

First                Last                      
-------------------- ------------------------- 
Steven               King                      
Neena                Kochhar                   
Lex                  De Haan                   
 
3 rows selected

Example 2-6 shows how to use the WHERE ... AND clause to return the rows that are restricted to two separate condition, to match a salary that is greater or equal to 11,000, and an assigned (not null) commission rate.

Example 2-6 Testing Multiple Conditions

SELECT first_name "First", last_name "Last", 
SALARY "Salary", COMMISSION_PCT "%"
FROM employees
WHERE salary >=11000 AND commission_pct IS NOT NULL;

The results of the query appear.

First                Last                  Salary                 %
-------------------- --------------------- --------------------   ----- 
John                 Russell               14000                  0.4
Karen                Partners              13500                  0.3
Alberto              Errazuriz             12000                  0.3
...
6 rows selected

Example 2-7 uses the WHERE clause to return the six rows where the last name starts with Ma: Mallin, Markle, Marlow, Marvins, Matos, and Mavris. If you use a matching expression '%ma%' instead (the text ma could appear anywhere in the column), your results would contain only three rows, for Kumar, Urman, and Vollman.

Example 2-7 Testing for a Matching String

SELECT first_name "First", last_name "Last"
FROM employees
WHERE last_name LIKE 'Ma%';

The results of the query appear.

First                Last                      
-------------------- ------------------------- 
Jason                Mallin                    
Steven               Markle                    
James                Marlow                    
...
6 rows selected

Example 2-8 shows you how to use the WHERE ... IN clause to find employees who work in several different departments, matching the DEPARTMENT_ID to a list of values 100, 110, 120. The result will contain eight rows, with four rows matching the first value in the list, and the other two rows matching the second value in the list; there are no matches for 120.

Example 2-8 Testing for a Match in a List of Values

SELECT first_name "First", last_name "Last", department_id "Department"
FROM employees
WHERE department_id IN (100, 110, 120);

The results of the query appear.

First                Last                      Department             
-------------------- ------------------------- ---------------------- 
John                 Chen                      100                    
Daniel               Faviet                    100                    
William              Gietz                     110                    
...
8 rows selected

If you want to find employees who work in a particular department, but do not know the corresponding department_id value, you must look in both the employees and departments tables. Looking at an intersection of two tables is a JOIN operation.

Fully qualified column names, such as employees.employee_id, are optional. However, when queries use two or more tables that have the same column name, you must identify these columns with the table. For example, the employees.department_id and departments.department_id could be used together to determine the name of a department in which an employee works.

Note that when using fully qualified column names, the query is more readable if you use an alias for the name of the table, such as d for departments. The column departments.department_id then becomes d.department_id, and employees.department_id becomes e.department_id. You must create these table aliases in the FROM clause of the query.

Example 2-9 shows the result set containing columns from two separate tables. Because the column names in the report are unique, they did not need to be qualified by the table name. However, because the WHERE clause uses the same column name from two different tables, you must qualify it.

Example 2-9 Testing for a Value in Another Table

SELECT e.first_name "First", e.last_name "Last", d.department_name "Department"
FROM employees e, departments d
WHERE e.department_id = d.department_id;

The results of the query appear.

First                Last                      Department                     
-------------------- ------------------------- ------------------------------ 
Jennifer             Whalen                    Administration                 
Michael              Hartstein                 Marketing                      
Pat                  Fay                       Marketing                      
...
106 rows selected

Searching for Patterns in Data

Regular expressions allow you to use standard syntax conventions to search for complex patterns in character sequences. A regular expression defines the search pattern by using metacharacters that specify search algorithms, and literals that specify the characters.

Regular expression functions include REGEXP_INSTR, REGEXP_LIKE, REGEXP_REPLACE, and REGEXP_SUBSTR.

Example 2-10 shows how to find all managers. The metacharacter | indicates an OR condition, which you must use because the manager position is specified either as '%_MGR' or '%_MAN', depending on department. The option i specifies that the match should be case insensitive.

Example 2-10 Finding a Matching Data Pattern

SELECT first_name "First", last_name "Last", job_id "Job"
FROM employees
WHERE REGEXP_LIKE (job_id, '(_m[an|gr])', 'i');

The results of the query appear.

First                Last                      Job        
-------------------- ------------------------- ---------- 
Nancy                Greenberg                 FI_MGR     
Den                  Raphaely                  PU_MAN     
Matthew              Weiss                     ST_MAN     
...
14 rows selected

Example 2-11 shows how the REGEXPR_LIKE expression selects rows where the last_name has a double vowel (two adjacent occurrences of either a, e, i, o, or u). See Oracle Database SQL Language Reference for information about the REGEXP_LIKE condition.

Example 2-11 Finding a Matching Data Pattern (Adjacent Characters)

SELECT first_name "First", last_name "Last"
FROM employees
WHERE REGEXP_LIKE (last_name, '([aeiou])\1', 'i');

The results of the query appear.

First                Last                      
-------------------- ------------------------- 
Harrison             Bloom                     
Lex                  De Haan                   
Kevin                Feeney                    
...
8 rows selected

To find a data pattern and replace it with another one, use the REGEXPR_REPLACE. Example 2-12 replaces the phone numbers of the format 'nnn.nnn.nnnn' with the format '(nnn) nnn-nnnn'. Note that digits are matched with the metacharacter [:digit], while the metacharacter {n} indicates the number of occurrences. The metacharacter '.' typically indicates any character in an expression, so the metacharacter \ is used as an escape character and makes the following character in the pattern a literal. This result set shows the telephone numbers in the new format. See Oracle Database SQL Language Reference for information about the REGEXP_REPLACE condition.

Example 2-12 Replacing a Data Pattern

SELECT first_name "First", last_name "Last",
phone_number "Old Number",
REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "New Number"
FROM employees
WHERE department_id = 90;

The results of the query appear.

First                Last                   Old Number           New Number
-------------------- ---------------------- -------------------- --------------
Steven               King                   515.123.4567         (515) 123-4567
Neena                Kochhar                515.123.4568         (515) 123-4568
Lex                  De Haan                515.123.4569         (515) 123-4569
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
3 rows selected

Example 2-13 shows how you can use the REGEXPR_SUBSTR function to find the first substring that matches a pattern. Note that the metacharacter'+' indicates multiple occurrences of the pattern. This result set extracts numerals and dashes from the street_address column. See Oracle Database SQL Language Reference for information about the REGEXP_SUBSTR expression.

Example 2-13 Returning a Substring

SELECT street_address, REGEXP_SUBSTR(street_address, 
'[[:digit:]-]+', 1, 1) "Street Numbers"
FROM locations;

The results of the query appear.

STREET_ADDRESS                           Street Numbers
---------------------------------------- ----------------------
1297 Via Cola di Rie                     1297
93091 Calle della Testa                  93091
2017 Shinjuku-ku                         2017
...
23 rows selected

The REGEXPR_INSTR function enables you to find the position of the first substring that matches a pattern. In Example 2-14, you use REGEXPR_INSTR to find a space character, ' '. Note that the metacharacter '+' indicates multiple occurrences of the pattern. This result set shows the position of the first space in each address. See Oracle Database SQL Language Reference for information about the REGEXP_INSTR expression.

Example 2-14 Returning a Location of a Substring

SELECT street_address, REGEXP_INSTR(street_address, '[ ]+', 1, 1) "Position"
FROM locations;

The results of the query appear.

STREET_ADDRESS                           Position               
---------------------------------------- ---------------------- 
1297 Via Cola di Rie                     5                      
93091 Calle della Testa                  6                      
2017 Shinjuku-ku                         5                      
...
23 rows selected

The function REGEXPR_COUNT determines the number of times the specified character pattern repeats in a string. In Example 2-15, REGEXPR_COUNT returns the number of times the space character occurs in the street_address column of the table locations. See Oracle Database SQL Language Reference for information about the REGEXP_COUNT expression.

Example 2-15 Returning the Number of Occurrences of a Substring

SELECT street_address, REGEXP_COUNT(street_address, ' ', 1) "Number of Spaces"
FROM locations;

The results of the query appear.

STREET_ADDRESS                           Number of Spaces       
---------------------------------------- ---------------------- 
1297 Via Cola di Rie                     4                      
93091 Calle della Testa                  3                      
2017 Shinjuku-ku                         1                      
...
23 rows selected

This result set shows the number of spaces in each address.

See Also:

Sorting Data

In SQL, the ORDER BY clause is used to identify which columns are used to sort the resulting data. The sort criteria does not have to be included in the result set, and can include expressions, column names, arithmetic operations, user-defined functions, and so on.

Example 2-16 shows an ORDER BY clause that returns the result set sorted in order of last_name, in ascending order.

Example 2-16 Use Quoted Alias Columns

SELECT first_name "First", last_name "Last", hire_date "Date Started" 
FROM employees
ORDER BY last_name;

The results of the query appear.

First                Last                      Date Started              
-------------------- ------------------------- ------------------------- 
Ellen                Abel                      11-MAY-96                 
Sundar               Ande                      24-MAR-00                 
Mozhe                Atkinson                  30-OCT-97                 
...              
107 rows selected

Using Built-In and Aggregate Functions

SQL arithmetic operators and other build-in functions allow you to perform calculations directly on data stored in the tables.

See Also:

Using Arithmetic Operators

Oracle Database SQL supports the basic arithmetic operators, such as the plus sign (+) for addition, the minus sign (-) for subtraction, the asterisk (*) for multiplication, and the forward slash (/) for division. These are evaluated according to standard arithmetic rules of evaluation order.

In Example 2-17, the result set show the salary earned by employees who are eligible for commission earnings, in order of the hire date.

Example 2-17 Evaluating an Arithmetic Expression

SELECT first_name "First", last_name "Last", salary * 12 "Annual Compensation"
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY hire_date;

The results of the query appear.

First                Last                      Annual Compensation    
-------------------- ------------------------- ---------------------- 
Janette              King                      120000                 
Patrick              Sully                     114000                 
Ellen                Abel                      132000                 
...
35 rows selected

Using Numeric Functions

Oracle Database has many numeric functions for manipulating numeric values, such as ROUND for rounding to a specified decimal or TRUNC for truncating to a specified decimal. These functions all return a single value for each row that is evaluated.

Example 2-18 shows how to determine daily pay, rounded off to the nearest cent.

Example 2-18 Rounding off Numeric Data

SELECT first_name "First", last_name "Last", 
ROUND(salary/30, 2) "Daily Compensation"
FROM employees;

The results of the query appear.

First                Last                      Daily Compensation     
-------------------- ------------------------- ---------------------- 
Steven               King                      800                    
Neena                Kochhar                   566.67                 
Lex                  De Haan                   566.67                 
...
107 rows selected

Example 2-19 shows how to determine daily pay that is truncated at the nearest dollar. Note that the TRUNC function does not round-up the value.

Example 2-19 Truncating Numeric Data

SELECT first_name "First", last_name "Last", 
  TRUNC(salary/30, 0) "Daily Compensation"
FROM employees;

The results of the query appear.

First                Last                      Daily Compensation     
-------------------- ------------------------- ---------------------- 
Steven               King                      800                    
Neena                Kochhar                   566                    
Lex                  De Haan                   566                    
...
107 rows selected

See Also:

Using Character Functions

Oracle Database includes an extensive list of character functions for customizing character values.

These functions can change the case of a character expression to UPPER or LOWER, remove blanks, concatenate strings, and extract or remove substrings.

Example 2-20 demonstrates how to change the character case of your expression. The result set shows the results of UPPER, LOWER, and INITCAP functions.

Example 2-20 Changing the Case of Character Data

SELECT UPPER(first_name) "First upper", 
LOWER(last_name) "Last lower",
INITCAP(email) "E-Mail"
FROM employees;

The results of the query appear.

First upper          Last lower                E-Mail                    
-------------------- ------------------------- ------------------------- 
STEVEN               king                      Sking                     
NEENA                kochhar                   Nkochhar                  
LEX                  de haan                   Ldehaan                   

To produce information from two separate columns or expressions in the same column of the report, you can concatenate the separate results by using the concatenation operator, ||. Note also that in Example 2-21, you are performing a 4-way join operation. This result set shows that the simple concatenation function in column Name listed the last_name value immediately after the first_name value, while the nested concatenation function in column Location separated the city and country_name values.

Example 2-21 Concatenating Character Data

SELECT e.first_name || ' ' || e.last_name "Name",
  l.city || ', ' || c.country_name "Location"
FROM employees e, departments d, locations l, countries c
WHERE e.department_id=d.department_id AND
  d.location_id=l.location_id AND
  l.country_id=c.country_id
ORDER BY last_name;

The results of the query appear.

Name                           Location 
-----------------------------  -------------------------------------------- 
Ellen Abel                     Oxford, United Kingdom 
Sundar Ande                    Oxford, United Kingdom
Mozhe Atkinson                 South San Francisco, United States of America
...
106 rows selected

You can use RTRIM and LTRIM functions to remove characters (by default, spaces) from the beginning or the end of character data, respectively. The TRIM function removes both leading and following characters. In Example 2-22, you use a type conversion function, TO_CHAR. This result set shows that all employees without a leading M in their last_name values, the MAN missing from the end of the job_id values, and the leading 0 is missing from the date_hired values.

Example 2-22 Trimming Character Data

SELECT LTRIM(last_name, 'M') "Last Name", 
RTRIM(job_id, 'MAN') "Job", 
TO_CHAR(TRIM(LEADING 0 FROM hire_date)) "Hired"
FROM employees
WHERE department_id=50;

The results of the query appear.

Last Name                 Job        Hired     
------------------------- ---------- --------- 
Weiss                     ST_        18-JUL-96 
Fripp                     ST_        10-APR-97 
Kaufling                  ST_        1-MAY-95  
Vollman                   ST_        10-OCT-97 
ourgos                    ST_        16-NOV-99 
...
ikkilineni                ST_CLERK   28-SEP-98 
Landry                    ST_CLERK   14-JAN-99 
arkle                     ST_CLERK   8-MAR-00  
...
arlow                     ST_CLERK   16-FEB-97 
...
allin                     ST_CLERK   14-JUN-96 
...
Philtanker                ST_CLERK   6-FEB-00  
...
Patel                     ST_CLERK   6-APR-98  
...
atos                      ST_CLERK   15-MAR-98 
Vargas                    ST_CLERK   9-JUL-98  
Taylor                    SH_CLERK   24-JAN-98 
...
Geoni                     SH_CLERK   3-FEB-00  
...
Cabrio                    SH_CLERK   7-FEB-99  
...
Bell                      SH_CLERK   4-FEB-96  
Everett                   SH_CLERK   3-MAR-97  
cCain                     SH_CLERK   1-JUL-98  
...
45 rows selected

You can use RPAD to add characters (by default, spaces) to the end of character data. The LPAD function adds characters to the beginning of character data.

In Example 2-23, the result set shows a simple histogram of relative salary values.

Example 2-23 Padding Character Data

SELECT first_name || ' ' || last_name "Name",
RPAD(' ', salary/1000, '$') "Salary"
FROM employees;

The results of the query appear.

Name                                   Salary
-------------------------------------- ----------------
Steven King                            $$$$$$$$$$$$$$$$$$$$$$$
Neena Kochhar                          $$$$$$$$$$$$$$$$
Lex De Haan                            $$$$$$$$$$$$$$$$
...
107 rows selected

You can use SUBSTR to extract only a substring of data, specified by the starting character position and the total number of characters.

In Example 2-24, you use SUBSTR to abbreviate the first_name value to an initial, and strip the area code from the phone_number value.

Example 2-24 Extracting a Substring of Character Data

SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name",
  SUBSTR(phone_number, 5, 8) "Phone"
FROM employees;

The results of the query appear.

Name                         Phone
---------------------------- --------
S. King                      123.4567
N. Kochhar                   123.4568
L. De Haan                   123.4569
...
107 rows selected

This result set shows the first_name values abbreviated to an initial, and the phone_number values without the leading area code component.

You can use REPLACE, in combination with SUBSTR, to replace a specific substring if you know its relative location in the character data.

In Example 2-25, you use SUBSTR in the WHERE clause to replace the abbreviation for a job code.

Example 2-25 Replacing Substring of Character Data

SELECT SUBSTR(first_name, 1, 1) || '. ' || last_name "Name",
REPLACE(job_id, 'SH', 'SHIPPING') "Job"
FROM employees
WHERE SUBSTR(job_id, 1, 2) = 'SH';

The results of the query appear.

Name                     Job
-----------------------  ------------------------
W. Taylor                SHIPPING CLERK
J. Fleaur                        SHIPPING_CLERK                                                                   
M. Sullivan              SHIPPING_CLERK     
...
20 rows selected

This result set shows the first_name values abbreviated to an initial, and the job_id values were replaced.

See Also:

Using Datetime Functions

Oracle Database has data functions for manipulating and calculating date and time data, including interval functions.

In Example 2-26, you will determine the duration of employment in a particular job for those employees who have switched to a different position. Note that the names are not unique because employees may hold more than two different positions over time. See Oracle Database SQL Language Reference for information about the MONTHS_BETWEEN function.

Example 2-26 Determining the Number of Months Between Dates

SELECT e.first_name || ' ' || e.last_name "Name", 
TRUNC(MONTHS_BETWEEN(j.end_date, j.start_date)) "Months Worked"
FROM employees e, job_history j
WHERE e.employee_id = j.employee_id
ORDER BY "Months Worked";

The results of the query appear.

Name                                           Months Worked
---------------------------------------------- -------------
Jonathon Taylor                                            9
Payam Kaufling                                            11
Jonathon Taylor                                           11
...
10 rows selected

You will notice that this result shows that of the employees who left the company, the shortest and the longest stays were 9 and 69 months, respectively.

In Example 2-27, you will use the EXTRACT function to determine if employees are in their sixth calendar year of continuous employment. The EXTRACT function can also be used in combination with MONTH, DATE, and so on.

Note that the SYSDATE function gives the current date of the system clock. See Oracle Database SQL Language Reference for information about the SYSDATE function.

Example 2-27 Determining the Years Between Dates

SELECT first_name || ' ' || last_name "Name", 
(EXTRACT(YEAR from SYSDATE) - EXTRACT(YEAR FROM hire_date)) "Years Employed"
FROM employees;

The results of the query appear.

Name                                           Years Employed
---------------------------------------------- --------------
Steven King                                                20
Neena Kochhar                                              18
Lex De Haan                                                14
...
107 rows selected

You will notice that this result shows that employee 'Steven King' has worked for the company the longest, 20 years.

In Example 2-28, you will use the last_day function to determine the last day of the month in which an employee was hired.

Example 2-28 Getting the Last Day of the Month for a Specified date

SELECT first_name || ' ' || last_name "Name", hire_date "Date Started",
  LAST_DAY(hire_date) "End of Month"
FROM employees;

The results of the query appear.

Name                           Date Started       End of Month              
------------------------------ ------------------ ------------------------- 
Steven King                    17-JUN-87          30-JUN-87                 
Neena Kochhar                  21-SEP-89          30-SEP-89                 
Lex De Haan                    13-JAN-93          31-JAN-93                 
...
107 rows selected

You will notice that this result shows the correct end of the month for each hire_date value.

In Example 2-29, you will use the ADD_MONTHS function to add 6 months to the date on which an employee was hired. See Oracle Database SQL Language Reference for information about the ADD_MONTH function.

Example 2-29 Adding Months to a Date

SELECT first_name || ' ' || last_name "Name", hire_date "Date Started",
  ADD_MONTHS(hire_date, 6) "New Date"
FROM employees;

The results of the query appear.

Name                     Date Started              New Date                  
------------------------ ------------------------- ------------------------- 
Steven King              17-JUN-87                 17-DEC-87                 
Neena Kochhar            21-SEP-89                 21-MAR-90                 
Lex De Haan              13-JAN-93                 13-JUL-93                 
...
107 rows selected

In Example 2-30, you will use the SYSTIMESTAMP function determine the current system time and date. SYSTIMESTAMP is similar to SYSDATE, but also contains time of day information, including the time zone and fractional seconds. See Oracle Database SQL Language Reference for information about the SYSTIMESTAMP function.

Note that instead of an hr schema table, you are using the table DUAL, a small table in the data dictionary that you can reference to guarantee a known result. See Oracle Database Concepts for information about the DUAL table and Oracle Database SQL Language Reference for information about selecting from the DUAL table.

Example 2-30 Getting the System Date and Time

SELECT EXTRACT(HOUR FROM SYSTIMESTAMP) || ':' ||
EXTRACT(MINUTE FROM SYSTIMESTAMP) || ':' ||
ROUND(EXTRACT(SECOND FROM SYSTIMESTAMP), 0) || ', ' ||
EXTRACT(MONTH FROM SYSTIMESTAMP) || '/' ||
EXTRACT(DAY FROM SYSTIMESTAMP) || '/' ||
EXTRACT(YEAR FROM SYSTIMESTAMP) "System Time and Date"
FROM DUAL;

The results of the query appear.

System Time and Date
------------------------------------------------------------
18:25:56, 4/5/2007

Your result would change, depending on the current SYSTIMESTAMP value.

See Also:

Using Data Type Conversion Functions

Oracle Database has data functions for converting between different data types. This is particularly useful when you need to display data of different data types in the same column.

There are three general types of conversion functions: for characters (TO_CHAR), for numbers (TO_NUMBER), for dates (TO_DATE) and for timestamps (TO_TIMESTAMP).

You will use the TO_CHAR function to convert a date into a desired format. Example 2-31 converts the HIRE_DATE values to a 'FMMonth DD YYYY' format; note that the FM option removes all leading or trailing blanks from the month name. Other options you could use include 'DD-MON-YYYY AD', 'MM-DD-YYYY HH24:MI:SS', and so on.

Example 2-31 Using TO_CHAR to Convert a Date Using a Format Template

SELECT first_name || ' ' || last_name "Name", 
  TO_CHAR(hire_date, 'FMMonth DD YYYY') "Date Started"
FROM employees;

The results of the query appear.

Name                                           Date Started
---------------------------------------------- -----------------
Steven King                                    June 17 1987
Neena Kochhar                                  September 21 1989
Lex De Haan                                    January 13 1993   
...
107 rows selected

Your result set lists all the hire_date values in the new format.

Example 2-32 shows how you can use two standard format tags, Short Date (DS) and Long Date (DL), to format your date.

Example 2-32 Using TO_CHAR to Convert a Date Using a Standard Format

SELECT first_name || ' ' || last_name "Name", 
  TO_CHAR(hire_date, 'DS') "Short Date",
  TO_CHAR(hire_date, 'DL') "Long Date"
FROM employees;

The results of the query appear.

Name                         Short Date  Long Date
---------------------------  ----------  -------------------------
Steven King                  6/17/1987   Wednesday, June 17, 1987
Neera Kochhar                9/21/19889  Thursday, September 21, 1989
Lex De Haen                  1/13/1993   Wednesday, January 13, 1993
...
107 rows selected 

You can use the TO_CHAR function to convert a number to a desired currency format. Example 2-33 will convert the salary values to a '$99,999.99' format. See Oracle Database SQL Language Reference for TO_CHAR.

Example 2-33 Using TO_CHAR to Convert a Number to a Currency Template

SELECT first_name || ' ' || last_name "Name", 
  TO_CHAR(salary, '$99,999.99') "Salary"
FROM employees;

The results of the query appear.

Name                                           Salary
---------------------------------------------- -----------
Steven King                                     $24,000.00
Neena Kochhar                                   $17,000.00
Lex De Haan                                     $17,000.00 
...
107 rows selected

Example 2-34 shows how you can use the TO_NUMBER function to convert a character into a number that you can subsequently use in calculations. See Oracle Database SQL Language Reference for TO_NUMBER.

Example 2-34 Using TO_NUMBER to Convert a Character to a Number

SELECT first_name || ' ' || last_name "Name",
  TO_NUMBER('300') + salary  "Proposed Salary"
FROM employees
WHERE SUBSTR(job_id, 4, 5) = 'CLERK';

The results of the query appear.

Name                                           Proposed Salary
---------------------------------------------- ---------------
Alexander Khoo                                            3400
Shelli Baida                                              3200
Sigal Tobias                                              3100
...
45 rows selected 

Your result set lists all the proposed salary values for the selected subset of employees.

You can use the TO_DATE function to convert a character data with a specified format mode into a date. In Example 2-35, you will use the format model 'Month dd, YYYY, HH:MI A.M.'; other formats include 'DD-MON-RR', 'FF-Mon-YY HH24:MI:SI', and so on.

Example 2-35 Using TO_DATE to Convert a Character Data to a Date

SELECT TO_DATE('January 5, 2007, 8:43 A.M.', 
  'Month dd, YYYY, HH:MI A.M.') "Date"
FROM DUAL;

The results of the query appear.

Date
---------
05-JAN-07

Your result converts the character data, interpreted by the specified format string, into a DATE type.

Example 2-36 shows how you can use the TO_TIMESTAMP method with format models such as 'DD-Mon-RR HH24:MI:SS.FF'. See Oracle Database SQL Language Reference for TO_DATE.

Example 2-36 Using TO_TIMESTAMP to Convert Character Data to a Timestamp

SELECT TO_TIMESTAMP('May 5, 2007, 8:43 A.M.', 
  'Month dd, YYYY, HH:MI A.M.') "Timestamp"
FROM DUAL;

The results of the query appear.

Timestamp
---------------------------------------------
05-MAY-07 08.43.00.000000000 AM

Your result converts the character data, interpreted by the specified format string, into a TIMESTAMP type.

See Also:

Using Aggregate Functions

Aggregate functions operate on groups of rows, or an entire table or view. By their nature, these functions provide statistical results for sets, and include average (AVG), count (COUNT), maximum (MAX), minimum (MIN), standard deviation (STDEV), sum (SUM), and so on.

Aggregate functions are especially powerful when used it in combination with the GROUP BY clause, where a query returns a list that is grouped by one or more columns, with a distinct result for each of the groupings.

You can also use the HAVING clause, which specifies that a query should only return rows where aggregate values meet the specified conditions.

Example 2-37 shows how you can use the COUNT function and the GROUP BY clause to determine how many people report to each manager. Note that the wildcard, *, is used to denote the counting of an entire record.

Example 2-37 Counting the Number of Rows That Satisfy an Expression

SELECT manager_id "Manager",
  COUNT(*) "Number of Reports"
FROM employees
GROUP BY manager_id;

The results of the query appear.

  Manager Number of Reports
--------- -----------------
                          1
      100                14
      123                 8
...
19 rows selected

Your result shows how many people report to each manager. Note that one person does not report to anyone; if you examine the data, you will see that Steven King does not have a supervisor.

Example 2-38 shows how you can also use the COUNT function with a DISTINCT option to determine how many distinct values and are in a data set. Here, you will count the number of departments that have employees.

Example 2-38 Counting a Number of Distinct Values in a Set

SELECT COUNT(DISTINCT department_id) "Number of Departments"
FROM employees;

The results of the query appear.

Number of Departments
---------------------
11

Your result shows that 11 departments have employees. If you look at the departments table, you will note that it lists 27 departments.

You can use basic statistical functions, such as MIN, MAX, MEDIAN, AVG, and so on, to determine the range of salaries across the set. In Example 2-39, you will examine salaries grouped by job_id, but a similar query could be used to examine salaries across departments, locations, and so on.

Example 2-39 Determining Statistical Information

SELECT job_id "Job", COUNT(*) "#", MIN(salary) "Minimum", 
  ROUND(AVG(salary), 0) "Average",  
  MEDIAN(salary) "Median", MAX(salary) "Maximum", 
  ROUND(STDDEV(salary)) "Std Dev"
FROM employees
GROUP BY job_id
ORDER BY job_id;

The results of the query appear.

Job                 #    Minimum    Average     Median    Maximum    Std Dev
---------- ---------- ---------- ---------- ---------- ---------- ----------
AC_ACCOUNT          1       8300       8300       8300       8300          0
AC_MGR              1      12000      12000      12000      12000          0
AD_ASST             1       4400       4400       4400       4400          0
AD_PRES             1      24000      24000      24000      24000          0
AD_VP               2      17000      17000      17000      17000          0
FI_ACCOUNT          5       6900       7920       7800       9000        766
FI_MGR              1      12000      12000      12000      12000          0
HR_REP              1       6500       6500       6500       6500          0
IT_PROG             5       4200       5760       4800       9000       1926
MK_MAN              1      13000      13000      13000      13000          0
MK_REP              1       6000       6000       6000       6000          0
... 
19 rows selected

Your result shows the statistics for 19 different jobs.

If you use the HAVING clause, you can limit your result set to only the kind of values that interest you. In Example 2-40, you see the salary budget for departments where the sum of salaries exceeds $1,000,000 annually.

Example 2-40 Limiting the Results Using the HAVING Clause

SELECT Department_id "Department", SUM(salary*12) "All Salaries"
FROM employees
HAVING SUM(salary * 12) >= 1000000
GROUP BY department_id;

The results of the query appear.

Department All Salaries
---------- ------------
        50      1876800
        80      3654000

Your result shows that only two departments have salary budgets in excess of $1,000,000.

You can use the RANK function to determine the relative ordered rank of a number, and use the PERCENT_RANK function to determine the percentile position. In Example 2-41, you determine these values for a salary of $3,000 over the subset of all employees who have a 'CLERK' designation in the job_id.

You can also examine groups using the WITHIN GROUP function.

Example 2-41 Determining RANK and PERCENT_RANK

SELECT RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank", 
  ROUND(100 * (PERCENT_RANK(3000) 
  WITHIN GROUP (ORDER BY salary DESC)), 0) "Percentile"
FROM employees
WHERE job_id LIKE '%CLERK';

The results of the query appear.

      Rank Percentile
---------- ----------
        20         42

Your result shows that a salary of $3,000 is the 20th highest, and that it is in the 42nd percentile among all employees who have a 'CLERK' designation.

The DENSE_RANK function works much like the RANK function, but the identical values receive the same rank, and there are no gaps in the ranking. In Example 2-42, you will determine the DENSE_RANK of $3,000 over the subset of all employees who have a 'CLERK' designation in the job_id.

Example 2-42 Determining DENSE_RANK:

SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY salary DESC) "Rank"
FROM employees
WHERE job_id LIKE '%CLERK';

The results of the query appear.

      Rank
----------
        12

Your result shows that a salary of $3,000 is the 12th highest using the DESNE_RANK function. Contrast it with the 20th rank obtained in the previous example that used the RANK function.

See Also:

Using NULL Value Functions

To work with NULL values, Oracle Database supplies two functions. NVL substitutes a specified value if a NULL is encountered, and NVL2 specifies two possible expressions that could be evaluated (one if none of its component variables is NULL, and another one if at least one variable is NULL).

In Example 2-43, you will use the NVL and NVL2 functions to determine what the whole annual compensation would be for each employee, if they were involved in a $300,000 sale. Note that the commission rate is a multiplier on sales volume, not on base salary. Note also that the WHERE clause limits the result set to managers.

Example 2-43 Using the NVL and NVL2 Functions

SELECT first_name || ' ' || last_name "Name",
  NVL((commission_pct * 100), 0) "Comm Rate",
  NVL2(commission_pct, 
    ROUND(salary * 12 + commission_pct * 300000, 2),
    salary * 12) "With $300K Sales"
FROM employees
WHERE job_id LIKE '%_M%' AND department_id = 80;

The results of the query appear.

Name                               Comm Rate              With $300K Sales 
---------------------------------- ---------------------- ----------------- 
John Russell                       40                     288000   
Karen Partners                     30                     252000   
Alberto Errazuriz                  30                     234000   
Gerald Cambrault                   30                     222000   
Eleni Zlotkey                      20                     186000   
 
5 rows selected

Your result shows that in the Comm Rate column, the NVL function replaces a NULL value by 0. In the With $300K Sales column, the NVL2 function generates values from two different expressions, depending on the value of the COMMISSION_PCT value.

Using Conditional Functions

Oracle Database provides two functions that can return values based on multiple condition values.

The CASE function is equivalent to nestled IF ... THEN ... ELSE statements, as it compares a value, an expression, or a search condition, and returns a result when it finds a match.

In Example 2-44, you will use the CASE structure to view prospective salary increases that would be awarded based on the length of service with the company.

Example 2-44 Using the CASE Function

SELECT first_name || ' ' || last_name "Name",
hire_date "Date Started", salary "Current Pay",
CASE 
  WHEN hire_date < TO_DATE('01-Jan-90') THEN TRUNC(salary*1.15, 0)
  WHEN hire_date < TO_DATE('01-Jan-95') THEN TRUNC(salary*1.10, 0)
  WHEN hire_date < TO_DATE('01-Jan-00') THEN TRUNC(salary*1.05, 0)
  ELSE salary END "Proposed Salary"
FROM employees;

The results of the query appear.

Name                        Date Started      Current Pay      Proposed Salary
--------------------------  ----------------  ---------------  -------------------
Steven King                 17-JUN-87         24000            27600
Neena Kochhar               21-SEP-89         17000            19550
Lex De Haen                 13-JAN-93         17000            18700
...
107 rows selected

Your result shows that the values in the Proposed Salary column have been adjusted based on the values of Date Started.

The DECODE function compares a value or expression to search values, and returns a result when it finds a match. If a match is not found, then DECODE returns the default value, or NULL (if a default value is not specified).

In Example 2-45, you will use the DECODE function to assign possible salary increases based on the job_id value.

Example 2-45 Using the DECODE Function

SELECT first_name || ' ' || last_name "Name",
job_id "Job", salary "Current Pay",
DECODE(job_id, 
 'PU_CLERK', salary * 1.10,
 'SH_CLERK', salary * 1.15,
 'ST_CLERK', salary * 1.20,
             salary) "Proposed Salary"
FROM employees;

The results of the query appear.

Name                        Job               Current Pay      Proposed Salary
--------------------------  ----------------  ---------------  -------------------
...
Alexander Khoo              PU-CLERK          3100             3410
...
Julia Nayer                 ST_CLERK          3200             3840
...
Winston Taylor              SH_CLERK          3200             3680 
...
107 rows selected

Your result shows that the values in the 'Proposed Salary' column have been adjusted based on the job_id value.

See Also:

Adding, Changing, and Deleting Data

Adding, changing and deleting operations in the database are commonly called Data Manipulation Language (DML) statements:

  • An INSERT statement adds new rows to an existing table.

  • An UPDATE statement modifies the values of a set of existing table rows.

  • A DELETE statement removes existing rows from a table.

Because these statements change the data in your table, Oracle recommends that you use transaction management to group all dependent DML statements together.

Inserting Information

When you use the INSERT statement to add a row of data to a table, the data inserted must be valid for the data type and size of each column of the table.

The general syntax of the INSERT command looks like the following. Note that the list of values has to be in the same order as the columns of the table.

INSERT INTO table_name VALUES
(list_of_values_for_new_row);

In Example 2-46, you will use the INSERT function to add a new row to the employees table.

Example 2-46 Using the INSERT Statement When All Information Is Available

INSERT INTO employees VALUES 
  (10, 'George', 'Gordon', 'GGORDON', '650.506.2222', 
   '01-JAN-07', 'SA_REP', 9000, .1, 148, 80);

The results of the query appear.

1 row created. 

Your result shows that the new row has been successfully added to the employees table.

When all of the information is not available at the time a new record is added to the database, Example 2-47 shows how you can insert values only into the specified known columns of the table and then set the remaining columns to NULL.

Note that if the columns that are set to NULL are specified with a NOT NULL constraint, this would generate an error.

Example 2-47 Using the INSERT Statement When Some Information Is Not Available

INSERT INTO employees VALUES 
  (20, 'John', 'Keats', 'JKEATS', '650.506.3333', 
   '01-JAN-07', 'SA_REP', NULL, .1, 148, 80);

The results of the query appear.

1 row created. 

Your result shows that the new row has been successfully added to the employees table.

See Also:

Updating Information

When you use the UPDATE statement to update data in a row of a table, the new data must be valid for the data type and size of each column of the table.

The general syntax of the UPDATE command looks like the following. Note that the columns that are altered must be identified, and the matching conditions must be met.

UPDATE table_name
SET column_name = value;
WHERE condition;

To update information in a row that is missing data, the missing data column should be specified. In Example 2-48, you will update the salary column for a previously inserted record.

Example 2-48 Using the UPDATE Statement to Add Missing Data

UPDATE employees
SET salary = 8500
WHERE last_name = 'Keats';

The results of the query appear.

1 row updated. 

Your result shows that the matching row has been updated.

Example 2-49 shows how you can use the UPDATE statement to update multiple rows.

Example 2-49 Using the UPDATE Statement to Change Data

UPDATE employees
SET commission_pct=commission_pct + 0.05
WHERE department_id = 80;

The results of the query appear.

36 rows updated. 

Your result shows that the specified rows are updated.

See Also:

Deleting Information

Using the DELETE statement, you can delete specific rows in a table. If you want to delete all the rows in the table, the empty table still exists. If you want to remove the entire table from the database, use the DROP TABLE statement.

Note that if you accidentally delete rows, you can restore the rows with the ROLLBACK statement.

Example 2-50 shows how you can use the DELETE statement to remove the data you added previously.

Note the use of the WHERE clause; without it, all the rows are deleted.

Example 2-50 Using the DELETE Statement

DELETE FROM employees
WHERE hire_date = '1-Jan-2007';

The results of the query appear.

2 rows deleted. 

Your result shows that the specified rows are deleted.

See Also:

Controlling Transactions

Many applications model business processes that require that several different operations be performed together, or not at all. For example, if a manager left the company, a row would be inserted into the job_history table to show when that person left, and all the employees that report to that manager must be re-assigned within the employees table. This sequence of operations must be treated as a single unit, or a transaction.

The following transaction control statements manage the changes made by DML statements and group them into transactions.

  • The COMMIT statement ends the current transaction and makes all changes performed in the transaction permanent. COMMIT also erases all savepoints in the transaction, and releases transaction locks.

  • The ROLLBACK statement reverses the work done in the current transaction; it causes all data changes since the last COMMIT or ROLLBACK to be discarded. The state of the data is then "rolled back" to the state it had prior to the requested changes.

  • The SAVEPOINT statement identifies a point in a transaction to which you can later roll back.

Oracle recommends that you explicitly end transactions using either a COMMIT or a ROLLBACK statement. If you do not explicitly commit the transaction and the program terminates abnormally, then Oracle Database automatically rolls back the last uncommitted transaction.

Committing Transaction Changes

An explicit COMMIT statement ends your transaction, and makes all the changes in the database permanent. Until you commit a transaction, you can see all of the changes made by you to the database, but these changes are not final or visible to other users of the database instance. Once you commit a transaction, all changes become visible to other users and their statements that execute after your transaction.

You can undo any changes made prior to an explicit COMMIT by a ROLLBACK statement.

Example 2-51 shows how to use the COMMIT statement after adding a new row to the regions table.

Example 2-51 Using the COMMIT Statement

INSERT INTO regions VALUES (5, 'Africa'); 
COMMIT;

The results of the query and COMMIT statement appear.

Commit complete.

If you manually check the contents of the regions table, you will see that it now has the new row.

Description of transaction_2.gif follows
Description of the illustration transaction_2.gif

Rolling Back Transaction Changes

The ROLLBACK statement rolls back all of the transactions you have made since the last COMMIT statement. If you do not have a preceding COMMIT statement in your program, it rolls back all operations.

Example 2-52 and Example 2-53 show how to use the ROLLBACK statement to undo changes to the regions table.

Example 2-52 Changing the REGIONS Table

UPDATE regions
SET region_name = 'Just Middle East'
WHERE region_name = 'Middle East and Africa';

The results of the query appear.

1 row updated.

Manually check the contents of the regions table.

You will see that it now has the updated region_name value. Description of transaction_4.gif follows
Description of the illustration transaction_4.gif

Example 2-53 Performing a ROLLBACK on the Change to the REGIONS Table

ROLLBACK;

Manually check the contents of the regions table by clicking the Refresh icon. You will see that the region_name value is changed back to the original value.

Description of transaction_2.gif follows
Description of the illustration transaction_2.gif

Setting Savepoints

You can use the SAVEPOINT statement to identify a point in a transaction to which you can later roll back. Because you can use as many savepoints as your application requires, you can implement greater transaction control in your application.

In Example 2-54, you will use the ROLLBACK statement after adding a new row to the regions table.

Example 2-54 Using the SAVEPOINT Statement

UPDATE regions
  SET region_name = 'Middle East'
  WHERE region_name = 'Middle East and Africa';
SAVEPOINT reg_rename;

UPDATE countries
  SET region_id = 5
  WHERE country_id = 'ZM';
SAVEPOINT zambia;

UPDATE countries
  SET region_id = 5
  WHERE country_id = 'NG';
SAVEPOINT nigeria;
 
UPDATE countries
  SET region_id = 5
  WHERE country_id = 'ZW';
SAVEPOINT zimbabwe;

UPDATE countries
  SET region_id = 5
  WHERE country_id = 'EG';
SAVEPOINT egypt;

ROLLBACK TO SAVEPOINT nigeria;

COMMIT;

The results for each UPDATE and SAVEPOINT statement follow.

1 row updated.
Savepoint created. 
Description of transaction_7.gif follows
Description of the illustration transaction_7.gif

Manually check the contents of the regions table. You may need to click the Refresh icon. You will see that it now has the updated region_name value. Description of transaction_8.gif follows
Description of the illustration transaction_8.gif

Next, manually check the contents of the countries table. You may need to click the Refresh icon. You will see that it now has the updated region_name values for 'Zambia' and 'Nigeria', but not for 'Zimbabwe' and 'Egypt'. Description of transaction_9.gif follows
Description of the illustration transaction_9.gif

You can see that the change in data was reversed by the ROLLBACK to the savepoint nigeria.