This course introduces students to PL/SQL and explains the benefits of this powerful programming language. Students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students also learn to create anonymous PL/SQL blocks and are introduced to stored procedures and functions. Students learn about declaring identifiers and trapping exceptions. Demonstrations and hands-on practice reinforce the fundamental concepts.
Students use Oracle SQL Developer to develop these program units. SQL*Plus and JDeveloper are introduced as optional tools.
Learn To:
Declaring PL/SQL Variables
Working with Composite Data Types
Conditionally control code flow (loops, control structures)
Explicit cursors
Handle runtime errors
Describe stored procedures and functions
Skills Gained
Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
Handle runtime errors
Describe stored procedures and functions
Use cursors to process rows
Design PL/SQL anonymous block that execute efficiently.
Describe the features and syntax of PL/SQL
Who Can Benefit
PL/SQL Developer
System Analysts
Technical Consultant
Forms Developer
Portal Developer
Prerequisites
Required Prerequisites:
Oracle Database 11g: SQL Fundamentals II
Oracle Database 11g: SQL Fundamentals I
Suggested Next Courses:
Oracle Database 11g: Develop PL/SQL Program Units
Code:
11g-PLSQL-FUND
Length:
2 days
Type:
Instructor-Led
Certified By:
Oracle
Tuition:
$1,200
This course is taught by Certified Oracle instructors. There is a difference. Learn More
This course is in the following categories. Click the categories to find similar courses and topics of interest.
There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below. We can also contact you when the course is scheduled in your area.
Introduction
Course Objectives
Course Agenda
Describing the Human Resources (HR) Schema
PL/SQL development environments Available in this course
Introduction to SQL Developer
Introduction to PL/SQL
PL/SQL Overview
Benefits of PL/SQL Subprograms
Overview of the Types of PL/SQL blocks
Creating and Executing a Simple Anonymous Block
Generating Output from a PL/SQL Block
Declaring PL/SQL Identifiers
Different Types of Identifiers in a PL/SQL subprogram
Using the Declarative Section to Define Identifiers
Storing Data in Variables
Scalar Data Types
%TYPE Attribute
Bind Variables
Using Sequences in PL/SQL Expressions
Writing Executable Statements
Describing Basic PL/SQL Block Syntax Guidelines
Commenting Code
SQL Functions in PL/SQL
Data Type Conversion
Nested Blocks
Operators in PL/SQL
Interacting with the Oracle Server
Including SELECT Statements in PL/SQL to Retrieve data
Manipulating Data in the Server Using PL/SQL
The SQL Cursor concept
Using SQL Cursor Attributes to Obtain Feedback on DML
Saving and Discarding Transactions
Writing Control Structures
Conditional processing Using IF Statements
Conditional processing Using CASE Statements
Simple Loop Statement
While Loop Statement
For Loop Statement
The Continue Statement
Working with Composite Data Types
Using PL/SQL Records
Using the %ROWTYPE Attribute
Inserting and Updating with PL/SQL Records
INDEX BY Tables
INDEX BY Table Methods
INDEX BY Table of Records
Using Explicit Cursors
Understanding Explicit Cursors
Declaring the Cursor
Opening the Cursor
Fetching data from the Cursor
Closing the Cursor
Cursor FOR loop
Explicit Cursor Attributes
FOR UPDATE Clause and WHERE CURRENT Clause
Handling Exceptions
Understanding Exceptions
Handling Exceptions with PL/SQL
Trapping Predefined Oracle Server Errors
Trapping Non-Predefined Oracle Server Errors
Trapping User-Defined Exceptions
Propagate Exceptions
RAISE_APPLICATION_ERROR Procedure
Creating Stored Procedures and Functions
Understanding Stored Procedures and Functions
Differentiate between anonymous blocks and subprograms
Create a Simple Procedure
Create a Simple Procedure with IN parameter
Create a Simple Function
Execute a Simple Procedure
Execute a Simple Function
When you take a certified course with ExitCertified, you are learning from
the creators of the products you use. Our commitment to your IT
community, along with our authorization to deliver certified courses,
ensures you receive a premium training experience.
