Managed Business & IT Training Solutions

MBIT Training Ltd

Introduction to Oracle 12c PL/SQL Programming

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.
SKU: O12cPL
Manufacturer: Oracle
£1,690.00 (GBP)
£1,200.00 (GBP)

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 courseware 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.

Audience: Application developers and database administrators.

Prerequisites: A good working knowledge of Oracle 11g SQL features is required. Programming experience in a high-level language, such as COBOL™, Java™, or Perl is also required.

Course content:

1. Course Introduction

Course Objectives

Course Overview

Using the Workbook

Suggested References

Sample Database

2. 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

3. 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

4. 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

5. 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

6. Exception Handling

SQLCODE and SQLERRM

Exception Handlers

Nesting Blocks

Scope and Name Resolution

Declaring and Raising Named Exceptions

User-Defined Exceptions

7. Records, Collections, and UserDefined 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

8. 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

9. Bulk Operations

Bulk Binding

BULK COLLECT Clause

FORALL Statement

FORALL Variations

Bulk Returns

Bulk Fetching with Cursors

10. 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

11. 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

12. Working with LOBs

Large Object Types

Oracle Directories

LOB Locators

Internal LOBs

LOB Storage and SECUREFILEs

External LOBs

Temporary LOBs

The DBMS_LOB Package

13. 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

Appendix A: Dynamic SQL

Generating SQL at Runtime

Native Dynamic SQL vs. DBMS_SQL Package

The EXECUTE IMMEDIATE Statement

Using Bind Variables

Multi-row Dynamic Queries

Bulk Operations with Dynamic SQL

Using DBMS_SQL

DBMS_SQL Subprograms

Appendix B:

PL/SQL Versions, Datatypes and Language Limits