Learning PL/SQL - Beginner to Advanced The Example Way

Complete guide from beginner to PL/SQL genius - Unique industry standard examples - Rapid PL/SQL interview preparation

Created by : Rated :4.8 91342 views

Last Updated: 2022-04-25 08:04:12 English Course Type: Self Learning

Each topic is covered with application scenarios - Industry standard examples
Oracle PL/SQL programming skills from absolute zero
Basic knowledge of PL/SQL language - Block Structure
Exception handling with different types of exception
Special PL/SQL datatypes - Anchored and Record datatypes
PL/SQL Cursors - Concept and Types of Cursors
Stored Subprograms - Procedures, Functions, Packages, Triggers & Object Types
Object Oriented Programming in PL/SQL
Bulk data handling by using Collections - Nested Tables & Varrays

PL/SQL language is programmable extension to the SQL language and one must learn it to add three folds chances to get a dream database programmer job. With the study of PL/SQL you will become virtually invincible in the field of database programming, data analytics, DBA and trainer.

The course is most comprehensive and delivered in logical sequence of sections and video lessons. There is no point is wasting time on youtube hunting through various lessons, though for free, they may not even be delivered by an experienced instructor. This course has been designed and delivered by an instructor who has 25 years of working experience in various capacities in the field of Information Technology.

This course is for -

Students seeking database side prestigious career as Programmer Analyst
Working programmers who are next targeting to become DBAs and they must know SQL & PL/SQL very well
All UG level students and above looking to make their career in the field of information technology
Project analysts, managers and designers
All those who are conversant with SQL must learn PL/SQL to extend their skill level

You need to have Oracle XE (Express Edition) installed on your machine. It is absolutely free downloadable from Oracle's site. Installation instructions have also been given by the Oracle
This course is delivered on Windows platform. But It may be any platform of your choice. All the contents and code of the examples / programs will remain the same

You should have fair knowledge of database concepts
You should have fair knowledge of SQL, may not be necessarily with Oracle but any database

Section 1 : Introduction to PL/SQL 2 Lectures 00:57:25
Lecture 1 :
My First PL/SQL Program Block - PL/SQL Language Overview Preview
Understanding the PL/SQL block structure - Different sections of PL/SQL Block - Declaration, Execution and Exception Handling Sections - Writing a first program in PL/SQL - Providing input and display the output of the program
Lecture 2 :
Types of PL/SQL Exceptions - Exception Handling
What is an Exception - Different types of Exceptions in PL/SQL - Built-in, Customized, Pragma Exception types - Case studies of exception handling
Section 2 : PL/SQL Special Data Types 2 Lectures 00:37:42
Lecture 1 :
PL/SQL Anchored Datatype %TYPE
What is %TYPE Datatype - Why Require Anchored Datatype - How to declare %TYPE variables
Lecture 2 :
PL/SQL Record Datatype %ROWTYPE
What is record type - How to define customized record type - Built in record type %ROWTYPE - Use Examples
Section 3 : PL/SQL Control Structures 2 Lectures 00:44:33
Lecture 1 :
PL/SQL Conditional Control Statements
Controlling the flow of programs - Conditional Statements - IF, ELSIF, and CASE Statements - Case studies and Examples
Lecture 2 :
PL/SQL Loops - Recursive Structures
Different forms of loops - Use under different conditions - Properties of different looping structures explained with examples
Section 4 : PL/SQL Cursors 8 Lectures 02:40:49
Lecture 1 :
PL/SQL Cursor Basics
What is PL/SQL cursor - PL/SQL Cursor basics - PL/SQL Cursor Types - Definitions of Explicit, Implicit, Reference and Sub-query cursors
Lecture 2 :
PL/SQL Explicit Cursor
What is explicit cursor - Form and nature - Properties of explicit cursor - CURSOR FOR loop - Case example
Lecture 3 :
PL/SQL Implicit Cursor
What is implicit cursor - Form and nature - Implicit cursor exceptions - Case and examples
Lecture 4 :
PL/SQL Ref Cursor
Lecture 5 :
PL/SQL Strong Type Ref Cursor
Form and nature of strong typed ref cursor - Use of strong typed ref cursor - Case examples
Lecture 6 :
PL/SQL Weak Type Cursor
Form and nature of weak typed ref cursor - Use of weak typed ref cursor - Case examples
Lecture 7 :
PL/SQL Built-in Sys_Refcursor
What is built-in sys_refcursor - Properties of sys_refcursor - Use of sys_refcursor as parameter of procedure - How to draw bulk data from the program by using sys_refcursor
Lecture 8 :
PL/SQL Cursor Subqueries
Concept of cursor subqueries - Declaration of nested query as cursor - Use of cursor subquery - Case example
Section 5 : PL/SQL Stored Procedures and Functions 2 Lectures 00:47:47
Lecture 1 :
PL/SQL Stored Procedure
Properties of Procedure - When to use Procedure - Ways of returning value(s) from Procedure - Case example
Lecture 2 :
PL/SQL Stored Function
Properties of Function - When to use Function - What to avoid in Function - Case examples
Section 6 : PL/SQL Packages 4 Lectures 01:20:20
Lecture 1 :
PL/SQL Package Basics
What is Package - Package Header and Body - Properties of Header and Body - Public and Private Declaration - A simple package example
Lecture 2 :
PL/SQL Package Program Overloading
What is overloading - Why need program overloading - When to overload - Case example
Lecture 3 :
PL/SQL Package Initialization
What is package initialization - Why need package initialization - When to use package initialization - Case of private / local declaration of variables and programs - Pragma Autonomous Transaction - Case study of package initialization
Lecture 4 :
PL/SQL Forward Program Calling
What is meant by forward declaration or forward calling - When to use forward calling - Organization of packaged programs by using forward declaration - Case study of forward declaration
Section 7 : PL/SQL Triggers 7 Lectures 02:24:21
Lecture 1 :
PL/SQL Trigger Basics
What is a Trigger - Why we need triggers - Trigger types - Writing a trigger
Lecture 2 :
PL/SQL DML Triggers
Properties of DML triggers - Row Level - Table Level - Before / After Timed Triggers - Properties of each type of Triggers - Guidelines for choosing the appropriate type - Combination of events to fire the trigger - Case examples
Lecture 3 :
PL/SQL Mutating Table Trigger Error - Nature, reason and work around
What is Mutating Table error - Conditions when the Mutating Table error appears - Cause analysis of the error - Practical example of work around
Lecture 4 :
PL/SQL Trigger Predicates
What are trigger predicates - Why use trigger predicates - Execution of code relevant to firing event - Explanation of trigger predicates with case example
Lecture 5 :
PL/SQL Triggers WHEN Clause
What is WHEN clause - Preventing unnecessary firing of the trigger - Situations to use WHEN clause - Limitations - Case examples
Lecture 6 :
PL/SQL System Triggers
What are system triggers - Properties of System / Database triggers - Database level trigger events - Schema level triggers - Auditing user activities using the system triggers - Case examples
Lecture 7 :
PL/SQL Instead-of Trigger
What are instead-of triggers - Triggers on views - Why need instead-of triggers - Properties of instead-of triggers - Case examples
Section 8 : Object Oriented PL/SQL 3 Lectures 00:53:16
Lecture 1 :
PL/SQL Object Oriented Concepts
Basic concept of object oriented - Real time object analogy - Why use object oriented approach - Properties of objects - Difference between Object Type and Class
Lecture 2 :
PL/SQL Object Type Creation
Creation of Object Type - Properties of an Object Type - Differences and Similarities between Object Type and Package - Type Header - Type Body - Object Type Constructor - Object Type initialization - PL/SQL program demonstrating use of object type
Lecture 3 :
PL/SQL Object Table and Row Objects
What is object table - Create an object table - Concept of Row Object - Data Manipulation in Object Table - Method Integration with Object Table

