Course length: 3.0 day(s)
Course Description
Course Objective: You will use PL/SQL to create programs with program units such as procedures, functions, packages, and database triggers to enhance data manipulations.
Target Student: 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.
Prerequisites: Students enrolling in this class should have knowledge of working with Oracle databases. To ensure your success, we recommend that you first complete the following Element K courses or have equivalent knowledge.
- Oracle Database 11g: Introduction, and
- Oracle Database 11g: SQL Fundamentals I & II
Hardware Requirements
To use Oracle 11g Release 2 (Standard Edition) on each student’s machine, you need the following hardware:
- Intel® Pentium® of 800 MHz or higher processor.
- 2 gigabyte (GB) of RAM or more.
- 6 gigabytes (GB) of available hard-disk space or more.
- CD-ROM or DVD drive.
- Super VGA or higher resolution monitor.
- Mouse or a compatible pointing device.
- A projection system to display the instructor’s computer screen.
Software Requirements
Each computer requires the following software:
- Oracle 11g Release 2 (Standard Edition) server software.
- Oracle 11g Release 2 (Standard Edition) client software.
- Microsoft® Office™ Professional 2007 or higher.
- JDK 6.0 (64 bit) or later.
- SQL Developer
Course 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
