Oracle Database – PLSQL.

Oracle Database: Program with PL/SQL
From Yes-M Systems LLC
Student Location – To students from around the world
Delivery Method: Instructor-Led – Live Training
Classroom and/or Online

Oracle Database: Program with PL/SQL

1. Introduction    
1.1.    Course Objectives
1.2.    Course Agenda
1.3.    Describe the Human Resources (HR) Schema
1.4.    PL/SQL development environments available in this course
1.5.    Introduction to SQL Developer

2    Introduction to PL/SQL
2.1.    Overview of PL/SQL
2.2.    Identify the benefits of PL/SQL Subprograms
2.3.    Overview of the types of PL/SQL blocks
2.4.    Create a Simple Anonymous Block
2.5.    How to generate output from a PL/SQL Block?

3    Declare PL/SQL Identifiers
3.1.    List the different Types of Identifiers in a PL/SQL subprogram
3.2.    Usage of the Declarative Section to Define Identifiers
3.3.    Use variables to store data
3.4.    Identify Scalar Data Types
3.5.    The %TYPE Attribute
3.6.    What are Bind Variables?
3.7.    Sequences in PL/SQL Expressions

4    Write Executable Statements
4.1.    Describe Basic PL/SQL Block Syntax Guidelines
4.2.    Learn to Comment the Code
4.3.    Deployment of SQL Functions in PL/SQL
4.4.    How to convert Data Types?
4.5.    Describe Nested Blocks
4.6.    Identify the Operators in PL/SQL

5    Interaction with the Oracle Server
5.1.    Invoke SELECT Statements in PL/SQL
5.2.    Retrieve Data in PL/SQL
5.3.    SQL Cursor concept
5.4.    Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
5.5.    Data Manipulation in the Server using PL/SQL
5.6.    Understand the SQL Cursor concept
5.7.    Use SQL Cursor Attributes to Obtain Feedback on DML
5.8.    Save and Discard Transactions

6    Control Structures
6.1.    Conditional processing using IF Statements
6.2.    Conditional processing using CASE Statements
6.3.    Describe simple Loop Statement
6.4.    Describe While Loop Statement
6.5.    Describe For Loop Statement
6.6.    Use the Continue Statement

7    Composite Data Types
7.1.    Use PL/SQL Records
7.2.    The %ROWTYPE Attribute
7.3.    Insert and Update with PL/SQL Records
7.4.    INDEX BY Tables
7.5.    Examine INDEX BY Table Methods
7.6.    Use INDEX BY Table of Records

8    Explicit Cursors
8.1.    What are Explicit Cursors?
8.2.    Declare the Cursor
8.3.    Open the Cursor
8.4.    Fetch data from the Cursor
8.5.    Close the Cursor
8.6.    Cursor FOR loop
8.7.    The %NOTFOUND and %ROWCOUNT Attributes
8.8.    Describe the FOR UPDATE Clause and WHERE CURRENT Clause

9    Exception Handling
9.1.    Understand Exceptions
9.2.    Handle Exceptions with PL/SQL
9.3.    Trap Predefined Oracle Server Errors
9.4.    Trap Non-Predefined Oracle Server Errors
9.5.    Trap User-Defined Exceptions
9.6.    Propagate Exceptions

10    Stored Procedures
10.1.    Create a Modularized and Layered Subprogram Design
10.2.    Modularize Development With PL/SQL Blocks
10.3.    Understand the PL/SQL Execution Environment
10.4.    List the benefits of using PL/SQL Subprograms
10.5.    List the differences between Anonymous Blocks and Subprograms
10.6.    Create, Call, and Remove Stored Procedures
10.7.    Implement Procedures Parameters and Parameters Modes
10.8.    View Procedure Information

11    Stored Functions and Debugging Subprograms
11.1.    Create, Call, and Remove a Stored Function
11.2.    Identify the advantages of using Stored Functions
11.3.    Identify the steps to create a stored function
11.4.    Invoke User-Defined Functions in SQL Statements
11.5.    Restrictions when calling Functions
11.6.    Control side effects when calling Functions
11.7.    View Functions Information
11.8.    How to debug Functions and Procedures?

12    Packages
12.1.    Listing the advantages of Packages
12.2.    Describe Packages
12.3.    What are the components of a Package?
12.4.    Develop a Package
12.5.    How to enable visibility of a Package’s Components?
12.6.    Create the Package Specification and Body
12.7.    Invoke the Package Constructs
12.8.    View the PL/SQL Source Code using the Data Dictionary

13    Deploying Packages
13.1.    Overloading Subprograms in PL/SQL
13.2.    Use the STANDARD Package
13.3.    Use Forward Declarations to solve Illegal Procedure Reference
13.4.    Implement Package Functions in SQL and Restrictions
13.5.    Persistent State of Packages
13.6.    Persistent State of a Package Cursor
13.7.    Control side effects of PL/SQL Subprograms
13.8.    Invoke PL/SQL Tables of Records in Packages

14    Triggers
14.1.    Describe Triggers
14.2.    Identify the Trigger Event Types and Body
14.3.    Business Application Scenarios for Implementing Triggers
14.4.    Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
14.5.    Identify the Trigger Event Types, Body, and Firing (Timing)
14.6.    Differences between Statement Level Triggers and Row Level Triggers
14.7.    Create Instead of and Disabled Triggers
14.8.    How to Manage, Test and Remove Triggers?

15    Manage Dependencies
15.1.    Overview of Schema Object Dependencies
15.2.    Query Direct Object Dependencies using the USER_DEPENDENCIES View
15.3.    Query an Object’s Status
15.4.    Invalidation of Dependent Objects
15.5.    Display the Direct and Indirect Dependencies
15.6.    Understand Remote Dependencies
15.7.    Recompile a PL/SQL Program Unit

Disclaimer: Yes-M Systems and/or their instructors reserve the right to make any changes to the syllabus as deemed necessary to best fulfil the course objectives. Students registered for this course will be made aware of any changes in a timely fashion using reasonable means.

Back to Top
Call for Enquiry