Oracle PL/SQL Tutorial -
A Whistle Stop Tour

This is the first PL/SQL tutorial in the series and provides an overview of the language and its capabilities as well as links to enable you to explore further.

In this tutorial we'll answer the following questions:

  • what is PL/SQL? 
  • What can it do?

What is PL/SQL?

Learn the fundamentals of PL/SQL in our short video which shows you how to write a program to display "Hello World" in about 30 seconds!

PL/SQL is Oracle's proprietary, block-structured, strongly-typed, procedural programming language for manipulating data and interacting with the database.

The procedural part is important - the language provides you with all the things that SQL lacks like user-defined functions, loops, control statements (though SQL does provide some control in the form of the CASE clause, and the DECODE and NVL functions.). In fact the name stands for "Procedural Language extensions to the Structured Query Language".

Overall PL/SQL is a high-level language very similar to Pascal and ADA (from which it is derived). As Pascal was designed as a teaching language, this makes it easy to learn and to use and it promotes good programming practice. Although it is still possible to write bad code you just have to try that bit harder to do so!

Oracle Tips & Tricks
to SKYROCKET Your Career!

If you're not already a subscriber to our newsletter you're missing out on myriad of tips and techniques to help you become a better, faster, smarter developer. Subscribe now and ignite your career

PL/SQL has been described as the poor man's object-oriented language, but that was harsh even before the specific object-oriented features were added in Oracle8. It is a very powerful and flexible language which, whilst not strictly object-oriented like Java, still has some o-o features, such as encapsulation, data hiding, inheritance, object types etc.

However, not all object-oriented features are available in all versions of PL/SQL The higher the version you're using, the more o-o features are available. Coverage of the object-oriented features has been deferred to a separate tutorial.

What can you do with PL/SQL?

What can you do with it? The answer is plenty.

As hinted at earlier, you can define your own functions which can be embedded in SQL statements in the same way as the built-in functions.

You can also use it to write complete programs to implement business logic, furthermore you can store these programs in the database itself (and take advantage of the extra power of the server) or in Oracle Forms and Reports.

Stored procedures can be invoked from SQL, or other languages such as Java, C, FORTRAN or Visual Basic (these topics are outside the scope of this PL/SQL tutorial).

In short there is little that you can't do - as long as you don't need fancy i/o or file handling features. The only language element for displaying information directly to the end-user is the DBMS_OUTPUT package, whilst the (limited) file handling capabilities are provided by the UTL_FILE package. 

That was just a brief overview of the language - this PL/SQL tutorial continues with