Plsql By Example

  1. Home
  2. Plsql
  3. Plsql By Example

Tags: Pl/SQL

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

Last updated 2022-01-10 | 4.5

- Declare PL/SQL Variables
- Conditionally control code flow (loops
- control structures)
- Use some of the Oracle supplied PL/SQL packages to generate screen output

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)

* Requirements

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

Description

  • 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

Welcome... 2 lectures

Welcome... Preview 03:15

How to make best use of the Program... Preview 1 page

Software Installation 5 lectures

Installing Oracle Preview 03:08

Installing Java SDK Preview 02:00

Installing SQL Developer Preview 03:50

Running scripts necessary for the course Preview 02:12

Scripts for our Lab Exercises Preview 4 pages

PL/SQL Basics 12 lectures

What is PL/SQL Preview 02:59

PL/SQL Advantages Preview 02:34

PL/SQL Structure Preview 01:51

First Example Preview 01:22

Declaring Variables Preview 05:03

Comments in PL/SQL Preview 01:44

Scope of Variables Preview 01:54

IF then ELSE statement Preview 05:16

CASE Statemet Preview 01:58

WHILE Loop Preview 02:10

FOR Loop Preview 02:09

Exercise #1 Preview 3 pages

Processing Data via PL/SQL 4 lectures

Reading data from database Preview 04:18

What is %TYPE Preview 02:12

Inserting data into database Preview 03:09

Exercise #2 Preview 3 pages

PL/SQL Blocks 9 lectures

Anonymous Blocks Preview 02:46

What are Procedures? Preview 03:07

Creating a Procedure Preview 02:35

Calling a Procedure Preview 03:16

Procedure with OUT Mode Preview 02:54

Procedure with IN OUT Mode Preview 02:17

What are Functions? Preview 03:32

Calling a Function Preview 02:31

Exercise #3 Preview 4 pages

Exceptions 5 lectures

What are Exceptions Preview 02:46

Exceptions Example Preview 08:26

User Defined Exceptions Preview 03:12

System Defined Exceptions list Preview 2 pages

Exercise #4 Preview 5 pages

Packages 5 lectures

What are Packages Preview 04:01

Package Specification Preview 04:11

Package Body Preview 03:36

Executing sub programs present in Packages Preview 01:34

Exercise #5 Preview 4 pages

Working with Records 9 lectures

What are Records? Preview 02:27

Records Example Preview 03:44

Working with Record data Preview 03:51

Passing Records as parameters Preview 01:59

Inserting data using Records Preview 02:05

Updating data using Records Preview 02:19

User defined Record Types Preview 01:25

User defined Record Example Preview 02:28

Exercise #6 Preview 5 pages

Cursors 10 lectures

What are Cursors? Preview 01:50

Implicit Cursor Preview 04:37

Explicit Cursor Preview 01:21

Retrieving 1 row using Explicit Cursor Preview 03:11

Retrieving more than 1 row using Explicit Cursor Preview 02:39

Using Records in Cursors Preview 02:42

Cursor FOR Loop Preview 03:42

Cursor Variable and Reference Cursor Preview 05:48

Exceptions for Cursors Preview 01:01

Exercise #7 Preview 4 pages

Collections 9 lectures

What are Collections Preview 01:55

Collection Terminology Preview 01:34

Associative Arrays Preview 04:21

Nested Tables Preview 05:17

What are VARRAYS Preview 02:48

Collection Methods Preview 02:34

MULTISET Operators Preview 04:06

Collections Summary Preview 01:51

Exercise #8 Preview 4 pages

Triggers 10 lectures

Scripts necessary to practise Triggers Preview 1 page

Trigger's Introduction Preview 04:00

Trigger Types Preview 04:10

Statement Level Trigger Preview 06:23

Statement Level Trigger with multiple Actions Preview 06:37

Row Level Trigger Preview 04:07

OLD and NEW Pseudo Records with an Example Preview 06:53

Restricting the Trigger based on a Condition using the WHEN clause Preview 04:36

Restricting the Trigger at a column level using the OF clause Preview 03:24

Enable/Disable/Drop a Trigger Preview 1 page

Advanced Topics - Bulk Processing 6 lectures

Context Switch Preview 02:45

Bulk Processing Introduction Preview 04:35

Bulk Processing Example Preview 03:54

Bulk Processing with LIMIT option Preview 04:50

Bulk Processing with ROWTYPE Preview 02:40

Handling Bulk Processing Exceptions Preview 05:28

Advanced Topics - Dynamic SQL 7 lectures

Dynamic SQL Introduction Preview 05:08

Dynamic SQL Example Preview 04:58

Bind variables and Parsing Preview 02:53

Dynamic SQL with Bind variables Preview 03:25

Dynamic SQL with Cursors Preview 02:55

Dynamic SQL with Bulk Collect Preview 02:14

DBMS_SQL package Preview 03:11

Advanced Topics - Table Functions 3 lectures

Object Creation Preview 02:28

Table Functions Preview 03:35

Pipelined Table Functions Preview 03:11

Advanced Topics - Large Objects (LOB's) 6 lectures

Large Objects (LOB's) Introduction Preview 05:26

Character Large Object (CLOB) Preview 05:09

Binary Large Object (BLOB) Preview 01:27

Storing Images/Videos in BLOB's Preview 06:40

Binary Files (BFILE) Preview 03:12

Temporary LOB's Preview 03:50

Conclusion 1 lectures

Where to go from here... Preview 1 page