I work with Oracle. I know that you can use 'case' in an SQL query; could you tell me if you can use other functions like 'if' ,'loop', 'for'... The thing is that I want to use this type of function in an SQL query, not in a PL/SQL procedure or function.
The main reason for wanting to use SQL instead of PL/SQL is to make the query portable, so that, if you ever have to move the query to some other database, you will have to change as little as possible. As a general rule, this is good advice.
When the SQL becomes complex, though, you have to reconsider. For example, if you need extra self-joins to accomplish in SQL what a bit of logic in a stored procedure can do easily, such as described in A "simple" crosstab problem, then that's a performance issue, and the procedure will often be the better choice.
While portability is a valid concern, never pay a performance penalty today for a potential portability savings at some undetermined future date.
In any case, avoiding procedural code has lost some of its importance now that Persistent Stored Modules are part of the SQL standard. PSMs (i.e. stored procedures) allow you to use structures like BEGIN/END blocks, LOOPs, conditionals like WHILE and IF/ELSE, and so on. There may be minor variations as implemented in languages like PL/SQL and Transact-SQL (which pre-date the standard and can hardly be expected to retroactively disallow the variations), but on the whole the similarities are greater than the differences.
In conclusion, if the query performs acceptably as pure SQL, that's great. But if there's a way to improve it using a stored procedure, go right ahead and do it that way. In fact, many installations make it mandatory to write a stored procedure for every query, if only because doing so bypasses the need for the compiler to bind (parse) the query every time it runs.
For More Information
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.