How to differentiate between SQL and PL/SQL?

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

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.

StackExchange Database Administrators Q#1121, answer score: 13