Constructing SQL Statements for Oracle

Although SQL was developed to provide consistent and easy access to relational databases, many databases have minor syntax variations in SQL syntax. In fact, Microsoft Access, Microsoft SQL Server, MySQL, and Oracle all use SQL, but there are enough differences in the individual syntax to make life difficult for the unaware.

Oracle SQL Guidelines

Before we discuss Oracle SQL syntax variations, though, let's look at a basic list of guidelines.

1. Names can be from 1 to a maximum of 30 characters

2. Names must begin with a letter.

3. Names cannot be a reserved word.

4. Names cannot be a SQL command.

5. A name can begin with, end with, and contain illegal characters if it is enclosed in double quotes.

6. You can construct SQL statements using the standard A through Z character set, numbers 0 through 9, spaces, and + - * = ? @ ( ) _ . , < > | $ #.

7. Oracle strongly discourages you from using # and $.

8. Oracle does not distinguish between spaces and tabs, carriages returns, and multiple spaces. For example, Oracle considers the following SELECT statements identical:

   SELECT BOOKS.BOOKID, BOOKS.TITLE FROM BOOKS SELECT BOOKS.BOOKID, BOOKS.TITLE FROM BOOKS

9. SQL code is not case sensitive. However, Oracle variable names are case sensitive.

Comments

As discussed in Chapter 3, it is essential that you comment your code. Although you can comment around your SQL in the web-scripting language of your choice, you can also add comments within Oracle SQL statements. Comments do not affect Oracle's execution of the statement. A comment can appear between any keywords, parameters, or punctuation marks in a statement. You can create a comment in a SQL statement using two methods.

Method One

Method one lets you span a comment over several lines. Begin your comment with /* and end with */. Here's an example:

/* RETURN A RECORDSET   CONTAINING THE BOOKID   AND TITLE FROM THE BOOKS TABLE */  SELECT BOOKS.BOOKID, BOOKS.TITLE FROM BOOKS

Method Two

Method two limits a comment to just one line. Begin the comment with --. Here's an example:

-- RETURN A RECORDSET -- CONTAINING THE BOOKID -- AND TITLE FROM THE BOOKS TABLE  SELECT BOOKS.BOOKID, BOOKS.TITLE FROM BOOKS

SQL Syntax Variations

For the most part, Oracle SQL statements match up well with the SQL syntax of other database systems. Straight Select, Insert, Update, and Delete SQL are all similar. However, Oracle does differ concerning joins. As we mentioned in Chapter 5, a join defines a relationship between data tables. The three most common types of joins are:

• Inner

• Left outer

• Right outer

Inner Join

Oracle does not support any of the "join" syntax. To create joins in Oracle, you must define the relationships between tables in the WHERE clause of the SQL statement.

For example, here is a simple join between the Book table and the Category table of the Books database sample:

SELECT BOOKS.BOOKID, BOOKS.TITL EFROM BOOKS, CATEGORIES WHERE BOOKS.CATEGORY = CATEGORIES.CATEGORIESID

Outer Join

Although Oracle does not support the "outer join" syntax, you can easily create a left or right outer join using a special operator, (+). Placing (+) next to a column specifies that the column can be padded with NULLs in the OUTER JOIN recordset. The following statement creates a left outer join in our sample database.

SELECT BOOKS.BOOKID, BOOKS.TITLE FROM BOOKS, CATEGORIES WHERE BOOKS.CATEGORY = CATEGORIES.CATEGORIESID (+) 

The resulting recordset looks like this:

BookID	CategoryID
1	1
2	1
3	3
4	4
5	<NULL>
6	<NULL>

Likewise, to create a right outer join, move the special operator as follows:

SELECT BOOKS.BOOKID, BOOKS.TITLE FROM BOOKS, CATEGORIES WHER EBOOKS.CATEGORY(+) = CATEGORIES.CATEGORIESID

The resulting recordset looks like this:

BookID	CategoryID
1	1
2	1
<NULL>	2
3	3
4	4
<NULL>	5
1	1
2	1

Note 

An Oracle idiosyncrasy is that you cannot use (+) on both sides of the equality to write a full outer join. Also, you cannot outer join the same table to more than one other table in a single SELECT statement.

Tuning Your SQL Statements

Your primary goal as a web developer is to deliver information in a quick and efficient manner via the web. Unfortunately, poor database performance can dramatically slow your web application speed. Poor database performance can often be the result of poorly tuned SQL queries. Therefore, every SQL code tweak you can apply to shave off a few milliseconds of data access and retrieval time is vital. Fortunately, you can use a simple strategy to tune your SQL queries:

Say Exactly What You Want and Take Only What You Need

Specifically, avoid using * in SELECT statements. Using * forces Oracle to look up every column name in the database table and usually returns data you don't need in the recordset. Obviously, lookup and large data transactions take extra time and can slow performance. As an alternative, list every column name you plan to use in SELECT statements.

Likewise, list specific fields in INSERT statements instead of structuring the SQL statement to insert data based on column order. For example, the following statement:

INSERT INTO YOURTABLE VALUES  ('To Kill a Mockingbird','ISBN-00001','01/01/2003')

ties your SQL statement to the column order of the table. To free you code and database from this dependence, list each column name in your SQL as shown in the following statement.

INSERT INTO YOURTABLE  (BOOKTITLE,ISBN,CREATED_DT)  VALUES  ('To Kill a Mockingbird','ISBN-00001','01/01/2003')