VTEC Training
Course Code: 5617
Oracle Database 12c: PL/SQL Fundamentals I and II
VTEC Training
Portland,
ME
Class Dates:
6/24/2019
8/26/2019
Length:
5 Days
Cost:
$3295.00
Class Time:
Technology:
Database
Delivery:
Overview
- Course Overview
- This 5 day training 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 one learns how to begin building executable PL/SQL program units. One learns 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.
Part 2 - Explains how database-resident program units can be used as part of the overall database application architecture and the benefits realized by doing so. It then builds upon one's knowledge of database-resident program units and applies these to the development of PL/SQL packages.
- Audience
- The target audience for this textbook is all Oracle professionals. Among the specific groups for whom this textbook will be helpful are:
• Application designers and database developers
• Database administrators
• Web server administrators
Subjects applicable to certification as both an Oracle PL/SQL Developer Certified Associate (OCA) and an Oracle Advanced PL/SQL Developer Certified Professional (OCP). The topics considered are included within "Exam 1Z0-144: Program With PL/SQL".
Prerequisites
- Oracle Database 12c: SQL - Fundamentals (Levels I & II) or Oracle Database 12c: SQL (Levels I, II & III) are recommended.
Recommended Courses:
Course Details
- 12c PL/SQL I
- 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
- PL/SQL II – Intermediate: Develop Program
- Introducing Database-Resident Program Units
- About 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
- Creating & Maintaining Packages
- About 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
- About 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