SQL PL database programming

Since version 9, Db2 has greatly extended its support of the SQL 'Procedural Language', known as SQL PL. Currently essentially all syntax possibilities of the SQL standard for procedural logic inside SQL are supported. Recently, Db2 even added support for the "ARRAY" data type, only within SQL PL.

Also MySQL (since version 5) and MariaDB support SQL PL for implementing stored procedures, triggers, and user defined functions (UDFs). Db2, MySQL and MariaDB thereby serve, together with PostgreSQL, as vanguard in the implementation of the "Persistent Stored Modules" (PSM) of the SQL standard for ISO and ANSI (since SQL:1999).

Unfortunately, Oracle uses an other procedural language viz. PL/SQL (see Software development with PL/SQL) for which this course is not appropriate. Also Transact-SQL, used by SQL Server, is not compatible with SQL PL.

SQL PL is especially useful for creating stored procedures, which will more and more replace applications which in the earlier days were mainly written in (for z/OS) COBOL, PL/I, REXX, (for Unix) Java, C, C++, (for MS-Windows), and Java, or C#. SQL PL will thus become the dedicated programming language for integrating application logic with embedded SQL. On Db2, this automatically means that the procedure will be "static SQL": it will be interpreted just once, and then executed several times, which is an important performance benefit.

This course primarily covers the programming language SQL PL; all syntactic details and possibilities will be treated in sufficient detail. This does of course 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 have the opportunity to develop themselves some stored procedures with SQL PL during the course, either on Db2 for z/OS, on Db2 for LUW, on PostgreSQL, or on MySQL, in order to make themselves familiar with this new material.

When completing this course,

  • participants will know the basic syntax of the SQL PL programming language;
  • one will be able to develop, debug and execute new programs;
  • they will know the caveats when porting existing program modules in an other programming language;
  • the participants will be able to use cursors and exception handlers in clever ways.

Schedule

datedur.lang.locationprice 
26 Mar2Leuven 1050 EUR (excl. VAT)
28 May2Woerden 1050 EUR (exempt from VAT)
SESSION INFO AND ENROLMENT

Intended for

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

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.

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

Training method

Classroom instruction with exercises.

Duration

2 days.

Course leader

Peter Vanroose.

Reviews

J'ai appris pas mal de choses intéressantes

 
  (, )

Satisfait, explications détaillées sur les chapitres, avec le lien avec le système db2

 
  (, )

Cours très intéressant. Mes objectifs personnels vis à vis de ce cours sont atteints et même dépassés.

 
  (, )

bon pour les bases

 
  (, )

SESSION INFO AND ENROLMENT