Ordering your results - Oracle

In general, there is no guarantee that the result set generated by your query will be in any particular order. If you want your results to be sorted by one or more columns, you can add an ORDER BY clause after the WHERE clause. The following example sorts the results from the New England query by customer name:

  SELECT c.cust_nbr, c.name, r.name
  FROM customer c INNER JOIN region r
  ON r.region_id = c.region_id
  WHERE r.name = 'New England'
  ORDER BY c.name;

CUST_NBR         NAME              NAME
-------- ---------------------- -----------
       1   Cooper Industries    New England
       3   Ditech Corp.         New England
       2   Emblazon Corp.       New England
       4   Flowtech Inc.        New England
       5   Gentech Industries   New England

You may also designate the sort column(s) by their position in the SELECT clause. To sort the previous query by customer number, which is the first column in the SELECT clause, you could issue the following statement:

  SELECT c.cust_nbr, c.name, r.name
  FROM customer c INNER JOIN region r
  ON r.region_id = c.region_id
  WHERE r.name = 'New England'
  ORDER BY 1;

 CUST_NBR          NAME                 NAME
---------- ------------------------ -----------
         1   Cooper Industries      New England
         2   Emblazon Corp.         New England
         3   Ditech Corp.           New England
         4   Flowtech Inc.          New England
         5   Gentech Industries     New England

Specifying sort keys by position will certainly save you some typing, but it can often lead to errors if you later change the order of the columns in your SELECT clause.

Removing duplicates

In some cases, your result set may contain duplicate data. For example, if you are compiling a list of parts that were included in last month’s orders, the same part number would appear multiple times if more than one order included that part. If you want duplicates removed from your result set, you can include the DISTINCT keyword in your SELECT clause, as in:

  SELECT DISTINCT li.part_nbr
  FROM cust_order co INNER JOIN line_item li
  ON co.order_nbr = li.order_nbr
  WHERE co.order_dt >= TO_DATE('01-JUL-2001','DD-MON-YYYY')
    AND co.order_dt < TO_DATE('01-AUG-2001','DD-MON-YYYY');

This query returns the distinct set of parts ordered during July 2001. Without the DISTINCT keyword, the result set would contain one row for every line-item of every order, and the same part would appear multiple times if it was included in multiple orders. When deciding whether to include DISTINCT in your SELECT clause, keep in mind that finding and removing duplicates necessitates a sort operation, which can greatly increase the execution time of your query.

The INSERT Statement

The INSERT statement is the mechanism for loading data into your database. This section will introduce the traditional single-table INSERT statement, as well as the new multitable INSERT ALL statement introduced in Oracle 9i.

Single-table inserts

With the traditional INSERT statement, data can be inserted into only one table at a time, although the data being loaded into the table can be pulled from one or more additional tables. When inserting data into a table, you do not need to provide values for every column in the table; however, you need to be aware of the columns that require non-NULL* values and the ones that do not. Here’s the definition of the employee table:

  describe employee

          Name               Null?      Type
-------------------------- -------- ------------
  EMP_ID                   NOT NULL   NUMBER(5)
  FNAME                               VARCHAR2(20)
  LNAME                               VARCHAR2(20)
  DEPT_ID                  NOT NULL   NUMBER(5)
  MANAGER_EMP_ID                      NUMBER(5)
  SALARY                              NUMBER(5)
  HIRE_DATE                           DATE
  JOB_ID                              NUMBER(3)

The NOT NULL designation for the emp_id and dept_id columns indicates that values are required for these two columns. Therefore, you must be sure to provide values for at least these two columns in your INSERT statements, as demonstrated by the following:

  INSERT INTO employee (emp_id, dept_id)
  VALUES (101, 20);

Any inserts into employee may optionally include any or all of the remaining six columns, which are described as nullable since they may be left undefined. Thus, you could decide to add the employee’s last name to the previous statement:

  INSERT INTO employee (emp_id, lname, dept_id)
  VALUES (101, 'Smith', 20);

The VALUES clause must contain the same number of elements as the column list, and the data types must match the column definitions. In this example, emp_id and dept_id hold numeric values while lname holds character data, so the INSERT statement will execute without error. Oracle always tries to convert data from one type to another automatically, however, so the following statement will also run without error:

  INSERT INTO employee (emp_id, lname, dept_id)
  VALUES ('101', 'Smith', '20');

Sometimes, the data to be inserted needs to be retrieved from one or more tables. Since the SELECT statement generates a result set consisting of rows and columns of data, you can feed the result set from a SELECT statement directly into an INSERT statement, as in:

  INSERT INTO employee (emp_id, fname, lname, dept_id, hire_date)
  SELECT 101, 'Dave', 'Smith', d.dept_id, SYSDATE
  ROM department d
  WHERE d.name = 'ACCOUNTING';

In this example, the purpose of the SELECT statement is to retrieve the department ID for the Accounting department. The other four columns in the SELECT clause are either literals  (101,'Dave','Smith') or function calls (SYSDATE).

Multitable inserts

