| |
 |
|
Oracle Concepts by Burleson
Consulting |
Architecture of the SQL
Language and PL/SQL
Overview
This tutorial will begin with an introduction of
the Oracle SQL*Plus utility and a short tutorial on the proper
techniques for developing SQL queries. We will also introduce the
architecture of SQL as a vehicle for extracting data from a relational
Oracle database management system. The topics will include a
getting-started section on using SQL*Plus and developing SQL queries,
the history and evolutions of SQL to show the student how SQL has
evolved to meet changing demands from relational databases. This
tutorial will also explore the evolution of PL/SQL and show how PL/SQL
is used within Oracle as an alternative to traditional procedural
languages.
The SQL section of this tutorial will deal
primarily with a theoretical evolution of SQL as a data access method
for relational databases. We will examine the declarative nature of
SQL statements and understand how the select, project and join
operators can be used in order to get data out of any relational
database. We will also take a look at the concept of the SQL optimizer
and understand how the optimizer chooses the appropriate access path
to the data within the database.
We will also introduce SQL tuning and understand
the basic goals of SQL tanning as they relate to the optimization of
SQL within any relational database management system. This will
include a discussion of database design as it relates to SQL
performance and a discussion of the barriers to SQL tuning, especially
with regard to vendor-based SQL applications.
We will also take a look at techniques for
identifying the offensive SQL within the database management system
and examine techniques for extracting and tuning individual SQL
statements.
The PL/SQL section of this tutorial will start
with the basic introduction to the PL/SQL programming language. We
begin by taking a look at the origins of the PL/SQL language, as well
as an understanding of the different incarnation of PL/SQL, and how
the different versions contain progressively more powerful programming
features.
We also take a look at stored procedures,
functions, and packages within the database programming environment.
We will review the concept of modular coding; introduce PL/SQL block
structures, and understanding the differences between the stored
procedures and functions in an operational environment. We also
examine the concept of tutorial overloading, and how to use forward
declarations within stored procedures.
We wrap up the PL/SQL section of this text and by
examining PL/SQL packages. PL/SQL packages are an important construct
and a database environment, and this section will introduce the
package structure and how you can take stored procedures and functions
and encapsulated them into Oracle packages.
Learning Outcomes
Upon completion of this tutorial, you will be
able to:
* Use SQL*Plus to enter SQL queries and format
output.
* Create and execute SQL queries with Oracle.
* Describe the history and evolution of SQL.
* Explain the history and evolution of PL/SQL.
* Describe processing of SQL and PL/SQL
statements within the database.
* Explain SGA considerations for SQL and
PL/SQL.
* Explain the main features of the PL/SQL
language.
Differentiate between Oracle stored procedures,
functions and packages.
Required Text Reading
- Donald K. Burleson – Oracle
High-performance SQL Tuning, Osborne McGraw-Hill, 2002
-
Chapter1: This Chapter provides a high-level overview of SQL and
presents the basic concepts associated with Oracle SQL. You
should pay special attention to the description of Oracle SQL as a
declarative language and how SQL differs from other data access
languages.
- Steven Feuerstein,
Oracle PL/SQL Programming, second edition, O’Reilly &
Associates, 2001
- Chapters 1, 14, and 15. Chapter1 should
provide you with a conceptual understanding of the features and
evolution of the PL/SQL programming language. Chapters 14 and 15
explain the concept of modularity within the PL/SQL programming
language, as well as understanding how PL/SQL can be and
capsulated into code blocks, stored procedures, and functions.
Required Online Reading
Optional Reading
- Mishra,
Sanjay & Beaulieu, Alan -
Mastering Oracle SQL, O’Reilly & Associates, 2002
- Celko, Joseph,
Joe Celko's SQL for Smarties: Advanced SQL
Programming, Morgan Kaufmann Publishers; ISBN: 1558605762, 1999,
Chapter 1
- Urman, Scott,
Oracle Advanced PL/SQL Programming, Oracle Press,
2000, Chapters 1 and 2.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
