Overview

Official Oracle Curriculum

This Oracle Database: PL/SQL Fundamentals training introduces you to PL/SQL. It helps you understand the benefits of this powerful programming language, while teaching you real-world scenarios where these lessons can be applied.

Learn To:

  • Describe the features and syntax of PL/SQL.
  • Design a PL/SQL anonymous block that executes efficiently.
  • Write PL/SQL code to interface with the database.
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors
  • Handle runtime errors.
  • Describe stored procedures and functions.
  • Apply declaring identifiers and trapping exceptions.
  • Create PL/SQL blocks of application code that can be shared by multiple forms, reports and data management applications.

Benefits to You

Ensure fast, reliable, secure and easy to manage performance. Optimize database workloads, lower IT costs and deliver a higher quality of service by enabling consolidation onto database clouds.

Gain Experience through Hands-On Practice

In this course, you'll learn from expert Oracle instructors, then engage in demonstrations and hands-on practice to reinforce the fundamental concepts. You'll use Oracle SQL Developer to develop these program units. SQL*Plus is introduced as optional tools.

Audience

  • PL/SQL Developer
  • System Analysts
  • Technical Consultant
  • Forms Developer
  • Portal Developer
Read more

Prerequisites

  • Familiarity with data processing concepts and technique
  • Familiarity with programming languages

Please note: In order to provision you with your courseware and lab access for this course QA must share several items of basic personal information with our partner (usually your full name and email address). For more information on this please visit our QA Partner data sharing page. If you have any questions or concerns please contact your QA account manager.

Read more

Delegates will learn how to

  • Describe the features and syntax of PL/SQL
  • Design PL/SQL anonymous block that execute efficiently
  • Write PL/SQL code to interface with the database
  • Use PL/SQL programming constructs and conditionally control code flow (loops, control structures, and explicit cursors)
  • Handle runtime errors
  • Create and execute simple stored procedures and functions
Read more

Outline

Introduction

  • Course Objectives, Course Agenda and Class Account Information
  • Describe the Human Resources (HR) Schema
  • Identify the Appendices Used in this Course
  • Introduction to PL/SQL Development Environments
  • Oracle Database 19c SQL Documentation and Additional Resources

Introduction to PL/SQL

  • Understanding the benefits and structure of PL/SQL
  • Examining PL/SQL Blocks
  • Generating output messages in PL/SQL

Declaring PL/SQL Variables

  • Identify valid and invalid identifiers
  • Declare and initialize variables
  • List and describe various data types
  • Identify the benefits of using the %TYPE attribute
  • Declare, use, and print bind variables

Writing Executable Statements

  • List Lexical Units in a PL/SQL Block
  • Use SQL Functions in PL/SQL
  • Use Data Type Conversion
  • Use Nested Blocks as Statements
  • Reference an Identifier Value in a Nested Block
  • Qualify an Identifier with a Label
  • Use Operators in PL/SQL
  • Using Sequences in PL/SQL Expressions

Using SQL Statements within a PL/SQL Block

  • Identify and use the SQL 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 and 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 Type
  • Inserting and Updating with PL/SQL Records
  • Use INDEX BY Tables to Hold Multiple Values of the Same Data Type

Using Explicit Cursors

  • Define Cursors
  • Explain Explicit Cursor Operations
  • Controlling Explicit Cursors
  • Use Explicit Cursors to Process Rows
  • Cursors and Records
  • Cursor FOR Loops Using Subqueries
  • Explicit Cursor Attributes
  • The %NOTFOUND and %ROWCOUNT Attributes

Handling Exceptions

  • Define Exception
  • Handling Exceptions with PL/SQL
  • Predefined Exceptions
  • Trapping Predefined and Non-predefined Oracle Server Errors
  • Functions that Return Information on Encountered Exceptions
  • Trapping User-Defined Exceptions
  • Propagate Exceptions
  • Basic RAISE Statement

Creating Stored Procedures and Functions

  • Overview of Stored Procedures and Functions
  • Differentiate between anonymous blocks and subprograms
  • Show the CREATE OR REPLACE PROCEDURE | FUNCTION
  • Understand the Header Area of a Stored Procedure and Function
  • Create Simple Procedures and Functions
  • Create a Simple Procedure with an IN Parameter
  • Execute a Procedure and a Function
Read more

Why choose QA

Special Notices

Please note that Oracle courses running at London training centres are subject to a 10% surcharge.

Please note: In order to provision you with your courseware and lab access for this course QA must share several items of basic personal information with our partner (usually your full name and email address). For more information on this please visit our QA Partner data sharing page. If you have any questions or concerns please contact your QA account manager.

Important Note: Your official Oracle digital course material now includes an additional 5 hours of content for you to consume after the course to help embed the learning. Your ELS will be accessible for 90 days from purchase.

Frequently asked questions

See all of our FAQs

How can I create an account on myQA.com?

There are a number of ways to create an account. If you are a self-funder, simply select the "Create account" option on the login page.

If you have been booked onto a course by your company, you will receive a confirmation email. From this email, select "Sign into myQA" and you will be taken to the "Create account" page. Complete all of the details and select "Create account".

If you have the booking number you can also go here and select the "I have a booking number" option. Enter the booking reference and your surname. If the details match, you will be taken to the "Create account" page from where you can enter your details and confirm your account.

Find more answers to frequently asked questions in our FAQs: Bookings & Cancellations page.

How do QA’s virtual classroom courses work?

Our virtual classroom courses allow you to access award-winning classroom training, without leaving your home or office. Our learning professionals are specially trained on how to interact with remote attendees and our remote labs ensure all participants can take part in hands-on exercises wherever they are.

We use the WebEx video conferencing platform by Cisco. Before you book, check that you meet the WebEx system requirements and run a test meeting (more details in the link below) to ensure the software is compatible with your firewall settings. If it doesn’t work, try adjusting your settings or contact your IT department about permitting the website.

Learn more about our Virtual Classrooms.

How do QA’s online courses work?

QA online courses, also commonly known as distance learning courses or elearning courses, take the form of interactive software designed for individual learning, but you will also have access to full support from our subject-matter experts for the duration of your course. When you book a QA online learning course you will receive immediate access to it through our e-learning platform and you can start to learn straight away, from any compatible device. Access to the online learning platform is valid for one year from the booking date.

All courses are built around case studies and presented in an engaging format, which includes storytelling elements, video, audio and humour. Every case study is supported by sample documents and a collection of Knowledge Nuggets that provide more in-depth detail on the wider processes.

Learn more about QA’s online courses.

When will I receive my joining instructions?

Joining instructions for QA courses are sent two weeks prior to the course start date, or immediately if the booking is confirmed within this timeframe. For course bookings made via QA but delivered by a third-party supplier, joining instructions are sent to attendees prior to the training course, but timescales vary depending on each supplier’s terms. Read more FAQs.

When will I receive my certificate?

Certificates of Achievement are issued at the end the course, either as a hard copy or via email. Read more here.

Contact Us

Please contact us for more information