Oracle Database 12c - PL/SQL Fundamentals

Price
$2,695.00
Duration
 5 Days
Delivery Methods
 VILT    Private Group

In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c.

 

Upcoming Class Dates and Times

Aug 02  - Aug 06, 2021
8:00AM - 4:00PM Central
5 Days
Virtual Instructor Led
 GTR

Who Should Attend

This course is intended for individuals who are Application designers and database developers, database administrators and web server administrators.

Agenda

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
2 - About Bind & Substitution Variables
  • Using SQL Developer
  • Using SQL*Plus
3 - Choosing a Database Programming Language
  • What is Database Programming?
  • PL/SQL Performance Advantages
  • Integration wth Other Languages
4 - 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
5 - 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
6 - Begin Section
  • About the Begin Section
  • Manipulating Program Data
  • Logic Control & Branching
  • GOTO
  • LOOP
  • IF-THEN-ELSE
  • CASE
7 - Exception Section
  • ABOUT THE EXCEPTION SECTION
  • ISOLATING THE SPECIFIC EXCEPTION
  • PRAGMA EXCEPTION_INIT
  • SQLCODE &SQLERRM Example
  • SQL%ROWCOUNT &SELECT...INTO
8 - Beyond the Basics: Explicit Cursors
  • ABOUT EXPLICIT CURSORS
  • EXTENDED CURSOR TECHNIQUES
  • FOR UPDATE OF Clause
  • WHERE CURRENT OF Clause
  • Using FOR…LOOP Cursors
9 - Beyond the Basics: Nested Blocks
10 - Beyond the Basics: Declared Subprograms
  • USING DECLARED SUBPROGRAMS
  • DECLARED PROCEDURE
  • DECLARED FUNCTION
11 - Introducing Databse-Resident Program Units
  • ABOUT DATABASE---RESIDENT PROGRAMS
  • PHYSICAL STORAGE & EXECUTION
  • TYPES OF STORED PROGRAM UNITS
  • STORED PROGRAM UNIT ADVANTAGES
  • MODULAR DESIGN PRINCIPLES
12 - 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
13 - 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
14 - 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
15 - Managing Dependencies
  • DEPENDENCY INTERNALS
  • TRACKING DEPENDENCIES
  • THE DEPENDENCY TRACKING UTILITY
  • SQL DEVELOPER DEPENDENCY INFO
  • DEPENDENCY STRATEGY CHECKLISTS
16 - Creating & Maintaining Packages
  • ABOUT PACKAGES
  • CREATING PACKAGES
  • MAINTAINING PACKAGES
  • PERFORMANCE CONSIDERATIONS
17 - Advanced Package Capabilities
  • DEFINER & INVOKER RIGHTS
  • WHITE LITS & ACCESSIBLE BY
  • PERSISTENT GLOBAL OBJECTS
  • DEFINING INITIALIZATION LOGIC
  • OBJECT ORIENTATION SUPPORT
18 - Advanced Cursor Techniques
  • USING CUSROS VARIABLES
  • USING SYS_REFCURSOR
  • USING CURSOR EXPRESSIONS
19 - Using System-Supplied Package
  • DBMS_OUTPUT()
  • UTL_FILE()
  • FOPEN() EXAMPLE
20 - Database Trigger Concepts
  • 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
22 - Maintaining Database Triggers
  • CALL SYNTAX
  • TRIGGER MAINTENANCE TASKS
  • SHOW ERRORS TRIGGER
  • DROP TRIGGER
  • ALTER TRIGGER
  • MULTIPLE TRIGGERS FOR A TABLE
  • HANDLING MUTATING TABLE ISSUES
23 - Implementing System Event Triggers
  • WHAT ARE SYSTEM EVENT TRIGGERS?
  • DEFININGTHE SCOPE
  • AVAILABLE SYSTEM EVENTS
  • SYSTEM EVENT ATTRIBUTES
 

Do You Have Additional Questions? Please Contact Us Below.

contact us contact us 
Contact Us about Starting Your Business Training Strategy with United Training