Please note that this list of topics is based on our standard course offering, evolved from typical industry uses and trends. We'll work with you to tune this course and level of coverage to target the skills you need most. Topics, agenda and labs are subject to change, and may adjust during live delivery based on audience interests, skill-level and participation.
1. Getting Started
- Quick Overview of Oracle Database 19c
- Available PL/SQL development environments
- Oracle documentation and additional resources
2. Introduction to PL/SQL
- Understanding the benefits and structure of PL/SQL
- Understanding PL/SQL blocks
- Generating output messages in PL/SQL
3. Declaring PL/SQL Variables
- Introducing variables
- Handling variables of different types
- Using the %TYPE attribute and composite data types
- Using bind variables
4. Writing Executable Statements
- Writing executable statements in a PL/SQL block
- Writing nested blocks
- Using operators and developing readable code
5. Using SQL Statements Within a PL/SQL Block
- SQL statements in PL/SQL blocks
- Manipulating data with PL/SQL
- Introducing SQL cursors
6. Writing Control Structures
- Using IF statements
- Using CASE statements and CASE expressions
- Constructing and identifying loop statements
7. Working with Composite Data Types
- Understanding composite data types
- Using PL/SQL records
- Manipulating data with PL/SQL records
- Advantages of the %ROWTYPE attribute
- Using PL/SQL collections
- Examining associative arrays
- Introducing nested tables
- Introducing VARRAY
8. Using Explicit Cursors
- What are explicit cursors?
- Using explicit cursors
- Using cursors with parameters
- Locking rows and referencing the current row
9. Handling Exceptions
- Define PL/SQL exceptions
- Recognize unhandled exceptions
- List and use different types of PL/SQL exception handlers
- Trap unanticipated errors
- Describe the effect of exception propagation in nested blocks
- Customize PL/SQL exception messages
10. Introducing Stored Procedures and Functions
- Differentiate between anonymous blocks and subprograms
- Create a simple procedure and invoke it from an anonymous block
- Create a simple function
- Create a simple function that accepts a parameter
- Differentiate between procedures and functions
11. Using SQL*Plus
- Log in to SQL*Plus
- Edit SQL commands
- Format the output by using SQL*Plus commands
- Interact with script files
12. Using SQL Developer
- Identify the menu items of Oracle SQL Developer
- Create a database connection
- Manage database objects
- Use SQL Worksheet
- Save and run SQL scripts
- Create and save reports
13. Commonly Used SQL Commands
- Execute a basic SELECT statement
- Create, alter, and drop a table
- Insert, update, and delete rows
- Perform join operations
14. REF Cursors
- Cursor Variables
- Defining REF CURSOR Types
- Using the OPEN-FOR, FETCH, and CLOSE Statements
