SQL
Язык программирования
Можно вычислить целочисленные факториалы только до 20!. При попытке вычислить 21! возникает ошибка “Arithmetic overflow error”, т.е. происходит переполнение разрядной сетки.
Для вещественных чисел вычисляется факториал 100! (Для этого в примере необходимо заменить bigint на float в 3-ей строке)
Чистый SQL позволяет вводить переменные в процессе исполнения запроса в виде заменяемых переменных. Для определения такой переменной ее имя (в данном случае A, B и C) следует использовать с амперсандом
Существует несколько способов ввести значения для заменяемых переменных. В данном примере первая ссылка на каждую переменную предваряется не одинарным, а двойным амперсандом
Следует отметить, что ссылки заменяются на значения “как есть”, поэтому отрицательные коэффициенты следует вводить в скобках.
Первая строка примера задает символ для десятичного разделителя, который используется при преобразовании чисел-корней в строки.
Сам запрос состоит из четырех разных запросов. Каждый запрос возвращает строку, содержащую результат вычислений, в одном из случаев (A=0, D=0, D>0 и D<0) и ничего — в трех остальных случаях. Результаты всех четырех запросов объединяются, чтобы получить окончательный результат.
SQL (Structured Query Language — Структурированный язык запросов) — язык управления базами данных для реляционных баз данных. Сам по себе SQL не является Тьюринг-полным языком программирования, но его стандарт позволяет создавать для него процедурные расширения, которые расширяют его функциональность до полноценного языка программирования.
Язык был создан в 1970х годах под названием “SEQUEL” для системы управления базами данных (СУБД) System R. Позднее он был переименован в “SQL” во избежание конфликта торговых марок. В 1979 году SQL был впервые опубликован в виде коммерческого продукта Oracle V2.
Первый официальный стандарт языка был принят ANSI в 1986 году и ISO — в 1987. С тех пор были созданы еще несколько версий стандарта, некоторые из них повторяли предыдущие с незначительными вариациями, другие принимали новые существенные черты.
Несмотря на существование стандартов, большинство распространенных реализаций SQL отличаются так сильно, что код редко может быть перенесен из одной СУБД в другую без внесения существенных изменений. Это объясняется большим объемом и сложностью стандарта, а также нехваткой в нем спецификаций в некоторых важных областях реализации.
SQL создавался как простой стандартизированный способ извлечения и управления данными, содержащимися в реляционной базе данных. Позднее он стал сложнее, чем задумывался, и превратился в инструмент разработчика, а не конечного пользователя. В настоящее время SQL (по большей части в реализации Oracle) остается самым популярным из языков управления базами данных, хотя и существует ряд альтернатив.
SQL состоит из четырех отдельных частей:
Язык был создан в 1970х годах под названием “SEQUEL” для системы управления базами данных (СУБД) System R. Позднее он был переименован в “SQL” во избежание конфликта торговых марок. В 1979 году SQL был впервые опубликован в виде коммерческого продукта Oracle V2.
Первый официальный стандарт языка был принят ANSI в 1986 году и ISO — в 1987. С тех пор были созданы еще несколько версий стандарта, некоторые из них повторяли предыдущие с незначительными вариациями, другие принимали новые существенные черты.
Несмотря на существование стандартов, большинство распространенных реализаций SQL отличаются так сильно, что код редко может быть перенесен из одной СУБД в другую без внесения существенных изменений. Это объясняется большим объемом и сложностью стандарта, а также нехваткой в нем спецификаций в некоторых важных областях реализации.
SQL создавался как простой стандартизированный способ извлечения и управления данными, содержащимися в реляционной базе данных. Позднее он стал сложнее, чем задумывался, и превратился в инструмент разработчика, а не конечного пользователя. В настоящее время SQL (по большей части в реализации Oracle) остается самым популярным из языков управления базами данных, хотя и существует ряд альтернатив.
SQL состоит из четырех отдельных частей:
- язык определения данных (DDL) используется для определения структур данных, хранящихся в базе данных. Операторы DDL позволяют создавать, изменять и удалять отдельные объекты в БД. Допустимые типы объектов зависят от используемой СУБД и обычно включают базы данных, пользователей, таблицы и ряд более мелких вспомогательных объектов, например, роли и индексы.
- язык манипуляции данными (DML) используется для извлечения и изменения данных в БД. Операторы DML позволяют извлекать, вставлять, изменять и удалять данные в таблицах. Иногда операторы
select
извлечения данных не рассматриваются как часть DML, поскольку они не изменяют состояние данных. Все операторы DML носят декларативный характер. - язык определения доступа к данным (DCL) используется для контроля доступа к данным в БД. Операторы DCL применяются к привилегиям и позволяют выдавать и отбирать права на применение определенных операторов DDL и DML к определенным объектам БД.
- язык управления транзакциями (TCL) используется для контроля обработки транзакций в БД. Обычно операторы TCL включают
commit
для подтверждения изменений, сделанных в ходе транзакции,rollback
для их отмены иsavepoint
для разбиения транзакции на несколько меньших частей.
Примеры:
Hello, World!:
Пример для версий Oracle 10g SQL, Oracle 11g SQLСтрока ‘Hello, World!’ выбирается из встроенной таблицыdual
, используемой для запросов, не требующих обращения к настоящим таблицам.select 'Hello, World!'
from dual;
Факториал:
Пример для версий Oracle 10g SQL, Oracle 11g SQLSQL не поддерживает циклы, рекурсии или пользовательские функции. Данный пример демонстрирует возможный обходной путь, использующий:- псевдостолбец
level
для создания псевдотаблицt1
иt2
, содержащих числа от 1 до 16, - агрегатную функцию
sum
, позволяющую суммировать элементы множества без явного использования цикла, - и математические функции
ln
иexp
, позволяющие заменить произведение (необходимое для вычисления факториала) на сумму (предоставляемую SQL).
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
Числа Фибоначчи:
Пример для версий Oracle 10g SQL, Oracle 11g SQLSQL не поддерживает циклы или рекурсии, кроме того, конкатенация полей из разных строк таблицы или запроса не является стандартной агрегатной функцией. Данный пример использует:- формулу Бине и математические функции
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;
Hello, World!:
Пример для версий Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012, MySQL 5, PostgreSQL 8.4, PostgreSQL 9.1, sqlite 3.7.3select 'Hello, World!';
Факториал:
Пример для версий Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012Используется рекурсивное определение факториала, реализованное через рекурсивный запрос. Каждая строка запроса содержит два числовых поля — n и n!, и каждая следующая строка вычисляется с использованием данных из предыдущей.Можно вычислить целочисленные факториалы только до 20!. При попытке вычислить 21! возникает ошибка “Arithmetic overflow error”, т.е. происходит переполнение разрядной сетки.
Для вещественных чисел вычисляется факториал 100! (Для этого в примере необходимо заменить bigint на float в 3-ей строке)
with factorial(n, f) as
(
select 0, convert(bigint,1)
union all
select n+1, f*(n+1) from factorial where n<20
)
select cast(n as varchar)+'! = '+cast(f as varchar)
from factorial
Числа Фибоначчи:
Пример для версий Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012Используется итеративное определение чисел Фибоначчи, реализованное через рекурсивный запрос. Каждая строка запроса содержит два соседних числа последовательности, и следующая строка вычисляется как (последнее число, сумма чисел) предыдущей строки. Таким образом все числа, кроме первого и последнего, встречаются дважды, поэтому в результат входят только первые числа каждой строки.with fibonacci(a, b) as
(
select 1, 1
union all
select b, a+b from fibonacci where b < 1000
)
SELECT cast(a as varchar)+', ' AS [text()]
FROM fibonacci
FOR XML PATH ('')
Факториал:
Пример для версий Oracle 10g SQL, Oracle 11g SQLЭтот пример демонстрирует использование оператора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
);
Числа Фибоначчи:
Пример для версий Oracle 10g SQL, Oracle 11g SQLЭтот пример демонстрирует использование оператора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]))
)
);
Факториал:
Пример для версий MySQL 5ЗаменитеTABLE
на любую таблицу, к которой есть доступ, например, mysql.help_topic
.select concat(cast(t2.n as char), "! = ", cast(exp(sum(log(t1.n))) as char))
from
( select @i := @i+1 AS n
from TABLE, (select @i := 0) as sel1
limit 16 ) t1,
( select @j := @j+1 AS n
from TABLE, (select @j := 0) as sel1
limit 16 ) t2
where t1.n <= t2.n
group by t2.n
Числа Фибоначчи:
Пример для версий MySQL 5ЗаменитеTABLE
на любую таблицу, к которой есть доступ, например, mysql.help_topic
.select concat(group_concat(f separator ', '), ', ...')
from (select @f := @i + @j as f, @i := @j, @j := @f
from TABLE, (select @i := 1, @j := 0) sel1
limit 16) t
Hello, World!:
Пример для версий Oracle 10g SQL, Oracle 11g SQLВ этом примере используется анонимный блок PL/SQL, который выводит сообщение в стандартный поток вывода с помощью пакетаdbms_output
.begin
dbms_output.put_line('Hello, World!');
end;
Факториал:
Пример для версий Oracle 10g SQL, Oracle 11g SQLЭтот пример демонстрирует итеративное вычисление факториала средствами 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;
Числа Фибоначчи:
Пример для версий Oracle 10g SQL, Oracle 11g SQLЭтот пример использует итеративное определение чисел Фибоначчи. Уже вычисленные числа хранятся в структуре данных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;
Квадратное уравнение:
Пример для версий Oracle 10g SQL, Oracle 11g SQLЭтот пример тестировался в 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 10g SQL, Oracle 11g SQLВ этом примере используются регулярные выражения Oracle SQL. Первым действием функцияregexp_replace
заменяет все цифры на пробелы — это необходимо для initcap
, которая считает цифры частью слов и не переводит буквы, следующие за цифрами, в верхний регистр. Затем применяется initcap
, которая переводит все слова в нижний регистр, а их первые буквы — в верхний. Наконец, второе использование regexp_replace
удаляет из строки все знаки пунктуации и пробелы.select regexp_replace(initcap(regexp_replace('&TEXT', '[[:digit:]]', ' ')), '([[:punct:] | [:blank:]])', '')
from dual
CamelCase:
Пример для версий Oracle 11g SQLВ этом примере используются регулярные выражения 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;
Квадратное уравнение:
Пример для версий Oracle 11g SQLdeclare
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;
Факториал:
Пример для версий PostgreSQL 9.1В PostgreSQL задача вычисления факториала решается полностью встроенными средствами: постфиксной функцией!
(существует также префиксная форма !!
) и функцией generate_series
, которая генерирует набор строк, содержащих значения от первого до второго аргумента, включительно.select n || '! = ' || (n!)
from generate_series(0,16) as seq(n);
Числа Фибоначчи:
Пример для версий PostgreSQL 9.1WITH RECURSIVE t(a,b) AS (
VALUES(1,1)
UNION ALL
SELECT b, a + b FROM t
WHERE b < 1000
)
SELECT array_to_string(array(SELECT a FROM t), ', ') || ', ...';
Факториал:
Пример для версий Microsoft SQL Server 2005declare @max_n tinyint = 20
;with t as (
select 1 as n, convert(bigint,1) as f
union all
select n+1, f*(n+1) from t
where n < @max_n
)
select convert(varchar,n)+'! = '+convert(varchar(32),f)+', '
from t as [text]
FOR XML PATH ('')
Числа Фибоначчи:
Пример для версий Microsoft SQL Server 2005, Microsoft SQL Server 2008 R2, Microsoft SQL Server 2012Используется возможность рекурсивных запросов. Кол-во членов ряда — 92declare @max_n tinyint = 92
;with t as (
select n = 1, fib = convert(bigint,1), xfib = convert(bigint,0)
union all
select n = n+1, fib = fib+xfib, xfib = fib from t
where n < @max_n
)
select fib from t