Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

share|improve this question
add comment

3 Answers 3

up vote 9 down vote accepted

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.

share|improve this answer
2  
Indeed, the line is blurred. You could write an SQL statement to create a PL/SQL procedure that has an SQL statement in it that calls a PL/SQL function that has an SQL statement in it, etc. –  Leigh Riffel Feb 7 '11 at 20:51
1  
@Leigh down the rabbit hole we go! –  ScottCher Feb 7 '11 at 21:58
add comment

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.

share|improve this answer
1  
To add to the mix CALL is an SQL statement used to execute a stored program unit, and is re-written under-the-hood as a BEGIN...END –  Gary Feb 7 '11 at 22:08
    
As is EXECUTE, it's syntactic sugar –  Gaius Feb 9 '11 at 5:03
add comment

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.

share|improve this answer
1  
I would say that PL/SQL is a de facto standard, whereas SQL is a de jure standard :-) –  Gaius Feb 8 '11 at 8:02
2  
@Gaius and from that statement alone I would guess two things: You're an ORA dev, and you think TSQL is just messy :p –  jcolebrand Feb 8 '11 at 14:40
    
@jcolebrand My first DBA job was actually on Sybase, but you're right, I mainly work with Oracle these days. I'd be pretty happy if you could write either T-SQL or PL/SQL on either database, but the languages reflect their platforms (e.g. T-SQL is the way it is because cursors and locks are expensive in SQL Server and its descendents, and PL/SQL is the way it is because someone at Oracle last worked at the DoD) :-) –  Gaius Feb 9 '11 at 5:02
    
@Gaius Ah, yes, the old argument about standards being so good because there are so many to choose from. I think its good to indicate SQL is a "standard" and that PL/SQL is proprietary so +1 @jcolebrand. –  ScottCher Feb 17 '11 at 15:57
    
@ScottCher ~ Thanks for that. I feel dumb saying that it's a standard because there's not (afaik) a standards body to stamp it with a seal of approval and give it a number. But it is well defined and agreed upon. ;) –  jcolebrand Feb 17 '11 at 16:00
show 3 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.