Oracle 19C PL/SQL FUNDAMENTALS | Introduction to PL/SQL

Start Date: January 01, 1970 – 12:00 AM (Pacific Time)

End Date: January 01, 1970 – 12:00 AM (Pacific Time)

$2,145.00

*Looking for flexible schedule (after hours or weekend)? Please call or email us: 858-208-4141 or sales@ccslearningacademy.com.
Student financing options are available.

Transitioning military and Veterans, please contact us to sign up for a free consultation on training and hiring options.
Qty:
Category:

Download PDF of Course Details

Course Description:

Oracle 19C PL/SQL Fundamentals provides a complete, hands-on, comprehensive introduction to PL/SQL including the use of both SQL Developer and SQL Plus.

Course Topics

PL/SQL PROGRAM STRUCTURE

  • PL/SQL VS. SQL
  • PL/SQL ENGINES AVAILABLE
  • ANONYMOUS PL/SQL BLOCK STRUCTURE
  • OBJECT NAMING RULES
  • VARIABLE DECLARATIONS
  • AVAILABLE DATATYPES
  • Scalar Datatypes
  • Using Extended Datatypes
  • Object Types
  • EXECUTABLE STATEMENTS
  • EXPRESSIONS
  • BLOCK LABELING
  • VARIABLE SCOPING RULES
  • COMMENTS IN PROGRAMS AND SCRIPTS
  • BASIC CODING STANDARDS

PL/SQL FLOW CONTROL

  • CONDITIONAL CONTROL
  • COMPARISON OPERATORS
  • LOGICAL OPERATORS
  • Truth Tables
  • REPETITION CONTROL
  • The Simple Loop
  • WHILE Loop
  • FOR Loop
  • CONTINUE Statements In Loops
  • Step Loops
  • THE GOTO STATEMENT
  • CASE EXPRESSIONS / STATEMENTS
  • BIND VARIABLES
  • SUBSTITUTION VARIABLES

SQL DEVELOPER AND PL/SQL

  • SQL DEVELOPER AND PL/SQL
  • CREATING AND EXECUTING SCRIPTS

SELECT INTO

  • SELECTING SINGLE ROWS OF DATA
  • ANCHORING VARIABLES TO DATATYPES
  • DML IN PL/SQL
  • RETURNING INTO
  • SEQUENCES IN PL/SQL
  • TRANSACTION CONTROL IN PL/SQL
  • AUTONOMOUS TRANSACTIONS

THE PL/SQL CURSOR

  • DECLARING EXPLICIT CURSORS
  • OPENING AND CLOSING EXPLICIT CURSORS
  • USING EXPLICIT CURSORS TO RETRIEVE VALUES
  • EXPLICIT CURSOR ATTRIBUTES
  • USING A LOOP WITH AN EXPLICIT CURSOR
  • USING PERCENT ROWTYPE WITH CURSORS
  • THE CURSOR FOR LOOP
  • DBMS OUTPUT

OPTIMIZATION

  • TIMING PL/SQL
  • FOR UPDATE / WHERE CURRENT OF

PL/SQL EXCEPTION HANDLING

  • THE EXCEPTION SECTION
  • ORACLE NAMED EXCEPTIONS
  • PRAGMA EXCEPTION_INIT
  • USER DEFINED EXCEPTIONS
  • The Scope Of User-Defined Exceptions
  • RAISING NAMED EXCEPTIONS
  • EXCEPTION PROPAGATION
  • RAISING AN EXCEPTION AGAIN
  • LIFE AFTER AN EXCEPTION
  • WHEN OTHERS
  • TAKING YOUR BALL AND GOING HOME
  • DBMS_ERRLOG

STORED PROCEDURES

  • PROCEDURES
  • BENEFITS OF STORED PROCEDURES
  • Database Security
  • Performance
  • Productivity
  • Portability
  • PARAMETERS AND STORED PROCEDURES
  • Parameter Notation
  • STORED OBJECT CREATION
  • Syntax For Creating A Procedure
  • COMPILATION ERRORS
  • VIEWING COMPILED CODE
  • DROPPING A PROCEDURE
  • THE ALTER COMMAND AND STORED PROCEDURES

CREATING FUNCTIONS IN PL/SQL

  • FUNCTIONS
  • PURITY LEVELS
  • USING WHITE LISTS
  • OPTIMIZATIONS
  • PARALLEL ENABLE
  • DETERMINISTIC FUNCTIONS
  • PL/SQL RESULT CACHE
  • NOCOPY
  • DBMS OUTPUT IN FUNCTIONS
  • USING THE WITH CLAUSE FOR FUNCTIONS
  • PRAGMA UDF
  • PRAGMA INLINE
  • USING SQL DEVELOPER WITH STORED PROCEDURES
  • DEBUGGING

