Oracle 11g SQL

Version of implementation Oracle SQL of programming language SQL

A stable major release of Oracle.

Examples:

Hello, World! - SQL (5):

‘Hello, World!’ string is selected from built-in table dual which is used for queries which don’t need data from real tables.

select 'Hello, World!'
  from dual;

Factorial - SQL (6):

Pure SQL doesn’t support loops, recursions or user-defined functions. This example shows one possible workaround which uses

pseudocolumn level to construct a pseudotable containing numbers 1 through 16,
aggregate function sum which allows to sum the elements of a set
and math functions exp and ln to replace multiplication (required to calculate the factorial) with addition (provided by SQL).

Note that 0! will not be present in the return, since trying to calculate ln(0) produces an exception.

select t2.n || '! = ' || round(exp(sum(ln(t1.n))))
  from 
  ( select level n
      from dual
   connect by level <= 16) t1,
  ( select level n
      from dual
   connect by level <= 16) t2
 where t1.n<=t2.n
 group by t2.n
 order by t2.n

Pure SQL doesn’t support loops, recursions or user-defined functions. Besides, concatenating fields from multiple rows of a table or a subquery is not a standard aggregate function. This example uses:

Binet’s formula and math functions round, power and sqrt to calculate n-th Fibonacci number;
pseudocolumn level to construct a pseudotable t1 containing numbers 1 through 16;
built-in function SYS_CONNECT_BY_PATH to concatenate the resulting numbers in ascending order.

 SELECT REPLACE(MAX(SYS_CONNECT_BY_PATH(fib||', ', '/')),'/','')||'...' fiblist 
   FROM ( 
    SELECT n, fib, ROW_NUMBER() 
      OVER (ORDER BY n) r 
      FROM (select n, round((power((1+sqrt(5))*0.5, n)-power((1-sqrt(5))*0.5, n))/sqrt(5)) fib 
              from (select level n
                      from dual
                   connect by level <= 16) t1) t2
 ) 
  START WITH r=1 
CONNECT BY PRIOR r = r-1;

Factorial - SQL (43):

This example shows the use of model clause, available since Oracle 10g. It allows array-like processing of query rows. Each row has two columns — iteration number (stored in n) and its factorial (stored in f).

select n || '! = ' || f factorial
  from dual
 model
    return all rows
    dimension by ( 0 d ) 
    measures ( 0 f, 1 n )
    rules iterate (17)
    ( f[iteration_number] = decode(iteration_number, 0, 1, f[iteration_number-1]*iteration_number),
      n[iteration_number] = iteration_number
    );

Fibonacci numbers - SQL (45):

This example shows the use of model clause, available since Oracle 10g. It allows array-like processing of query rows. Each row has two columns — Fibonacci number itself (stored in f) and concatenation of all Fibonacci numbers less than or equal to the current Fibonacci number (stored in s). Iterative aggregation of Fibonacci numbers in the same query that they were generated is easier than aggregating them separately.

select max(s) || ', ...'
  from
(select s
   from dual
   model 
     return all rows
     dimension by ( 0 d ) 
     measures ( cast(' ' as varchar2(200)) s, 0 f)
     rules iterate (16)
     (  f[iteration_number] = decode(iteration_number, 0, 1, 1, 1, f[iteration_number-1] + f[iteration_number-2]), 
        s[iteration_number] = decode(iteration_number, 0, to_char(f[iteration_number]), s[iteration_number-1] || ', ' || to_char(f[iteration_number]))
     )
);

Hello, World! - SQL (89):

This example accomplishes the task by means of anonymous PL/SQL block, which uses standard package dbms_output to print the message to standard output.

begin
    dbms_output.put_line('Hello, World!');
end;

Factorial - SQL (90):

This example calculates factorials iteratively by means of PL/SQL.

declare
    n    number := 0;
    f    number := 1;
begin
    while (n<=16)
    loop
        dbms_output.put_line(n || '! = ' || f);
        n := n+1;
        f := f*n;
    end loop;
end;

Fibonacci numbers - SQL (91):

This example implements iterative definition of Fibonacci numbers by means of PL/SQL. Already calculated numbers are stored in varray, PL/SQL analogue of array in other languages.

declare
    type vector is varray(16) of number;
    fib  vector := vector();
    i    number;
    s    varchar2(100);       
