Learning Plsql The Example Way

Tags: Pl/SQL

Rapid PL/SQL interview preparation - PL/SQL Cursors, Procedures, Functions, Packages & Triggers - Object Oriented PL/SQL

Last updated 2022-01-10 | 4.1

- Oracle PL SQL Programming
- Block Structure of PL SQL
- Exception handling / different types of exceptions

What you'll learn

Oracle PL SQL Programming

Block Structure of PL SQL

Exception handling / different types of exceptions

Special PL SQL Anchored and Record Datatypes

Concept and types of PL SQL Cursors

Writing Procedure

Function

Package

Trigger

Object Types

Object Oriented Programming in PL SQL

PL/SQL Collections - Nested Tables and Varrays

* Requirements

* UG degree in any branch
* Some background in SQL is advised

Description

*****************************************************************************

Added lecture PL/SQL Object Table & Row Object *** Section - Object Oriented PLSQL (09 Jan 2020)

*****************************************************************************

After a successful study in SQL, now you need to know the PL/SQL thoroughly to make make you a genuine back end programmer. Even if you are an acclaimed front-end programmer, with the study of SQL coupled with PL/SQL through this course you will become virtually invincible in the field as database analyst/programmer, DBA and trainer.

If you have already wasted your time and money in hunting for a complete yet concise course in PL/SQL through random youtube videos mostly offered by inexperienced people and not certified for their authenticity and quality - then you are at the right place here. This course brings step-by-step lessons in its logical series of videos to help you master the programming in PL/SQL. Moreover this same channel brings to you a complete course in SQL to make a one stop shop for all your database side learning.

This course has been brought by an highly experienced faculty who have had 20+ years of experience in working as Oracle developer and DBA out of which 6 years onside in USA and UK. The faculty has been working in Oracle field ranging from Oracle version 6 to the latest 19C.

Who this course is for?

Students seeking database side prestigious career as Programmer Analyst
Working professionals who are next targeting to become a DBA must know the SQL & PL/SQL thoroughly
All UG students from Engineering, Science and Commerce streams stepping into the field of Information Technology
Project analysts, analysts and designers
All those who have studied SQL must learn PL/SQL to make the circle complete

Who this course is for:

Students of computer science and engineering, fresher or experienced
Professionals working in other front end languages like Java, Python, Dot NET
Students aspiring to make career in Databases, Data Mining, Data Science
Candidates aspiring to learn Database side Backend programming
Students who have studied SQL

Course content

9 sections • 30 lectures

Introduction 2 lectures

Introduction Preview 23:39

PLSQL language origin, language block structure and writing a simple block of PLSQL program

PL SQL Exception Handling Preview 33:46

Types of Exceptions in PLSQL, Basics of Exception Handling, Case studies and examples

PLSQL Special Datatypes 2 lectures

PL SQL Anchored Datatype %TYPE Preview 18:56

Use of Anchored Datatype in PLSQL block, Need for the use of %TYPE, Case & Example

PL SQL Record Datatype %ROWTYPE Preview 18:46

Use of Record Type. How to create? Built in Record Type %ROWTYPE, Use & Example

PLSQL Control Structures 2 lectures

PL SQL Conditional Statements Preview 27:05

Controlling the flow of the program with conditional statements IF, ELSIF and CASE - Explained with Examples

PL SQL Looping Statements Preview 17:29

Different forms of LOOPS, their use under different conditions, Properties of LOOPS explained with examples

PLSQL Cursors 3 lectures

PL SQL Cursor Basics Preview 07:06

PL/SQL cursor basics - What is PL/SQL Cursor - Types of PL/SQL Cursors - Purpose of PL/SQL Cursors - Definitions of Explicit, Implicit, Ref and Sub-query cursors

PL SQL Explicit Cursor Preview 46:20

Form and nature of Explicit Cursor - Properties of Explicit Cursor - Cursor FOR LOOP - Use and Case Example