How do i access the course after purchase?
It's simple. When you sign up, you'll immediately have unlimited viewing of thousands of expert courses, paths to guide your learning, tools to measure your skills and hands-on resources like exercise files. There’s no limit on what you can learn and you can cancel at any time.
Are these video based online self-learning courses?
Yes. All of the courses comes with online video based lectures created by certified instructors. Instructors have crafted these courses with a blend of high quality interactive videos, lectures, quizzes & real world projects to give you an indepth knowledge about the topic.
Can i play & pause the course as per my convenience?
Yes absolutely & thats one of the advantage of self-paced courses. You can anytime pause or resume the course & come back & forth from one lecture to another lecture, play the videos mulitple times & so on.
How do i contact the instructor for any doubts or questions?
Most of these courses have general questions & answers already covered within the course lectures. However, if you need any further help from the instructor, you can use the inbuilt Chat with Instructor option to send a message to an instructor & they will reply you within 24 hours. You can ask as many questions as you want.
Do i need a pc to access the course or can i do it on mobile & tablet as well?
Brilliant question? Isn't it? You can access the courses on any device like PC, Mobile, Tablet & even on a smart tv. For mobile & a tablet you can download the Learnfly android or an iOS app. If mobile app is not available in your country, you can access the course directly by visting our website, its fully mobile friendly.
Do i get any certificate for the courses?
Yes. Once you complete any course on our platform along with provided assessments by the instructor, you will be eligble to get certificate of course completion.
For how long can i access my course on the platform?
You require an active subscription to access courses on our platform. If your subscription is active, you can access any course on our platform with no restrictions.
Is there any free trial?
Currently, we do not offer any free trial.
Can i cancel anytime?
Yes, you can cancel your subscription at any time. Your subscription will auto-renew until you cancel, but why would you want to?

Prashant Munshi,

91342 Course Views

1 Courses

Bachelor of Engineering in Electronics and Power, having total industry experience 38 years out of which 23 years in the field of Information Technology. Expertise in all types of database systems and allied technologies. Particular specialization in Oracle database administration. 6 years onsite experience as Oracle DBA in USA and UK. Corporate trainer to CMM5 level companies and adjunct faculty to various engineering and technology colleges in Central India