Table Functions, Part 3b: implementing table functions for PL/SQL Challenge reports

So the public-facing side of my function is done. Time to dive inside! Of course, as with almost any piece of code, most of the details are very application-specific and not all that critical to the lesson at hand. So I will take liberties will code to focus in on the most relevant aspects, but I will also use this opportunity to suggest some ideas about how best to build such functions to maximize readability and make it easy to maintain.

First, the declaration section; what you see here are the usual constants (mostly, to execute "setup" functionality once and not repeat it) and variables. But then you see a number of nested subprograms. In case you were not aware, you can define functions and procedures within other functions and procedures.

For a table function that constructs a complex dynamic query, it was crucial for me to hide various construction steps behind a function interface to make it easier to see how the pieces fit together.

   FUNCTION ir_other_ranking_tf (
      category_in         IN VARCHAR2,
      period_type_in      IN VARCHAR2,
      competition_id_in   IN INTEGER,
      period_in           IN VARCHAR2)
      RETURN ir_other_ranking_nt
   IS
      c_competition    CONSTANT qdb_competitions%ROWTYPE
         := qdb_competition_mgr.one_competition (
               competition_id_in) ;

      c_freq_column    CONSTANT VARCHAR2 (100)
         := CASE period_type_in
               WHEN qdb_config.c_daily THEN 'YYYY_DDD'
               WHEN qdb_config.c_weekly THEN 'YYYY_IW'
               WHEN qdb_config.c_monthly THEN 'YYYY_MM'
               WHEN qdb_config.c_quarterly THEN 'YYYY_Q'
               WHEN qdb_config.c_yearly THEN 'YYYY'
            END ;
      c_select_list1   CONSTANT qdb_config.maxvarchar2
         := ',
            "Player Count" ,
            NVL (top20.top20, 0) ,
            NVL (top100.top100, 0) ,
            "Score" ,
            "Weighted Score" ,
            "Pct Correct" ,
             qdb_utilities.seconds_to_duration (answer_time, 0),
            "Max Score" ,
            "Max Weighted Score" ,
            "Quizzes Taken" ,
            "% Taken" ,
            "Score per Quiz" ,
            RANK () OVER (
              ORDER BY "Weighted Score" DESC, answer_time ASC) ,
            RANK () OVER (
              ORDER BY "Score per Quiz" DESC, answer_time ASC))' ;

      c_select_list2   CONSTANT qdb_config.maxvarchar2
         := ',
          COUNT (*) "Player Count" ,
          SUM (user_score) "Score" ,
          SUM (user_wscore) "Weighted Score" ,
          ROUND (SUM (pct_correct_answers) / 
             COUNT (*), 1) "Pct Correct" ,
          SUM (user_seconds) answer_time ,
          SUM (best_possible_score) "Max Score" ,
          SUM (best_possible_wscore) "Max Weighted Score" ,
          SUM (total_quizzes) "Quizzes Taken" ,
          ROUND (SUM (total_quizzes) / 
             SUM (potential_quizzes) * 100) "% Taken" ,
          ROUND (SUM (user_wscore)   / 
             SUM (total_quizzes)) "Score per Quiz"' ;

      l_query                   qdb_config.maxvarchar2;
      l_return                  ir_other_ranking_nt;

      PROCEDURE trc (context_in   IN VARCHAR2,
                     text_in      IN VARCHAR2)
      IS
      BEGIN
         qdb_utilities.trace_activity_force (
            'ir_other_ranking_tf ' || context_in,
            text_in);
      END;

      FUNCTION period_qualifier (if_not_lifetime_in IN VARCHAR2)
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN CASE period_type_in
                   WHEN qdb_config.c_lifetime THEN NULL
                   ELSE if_not_lifetime_in
                END;
      END;

      FUNCTION period_qualifier_col (
         add_comma_in   IN BOOLEAN DEFAULT TRUE)
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN period_qualifier (
                      'mv.'
                   || c_freq_column
                   || CASE
                         WHEN add_comma_in THEN ','
                         ELSE NULL
                      END);
      END;

      FUNCTION detail_tables
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN CASE category_in
           WHEN 'organization_name'
           THEN
              ' qdb_memberships mb, qdb_organizations cat '
           ELSE
              ' qdb_users cat'
           END;
      END;

      FUNCTION detail_where
         RETURN VARCHAR2
      IS
      BEGIN
         RETURN    CASE period_type_in
                      WHEN qdb_config.c_lifetime
                      THEN
                         NULL
                      ELSE
                            period_qualifier_col (
                               add_comma_in   => FALSE)
                         || ' = '''
                         || period_in
                         || ''' AND '
                   END
                || CASE category_in
                      WHEN 'organization_name'
                      THEN
                         ' mv.user_id = mb.user_id AND
                             mb.organization_id = 
                             cat.organization_id '
                      ELSE
                         ' mv.user_id = cat.user_id '
                   END;

      END;

And now it's time to construct the query. Note that I include tracing both before and after query construction. I imagine it will be pretty obvious why: executing a dynamically-constructed statement isn't hard (I show that next). Putting together something as complicated as the query needed for this report is, on the other hand, a real treat; what's the change I will get it correct the first thirty or so iterations? Being able to see the string I put together is a big help.

I don't think it makes a whole lot of sense to go through the query construction in detail. The bottom line is that I was dealing with different materialized views (so: different name of view), and to some extent different names for columns in each view. I used the constants and nested functions (shown above) to avoid repetition and improve readability (perhaps just a little bit, anyway).


   BEGIN
      IF qdb_utilities.trace_is_on
      THEN
         trc (
            ' period_type, competition, c_freq_column, period',
               period_type_in
            || '-'
            || competition_id_in
            || '-'
            || c_freq_column
            || '-'
            || period_in);
      END IF;

      l_query :=
            'SELECT ir_other_ranking_ot('
         || CASE period_type_in
               WHEN qdb_config.c_lifetime THEN '''Lifetime'''
               ELSE 'mv.' || c_freq_column
            END
         || ', mv.'
         || category_in
         || c_select_list1
         || ' FROM (SELECT '
         || CASE period_type_in
               WHEN qdb_config.c_lifetime THEN NULL
               ELSE 'mv.' || c_freq_column || ','
            END
         || 'cat.'
         || category_in
         || c_select_list2
         || ' FROM mv_qdb_rankings_'
         || period_type_in
         || ' mv,'
         || detail_tables ()
         || ' WHERE competition_id = :competition_id_in AND '
         || CASE category_in
               WHEN 'company_name'
               THEN
                  ' cat.company_name IS NOT NULL AND '
               ELSE
                  NULL
            END
         || detail_where ()
         || ' GROUP BY '
         || period_qualifier_col ()
         || 'cat.'
         || category_in
         || ') mv , (SELECT '
         || period_qualifier_col ()
         || category_in
         || ',COUNT (*) top100 FROM mv_qdb_rankings_'
         || period_type_in
         || ' mv,'
         || detail_tables ()
         || ' WHERE overall_rank <= 100 AND '
         || detail_where ()
         || ' AND competition_id  = :competition_id_in GROUP BY '
         || period_qualifier_col ()
         || category_in
         || ') top100 , (SELECT '
         || period_qualifier_col ()
         || category_in
         || ', COUNT (*) top20 FROM mv_qdb_rankings_'
         || period_type_in
         || ' mv, '
         || detail_tables ()
         || '  WHERE overall_rank <= 20 AND '
         || detail_where ()
         || ' AND competition_id  = :competition_id_in GROUP BY '
         || period_qualifier_col ()
         || category_in
         || ') top20 WHERE top100.'
         || category_in
         || '(+) = mv.'
         || category_in
         || ' AND top20.'
         || category_in
         || '(+) = mv.'
         || category_in
         || period_qualifier (
                  ' AND top100.'
               || c_freq_column
               || '(+) = mv.'
               || c_freq_column
               || ' AND top20.'
               || c_freq_column
               || '(+) = mv.'
               || c_freq_column);

      IF qdb_utilities.trace_is_on
      THEN
         trc (' Other Ranking query', l_query);
      END IF;

OK, and now it's time to execute the query, collect the result set with a BULK COLLECT clause, and then send it out to the invoking SELECT.

And, of course, if something goes wrong, I send the value of l_query to the error log, so I can eyeball it and - hopefully - quickly identify the dumb mistake I made (left out a comma, didn't leave a space between keywords in the query, etc.)

      EXECUTE IMMEDIATE l_query
         BULK COLLECT INTO l_return
         USING competition_id_in,
               competition_id_in,
               competition_id_in;

      RETURN l_return;
   EXCEPTION
      WHEN OTHERS
      THEN
         qdb_utilities.syslog_error (
            'ir_other_ranking_tf',
            'Failure executing dynamic query',
            l_query); 
         RAISE;
   END;

So now you believe me, right? The implementation of a table function is....application-dependent. You are unlikely to build something exactly like this, but hopefully the approach I took (declaring lots of constants and nested subprograms to isolate repeated logic and improve readability) will offer you ideas on optimizing your own table function implementation.

Now I must finish up my four hour workshop for the GLOC conference next week.

And write another quiz for the PL/SQL Challenge.

And start writing the first draft of my Project Solver Call to Action.

:-)

Links to Table Function Series

Comments

  1. Steven, came across this post and noticed the seemingly duplicate declaration of c_select_list1. Whilst the declaration can be 'overloaded' as soon as you reference this it should throw a PLS-00371.

    I am probably lacking caffeine and fail to spot the obvious as I am just reading the code rather than compiling it....

    ReplyDelete
  2. Thanks, Chris, that is definitely a typo. Not sure how it crept in. Will fix now!

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts