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
