Function that returns Table vs Repeated Query

Question

I have let's say 3 procedures that uses basically the same base query. With this if I have an update to the query, I have to copy it the same time to the 2 other procedures to ensure it is synced:

ie:

PROCEDURE get_something(name IN VARCHAR2, result OUT VARCHAR2){

  SELECT * 
    FROM (SELECT * from multisource)
   WHERE col1 = name;

...

}

PROCEDURE get_something2(address IN VARCHAR2, result OUT VARCHAR2){

  SELECT * 
    FROM (SELECT * from multisource)
   WHERE col2 = address;

...

}

PROCEDURE get_something3(var IN VARCHAR2, result OUT VARCHAR2){

  SELECT * 
    FROM (SELECT * from multisource)
   WHERE col3 = var;

...

}

I am thinking of creating a function that returns a table of the base query, and just call it from the 3 procedures. So if I have and update, I will just update the query under the function.

ie:

FUNCTION get_base()
        RETURN table_t
        PIPELINED IS

        rec            record_r;

    BEGIN

        SELECT * from multisource...

       (maybe a pipeleined funcion or cursor here)

        RETURN;
   END get_base;

Then

PROCEDURE get_something(name IN VARCHAR2, result OUT VARCHAR2){

      SELECT * 
        FROM (SELECT * from table(get_base))
       WHERE col1 = name;

    ...

    }

PROCEDURE get_something2(address IN VARCHAR2, result OUT VARCHAR2){

      SELECT * 
        FROM (SELECT * from table(get_base))
       WHERE col2 = address;

    ...

    }

PROCEDURE get_something3(var IN VARCHAR2, result OUT VARCHAR2){

  SELECT * 
    FROM (SELECT * from table(get_base))
   WHERE col3 = var;

...

}

My question is, is this a good practice? Will I have performance issues using this? Are there alternative solutions?

Answer 1

Why not just tweak your logic and procedure to use just one select like this:

PROCEDURE get_something3(name IN VARCHAR2,
                         address IN VARCHAR2,
                         var IN VARCHAR2,
                         result OUT VARCHAR2){
  ....
  SELECT * 
    FROM multisource
   WHERE col1 = nvl(name,col1)
     and col2 = nvl(address,col2)
     and col3 = nvl(var,col3);
  ....

Then you call your procedure just passing the value you want to process and NULL in the other parameters.

Answer 2

Table functions are generally not a good way to promote the DRY (Don't Repeat Yourself) principle. A view would probably be a better approach.

Table functions are neat, and solve some interesting challenges. But they are a bit over-used and can cause a few problems:

1. They prevent optimizer transformations. Oracle is excellent at SQL optimization and can re-write a lot of SQL statements in many different ways. A table function makes most of those tricks impossible. Predicate pushing and view merging won't happen across a table function barrier. Features like parallelism are trivial in a pure SQL approach, but become difficult with table functions.
2. They are uncommon. This is not a strong reason to avoid something. But don't use something just because it's different. What's cool to you is just a bit frustrating to most other people. The simpler way is usually the best.
3. They are more buggy. In my experience table functions are buggier than regular SQL. This is probably because they are not common, and not tested and maintained as well as other features. You can expect slightly more ORA-600 errors and slightly more gotchas with table functions.

Views are the standard way to prevent repeating SQL.

Unfortunately, most view implementations suck. But there are a few simple reasons why views get so ugly. If you plan ahead, you can avoid these common problems:

1. Developers think SQL isn't a real programming language and don't bother to follow simple code guidelines. They don't bother to use good, readable names, they don't use comments, and they don't spend the time to make the code look good.
2. Views are part of some sacred data dictionary. Only a DBA or the "architect" is allowed to change them. The process involves some Rube Goldberg machine that produces a fancy PDF file, requiring multiple signatures. The file is printed out and posted on the wall but nobody uses it for meaningful work. All the overhead for the paperwork means that nobody ever refactors the views.