Skip to content

Instantly share code, notes, and snippets.

@gvenzl
Last active December 22, 2021 00:42
Show Gist options
  • Save gvenzl/78899841466bd81cfdea2911492ec11f to your computer and use it in GitHub Desktop.
Save gvenzl/78899841466bd81cfdea2911492ec11f to your computer and use it in GitHub Desktop.

Print a nice little Christmas tree with recursive SQL and some UNION ALL:

set heading off;
set feedback off;

WITH tree(lev, xmas) AS (
  SELECT 1 lev, RPAD(' ', 10, ' ')
   || '*' xmas
    FROM   dual
  UNION ALL
  SELECT tree.lev+1 lev,
    RPAD(' ',10-tree.lev,' ')
     || RPAD('^',tree.lev+1,'^')
     || LPAD('^',tree.lev,'^') xmas
      FROM tree
        WHERE tree.lev < 10
)
SELECT '   Merry Christmas!'
  FROM dual
UNION ALL
SELECT xmas
  FROM tree
UNION ALL
SELECT '         | |'
  FROM dual
UNION ALL
SELECT '     ~~~/   \~~~'
  FROM dual;

Output:

SQL> WITH tree(lev, xmas) AS (
  2    SELECT 1 lev, RPAD(' ', 10, ' ')
  3     || '*' xmas
  4      FROM   dual
  5    UNION ALL
  6    SELECT tree.lev+1 lev,
  7      RPAD(' ',10-tree.lev,' ')
  8       || RPAD('^',tree.lev+1,'^')
  9       || LPAD('^',tree.lev,'^') xmas
 10        FROM tree
 11          WHERE tree.lev < 10
 12  )
 13  SELECT '   Merry Christmas!'
 14    FROM dual
 15  UNION ALL
 16  SELECT xmas
 17    FROM tree
 18  UNION ALL
 19  SELECT '         | |'
 20    FROM dual
 21  UNION ALL
 22  SELECT '     ~~~/   \~~~'
 23*   FROM dual;

   Merry Christmas!
          *
         ^^^
        ^^^^^
       ^^^^^^^
      ^^^^^^^^^
     ^^^^^^^^^^^
    ^^^^^^^^^^^^^
   ^^^^^^^^^^^^^^^
  ^^^^^^^^^^^^^^^^^
 ^^^^^^^^^^^^^^^^^^^
         | |
     ~~~/   \~~~
SQL>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment