Oracle Database 11g: Programming with PL/SQL


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


您的电子邮箱地址不会被公开。 必填项已用 * 标注