Oracle Database: PL/SQL Fundamentals

 

Course Description:

  The Oracle Database 12c: PL/SQL Fundamentals course is a 5-day course that is designed to teach students Oracle database programming using the PL/SQL programming language. Students will learn the syntax, structure and features of the language.

The course will also provide a foundation for the entire Sideris Oracle PL/SQL programming series, allowing one to progress from introductory topics to advanced application design and programming and finally onto writing complex high-performance applications.

Students will also learn the use of database-resident stored program units such as procedures, functions, packages and database triggers. New features introduced with the Oracle 12c release of the database are also discussed and demonstrated.

This course is taught using version 12c, but the topics are applicable for either version 11g or 12c.

Topics covered in the course include:

• SELECTION & SETUP OF THE DATABASE INTERFACE
• ABOUT BIND & SUBSTITUTION VARIABLES
• CHOOSING A DATABASE PROGRAMMING LANGUAGE
• PL/SQL LANGUAGE FUNDAMENTALS
• DECLARE SECTION
• BEGIN SECTION
• EXCEPTION SECTION
• EXPLICIT CURSORS
• DATABASE-RESIDENT PROGRAM UNITS
• CREATING STORED PROCEDURES & FUNCTIONS
• EXECUTING STORED PROCEDURES & FUNCTIONS
• MAINTAINING STORED PROGRAM UNITS
• MANAGING DEPENDENCIES
• CREATING & MAINTAINING PACKAGES
• ADVANCED PACKAGE CAPABILITIES
• ADVANCED CURSOR TECHNIQUES
• USING SYSTEM-SUPPLIED PACKAGES
• DATABASE TRIGGER CONCEPTS
• CREATING DATABASE TRIGGERS
• MAINTAINING DATABASE TRIGGERS
• IMPLEMENTING SYSTEM EVENT TRIGGERS

Target Student:This course is intended for Oracle professionals, both business and systems professionals.

Students should possess the following skills and experience before attending the course:

• have taken the Oracle Database 12c: SQL Fundamentals class or have quaivalent knowledge.

 

SELECTION & SETUP OF THE DATABASE INTERFACE

CONSIDERING AVAILABLE TOOLS

SELECTING THE APPROPRIATE TOOL

ORACLE NET DATABASE CONNECTIONS

ORACLE PAAS DATABASE CONNECTIONS

SETUP SQL DEVELOPER

SETUP SQL*PLUS

SETUP JDEVELOPER

UNDERSTANDING BIND & SUBSTITUTION VARIABLES

USING SQL DEVELOPER

USING SQL*PLUS

CHOOSING A DATABASE PROGRAMMING LANGUAGE

WHAT IS DATABASE PROGRAMMING?

PL/SQL PERFORMANCE ADVANTAGES

INTEGRATION WITH OTHER LANGUAGES

PL/SQL LANGUAGE FUNDAMENTALS

PL/SQL PROGRAM STRUCTURE

LANGUAGE SYNTAX RULES

EMBEDDING SQL

WRITING READABLE CODE

GENERATING DATABASE OUTPUT

SQL*PLUS INPUT OF A PROGRAM BLOCK

DECLARE SECTION

UNDERSTANDING THE DECLARE SECTION

DECLARE PRIMITIVE TYPES

DECLARATION OPTIONS

NOT NULL

CONSTANT

DATA DICTIONARY INTEGRATION

%TYPE

DECLARE SIMPLE USER-DEFINED TYPES

TYPE ... TABLE

TYPE ... RECORD

EXTENDED USER-DEFINED TYPES

BEGIN SECTION

UNDERSTANDING THE BEGIN SECTION

MANIPULATING PROGRAM DATA

LOGIC CONTROL & BRANCHING

GOTO

LOOP

IF-THEN-ELSE

CASE

EXCEPTION SECTION

UNDERSTANDING THE EXCEPTION SECTION

ISOLATING THE SPECIFIC EXCEPTION

PRAGMA EXCEPTION_INIT

SQLCODE & SQLERRM Example

SQL%ROWCOUNT & SELECT...INTO

BEYOND THE BASICS: EXPLICIT CURSORS

UNDERSTANDING EXPLICIT CURSORS

EXTENDED CURSOR TECHNIQUES

FOR UPDATE OF Clause

WHERE CURRENT OF Clause

Using FOR…LOOP Cursors

INTRODUCING DATABASE-RESIDENT PROGRAM UNITS

