academy.oracle.
com
Database Programming with PL/SQL – Course Description
Overview
This course introduces students to PL/SQL, Oracle’s procedural extension language for SQL and the Oracle
relational database. Participants explore the differences between SQL and PL/SQL. They also examine the
characteristics of PL/SQL and how it is used to extend and automate SQL to administer the Oracle database. This
course culminates with a project that challenges students to program, implement, and demonstrate a database
solution for a business or organization.
Available Curriculum Languages:
• English
Duration
• Recommended total course time: 180 hours*
• Professional education credit hours for educators who complete Oracle Academy training: 60
* Course time includes instruction, self-study/homework, practices, projects, and assessment
Target Audiences
Educators
• College/university faculty who teach computer programming or a related subject
• Secondary school teachers who teach computer programming
Students
• Students who wish to learn the techniques and tools to automate database application tasks
• Students who possess basic mathematical, logical, and analytical problem-solving skills
• Novice programmers, as well as those at advanced levels, to learning the PL/SQL programming language to an
advanced level
Prerequisites
Required
• Previous experience with at least one programming language
Suggested
• Previous Experience with a database application
• Oracle Academy Course – Database Design and Database Programming with SQL
Suggested Next Courses
• Getting Started with Java Using Alice
• Creating Java Programs with Greenfoot
• Java Fundamentals
• Java Programming
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their
respective owners.
2
Lesson-by-Lesson Topics
Fundamentals
• Introduction to PL/SQL
• Benefits of PL/SQL
• Creating PL/SQL Blocks
Defining Variables and Datatypes
• Using Variables in PL/SQL
• Recognizing PL/SQL Lexical Units
• Recognizing Data Types
• Using Scalar Data Types
• Writing PL/SQL Executable Statements
• Nested Blocks and Variable Scope
• Good Programming Practices
Using SQL in PL/SQL
• Review of SQL DML
• Retrieving Data in PL/SQL
• Manipulating Data in PL/SQL
• Using Transaction Control Statements
Program Structures to Control Execution Flow
• Conditional Control: IF Statements
• Conditional Control: CASE Statements
• Iterative Control: Basic Loops
• Iterative Control: WHILE and FOR Loops
• Iterative Control: Nested Loops
Using Composite Datatypes
• User-Defined Records
• Indexing Tables of Records
Using Cursors and Parameters
• Introduction to Explicit Cursors
• Using Explicit Cursor Attributes
• Cursor FOR Loops
• Cursors with Parameters
• Using Cursors for UPDATE
• Using Multiple Cursors
Exception Handling
• Handling Exceptions
• Trapping Oracle Server Exceptions
• Trapping User-Defined Exceptions
• Recognizing the Scope of Exceptions
Using and Managing Procedures
• Creating Procedures
• Using Parameters in Procedures
• Passing Parameters
Using and Managing Functions
• Creating Functions
• Using Functions in SQL Statements
• Review of the Data Dictionary
• Managing Procedures and Functions
• Review of Object Privileges
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
3
• Using Invoker's Rights and Autonomous Transactions
Using and Managing Packages
• Creating Packages
• Managing Package Concepts
• Advanced Package Concepts
Getting the Best out of Packages
• Persistent State of Package Variables
• Using Oracle-Supplied Packages
Improving PL/SQL Performance
• Using Dynamic SQL
• Improving PL/SQL Performance
Using and Managing Triggers
• Introduction To Triggers
• Creating DML Triggers, Part I
• Creating DML Triggers, Part II
• Creating DDL and Database Event Triggers
• Managing Triggers
Recognizing and Managing Dependencies
• Introduction to Dependencies
• Understanding Remote Dependencies
Using the PL/SQL Compiler
• Using PL/SQL Initialization Parameters
• Displaying Compiler Warning Messages
• Using Conditional Compilation
• Hiding Your Source Code
To search and register for events scheduled in your area, visit the Academy events calendar.
Copyright © 2019, Oracle and/or its affiliates. All rights reserved. Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
