Problem solve Get help with specific problems with your technologies, process and projects.

Oracle SQL functions, not PL/SQL

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

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.