
IT EXPERT SYSTEM, INC
IT Training, Staffing and IT Services Provider
Empower Clients Through IT
Schaumburg | Des Plaines | Naperville IL, USA
Database Module (Oracle)
The Oracle PL/SQL language is a flexible procedural extension to SQL and increases productivity, performance, scalability, portability and security. In this course, you will gain the practical knowledge to write PL/SQL programs. You will learn to build stored procedures, design and execute modular applications and increase the efficiency of data movement.
Course Content
-
PL/SQL fundamentals
-
Declaring and anchoring variables to database definitions
-
Flow control constructs
-
DML commands: Select, Insert, Update, Delete
-
DDL commands: Create, Alter Tables/Views/Sequence
-
TCL commands: Grant, Revoke
-
Joins
-
Trigger
-
Stored procedure
-
-
Oracle 11g/12c PL/SQL features
-
PL/SQL in Oracle 11g
-
Returning implicit cursor results from stored procedures in Oracle 12c
-
Declaring local functions within SELECT statements in Oracle 12c
-
-
Data manipulation techniques
-
Maintaining data with DML statements
-
Employing the RETURNING INTO clause
-
Solving the fetch-across-commit problem
-
-
Managing data retrieval with cursors
-
Implications of explicit and implicit cursors
-
Simplifying cursor processing with cursor FOR LOOPs
-
Embedding cursor expressions in SELECT statements
-
-
Cursor variables
-
Strong vs. weak cursor variables
-
Passing cursor variables to other programs
-
Defining REF CURSORS in packages
-
Developing Well-Structured and Error-Free Code
-
-
Error handling
-
Propagation and scope
-
"Retrying" problem transactions with EXCEPTION processing
-
-
Debugging PL/SQL blocks
-
Simplifying testing and debugging with conditional compilation
-
Interpreting compiler messages
-
Applying structured testing techniques
-
Building and applying a testbed
-
Leveraging the debugging facilities in SQL Developer
-
Achieving Maximum Reusability
-
-
Writing stored procedures and functions
-
Calling and invoking server-side logic
-
Passing input and output parameters
-
Implementing an autonomous transaction
-
-
Calling user-written functions
-
Calling PL/SQL functions from SQL
-
Building table-valued functions
-
-
Developing safe triggers
-
Employing :OLD and :NEW variables
-
Avoiding unreliable trigger constructs
-
Exploiting schema and database triggers
-




-
Exploiting complex data types
-
PL/SQL tables, nested tables, VARRAYs
-
Collection types
-
Stepping through dense and nonconsecutive collections
-
-
Bulk binding for high performance
-
Moving data into and out of PL/SQL blocks
-
BULK COLLECT INTO and FORALL
-
BULK cursor attributes
-
BULK EXCEPTION handling
-
Invoking Native Dynamic SQL
-
-
Finessing the compiler
-
The EXECUTE IMMEDIATE statement
-
The RETURNING INTO clause
-
-
Types of dynamic SQL
-
Building SQL statements during runtime
-
Auto generating standard code
-
-
Package tips and techniques
-
Package structure: SPEC and BODY
-
Eliminating dependency problems
-
Overloading for polymorphic effects
-
Evaluating application frameworks
-
Declaring and using persistent global variables
-
-
Database Design/Architect
-
Database Design (ERD)
-
Normalization vs De-Normalization
-
Conceptual vs Logical vs Physical Design
-
Capacity Planning and Forecasting
-
OLTP vs OLAP design concepts
-
-
Database Administration
-
Install, Configure and Administer
-
Implement Security
-
Backup and Restore procedure
-
Disaster Recovery Planning
-
Replication, Mirroring concepts
-
Performance Tuning
-