Oracle SQL for data analysts - programming and performance

Knowledge of relational databases, free use of SELECT statement in terms of projection, selection, joins and subqueries.

Level
Advanced

Form
Lecture, Presentation, Intensive workshop

Purpose of the training
The training is addressed to data analysts and users who need to flexibly and efficiently extract data from the database using SQL commands directly. It is a complement to the ORA_SA1 training and focuses on the efficiency of executing SQL statements and on scenarios in which the SELECT command alone is not enough and one should use the much more powerful PL/SQL language.

Benefits of completing the training
Learn advanced data analysis techniques using the PL/SQL language, create your own functions and learn techniques to improve the performance of executed reports.

Introduction to PL/SQL language

• The execution environment
• Comparison of SQL and PL/SQL
• Language characteristics, lexical units
• Structure of PL/SQL blocks
• Anonymous blocks vs. stored subroutines
• Program execution

Scalar variables

• Declarations
• Data types
• Lifetime, scope, initialization

Controlling the order of instruction execution

• Decision making
• Loops

SQL commands in PL/SQL

• DML commands
• DDL commands and dynamic SQL
• TCL Commands and Transactivity
• Simplified SELECT command

Cursor handling

• Record types
• Static cursors
• Cursor loops

Error and exception handling

• Names, codes, messages
• Exception handling section and propagation
• Application errors

Creating stored procedures and functions

• Compilation, object status, source codes
• Parameterization
• Calling methods

Collections

• Associative tables (index by), nested tables
• Attributes, handling, initialization, memory allocation

Mass operations on collections

• Mass queries (BULK COLLECT)
• Bulk DML (FORALL)
• Exception handling in bulk operations
• Array and stream functions

Elements of SQL command execution performance

• SQL command execution process, goal setting
• Acquisition and analysis of the command execution plan 
• Controlling the operation of the cost optimizer
• Reading full and via index
• Tips & Tricks

Suggested pre-training
ORA_SA1 or ORA_S1
Suggested follow-up training courses
ORA_SA2