Oracle PL/SQL Programming Part 1

Course Description

 

This Oracle 11g PL/SQL programming Part 1 online training course introduces you to PL/SQL and helps you understand the benefits of Oracle’s proprietary database language. You will learn to create PL/SQL blocks of application code that can be shared by multiple users, forms, reports, and data management applications. In addition you will create anonymous PL/SQL blocks as well as named stored procedures, functions, packages and triggers. This Oracle course also covers learning to develop, execute, and manage PL\SQL stored program units, subprograms, triggers, declaring identifiers and both user-defined and pre-defined exceptions. You will be introduced to the utilization of some of the more useful Oracle-supplied packages.

 

Once you have completed Oracle 11g PL/SQL Programming Part 1 the next course in this series is Oracle 11g PL/SQL Programming Part 2. The Oracle 11g PL/SQL Programming Part 2 course will continue to guide you through more intermediate PL/SQL topics. You will learn how to handle runtime errors, cursor processing, stored procedures and functions, and design and use PL/SQL packages. Also, you will learn how to manage dependencies between stored units, manage security, and create automatic triggers to solve business requirement. Click here to learn more about Oracle 11g PL/SQL Programming Part 2.

 

In This Course You Will Learn How To:

  • Describe the features and syntax of PL/SQL
  • Declare and use both scalar and complex datatypes
  • Use PL/SQL constants and Boolean values
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Use CASE statement and expression
  • Simple loop, While loop, For loop
  • CONTINUE statement
  • Handle runtime errors
  • Use both pre-defined and user-defined exceptions
  • Use the PRAGMA EXCEPTION_INIT clause
  • Design and execute PL/SQL anonymous block
  • Use the Oracle supplied PL/SQL packages to generate screen output

 

Prerequisites

 

You need to have equivalent experience or have taken the following courses:

  • Oracle 10g/11g: Introduction to SQL
  • Oracle 10g/11g: Advanced SQL
  • Oracle 11g/12c: Introduction to SQL
  • Oracle 11g/12c: Advanced SQL

 

Outline

 

Module 00: Oracle 11g PL/SQL programming Part 1 online training - Course Introduction

 

Module 01: PL/SQL Overview Part 1

  • Define PL/SQL
  • Identify Advantages of Using PL/SQL
  • Basic PL/SQL Commands
  • Discuss where PL/SQL is used
  • Exercise

 

Module 02: PL/SQL Overview Part 2

  • Understand PL/SQL Block Structure
  • List the Four Main Parts of a PL/SQL Block
  • State the Purpose of each Part
  • Write Anonymous Blocks
  • Nested Blocks
  • Use DBMS_OUTPUT.PUT_LINE Procedure
  • Exercise

 

Module 03: I/O Within PL/SQL and Overview of Sample Environment

  • I/O Within PL/SQL
  • Overview of Sample Environment
  • Exercise: PL/SQL Overview

 

Module 04: Datatypes and Scalar Variables

  • Datatypes
  • Declare Scalar Variables

 

Module 05: Composite Variables

  • Create Composite Variables
  • Assign and update Values for Variables
  • Reference PL/SQL Table Variables

 

Module 06: Datatypes and Scalar Variables Exercise and Exercise Review

 

Module 07: SQL Statements in PL/SQL

  • Select Data from the Database into PL/SQL Variables
  • The INTO Clause
  • Using SQL in PL/SQL: SELECT
  • Exercise Review

 

Module 08: DML Commands and Transaction Processing

  • DML Commands
  • Using RETURNING Clause
  • Transaction Processing Statements: COMMIT, ROLLBACK, and SAVEPOINT

 

Module 09: DML Commands and Transaction Processing Exercise and Exercise Review

 

Module 10: IF and CASE Statements

  • Use IF..THEN..ELSE Logic in PL/SQL
  • Use the CASE Statement
  • Exercise: IF and CASE
  • Exercise Review

 

Module 11: Loops and Labels

  • Labels and GOTO Statement
  • Looping Statements
  • Simple Loops, Numeric FOR Loops, WHILE Loops
  • Exercise: Loops
  • Exercise Review
  • What Should I Know about Conditional Logic and Loops?

 

Module 12: Exception Handling

  • Exception Handling
  • Define and Code Exceptions
  • The OTHERS Exception

 

Module 13: Pre-Defined Exceptions

  • Pre-Defined Exceptions
  • Exercise: Pre-Defined Exceptions
  • Exercise Review

 

Module 14: Using SQL code, SQLERRM and User-defined Exceptions

  • SQL code
  • SQLERRM
  • User-defined exceptions

 

Module 15: User-Defined Exceptions Exercise and Exercise Review

  • What Should I Know about Exceptions?

 

o Identify and code exceptions

o Use pre-defined exceptions

o Declare exceptions

o Write exception handlers

o Implement exception handler for OTHERS exception

o Create and handle user-defined exceptions

o Report error messages - both user and system

 

Module 16: Exception Propagation and Displaying Errors

  • Exception Propagation
  • Using Nested Blocks
  • Using RAISE_APPLICATION_ERROR
  • Display errors with DBMS_UTILITY

 

Module 17: Exception Propagation and Displaying Errors Exercise and Exercise Review