Course Outline

Oracle 19c PL/SQL Fundamentals (TTOR12019)

Duration
3 Days
Price
1.00 ج.م.‏
Course Type
No Location
Oracle 19c PL/SQL Fundamentals is a three-day, hands-on course that introduces Oracle database programming using the PL/SQL programming language. Throughout the course students will explore the core syntax, structure and features of the language. This course will also lay the foundation for the entire 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. The course also explores applying the newly learned skills to the development of database applications. Participants will learn how to use database-resident stored program units such as procedures, functions, packages and database triggers. Students will also learn about the latest features in Oracle 19c.

Learning Objectives

Working within in a hands-on learning environment, guided by our expert team, attendees will develop a practical approach to Oracle Database Technology. Throughout the course participants will explore:

  • Using PL/SQL programming language for database applications and development
  • incorporating PL/SQL modules within the application architecture from the initial design and planning phase
  • The essentials of building executable PL/SQL program units
  • Each of the major segments of a working program and how these interact with each other during program execution
  • Important error or exception handling capabilities of the language.
  • How database-resident program units can be used as part of the overall database application architecture
  • Applying these new skills to the development of PL/SQL packages.
  • Advanced database programming capabilities and benefits
  • How database triggers can be used as part of an advanced database application design

Course Outline

1 - 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 and Substitution Variables
  • Using SQL Developer
  • Using SQL *Plus
2 - Choosing a Database Programming Language
  • What is Database Programming
  • PL/SQL Programming
  • PL/SQL Performance Advantages
  • Integration with Other Languages
3 - PL/SQL Language Essentials
  • PL/SQL Program Structure
  • Language Syntax Rules
  • Embedding SQL
  • Writing Readable Code
  • Generating Readable Code
  • Generating Database Output
  • SQL * Plus Input of Program Block
4 - 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
5 - BEGIN Section
  • About the Begin Section
  • Manipulating Program Data
  • Logic Control & Braching
  • GOTO
  • LOOP
  • IF-THEN-ELSE
  • CASE
6 - EXCEPTION Section
  • About the Exception Section
  • Isolating the Specific Exception
  • Pragma Exception_INIT
  • SQLCODE &SQLERRM Example
  • SQL%ROWCOUNT & Select … Into
7 - Beyond the Basics : Explicit Cursors
  • About Explicit Cursors
  • Extend Cursor Techniques
  • For Update of Clause
  • Where Current of Clause
  • Using for … Loop Cursors
8 - Introduction Database Resident Programming Units
  • About Database – Resident Programs
  • Physical Storage & Execution
  • Types of Stored Program Units
  • Stored Program Unit Advantages
  • Modular Design Principles
9 - Creating Stored Procedures and 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
10 - Executing Stored Procedures and Functions
  • Calling Procedures & Functions
  • Unit Testing with Execute
  • Anonymous Block Unit Testing
  • Specifying a Parameter Notation
  • SQL Worksheet Unit Testing
  • Calling Functions from SQL
11 - Maintaining Stored Programming Units
  • Recompiling Programs
  • Mass Recompilation Using UTL_RECOMP()
  • Dropping Procedures & Functions
  • Drop Procedures & Functions
  • Drop Procedure / Function
  • Data Dictionary Metadata
  • Using USER_OBJECTS
  • Using USER_SOURCE
  • Using USER_ERRORS
  • Using USER_OBJECT_SIZE
  • Using USER_DEPENDENCIES
12 - Managing Dependencies
  • Dependency Internals
  • Tracking Dependencies
  • The Dependency Tracking Utility
  • SQL Developer Dependency Info
  • Dependency Strategy Checklists
13 - Creating & Maintaining
  • About Packages
  • Creating Packages
  • Maintaining Packages
  • Performance Considerations
14 - Advanced Package Capabilities
  • Definer & Invoker Rights
  • White Lists & Accessible By
  • Persistent Global Objects
  • Defining Initilization Logic
  • Object Orientation Support
15 - Advanced Cursor Techniques
  • Using Cursor Variables
  • Using SYS_REFCURSOR
  • Using Cursor Expressions
16 - Using System Supplied Packages
  • DBMS_OUTPUT()
  • UTL_FILE()
  • FOPEN() Example
17 - Database Trigger Concepts
  • About Database Triggers
  • DML Event Trigger Sub-Types
  • Database Trigger Scenario
  • Trigger Exhaustion Mechanisms
  • Trigger within SQL Worksheet
18 - 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 and Application
19 - Maintaining Database Triggers
  • Call Syntax
  • Trigger Maintenance Tasks
  • Show Errors Trigger
  • Drop Trigger
  • Alter Trigger
  • Multiple Triggers for a Table
  • Handling Mutating Table Issues
20 - Implementing System Event Triggers
  • What are System Event Triggers
  • Defining the Scope
  • Available System Events
  • System Event Attributes

Target Audience

This course is geared for anyone needing to interface with an Oracle database such as end users, business analysts, application developers and database administrators / DBAs.

Upcoming Class Dates and Times
Login