SQL PL database programming (EN/NL/FR)

This 2 day ABIS course covers the programming language SQL PL; all syntactic details and possibilities will be treated in detail. This does include the interaction between the elements of the programming environment (like variables, program flow, exception handling) and the embedded DML ingredients like SELECT statements or cursors. The participants will develop themselves stored procedures with SQL PL, either on Db2 for z/OS, on Db2 for LUW, on PostgreSQL, or on MySQL.

This course is intended for everybody interested in the SQL PL language; prior SQL knowledge is adviced.

Remark: Course description in English; Dutch and French versions are available on the ABIS website. Courses are planned in Dutch, English, and French. Consult the ABIS website for alternate course formats.

Main topics:

• Introduction SQL PL
• Variables
  • declaration, initialisation, use; BEGIN ... END blocks
• Program flow
  • conditional structures IF...THEN...ELSE structure; CASE-statement;
  • iterations: WHILE..DO loop • FOR...DO loop • ITERATE • REPEAT...UNTIL • LOOP • cursor-based FOR-loop
• Working with database objects; cursor management:
  • SELECT INTO • declaration and use of cursors • parametrization • insert, delete, update 'where current of' cursor
• Error handling
  • predefined exceptions • applicatively defined exception handling • SIGNAL • GET DIAGNOSTICS
• Procedural objects
  • user-defined scalar functions • native stored procedures • triggers • SQL CALL-statement;
  • possibilities of parameter declaration and 'locators'; possible options like 'commit on return'
• Dynamic SQL
  • PREPARE and EXECUTE of a dynamically built query

Intended for/Audience: This course is first of all meant for application programmers and developers of programs that make use of Db2 or MySQL, and who want to learn how to write this kind of programs with SQL PL.

Also DBAs will be able to build up the necessary experience during this course to build or deploy stored procedures, triggers or user-defined functions. Furthermore they will gather useful ideas for automating some DBA tasks in a simple way.

Background/Prerequisites: Some experience with SQL and with Db2 (cf. Db2 for z/OS fundamentals course or Db2 for LUW fundamentals course), MySQL or MariaDB (cf. MySQL & MariaDB fundamentals course) or PostgreSQL is needed. Knowledge of an other procedural programming language (like COBOL, PL/I, REXX, Java, C, ...) might be an advantage.

Training Method/Didactics: Classroom instruction with exercises.

Duration: 2 days.