Course Setup
- Course Agenda
- Course Setup Notes / 4 Options
- Option 2- creating the HR Schema
- Option 3- Part 1 : Oracle Database 12c Architecture
- Option 3- Part 2 : Downloading Oracle DB 12c
- Option 3- Part 3 : Installing Oracle DB 12c
- Option 3- Part 4 : Connecting SYS using SQL plus & SQL Developer
- Option 3- Part 5: Unlock HR Account Part 1
- Option 3- Part 6 : Unlock HR Account Part 2
- Option 3- Part 7 : ORA-01033 oracle initialization or shutdown in progress 12c
- Option 4- Installing Oracle 11g express Edition + Unlock HR
- About Oracle 18c express edition
- HR Schema in this Course
- Navigate HR schema
Overview of PL/SQL
- What is PL/SQL? What is the benefits?
- PL/SQL Block Structure & Types of Blocks
- Writing simple Anonymous block
- Using SQL*plus to run anonyms block
- Creating Script and run it
- Chapter 2 Quiz
Declaring PLSQL Variables
- About Variables and the syntax
- Defining Variables exercise
- Guidelines for declaring & initializing variables
- Delimiters in string literals (the q' Notation)
- Types of variables and data types 1
- Types of variables and data types 2
- Data Types exercise
- Define Variables Using %TYPE
- Understating the SELECT statement in PL/SQL
- Bind Variables
- Chapter 3 Quiz
Writing executable statements
- Inside PL/SQL Block
- Inside PL/SQL Block (exercise)
- Nested Blocks
- SQL & PL/SQL programing Guidelines
- Chapter 4 Quiz
Interacting with Oracle DB Server
- SELECT statement & DML in PL/SQL
- SELECT statement & DML in PL/SQL exercise
- Understanding Implicit SQL cursor/ SQL Attributes
- Implicit SQL cursor exercise
- Chapter 5 Quiz
Writing Control structure
- IF Statement Part 1
- IF Statement Part 2
- Handling nulls in IF statement
- CASE expression & CASE Statement
- Basic Loop
- While Loop
- FOR Loop
- Nested Loops
- Nested Loops and Labels
- Continue Statement
- Chapter 6 Quiz
Working with Composite Data Types
- PL/SQL Records: Programmer method part 1
- PL/SQL Records: Programmer method part 2
- PL/SQL Records: %ROWTYPE method
- Nested PL/SQL Records
- INDEX BY tables (Associative arrays)
- INDEX BY tables Methods
- INDEX BY tables of Records
- nested table
- Varray
- Chapter 7 Quiz
Using explicit cursors
- Understanding Explicit Cursor
- Explicit Cursor exercise
- Explicit Cursor Attributes
- FOR LOOP Cursor
- Cursor with Parameters
- FOR UPDATE Clause & CURRENT OF Clause
- Chapter 8 Quiz
Handling Exceptions
- Understanding Exceptions
- Predefined Oracle Server Error Exception
- Common mistakes when dealing with exceptions
- Non-Predefined Oracle Server Error Exception
- User-Defined Error Exception
- Group Functions and exceptions
- Many blocks and many exceptions
- Chapter 9 Quiz
Creating Procedure
- Understanding Procedures and subprograms
- Understanding Procedures Syntax
- Creating Procedure (IN parameter)
- Creating Procedure (OUT parameter)
- Creating Procedure (IN OUT parameter)
- Comparing the Paramter Modes
- Available notations for passing parameters
- Using the default option for Parameters
- Exception handling in multiple blocks
- Using Boolean and PL/SQL records as parameters
- Chapter 10 Quiz
Creating Functions
- Understanding user-defined functions
