Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.