Data, Analytics & AI Category Banner Image

Oracle Database PL/SQL (OCPS)

Length 3 days
Price NZD 3500 exc GST

Course overview

View dates &
book now

Why study this course

Oracle Database PL/SQL is a complete, hands-on course presenting the fundamental principles of PL/SQL, the Oracle® database’s procedural language extension to SQL.

In this course, you will explore development environments, best practices, and design principles used to create reusable program units, such as procedures and functions used to implement business logic and provide additional data validation.

This course assumes basic SQL knowledge. Oracle Database SQL course or similar knowledge is a recommended prerequisite.

Request Course Information

What you’ll learn

After completing this course, students should be able to:

Identify the programming extensions provided by PL/SQL
Write code to interface with an Oracle database
Use PL/SQL programming constructs to implement business logic
Use built-in, and user-defined data types
Handle runtime errors
Create reusable program units
Understand PL/SQL best practices

Oracle at Lumify Work

Oracle offers a complete enterprise cloud designed to modernise your business, including cloud applications and cloud infrastructure services. Lumify Work offers courses in Oracle Database and web app development so you can utilise the world's leading converged, multimodel database management system in the cloud.

View all Oracle courses

Who is the course for?

Anyone seeking to learn the fundamental principles of PL/SQL, the Oracle database’s procedural language extension to SQL.

Course subjects

Introduction to PL/SQL
- About PL/SQL
- PL/SQL Block Structure
- Anonymous Blocks
- Enabling Output
Declaring PL/SQL Variables
- Variables
- Identifier name requirements
- Declaration and initialisation
- SQL and PL/SQL Data Types
- Data Type Conversion
Writing Executable Statements
- PL/SQL Block Syntax
- Comments
- Use of SQL Functions in PL/SQL
- Using Sequences in PL/SQL blocks
- Operators
Using SQL Statements within a PL/SQL block
- DML statements usage in PL/SQL
- Implicit cursors
- Cursor attributes SQL%FOUND, SQL%NOTFOUND, and SQL%ROWCOUNT
Writing Control Structures
- If Statement
- Case Expressions
- Basic Loop
- For Loop
- While Loop
- Continue and Exit
Working with Composite Data Types
- PL/SQL records
- %ROWTYPE attribute
- Introduction to Collections
Using Explicit Cursors
- Controlling Explicit Cursors
- Cursor declaration
- Fetching Data from cursors
- Cursor For Loops
- Explicit Cursor Attributes
Exceptions Handling
- Understanding Exception with PL/SQL
- Trapping an Exception
- EXCEPTION_INIT Pragma
- Predefined Exceptions
- RAISE_APPLICATION_ERROR Procedure
Stored Procedures
- Benefits of code modularisation
- Stored Procedure definition
- Creating and compiling procedures
- Calling a procedure
- IN, OUT, and IN OUT parameter modes
- Default parameters
Functions
- Functions definition
- Procedures and Functions comparison
- Creating and invoking a function
- Calling User-Defined functions in SQL statements
PL/SQL Packages
- Advantages of Packages
- Package specification
- Package body
- Invoking package subprograms
Database Triggers
- Defining triggers
- Trigger event types
- Using conditional predicates
- CALL statements in triggers
- Statement-level and row-level triggers
- Using OLD and NEW qualifiers
- INSTEAD OF triggers

Prerequisites

This course assumes basic SQL knowledge
Oracle Database SQL course or similar knowledge is recommended

Terms & Conditions

The supply of this course by Lumify Work is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.