IIRF Online > Office Productivity > Oracle > PL/SQL > PL/SQL by Example - Beginner to Advanced PL/SQL
PL/SQL by Example - Beginner to Advanced PL/SQL by Udemy
Using PL/SQL learn how to combine the efficiency of SQL & Procedural Language(PL) to build a powerful PL/SQL Application
Course Highlights
- Declare PL/SQL Variables
- Conditionally control code flow (loops, control structures)
- Use some of the Oracle supplied PL/SQL packages to generate screen output
- Create anonymous PL/SQL blocks, functions and procedures
- Use PL/SQL packages to group and contain related constructs
- Catch run time errors using Exceptions
- Perform Data processing using Records and Cursors
- Manage data using Collections
- Create triggers to solve business challenges
- Peform Bulk Processing to improve performance
- Execute SQL using Dynamic SQL
- Pipelining the data as it is processed
- Working with Large Objects (LOB's)
Skills you will learn!
Curriculum
2 Topics
Welcome...
How to make best use of the Program...
5 Topics
Installing Oracle
Installing Java SDK
Installing SQL Developer
Running scripts necessary for the course
Scripts for our Lab Exercises
12 Topics
What is PL/SQL
PL/SQL Advantages
PL/SQL Structure
First Example
Declaring Variables
Comments in PL/SQL
Scope of Variables
IF then ELSE statement
CASE Statemet
WHILE Loop
FOR Loop
Exercise #1
4 Topics
Reading data from database
What is %TYPE
Inserting data into database
Exercise #2
9 Topics
Anonymous Blocks
What are Procedures?
Creating a Procedure
Calling a Procedure
Procedure with OUT Mode
Procedure with IN OUT Mode
What are Functions?
Calling a Function
Exercise #3
5 Topics
What are Exceptions
Exceptions Example
User Defined Exceptions
System Defined Exceptions list
Exercise #4
5 Topics
What are Packages
Package Specification
Package Body
Executing sub programs present in Packages
Exercise #5
9 Topics
What are Records?
Records Example
Working with Record data
Passing Records as parameters
Inserting data using Records
Updating data using Records
User defined Record Types
User defined Record Example
Exercise #6
10 Topics
What are Cursors?
Implicit Cursor
Explicit Cursor
Retrieving 1 row using Explicit Cursor
Retrieving more than 1 row using Explicit Cursor
Using Records in Cursors
Cursor FOR Loop
Cursor Variable and Reference Cursor
Exceptions for Cursors
Exercise #7
9 Topics
What are Collections
Collection Terminology
Associative Arrays
Nested Tables
What are VARRAYS
Collection Methods
MULTISET Operators
Collections Summary
Exercise #8
10 Topics
Scripts necessary to practise Triggers
Trigger's Introduction
Trigger Types
Statement Level Trigger
Statement Level Trigger with multiple Actions
Row Level Trigger
OLD and NEW Pseudo Records with an Example
Restricting the Trigger based on a Condition using the WHEN clause
Restricting the Trigger at a column level using the OF clause
Enable/Disable/Drop a Trigger
6 Topics
Context Switch
Bulk Processing Introduction
Bulk Processing Example
Bulk Processing with LIMIT option
Bulk Processing with ROWTYPE
Handling Bulk Processing Exceptions
7 Topics
Dynamic SQL Introduction
Dynamic SQL Example
Bind variables and Parsing
Dynamic SQL with Bind variables
Dynamic SQL with Cursors
Dynamic SQL with Bulk Collect
DBMS_SQL package
3 Topics
Object Creation
Table Functions
Pipelined Table Functions
6 Topics
Large Objects (LOB's) Introduction
Character Large Object (CLOB)
Binary Large Object (BLOB)
Storing Images/Videos in BLOB's
Binary Files (BFILE)
Temporary LOB's
1 Topic
Where to go from here...
PL/SQL by Example - Beginner to Advanced PL/SQL
Thank You!
Your review has been submitted successfully.