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.
Duration: 2 days
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