Oracle PL/SQL Tutorials For Beginners

Topic Introduction: In this part, we will briefly show some serial tutorials based on Oracle PL/SQL. 

Introduction to PL/SQL

• What is PL/SQL?
• Benefits of PL/SQL
• Block Structure of PL/SQL
• Block Type
• Execute Anonymous Block
• Enable Output of PL/SQL Block and Viewing Output 

Declaring PL/SQL Variable

• Use of Variable 
• Require for Variable Names
• Handling Variable 
• Declaring and Initializing PL/SQL Variable 
• Delimiters in String Literals
• Type of Variable
• Guidelines for Declaring and Initializing PL/SQL Variable 
• Naming Convention of PL/SQL Structure Used in this course
• Scalar Data Types
• Base Scalar Data Types
• Declaring Scalar Variable
• % Type Attribute
• Declaring Variable with the % Type Attribute
• Declaring Boolean Variable
• LOB Data Type Variable
• Composite Data Type: Records or Collections
• Bind Variables
• Referencing Bind Variables
• Using Auto Print with Bind Variables

Writing Executable Statement

• Lexical Units in PL/SQL Block
• PL/SQL Block Syntax and Guidelines
• Commenting Code
• SQL Functions in PL/SQL
• Using Sequence in PL/SQL Expressions
• Data Type Conversion
• Nested Block
• Variable Scope and Visibility
• Using Qualifier with Nested Block
• Challenge: Determining Variable Scope
• Operation in PL/SQL
• Programming Guidelines
• Indenting Code

Interacting with Oracle Database Server: SQL Statements in PL/SQL Program

• SQL Statements in PL/SQL
• Select Statements in PL/SQL
• Retrieving Statements in PL/SQL
• Naming Ambiguities
• Naming Convention
• Using PL/SQL to Manipulate Data
• Inserting Data
• Updating Data
• Deleting Data
• Merging Rows
• SQL Cursor
• SQL Cursor Attribute for Implicit Cursor

Writing Control Structure

• Controlling Flow of Execution
• IF Statement
• Simple IF Statement
• IF THEN ELSE Statement
• IF ELSIF ELSE Statement
• Null Values in IF Statement
• CASE Expression
• Search CASE Expression
• CASE Statement
• Handling Null
• Logic Tables
• Boolean Expression or Logical Expression
• Iterative Control: LOOP  Statements
• Basic Loops
• WHILE Loops
• For Loops
• For Loop Rules
• Suggested Use of Loop
• Nested Loop and Labels
• PL/SQL CONTINUE Statement

Working with Composite Data Type

• Composite Data Type
• PL/SQL Records or Collection
• PL/SQL Records
• Creating PL/SQL Records
• PL/SQL Records Structure
• % ROWTYPE Attribute
• Advantage of Using % ROWTYPE Attribute
• Inserting Record using % ROWTYPE 
• Updating a Row in a Table by Using a Record
• Associative Array(Index by Table)
• Associative Array Structure
• Step to Create Associative Array
• Creating and Accessing Associative Array
• Using INDEX BY Table Method
• INDEX BY  Table of Method Record
• Nested Table
• VARRAY
• Summary of Collection Type

Using Explicit Cursor

• Cursor
• Explicit Cursor Operations
• Controlling Explicit Cursor
• Declaring the Cursor
• Opening the Cursor
• Fatchinging Data from the Cursor
• Closing the Cursor
• Cursors and Records
• Cursor For Loops
• Explicit Cursor Attribute
• %ISOPEN Attribute
• %ROWCOUNT and %NOTFOUND Example
• Cursor For Loops using Subquery
• Cursors with Parameters
• FOR UPDATE Clause
• WHERE CURRENT OF Clause

Handling Exception

• What is Exception?
• Handling the Exception Example
• Understanding Exception with PL/SQL
• Handling Exception
• Exception Type
• Syntax to Trap Exception
• Guideline for Trapping Exception
• Trapping Predefine Oracle Server Error
• Trapping Non-Predefine Oracle Server Error
• Function for Trapping Exception
• Trapping User-Defined Exceptions
• Propagating Exception in Subblock
• RAISE_APPLICATION_ERROR Procedure

Introducing Store Procedure and Function

• Procedure and Function
• Difference between Anonymus block and Subprogram
• Procedure Syntex
• Creating Procedure
• Invoking Procedure
• Function Syntex
• Creating Function
• Invoking Function
• Invoking Function with Parameter

Package

Trigger

This tutorial is about Creating a Continue