PL/SQL

PL/SQL Course Outline

1. Overview

1.1. Features of PL/SQL
1.2. Advantages of PL/SQL

2. Environment Setup

2.1. Text Editor

3. Basic Syntax

3.1. The ‘Hello World’ Example
3.2. The PL/SQL Identifiers
3.3. The PL/SQL Delimiters
3.4. The PL/SQL Comments
3.4. PL/SQL Program Units

4. Data Types

4.1. PL/SQL Scalar Data Types and Subtypes
4.2. PL/SQL Numeric Data Types and Subtypes
4.3. PL/SQL Character Data Types and Subtypes
4.4. PL/SQL Boolean Data Types
4.5. PL/SQL Datetime and Interval Types
4.6. PL/SQL Large Object (LOB) Data Types
4.7. PL/SQL User-Defined Subtypes
4.8. NULLs in PL/SQL

5. Variables

5.1. Variable Declaration in PL/SQL
5.2. Initializing Variables in PL/SQL
5.3. Variable Scope in PL/SQL
5.4. Assigning SQL Query Results to PL/SQL Variables

6. Constants and Literals

6.1. Declaring a Constant
6.2. The PL/SQL Literals

7. Operators

7.1. Arithmetic Operators
7.2. Relational Operators
7.3.` Comparison Operators
7.4. Logical Operators
7.5. PL/SQL Operator Precedence

8. Conditions

9. Loops

9.1. Labeling a PL/SQL Loop
9.2. The Loop Control Statements

10. Strings

10.1. Declaring String Variables
10.2. PL/SQL String Functions and Operators

11. Arrays

11.1. Creating a Varray Type

12. Procedures

12.1. Parts of a PL/SQL Subprogram
12.2. Creating a Procedure
12.3. Executing a Standalone Procedure
12.4. Deleting a Standalone Procedure
12.5. Parameter Modes in PL/SQL Subprograms
12.6. Methods for Passing Parameters

13. Functions

13.1. Creating a Function
13.2. Calling a Function
13.3. PL/SQL Recursive Functions

14. Cursors

14.1. Implicit Cursors
14.2. Explicit Cursors
14.3. Declaring the Cursor
14.4. Opening the Cursor
14.5. Fetching the Cursor
14.6. Closing the Cursor

15. Records

15.1. Table-Based Records
15.2. Cursor-Based Records
15.3. User-Defined Records

16. Exceptions

16.1. Syntax for Exception Handling
16.2. Raising Exceptions
16.3. User-defined Exceptions
16.4. Pre-defined Exceptions

17. Triggers

17.1. Creating Triggers
17.2. Triggering a Trigger

18. Packages

18.1. Package Specification
18.2. Package Body
18.3. Using the Package Elements
The Package Specification
Creating the Package Body
Using The Package

19. Collections

19.1. Index-By Table
19.2. Nested Tables
19.3. Collection Methods
19.4. Collection Exceptions

20. Transactions

20.1. Starting and Ending a Transaction
20.2. Committing a Transaction
20.3. Rolling Back Transactions
20.4. Automatic Transaction Control

21. Date & Time

21.1. Field Values for Datetime and Interval Data Types
21.2. The Datetime Data Types and Functions
21.3. The Interval Data Types and Functions

22. DBMS Output

22.1. DBMS_OUTPUT Subprograms

23. Object Oriented

23.1. Instantiating an Object
23.2. Member Methods
23.3. Using Map method
23.4. Using Order method
23.5. Inheritance for PL/SQL Objects
23.6. Abstract Objects in PL/SQL