Difference between sql, pl/sql and sqlj

Question

I want to know

• how do the 3 compare with one another?
• which one to use when?
• which can be used as a replacement of other?

Answer 1

• SQL -- generic name for the language which is used to query relational databases. This is governed by various ANSI standards although I know of no actual RDBS which implements completely the latest standard. Several implement most of ANSI SQL - 92 which can be regarded as the lowest common denominator for SQL implementations.
• PL/SQL -- An Oracle specific extension to SQL which is used to write "STORED PROCEDURES" and "TRIGGERS" its effectivly a scripting languages which can use Oracles SQL natively. In SQLServer TSQL provides similar functionality.
• SQLJ -- Part of the Java standard, a (seldom used) feature which allows SQL to be coded inline with the Java code and for SQL syntax to be checked at compile time. Compare with JDBC which is a standard Java API which allows a Java program to invoke SQL at runtime.

So SQL allows basic querying and updateing of tables as in "SELEC * FROM MY.TABNAME", PL/SQL allows more intelligent scripts to be run against the database as something like " IF DAY = 01 THEN ( INSERT (TOTALS) VALUES(%VAL) INTO TOTTAB ) ELSE (UPDATE TOTTAB SET TOTALS = TOTALS + %VAL")

Answer 2

Regarding to your question "which one to use when". Some Oracle gurus recommend:

1. First try to solve your problem with plain SQL
2. If you can't do it with sql, then try PL/SQL
3. Only use other languages like Java externally of your database if you can't do it internally.

But of course you can treat your database as a dump storage of data and do all the business logic externally in a separate layer.

Answer 3

If the need is truly an SQL need, then I try to implement it in SQL first. There might be some specific circumstances where a PL/SQL procedural call with, perhaps a judicious use of bulk binds and bulk fetches, might be faster/cause fewer resource contentions/etc than a pure SQL solution, but that's rare in my experience.

I try to draw a distinction between "database logic" and "application logic" and while I admit it can be fuzzy sometimes, I try very hard to avoid writing application logic in PL/SQL. The reason is simply that it's far less expressive than Java and has much weaker APIs for pretty much everything except SQL interaction.

I personally don't use SQLJ, so I can't help you there, sorry.