|
|
Exam #1Z0-147 Oracle9i: Program with
PL/SQL
Course Type :
Part Time
Course Duration :
12 Weeks (3 Hours/ Week)
Course Fees : £750
Course Delivery Method : One-to-One
(Lecture led Hands-On Training)
Oracle9i:
Program with PL/SQL |
|
|
|
12 Weeks (36 Hours) |
6.30 - 9.30 PM |
Hounslow,
London |
This class
introduces participants to PL/SQL and helps them understand the benefits
of this powerful programming language. In the class, participants
learn to create PL/SQL blocks of application code that can be shared
by multiple forms, reports, and data management applications. Participants
learn to create procedures, functions, packages, and database triggers.
Participants use iSQL*Plus to develop these program units. Participants
also learn to manage PL/SQL program units and database triggers,
to manage dependencies, to manipulate large objects, and to use some
of the Oracle-supplied package.
Upon completion
of this class, participants will be given extra lab time to
prepare them to successfully pass the Oracle certification exam.
Who
should attend:
This course is
valuable for programmers, application developers, database administrators,
systems and network analysts, manager, Web developers and others
who are or will be, programming with PL/SQL.
Prerequisites:
Course
Objectives:
- Create, execute,
and maintain procedures, functions, packages, and triggers
- Manage dependencies
- Manipulate large
objects
- Conditionally control
code flow (loops, control structures, and explicit cursors)
- Describe Oracle-supplied
packages
- Handle runtime errors
- Manage program constructs
- Describe the features and syntax
of PL/SQL
Course Topics:
Introduction
- Describing PL/SQL
- Describing the Use of PL/SQL for the Developer
and the Database Administrator
- Explaining the Benefits of PL/SQL
- PL/SQL program constructs
- PL/SQL anonymous block structure
- Subprogram block structure
- Course objectives and overview
Declaring Variables
- Recognizing the Basic PL/SQL Block and Its
Sections
- Describing the Significance of Variables in
PL/SQL
- Distinguishing Between PL/SQL and Non-PL/SQL
Variables
- Declaring Variables and Constants
- Executing a PL/SQL Block
Writing Executable Statements
- Recognizing the Significance of the Executable
Section
- Writing Statements Within the Executable Section
- Describing the Rules of Nested Blocks
- Executing and Testing a PL/SQL Block
- Using Coding Conventions
Interacting with the Oracle Server
- Writing a Successful SELECT Statement in PL/SQL
- Declaring the Data type and Size of a PL/SQL
Variable Dynamically
- Writing Data Manipulation Language (DML) Statements
in PL/SQL
- Controlling Transactions in PL/SQL
- Determining the Outcome of SQL DML Statements
Writing Control Structures
- Identifying the Uses and Types of Control
Structures
- Constructing an IF Statement
- Constructing and Identifying Different Loop
Statements
- Controlling Block Flow Using Nested Loops
and Labels
- Using Logic Tables
Working with Composite
Data Types
- Creating User-Defined PL/SQL Records
- Creating a PL/SQL Table
- Creating a PL/SQL Table of Records
- Differentiating Among Records, Tables, and Tables
of Records
Writing Explicit Cursors
- Using a PL/SQL Record Variable
- Distinguishing Between the Implicit and Explicit
Cursor
- Writing a Cursor FOR Loop
Advanced Explicit Cursor Concepts
- Writing a Cursor that Uses Parameters
- Determining When a FOR UPDATE Clause in a
Cursor Is Required
- Using a PL/SQL Table Variable
- Using a PL/SQL Table of Records
Handling Exceptions
- Defining PL/SQL Exceptions
- Recognizing Unhandled Exceptions
- Listing and Using Different Types of PL/SQL
Exception Handlers
- Trapping Unanticipated Errors
- Describing the Effect of Exception Propagation
in Nested Blocks
- Customizing PL/SQL Exception Messages
Creating Procedures
- Describe the uses of procedures
- Create procedures
- Create procedures with arguments
- Invoke a procedure
- Remove a procedure
Creating Functions
- Describe the uses of functions
- Create a function
- Invoke a function
- Remove a function
- Differentiate between a procedure and a function
Managing Subprograms
- Describe system privilege requirements
- Describe object privilege requirements
- Query the relevant data dictionary views
- Debug subprograms
Creating Packages
- Describe packages and list their possible
components
- Create packages that include public and private
subprograms, as well as global and local variables
- Invoke objects in a package
- Remove packages
More Package Concepts
- Write packages that use the overloading feature
of PL/SQL
- Avoid errors with mutually referential subprograms
- Initialize variables with a one-time-only
procedure
- Specify the purity level of packaged functions
- Describe the persistent state of packaged
variables, cursors, tables, and records
- Query the relevant data dictionary views
Using Oracle-Supplied Packages
- Overview of Oracle-supplied packages
- View examples of some supplied packages
- Write dynamic SQL
Creating Database Triggers
- Describe different types of triggers
- Describe database triggers and their use
- Create database triggers
- Describe database trigger firing rules
- Drop database triggers
More Trigger Concepts
- Create triggers that fire when certain database
actions occur
- List some of the limitations of database triggers
- Determine when to use database triggers or
Oracle Server features
- Create triggers by using alternative events
(not INSERT/UPDATE/DELETE)
- Create triggers by using alternative levels
(not STATEMENT/ROW)
- Query the relevant data dictionary views
Managing Dependencies
- Overview of object dependencies
- Manage PL/SQL objects for recompilation
Manipulating Large Objects
- Compare and contrast LONG/RAW/LONG RAW with
large objects (LOBs)
- Understand LOBs
- Manage
binary large file objects (BFILEs)
- Use
PL/SQL with an LOB
- Create a
table with LOB columns
- Manipulate
LOBs
- Use DBMS_LOB Oracle-supplied
packages
- Create a temporary LOB
Return to top
|
|
ACCA
|
