 |
|
Advanced PL/SQL
 Click here to contact us
Course Overview
Advanced PL/SQL is designed for experienced programmers interested in using advanced PL/SQL concepts and features. It draws on real-world examples to demonstrate advanced PL/SQL techniques used in the field today. Instruction focuses on core language topics, emphasizing cursor usage and defining and implementing PL/SQL records and collections (index-by tables, nested tables and variable arrays). It takes an extended look at programmer-defined stored packages, inline functions and transaction processing concepts. Students will learn techniques for utilizing many of the advanced features of the latest database version of PL/SQL, such as bulk binds, autonomous transactions and native dynamic SQL, as well as a few of Oracle's most useful built-ins. Throughout all of the examples and exercises, standardized coding techniques are discussed and enforced. The course provides students with all of the essentials necessary to move their PL/SQL programming skills to the next level.
Course Goals
- To gain an understanding of advanced PL/SQL concepts and techniques
- To gain hands-on experience with advanced PL/SQL techniques through utilization of real-world exercises
Prerequisites
- SQL & SQL*Plus
- PL/SQL
- At least six months PL/SQL programming experience recommended
Who Should Attend?
- Application developers
- Application database administrators
Course Materials
- Advanced PL/SQL Course Notes
- PL/SQL Reference Book
- Advanced PL/SQL Sample Script Disk
Duration
Tuition
Course Outline
- Introduction
- Overview
- PL/SQL language
- Cursors
- DBMS_OUTPUT package
- Error handling
- Packaging techniques
- Advantages
- Global referencing
- Package initialization
- Overloaded procedures
- Triggers
- Overview
- General syntax
- Table triggers
- System triggers
- Instead of triggers
- Inline functions
- PL/SQL within SQL
- Purity levels
- Restrictions
- Pipelined functions
- General syntax
- Setting up the database environment
- Executing a pipelined function
- Using the parallel-enabled keyword records
- Table-based
- Cursor-based
- User-defined
- Collections
- Index-by tables
- One-dimensional
- Multidimensional
- Available methods
- Nested tables
- Constructor initialization
- One-dimensional
- Multidimensional
- Available methods
- Variable arrays
- Concept
- Available methods
- Collection exceptions
- Transaction processing
- Bulk binds
- Concept
- FORALL statement
- %BULK_ROWCOUNT attribute
- BULK COLLECT clause
- Combining FORALL and BULK COLLECT
- Managing transactions
- Commit point logic
- Specifying rollback segments
- Autonomous transactions
- Concept
- Defining with PRAGMA
- Transaction visibility
- Cursor variables (ref cursors)
- Defining
- Constrained (strong)
- Unconstrained (weak)
- Exception handling
- Native dynamic SQL
- Comparison to DBMS_SQL
- EXECUTE IMMEDIATE
- Specify values for bind variables
- Returning values from DML statements
- Executing with invoker's privileges
- Accepting column values from queries
- Executing row queries
- The INTO clause
- Implementing a cursor variable
- Executing PL/SQL blocks and stored code
- Advanced SQL techniques
- ANSI SQL
- Character and language processing
- SQL model clause
- Large objects (LOBs)
- Defining LOBs in the database
- Using LOBs in PL/SQL
- The DBMS_LOB package
- LOB limitations
- The object-relational model
- Oracle's new features
- Overview
- PL/SQL native compilation
- Common SQL parser
- XML support
- HTTP cookie support
- Object inheritance
- Language enhancements for Oracle9i
- 10g optimization
- Oracle supplied packages -- overview
- Performance tips and techniques
- Review
- Bibliography
|
|
|
|
