6/20/2001 -- I guess I had to deal with it sooner or later: Oracle has just announced the release of their most recent database incarnation, Oracle 9i. For those of you who are members of the Oracle Technology Network (http://otn.oracle.com) and running Oracle on Sun Solaris, HP/UX or Linux, you can now download this latest version from the OTN Web site.

The questions I usually get asked about any new release of software tend to be along the lines of "Is it worth it to upgrade?" or "What are some of the new features in the product?" and so on. To answer these at one time would take more space than I'm allowed to use for this column, but these questions need to be answered. So this column is the first in a series that will look at all the new features of 9i and how you can get the most out of them

Plus ça Change, plus C'est la Même Chose
Oracle has made significant strides in bringing their variant of the SQL language up to the standards accepted by the industry. This includes many of the features that the ANSI SQL99 standard has suggested that vendors should implement. Some of these include support for the ANSI standard join syntax, use of the DEFAULT keyword to assign default values explicitly to columns of a table when an INSERT or UPDATE takes place, and many others. However, being true to form and introducing innovations that benefit its users, Oracle has also extended the SQL language to include functionality beyond the standard. After all, as any RDBMS vendor will tell you, the standard is only a guideline and things can always be done better.

The MERGE Statement
One SQL extension that is welcome is the new MERGE statement. The idea behind this statement is simple: Check to see if a row already exists in a table based upon the rows in another table and, if it does, UPDATE the existing row. If not, INSERT a new row into the table.

An example might help here:

MERGE INTO RetiredEmployees AS Retired
        USING Employees AS Emps
        ON (Retired.EmpId = Emps.EmpID) 
WHEN MATCHED THEN
        UPDATE SET EffectiveDate=Emps.TerminationDate
WHEN NOT MATCHED THEN
        INSERT VALUES (Emps.EmpID, Emps.LastName, 
        Emps.FirstName, Emps.Address, Emps.HomePhone, 
        Emps.HireDate, Emps.TerminationDate); 

In this example, Oracle will join the RetiredEmployees table with the Employees table. If a match is found (i.e., the EmpID exists in both tables), an UPDATE of the RetiredEmployees row will be made setting the effective retirement date to the value of the TerminationDate column in the Employees table. If no match is found, a row will be inserted into the RetiredEmployees table.

You probably don't want to experience this kind of MERGE statement in your company as it may indicate that your job is not that secure, but it's a good example of how the new MERGE statement is useful. To perform the same task prior to Oracle 9i, you either had to write a PL/SQL block to accomplish the task or use two SQL statements -- one to do the join and perform the UPDATE, and the other to perform the INSERT based upon the results of an outer join between the two tables. Using a single statement to accomplish both tasks at once certainly makes life easier.

You Want To INSERT Data into More Than One Table in a Single Statement?
In many data warehousing environments, when data is transferred from the source system it will need to be transformed or massaged to fit the structure of the data warehouse. This is normal since data warehousing design differs greatly from the design of an OLTP system. However, because of this inherent design difference, it is sometimes necessary to take data from a single OLTP source table, or a source data file derived from the OLTP system, and split it into several different tables in the data warehouse.

Prior to Oracle 9i this meant that you had to load the data into a temporary table in the database and then perform several INSERT statements into the appropriate tables, or create a complex control file for a SQL*Loader load. In Oracle 9i you now have the capability to INSERT data into several tables at the same time when using a subquery on the source table (i.e., you are using an INSERT … SELECT statement).

Oracle 9i has four variations of the multi-table INSERT:

• the unconditional INSERT
• the conditional ALL INSERT
• the conditional FIRST INSERT
• the pivoting INSERT

The unconditional INSERT will add data from the source subquery into all of the tables specified in the INSERT ALL statement, as in this example:

        INSERT ALL
        INTO SalaryHistory VALUES (empid, datehired, salary) 
        INTO ManagerHistory VALUES (empid, mgrid, SYSDATE) 

        SELECT EmployeeID EMPID, HireDate DATEHIRED, 
                Sal SALARY, ManagerID MGRID
        FROM Employees
        WHERE DeptID = 100; 

   

        INSERT ALL
        WHEN Salary > 30000
    INTO SalaryHistory VALUES (empid, datehired, salary) 
    WHEN MgrID = 200
        INTO ManagerHistory VALUES (empid, mgrid, SYSDATE) 

        SELECT EmployeeID EMPID, HireDate DATEHIRED, 
                  (Sal*12) SALARY, ManagerID MGRID
        FROM Employees
        WHERE DeptID = 100; 

In this example, rows will be inserted into the SalaryHistory table only when the value of the Salary is greater than 30000 (the annual salary of the employee is more than $30,000). Rows will not be inserted into the ManagerHistory table unless the manager ID is 200.

A conditional FIRST INSERT differs from the conditional ALL INSERT in that you may specify multiple WHEN conditions on the INSERT statement, but only the first WHEN condition that evaluates to true will result in an INSERT taking place. For example, if we changed the previous example to a conditional FIRST INSERT, if the value of Salary was greater than 30000, the INSERT into the SalaryHistory table would take place; if not, then if the MgrID of the employee was 200, an INSERT into the ManagerHistory table would take place; otherwise no INSERT would take place. The code would change to the following:

        INSERT FIRST 
        WHEN Salary > 30000
        INTO SalaryHistory VALUES (empid, datehired, salary) 
        WHEN MgrID = 200
        INTO ManagerHistory VALUES (empid, mgrid, SYSDATE) 
        ELSE 
        INTO EmpHistory VALUES (empid, mgrid, datehiread, salary) 

        SELECT EmployeeID EMPID, HireDate DATEHIRED, 
                  (Sal*12) SALARY, ManagerID MGRID
        FROM Employees
        WHERE DeptID = 100; 

As you can see, the ability to insert data into multiple tables from a single source is quite handy. However, in the above examples, the source and target tables existed in the same database. How about if you needed to import data from an external source and then insert its records into multiple tables, or take a single row in a table and split it into several INSERT statements that would insert several rows into the same table? To accomplish that you may need to load the data into temporary tables and then issue the above statements, right? Not necessarily.

Check back next time when I talk about pivoting INSERTS and how to create external tables in Oracle 9i.