Oracle PL/SQL

In the Oracle PL/SQL training course, you'll learn:

• Overview of PL/SQL
  • PL/SQL Programming Standards
• Declaring PL/SQL Variables and Types

• Data and Record Types
• Working with Arrays and Collections
• Using attributes to make declarations

• Control Structures
  • Using various IF THEN statements
  • Working with Nested IF statements
  • Control flow with simple and searched CASE statements
• Looping Through Data
  • The basic LOOP
  • Using the FOR LOOP to execute code n number of times
  • Conditional LOOP using WHILE
  • Exiting LOOP using CONDITION statements
• Embedding SQL within PL/SQL
  • Using implicit cursors
  • Fetch single rows using SELECT INTO
  • Using INSERT, UPDATE AND DELETE
  • Why do I need COMMIT and ROLLBACK?
  • Using statement attributes SQL%FOUND, SQL%NOTFOUND and SQL%ROWTYPE
• Explicit Cursors
  • Execution cycle of an explicit cursor
  • How to declare, open, fetch and close a cursor
  • Choosing a Loop Structure
  • Executing a FOR Loop Cursor
• Understanding How to Handle Exceptions
  • Constructing exception handlers
  • Using predefined and user-defined exceptions
  • Examples using DECLARE, RAISE and HANDLE
  • Propagation of Exceptions
• Creating Reusable Code with Stored Procedures
  • Understanding the constructs of stored procedures
  • Using Functions within your stored procedures
  • Executing stored procedures
  • What is the difference between a stored procedure and a function?
• Using Packages to Compile Different Functionality
  • Why would you need Packages?
  • Package specifications
• Working with Collections in PL/SQL
  • Overview of types of Collections
    • Nested Tables
    • VARRAYs
    • Associative Arrays
  • Why do I need Arrays?
• Using Triggers to React to Events
  • What use cases do triggers apply to?
  • Creating triggers based on events
  • Statement level triggers
  • Row-level triggers
  • Using the INSTEAD OF trigger
  • How to enable, disable or drop triggers
• Executing BULK Operations to Improve Performance
  • Why do I need BULK operations?
  • Using SELECT BULK COLLECT
  • Creating FORALL statements
  • Using INSERT, UPDATE and DELETE
  • What options do I have with BULK operations?
• Best Practices and Performance Tuning
  • Using the PL/SQL Optimizer
  • Minimizing CPU overhead
  • Executing parallel updates
  • Profiling and tracing