The Scoop on SQL and PL/SQL - dummies

The Scoop on SQL and PL/SQL

By Michael Rosenblum, Paul Dorsey

As a database application developer, you interact with the Oracle DBMS by using the programming languages Structured Query Language (SQL, pronounced sequel) and Programming Language/Structured Query Language (PL/SQL, pronounced either P-L-S-Q-L or P-L-sequel).

The purpose of SQL and PL/SQL

SQL is the industry standard language for manipulating DBMS objects. Using SQL, you can create, modify, or delete database objects. This part of SQL is called Data Definition Language (DDL). You can also use SQL to insert, update, delete, or query data in these objects. This part of SQL is called Data Manipulation Language (DML).

Oracle’s implementation of SQL isn’t exactly industry standard. Virtually every DBMS (Oracle included) has invented items that are not part of the standard specification. For example, Oracle includes sequences and support for recursive queries that aren’t supported in other DBMS products.

Getting to know SQL in an Oracle environment allows you to work in almost any DBMS environment, such as SQLServer or MySQL, but you’ll encounter some differences in the DBMS environments. You should probably know SQL before trying to use PL/SQL.

PL/SQL is unique to Oracle. It isn’t industry standard. No other product uses it. Being able to use PL/SQL will help you work only within the Oracle database environment, but if you’re familiar with any other programming language, you’ll find that PL/SQL follows the same basic rules.

PL/SQL is similar to other non-object-oriented procedural programming languages, such as C or Pascal. Its intellectual roots go back to a programming language called Ada.

What makes PL/SQL unique is its tight integration with SQL. It is easier and more natural to embed SQL in PL/SQL than to do so in any other programming language. This makes PL/SQL ideal for writing large, complex programs that must interact with an Oracle database.

The difference between SQL and PL/SQL

SQL and PL/SQL are completely different languages. SQL is a limited language that allows you to directly interact with the database. You can manipulate objects (DDL) and data (DML) with SQL, but SQL doesn’t include all the things that normal programming languages have, such as loops and IF…THEN statements.

That is what PL/SQL is for. PL/SQL is a normal programming language that includes all the features of most other programming languages. But it has one thing that other programming languages don’t have, namely the easy ability to integrate with SQL.

What’s new in Oracle SQL and PL/SQL?

Oracle SQL and PL/SQL are evolving languages that constitute the backbone of applications written for the Oracle environment. Every version of the Oracle database expands the features of these languages. The production version of Oracle 10g Release 2 has recently been released. As with previous versions, this release offers lots of new things, including the following:

  • PL/SQL will probably run faster in the 10g version than it did in previous versions. You don’t have to do anything extra to benefit from that improvement. Oracle has made PL/SQL code run faster without requiring any additional work on the part of the programmer.
  • In SQL, many new commands allow you to retrieve information more easily than before.

Because every release brings new capabilities, keeping up with the new features in Oracle is important. Many developers don’t keep up with new features because “all the old features will still work,” but those developers miss out on the great new features included in each version. If you do a search for “new features in PL/SQL” or “new features in Oracle SQL” in Yahoo! or your favorite search engine, you’ll always find many articles and resources to show you the latest additions to these programming languages.