August 2018 update: please do feel encourage to read this and my other posts on table functions, but you will learn much more about table functions by taking my Get Started with PL/SQL Table Functions class at the Oracle Dev Gym. Videos, tutorials and quizzes - then print a certificate when you are done!
Waaaay back in April 2010, I decided (without really consciously deciding) that I wasn't busy enough. What I needed was the responsibility of support a 24x7 website that offered quizzes on SQL, PL/SQL and more. Well, actually, I decided that a while before April 2010, but April 2010 was when we launched the PL/SQL Challenge.
Over 900,000 answers to thousands of quizzes later, I thank my lucky stars that my original co-founder of the site, Finn Ellebaek Nielsen, suggested that perhaps my daily PL/SQL quiz should take a break on Saturday and Sunday (I was pushing for 7 quizzes a week, because I can be well, quite, insane).
In any case, we have had great fun building the site on Oracle Application Express, packed full of PL/SQL APIs. And hundreds of developers have told us how much they have enjoyed and benefited from what I call "active learning" - not just reading a book or watching a video, but challenging oneself to test one's knowledge (and, if you like that sort of thing, competing with others).
Yes, competing. Humans love to compete (some of them, mostly I suppose men). They love to be ranked. They love to win. And we give them that opportunity on the PL/SQL Challenge.
We offer lots of different kinds of rankings: by player, by country, by company, by affiliation (e.g., user group). You can choose the competition, the date range, etc. Because of this wide variety, we decided to utilize APEX's fantastic Interactive Reports feature.
One downside of Interactive Reports, however, is that they do not support a dynamic (constructed at runtime) query. Ranking reports run off of a series of materialized views, such as mv_qdb_rankings_q (quarterly rankings), so in our first pass we ended up with lots of different reports built on views that were built on top of the materialized views:
The report query itself was simple enough:
Still, you had to create a report, format the columns, etc. It was tedious work but somebody had to do it. Oh, not me. No, no. My son, Eli, who was the main APEX developer on the project. :-)
So "we" got the reports done, users used them, users were happy. But then....then it was time to enhance the reports. And then we both stared at those over-two-dozen reports and thought about having to go through each one of them apply the same changes to each one of them....
And suddenly we (with Eli definitely taking the lead here) were sure (desperate) that there had to be a better way, and we'd better figure out what that better way was.
Ideally, we would have just one report that would handle all of the variations we needed, in a way that the users would find easy to use.
Ideally, we would have a single view and just pass "parameters" to it, to alter its behavior and change the result set returned.
Sadly, views do not take parameters. But, wait....functions take parameters. If only we could call a function in the query.....well, we can! We can call a table function!
[Note to those who have never seen me present: those exclamation marks are real. I actually do get all excited about writing cool code in PL/SQL, even after multiple decades of doing so.]
Now, some of you might be thinking: why would you need a table function? Why not just use a series of "mutually exclusive" queries combined with a UNION ALL?
That is certainly a possibility, and in fact we did this for our player (as opposed to company, organizational , etc.) rankings, because the code required to construct the dataset via the table function turned out to be not all that complicated. The mutually exclusive UNION ALL query looks (in part) like this (all the MV-specific elements in red):
SELECT yyyy,
qdb_user_mgr.published_name_no_points (u.user_id),
qdb_user_mgr.published_name (u.user_id),
u.country,
u.company_name,
qdb_utilities.expertise_level (expertise_level_id),
overall_rank,
previous_rank,
rank_improvement,
overall_percentile,
ROUND (pct_correct_answers, 1),
user_score,
user_wscore,
qdb_utilities.seconds_to_duration (user_seconds, 0),
best_possible_score,
best_possible_wscore,
total_quizzes,
ROUND (total_quizzes / potential_quizzes * 100)
FROM mv_qdb_rankings_y mv, qdb_users u
WHERE mv.user_id = u.user_id
AND mv.competition_id = :p442_competition_id
AND yyyy = :p442_period
AND :p442_period_type = 'Y'
UNION ALL
SELECT yyyy_q,
qdb_user_mgr.published_name_no_points (u.user_id),
qdb_user_mgr.published_name (u.user_id),
u.country,
u.company_name,
qdb_utilities.expertise_level (expertise_level_id),
overall_rank,
previous_rank,
rank_improvement,
overall_percentile,
ROUND (pct_correct_answers, 1),
user_score,
user_wscore,
qdb_utilities.seconds_to_duration (user_seconds, 0),
best_possible_score,
best_possible_wscore,
total_quizzes,
ROUND (total_quizzes / potential_quizzes * 100)
FROM mv_qdb_rankings_q mv, qdb_users u
WHERE mv.user_id = u.user_id
AND mv.competition_id = :p442_competition_id
AND yyyy_q = :p442_period
AND :p442_period_type = 'Q'
I expect you get the idea from there. It's a long query (well, 143 lines, not all that crazy long), but it's fairly straightforward. No need for a table function.
When it came to the other reports, however, the table function came in very, very handy - because the logic and variations between the reports was more substantial.
In this post, I will explore how we applied table functions in our application, and go as far as the specification of the table function. In my next post, I will dive into the implementation of that function.
Before looking at specification of the function, allow me to share with you the single report query that replaced sixteen interactive reports:
Hopefully you can see why we talk about queries based on table functions as a kind of parameterized view. I am passing argument values to the parameters of the function. The function then returns the dataset as rows and columns. So from the standpoint of a developer building the report, we have kept (made) things very simple. All the complexity has been moved inside the function.
So let's look at the function. First, as explained in the 2nd post in this series, if I want to pass back more than a single scalar value from my table function, I need to create an object type and a nested table type based on that object type. Here goes:
CREATE OR REPLACE TYPE ir_other_ranking_ot IS OBJECT
(
period VARCHAR2 (500),
type_name VARCHAR2 (300),
num_of_players INTEGER,
top20 INTEGER,
top100 INTEGER,
score INTEGER,
wscore INTEGER,
pct_correct_answers NUMBER,
answer_time VARCHAR2 (500),
best_possible_score INTEGER,
best_possible_wscore INTEGER,
total_quizzes INTEGER,
perc_taken NUMBER,
score_per_quiz INTEGER,
overall_rank_wscore INTEGER,
overall_rank INTEGER
);
/
CREATE OR REPLACE TYPE ir_other_ranking_nt
IS TABLE OF ir_other_ranking_ot
/
Then I define a function that accepts the necessary parameters and returns a collection of that type.
CREATE OR REPLACE PACKAGE BODY qdb_rankings
IS
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;
END;
/
So the public-facing side of my function is done. Time to dive inside! Unfortunately, I lead a busy, busy life at Oracle and right now I am on deadline to complete my next article for Oracle Magazine. Consequently, please accept my apologies for publishing this post without getting into the "good stuff." I promise that it is coming right along!
Waaaay back in April 2010, I decided (without really consciously deciding) that I wasn't busy enough. What I needed was the responsibility of support a 24x7 website that offered quizzes on SQL, PL/SQL and more. Well, actually, I decided that a while before April 2010, but April 2010 was when we launched the PL/SQL Challenge.
Over 900,000 answers to thousands of quizzes later, I thank my lucky stars that my original co-founder of the site, Finn Ellebaek Nielsen, suggested that perhaps my daily PL/SQL quiz should take a break on Saturday and Sunday (I was pushing for 7 quizzes a week, because I can be well, quite, insane).
In any case, we have had great fun building the site on Oracle Application Express, packed full of PL/SQL APIs. And hundreds of developers have told us how much they have enjoyed and benefited from what I call "active learning" - not just reading a book or watching a video, but challenging oneself to test one's knowledge (and, if you like that sort of thing, competing with others).
Yes, competing. Humans love to compete (some of them, mostly I suppose men). They love to be ranked. They love to win. And we give them that opportunity on the PL/SQL Challenge.
We offer lots of different kinds of rankings: by player, by country, by company, by affiliation (e.g., user group). You can choose the competition, the date range, etc. Because of this wide variety, we decided to utilize APEX's fantastic Interactive Reports feature.
One downside of Interactive Reports, however, is that they do not support a dynamic (constructed at runtime) query. Ranking reports run off of a series of materialized views, such as mv_qdb_rankings_q (quarterly rankings), so in our first pass we ended up with lots of different reports built on views that were built on top of the materialized views:
The report query itself was simple enough:
Still, you had to create a report, format the columns, etc. It was tedious work but somebody had to do it. Oh, not me. No, no. My son, Eli, who was the main APEX developer on the project. :-)
So "we" got the reports done, users used them, users were happy. But then....then it was time to enhance the reports. And then we both stared at those over-two-dozen reports and thought about having to go through each one of them apply the same changes to each one of them....
And suddenly we (with Eli definitely taking the lead here) were sure (desperate) that there had to be a better way, and we'd better figure out what that better way was.
Ideally, we would have just one report that would handle all of the variations we needed, in a way that the users would find easy to use.
Ideally, we would have a single view and just pass "parameters" to it, to alter its behavior and change the result set returned.
Sadly, views do not take parameters. But, wait....functions take parameters. If only we could call a function in the query.....well, we can! We can call a table function!
[Note to those who have never seen me present: those exclamation marks are real. I actually do get all excited about writing cool code in PL/SQL, even after multiple decades of doing so.]
Now, some of you might be thinking: why would you need a table function? Why not just use a series of "mutually exclusive" queries combined with a UNION ALL?
That is certainly a possibility, and in fact we did this for our player (as opposed to company, organizational , etc.) rankings, because the code required to construct the dataset via the table function turned out to be not all that complicated. The mutually exclusive UNION ALL query looks (in part) like this (all the MV-specific elements in red):
SELECT yyyy,
qdb_user_mgr.published_name_no_points (u.user_id),
qdb_user_mgr.published_name (u.user_id),
u.country,
u.company_name,
qdb_utilities.expertise_level (expertise_level_id),
overall_rank,
previous_rank,
rank_improvement,
overall_percentile,
ROUND (pct_correct_answers, 1),
user_score,
user_wscore,
qdb_utilities.seconds_to_duration (user_seconds, 0),
best_possible_score,
best_possible_wscore,
total_quizzes,
ROUND (total_quizzes / potential_quizzes * 100)
FROM mv_qdb_rankings_y mv, qdb_users u
WHERE mv.user_id = u.user_id
AND mv.competition_id = :p442_competition_id
AND yyyy = :p442_period
AND :p442_period_type = 'Y'
UNION ALL
SELECT yyyy_q,
qdb_user_mgr.published_name_no_points (u.user_id),
qdb_user_mgr.published_name (u.user_id),
u.country,
u.company_name,
qdb_utilities.expertise_level (expertise_level_id),
overall_rank,
previous_rank,
rank_improvement,
overall_percentile,
ROUND (pct_correct_answers, 1),
user_score,
user_wscore,
qdb_utilities.seconds_to_duration (user_seconds, 0),
best_possible_score,
best_possible_wscore,
total_quizzes,
ROUND (total_quizzes / potential_quizzes * 100)
FROM mv_qdb_rankings_q mv, qdb_users u
WHERE mv.user_id = u.user_id
AND mv.competition_id = :p442_competition_id
AND yyyy_q = :p442_period
AND :p442_period_type = 'Q'
I expect you get the idea from there. It's a long query (well, 143 lines, not all that crazy long), but it's fairly straightforward. No need for a table function.
When it came to the other reports, however, the table function came in very, very handy - because the logic and variations between the reports was more substantial.
In this post, I will explore how we applied table functions in our application, and go as far as the specification of the table function. In my next post, I will dive into the implementation of that function.
Before looking at specification of the function, allow me to share with you the single report query that replaced sixteen interactive reports:
Hopefully you can see why we talk about queries based on table functions as a kind of parameterized view. I am passing argument values to the parameters of the function. The function then returns the dataset as rows and columns. So from the standpoint of a developer building the report, we have kept (made) things very simple. All the complexity has been moved inside the function.
So let's look at the function. First, as explained in the 2nd post in this series, if I want to pass back more than a single scalar value from my table function, I need to create an object type and a nested table type based on that object type. Here goes:
CREATE OR REPLACE TYPE ir_other_ranking_ot IS OBJECT
(
period VARCHAR2 (500),
type_name VARCHAR2 (300),
num_of_players INTEGER,
top20 INTEGER,
top100 INTEGER,
score INTEGER,
wscore INTEGER,
pct_correct_answers NUMBER,
answer_time VARCHAR2 (500),
best_possible_score INTEGER,
best_possible_wscore INTEGER,
total_quizzes INTEGER,
perc_taken NUMBER,
score_per_quiz INTEGER,
overall_rank_wscore INTEGER,
overall_rank INTEGER
);
/
CREATE OR REPLACE TYPE ir_other_ranking_nt
IS TABLE OF ir_other_ranking_ot
/
Then I define a function that accepts the necessary parameters and returns a collection of that type.
CREATE OR REPLACE PACKAGE BODY qdb_rankings
IS
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;
END;
/
So the public-facing side of my function is done. Time to dive inside! Unfortunately, I lead a busy, busy life at Oracle and right now I am on deadline to complete my next article for Oracle Magazine. Consequently, please accept my apologies for publishing this post without getting into the "good stuff." I promise that it is coming right along!
Links to Table Function Series
Comments
Post a Comment