PACKAGES

  • CREATING PACKAGES
  • PACKAGE BENEFITS
  • Security
  • Persistent State
  • I/O Efficiency
  • A SIMPLE PACKAGE
  • OVERLOADING
  • BODILESS PACKAGES
  • SOURCE CODE ENCRYPTION
  • CREATING PACKAGES FROM PROCEDURES AND FUNCTIONS

CREATING DML TRIGGERS

  • DML TRIGGERS
  • DML TRIGGER STRUCTURE
  • CONDITIONAL TRIGGERING PREDICATES
  • TRIGGERS FOR BUSINESS RULES ENFORCEMENT
  • MUTATING AND CONSTRAINING TABLES
  • COMPOUND TRIGGERS
  • CONTROLLING FIRING ORDER
  • DDL FOR TRIGGERS
  • VIEWING TRIGGER SOURCE
  • INSTEAD OF TRIGGERS

ADVANCED CONCEPTS

  • EMBEDDED PROCEDURES
  • THE OPTIMIZING COMPILER
  • PL/SQL COMPILER WARNINGS
  • COMPILING FOR DEBUGGING
  • CONDITIONAL COMPILATION / INQUIRY DIRECTIVES
  • Error Directives
  • Inquiry Directives
  • Using Static Constants
  • DBMS DB VERSION
  • NATIVE COMPILATION
  • Recompiling All Database Objects

FILE OPERATIONS

  • MOVING FILES BETWEEN DATABASES
  • DIRECTORY ACCESS
  • FILE MANIPULATION
  • FCLOSE Procedure
  • FCLOSE ALL Procedure
  • FCOPY Procedure
  • FFLUSH Procedure
  • FGETATTR Procedure
  • FGETPOS Function
  • FOPEN Function
  • FREMOVE Procedure
  • FRENAME Procedure
  • FSEEK Procedure
  • GET_LINE Procedure
  • GET_RAW Procedure
  • IS_OPEN Function
  • NEW_LINE Procedure
  • PUT Procedure
  • PUT_LINE Procedure
  • PUTF Procedure
  • PUT_RAW Procedure

COLLECTIONS

  • DEFINING RECORDS
  • COLLECTIONS
  • Associative Arrays
  • Nested Tables
  • VARRAYs / VARYING ARRAYs
  • Assignments
  • Comparing Collections
  • COLLECTION METHODS
  • EXISTS
  • FIRST
  • LAST
  • COUNT
  • LIMIT
  • PRIOR
  • NEXT
  • DELETE
  • TRIM
  • SET THEORY AND NESTED TABLES

BULK OPERATIONS

  • BULK BINDING
  • FORALL
  • SQL%BULK_ROWCOUNT
  • SAVE EXCEPTIONS / SQL%BULK_EXCEPTIONS
  • BULK COLLECT
  • The LIMIT Clause
  • FORALL And The INDICES OF Clause
  • FORALL And VALUES OF
  • PIPELINED TABLE FUNCTIONS
  • MULTIDIMENSIONAL COLLECTIONS

What You'll Learn

Oracle 19C PL/SQL Fundamentals provides a complete, hands-on, comprehensive introduction to PL/SQL including the use of both SQL Developer and SQL Plus.

Target Audience

This course is appropriate for anyone needing to interface with an Oracle database or those needing a general understanding of Oracle database functionality. That would include end users, business analysts, application developers and database administrators.

Prerequisites

Attendees should have basic computer skills. A basic knowledge of databases is helpful, but not required.

Inclusions

With CCS Learning Academy, you’ll receive:

  • Certified Instructor-led training
  • Official Training Seminar Student Handbook
  • Pre and Post assessments/evaluations
  • Collaboration with classmates (not currently available for self-paced course)
  • Real-world learning activities and scenarios
  • Exam scheduling support*
  • Enjoy job placement assistance for the first 12 months after course completion.
  • This course is eligible for CCS Learning Academy’s Learn and Earn Program: get a tuition fee refund of up to 50% if you are placed in a job through CCS Global Tech’s Placement Division*
  • Government and Private pricing available.*

*For more details call: 858-208-4141 or email: training@ccslearningacademy.com; sales@ccslearningacademy.com