Oracle Database: PL/SQL I Introduction

Course Description

This course introduces participants to Oracle database programming using the PL/SQL programming language. You will learn the syntax, structure and features of the language. This course will also lay the foundation to allow you to progress from introductory topics to advanced application design and programming and finally onto writing complex high-performance applications. PL/SQL may be considered as one of the key skill sets required for any Oracle professional, nearly as important as the SQL language itself.
2-3 days
Contact us for pricing
 

Prerequisites

Either of the courses Oracle Database 19c: SQL - Fundamentals (Levels I and II) or Oracle Database 19c: SQL Complete Library (Levels I, II and III) are recommended prerequisites for this course.

Audience

The target audience for this course is all Oracle professionals. Among the specific groups for whom this course will be helpful are:

Application designers and database developers
Database administrators
Web server administrators

Objectives

This course begins with an explanation of the intent and usage of the PL/SQL programming language for database applications. Important reasons why one should incorporate PL/SQL modules within the application architecture right from the initial design and planning phase are presented. Next you will learn how to begin building executable PL/SQL program units. You learn about each of the major segments of a working program and how these interact with each other during program execution, including the important error or exception handling capabilities of the language. The final section goes beyond the basics and begins to explore advanced topics that will be useful later in the Oracle PL/SQL developer course. Techniques and features that allow modular and reusable programs to be developed are covered, increasing productivity and maintainability of database applications.

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

ABOUT 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

ABOUT 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

ABOUT THE BEGIN SECTION
MANIPULATING PROGRAM DATA
LOGIC CONTROL & BRANCHING
GOTO
LOOP
IF-THEN‐ELSE
CASE

EXCEPTION SECTION

ABOUT THE EXCEPTION SECTION
ISOLATING THE SPECIFIC EXCEPTION
PRAGMA EXCEPTION_INIT
SQLCODE & SQLERRM Example
SQL%ROWCOUNT & SELECT...INTO

BEYOND THE BASICS: EXPLICIT CURSORS

ABOUT EXPLICIT CURSORS
EXTENDED CURSOR TECHNIQUES
FOR UPDATE OF Clause
WHERE CURRENT OF Clause
Using FOR LOOP Cursors

BEYOND THE BASICS: NESTED BLOCKS

BEYOND THE BASICS: DECLARED SUBPROGRAMS

USING DECLARED SUBPROGRAMS
DECLARED PROCEDURE
DECLARED FUNCTION
OracleOracle 19cOracle Database 19cOracle PL/SQLOracle 21c