0

I have a Oracle SQL function, which returns a bunch of records through various sub-queries. However, there are countless filters that can be applied to these result records.

In an application programming language, I'd be able to abstract away the filtering behavior by allowing a function parameter to my function i.e. a callback in JS or Functional Interface object in Java.

Another possibility is having optional parameters or function overloading, but it still isn't scalable in regards to different filtering demands. Also, it won't work on stored procedures or functions.

I'm new to writing SQL procedures and functions, so I'm wondering is there any other option besides using an application programming language?

2

Callbacks are totally against the idea behind SQL. It's designed as declarative language which queries describe the task and query optimizer builds composition of sorts, index seeks, merges, etc to meet the query. No any row-by-row processing by callbacks.
Oracle supports overloading, with restrictions http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/subprograms.htm#sthref1738 What do you men it doesn't work? You must place overloaded subprogramms into packages, but it's very reasonable way of bulding Oracle-side soft in any case regardless to overloading. But I agree, it hardly can help with countless filter options.

Yes, application is the best place to build the SQL query depending on multiple user choises (filter by price and color, or by year and brand, ...).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.