While inserting data into a single table is the norm, there are situations where data from a single source must be inserted either into multiple tables or into the same table multiple times. Such tasks would normally be handled programatically using PL/SQL, but Oracle9i introduced the concept of a multitable insert to allow complex data insertion via a single INSERT statement. For example, let’s say that one of Mary Turner’s customers wants to set up a recurring order on the last day of each month for the next six months. The following statement adds six rows to the cust_order table using a SELECT statement that returns exactly one row:

  INSERT ALL
  INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 
    order_dt, expected_ship_dt, status)
  VALUES (ord_nbr, cust_nbr, emp_id,
    ord_dt, ord_dt + 7, status)
  INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 
    order_dt, expected_ship_dt, status)
  VALUES (ord_nbr + 1, cust_nbr, emp_id,
    add_months(ord_dt, 1), add_months(ord_dt, 1) + 7, status)
  INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 
    order_dt, expected_ship_dt, status)
  VALUES (ord_nbr + 2, cust_nbr, emp_id,
    add_months(ord_dt, 2), add_months(ord_dt, 2) + 7, status)
  INTO cust_order (order_nbr, cust_nbr, sales_emp_id,  
    order_dt, expected_ship_dt, status)
  VALUES (ord_nbr + 3, cust_nbr, emp_id,
    add_months(ord_dt, 3), add_months(ord_dt, 3) + 7, status)
  INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 
    order_dt, expected_ship_dt, status)
  VALUES (ord_nbr + 4, cust_nbr, emp_id,
    add_months(ord_dt, 4), add_months(ord_dt, 4) + 7, status)
  INTO cust_order (order_nbr, cust_nbr, sales_emp_id, 
    order_dt, expected_ship_dt, status)
  VALUES (ord_nbr + 5, cust_nbr, emp_id,
    add_months(ord_dt, 5), add_months(ord_dt, 5) + 7, status)
  SELECT 99990 ord_nbr, c.cust_nbr cust_nbr, e.emp_id emp_id,
    last_day(SYSDATE) ord_dt, 'PENDING' status
  FROM customer c CROSS JOIN employee e
  WHERE e.fname = 'MARY' and e.lname = 'TURNER'
    and c.name = 'Gentech Industries';

The SELECT statement returns the data necessary for this month’s order, and the INSERT statement modifies the order_nbr, order_dt, and expected_ship_dt columns for the next five months’ orders. You are not obligated to insert all rows into the same table, nor must your SELECT statement return only one row, making the multitable insert statement quite flexible and powerful. The next example shows how data about a new salesperson can be entered into both the employee and salesperson tables:

  INSERT ALL
  INTO employee (emp_id, fname, lname, dept_id, hire_date)
  VALUES (eid, fnm, lnm, did, TRUNC(SYSDATE))
  INTO salesperson (salesperson_id, name, primary_region_id)
  VALUES (eid, fnm || ' ' || lnm, rid)
  SELECT 1001 eid, 'JAMES' fnm, 'GOULD' lnm,
    d.dept_id did, r.region_id rid
  FROM department d, region r
  WHERE d.name = 'SALES' and r.name = 'Southeast US';

So far, you have seen how multiple rows can be inserted into the same table and how the same rows can be inserted into multiple tables.The next, and final, example of multitable inserts demonstrates how a conditional clause can be used to direct each row of data generated by the SELECT statement into zero, one, or many tables:

  INSERT FIRST
    WHEN order_dt < TO_DATE('2001-01-01', 'YYYY-MM-DD') THEN
     INTO cust_order_2000 (order_nbr, cust_nbr, sales_emp_id,
       sale_price, order_dt)
     VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)
  WHEN order_dt < TO_DATE('2002-01-01', 'YYYY-MM-DD') THEN 
    INTO cust_order_2001 (order_nbr, cust_nbr,  
sales_emp_id,
       sale_price, order_dt)
    VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)
  WHEN order_dt < TO_DATE('2003-01-01', 'YYYY-MM-DD') THEN      INTO cust_order_2002 (order_nbr, cust_nbr, sales_emp_id,
       sale_price, order_dt)
    VALUES (order_nbr, cust_nbr, sales_emp_id, sale_price, order_dt)
  SELECT co.order_nbr, co.cust_nbr, co.sales_emp_id,
    co.sale_price, co.order_dt
  FROM cust_order co
  WHERE co.cancelled_dt IS NULL
    AND co.ship_dt IS NOT NULL;

This statement copies all customer orders prior to January 1, 2003, to one of three tables depending on the value of the order_dt column. The keyword FIRST specifies that once one of the conditions evaluates to TRUE, the statement should skip the remaining conditions and move on to the next row. If you specify ALL instead of FIRST, all conditions will be evaluated, and each row might be inserted into multiple tables if more than one condition evaluates to TRUE.

This article is excerpted from Mastering Oracle SQL by Sanjay Mishra and Alan Beaulieu (O'Reilly, 2004; ISBN  1590593006). Check it out at your favorite bookstore today. Buy this book now.