IT / Programming / IBM / DB2 / SQL PL Programming
| ID | 2757613 |
|---|---|
| Classroom | 3 days 9:00-16:30 |
| Webinar | 5 days 9:00-12:30 |
| Method | Lecture with examples and exercises. |
| Prequisite | General database knowledge |
| Audience | DBAs, database developers |
SQL PL, is a subset of SQL that provides procedural constructs that can be used to implement logic around traditional SQL statements. SQL PL is a high level programming language with a simple syntax, and common programming control statements. SQL PL procedures and functions can contain parameters, variables, assignment-statements, SQL PL control statements, and compound SQL statements. SQL PL procedures also support a powerful condition and error handling mechanism, nested and recursive calls, the returning of multiple result sets to the caller or the client application. This trainings explains you how to use SQL PL for scripting common DB-related tasks or writing functions and procedures with complex logic.
Introduction - Usage Scenarios - Variables and Data Types - Mix SQL with SQL PL - Blocks and Labels
Loops (FOR, WHILE, REPEAT, LOOP) - Conditions (IF, CASE) - Program control with GOTO, ITERATE, LEAVE, RETURN
Definition - Processing - Cursors for simple and multiple result sets - Data manipulation and cursor processing - Dynamic cursors
Analysis using SQLCODE and SQLSTATE - Complex exception handling - Custom error messages with SIGNAL and RESIGNAL - Analysis with GET DIAGNOSTICS
Simple dynamic SQL using EXECUTE IMMEDIATE - Prepared instructions with PREPARE and EXECUTE - Dynamic SQL in cursors
Definition - Parameters - Programming simple and nested procedures - Cursors as return values
Definition - Parameters and return values - Scalar functions and Table-valued functions - Using functionen in SQL
Definition - Before, After and Instead-Of Triggers - Data integrity through triggers - Triggers on row or on statement level
Identity and keys: identity columns, sequences - Versioning of data - Temporary tables - Materialized Query - Transactions: ROLLBACK, COMMIT, and savepoints -