to SQL or to PLSQL
Iggy Fernandez posted a riddle to be solved without PLSQL
http://www.amazon.com/gp/blog/post/PLNKI2MYB0YCYAUL
I tend to second Steven Feuerstein argument :
Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens.
In my book I mentionned techniques that enhance the basis functionality of SQL. Actually, many of those techniques allow you to write “shorter” statements than before (by avoid redundant subqueries) and also to gain performance, occasionnaly.
However the downside is that you will end up writting some code that only you can read.
In my opinion a zero-plsql approach is wrong. If you have a problem similar to Iggy Fernandez, then no no no, do not even try to solve it with SQL unless you are really doing this for fun
In my humble opinion, plsql and sql and complementary. If you want to program, use plsql. If you need to retrieve data, use sql.
And thanks a lot to Iggy for the excellent challenge !!!
Thanks for trying the puzzle, Laurent. Here is the complete quote from the interview with Steven Feuerstein that was published in the NoCOUG Journal.
“SQL is a set-oriented non-procedural language; i.e., it works on sets and does not specify access paths. PL/SQL on the other hand is a record-oriented procedural language, as is very clear from the name. What is the place of a record-oriented procedural language in the relational world?”
“Its place is proven: SQL is not a complete language. Some people can perform seeming miracles with straight SQL, but the statements can end up looking like pretzels created by someone who is experimenting with hallucinogens. We need more than SQL to build our applications, whether it is the implementation of business rules or application logic. PL/SQL remains the fastest and easiest way to access and manipulate data in an Oracle RDBMS, and I am certain it is going to stay that way for decades.”
P.S. NoCOUG is going to use the puzzle in an SQL competition with prizes to be provided by Apress. The competition will be open to SQL Server and DB2 users in addition to Oracle
You mean, I should now come up with a pure ANSI solution
It seems like using straight SQL vs PL/SQL is a mater of preferences or even programmers skill. Are there cases when straight SQL is a business necessity?
Thanks
AA
It is two things. PL is Programing Language. Like Java, C or perl.
Whenever you access the data, you use SQL. Even if you write PL/SQL, you have SQL in it.
Business necessity is performance. If you write tons of cursors instead of a single SQL query, you are likely to not achieve your performance objectives.
Thank you for the response.
Are there any public databases where you can pay for read-only access to run your own queries to retrieve data you are interested in?
Thanks
http://apex.oracle.com is a free service where you can have 10Mb schema and try all your queries. Not for production usage.