Essential Oracle 18c PL/SQL training course

Synopsis for Essential Oracle 18c PL/SQL training course

Its extended SQL right ? right? Wrong ! PLSQL is a programming language which can stand up against the big boys of C++, Java, PHP etc, what Procedural Language is, is a language specifically designed for use within an Oracle database; having mastered Oracle SQL on one of our other courses (the two days before if you are taking a public course), PLSQL training is the next logical step for a Developer working on an Oracle Database.

What's an Oracle Developer? Well it can be anyone who needs to work on the Oracle Database performing more complex functions on data that SQL on its own cannot provide, maybe moving onto producing front end products or administrating the Database. One thing to be sure you cannot learn PLSQL if you have not already learned SQL.

Why is PLSQL necessary? SQL is a very powerful language, it can create Databases, maintain them, create objects and populate them, at this (and other things) SQL is very good and the most efficient way of dealing with the Database, however SQL does have its limitations; SQL cannot for example loop through tables making decisions with each record it finds, of course expert SQL developers will talk about using inner joins and unions etc but the bottom line is, SQL needs a hand now and again to keep the Database functional, useful and protected.

Protected? When learning SQL you would have come across constraints, the most common of these are indexes, Primary, Unique, Foreign etc … in addition the table creator can create constraints that force a value into a column if a null or nothing has been provided, indeed the very structure of the table will force a column to only accept dates or numbers where specified, however that is the limit of constraints using SQL. If the Developer wishes to limit date of birth to only those born from 2000 onwards then a PLSQL trigger will need to be created, this can be programmed to ensure the date being inserted or updated follows the defined Business rule, triggers are an essential part of this course and the Delegate will learn how to wield them to protect the data.

The PLSQL course starts at the very beginning (to quote Julie Andrews in the Sound of Music), the first thing examined is the basic structure of a PLSQL statement, this will be commonly used for the first day of the course before the Delegate moves on to embedding PLSQL objects such as Functions, Procedures, Packages and Triggers into the Database. Once the basic structure is explained each 'block' in the structure is discussed regarding what can go where and more importantly what errors will occur when the rules are not followed, error handling and communicating with the Database are added and then the Delegate will move onto more interesting topics such as looping through the tables, logic gates in PLSQL and performing rudimentary SQL statements.

Did we say rudimentary SQL statements? Well one of the great secrets of SQL within PLSQL is that SQL statements such as Insert, Update and Delete can be performed within the PLSQL processing block without too much hassle, however SQL commands such as Select, Create etc need a little bit more handling; Select statements are dealt with early in the course when we introduce the cursors; Implicit and Explicit, they are vital to the PLSQL to query on the Database, however creating a new table for example will need Dynamic SQL and we discuss that right at the end of the course (take a look below this synopsis, the index is given for the course).

So for the first day or so we concentrate on the fundamentals of PLSQL, the building blocks for the objects needed in the Database, once these have been covered the course will move onto Functions, Procedures and finally after these Database triggers. SQL developers will already be aware of functions in the Database, this course teaches them how to write and use their own, with the best will in the world Oracle cannot provide Functions which will deal with any type of data value and convert according to the business rules of the Company; an example of this might be checking a postcode against a post town value … the number of Companies we have worked with in the past which have invalid postcodes for a City or vice versa, so create a Function which checks the City for a given postcode … Oracle definitely don’t provide one (we should know we have created an App on iOS and iPhone which not only lists all Oracle database functions, but also gives very comprehensive examples of their usage).

Many delegates will ask what the difference is between a Function and a Procedure, and the major difference is how the User interacts with each one; A Procedure can be viewed as a standalone process one which can be 'kicked off' and left to its own devices, maybe one which correlates productivity or sales on a daily overnight run, a Function would be ideally used to return a value following a request from the User with a provided original piece of data and because it is expected to return a value, a PLSQL function does not provide as much flexibility as a Procedure does … but more of that in the course !! What about Packages? Well they are a way of efficiently storing Functions and Procedures in the Database, grouping together processes which have a common theme or purpose.

Finally the other big PLSQL object in the Database … Triggers … they are event driven pieces of PLSQL, whereas Functions and Procedures need to be called, triggers react to an event taking place on the table they have been created on. These events are an Insert, Update or Delete of data taking place and can be used to validate the data going in and indeed prevent the changes if the changes are against the specified rules, we like Triggers, they are one of the ultimate ways of protecting the valuable data in the Database and form the last line of defence against accidental and wilful SQL skulduggery.

So three days of PLSQL programming, we don’t expect you to remember 100% of the course (especially the syntax) however we like to think you will remember that there is a method to achieve whatever circumstance you need to use PLSQL for and know that our comprehensive manual will have an example of the direction you need to take.

Just a few words about this course, we have already mentioned that we expect a Delegate to have prior knowledge of Oracle SQL before attending the course, this is why on our Public courses in London, Cardiff, Dublin etc (see the whole list off the menu) we convene the two day SQL course on the Monday/Tuesday before the Wednesday start of this course, but in addition be aware that we can come to your offices to give the course, we bring our own workshop equipment and all we need is enough tables and chairs for both the Presenter and the attendees to sit and learn comfortably in.