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
IF part_exists(1) OR part_exists(100) THEN
IF NVL(part_exists(1) ,part_exists(100)) THEN
IF COALESCE(part_exists(1) ,part_exists(100)) THEN
IF part_exists(1) AND part_exists(100) THEN
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. :-)
ps - no, you can't cut & paste the code while a question is active