Oracle Database 11g: Programming with PL/SQL


Description You will use PL/SQL to create programs with program units such as procedures, functions, packages, and database triggers to enhance data manipulations.

This course is designed for IT Professionals with programming experience. The target student will also have experience in using SQL with Oracle 10g or 11g.

Learning Objectives
Upon successful completion of this course, students will be able to:

  • write a simple PL/SQL program to familiarize themselves with the fundamentals and structure of PL/SQL blocks.
  • create a PL/SQL program using variables and lexical units.
  • use control structures to help them incorporate decision-making approach to programming.
  • handle PL/SQL exceptions.
  • work with PL/SQL cursors to embed SQL in PL/SQL code or call PL/SQL functions within SQL structures.
  • create PL/SQL subprograms.
  • use PL/SQL collections and records to enhance database performance during data processing.
  • develop and use PL/SQL packages.
  • create triggers to automatically trigger validation procedures when a table is modified.
  • use the PL/SQL compiler.
  • manage dependencies.
  • use dynamic SQL to handle changes in data definitions without recompiling.

Course Content

Lesson 1: Getting Started with PL/SQL Programming

Topic 1A: Examine PL/SQL Basics
Topic 1B: Write a Simple PL/SQL Program Block
Lesson 2: Using Variables and Lexical Units

Topic 2A: Use PL/SQL Variables and Data Types
Topic 2B: Use PL/SQL Lexical Units
Lesson 3: Using Control Structures

Topic 3A: Use PL/SQL Operators and Expressions
Topic 3B: Use Conditional Statements
Topic 3C: Use Iterative Statements
Lesson 4: Handling PL/SQL Exceptions

Topic 4A: Handle System-Defined Exceptions
Topic 4B: Handle User-Defined Exceptions
Lesson 5: Working with PL/SQL Cursors

Topic 5A: Manage Data Using Cursors
Topic 5B: Retrieve Data Using the Cursor with Looping Mechanisms
Lesson 6: Creating PL/SQL Subprograms

Topic 6A: Use Stored Subprograms
Topic 6B: Create User-Defined Subprograms
Lesson 7: Improving Database Performance with PL/SQL Collections and Records

Topic 7A: Work with Collections
Topic 7B: Create Record Variables
Lesson 8: Using PL/SQL Packages

Topic 8A: Create Packages
Topic 8B: Work with Product-Specific Packages
Lesson 9: Creating Triggers

Topic 9A: Create a Simple Trigger
Topic 9B: Create a Compound Trigger
Lesson 10: Using the PL/SQL Compiler

Topic 10A: Apply Conditional Compilation
Topic 10B: Hide PL/SQL Code
Lesson 11: Managing Dependencies

Topic 11A: Work with Trigger Dependencies
Topic 11B: Manage Package Dependencies
Lesson 12: Use Dynamic SQL

Topic 12A: Use Native Dynamic SQL
Topic 12B: Work with the DBMS_SQL Package