 |
|
|
|
|
|
Up until now, this book has focused on giving you an essential understanding of some of the most
important issues involved in using an Oracle database. The material from the earlier chapters gives you
the foundation of what Oracle is and how it is structured, as well as an understanding of the larger
issues facing you in your work with the Oracle database. You need this background before you can do
anything with the database. But now it’s time to start interacting with data in your Oracle database.
This chapter introduces you to the basics of Structured Query Language, more commonly known as
SQL. Most of what is covered in this chapter can be used with any number of relational databases, so
if you are familiar with SQL from other relational databases, you can probably give this chapter a light
skimming. More complex SQL syntax is covered in the following chapter, and some of the syntax
covered in the next chapter is not available with all relational databases.
This chapter covers a set of statements that you can use to create data structures and access data
within an Oracle database. There is more to SQL than the contents of this chapter alone, but the
goal of the chapter is to give you the necessary essentials of this powerful access language.
A Bit of History
To discover the real roots of SQL, you can refer to Dr. E. F. Codd’s paper, “A Relational Model of Data for
Large Shared Data Banks,” which can currently be found at www.acm.org/classics/nov95/
toc.html. This paper led to the creation of a language—Structured English Query Language—by IBM
Corporation and its release of System R. Oracle, which was originally called Software Development
Laboratories and renamed Relational Software, Inc. (RSI) in 1979, released the first commercially available
implementation of SQL in 1979 as Oracle Release 2. RSI was renamed Oracle Corporation in 1983.
In its early days, some relational databases used a less-than-standard version of the SQL language.
This caused a lot of problems moving applications from one database to another, and also made
programmers have to relearn the language when they used a different system. The standards committees,
noted in the section that follows, helped resolve this incompatibility by creating a base-level syntax
that all database vendors followed. At the current time, most every database vendor will support a
version of the current SQL standards. You will find odd syntax at times because of committed support of
older versions of a particular database, as well as syntax that implements features beyond the range of
an existing specification.Standards
One of the great virtues of SQL is that it is a standardized data language for use with relational databases.
Whenever you have a standard, you have standards bodies that create official versions of the standard.
There are two organizations that publish SQL standards:
? The American National Standards Institute (ANSI), whose latest standard is called ANSI
X3.135-1999
? The International Standards Organization (ISO) & International Electrotechnical Commission
(IEC), whose latest standard is called ISO/IEC 9075:1999, which will be replaced by ISO/IEC
9075:2003 in 2005
Companies claiming conformance to the ANSI and ISO standards must state the type of conformance
and the components that map to this conformance. The minimal conformance is called CORE SQL:2003.
You will find various Oracle documents, such as SQL Reference, Part No. B10759-01, which explain
which parts of the SQL standard Oracle supports fully, which parts of the SQL standard have partial
support, and the parts where Oracle provides equivalent functionality.
You should be aware that there are features of the Oracle Database that differ from the published
standards. This is true for most every relational database—there will be features that provide faster
response, special capabilities, and alternative implementation of statements or historical conventions
that do not conform to the standards.
Of course, many of these additions are also key differentiators between different relational databases, or
places where Oracle has gone beyond the dry specification of the standard to provide additional
functionality. If you are thinking about writing code that could run against more than one relational
database, you will have to check for compliance of all target databases with the standards and the
functionality you want to implement, or code only to the lowest common SQL functionality provided by
all database vendors.SQL Statement Groupings
There are two distinct grouping within the SQL language:
? Data manipulation language (DML). The chapter covers the basic keywords and clauses used
by DML — SELECT, INSERT, UPDATE, DELETE, the WHERE clause, joins, GROUP BY, ORDER BY,
and subqueries. This set of statements are the main pieces of the SQL language that you will use
in your applications.
? Data definition language (DDL). As the name implies, these statements are used to create and
modify objects stored in the database. You may not find yourself using these statements very
often, for a few reasons. First, initial creation of database objects is often the responsibility of the
database administrator, rather than the developer. Second, some of the important clauses in
DDL statements have to do with more administrative attributes of objects, which are beyond the
scope of this book. Third, plenty of tools are available to create database objects. Even HTML-DB,
covered in Chapter 23, has HTML-based tools to help you create database objects.
130
Chapter 8
Although you will probably use DDL less than DML, you may have to create database objects before
you can populate them with data your application will use. For that reason, the next section describes
basic DDL syntax.
For any SQL statement, you can specify a qualified name for the basic database object, such as a table or
a view. The qualified name uses the schema name, followed by a dot (.), and then the table name. The
schema name with the dot is optional, but if you do not specify them, Oracle will assume that the
database object is in the current schema.
Data Definition Language (DDL)
Oracle DDL statements can be organized in five groups of statements.
? Schema Object Manipulation. These statements allow you to create, alter, or drop schema
objects (tables, views, keys, and the like).
? Privileges. These statements allow you to grant or revoke privileges or rights and roles.
? Analysis. These statements allow you to analyze information about your tables, indexes, and clusters.
? Auditing. These statements allow you to initiate auditing options.
? Commenting. These statements allow you to manage comments/information in the data dictionary.
For the purposes of this basic introduction, you will need to understand three basic types of DDL
statements: CREATE, ALTER, and DROP.SapereOnLine |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
