Introduction to Oracle10g for Developers - 35 Hour Online Class or 5 Day Classroom Workshop

Nội dung khóa học

Khóa học này được thiết kế cho các lập trình viên những  người có trách nhiệm phát triển cơ sở dữ liệu trên oracle 10 G. Khóa học cung cấp những kỹ năng cần thiết để phát triển ứng dụng trên môi trường Oracle 10G. Khóa học giúp học viên hoàn thiện những kỹ năng trên ngôn ngữ SQL và PL/SQL .

Kết thúc khóa học, học viên có thể thi được các chứng chỉ quốc tế uy tín của Oracle – OCA.

• Introduction to Oracle 10g
• What is the Oracle Database?
• Enterprise Edition
• Standard Edition
• Express Edition (10g)
• Options
• Oracle Architecture Overview
• Common Schema Objects
• Workshop
• Introduction to SQL*Plus
• SQL*Plus: The Development Environment
• Connect to the Database
• SQL*Plus Describe Command
• Customizing Your Environment (LOGIN.SQL and Predefined Variables) (10g)
• Executing Queries in SQL*Plus
• Spooling Output
• Creating and Running Scripts
• Querying and Purging the Recycle Bin (10g)
• Workshop
• Datatypes
• General Oracle Datatypes
• Datetime Datatypes
• XML Data Type
• Miscellaneous Data Types
• Workshop
• Creating Tables and Indexes
• Naming Rules
• Listing Oracle Objects using the Data Dictionary
• Creating a Table
• Creating Tables from Other Tables (CTAS)
• Introducing Constraints
• Naming Constraints
• Disabling and Enabling Constraints
• Listing Constraints using the Data Dictionary
• Altering Table Structure
• Dropping Tables
• Privileges Required to Create a Table
• Creating a Table in Another Schema
• Creating a Synonym on a Table
• Introduction to Indexes
• Creating Indexes
• Index Use - Who Decides?
• Workshop
• Querying the Database, Part I - Select
• Simple SELECT Statement
• Eliminating Duplicate Rows with DISTINCT
• Calculated Columns and Column Aliases
• Sorting Results Sets
• Case (and Accent) Insensitive Sort (10g)
• Conditions and Comparison Operators
• Quoting Text Strings (10g)
• Logical Operators
• Search Lists: IN
• Search Range : BETWEEN
• Search Patterns: LIKE
• IS NULL Operator
• Using the ANSI Compliant CASE Expression
• Introduction to Regular Expressions in SQL (10g)
• Pattern Matching with REGEXP_LIKE (10g)
• Using REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR (10g)
• Workshop
• Oracle 10g Row Functions
• Introduction to Functions
• String Manipulation Functions
• Case Conversion Functions
• Concatenation Function
• Character Conversion Functions
• Date Conversion Functions
• Datetime Conversions
• Time Zone Functions
• Date Arithmetic & Functions
• Oracle Pseudo Columns (ROWNUM)
• Workshop
• Querying the Database, Part II - Joins
• Inner Joins
• Using Table Aliases in Joins
• Outer Joins
• ANSI Compliant Joins
• ANSI Natural Join
• ANSI Inner Join
• ANSI Outer Joins
• Full outer joins
• ANSI cross join
• Querying the Database, Part III - Aggregate Functions
• Aggregate (Group) Functions
• GROUP BY Clause
• Grouping Multiple Columns
• Golden GROUP BY Rule
• HAVING Clause
• ROLLUP
• CUBE
• GROUPING Function
• Workshop
• Querying the Database, Part IV - Subqueries
• Subquery Concepts
• Subquery Restrictions
• Subquery Rules
• Subquery IN Operator
• Subquery EXISTS Operator
• NOT EXISTS Operator
• Subquery in FROM Clause
• Extended Subquery Support
• Named Subqueries
• Workshop
• Querying the Database, Part V - Set Operators
• UNION Operator
• INTERSECT Operator
• MINUS Operator
• Workshop
• Data Manipulation
• Inserting Rows
• Returning Values from DML Statements
• Returning Aggregates (10g)
• DML Error Logging (10g)
• Inserting Multiple Rows
• 9i Multi-Table INSERT
• Introduction to Oracle Sequences
• Using a Sequence to Generate a Primary Key
• Deleting Rows
• TRUNCATE Command
• Updating a Single Row
• Updating Multiple Rows
• MERGE statement
• 10g MERGE Enhancements (10g)
• Workshop
• Transaction Control
• Transactions
• Oracle Transaction Statements
• Batch Commits (10g)
• Statement Level Read Consistency
• Transaction Level Read Consistency
• Oracle Isolation Levels
• Phantom and Non-Repeatable Reads
• Savepoints
• Implicit Commits
• Workshop
• Concurrency Control
• Introduction to Locks
• Row Lock Architecture
• Basic Locking Rules
• DML Locks
• DDL Locks
• Locking Issues: Lost Update, Blocking and Deadlocks
• Deadlocks: Causes and Fixes
• Workshop
• Oracle Views
• What is a View?
• Creating Views
• Updateable Views
• Read Only Views
• Views & The Data Dictionary
• Workshop
• Introduction to Oracle Security
• Object Privileges
• System Privileges
• Oracle Roles
• Determining Privileges Held
• Workshop
• Introduction to PL/SQL
• What is PL/SQL?
• Why Use PL /SQL?
• PL/SQL Program Structure
• Anonymous Blocks
• Sending Output to SQL*Plus
• Introduction to Procedures
• Procedure Compiler Errors
• Procedure Compiler Warnings (10g)
• Introduction to Functions
• Introduction to Packages
• Querying the Data Dictionary
• Introduction to Triggers
• Tools for PL/SQL Development
• Working in SQL*Plus
• Workshop
• Language Fundamentals
• PL/SQL Statements
• PL/SQL Symbols
• Quoting Mechanism (10g)
• Common PL/SQL Datatypes (10g Enhancements)
• Declaring Variables
• %TYPE Attribute
• PL/SQL Records
• %ROWTYPE Attribute
• Programmer Defined Records
• Variable Scope
• Nested Blocks
• Functions
• Regular Expressions (10g)
• IF Statement
• CASE Statement and Expression
• Simple CASE
• Searched CASE
• Simple Loops
• Nested Loops
• Numeric FOR Loop
• PL/SQL Arrays
• Simple Array Example
• Array Methods
• Workshop
• PL/SQL and SQL, Part I Basics
• SELECT INTO Statement
• Implicit Cursor Loops
• DML in PL/SQL
• Cursor Attributes
• Embedding DDL
• Workshop
• PL/SQL and SQL, Part II Cursors
• What is a Cursor?
• Explicit Cursor Processing
• Cursor Attributes
• Dynamic Cursors
• Dynamic Cursor Example
• Variable Scope & Cursors
• Cursor Parameters
• Cursor Records
• Cursor FOR LOOP
• Updating the Current Row
• Workshop
• PL/SQL and SQL, Part III Bulk Processing
• Bulk Processing
• Bulk Collect
• Bulk DML - FORALL (10g Enhancements)
• Bulk DELETE, INSERT and UPDATE
• Returning into Arrays (10g Enhancements)
• Workshop
• Stored Procedures
• What is a Stored Procedure?
• CREATE PROCEDURE Syntax
• Simple Example
• Review: Compile Errors
• Procedure Signatures
• Calling Procedures
• Procedure Synonyms
• Referencing Parameters by Name
• Returning Sets: REF CURSOR
• Dropping Stored Procedures
• Procedure Dependencies
• ALTER COMPILE Statement
• PL/SQL Procedure Privileges
• Granting Execute Privilege
• The Data Dictionary
• Workshop
• PL/SQL Functions
• What is a Function?
• CREATE FUNCTION Syntax
• Simple Example
• Calling Functions
• The Data Dictionary
• Table Functions
• Building a Table Function
• Using a Table Function
• Pipelined Functions
• Workshop
• Error Handling
• Types of Errors
• Runtime Errors
• Predefined Named Exceptions
• EXCEPTION Block Syntax
• Handling Exceptions
• Using FORMAT_ERROR_BACKTRACE (10g)
• Recovering from Errors
• Logging Exceptions
• RAISE_APPLICATION_ERROR Statement
• Raising Exceptions
• Raising User Exceptions
• EXCEPTION_INIT Pragma
• Workshop
• PL/SQL Packages
• Package Concepts
• Package Privileges
• Calling Packaged Objects
• Initialization Code
• Session Variables
• Package Body Variables
• Package Overloading
• Compiling Packages
• DROP PACKAGE Statement
• Summary of Oracle Supplied Packages
• Workshop
• DML Triggers
• DML Trigger Concepts
• Trigger Execution
• Row Triggers
• :OLD and :NEW Reference Variables
• Trigger Attributes
• Trigger Restrictions
• Table Triggers
• Autonomous Transactions
• Autonomous Triggers
• INSTEAD OF Triggers
• Workshop
• Trace and Tune PL / SQL
• Introduction to Tuning PL/SQL
• DBMS_PROFILER (10g Enhancements)
• Profiler Setup
• Using Profiler
• Reporting with PROFSUM
• Effective Use of Bind Variables
• Benchmarking with GET_TIME and GET_CPU_TIME (10g)
• Workshop
• File I/O Using UTL_FILE
• UTL_FILE Concepts
• Setup for UTL_FILE
• Unix File Permissions
• Opening Files
• Reading and Writing Files
• Closing Files
• Workshop
• Sending Email with UTL_MAIL (10g)
• Introduction to UTL_MAIL
• Setup for Emailing from the Database
• UTL_MAIL Example
• Workshop
• Appendices
• Table Descriptions
• Oracle Data Dictionary