SQL & PL/SQL

Hi all,

Good day....

Note : This thread is just for fun and meant for learning recursive techniques

Please ignore this if you are busy

-------------------------

https://en.wikipedia.org/wiki/Happy_number

For example, 19 is happy, as the associated sequence is:

1² + 9² = 82

8² + 2² = 68

6² + 8² = 100

1² + 0² + 0² = 1.

Just wanted to know if someone can re-write this below pl/sql snippet in plain SQL (using recursive with clause may be).. Saying so this pl/sql is not that bad either.. w.r.t performance.

SET SERVEROUTPUT ON
DECLARE
   l_res       NUMBER;
   l_var_new   NUMBER;
BEGIN
   FOR rec IN 0 .. 1000
   LOOP
      l_var_new := rec;
      WHILE (TRUE)
      LOOP
         EXECUTE IMMEDIATE
               'select '
            || RTRIM (REGEXP_REPLACE (l_var_new, '(.)', 'power(\1,2)+'), '+')
            || ' from dual'
            INTO l_res;
         IF ( (LENGTH (l_res) = 1 AND l_res <> 1) OR l_res = 1)
         THEN
            IF (l_res = 1)
            THEN
               --logmein.in ('NUMBER -> ' || rec);   ---- logging method
               DBMS_OUTPUT.put_line (rec);  ---just for our comfort.. (not for logging ;-))
            END IF;
            EXIT;
         END IF;
         l_var_new := l_res;
      END LOOP;
   END LOOP;
END;

Cheers,

Manik.