What is PL/SQL? It is the procedural (and sometimes object-oriented) programming
extension to SQL, provided by Oracle, exclusively for Oracle. If you are familiar
with another programming language called Ada, you will find striking similarities in
PL/SQL. The reason they are so similar is that PL/SQL grew from Ada, borrowing
many of its concepts from it.
The PL in PL/SQL stands for procedural language.PL/SQL is a proprietary language
not available outside the Oracle Database. It is a third-generation language (3GL)
that provides programming constructs similar to other 3GL languages, including
variable declarations, loops, error handling, etc. Historically, PL/SQL was procedural
only. As discussed in the preceding section, however, PL/SQL can now be considered
part of the object-oriented category of languages. Should we change the name to
PL/OO/SQL?
Structured Query Language (SQL)
The SQL in PL/SQL stands for structured query language.We use SQL to SELECT,
INSERT, UPDATE, or DELETE data. We use it to create and maintain objects and
users, and to control access rights to our instances.
SQL (pronounced as sequelor by its letter abbreviation) is the entrance, or
window, to the database. It is a fourth-generation language (4GL) that is intended
to be easy to use and quick to learn. The basic SQL syntax is not the creation of
Oracle. It actually grew out of the work done by Dr. E.F. Codd and IBM in the
early 1970s. The American National Standards Institute (ANSI) recognizes SQL
and publishes standards for the language.
Oracle supports ANSI-standard SQL but also adds its own twist in its SQL*Plus
utility. Through SQL*Plus, Oracle supports additional commands and capabilities
that are not part of the standard. SQL*Plus is a utility available in multiple forms:
¦ Command line From the Unix prompt or DOS prompt
¦ GUI SQL*Plus Client, SQL Worksheet, Enterprise Manager
¦ Web Page iSQL*Plus, Enterprise Manager in 10 g
With just a client installed, we can configure a network connection to remote
databases. Oracle 10 gmakes configuration even easier with a browser-based
Enterprise Manager and iSQL*Plus, both configured at install time.
Relational Database Overview
SQL is the window to the database, but what is the database? A databasein general
terms is anything that stores data. Electronic databases can be as simple as a
spreadsheet or word processing document.
As you might imagine, storing large amounts of data in a spreadsheet or word
processing document can become overwhelming very quickly. These onedimensional
databases have no efficient way of filtering redundant data, ensuring
consistent data entry, or handling information retrieval.
Oracle is a relational database management system, or RDBMS. Relational
databases store data in tables. Tablesare made up of columns that define the type of
data that can be stored in them (character, number, etc.). A table has a minimum
of one column.When data is placed in the table, it is stored in rows.This holds
true for all relational database vendors (see Figure 1-1).
In Oracle, tables are owned by a user, or schema. The schema is a collection of
objects, like tables, that the database user owns. It is possible to have two tables in
one database that have the same name as long as they are owned by different users.
Other vendors do not necessarily follow this approach. SQL Server, for example,
applies different terminology. The SQL Server database is more like an Oracle
schema, and the SQL Server servermore resembles the Oracle database. The result
is the same, however. Objects, such as tables, always have an owner.
It is possible to store all of our data in a single table, just like the spreadsheet,
but that does not take advantage of Oracle’s relational features. For example,
a table containing data about Oracle Press books is incomplete without author
information. It is possible that an author has written multiple titles. In a flat-file,
or single-table, model, the author is listed multiple times. This redundancy can be
avoided by splitting the data into two tables with a column that links related data
together.
Since we have the AUTHORS table, we don’t have to repeat author information
over and over for every title each person writes. We add a single AUTHOR1 column
in the BOOKS table and insert the appropriate ID value from the AUTHORS table
for each title in the BOOKS table. Using a FOREIGN KEY on the BOOKS.AUTHOR1
column, we can relate the two tables together using SQL. Let’s take a look at
an example:
CREATE TABLE authors (
id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
CREATE TABLE books (
isbn CHAR(10) PRIMARY KEY,
category VARCHAR2(20),
title VARCHAR2(100),
num_pages NUMBER,
price NUMBER,
copyright NUMBER(4),
author1 NUMBER CONSTRAINT books_author1
REFERENCES authors(id)
);
After inserting a few records into the tables, we can perform a SELECT, joining
the tables according to their relationship.
SELECT b.title, a.first_name, a.last_name
FROM authors a, books b
WHERE b.author1 = a.id;
This joins the two tables together and retrieves data just as you would have
seen it had it been stored in a flat file. The differences are less redundancy, fewer
opportunities for error, and greater flexibility. To add publisher information, all I
would need to do is create a table called PUBLISHER that contains an ID, then
add a column to the BOOKS table with a FOREIGN KEY pointing back to the
PUBLISHER.ID column.
IT Articles 2007
