Oracle SQL

Примеры:

Hello, World!:

Строка ‘Hello, World!’ выбирается из встроенной таблицы dual, используемой для запросов, не требующих обращения к настоящим таблицам.

select 'Hello, World!'
  from dual;

Факториал:

SQL не поддерживает циклы, рекурсии или пользовательские функции. Данный пример демонстрирует возможный обходной путь, использующий:

• псевдостолбец level для создания псевдотаблиц t1 и t2, содержащих числа от 1 до 16,
• агрегатную функцию sum, позволяющую суммировать элементы множества без явного использования цикла,
• и математические функции ln и exp, позволяющие заменить произведение (необходимое для вычисления факториала) на сумму (предоставляемую SQL).

Строка “0! = 1” не войдет в набор строк, полученный в результате, т.к. попытка вычислить ln(0) приводит к исключению.

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

Числа Фибоначчи:

SQL не поддерживает циклы или рекурсии, кроме того, конкатенация полей из разных строк таблицы или запроса не является стандартной агрегатной функцией. Данный пример использует:

• формулу Бине и математические функции ROUND, POWER и SQRT для вычисления n-ого числа Фибоначчи;
• псевдостолбец level для создания псевдотаблицы t1, содержащей числа от 1 до 16;
• встроенную функцию SYS_CONNECT_BY_PATH для упорядоченной конкатенации полученных чисел.

 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; 

Факториал:

Этот пример демонстрирует использование оператора model, доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — номер строки n и его факториал 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
    );

Числа Фибоначчи:

Этот пример демонстрирует использование оператора model, доступного начиная с версии Oracle 10g и позволяющего обработку строк запроса как элементов массива. Каждая строка содержит два поля — само число Фибоначчи и конкатенация всех чисел, меньше или равных ему. Итеративная конкатенация чисел в том же запросе, в котором они генерируются, выполняется проще и быстрее, чем агрегация как отдельное действие.

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!:

В этом примере используется анонимный блок PL/SQL, который выводит сообщение в стандартный поток вывода с помощью пакета dbms_output.

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

Факториал:

Этот пример демонстрирует итеративное вычисление факториала средствами 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;

Числа Фибоначчи:

Этот пример использует итеративное определение чисел Фибоначчи. Уже вычисленные числа хранятся в структуре данных varray — аналоге массива.

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;

Квадратное уравнение:

Этот пример тестировался в SQL*Plus, TOAD и PL/SQL Developer.

Чистый SQL позволяет вводить переменные в процессе исполнения запроса в виде заменяемых переменных. Для определения такой переменной ее имя (в данном случае A, B и C) следует использовать с амперсандом & перед ним каждый раз, когда нужно сослаться на эту переменную. Когда запрос выполняется, пользователь получает запрос на ввод значений всех заменяемых переменных, использованных в запросе. После ввода значений каждая ссылка на такую переменную заменяется на ее значение, и полученный запрос выполняется.

Существует несколько способов ввести значения для заменяемых переменных. В данном примере первая ссылка на каждую переменную предваряется не одинарным, а двойным амперсандом &&. Таким образом значение для каждой переменной вводится только один раз, а все последующие ссылки на нее будут заменены тем же самым значением (при использовании одиночного амперсанда в SQL*Plus значение для каждой ссылки на одну и ту же переменную приходится вводить отдельно). В PL/SQL Developer ссылки на все переменные должны предваряться одиночным знаком &, иначе будет возникать ошибка ORA-01008 “Not all variables bound”.

Следует отметить, что ссылки заменяются на значения “как есть”, поэтому отрицательные коэффициенты следует вводить в скобках.

Первая строка примера задает символ для десятичного разделителя, который используется при преобразовании чисел-корней в строки.

Сам запрос состоит из четырех разных запросов. Каждый запрос возвращает строку, содержащую результат вычислений, в одном из случаев (A=0, D=0, D>0 и D<0) и ничего — в трех остальных случаях. Результаты всех четырех запросов объединяются, чтобы получить окончательный результат.

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:

В этом примере используются регулярные выражения Oracle SQL. Первым действием функция regexp_replace заменяет все цифры на пробелы — это необходимо для initcap, которая считает цифры частью слов и не переводит буквы, следующие за цифрами, в верхний регистр. Затем применяется initcap, которая переводит все слова в нижний регистр, а их первые буквы — в верхний. Наконец, второе использование regexp_replace удаляет из строки все знаки пунктуации и пробелы.

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

CamelCase:

В этом примере используются регулярные выражения Oracle SQL в сочетании с PL/SQL. Функция regexp_substr возвращает подстроку text, являющуюся соответствием заданному регулярному выражению номер occurrence.

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;

Квадратное уравнение:

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;