INTRODUCTION TO PL/SQL
Benefits of PL/SQL
Overview of the Types of PL/SQL blocks
DECLARING PL/SQL IDENTIFIERS
Identify the Different Types of Identifiers
List the Uses for Variables
Declare PL/SQL Variables
WRITING EXECUTABLE STATEMENTS
Basic Block Syntax Guidelines
Use Operators in PL/SQL
INTERACTING WITH THE ORACLE SERVER
Include SELECT Statements in PL/SQL
Manipulate Data in the Server Using PL/SQL
The SQL Cursor concept
WRITING CONTROL STRUCTURES
Conditional processing Using IF & CASE Statements
Use Iterative Control with Looping Statements
WORKING WITH COMPOSITE DATA TYPES
Learn the Composite Data Types of PL/SQL Records and Tables
Inserting and Updating with PL/SQL Records
Use INDEX BY Tables
USING EXPLICIT CURSORS
Cursor FOR Loops
Use the FOR UPDATE Clause to Lock Rows
Use the WHERE CURRENT Clause to Reference the Current Row
HANDLING EXCEPTIONS
Predefined Exceptions
Propagate Exceptions
Use The RAISE_APPLICATION_ERROR Procedure
CREATING STORED PROCEDURES
Differentiate between formal and actual parameters
Parameter modes
CREATING STORED FUNCTIONS
List the CREATE OR REPLACE FUNCTION syntax
Using stored functions in SQL statements
CREATING PACKAGES
List the benefits or using PL/SQL packages
Create & Remove packages
USING MORE PACKAGE CONCEPTS
Overload definitions
Use forward declarations
Create a one-time package initialization block
Wrap code to hide the source
UTILIZING ORACLE SUPPLIED PACKAGES
Describe & use how DBMS_OUTPUT, UTL_FILE, HTP, UTL_MAIL, DBMS_SCHEDULER package to schedule PL/SQL code to run
DYNAMIC SQL AND METADATA
Write dynamic SQL using the EXECUTE IMMEDIATE syntax & with the DBMS_SQL package
Generate DDL from metadata using the DBMS_METADATA package
DESIGN CONSIDERATIONS FOR PL/SQL CODE
Use the NOCOPY, PARALLEL ENABLE hint
Use the AUTONOMOUS TRANSACTION pragma
Set the AUTHID directive
MANAGING DEPENDENCIES
Describe dependent and referenced objects
Track procedural dependencies with dictionary views
Manage local and remote procedural dependencies
MANIPULATING LARGE OBJECTS
Create and maintain LOB data types
Use the DBMS_LOB PL/SQL package to control LOBs
CREATING TRIGGERS
Describe different types of triggers
Create database triggers
Describe database trigger firing rules
Remove database triggers
APPLICATIONS FOR TRIGGERS
Create database and system event triggers
Create triggers on DDL statements
Explain the rules for reading and writing to tables with triggers
Manage trigger code
UNDERSTANDING AND INFLUENCING THE PL/SQL COMPILER
Describe native compilation and interpreted compilation
Explain the compiler warning mechanism
Use DBMS_WARNING to implement compiler warnings