Oracle PL/SQL
This course is designed to create PL/SQL blocks both anonymous and named. This course will cover PL/SQL objects and data types. It will also cover packages and how to debug and improve performance within PL/SQL. It will address deploying PL/SQL objects and using Oracle pre-define packages, procedures, and functions.
Description
This course is designed to create PL/SQL blocks both anonymous and named. This course will cover PL/SQL objects and data types. It will also cover packages and how to debug and improve performance within PL/SQL. It will address deploying PL/SQL objects and using Oracle pre-define packages, procedures, and functions.
Course Outline
Introduction
- PL/SQL development environments available in this course
- Introduction to SQL Developer
- Course Overview
Introduction to PL/SQL
- Overview of PL/SQL
- PL/SQL Subprograms
- Types of PL/SQL blocks
- Create an Anonymous Block
PL/SQL Terms and Types
- Different Types of Identifiers
- Using the Declarative Section
- Declare Variable
- Use Scalar Data Types
- The %TYPE Attribute
- Introduction to Bind Variables?
- Using Sequences in PL/SQL Expressions
Basic PL/SQL Statements
- Basic PL/SQL Block Syntax Guidelines
- How to Comment PL/SQL Blocks
- Deployment of SQL Functions in PL/SQL
- Conversion of Data Types?
- Using Nested Blocks
PL/SQL and the Oracle Database
- Invoke SELECT Statements in PL/SQL
- Retrieve Data in PL/SQL
- Using SQL Cursors
- Avoid Errors by using Naming Conventions when using Retrieval & DML Statements
- Data Manipulation using PL/SQL
- Using SQL Cursor Attributes and DML Statements
- Commit and rollback transactions
Conditional Structures>
- Using IF Statements
- Using CASE Statements
- Using a simple Loop Statement
- Using a While Loop Statement
- Using a For Loop Statement
PL/SQL Data Types
- Use PL/SQL Records
- The %ROWTYPE Attribute
- Insert and Update with PL/SQL Records
- INDEX BY Tables
- Examine INDEX BY Table Methods
- Use INDEX BY Table of Records
Explicit Cursors
- Define an Explicit Cursor
- Declare the Cursor
- Open the Cursor
- Fetch data from the Cursor
- Close the Cursor
- Cursor FOR loop
- The %NOTFOUND and %ROWCOUNT Attributes
- Describe the FOR UPDATE Clause and WHERE CURRENT Clause
Implicit Cursors
- Define an Implicit Cursor
- Use an Implicit Cursor
- Raise an Implicit Cursor
- Implicit Cursor Attributes
PL/SQL Exception Handling
- Overview of Exceptions
- Define an Exception
- Raise an Exception
- Propagate Exceptions
- Use the RAISE_APPLICATION_ERROR Procedure
Identify Stored Procedures
- Define a Stored Procedure
- Create a Stored Procedure
- Identify the differences between Anonymous Blocks and Subprograms
- Call, and Remove Stored Procedures
- Implement Procedures Parameters and Parameters Modes
- View the data dictionary about Stored Procedures
Identify Stored Functions
- Create, Modify, Call, and Remove a Stored Function
- Identify the advantages of using Stored Functions
- Identify the steps to create a stored function
- Identify the differences between a stored procedure and function
- Restrictions with Functions
- Debug functions and procedures
- View data dictionary information regarding Functions
PL/SQL Packages
- What is a Package?
- List the components of a Package
- Create a Package
- Call a package and its components
- View package information using the Data Dictionary
- Identify Common Oracle supplied packages
Deploying Packages
- Define Package overload
- Use the STANDARD Package
- Identify states of a Package
- Persistent State of a Package Cursor
- Control errors within a package
Using Dynamic SQL
- Using Dynamic SQL
- Using the execute immediate function
- Identify Dynamic SQL uses
- Identify Dynamic SQL limitations
Improving performance with PL/SQL CodeL
- Using Autonomous Transactions
- Use the NOCOPY Hint
- Use the PARALLEL_ENABLE Hint
- Implement result cache
- Using bulk operations within PL/SQL
Defining PL/SQL Triggers
- Overview of Triggers
- Identify Trigger Types
- Create database/server triggers
- Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
- Identify the Trigger Event Types and firing levels
- Differences between Statement Level Triggers and Row Level Triggers
- Create Instead of and Disabled Triggers
- Manage Triggers
Creating Compound Triggers
- What is a Compound Triggers
- Identify Uses of a Compound Trigger
- What is a Mutating trigger
- Triggers and privileges
- Create DDL Triggers
- Create Database Triggers
Manage Dependencies
- Identify Object Dependencies
- Identify privilege prorogations
- Use the USER_DEPENDENCIES View
- Query user_objects
- Identify Object invalidation