What is the equivalent of functions and methods in SQL?

Question

In language like C or Java, there are things like functions/methods to modularize and abstract stuff.

What is there in SQL, specifically for Oracle?

I've seen people talking about using views to reduce the complexity in various blogs but there is only so much that can be done with a view. (For example, a bind variable cannot be passed as a runtime parameter to a view). In tools like Oracle Reports the query sometimes gets very awkward running several hundreds of lines.

I was wondering what sort of techniques people use to manage the complexity of their SQL? What are the equivalents of functions and methods in SQL to keep SQL from becoming extremely complex?

• Blrfl: What would you bind into a view that you couldn't accomplish with the constructs a SELECT from that view would make available?
• SoylentGray: @Blrfl - When you create a view from query it indexes that view so that you can query off of it more efficiently than reruning the query every time.
• Blrfl: @Chad: My point was that even if you were able to bind a parameter at query time, the database still has to do the same things as if you used the expression to SELECT something (e.g., SELECT a+bound_value, b+bound_value ... or in a WHERE clause (e.g., ... WHERE a = bound_value). Whether you're using plain, indexed or materialized views, the database can't index against an expression that can't be calculated when the index needs to be updated.
• SoylentGray: @blrfl but you can create the view with the aggregate results and joins already completed. If you have a query that takes 30 seconds or more to run try putting it into a view It may run quite abit faster.
• Blrfl: Having a materialized view doesn't change the fact that the database can't evaluate unknowns (i.e., things that would be bound) until the query which provides them actually happens. The OP asked about making bound variables part of views; I just asked what benefit binding a value into a view would provide that a SELECT from that view can't.

Answer 1

I have found common table expressions (cte's) helpful for both reducing the size and complexity as well as making the query more readable. Temporary results (temp tables and so forth) can be a mixed blessing, as can user defined functions. They can certainly make the code more readable, but may have a performance impact (not that the impact is always negative, but it is something to watch out for).

• toddlermenot: Thanks. FWIW, I found that CTEs are under a different name in Oracle viz., WITH clause.

Answer 2

PL-SQL supports user defined functions. You can use this to re-use some common logic with queries. However, there are significant performance penalties for doing this, as UDFs hide indexes from the query. Most of the time, you're better off writing out your query logic again.

Another option is to use a computed column. This let's you do some (not all) of the same things as a scalar user defined function, but is more index-friendly.

• toddlermenot: Thanks for the tip about computed column. Haven't heard that one before.