PLSQL Tutorial - A Whistle Stop Tour of PL/SQL Features (ctd)

In part 1 of this PLSQL tutorial, we had a quick look at what you can do with PL/SQL and some of the language features. Now let's explore some of the benefits of PL/SQL for developers and the types of applications to which PL/SQL is most suited.

Why Use PL/SQL?

By using PL/SQL you gain greater control over the processing of data queried from, or being entered into the database and as PL/SQL is also built into the Oracle Developer Suite (Oracle Forms & Reports), you use it in these tools to provide more control over the user interface and the display of results.

PL/SQL stored procedures are also ideal for implementing complex business logic and keeping this logic out of the user interface. This enables you to have a multi-tier system comprising of the user interface (the front end), a mid-tier containing the business logic and the actual data in the 3rd tier in the database, although this would be physically implemented in 2 tiers as the data and stored procedures are both in the database, or just 1 tier if the database and the front-end both reside on the same physical machine.

Of course there are many other technologies you could use to provide the user-interface and/or implement the business logic such as C# or Java but none of these has the tight coupling with the database of PL/SQL.

When To Use PL/SQL

We've looked at the why, so let's consider the when

There are 3 conditions when PL/SQL is most suitable:

When you want a versatile, flexible, high-level language that is tightly integrated with Oracle and with SQL.

When you're not worried about being dependent on Oracle. Although PL/SQL is portable from one platform to another (e.g. Windows to UNIX or Linux) PL/SQL is proprietary to Oracle and so you need an Oracle database to run it.

When you don't want to perform complex i/o or need a fancy graphical user interface. As mentioned earlier in this PLSQL tutorial, I/O is not a strong point of PL/SQL and it does not provide a gui.

Click here for PLSQL tutorial part 1 - PL/SQL language elements
This part looks at why and when to use PL/SQL
Part3 - how to integrate PL/SQL with SQL.

----------------

Looking for instructor-led Oracle PLSQL training? See here for Oracle PLSQL training in the UK or here for Oracle PL/SQL training in New Zealand.

Return to Introductory PLSQL Tutorial for an overview of pl/sql.