0223-0227

Previous Table of Contents Next

Page 223

CHAPTER 11

SQL

IN THIS CHAPTER

• Oracle's SQL: An Overview 224
• Datatypes 224
• The CREATE Statement 227
• Writing Queries 234
• The DECODE Statement 250
• INSERTs, UPDATEs, and DELETEs 251
• Master/Detail or Parent/Child SQL 254
• Additional Tips 255

Page 224

This chapter covers the basics of Oracle's Structured Query Language (SQL), complete with extensions for the Oracle8 object-relational technology. Oracle8 extensions are compliant with open systems standards and allow the integration of existing relational systems with the new object-relational Oracle8 database. Most extensions are more useful for the implementation of large database systems but are included here for completeness.

Oracle's SQL: An Overview

SQL was introduced by IBM as the language to interface with its prototype relational database management system, System R. The first commercially available SQL relational database management system was introduced in 1979 by Oracle Corporation. Today, SQL is an industry standard, and Oracle Corporation clearly leads the world in relational database management system technology.

Because SQL is a non-procedural language, you can manipulate sets of records rather than one record at a time. The syntax is free- flowing , enabling you to concentrate on the data presentation. Oracle has two optimizers (cost-based and rule-based) that parse the syntax and format it into an efficient statement before the database engine receives it for processing. The database administrator (DBA) determines which optimizer is the default for each database instance. There are methods to choose cost-based or rule-based optimization as well.

SQL ”The Standard

The American National Standards Institute (ANSI) declared SQL as the standard language for relational database management systems. Most companies that produce relational database management systems support SQL and tend to comply with the ANSI SQL89 standard.

Datatypes

This chapter does not provide a lesson on data modeling and creating a proper database schema. To write proper SQL statements, you need a familiarity with database objects (tables, views, constraints, and so on).

One general rule to follow when you are writing SQL statements is that you cannot mix datatypes. Conversion utilities are available to convert from one type to another. These conversion functions are covered later in this chapter.

NOTE

Oracle does provide implicit datatype conversions, but explicit conversions are much easier to understand for subsequent code modifications.

Page 225

Numeric

The NUMBER datatype is used to store zero, negative, positive, fixed, and floating-point numbers with up to 38 digits of precision. Numbers range between 1.0 10 ^_130 and 1.0 10 ¹²⁶ .

Numbers can be defined in one of three ways:

 NUMBER(p,s) 

where p is the precision up to 38 digits and s is the scale (number of digits to the right of the decimal point). The scale can range between _84 and 127.

 NUMBER (p) 

This is a fixed-point number with a scale of zero and a precision of p.

 NUMBER 

This is a floating-point number with a precision of 38.

The following list shows how Oracle stores different scales and precisions:

Actual Data	Defined as	Stored as
123456.789	NUMBER(6,2)	123456.79
123456.789	NUMBER(6)	123457
123456.789	NUMBER(6,-2)	123400
123456.789	NUMBER	123456.789

Date

Instead of storing date and time information in a character or numeric format, IBM created a separate datatype. For each DATE datatype, the following information is stored:

Century
Year
Month
Day
Hour
Minute
Second

You can easily retrieve the current date and time by using the function SYSDATE.

Page 226

Date arithmetic is possible using number constants or other dates. Only addition and subtraction are supported. For example, SYSDATE + 7 returns one week from today.

Every database system has a default date format that is defined by the initialization parameter NLS_DATE_FORMAT. This parameter is usually set to DD-MON-YY, where DD is the day of the month (the first day of the month is 01), MON is the abbreviated month name , and YY is a two-digit year designation.

If you do not specify a time, the default time is 12:00:00 a.m. If only the time component is captured, the default date is the first day of the current month.

Character

There are six character types available:

• The CHAR datatype is used where fixed-length fields are necessary. Any length up to 255 characters can be specified. The default length is 1. When data is entered, any space left over is filled with blanks. All alphanumeric characters are allowed.
  
• The NCHAR datatype has the same attributes as the CHAR datatype and is an Oracle8 extension to support multi-byte character set databases.
  
• The VARCHAR2 is used for variable-length fields. A length component must be supplied when you use this datatype. The maximum length is 2000 characters. All alphanumeric characters are allowed.
  
• The NVARCHAR2 has the same attributes as the VARCHAR2 datatype and is an Oracle8 extension to support multi-byte character set databases.
  
• The LONG datatype is used to store large amounts of variable-length text. Any length up to 2GB can be specified. Be aware that there are some restrictions to using this datatype:
  
  Only one column per table can be defined as LONG.
  A LONG column cannot be indexed.
  A LONG column cannot be passed as an argument to a procedure.
  You cannot use a function to return a LONG column.
  You cannot use a LONG column in WHERE, ORDER BY, GROUP BY, or CONNECT BY clauses.
• The VARCHAR datatype is synonymous with VARCHAR2. Oracle Corporation is reserving this for future use. Do not use this datatype.

Binary

Two datatypes, RAW and LONGRAW, are available for storing binary type data such as digitized sound and images. These datatypes take on characteristics similar to the VARCHAR2 and LONG datatypes already mentioned.

Page 227

Use the RAW datatype to store binary data up to 2,000 characters and use the LONGRAW datatype to store binary data up to 2GB.

Oracle stores and retrieves only binary data; no string manipulations are allowed. Data is retrieved as hexadecimal character values.

Others

Every row in the database has an address. You can retrieve this address using the ROWID function. The format of 8 ROWID is as follows :

 BLOCK.ROW.FILE 

BLOCK is the data block of the data file containing the ROW. The data is in hexadecimal format and has the datatype ROWID.

MLSLABEL is a datatype used to store the binary format of a label used on a secure operating system.

The CREATE Statement

The CREATE statement opens the world to the user . Whether you create a simple temporary table or a complex database schema, you repeatedly use the CREATE statement. Only a few of the more common CREATE statements are covered here.

Tables

Every database designer must create a table some time. You need the CREATE TABLE system privilege to execute this command, and the DBA is responsible for administering these privileges.

Non-Partitioned Tables

The syntax for creating non-partitioned tables is presented in the following example:

 CREATE TABLE schema.TABLE (COLUMN DATATYPE default expression   column constraint) table constraint PCTFREE x PCTUSED x INITRANS x MAXTRANS x   TABLESPACE name STORAGE clause CLUSTER cluster clause ENABLE clause DISABLE clause AS subquery 

In this syntax, SCHEMA is an optional parameter to identify the database schema where the table will be placed. The default schema is your own.

TABLE is mandatory and is the name of the table being created.

COLUMN DATATYPE is required to identify each column in the table. Separate the columns with commas. A maximum of 254 columns per table is allowed.

The default expression is optional and is used to assign a default value to a column when a subsequent insert statement does not make an explicit assignment.

column constraint is optional. It is used to define an integrity constraint such as NOT NULL.

Previous Table of Contents Next