phone+91-87222 63165 / +1(510)-379-9024 contact@syncomint.com
Try Our Sample Training Videos

Course Info

PL/SQL

PL/SQL stands for Procedural Language (PL) extensions to Structured Query Language (SQL). PL/SQL is used to write stored procedures, triggers and other code stored in the powerful Oracle database. In addition, Oracle also uses this language in their frontend development environment, Developer, as well as their Web-based engine, Oracle Application Server.

Benefits of the Program

This course benefits those involved in programming with PL/SQL, including developers, database administrators, programmers, analysts and managers.  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.

Topic List

This course teaches you how to write PL/SQL programs to solve realistic business problems using Oracle's most strategic language. Syncomint provides you Classroom Training as well as Live Virtual Training. 

Course Content
[formac-acc title="Lesson 1: Introduction"]Course Objectives, Course Agenda, Human Resources (HR) Schema, Introduction to SQL Developer[/formac-acc] [formac-acc title="Lesson 2: Introduction to PL/SQL"]PL/SQL Overview, Benefits of PL/SQL Subprograms, Overview of the Types of PL/SQL blocks, Create a Simple Anonymous Block, Generate Output from a PL/SQL Block[/formac-acc] [formac-acc title="Lesson 3: PL/SQL Identifiers"]List the different Types of Identifiers in a PL/SQL subprogram , Usage of the Declarative Section to define Identifiers,Use variables to store data,Identify Scalar Data Types, The %TYPE Attribute, What are Bind Variables?, Sequences in PL/SQL Expressions[/formac-acc] [formac-acc title="Lesson 4: Write Executable Statements"]Describe Basic PL/SQL Block Syntax Guidelines, Comment Code, Deployment of SQL Functions in PL/SQL, How to convert Data Types?, Nested Blocks, Identify the Operators in PL/SQL[/formac-acc] [formac-acc title="Lesson 5: Interaction with the Oracle Server"]Invoke SELECT Statements in PL/SQL to Retrieve data, Data Manipulation in the Server Using PL/SQL,SQL Cursor concept, Usage of SQL Cursor Attributes to Obtain Feedback on DML, Save and Discard Transactions[/formac-acc] [formac-acc title="Lesson 6: Control Structures"]Conditional processing Using IF Statements, Conditional processing Using CASE Statements, Use simple Loop Statement, Use While Loop Statement, Use For Loop Statement, Describe the Continue Statement[/formac-acc] [formac-acc title="Lesson 7: Composite Data Types"]Use PL/SQL Records, The %ROWTYPE Attribute, Insert and Update with PL/SQL Records, Associative Arrays (INDEX BY Tables), Examine INDEX BY Table Methods, Use INDEX BY Table of Records[/formac-acc] [formac-acc title="Lesson 8: Explicit Cursors"]What are Explicit Cursors?, Declare the Cursor, Open the Cursor, Fetch data from the Cursor, Close the Cursor, Cursor FOR loop, Explicit Cursor Attributes, FOR UPDATE Clause and WHERE CURRENT Clause[/formac-acc] [formac-acc title="Lesson 9: Exception Handling"]Understand Exceptions, Handle Exceptions with PL/SQL, Trap Predefined Oracle Server Errors, Trap Non-Predefined Oracle Server Errors, Trap User-Defined Exceptions, Propagate Exceptions, RAISE_APPLICATION_ERROR Procedure[/formac-acc] [formac-acc title="Lesson 10: Stored Procedures and Functions"]Understand Stored Procedures and Functions, Differentiate between anonymous blocks and subprograms, Create a Simple Procedure, Create a Simple Procedure with IN parameter, Create a Simple Function, Execute a Simple Procedure, Execute a Simple Function[/formac-acc]

ClassRoom Schedule

Classroom Training - 10 Days

Day 1

11AM-5PM
INTODUCTION
  • Course Objectives
  • Course Agenda
  • Introduction to SQL Developer

Day 2

11AM-5PM
Introduction to PL/SQL
  • PL/SQL Overview
  • Benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • Generate Output from a PL/SQL Block

Day 3

11AM-5PM
PL/SQL IDENTIFIERS
  • List the different Types of Identifiers in a PL/SQL subprogram
  • Usage of the Declarative Section to define Identifiers
  • Use variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions

Day 4

11AM-5PM
WRITE EXECUTABLE STATEMENTS
  • Describe Basic PL/SQL Block Syntax Guidelines
  • Comment Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Nested Blocks
  • Identify the Operators in PL/SQL

Day 5

11AM-5PM
INTERACTION WITH THE ORACLE SERVER
  • Invoke SELECT Statements in PL/SQL to Retrieve data
  • Data Manipulation in the Server Using PL/SQL
  • SQL Cursor concept
  • Usage of SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions

Day 6

