|
| |
Introduction to PL/SQL
Assumptions and pre-requisites
Delegates are programmers with some experience of SQL.
Course Outline
Architecture
- Why PL/SQL
- Overview of Environments and architecture:
- Client side SQLPLUS, Forms, Reports.
- Web services (ModPLSQL etc including PLSQL Server Pages).
- Server side Stored Procedures and triggers.
Basic Structure and Variables
- Block Structure.
- Scalar Variables (Varchar, Number etc.).
- Variable Scope and anonymous begin end blocks.
- Assignments and Comparisons.
- Built in Functions
Control Structures
- Sequence, Iteration, Selection.
- Control Structures.(IF LOOP GOTO CASE etc)
Procedure Builder
- Procedure Builder Basics
- Coding, Running and Debugging.
- Debugging outside of procedure builder.
Database Access
- Select into
- Update Insert Delete.
- Basic error handling (predefined exceptions).
Cursors
- Records.
- Cursors.
- For loop Cursors.
- Parameter cursor.
- Producing text files such as CSV files.
PLSQL Tables and REF CURSORS
- Composite data types.
- Tables using scalar and composite data types.
- Populating tables from a cursor.
- Bulk binds and fetches etc.
- Transactions-locking
- Pessimistic/optimistic locking (select for update).
- Update through cursors with rowid and updating cursors.
- REF CURSORS
Error Handling
- Exception Propagation.
- Named PL/SQL Blocks.
- Pragma statements to define exceptions.
- Raise Application Error
- Using SQLERRM etc.
- Re-Raising exceptions.
- Re-Trying transactions.
Stored Program Units
- Why and when to use.
- Procedures.
- Functions.
- Packages.
- Local/Global variables.
- Operator overloading.
- Security and name space considerations.
- Invokers Rights
- Autonomous Transactions
Triggers
- Why and when to use (complex validation)
- When they fire.
- When other triggers and update through join views.
Native Dynamic SQL
- History DBMSSQL etc
- Executing Queries ,DML and DDL
- Execute Immediate.
- Bind Variables (performance Issues and reuse).
- Cursor Variables.
Appendix
- Reserved Words.
- Delimiters
- Operators
- Data Types.
- Built in Exceptions.
- Supplied Packages.
[RETURN TO LIST OF COURSES]
|
