Oracle 19c PL/SQL Fundamentals

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

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.

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

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.