begin
    fib.extend(16);
    fib(1) := 1;
    fib(2) := 1;
    s := fib(1) || ', ' || fib(2) || ', ';
    for i in 3..16 loop
        fib(i) := fib(i-1) + fib(i-2);
        s := s || fib(i) || ', '; 
    end loop;
    dbms_output.put_line(s || '...');
end;

Quadratic equation - SQL (171):

This example was tested in SQL*Plus and TOAD.

Pure SQL allows to input values at runtime in the form of substitution variables. To define a substitution variable, use its name (in this case A, B and C) with an ampersand & before it each time you need to reference it. When the query needs to be executed, you will receive a prompt to enter the values of the variables. Each reference to such variable will be replaced with its value, and the resulting query will be executed.

There are several ways to input the values for substitution variables. In this example first reference to each variable has double ampersand && before its name. This way the value of the variable has to be entered only once, and all following references will be replaced with the same value (using single ampersand in SQL*Plus asks to enter the value for each reference to the same variable). PL/SQL Developer requires that all variables have single & before their names, otherwise ORA-01008 “Not all variables bound” exception will be raised.

Note that the references are substituted with the values “as is”, so negative values of coefficients have to be entered in brackets.

First line of the example sets the character for decimal separator which is used when the numbers are converted into strings.

The query itself is composed of four different queries. Each query returns a string containing the result of calculations for one case of quadratic equation, and returns nothing for three other cases. The results of each query are united to produce the final result.

alter session set NLS_NUMERIC_CHARACTERS='. ';

select 'Not a quadratic equation.' ans
  from dual
 where &&A = 0
union
select 'x = ' || to_char(-&&B/2/&A)
  from dual
 where &A != 0 and &B*&B-4*&A*&&C = 0
union
select 'x1 = ' || to_char((-&B+sqrt(&B*&B-4*&A*&C))/2/&A) || ', x2 = ' || to_char(-&B-sqrt(&B*&B-4*&A*&C))/2/&A
  from dual
 where &A != 0 and &B*&B-4*&A*&C > 0
union
select 'x1 = (' || to_char(-&B/2/&A) || ',' || to_char(sqrt(-&B*&B+4*&A*&C)/2/&A) || '), ' || 
       'x2 = (' || to_char(-&B/2/&A) || ',' || to_char(-sqrt(-&B*&B+4*&A*&C)/2/&A) || ')' 
  from dual
 where &A != 0 and &B*&B-4*&A*&C < 0;

CamelCase - SQL (283):

This example uses Oracle SQL regular expressions. First use of regexp_replace replaces all digits with spaces — this is necessary for further initcap usage (this function treats digits as parts of the words and doesn’t capitalize following letters). Next, initcap converts all words to lowercase with first letter capitalized. Finally, second use of regexp_replace removes all punctuation and spaces from the string.

&TEXT is substitution variable, which allows to input different strings without rewriting the select statement.

select regexp_replace(initcap(regexp_replace('&TEXT', '[[:digit:]]', ' ')), '([[:punct:] | [:blank:]])', '')
  from dual

CamelCase - SQL (427):

This example uses Oracle regular expressions combined with PL/SQL. regex_substr returns a substring of text that is occurrence‘s match to the given regular expression.

declare
  text varchar2(100) := '&user_input';
  word varchar2(100);
  camelcase varchar2(100);
  occurrence number := 1;
begin
  loop
    word := regexp_substr(text, '[[:alpha:]]+', 1, occurrence);
    exit when word is null;
    camelcase := camelcase || initcap(word);
    occurrence := occurrence + 1;
  end loop;
  dbms_output.put_line(camelcase);
end;

Quadratic equation - SQL (428):

declare
  A number := '&A';
  B number := '&B';
  C number := '&C';
  D number := B * B - 4 * A * C;
begin
  if A = 0 then
    dbms_output.put_line('Not a quadratic equation.');
    return;
  end if;
  if D = 0 then
    dbms_output.put_line('x = ' || to_char(-B/2/A));
  elsif D > 0 then
    dbms_output.put_line('x1 = ' || to_char((-B-sqrt(D))/2/A));
    dbms_output.put_line('x2 = ' || to_char((-B+sqrt(D))/2/A));
  else
    dbms_output.put_line('x1 = (' || to_char(-B/2/A) || ', ' || to_char(sqrt(-D)/2/A) || ')');
    dbms_output.put_line('x2 = (' || to_char(-B/2/A) || ', ' || to_char(-sqrt(-D)/2/A) || ')');
  end if;
end;