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

Table of Contents

COMMIT And ROLLBACK

In order to save changes to the database, you must issue a COMMIT statement. This instructs Oracle that your changes are complete and that the database should write the changes to the data and release any locks that you have on database objects.

If you make a mistake and wish to have your changes to the database erased, you must issue a ROLLBACK statement. This instructs the database to discard your changes and release any locks that you have on database objects.

Datatypes

Table 2.1 shows a complete list of internal datatypes used by the Oracle7 database.

**Table 2.1** Datatypes used by the Oracle7 database.

Datatype	Description
char	A fixed-length datatype, having a maximum width of 255 bytes. A size must be specified. A single character takes one byte in most systems, although some systems use multibyte characters .
varchar	In Oracle7, this is the same as the varchar2 datatype (below). However, Oracle suggests that the function of this type will change in a future release. Developers and DBAs are advised not to use this datatype; instead, use the varchar2 datatype.
varchar2	A variable length character string of up to 2,000 bytes. A maximum length for the column must be defined.
rowid	Not a real datatype, rowid is a pseudocolumn that is stored for each row of data in the database. A rowid is the physical location of a row on disk and allows Oracle to quickly access the data contained in the row.
date	A fixed length field that is seven bytes in length. Time is stored along with the date in this format. The default format for dates is DD-MM-YY HH:MM:SS AM (For example, 01-JAN-98 12:00:01 AM). Any date between 4712 BC and AD 4712 can be stored. The time of day is stored using a seconds-since-midnight algorithm.
number	A variable length column that holds real values as well as integer values. Precision and scale can be specified. Up to 38 significant digits can be stored. The ANSI standard float may also be used; this is synonymous with the number type.
long	A variable length column that can hold up to 2 GB of data, most commonly used to store character data for which a varchar2 column is inappropriate.
raw	A variable length column used to store binary data and can be up to 255 bytes in length. A size must be defined.
longraw	A variable length column, similar to the raw type, that has a length of up to 2 GB. This type might be used to store music or image files.
mlslabel	This is the binary format of a secure operating system label. It is primarily used with Trusted Oracle, but can be used with standard Oracle as well.

The most common datatypes that you ll encounter are date , varchar2 , and number (and their subtypes ). Even in databases that use the other datatypes, the majority of the columns tend to be of these three types.

Converting Data Of Different Types

Oracle has two methods of converting data between differing types:

Implicit conversion of a datatype can be performed automatically. Oracle will attempt to convert data from one type to another if types are mixed in an expression.

Explicit conversion of datatypes is provided via SQL functions.

Oracle Corporation recommends that explicit data conversion always be used for a number of reasons, including:

SQL code is easier to understand.

Implicit conversions can have a negative performance impact.

Implicit conversions rely on the context in which the columns are referenced and will not always work consistently.

Oracle Corporation might choose to change the functionality of implicit conversions in a future release.

Joins

A join occurs when a SELECT statement references more than one table, view, or snapshot in the FROM clause. Joins do take some processing power to handle, but a well-written join typically doesn t drag down the performance of the database. There are three types of joins that can be used: Cartesian products, simple joins, and outer joins.

Cartesian Products

A Cartesian product occurs when you fail to specify a join condition for the tables in the WHERE clause of your SELECT statement. Listing 2.7 displays a query that causes a Cartesian product.

Listing 2.7 A query that causes a Cartesian product.

 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;

If the STUDENTS table contains 10,000 rows, DEGREE_PLANS contains 120 rows, and STUDENT_FINANCIAL_AID contains 9,000 rows, the Cartesian product generated by the query will contain 10,000 — 9,000 — 120 (10,800,000,000) rows! A result set this large is rarely useful and extremely detrimental to the performance of a database.

Simple Joins

A simple join returns all rows that match one or more specified conditions between tables. The columns compared in the join conditions do not have to have the same name , but should have similar datatypes. Listing 2.8 shows a query using a simple join.

Listing 2.8 A query using a simple 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    SFA.total_aid >= 1000.00 AND    S.gpa > 3.0;

This query will return a result set containing all students who have declared degree plans, have earned at least $1,000 in financial aid, and have a grade point average greater than 3.0. If a student has not declared a degree plan, no row will be returned for that student.

Table of Contents