SQL Language Enhancements
9i includes SQL language enhancements to conform to the latest international SQL standard, referred to as SQL-99, as well as some other miscellaneous improvements.
Joins
SQL statements that perform joins typically combine rows from two or more tables or views. The Cartesian product is a join that has no WHERE clause, so it produces all combinations of rows from the two input tables. With SQL-99 syntax, use the keywords CROSS JOIN to implement a Cartesian product.
Use the keywords NATURAL JOIN to match rows that have the same values in all columns that have the same name (the columns must also have the same data type):
SELECT location_id, city, department_name
FROM location_table NATURAL JOIN department_table ;
To perform a join where columns do not have the same name or you want to specify the columns on which to join, use the keyword USING:
SELECT location_id, city, department_name
FROM location_table JOIN department_table
USING (location_id) ;
Alternatively, you could use the SQL-99 ON clause to specify the join separate from other parts of the WHERE clause.
Recall that outer joins combine all matching rows from both tables, along with rows that are unmatched (from either one table or the other). A full outer join returns all matching rows, plus the unmatched rows from both tables. Previously in Oracle, you encoded a plus sign (“+”) after a table name to signify an outer join. Using the SQL-99 syntax, use the words LEFT OUTER JOIN or RIGHT OUTER JOIN instead. The table to the left or right (respectively) of these keywords returns its unmatched rows. Or specify FULL OUTER JOIN to retrieve the matched rows plus the unmatched rows from both tables.
Here’s an example LEFT OUTER JOIN:
SELECT d.department_id, e.last_name
FROM departments d LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
ORDER BY d.department_id;
The FULL OUTER JOIN returns rows based on the matching condition, plus unmatched rows from the tables to the left and right of the join clause:
SELECT d.department_id as d_dept_id, e.department_id as e_dept_id,
e.last_name
FROM departments d FULL OUTER JOIN employees e
ON d.department_id = e.department_id
ORDER BY d.department_id;
(The above two examples are from Oracle Corporation’s Oracle9I SQL Reference manual, Release 2.)
CASE Expressions
Expressions consists of one or more values, operators, and SQL functions and result in a value. Expressions can appear in any of these clauses:
- The SELECT clause of queries
- The WHERE, ORDER BY, or HAVING clauses
- The VALUES clause of the INSERT statement
- The SET clause of an UPDATE statement
Oracle9I introduces CASE expressions. They have two basic forms. Here are examples of each:
SELECT last_name, class_membership,
CASE class_membership WHEN 1 THEN ‘Freshman’
WHEN 2 THEN ‘Sophomore’
WHEN 3 THEN ‘Junior’
WHEN 4 THEN ‘Senior’
ELSE ‘Unknown’ END As “Classes”
FROM class_table ;
The second CASE expression format is called the searched CASE. In it, comparisons occur after the WHEN clause:
SELECT last_name, salary,
CASE WHEN salary < 40000 THEN ‘low’
WHEN salary < 80000 THEN ‘average’
WHEN salary >= 80000 THEN ‘high’ END As “Category”
FROM employee_table ORDER BY last_name ;
New Functions
9i introduces many new SQL functions. Among them are various analytic functions. These provide for distributions, percentiles, ranking, histograms and grouping sets.
Also new is the NULLIF function: NULLIF (expression_1, expression_2)
If the expressions are equal, it returns NULL. Otherwise, it returns expression_1.
The COALESCE function returns the first value in the list that is not null:
COALESCE (expression_1, expression_2, expression_3 . . .)
Globalization Enhancements
9i introduces new date and time data types:
| Data Type: | Use: |
| TIMESTAMP | Like DATE, but specifies fractional seconds precision |
| TIMESTAMP WITH TIME ZONE | TIMESTAMP plus time zone displacement |
| TIMESTAMP WITH LOCAL TIME ZONE | TIMESTAMP plus time normalized in terms of the database’s time zone |
| INTERVAL DAY TO SECOND | A time duration stored in terms of days, hours, minutes and seconds |
| INTERVAL YEAR TO MONTH | A time duration stored in terms of years and months |
Oracle supports two new Unicode character sets:
| New Character Set: | Use: |
| AL16UTF16 | 2-byte, fixed width (also known as UTF16 or UCS2 |
| AL32UTF8 | 8-bit, variable-width encoding (use instead of UTF8) |
9i allows you to have up to 4 sort values for each character to enable a four-level linguistic sort.
Miscellaneous Language Enhancements
9i introduces the SQL MERGE statement. This provides for bulk insert and/or update of data records into a table. If a record matches a row in the target table as per some condition you specify, that row in the target table is updated with the new record. If a record in the input stream does not match any row in the existing table (as per the ON condition you specify), that that row is inserted into the target table.
9i allows you to create a constraint index explicitly as part of the CREATE TABLE statement:
CREATE TABLE my_table
(column_a CHAR(5)
CONSTRAINT my_pk PRIMARY KEY
USING INDEX
(CREATE INDEX
my_ix ON my_table(column_a)),
column_b CHAR(10) );
When you later drop or disable the constraint, you can tell Oracle either to KEEP or DROP the index:
ALTER TABLE my_table DROP CONSTRAINT my_pk KEEP INDEX ;
Oracle now minimizes the duration of its lock during foreign-key checking. Previously these locks were held until the operation completed. 9i also caches up to 256 primary keys, for quicker reference during referential checking.
On the SELECT...FOR UPDATE statement, you can now specify a maximum number of seconds to wait for any locks the statement requires:
SELECT... FOR UPDATE WAIT 10 ;
If the program does not get the lock within the specified time, it receives an error message from Oracle and continues.
For performance, 9i can generate native C code from PL/SQL procedures, then compile the C code (assuming you have a C compiler on your server).
9i introduces a new GUI tool called the Locale Builder. It maintains information about the language, territory, character set, sort definitions, and the like in .nlt and .nlb files. Start the Locale Builder by issuing the command: lbuilder under Unix, or select it off the Windows server “Start Program” menus.