PL SQL Implicit Cursor Preview 23:14

Form and nature of Implicit Cursor - Properties of Implicit Cursors - Exceptions associated with Implicit Cursors - Case & Examples

PLSQL Cursor Variables - REF CURSOR 5 lectures

What is REF CURSOR? Preview 13:28

What is REF CURSOR? - Types of REF CURSOR - Properties of different types of REF CURSOR - Choosing appropriate type of REF CURSOR - Case & Examples

PL SQL Strong Typed REF CURSOR Preview 19:16

Form and nature of Strong Typed REF CURSOR - Use of Strong Typed REF CURSOR explained with a case examples

PL SQL Weak Typed REF CURSOR Preview 20:05

Form and nature of Weak Typed REF CURSOR - Use of Weak Typed REF CURSOR explained with case examples.

PL SQL Built In SYS_REFCURSOR Preview 11:19

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

PL SQL Cursor Subqueries Preview 14:45

Concept of Cursor Subqueries - Declaration of Nested Query as Cursor - Use of Cursor Subquery explained with case & examples

Stored Procedures and Functions 2 lectures

PL SQL Stored Functions Preview 23:20

What are properties of Function - When to use Function - What to avoid in Function - Cases & Examples

PL SQL Stored Procedures Preview 24:38

Properties of Procedure - When to use Procedure - Returning value(s) from Procedure - Cases and Examples

PLSQL Packages 4 lectures

Package Basic Concepts Preview 22:27

What is Package - Properties of Package Header and Body - Public / Private Declaration - Package Example

Program Overloading in Package Preview 17:03

What is Overloading - Why Overloading of the programs - Conditions of Overloading - Case Examples

PL SQL Package Initialization Preview 22:26

What is Package Initialization - Why need Package Initialization - When to use Package Initialization - Private declaration of Programs and variables - Pragma Autonomous Transaction - Case study of Package Initialization

PL SQL Forward Program Calling Preview 18:49

Meaning of Forward Declaration or Forward Calling - When to use Forward Calling - Organization of Packaged Programs by using Forward Declaration - Case study of forward calling

PLSQL Triggers 7 lectures

PL SQL Trigger Basics Preview 17:17

PL SQL DML Triggers Preview 25:35

Properties of DML Triggers - ROW level Triggers - TABLE level Triggers - Timing of firing BEFORE & AFTER - Properties of each type - Guidelines for choosing the correct type - Combination of Events to fire the trigger - Case Examples

PL SQL Mutating Table Trigger Error Preview 23:36

What is Mutating Table error - Conditions when it appears - Cause analysis of the error - Practical example of work around Mutating Table Error

PL SQL Trigger Predicates Preview 18:13

What are Trigger Predicates - What Trigger Predicates are used for - Execution of lines of code in trigger relevant to the event - Explanation of Trigger Predicates through a practical case example

PL SQL Trigger WHEN Clause Preview 23:03

Preventing unnecessary trigger firing through WHEN clause - When to use WHEN clause - Limitations for the use of WHEN clause - Case example

PL SQL System Level Triggers Preview 16:04

What are System Level Triggers - Properties of Database Triggers - Database level triggering events - Schema level triggers - System Triggers for auditing user activity - Case Examples

PL SQL Instead of Triggers Preview 21:31

What are Instead of Triggers - Triggers on Views - Why need instead of Triggers - Properties of Instead of Triggers - Case Examples

Object Oriented PLSQL 3 lectures

PL SQL Object Oriented Concepts Preview 18:19

Understand the basic concept behind the object oriented programming - What is an object explained with a real time example - Why use object oriented programming - Explanation on various properties of an object - Difference between object type and class

PL SQL Object Type Creation Preview 23:07

How to create an object type - Properties of an object type - Difference & Similarities between Object Type and Package - Case example of an Object Type - Type Header - Type Body - Object Type Constructor - Object Type initialization - Object Type use in a PL/SQL program