11AM-5PM
CONTROL STRUCTURES
  • Conditional processing Using IF Statements
  • Conditional processing Using CASE Statements
  • Use Simple Loop Statement
  • Use While Loop Statement
  • Use For Loop Statement
  • Describe the Continue Statement

Day 7

11AM-5PM
  • COMPOSITE DATA TYPES
  • Use PL/SQL Records
  • The %ROWTYPE Attribute
  • Insert and Update with PL/SQL Records
  • Associative Arrays (INDEX BY Tables)
  • Examine INDEX BY Table Methods
  • Use INDEX BY Table of Records

Day 8

11AM-5PM
EXPLICIT CURSORS
  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • Explicit Cursor Attributes
  • FOR UPDATE Clause and WHERE CURRENT Clause

Day 9

11AM-5PM
EXCEPTION HANDLING
  • Understand Exceptions
  • Handle Exceptions with PL/SQL
  • Trap Predefined Oracle Server Errors
  • Trap Non-Predefined Oracle Server Errors
  • Trap User-Defined Exceptions
  • Propagate Exceptions
  • RAISE_APPLICATION_ERROR Procedure

Day 10

11AM-5PM
STORED PROCEDURES AND FUNCTIONS
  • Understand Stored Procedures and Functions
  • Differentiate between anonymous blocks and subprograms
  • Create a Simple Procedure
  • Create a Simple Procedure with IN parameter
  • Create a Simple Function
  • Execute a Simple Procedure
  • Execute a Simple Function

Live Virtual Class Schedule

Virtual Training - 5 Days

Day 1

8AM-12PM && 1PM-5PM
INTRODUCTION
  • Course Objectives
  • Course Agenda
  • Human Resources (HR) Schema
  • Introduction to SQL Developer
INTRODUCTION TO PL/SQL
  • PL/SQL OvervieW
  • Benefits of PL/SQL Subprograms
  • Overview of the Types of PL/SQL blocks
  • Create a Simple Anonymous Block
  • Generate Output from a PL/SQL Block

Day 2

8AM-12PM && 1PM-5PM
PL/SQL IDENTIFIERS
  • List the different Types of Identifiers in a PL/SQL subprogram
  • Usage of the Declarative Section to define Identifiers
  • Use variables to store data
  • Identify Scalar Data Types
  • The %TYPE Attribute
  • What are Bind Variables?
  • Sequences in PL/SQL Expressions
WRITE EXECUTABLE STATEMENTS
  • Describe Basic PL/SQL Block Syntax Guidelines
  • Comment Code
  • Deployment of SQL Functions in PL/SQL
  • How to convert Data Types?
  • Nested Blocks
  • Identify the Operators in PL/SQL

Day 3

8AM-12PM && 1PM-5PM
INTERACTION WITH THE ORACLE SERVER
  • Invoke SELECT Statements in PL/SQL to Retrieve data
  • Data Manipulation in the Server Using PL/SQL
  • SQL Cursor concept
  • Usage of SQL Cursor Attributes to Obtain Feedback on DML
  • Save and Discard Transactions
CONTROL STRUCTURES
  • Conditional processing Using IF Statements
  • Conditional processing Using CASE Statements
  • Use simple Loop Statement
  • Use While Loop Statement
  • Use For Loop Statement
  • Describe the Continue Statement

Day 4

8AM-12PM && 1PM-5PM
COMPOSITE DATA TYPES
  • Use PL/SQL Records
  • The %ROWTYPE Attribute
  • Insert and Update with PL/SQL Records
  • Associative Arrays (INDEX BY Tables)
  • Examine INDEX BY Table Methods
  • Use INDEX BY Table of Records
EXPLICIT CURSORS
  • What are Explicit Cursors?
  • Declare the Cursor
  • Open the Cursor
  • Fetch data from the Cursor
  • Close the Cursor
  • Cursor FOR loop
  • Explicit Cursor AttributesFOR UPDATE Clause and WHERE CURRENT Clause

Day 5

8AM-12PM && 1PM-5PM
EXCEPTION HANDLING
  • Understand Exceptions
  • Handle Exceptions with PL/SQL
  • Trap Predefined Oracle Server Errors
  • Trap Non-Predefined Oracle Server Errors
  • Trap User-Defined Exceptions
  • Propagate Exceptions
  • RAISE_APPLICATION_ERROR Procedure
STORED PROCEDURE AND FUNCTIONS
  • Understand Stored Procedures and Functions
  • Differentiate between anonymous blocks and subprograms
  • Create a Simple Procedure
  • Create a Simple Procedure with IN parameter
  • Create a Simple Function
  • Execute a Simple Procedure
  • Execute a Simple Function
Connect With Us

Call: +91-87222 63165 (India)
Call: +1 510-379-9024 (USA)

Mail: contact@syncomint.com