  
                     
|
| |
 |
INTRODUCTION TO ORACLE9I/10G PROGRAMMING
| Course Description |
|
In this class, students will learn how to create, retrieve, and manipulate objects in Oracle10g Structured Query Language (SQL). Students will also be introduced to Oracle10g database features and tools. The Oracle10g release has greatly enhanced the features and functionality of PL/SQL. Students will learn the fundamentals of the PL/SQL programming language. Students will write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students will learn programming, management, and security issues of working with PL/SQL program units. Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.
Course Length: 5 Days
Course Tuition: $1890 (US)
|
• Relational Database and SQL Overview
Review of Relational Database Terminology
Relational Database Management Systems
Introduction to SQL
Oracle Versioning and History
Logical and Physical Storage Structures
Connecting to a SQL Database
Datatypes
Sample Database
• Using Oracle SQL*Plus
SQL*Plus
The SQL Buffer
Buffer Manipulation Commands
Running SQL*Plus Scripts
Tailoring Your SQL*Plus Environment
Viewing Table Characteristics
SQL*Plus Substitution Variables
Interactive SQL*Plus Scripts
Using iSQL*Plus
• SQL Queries - The SELECT Statement
The SELECT Statement
The CASE...WHEN Statement
Choosing Rows with the WHERE Clause
NULL Values
Compound Expressions
IN and BETWEEN
The LIKE Operator
Creating Some Order
• Scalar Functions
SQL Functions
Using SQL Functions
String Functions
Numeric Functions
Date Functions
Conversion Functions
Date Formats
Oracle Pseudocolumns
• SQL Queries - Joins
Selecting from Multiple Tables
Joining Tables
Self Joins
Outer Joins
Types of Outer Joins
• Aggregate Functions and Advanced Techniques
Subqueries
Correlated Subqueries
The EXISTS Operator
The Aggregate Functions
Grouping Rows
Combining SELECT Statements
• Data Manipulation and Transactions
The INSERT Statement
The UPDATE Statement
The DELETE Statement
Transaction Management
Concurrency
Explicit Locking
Data Inconsistencies
Loading Tables From External Sources
• Data Definition and Control Statements
Standard Datatypes
Defining Tables
Constraints
Inline Constraints
Modifying Table Definitions
Deleting a Table Definition
Controlling Access to Your Tables
• Other Database Objects
Views
Creating Views
Updateable Views
Sequences
Synonyms
• Triggers
Beyond Declarative Integrity
Triggers
Types of Triggers
Row-Level Triggers
Cascading Triggers and Mutating Tables
Generating an Error
Triggers on Views
System Triggers
Maintaining Triggers
• PL/SQL Variables and Datatypes
Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and Conversions
Selecting into a Variable
|
• PL/SQL Syntax and Logic
PL/SQL Blocks and Programs
Conditional Statements – IF/THEN
Conditional Statements – CASE
Comments and Labels
Loops
WHILE and FOR Loops
• Stored Procedures and Functions
Stored Subprograms
Procedures and Functions
Creating a Stored Procedure
Calling a Stored Procedure
Passing Parameters and Default Arguments
Parameter Modes
Creating a Stored Function
Calling a Stored Function
Stored Functions and SQL
Local Procedures and Functions
• Exception Handling
SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope and Name Resolution
User-Defined Exceptions
Compile-Time Warnings
• Records and Collections
Record Variables
Using the %ROWTYPE Attribute
VARRAY and Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Associative Array Collections
Collection Methods
Iterating Through Collections
FORALL Statement
BULK COLLECT Clause
• Cursors
Multi-Row Queries
Declaring and Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor
• Using Packages
Packages
Oracle-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages
• Creating Packages
Structure of a Package
The Package Interface and Implementation
Package Variables
Overloading Package Functions and Procedures
Named Parameter Notation
REF CURSOR Variables
• Working with LOBs
Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
External LOBs
Temporary LOBs
The DBMS_LOB Package
• Maintaining PL/SQL Code
Privileges for Stored Programs
PL/SQL Stored Program Compilation
The PL/SQL Execution Environment
Dependencies and Validation
Maintaining Stored Programs
• Appendix A - The Data Dictionary
Introducing the Data Dictionary
DBA, ALL, and USER Data Dictionary Views
Some Useful Data Dictionary Queries
• Appendix B - Dynamic SQL
Generating SQL at Runtime
Native Dynamic SQL vs. DBMS_SQL Package
The EXECUTE IMMEDIATE Statement
Using Bind Variables
Multi-row Dynamic Queries
Bulk Operations with Dynamic SQL
Using DBMS_SQL
DBMS_SQL Subprograms
• Appendix C - PL/SQL Versions, Datatypes, and Language Limits
• Appendix D - Oracle10g Supplied Packages
Contact us for course schedules or more
information.
|
|
|
|
|
|
|
