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
| date | dur. | lang. | location | price | |
| 12 May | 2 | web based | 1250 EUR (excl. VAT) | ||
| 12 May | 2 | Leuven | 1250 EUR (excl. VAT) | ||
| SESSION INFO AND ENROLMENT | |||||
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 |