Introduction to Oracle 12c PL/SQL Programming

Request more details:

submit request
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 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.
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