Introduction
- Description of PL/SQL
-
Capabilities of PL/SQL
-
Advantages of using PL/SQL
Declaring Variables
- Anonymous PL/SQL block and its sections
-
Types of PL/SQL programming constructs
-
Defining and using PL/SQL variables
-
Declaring PL/SQL variables and constants
-
Executing PL/SQL block
Writing executable commands
- The executable section of the block
-
Rules for nested blocks
-
Executing and testing PL/SQL block
-
Using coding conventions
Interaction with Oracle® Server
- Features of SELECT-statement in PL/SQL block
-
Dynamic declaration of data types
-
Writing data manipulation statements (DML) in PL/SQL block
-
Transaction management in PL/SQL
-
Defining the output of DML statements
The use of control structures
- Types of control structures
-
Using the IF statement
-
Expressions and CASE statement
-
Various types of loops
-
Controlling handling using nested loops and labels
-
Using logical tables
Working with composite data types
- Creating user-defined PL/SQL records
-
Adding and updating tables using PL/SQL records. Associative arrays
Using Cursors
- Differences between implicit and explicit cursor
-
Statements for handling explicit cursor
-
Explicit cursor attributes
-
FOR loop for the cursor
-
Cursors with parameters
-
Using FOR UPDATE in the definition of the cursor
-
Cursors with subqueries
Exception handling
- Defining PL/SQL exceptions
-
Propagation of unhandled exceptions
-
Types of PL/SQL exception handlers
-
Interception of non-predefined exceptions
-
Exception propagation in nested blocks
-
Generation of custom PL/SQL error messages
Creating stored procedures and functions
- Creating and calling a procedure
-
Getting information about procedures and deleting procedures
-
Creating and calling functions
-
Getting information about the functions and deleting functions
Working with packages
- Package structure
-
Package design: header and body
-
Creating packages
-
Advantages of using packages
Using Triggers
- The principle of operation
-
Types of triggers
-
Creating table level triggers