SQL PL and SQL/PSM database programming

SQL/PSM (SQL/Persistent Stored Modules) is an ISO standard for "procedural logic" in SQL (since SQL:1999). So SQL/PSM is a programming language, mainly used to implement stored procedures, triggers and user-defined functions (UDFs) in several relational databases.

IBM's SQL PL (used in Db2) was the first commercial implementation of SQL/PSM. Also MySQL and MariaDB use SQL/PSM for their stored procedures, triggers, and UDFs; and in PostgreSQL one can use the module PL/pgPSM for this purpose.

Unfortunately, Oracle uses an other procedural language viz. PL/SQL (see Software development with PL/SQL) for which this course is not appropriate. PL/SQL is also used in PostgreSQL. A third procedural language, Transact-SQL (t-SQL), used by SQL Server and other Microsoft products, is also incompatible with SQL/PSM.

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

This course primarily covers the programming language SQL/PSM; 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/PSM during the course, either on Db2, MySQL, or MariaDB, in order to make themselves familiar with this material.

When completing this course,

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

Schedule

No public sessions are currently scheduled. We will be pleased to set up an on-site course or to schedule an extra public session (in case of a sufficient number of candidates). Interested? Please let us know.

Intended for

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

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

Background

Some experience with SQL and with one of the following RDBMS is needed: Db2 (cf. Db2 for z/OS fundamentals course or Db2 for LUW fundamentals course), MySQL or MariaDB (cf. MySQL & MariaDB fundamentals course). Knowledge of an other procedural programming language might be an advantage, but is not needed.

Main topics

  • Introduction SQL/PSM
  • 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.

Also interesting

Enrollees for this training also took the following courses:


SESSION INFO AND ENROLMENT