IT / Programming / IBM / DB2 / SQL PL Programming

DB2 - SQL PL Programming

Details

ID 2757613
Classroom 3 days
Webinar 5 days
Method Lecture with examples and exercises.
Prequisite General database knowledge
Audience DBAs, database developers

Services:

  • Lunch / Catering
  • Assistance for hotel / travel bookings
  • Comelio certificate
  • Flexible: Free cancellation up until 10 days before the training

Summary

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.

Training Dates

  • 2020-Nov-09 - Nov-13
  • 2021-Jan-18 - Jan-22
  • 2021-Mar-29 - Apr-02
  • 2021-Jun-07 - Jun-11

850 EUR +VAT

Location | Enrollment


Agenda

Fundamentals of SQL PL

Introduction - Usage Scenarios - Variables and Data Types - Mix SQL with SQL PL - Blocks and Labels

Control Structures

Loops (FOR, WHILE, REPEAT, LOOP) - Conditions (IF, CASE) - Program control with GOTO, ITERATE, LEAVE, RETURN

Cursors

Definition - Processing - Cursors for simple and multiple result sets - Data manipulation and cursor processing - Dynamic cursors

Errors and Exception Handling

Analysis using SQLCODE and SQLSTATE - Complex exception handling - Custom error messages with SIGNAL and RESIGNAL - Analysis with GET DIAGNOSTICS

Dynamic SQL

Simple dynamic SQL using EXECUTE IMMEDIATE - Prepared instructions with PREPARE and EXECUTE - Dynamic SQL in cursors

Procedures

Definition - Parameters - Programming simple and nested procedures - Cursors as return values

Functions

Definition - Parameters and return values - Scalar functions and Table-valued functions - Using functionen in SQL

Triggers

Definition - Before, After and Instead-Of Triggers - Data integrity through triggers - Triggers on row or on statement level

Techniques of Application Development

Identity and keys: identity columns, sequences - Versioning of data - Temporary tables - Materialized Query - Transactions: ROLLBACK, COMMIT, and savepoints -