Introduction to PL/SQL
- Why PL/SQL – the benefits over plain SQL
- The block structure: DECLARE, BEGIN, EXCEPTION, END
- Executing anonymous blocks, output with DBMS_OUTPUT
- Tools: SQL Developer and the VS Code extension for Oracle
Variables and Data Types
- Declaring variables and constants
- Working type-safely with %TYPE and %ROWTYPE
- Composite types: records
- Scope and visibility
- New in the 23ai/26ai line: BOOLEAN as a genuine SQL data type
SQL Inside PL/SQL
- SELECT INTO as well as INSERT, UPDATE and DELETE within the block
- Implicit cursors and their attributes such as SQL%ROWCOUNT
- Transaction control within PL/SQL
- Using sequences and SQL functions
Control Structures
- IF, ELSIF and CASE
- Basic loop, WHILE and FOR, nested loops
- CONTINUE, EXIT WHEN and loop labels
- The cursor FOR loop as the standard pattern
Explicit Cursors and Collections
- Declaring, opening, reading and closing cursors
- Cursors with parameters, FOR UPDATE and WHERE CURRENT OF
- Associative arrays, nested tables and VARRAYs at a glance
- A look ahead at BULK COLLECT and FORALL
Error Handling
- Predefined and custom exceptions
- RAISE, RAISE_APPLICATION_ERROR and PRAGMA EXCEPTION_INIT
- SQLCODE, SQLERRM and FORMAT_ERROR_BACKTRACE
- Guidelines for robust error handling
Subprograms, Packages and Triggers
- Procedures and functions, parameter modes IN, OUT and IN OUT
- Developing modularly instead of repeating code
- Packages: specification and body
- Developing and using triggers
- Dynamic SQL with EXECUTE IMMEDIATE
Debugging, Dependencies and Outlook
- Debugging in SQL Developer
- Understanding dependencies between objects
- Fundamentals of code optimisation
- Outlook: SQL Firewall from a developer’s perspective, DBMS_VECTOR and Select AI
