First thing that good developer should do is to write “Hello World” in his programming language.
I will try to make it more complex. We will use some “standard” programming question:Using fizzbuzz to find developers who grok coding
And change it to “Hello World” problem:
Write a program that prints the numbers from 1 to 100. But for multiples of three print “Hello” instead of the number and for the multiples of five print “World”. For numbers which are multiples of both three and five print “HelloWorld”.
In PL/SQL we can solve this problem in two ways:
- Crazy way, we will not do it: wrote some procedure that will use DBMS_OUTPUT package to show to lines
- Silky smooth simple way: make a query
SELECT CASE WHEN MOD(LEVEL, 15) = 0 THEN 'HelloWorld' WHEN MOD(LEVEL, 3) = 0 THEN 'Hello' WHEN MOD(LEVEL, 5) = 0 THEN 'World' ELSE to_char(LEVEL) END FROM dual CONNECT BY LEVEL <= 100;
As you can see in this query we use only one PL/SQL function
CONNECT BY LEVEL<=100
that generate series of records and that’s all… no magic, no unnecessary code, no headache.