Open / Close contact form

Oracle Database 11g : Program With PL/SQL Course

Course Overview

This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications.

Course Objectives

  • Design PL/SQL anonymous blocks that execute efficiently
  • Write PL/SQL code to interface with the database
  • Describe the features and syntax of PL/SQL
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Handle runtime errors
  • Create simple procedures and functions
  • Design PL/SQL packages to group and contain related constructs
  • Schedule PL/SQL jobs to run independently
  • Create triggers to solve business challenges

Introduction To Pl/Sql

  • What is PL/SQL
  • PL/SQL Environment
  • Benefits of PL/SQL
  • Overview of the Types of PL/SQL blocks
  • Create and Execute a Simple Anonymous Block
  • Generate Output from a PL/SQL Block
  • SQL Developer as PL/SQL Programming Environment

Declaring Pl/Sql Identifiers

  • Identify the Different Types of Identifiers in a PL/SQL subprogram
  • Use the Declarative Section to Define Identifiers
  • List the Uses for Variables
  • Store Data in Variables
  • Declare PL/SQL Variables

Writing Executable Statements

  • Describe Basic Block Syntax Guidelines
  • Use Literals in PL/SQL
  • Customize Identifier Assignments with SQL Functions
  • Use Nested Blocks as Statements
  • Reference an Identifier Value in a Nested Block
  • Qualify an Identifier with a Label
  • Use Operators in PL/SQL
  • Use Proper PL/SQL Block Syntax and Guidelines

Interacting With The Oracle Server

  • Identify the SQL Statements You Can Use in PL/SQL
  • Include SELECT Statements in PL/SQL
  • Retrieve Data in PL/SQL with the SELECT Statement
  • Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
  • Manipulate Data in the Server Using PL/SQL
  • The SQL Cursor concept
  • Use SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

Writing Control Structures

  • Control PL/SQL Flow of Execution
  • Conditional processing Using IF Statements
  • Conditional Processing CASE Statements
  • Handle Nulls to Avoid Common Mistakes
  • Build Boolean Conditions with Logical Operators
  • Use Iterative Control with Looping Statements

Working With Composite Data Types

  • Learn the Composite Data Types of PL/SQL Records and Tables
  • Use PL/SQL Records to Hold Multiple Values of Different Types
  • Inserting and Updating with PL/SQL Records
  • Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

Using Explicit Cursors

  • Cursor FOR Loops Using Subqueries
  • Increase the Flexibility of Cursors By Using Parameters
  • Use the FOR UPDATE Clause to Lock Rows
  • Use the WHERE CURRENT Clause to Reference the Current Row
  • Use Explicit Cursors to Process Rows
  • Explicit Cursor Attributes
  • Cursors and Records

Handling Exceptions

  • Handling Exceptions with PL/SQL
  • Predefined Exceptions
  • Trapping Nonpredefined Oracle Server Errors
  • Functions that Return Information on Encountered Exceptions
  • Trapping User-Defined Exceptions
  • Propagate Exceptions
  • Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications

Creating Stored Procedures

  • Describe PL/SQL blocks and subprograms
  • Describe the uses of procedures
  • Create procedures
  • Differentiate between formal and actual parameters
  • List the features of different parameter modes
  • Create procedures with parameters and invoke a procedure
  • Handle exceptions in procedures

Creating Stored Functions

  • Describe stored functions
  • List the CREATE OR REPLACE FUNCTION syntax
  • Identify the steps to create a stored function
  • Create a stored function in SQL Developer and execute a stored function
  • Identify the advantages of using stored functions in SQL statements
  • Identify the restrictions of calling functions from SQL statements
  • Describe how procedures and functions differ

Creating Packages

  • List the benefits or using PL/SQL packages
  • Differentiate between a package specification and a package body
  • Create packages
  • Include public and private constructs in a package
  • Call public and private constructs in a package
  • Remove packages

Creating Triggers

  • Describe different types of triggers
  • Describe database triggers and their use
  • Create database triggers
  • Describe database trigger firing rules
  • Remove database triggers

Applications For Triggers

  • Create database and system event triggers
  • Create triggers on DDL statements
  • Use the CALL statement in triggers to invoke procedures
  • Explain the rules for reading and writing to tables with triggers
  • Describe business application scenarios for implementing with triggers
  • Manage trigger code

Understanding And Influencing The Pl/Sql Compiler

  • Describe native compilation and interpreted compilation
  • List the features of native compilation
  • Switch between native and interpreted compilation for compiled PL/SQL code
  • Set the parameters to control aspects of PL/SQL compilation
  • Explain the compiler warning mechanism
  • List the steps to use the compiler warnings
  • Use DBMS_WARNING to implement compiler warnings

Enquire Now

Learn Adobe Photoshop
Your name:
Email address:
Phone number:
Message:

Our google reviews

Other related courses