home 
 
 
 
nlfr
 
Home
About ABIS
Services
Courses
Resources
Contact
MyABIS
C
All CoursesbalkjeGeneral » Introduction to HW & SW » Soft skills » TracksOperating systems » MVS - z/OS » UNIX - Linux - AIX » Mac OS X » iPad and iPhone iOSDatabases and middleware » Relational databases & SQL » DB2 for z/OS » DB2 for LUW » Oracle » SQL Server » MySQL & MariaDB » IMS » CICS » IBM MQ » WebSphere » Big data and analyticsApplication development » Methods and techniques » TOGAF » PRINCE2 » Agile development and Scrum » Programming languages » Internet development » Object Oriented systems » Java » Development tools » SAS » XML » SOA & web servicesSystems management » ITIL » SecuritybalkjePractical informationRegistration 
SQL PL database programming

Objectives

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 5 supports SQL PL for implementing stored procedures, triggers, and user defined functions (UDFs). Together with PostgreSQL, both DB2 and MySQL thereby serve 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 benifit.

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.

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 • parametrisation • 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'

PREPARE and EXECUTE of a dynamically built query

 

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. Forthermore 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 (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

Classroom instruction with exercises.

Course leader

Peter Vanroose.

Duration

2 days.

Schedule

You can enrol by clicking on a date
datedur.lang.  location  price
23 Nov2?Leuven  (BE)1000 EUR  (excl. VAT) 

Reviews

     
zeer leerrijke cursus (, )
     
Goede uitleg van de theorie en een goede begeleiding bij het maken van de oefeningen. (, )
     
Bon cours. Très utile. Un peu plus de temps pour la pratique que aurait pu être utilisé pour voir l'orienté objet de manière plus approfondie. (, )
     
Niveau misschien wat hoog voor sommige voorbeelden (wegens tot nu ongebruikelijk in onze omgeving). (, )
     
Voldoende voor mijn behoefte aan kennis (, )
     
interessante cursus. Misschien spreiden over 3 dagen zodat de oefeningen kunnen afgewerkt worden. Ofwel de oefeningen wat simpeler houden (, )
     
Cours avec une bonne approche par support aux exercices. Formateur maitrisant son sujet. (, )
     
Goede cursus, goede oefeningen (, )
     
Correct mais un peu trop de temps pour la pratique. (, )
     
Beaucoup de pratique avec explication et aide constante permettent un apprentissage rapide (, )

Refresh this page to see other comments.