PL/SQL by Example - Beginner to Advanced PL/SQL

Learn how to combine the efficiency of SQL and Procedural Language(PL) to build a powerful Application.
4.6 (183 ratings)
Instead of using a simple lifetime average, Udemy calculates a
course's star rating by considering a number of different factors
such as the number of ratings, the age of ratings, and the
likelihood of fraudulent ratings.
2,280 students enrolled
$19
$200
90% off
Take This Course
  • Lectures 103
  • Length 5.5 hours
  • Skill Level All Levels
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 7/2015 English

Course Description

Learn to become a PL/SQL developer in just four weeks.

This fast, easy and effective course will take you from zero PL/SQL writing skills to being able to write efficient programs to process data.

PL/SQL is the Oracle Procedural Language extension of SQL. A PL/SQL program can have both SQL statements and procedural statements. In the PL/SQL program, the SQL statements are used to access sets of data stored in a database, while the procedural statements are used to process individual piece of data and control the program flow.

In this course participants will learn

  • Describe the features and syntax of PL/SQL
  • Design PL/SQL anonymous blocks that execute efficiently
  • Use the Oracle supplied PL/SQL packages to generate screen output
  • Create and debug Stored Procedures and Functions
  • Design PL/SQL packages to group related constructs
  • Use PL/SQL programming constructs and conditionally control code flow
  • Create overloaded package subprograms for more flexibility
  • Handle runtime errors using Exceptions
  • Writing manageable code using Records
  • Storing and processing data using Collections
  • Processing data using Implicit and Explicit Cursors

Softwares used

  • Oracle Database 11g
  • SQL Developer

What are the requirements?

  • SQL Knowledge is needed and is a must!!!

What am I going to get from this course?

  • 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)

What is the target audience?

  • This PL/SQL course is meant for students who want to learn Procedure Language to control SQL Statements.
  • This course is not for you if you're looking to learn more advanced concepts of PL/SQL

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Welcome...
Preview
03:15
How to make best use of the Program...
1 page
Section 1: Software Installation
Installing Oracle
Preview
04:07
Installing Java SDK
Preview
02:20
Installing SQL Developer
Preview
03:21
Running scripts necessary for the course
02:12
Scripts for our Lab Exercises
4 pages
Section 2: PL/SQL Basics
What is PL/SQL
02:59
PL/SQL Advantages
02:34
PL/SQL Structure
01:51
First Example
01:22
Declaring Variables
05:03
Comments in PL/SQL
01:44
Scope of Variables
01:54
IF then ELSE statement
05:16
CASE Statemet
01:58
WHILE Loop
02:10
FOR Loop
02:09
Exercise #1
3 pages
Section 3: Processing Data via PL/SQL
Reading data from database
04:18
What is %TYPE
02:12
Inserting data into database
03:09
Exercise #2
3 pages
Section 4: PL/SQL Blocks
Anonymous Blocks
02:46
What are Procedures?
03:07
Creating a Procedure
02:35
Calling a Procedure
03:16
Procedure with OUT Mode
02:54
Procedure with IN OUT Mode
02:17
What are Functions?
03:32
Calling a Function
02:31
Exercise #3
4 pages
Section 5: Exceptions
What are Exceptions
02:46
Exceptions Example
08:26
User Defined Exceptions
03:12
System Defined Exceptions list
2 pages
Exercise #4
5 pages
Section 6: Packages
What are Packages
04:01
Package Specification
04:11
Package Body
03:36
Executing sub programs present in Packages
01:34
Exercise #5
4 pages
Section 7: Working with Records
What are Records?
02:27
Records Example
03:44
Working with Record data
03:51
Passing Records as parameters
01:59
Inserting data using Records
02:05
Updating data using Records
02:19
User defined Record Types
01:25
User defined Record Example
02:28
Exercise #6
5 pages
Section 8: Cursors
What are Cursors?
01:50
Implicit Cursor
04:37
Explicit Cursor
01:21
Retrieving 1 row using Explicit Cursor
03:11
Retrieving more than 1 row using Explicit Cursor
02:39
Using Records in Cursors
02:42
Cursor FOR Loop
03:42
Cursor Variable and Reference Cursor
05:48
Exceptions for Cursors
01:01
Exercise #7
4 pages
Section 9: Collections
What are Collections
01:55
Collection Terminology
01:34
Associative Arrays
04:21
Nested Tables
05:17
What are VARRAYS
02:48
Collection Methods
02:34
MULTISET Operators
04:06
Collections Summary
01:55
Exercise #8
4 pages
Section 10: Triggers
Scripts necessary to practise Triggers
1 page
Trigger's Introduction
04:00
Trigger Types
04:10
Statement Level Trigger
06:23
Statement Level Trigger with multiple Actions
06:37
Row Level Trigger
04:07
OLD and NEW Pseudo Records with an Example
06:53
Restricting the Trigger based on a Condition using the WHEN clause
04:36
Restricting the Trigger at a column level using the OF clause
03:24
Enable/Disable/Drop a Trigger
1 page
Section 11: Advanced Topics - Bulk Processing
Context Switch
02:45
Bulk Processing Introduction
04:35
Bulk Processing Example
03:54
Bulk Processing with LIMIT option
04:50
Bulk Processing with ROWTYPE
02:40
Handling Bulk Processing Exceptions
05:28
Section 12: Advanced Topics - Dynamic SQL
Dynamic SQL Introduction
05:08
Dynamic SQL Example
04:58

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Amarnath Reddy, Oracle Architect

Amarnath has a Masters Degree in computer science with around 15 years of experience in the IT industry. He has successfully designed and implemented projects for clients in South Africa, India and USA. Currently his main focus is on Datawarehouse Architecture.

Amarnath is currently working for a fortune 500 company in USA and has pioneered the data architecture discipline. His Innovative and simple architectural designs have won accolades in the company.

Today, he brings leadership and technical expertise to enable customers to optimize and modernize their IT environments leveraging Business Intelligence tools, Big Data, social, mobile, and the Internet of Things.

Ready to start learning?
Take This Course