How to differentiate between SQL and PL/SQL?

Question

I know the question might sound too stupid, but I never understood this part.

SQL*Plus works with both SQL and PL/SQL. How do I know whether some code is SQL or PL/SQL? If my code has a for loop, is it not SQL anymore?

PL/SQL is an extension for SQL to have loops, conditionals etc. Then any SQL code is by default PL/SQL code? Isn't it so?

Is there a demarcation between SQL and PL/SQL?

Two examples of differentiating b/w SQL and PL/SQL that triggerred this question:

What is the difference between these two create table statements?

https://stackoverflow.com/questions/2267386/oracle-11g-varray-of-objects/2267813#2267813

Answer 1

Its an interesting question, to be sure. Most people who are familiar with Oracle development wouldn't give it a thought but when you come down to it, its sometimes confusing to define the demarcation between SQL and PL/SQL.

By looking at the definition of the acronyms, you start to get an idea of what areas of functionality each covers:

SQL - Structured Query Language

PL/SQL - Procedural Language / Structured Query Language

The observant reader might notice how SQL shows up twice 8) That's because SQL is often embedded within PL/SQL - PL/SQL is a language that was made to provide a proprietary 4th generation language (4GL) that plays very well with database objects in Oracle.

Wikipedia has some pretty good material on both SQL and PL/SQL

The confusing part is where PL/SQL and SQL overlap a bit. SQL's purview includes data insert, query, update and delete, the so-called DML, or data manipulation language operations, but it also includes create, alter, rename, drop which are DDL or data definition language operations. Its here where some might get confused. The operation to create a stored procedure, something written using PL/SQL, is actually SQL - you use SQL to create the database object that represents a block of PL/SQL.

Likewise, you can embed SQL code inside your PL/SQL. A FOR loop in PL/SQL can be based upon a SQL query, for example. Blows your mind a little, eh? You create a procedure using SQL that actually internally uses SQL to perform some action on records from the database.

Cool stuff if you ask me.

Answer 2

If it is wrapped in

• BEGIN ... END
• DECLARE ... END
• CREATE OR REPLACE ... END
• Is a one-liner prefixed EXECUTE

Then it is PL/SQL. What does this mean under the hood? SQL gets "compiled" to a query plan and executed, immediately returning a result set in the event of SELECT or the number of rows affected in other cases, or an error. PL/SQL however is more involved. Every object referenced in it is checked for existence and necessary grants, then the PL/SQL is compiled down to near-native code, which executes at full speed. The dependencies are tracked and if a table changes, then any PL/SQL referencing it requires recompilation. The reason for this is speed; since everything is done up front at compile time, there is no need to have any runtime checks in PL/SQL, whereas SQL statements must be checked every time (even tho' query plans are cached, the so-called soft parse, privileges must still be checked, and even a soft parse has a cost associated with it).

This is one of the "killer advantages" of stored procedures as Oracle does them; actually executing a PL/SQL stored proc or trigger does the absolute minimum amount of computation to get the result. Application code running outside of the Oracle kernel where it is untrusted has to jump through more hoops in order to get at the data. It's the same argument for strongly-typed, referentially transparent high-level languages such as OCaml or Haskell - do more work once, at compile time, and reap the benefits on the millions of time the code is executed.

Answer 3

Is there a demarcation between SQL and PL/SQL?

SQL is a standard*.

PL/SQL is a vendor extension to the SQL standard*.

*SQL is a language that has an explicit grammar and rules for how that grammar should be implemented, and is but is not a standard per-se. However, since there is a language specification, that everyone can agree on, then anything written in SQL would be portable if the program was only written in SQL.

But because PL/SQL is a vendor extension, it will have language parts that other vendors may not support.