Oracle PL/SQL Course Curriculum

• Overview of PL/SQL

1. What is PL/SQL? What are the benefits?
2. PL/SQL Block Structure & Types of Blocks
3. Writing simple anonymous block
4. Using SQL*plus to run anonymous block
5. Creating Script and run it
6. Quiz

• Declaring PL/SQL Variables

1. About Variables and the syntax
2. Defining Variables exercise
3. Guidelines for declaring & initializing variables
4. Delimiters in string literals (the q’ Notation)
5. Types of variables and data types 1
6. Types of variables and data types 2
7. Data Types exercise
8. Define Variables Using %TYPE
9. Understating the SELECT statement in PL/SQL
10. Bind Variables
11. Quiz

• Writing Executable Statements

1. Inside PL/SQL Block
2. Inside PL/SQL Block (exercise)
3. Nested Blocks
4. SQL & PL/SQL programming Guidelines
5. Quiz

• Interacting with Oracle DB Server

1. SELECT statement & DML in PL/SQL
2. SELECT statement & DML in PL/SQL exercise
3. Understanding Implicit SQL cursor/ SQL Attributes
4. Implicit SQL cursor exercise
5. Quiz

• Writing Control Structure

1. IF Statement Part 1
2. IF Statement Part 2
3. Handling nulls in IF statement
4. CASE expression & CASE Statement
5. Basic Loop
6. While Loop
7. FOR Loop
8. Nested Loops
9. Nested Loops and Labels
10. Continue Statement

• Working with Composite Data Types

1. PL/SQL Records: Programmer method part 1
2. PL/SQL Records: Programmer method part 2
3. PL/SQL Records: %ROWTYPE method
4. Nested PL/SQL Records
5. INDEX BY tables (Associative arrays)
6. INDEX BY tables Methods
7. INDEX BY tables of Records
8. Nested table
9. VARRAY

• Using Explicit Cursors

1. Understanding Explicit Cursor
2. Explicit Cursor exercise
3. Explicit Cursor Attributes
4. FOR LOOP Cursor
5. Cursor with Parameters
6. FOR UPDATE Clause & CURRENT OF Clause

• Handling Exceptions

1. Understanding Exceptions
2. Predefined Oracle Server Error Exception
3. Common mistakes when dealing with exceptions
4. Non-Predefined Oracle Server Error Exception
5. User-Defined Error Exception
6. Group Functions and exceptions
7. Many blocks and many exceptions

• Creating Procedures

1. Understanding Procedures and subprograms
2. Understanding Procedures Syntax
3. Creating Procedure (IN parameter)
4. Creating Procedure (OUT parameter)
5. Creating Procedure (IN OUT parameter)
6. Comparing the Parameter Modes
7. Available notations for passing parameters
8. Using the default option for Parameters
9. Exception handling in multiple blocks
10. Using Boolean and PL/SQL records as parameters

• Creating Functions

1. Understanding user-defined functions
2. Difference between procedures & functions
3. Creating function example
4. No_data_found and functions
5. Advantages of user-defined functions in SQL Statements
6. Restrictions when calling functions from SQL part 1
7. Restrictions when calling functions from SQL part 2

• Creating Packages

1. Understanding packages by nice example
2. More about packages
3. Creating package practice
4. Creating package specification without body
5. Guidelines for writing packages
6. The visibility of a package’s components
7. When & Why to recompile the package?

• Working with Packages

1. Understanding Overloading Subprograms in PL/SQL
2. Overloading Subprograms practices (procedures)
3. Overloading Subprograms practices (functions)
4. Overloading Standard package
5. Forward Declaration
6. Persistent State of packages
7. Using PRAGMA SERIALLY_REUSABLE
8. Persistent State and cursor
9. using PL/SQL tables in packages

• Using Oracle-supplied Packages in Application Development

1. About oracle-supplied Packages
2. DBMS_OUTPUT part 1
3. DBMS_OUTPUT part 2
4. UTL_FILE Package Part 1 / create directory
5. UTL_FILE Package Part 2 / get_line example 1
6. UTL_FILE Package Part 3 / get_line example 2
7. UTL_FILE Package Part 4 / put_line
8. UTL_FILE Package Part 5 / exceptions
9. Mail

• Using Dynamic SQL

1. Execution flow of SQL
2. What is Dynamic SQL?
3. Execute immediate example 1 / delete any table
4. Execute immediate example 2 / DDL in PLSQL
5. Execute immediate example 3 / USING Clause
6. Execute immediate example 4 / INTO Clause
7. Execute immediate example 5 / single row query
8. What is REF Cursor?
9. Dynamic SQL with multi row query
10. Execute immediate/ more examples
11. DBMS_SQL Package VS Execute immediate

• Design Consideration for PL/SQL Code

1. Standardizing Part 1
2. Standardizing Part 2
3. Definer’s Rights
4. Invoker’s Rights
5. Autonomous Transactions
6. Features Autonomous Transactions
7. The (IN) parameters always passed by reference
8. The (out/in out) parameters can be passed by value or reference
9. Passing by reference example (IN parameters)
10. Passing by value VS passing by reference using nocopy
11. When Does NOCOPY hint has no effects?
12. NOCOPY Performance Exercise
13. Using the PARALLEL_ENABLE hint
14. Using the RESULT_CACHE hint
15. Using the DETERMINISTIC hint
16. Understanding Bulk Binding
17. Bulk Binding Example
18. Bulk binding with save exception part 1
19. Bulk binding with save exception part 2
20. Understanding Bulk collect
21. Bulk collect another example
22. Bulk collect and cursors
23. Using returning in bulk collect
24. INDICES OF

• Creating Triggers

1. Understanding Triggers
2. Tigger Types/ Trigger Event Types
3. Statement Level trigger VS Row Level (Part 1)
4. Statement Level trigger VS Row Level (Part 2)
5. Triggers common cases
6. Statement Level trigger Example 1
7. Statement Level trigger Example 2
8. Understanding the NEW & OLD qualifiers
9. Row level trigger example
10. Row level trigger (create Audit table)
11. Trigger Firing sequence
12. Compile/enable/ disable/ drop trigger
13. Using the triggers for populating a default value
14. Instead of triggers (on Views)

Everyone

Passion and determination to achieve your goals!