ORACLE SQL/PLSQL ONLIE TRAINING COURSE

INTRODUCTION & COURSE CONTENT

1. PL/SQL Overview
 Definition
 Object-orientation implementation
 Advantages and disadvantages
 Assumed knowledge
 Performance
2. PL/SQL Language Semantics & Constructs
 Block structures
 Variables and constants
 Query processing
 Control structures
 Modules and sub-programs
 Abstract objects (e.g., collections, object types)
 Error and exception handlers
 Use of SQL*Plus
3. PL/SQL Language Properties & Conventions
 Character set
 Lexical elements (e.g., reserve words, comments)
 Declarations
 Defaults
 NULL/NOT NULL
 %TYPE and %ROWTYPE attributes
 Naming conventions
 Scoping
 Name resolution
 Assignments (e.g., to variables)

 Expressions and comparisons
 Built-in functions
 Data types
4. PL/SQL – The Big Picture
Client-server architecture PL/SQL execution flow SQL*Plus DBMS_OUTPUT.LIN
5. How Sql Interoperates With PL/SQL
 SELECT INTO
 DML within PL/SQL block
 PL/SQL block sequencing
 Savepoints
 Commit/rollback
6. Conditional Constructs
 IF-THEN
 IF-THEN-ELSE
 ELSEIF
 Nested IF
 CASE statements
 NULLIF/COALESCE
 Loops
 EXIT WHEN
 GOTO
7. Exception & Error Handling
 Reason for
 Runtime vs. compile-time errors
 Built-in exceptions
 How exceptions are ‘raised’/’propagated’
8. SQL With PL/SQL Programming
 Definition and concepts
 INSERT, UPDATE and DELETE of PL/SQL records
 FOR loops and cursors
 Subqueries
 REF CURSOR
 Transaction controls

9. PL/SQL Subprograms
 Definition
 Advantages
 Subprogram types
 Procedures
 Functions
 Parameter passing
 Invoking
 Overloading
10. PL/SQL Objects – An Introduction
 Definition
 Advantages
 Structure
 Initialisation
 Attributes
 Constructors
 Methods
 Manipulating
11. Introduction To Package Concepts
 Definition
 Benefits
 Specifications
 Body
 How to call
 Private objects
 Variables and cursors
12. TRIGGERS
 Definition
 When to consider
 BEFORE/AFTER
 ROW/STATEMENT
 INSTEAD OF
13. PL/SQL Debugging

 DBMS_DEBUG
 ADP_DEBUG
 How to debug a PL/SQL block
 How to start the ‘debuggee’
 How to start the ‘debugger’
 Breakpoints
 Executing the debugger
 ‘Stepping’ the debugger
 ADP package definition
 ADP debugger body
 XXX.SQL strings

CONTACT US