UNDERSTANDING DATABASE-RESIDENT PROGRAMS

PHYSICAL STORAGE & EXECUTION

TYPES OF STORED PROGRAM UNITS

STORED PROGRAM UNIT ADVANTAGES

MODULAR DESIGN PRINCIPLES

CREATING STORED PROCEDURES & FUNCTIONS

STORED PROCEDURES & FUNCTIONS

CREATE PROCEDURE / CREATE FUNCTION

CREATING PROCEDURES & FUNCTIONS

RAISE_SALARY() Procedure

SALARY_VALID() Function

THE PARAMETER SPECIFICATION

DEFAULT Clause

SYSTEM & OBJECT PRIVILEGES

USING THE DEVELOPMENT TOOLS

EXECUTING STORED PROCEDURES & FUNCTIONS

CALLING PROCEDURES & FUNCTIONS

UNIT TESTING WITH EXECUTE

ANONYMOUS BLOCK UNIT TESTING

SPECIFYING A PARAMETER NOTATION

SQL WORKSHEET UNIT TESTING

CALLING FUNCTIONS FROM SQL

MAINTAINING STORED PROGRAM UNITS

RECOMPILING PROGRAMS

Mass Recompilation Using UTL_RECOMP()

DROPPING PROCEDURES & FUNCTIONS

DROP PROCEDURE / FUNCTION

DATA DICTIONARY METADATA

Using USER_OBJECTS

Using USER_SOURCE

Using USER_ERRORS

Using USER_OBJECT_SIZE

Using USER_DEPENDENCIES

MANAGING DEPENDENCIES

DEPENDENCY INTERNALS

TRACKING DEPENDENCIES

THE DEPENDENCY TRACKING UTILITY

SQL DEVELOPER DEPENDENCY INFO

DEPENDENCY STRATEGY CHECKLISTS

CREATING & MAINTAINING PACKAGES

UNDERSTANDING PACKAGES

CREATING PACKAGES

MAINTAINING PACKAGES

PERFORMANCE CONSIDERATIONS

ADVANCED PACKAGE CAPABILITIES

DEFINER & INVOKER RIGHTS

WHITE LISTS & ACCESSIBLE BY

PERSISTENT GLOBAL OBJECTS

DEFINING INITIALIZATION LOGIC

OBJECT ORIENTATION SUPPORT

ADVANCED CURSOR TECHNIQUES

USING CURSOR VARIABLES

Using SYS_REFCURSOR

USING CURSOR EXPRESSIONS

USING SYSTEM-SUPPLIED PACKAGES

DBMS_OUTPUT()

UTL_FILE()

FOPEN() Example

DATABASE TRIGGER CONCEPTS

UNDERSTANDING DATABASE TRIGGERS

DML EVENT TRIGGER SUB-TYPES

DATABASE TRIGGER SCENARIO

TRIGGER EXECUTION MECHANISMS

TRIGGERS WITHIN SQL WORKSHEET

CREATING DATABASE TRIGGERS

STATEMENT-LEVEL TRIGGERS

Using RAISE_APPLICATION_ERROR()

ROW-LEVEL TRIGGERS

EXAMPLES OF TRIGGERS

EMPLOYEE_SALARY_CHECK Example

EMPLOYEE_JOURNAL Example

BUDGET_EVENT Example

INSTEAD OF TRIGGERS

TRIGGERS WITHIN AN APPLICATION

MAINTAINING DATABASE TRIGGERS

CALL SYNTAX

TRIGGER MAINTENANCE TASKS

SHOW ERRORS TRIGGER

DROP TRIGGER

ALTER TRIGGER

MULTIPLE TRIGGERS FOR A TABLE

HANDLING MUTATING TABLE ISSUES

IMPLEMENTING SYSTEM EVENT TRIGGERS

WHAT ARE SYSTEM EVENT TRIGGERS?

DEFINING THE SCOPE

AVAILABLE SYSTEM EVENTS

SYSTEM EVENT ATTRIBUTES

 

 

 

We offer live-instructor group training classes for your team.

Upskill your team from the comfort of your office or online.

Benefits of Group Training Include:

• Experienced Professional Instructor Trains Your Team
• Content Focused On Your Team's Needs
• Convenient Scheduling and Class Setup
• Significant Per/Student Cost Savings
• Online, On-Site and Blended Options Available

Get A Free Consultation