Introduction to PL/SQL
o What is PL/SQL
o PL/SQL Environment
o Benefits of PL/SQL
o Overview of the Types of PL/SQL blocks
o Create and Execute a Simple Anonymous Block
o Generate Output from a PL/SQL Block
o SQL*Plus as PL/SQL Programming Environment
Declaring PL/SQL Identifiers
o Identify the Different Types of Identifiers in a PL/SQL subprogram
o Use the Declarative Section to Define Identifiers
o List the Uses for Variables
o Store Data in Variables
o Declare PL/SQL Variables
Writing Executable Statements
o Describe Basic Block Syntax Guidelines
o Use Literals in PL/SQL
o Customize Identifier Assignments with SQL Functions
o Use Nested Blocks as Statements
o Reference an Identifier Value in a Nested Block
o Qualify an Identifier with a Label
o Use Operators in PL/SQL
o Use Proper PL/SQL Block Syntax and Guidelines
Interacting with the Oracle Server
o Identify the SQL Statements You Can Use in PL/SQL
o Include SELECT Statements in PL/SQL
o Retrieve Data in PL/SQL with the SELECT Statement
o Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements
o Manipulate Data in the Server Using PL/SQL
o The SQL Cursor concept
o Use SQL Cursor Attributes to Obtain Feedback on DML
o Save and Discard Transactions
Writing Control Structures
o Control PL/SQL Flow of Execution
o Conditional processing Using IF Statements
o Conditional Processing CASE Statements
o Handle Nulls to Avoid Common Mistakes
o Build Boolean Conditions with Logical Operators
o Use Iterative Control with Looping Statements
Working with Composite Data Types
o Learn the Composite Data Types of PL/SQL Records and Tables
o Use PL/SQL Records to Hold Multiple Values of Different Types
o Inserting and Updating with PL/SQL Records
o Use INDEX BY Tables to Hold Multiple Values of the Same Data Type
Using Explicit Cursors
o Cursor FOR Loops Using Sub-queries
o Increase the Flexibility of Cursors By Using Parameters
o Use the FOR UPDATE Clause to Lock Rows
o Use the WHERE CURRENT Clause to Reference the Current Row
o Use Explicit Cursors to Process Rows
o Explicit Cursor Attributes
o Cursors and Records
Handling Exceptions
o Handling Exceptions with PL/SQL
o Predefined Exceptions
o Trapping Non-predefined Oracle Server Errors
o Functions that Return Information on Encountered Exceptions
o Trapping User-Defined Exceptions
o Propagate Exceptions
o Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications
Creating Stored Procedures
o Describe the block structure for PL/SQL stored procedures
o Invoke a stored procedure/function from different tools
o Call a stored procedure with host variables from SQL*Plus, Forms, Java, C, etc
o Invoke a stored procedure from an anonymous block or another stored procedure
o List the CREATE OR REPLACE PROCEDURE syntax
o Identify the development steps for creating a stored procedure
o Use the SHOW ERRORS command
o View source code in the USER_SOURCE dictionary view
Creating Stored Functions
o Describe stored functions
o List the CREATE OR REPLACE FUNCTION syntax
o Identify the steps to create a stored function
o Execute a stored function
o Identify the advantages of using stored functions in SQL statements
o Identify the restrictions of calling functions from SQL statements
o Remove a function
Creating Packages
o List the benefits or using PL/SQL packages
o Differentiate between a package specification and a package body
o Create packages
o Include public and private constructs in a package
o Call public and private constructs in a package
o Remove packages
Using More Package Concepts
o Overload procedure and function definitions
o Use forward declarations
o Create a one-time package initialization block
o Follow the persistent state of constructs in packages
o Use PL/SQL tables and records in packages
o Wrap code to hide the source
Utilizing Oracle Supplied Packages in Application Development
o List the various uses for the Oracle supplied packages
o Reuse pre-packaged code to complete various tasks from developer to DBA purposes
o Use the DESCRIBE command to view the package specifications and overloading
o Describe how DBMS_OUTPUT works
o Use UTL_FILE to direct output to operating system files
o Use the HTP package to generate a simple web page
o Describe the main features of UTL_MAIL
o Call the DBMS_SCHEDULER package to schedule PL/SQL code to run
Dynamic SQL and Metadata
o Describe using native dynamic SQL
o List the execution flow of SQL
o Write dynamic SQL using the EXECUTE IMMEDIATE syntax
o Write dynamic SQL with the DBMS_SQL package
o Generate DDL from metadata using the DBMS_METADATA package
Design Considerations for PL/SQL Code
o Standardize constants with a constant package
o Standardize exceptions with an exception package
o Write PL/SQL code that uses local subprograms
o Use the NOCOPY compiler hint to pass parameters by reference
o Use the PARALLEL ENABLE hint for optimization
o Use the AUTONOMOUS TRANSACTION pragma to run independent transactions within a single transaction
o Set the AUTHID directive to execute programs with the privileges of the calling user instead of the creating user
o Use bulk binding for multi-row operations
Managing Dependencies
o Describe dependent and referenced objects
o Track procedural dependencies with dictionary views
o Predict the effect of changing a database object upon stored procedures and functions
o Manage local and remote procedural dependencies
Manipulating Large Objects
o Describe a LOB object
o Create and maintain LOB data types
o Differentiate between internal and external LOBs
o Use the DBMS_LOB PL/SQL package to control LOBs
o Describe the use of temporary LOBs
Creating Triggers
o Describe different types of triggers
o Describe database triggers and their use
o Create database triggers
o Describe database trigger firing rules
o Remove database triggers
Applications for Triggers
o Create database and system event triggers
o Create triggers on DDL statements
o Use the CALL statement in triggers to invoke procedures
o Explain the rules for reading and writing to tables with triggers
o Describe business application scenarios for implementing with triggers
o Manage trigger code
Understanding and Influencing the PL/SQL Compiler
o Describe native compilation and interpreted compilation
o List the features of native compilation
o Switch between native and interpreted compilation for compiled PL/SQL code
o Set the parameters to control aspects of PL/SQL compilation
o Write a query to retrieve information from the dictionary views on how the PL/SQL code is compiled
o Explain the compiler warning mechanism
o List the steps to use the compiler warnings
o Use DBMS_WARNING to implement compiler warnings
