PLSQL by Example Beginner to Advanced PLSQL

PLSQL by Example Beginner to Advanced PLSQL

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

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/SQLDesign PL/SQL anonymous blocks that execute efficientlyUse the Oracle supplied PL/SQL packages to generate screen outputCreate and debug Stored Procedures and FunctionsDesign PL/SQL packages to group related constructsUse PL/SQL programming constructs and conditionally control code flow Create overloaded package subprograms for more flexibilityHandle runtime errors using ExceptionsWriting manageable code using RecordsStoring and processing data using CollectionsProcessing data using Implicit and Explicit Cursors
Softwares used
Oracle Database 11gSQL Developer

Overview

Lecture 1 Welcome…

Lecture 2 How to make best use of the Program…

Section 1: Software Installation

Lecture 3 Installing Oracle

Lecture 4 Installing Java SDK

Lecture 5 Installing SQL Developer

Lecture 6 Running scripts necessary for the course

Lecture 7 Scripts for our Lab Exercises

Section 2: PL/SQL Basics

Lecture 8 What is PL/SQL

Lecture 9 PL/SQL Advantages

Lecture 10 PL/SQL Structure

Lecture 11 First Example

Lecture 12 Declaring Variables

Lecture 13 Comments in PL/SQL

Lecture 14 Scope of Variables

Lecture 15 IF then ELSE statement

Lecture 16 CASE Statemet

Lecture 17 WHILE Loop

Lecture 18 FOR Loop

Lecture 19 Exercise #1

Section 3: Processing Data via PL/SQL

Lecture 20 Reading data from database

Lecture 21 What is %TYPE

Lecture 22 Inserting data into database

Lecture 23 Exercise #2

Section 4: PL/SQL Blocks

Lecture 24 Anonymous Blocks

Lecture 25 What are Procedures?

Lecture 26 Creating a Procedure

Lecture 27 Calling a Procedure

Lecture 28 Procedure with OUT Mode

Lecture 29 Procedure with IN OUT Mode

Lecture 30 What are Functions?

Lecture 31 Calling a Function

Lecture 32 Exercise #3

Section 5: Exceptions

Lecture 33 What are Exceptions

Lecture 34 Exceptions Example

Lecture 35 User Defined Exceptions

Lecture 36 System Defined Exceptions list

Lecture 37 Exercise #4

Section 6: Packages

Lecture 38 What are Packages

Lecture 39 Package Specification

Lecture 40 Package Body

Lecture 41 Executing sub programs present in Packages

Lecture 42 Exercise #5

Section 7: Working with Records

Lecture 43 What are Records?

Lecture 44 Records Example

Lecture 45 Working with Record data

Lecture 46 Passing Records as parameters

Lecture 47 Inserting data using Records

Lecture 48 Updating data using Records

Lecture 49 User defined Record Types

Lecture 50 User defined Record Example

Lecture 51 Exercise #6

Section 8: Cursors

Lecture 52 What are Cursors?

Lecture 53 Implicit Cursor

Lecture 54 Explicit Cursor

Lecture 55 Retrieving 1 row using Explicit Cursor

Lecture 56 Retrieving more than 1 row using Explicit Cursor

Lecture 57 Using Records in Cursors

Lecture 58 Cursor FOR Loop

Lecture 59 Cursor Variable and Reference Cursor

Lecture 60 Exceptions for Cursors

Lecture 61 Exercise #7

Section 9: Collections

Lecture 62 What are Collections

Lecture 63 Collection Terminology

Lecture 64 Associative Arrays

Lecture 65 Nested Tables

Lecture 66 What are VARRAYS

Lecture 67 Collection Methods

Lecture 68 MULTISET Operators

Lecture 69 Collections Summary

Lecture 70 Exercise #8

Section 10: Triggers

Lecture 71 Scripts necessary to practise Triggers

Lecture 72 Trigger’s Introduction

Lecture 73 Trigger Types

Lecture 74 Statement Level Trigger

Lecture 75 Statement Level Trigger with multiple Actions

Lecture 76 Row Level Trigger

Lecture 77 OLD and NEW Pseudo Records with an Example

Lecture 78 Restricting the Trigger based on a Condition using the WHEN clause

Lecture 79 Restricting the Trigger at a column level using the OF clause

Lecture 80 Enable/Disable/Drop a Trigger

Section 11: Advanced Topics – Bulk Processing

Lecture 81 Context Switch

Lecture 82 Bulk Processing Introduction

Lecture 83 Bulk Processing Example

Lecture 84 Bulk Processing with LIMIT option

Lecture 85 Bulk Processing with ROWTYPE

Lecture 86 Handling Bulk Processing Exceptions

Section 12: Advanced Topics – Dynamic SQL

Lecture 87 Dynamic SQL Introduction

Lecture 88 Dynamic SQL Example

Lecture 89 Bind variables and Parsing

Lecture 90 Dynamic SQL with Bind variables

Lecture 91 Dynamic SQL with Cursors

Lecture 92 Dynamic SQL with Bulk Collect

Lecture 93 DBMS_SQL package

Section 13: Advanced Topics – Table Functions

Lecture 94 Object Creation

Lecture 95 Table Functions

Lecture 96 Pipelined Table Functions

Section 14: Advanced Topics – Large Objects (LOB’s)

Lecture 97 Large Objects (LOB’s) Introduction

Lecture 98 Character Large Object (CLOB)

Lecture 99 Binary Large Object (BLOB)

Lecture 100 Storing Images/Videos in BLOB’s

Lecture 101 Binary Files (BFILE)

Lecture 102 Temporary LOB’s

Section 15: Conclusion

Lecture 103 Where to go from here…

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

Course Information:

Udemy | English | 5h 41m | 1.60 GB
Created by: Amarnath Reddy

You Can See More Courses in the Office Productivity >> Greetings from CourseDown.com