TRAINING

Enroll Now

Oracle SQL & PL/SQL Training

Learn to process SQL commands and advance your programming skills.

SQL and PL/SQL is designed specifically to process SQL commands. With SQL and PL/SQL Training, learn how it works.

This Intro to SQL & PL SQL training teaches you basic concepts of relational databases and the SQL programming language. You'll gain essential SQL & PL SQL skills to write queries against single & multiple tables, manipulate data in tables & create database objects and application programming.

Students will benefit from this course as it enables programmers with the skill of using this programming language. In the class students will learn to create anonymous PL/SQL blocks, to create procedures, functions and triggers. Students will learn to use explicit cursors in their program blocks. Demonstrations and hands-on practice reinforce the fundamental concepts. Students use Oracle SQL Developer to develop these program units. SQL*Plus is introduced as optional tools.

Oracle SQL Course Contents:

• Review of Basic SQL and history of SQL
• SQL constructs
• Entity/relation modelling
• Optimizing Oracle SQL
• Using SQL*Plus
• Explain plan and reading execution plans
• Table joining internals
• Subqueries
• Advanced SQL operators
• DML and SQL Tuning
• Aggregation IN sql

PL/SQL Course Contents

Basics of PL/SQL

• PL/SQL architecture
• PL/SQL and SQL*Plus
• PL/SQL Basics
• Introduction to PL/SQL
• PL/SQL as a 4 th  generation language
• Compiling vs. Interpreting
• Declare statement
• Exercise: Write “hello” world PL/SQL program
• Using PL/SQL Variables
• PL/SQL Constants
• PL/SQL Datatypes
• Error messages – user_errors and show errors
• PL/SQL wrapper utility

PL/SQL structures 

• Simple blocks
• Control structures
• PL/SQL records
• Recognizing the Basic PL/SQL Block and Its Sections
• Describing the Significance of Variables in PL/SQL
• Distinguishing Between PL/SQL and Non-PL/SQL Variables
• Declaring Variables and Constants
• Executing a PL/SQL Block

Error checking – exception handling

• Defining exceptions
• Using the when others clause
• Ensuring complete error checking
• Passing error messages to calling routine

Boolean logic in PL/SQL

• Identifying the Uses and Types of Control Structures
• Constructing an IF Statement
• Constructing and Identifying Different Loop Statements
• Controlling Block Flow Using Nested Loops and Labels
• Using Logic Tables
• If-then-else structure
• Testing for numbers characters and Booleans

Cursors in PL/SQL

• Cursor basics
• Using a cursor for a multi-row SQL query

Iteration in PL/SQL

• For loop
• While loop

PL/SQL tables

• Defining PL/SQL tables
• Reasons to use PL/SQL tables
• Populating a PL/SQL table
• Retrieving from a PL/SQL table

Dynamic SQL in PL/SQL

• Introduction to the dbms_sql package
• Creating a dynamic SQL statement

Nested blocks in PL/SQL

• Creating nested blocks
• Understanding scope in nested blocks

Triggers in PL/SQL

• Triggers and database events
• Defining a trigger
• Timing a trigger
• Enabling and disabling a trigger

Stored procedures, functions and packages

• Basics of stored procedures
• Basics of functions
• Basics of packages
• Defining stored procedures & functions
• Function and stored procedures prototypes
• Passing arguments to functions and stored procedures
• Recompiling functions and stored procedures
• Pinning packages in the SGA with dbms_shared_pool.keep
• Package forward declaration
• Package dependency
• Package overloading
• Listing package information

Bulking in PL/SQL

• Bulk queries
• Bulk DML (forall statement)
• Using cursor attributes

Analyzing impact of bilk operations