Once was a time when I read a few questions every morning at
AskTom. It only took a few minutes but the amount of hints, tips, tricks and general best practice guidelines I learned became so valuable as my experience grew.
I still find it a valuable resource, but these days as a seasoned developer, I found myself a solution with a different style of learning - keeps my mind active and I learn the odd thing occasionally, other times it helps affirm my current understanding of Oracle behaviour.
Steven Feuerstein and his friends have created a (business) daily
PL/SQL Challenge. You may have heard about it and wondered if you should give it a go - for most probably a variety of reasons.
Well, you don't even have to compete for the
frequent prizes - you can have a private profile; or you may see people providing a link to their
public profiles on their blog; or as a resume item.
The main thing is the occasional "test your knowledge" - typically a little snippet of code to have you thinking for a couple of minutes.
Steven has provided players an opportunity to submit their own quiz questions, so if you're curious of the sort of questions that get asked, here's mine from 28th February 2011 - it's a lesson on
Short Circuit Evaluation:
I create and populate a table as follows:
CREATE TABLE plch_parts
(
partnum INTEGER
, partname VARCHAR2 (100)
)
/
BEGIN
INSERT INTO plch_parts
VALUES (1, 'Mouse');
INSERT INTO plch_parts
VALUES (100, 'Keyboard');
COMMIT;
END;
/
Then I define the following function:
CREATE OR REPLACE FUNCTION part_exists (pn_partnum IN plch_parts.partnum%TYPE)
RETURN BOOLEAN IS
ln_partname plch_parts.partname%TYPE;
BEGIN
SELECT partname
INTO ln_partname
FROM plch_parts
WHERE partnum = pn_partnum;
DBMS_OUTPUT.PUT_LINE(ln_partname);
RETURN TRUE;
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN FALSE;
END part_exists;
/
Which of the choices, when used in place of the /*IF CONDITION*/ comment in the following block,
will result the subsequent output being displayed on the screen after the block is executed?
BEGIN
/*IF CONDITION*/
DBMS_OUTPUT.put_line('TRUE');
END IF;
END anon;
/
Mouse
Keyboard
TRUE
| A |
IF part_exists(1)
OR part_exists(100) THEN
|
| B |
IF NVL(part_exists(1)
,part_exists(100)) THEN
|
| C |
IF COALESCE(part_exists(1)
,part_exists(100)) THEN
|
| D |
IF part_exists(1)
AND part_exists(100) THEN
|
| E |
IF CASE WHEN part_exists(1)
OR part_exists(100) THEN TRUE END THEN
|
The rules are well written, the idea is well presented (unlike my hand written html table representation here), and the user feedback is fantastic. Steven also has an associated
blog for constructive discussions on the occasional contentious question, or question of general interest or quirkiness.
My question was well received by some friends, and Steven now provides users with simple, key survey results from each question - and I was happy with mine.
The answer is
B &
D - "NVL" & "AND"
I provided the following verification code to assist explanation of the answer:
BEGIN
-- As soon as the first expression returns true, PL/SQL no longer needs to evaluate the second. This is perfect for situations where you can put more efficient calculations first.
IF part_exists(1)
OR part_exists(100) THEN
DBMS_OUTPUT.put_line('TRUE');
END IF;
END anon;
/
BEGIN
-- Ideally this would behave as per COALESCE, but unfortunately NVL does not support short circuit evaluation.
IF NVL(part_exists(1)
,part_exists(100)) THEN
DBMS_OUTPUT.put_line('TRUE');
END IF;
END anon;
/
BEGIN
-- COALESCE is ANSI-defined shorthand for similarly written CASE functions, thereby supporting short circuit evaluation. COALESCE is generally preferred to NVL.
IF COALESCE(part_exists(1)
,part_exists(100)) THEN
DBMS_OUTPUT.put_line('TRUE');
END IF;
END anon;
/
BEGIN
-- Since this uses the AND operator, both expressions need to be evaluated to determine if the entire boolean equation is true. Oracle would use short-circuit evaluation if the situation permits, for instance, if the first expression returned false the second would not be evaluated.
IF part_exists(1)
AND part_exists(100) THEN
DBMS_OUTPUT.put_line('TRUE');
END IF;
END anon;
/
BEGIN
-- The Oracle database will use short circuit evaluation for CASE expressions.
IF CASE WHEN part_exists(1)
OR part_exists(100) THEN TRUE END THEN
DBMS_OUTPUT.put_line('TRUE');
END IF;
END anon;
/
There are currently over 1000 regular players, but only about two dozen regulars are registered as Australian - I've seen more than that at our
AUSOUG branch meetings! Come on Aussies - "represent!"
It's a great learning & affirming tool - I'll be on the lookout for inspiration for submitting more questions. :-)
ScottWe
ps - no, you can't cut & paste the code while a question is active
Recently I saw Chris asking the question, I was very curious to see who responded. Around the world responses differ when it comes to talk about people's beliefs. This week is about helping break down those barriers as some people even feel bullied for a variety of unfortunate reasons. Of course, at times around the world it goes both ways, so the week is also about understanding that it's not opposing sides of the fence, but a way of life unto it's own.
