Introduction to Oracle 12c PL/SQL Programming

This course has been superseded

We suggest the following instead:
Oracle Database - PL/SQL I Introduction

Course Description

A Relational Database Management System (RDBMS) is a software system that allows you to create and manage a relational database. Minimum requirements for such a system are defined by both ANSI and ISO. PL/SQL is Oracle's Procedural Language for SQL. It is Oracle's database programming language for creating stored procedures, functions, database triggers, and object methods. PL/SQL can be used for implementing business rules, computing algorithms, manipulating data, and for stand-alone programs. Oracle 12c focuses on Oracle's cloud-based offerings with a new architecture Plugable Databases (PDBs) designed for rapid deployment and migration in a multi-tenant environment. This course is designed to teach both end-users and developers accessible but powerful SQL query and data manipulation (DML) skills, then teach critical programming and optimization techniques using advanced PL/SQL features.
3 days
Contact us for pricing
 

Prerequisites

A good working knowledge of Oracle 12c SQL features is required. Programming experience in a high-level language, such as COBOL, Java, or Perl is also required.

Course Introduction

Course Objectives
Course Overview
Using the Workbook
Suggested References
Sample Database

Triggers

Beyond Declarative Integrity
Triggers
Types of Triggers
Trigger Sequencing
Row-Level Triggers
Trigger Predicates
Trigger Conditions
Using SEQUENCEs
Cascading Triggers and Mutating Tables
Generating an Error
Maintaining Triggers

PL/SQL Variables and Datatypes

Anonymous Blocks
Declaring Variables
Datatypes
Subtypes
Character Data
Dates and Timestamps
Date Intervals
Anchored Types
Assignment and Conversions
Selecting into a Variable
Returning into a Variable

PL/SQL Syntax and Logic

Conditional Statements IF/THEN
Conditional Statements CASE
Comments and Labels
Loops
WHILE and FOR Loops
SQL in PL/SQL
Local Procedures and Functions

Stored Procedures and Functions

Stored Subprograms
Creating a Stored Procedure
Procedure Calls and Parameters
Parameter Modes
Named Parameter Notation
Default Arguments
Creating a Stored Function
Stored Functions and SQL
Invoker's Rights

Exception Handling

SQLCODE and SQLERRM
Exception Handlers
Nesting Blocks
Scope and Name Resolution
Declaring and Raising Named Exceptions
User-Defined Exceptions

Records, Collections, and User-Defined Types

Record Variables
Using the %ROWTYPE Attribute
User-Defined Object Types
VARRAY and Nested TABLE Collections
Using Nested TABLEs
Using VARRAYs
Collections in Database Tables
Associative Array Collections
Collection Methods
Iterating Through Collections

Cursors

Multi-Row Queries
Declaring and Opening Cursors
Fetching Rows
Closing Cursors
The Cursor FOR Loop
FOR UPDATE Cursors
Cursor Parameters
The Implicit (SQL) Cursor

Bulk Operations

Bulk Binding
BULK COLLECT Clause
FORALL Statement
FORALL Variations
Bulk Returns
Bulk Fetching with Cursors

Using Packages

Packages
Oracle-Supplied Packages
The DBMS_OUTPUT Package
The DBMS_UTILITY Package
The UTL_FILE Package
The DBMS_METADATA Package
XML Packages
Networking Packages
Other Supplied Packages

Creating Packages

Structure of a Package
The Package Interface and Implementation
Package Variables and Package State
Overloading Package Functions and Procedures
Forward Declarations
Strong REF CURSOR Variables
Weak REF CURSOR Variables

Working with LOBs

Large Object Types
Oracle Directories
LOB Locators
Internal LOBs
LOB Storage and SECUREFILEs
External LOBs
Temporary LOBs
The DBMS_LOB Package

Maintaining PL/SQL Code

Privileges for Stored Programs
Data Dictionary
PL/SQL Stored Program Compilation
Conditional Compilation
Compile-Time Warnings
The PL/SQL Execution Environment
Dependencies and Validation
Maintaining Stored Programs
Oracle 12cOracle ProgrammingOracle Database 12cOracle PL/SQLOracle PL/SQL Programming