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

Name: PL/SQL by Example - Beginner to Advanced PL/SQL
Rating: 4.5 (24758 reviews)

Using PL/SQL learn how to combine the efficiency of SQL & Procedural Language(PL) to build a powerful PL/SQL Application

Created byAmarnath Reddy

Last updated 12/2018

English

What you'll learn

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)

Course content

16 sections • 103 lectures • 4h 59m total length

Welcome...3:15
How to make best use of the Program...1:00

What is PL/SQL2:59
explore how pl/sql extends sql with procedural statements to define business logic, control flow, and data processing in a single block, addressing sql's limitations.
PL/SQL Advantages2:34
PL/SQL Structure1:51
First Example1:22
Explore your first PL/SQL example by displaying output with dbms_output.put_line. Learn to enable the dbms output window, run the program, and see the welcome message.
Declaring Variables5:03
Learn how to declare variables in a PL/SQL block, assign defaults with colon equal to and the default keyword, and declare character variables, outputs, and constants to prevent reassignment.
Comments in PL/SQL1:44
Learn to define PL/SQL comments, including single-line comments with two dashes and multi-line comments using /# to start and #/ to end, and follow best practices for clarity.
Scope of Variables1:54
IF then ELSE statement5:16
CASE Statemet1:58
WHILE Loop2:10
FOR Loop2:09
Exercise #13:00

Reading data from database4:18
Fetch data from the database using select into in PL/SQL, ensuring variable data types match the column types (number, varchar, date, timestamp, composite) and display results with dbms_output.
What is %TYPE2:12
Inserting data into database3:09
Learn how to insert data into a database using a PL/SQL block, starting with a declare block that defines variables with table.column%type, then inserting into the customer table and commit.
Exercise #23:00

What are Exceptions2:46
Discover how pl/sql uses the exception block to catch errors during execution, differentiate system-defined and user-defined exceptions, and record and handle them with when clauses.
Exceptions Example8:26
User Defined Exceptions3:12
Declare a user defined exception, raise it when customer_ID is less than or equal to zero, and catch it to display that customer_ID must be greater than zero.
System Defined Exceptions list2:00
Exercise #45:00

What are Records?2:27
Records Example3:44
Learn how to use a record data type to fetch and display customer data in PL/SQL, replacing ten variables with a single composite record for any number of columns.
Working with Record data3:51
Passing Records as parameters1:59
Inserting data using Records2:05
Updating data using Records2:19
Learn to update database data using a record data type in PL/SQL by fetching into a record, modifying fields, and applying update with a where clause.
User defined Record Types1:25
User defined Record Example2:28
Exercise #65:00

What are Cursors?1:50
Implicit Cursor4:37
Explicit Cursor1:21
Retrieving 1 row using Explicit Cursor3:11
Retrieving more than 1 row using Explicit Cursor2:39
Using Records in Cursors2:42
Cursor FOR Loop3:42
Master the cursor for loop to replace explicit cursor handling by automatically processing query results, with Oracle fetching rows internally.
Cursor Variable and Reference Cursor5:48
Exceptions for Cursors1:01
Exercise #74:00

Requirements

SQL Knowledge is needed and is a must!!!

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

Who this course is for:

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

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

What you'll learn

Explore related topics

Course content

Welcome...2 lectures • 4min

Software Installation5 lectures • 15min

PL/SQL Basics12 lectures • 32min

Processing Data via PL/SQL4 lectures • 13min

PL/SQL Blocks9 lectures • 27min

Exceptions5 lectures • 21min

Packages5 lectures • 17min

Working with Records9 lectures • 25min

Cursors10 lectures • 31min

Collections9 lectures • 28min

Requirements

Description

Who this course is for: