Oracle® 11g, Basics of PL/SQL Language

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