|
|
|
Oracle Database 11g: PL/SQL Fundamentals
This course starts with an introduction to PL/SQL and proceeds to list the benefits of this powerful
programming language. Participants learn to create anonymous PL/SQL blocks, stored procedures and
functions; and about declaring identifiers and trapping exceptions. Demonstrations and hands-on practice
reinforce the fundamental concepts.
This is appropriate for a 10g and 11g audience. There are minor changes between 10g and 11g features in SQL
Prerequisite
Taken course PT-OR1101 - Oracle Database 11g: Introduction to SQL or equivalent experience
Training Format
Instructor Led Sessions with Intensive Hands-on Training, utilizing our (Lecture | Lab | Review) Delivery.
Comprehensive Study Materials and Pre/Post-Class Mentoring and Practice Test.
Oracle 11g Enterprise Edition installed on your PC (Laptop preferred)
Bonus : Exposure to Basic UNIX Scripting, Linux, TOAD, SQL*Loader, SFTP, and SecureCRT
|
|
Course Outline
|
Introduction
• Course Objectives
• Course Agenda
• Describe the Human Resources (HR) Schema
• PL/SQL development environments available in this course
• Introduction to SQL Developer
Introduction to PL/SQL
• Overview of PL/SQL
• Identify the benefits of PL/SQL Subprograms
• Overview of the types of PL/SQL blocks
• Create a Simple Anonymous Block
• How to generate output from a PL/SQL Block?
Declare PL/SQL Identifiers
• List the different Types of Identifiers in a PL/SQL subprogram
• Usage of the Declarative Section to Define Identifiers
• Use variables to store data
• The %TYPE Attribute
• What are Bind Variables?
• Sequences in PL/SQL Expressions
Write Executable Statements
• Describe Basic PL/SQL Block Syntax Guidelines
• Learn to Comment the Code
• Deployment of SQL Functions in PL/SQL
• How to convert Data Types?
• Describe Nested Blocks
• Identify the Operators in PL/SQL
Interaction with the Oracle Server
• Invoke SELECT Statements in PL/SQL
• Retrieve Data in PL/SQL
• SQL Cursor concept
• Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
• Data Manipulation in the Server using PL/SQL
• Understand the SQL Cursor concept
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions
Control Structures
• Conditional processing using IF Statements
• Conditional processing using CASE Statements
• Describe simple Loop Statement
• Describe While Loop Statement
• Describe For Loop Statement
• Use the Continue Statement
Composite Data Types
• Use PL/SQL Records
• The %ROWTYPE Attribute
• Insert and Update with PL/SQL Records
• INDEX BY Tables
• Examine INDEX BY Table Methods
• Use INDEX BY Table of Records
Explicit Cursors
• What are Explicit Cursors?
• Declare the Cursor
• Open the Cursor
• Fetch data from the Cursor
• Close the Cursor
• Cursor FOR loop
• The %NOTFOUND and %ROWCOUNT Attributes
• Describe the FOR UPDATE Clause and WHERE CURRENT Clause
Exception Handling
• Understand Exceptions
• Handle Exceptions with PL/SQL
• Trap Predefined Oracle Server Errors
• Trap Non-Predefined Oracle Server Errors
• Trap User-Defined Exceptions
• Propagate Exceptions
• RAISE_APPLICATION_ERROR Procedure
Stored Procedures
• Create a Modularized and Layered Subprogram Design
• Modularize Development With PL/SQL Blocks
• Understand the PL/SQL Execution Environment
• List the benefits of using PL/SQL Subprograms
• List the differences between Anonymous Blocks and Subprograms
• Create, Call, and Remove Stored Procedures
• Implement Procedures Parameters and Parameters Modes
• View Procedure Information
Stored Functions and Debugging Subprograms
• Create, Call, and Remove a Stored Function
• Identify the advantages of using Stored Functions
• Identify the steps to create a stored function
• Invoke User-Defined Functions in SQL Statements
• Restrictions when calling Functions
• Control side effects when calling Functions
• View Functions Information
• How to debug Functions and Procedures?
Click here to go back to previous page
|
|
|
