042-045 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Outer Joins

An outer join instructs the database to return a row from one table, even if no corresponding row is found in another table. Listing 2.9 uses an outer join to get the names and grade point averages of students who have not declared a degree plan.

Listing 2.9 A query using an outer join.

 SELECT S.last_name, S.first_name, S.gpa,        DP.degree_plan_description,        SFA.total_financial_aid FROM   STUDENTS S,        DEGREE_PLANS DP,        STUDENT_FINANCIAL_AID SFA WHERE  S.financing_num = SFA.financing_num AND    S.degree_plan = DP.degree_plan (+) AND    S.gpa > 3.0;

This query will return the names of students who have not declared a degree plan as well as those students who have declared a degree plan.

Table Aliases

When joining two or more tables in a query, columns that exist in both tables must be referenced by the name of the source table for the column. Oracle allows developers to assign an alias to a table to make column name references less of a chore to use. The following example illustrates this type of reference ”first using full table names and then using table aliases.

 SELECT STUDENTS.ssn, STUDENT_FINANCIAL_AID.total_aid FROM   STUDENTS, STUDENT_FINANCIAL_AID WHERE  STUDENTS.financing_num = STUDENT_FINANCIAL_AID.financing_num; SELECT S.ssn, SFA.total_aid FROM   STUDENTS S, STUDENT_FINANCIAL_AID SFA WHERE  S.financing_num = SFA.financing_num;

The second query is functionally identical to the first, but is much less tiresome to write.

TIP: Creating Meaningful Table Aliases
A simple method of creating a meaningful alias for a table name is to use the initial of each word in the table name. For instance, in the previous example the STUDENT_FINANCIAL_AID table was given the alias SFA .

Locks

Oracle uses a sophisticated lock mechanism to prevent multiple users from altering the same data at the same time. This mechanism is typically invisible to database users. It s not uncommon, especially during system development or after system hang-ups, for a deadlock to exist.

Consider this situation: user jallen has the STUDENTS table locked and needs to obtain a lock on the STUDENT_FINANCIAL_AID table. User msmith has the STUDENT_FINANCIAL_AID table locked and needs to obtain a lock on the STUDENTS table. This is a deadlock, because each user is preventing the other from completing a transaction.

Locks are cleared when a user issues a COMMIT or ROLLBACK statement. Locks can also be explicitly obtained, but this is a rare event. It s best to let Oracle determine which objects that you need to lock.

NULL Values

A NULL value is a column that does not have a defined value. A NULL value is never equal to any other value, including zero and NULL . The expression

 NULL = NULL

returns a FALSE result. Any column in a table that is not constrained by a NOT NULL or primary key constraint can contain a NULL value.

You can test for NULL values in a column using the IS NULL operator:

 WHERE <column> IS NULL WHERE <column> IS NOT NULL

In reality, unexpected NULL values can cause all sorts of heartache for developers. The best way to handle this situation is to be aware that columns can contain NULL values while you re coding and to be on the lookout for situations in which NULL values are likely to be present.

Operators

SQL incorporates operators that are quite similar to the operators in other languages, as shown in Table 2.2.

**Table 2.2** SQL operators.

Operator	Usage
**	The exponentiation operator ( 22 IS 4** )
NOT	Negates a condition ( IS NOT NULL , NOT IN , NOT BETWEEN )
+	The addition operator ( 2 + 2 IS 4 ) as well as the unary indicator of a positive number (+2)
-	The subtraction operator ( 4 - 2 IS 2 ) as well as the unary indicator of a negative number (-2)
*	The multiplication operator ( *2 2 IS 4** )
/	The division operator ( 4 / 2 IS 2 )
	The concatenation operator ( ˜A ˜B IS ˜AB )
=	The equation operator ( 2 = 2 )
!=	The non-equation operator ( 3 != 2 )
<	The less than operator ( 2 < 4 )
>	The greater than operator ( 4 > 2 )
<=	The less than or equal to operator ( 2 <= 4, 2 <= 2 )
>=	The greater than or equal to operator ( 4 >= 2, 2 >= 2 )
IS NULL	Tests a variable or condition for a NULL value ( first_name IS NULL )
LIKE	Allows wildcard searches (last_name LIKE ˜SM% )
BETWEEN	An inclusive range test ( 1, 2, AND 3 ARE BETWEEN 1 AND 3 )
IN	A set operator ( 2 IS IN {1, 2, 3} )
AND	A logical and ( x < y AND y < z )
OR	A logical or ( x < y OR y < z )

Any of these operators may be used anywhere in a DML statement with the exception of the FROM clause, as shown by these examples:

 SELECT 2**nRealValue FROM   CHECK_VALUES WHERE  nRealValueProcessed = 'F'; DELETE FROM   STUDENTS WHERE  overall_gpa < 0.2; UPDATE STUDENTS SET    overall_gpa = 1.02 * most_recent_gpa WHERE  ssn = 999999999; INSERT INTO   CHECK_VALUES        (nRealValueProcessed,         nRealValue) VALUES ('F',         (2.031 ** 3) / 9);

Subqueries

A subquery is a query within the WHERE clause of a DML statement, as shown in this example:

 SELECT ssn FROM   STUDENTS WHERE  overall_gpa = (SELECT max (overall_gpa)                       FROM   STUDENTS);

Subqueries can be used in any type of DML statement.

Table of Contents