OraQA

The issue is very often related to context switching. A context switch is when you write plsql code that contains sql statement, or sql that contains plsql functions.

select emp,dbms_random.value from emp;

implies a context switch

begin update emp set sal=0; end;

implies a context switch too.

However, it is sometimes better practice to have a few context switches rather than a way too much complex SQL query, that nobody will ever be able to maintain but you.

Ok, let’s try to print the equation abc+def=ghi where abcdefghi are digits from 1 to 9 with no duplicate.

SQL> create or replace function f return sys.odcivarchar2list pipelined deterministic is
  2    a7 number;
  3    a8 number;
  4    a9 number;
  5  begin
  6    -- How to solve the 3 Digits Plus 3 Digits Puzzle
  7    -- where a1a2a3 + a4a5a6 = a7a8a9 and each a is an unique digit from 1 to 9
  8    for a1 in 1..9 loop
  9      for a2 in 1..9 loop
 10        if a2 not in (a1) then
 11          for a3 in 1..9 loop
 12            if a3 not in (a1,a2)
 13            then
 14              for a4 in 1..9 loop
 15                if a4 not in (a1,a2,a3) and a1+a4<10
 16                then
 17                  for a5 in 1..9 loop
 18                    if a5 not in (a1,a2,a3,a4) and a1*10+a2+a4*10+a5<100
 19                    then
 20                      for a6 in 1..9 loop
 21                        if a6 not in (a1,a2,a3,a4,a5) and a1*100+a2*10+a3+a4*100+a5*10+a6<1000
 22                        then
 23                          a9:=a3+a6;
 24                          a8:=trunc(a9/10);
 25                          a9:=a9-a8*10;
 26                          if a9 not in (a1,a2,a3,a4,a5,a6,0)
 27                          then
 28                            a8:=a8+a2+a5;
 29                            a7:=trunc(a8/10);
 30                            a8:=a8-a7*10;
 31                            if a8 not in (a1,a2,a3,a4,a5,a6,a9,0)
 32                            then
 33                              a7:=a7+a1+a4;
 34                              if a7 not in (a1,a2,a3,a4,a5,a6,a8,a9,0)
 35                              then
 36                                pipe row(a1||a2||a3||'+'||a4||a5||a6||'='||a7||a8||a9);
 37                              end if;
 38                            end if;
 39                          end if;
 40                        end if;
 41                      end loop;
 42                    end if;
 43                  end loop;
 44                end if;
 45              end loop;
 46            end if;
 47          end loop;
 48        end if;
 49      end loop;
 50    end loop;
 51  end;
 52  /

Function created.

SQL>
SQL> set timi on
SQL> select column_value as Equation_str, decode(rownum,1,count(*) over()) cnt from table(f);
124+659=783                 336
125+739=864
...
784+152=936

336 rows selected.

Elapsed: 00:00:00.96
SQL>
SQL> SELECT num1||' + '||num2||' = '|| num3 as Equation_str,
  2         CASE WHEN lag(cnt) over (order by num1)  cnt
  3                OR lag(cnt) over (order by num1) IS NULL
  4       THEN  cnt END AS Counter
  5  FROM
  6  (SELECT num1, num2, num3, count(*) over ( ) as cnt
  7   FROM
  8   (SELECT substr(num,1,3) num1, substr(num,4,3) num2, substr(num,7,3) num3
  9    FROM
 10    (SELECT replace(sys_connect_by_path(n,','), ',') num
 11     FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <=9)
 12     WHERE LEVEL = 9
 13     CONNECT BY NOCYCLE PRIOR n != n
 14     AND LEVEL <=9
 15     AND CASE LEVEL
 16              WHEN 1
 17              THEN CASE WHEN n < 9 THEN 1 END
 18              WHEN 4
 19              THEN CASE WHEN n  + CONNECT_BY_ROOT(n) = 3 THEN 1 END
 23                        ELSE CASE WHEN n >= CONNECT_BY_ROOT(n) + 1 THEN 1 END
 24                   END
 25             ELSE 1 END = 1
 26     )
 27    )
 28   WHERE to_number(num1)     + to_number(num2)       =  to_number(num3)
 29   );
124 + 659 = 783             336
125 + 739 = 864
...
784 + 152 = 936

336 rows selected.

Elapsed: 00:00:22.91

hmm, do not you just love select * from table(f); ? Just a sinister scheme to seduce you.

Filed under PL/SQL, SQL. Follow any responses to this entry through this RSS 2.0 feed.