Одностороннее сравнение Oracle 10g с MS SQL Server 2008 с точки зрения программиста SQL
Ожидает приглашения
На работе было задание перевести запросы SQL, написанные для СУБД Oracle 10g, на MS SQL Server 2008 (читай, миграция/перенос запросов SQL из Oracle в MS SQL). По ходу решил протоколировать обнаруженные расхождения. Материал данной статьи может содержать ошибки и будет отражать исключительно мое мнение (вполне вероятно — ошибочное).
В заголовке статьи я использовал термин «программист SQL», под которым имею в виду занимающегося написанием различных запросов на языке SQL человека.
Также в заголовке статьи использован термин «одностороннее сравнение», которым подчеркивается, что запросы переводились только в одну сторону, с Oracle 10g (далее Oracle) на MS SQL Server 2008 (далее MS SQL), и расхождения/разности выявлялись в одном направлении.
Итак…
— В Oracle есть функция DECODE, по-моему достаточно удобная. Её в MS SQL не оказалось. Пришлось воспользоваться оператором CASE-WHEN-THEN-ELSE-END.
— В Oracle есть функция CEIL — разновидность округления. В MS SQL та же функция называется CEILING.
— В Oracle есть Materialized View, в MS SQL этого счастья — нет. Придется создавать таблицы и продумывать DROP, INSERT, UPDATE.
— В Oracle есть функция wm_concat, которая используется для сцепления группы строковых значений. В MS SQL именно этой функции нет, но есть функция STUFF. Если вам придется искать замену wm_concat, то советую копать в сторону следующего запроса:
— В Oracle был запрос, который использовал функцию FIRST_VALUE в связке с OVER и PARTITION BY. Перевод этого запроса в MS SQL превратился в настоящее приключение (и даже небольшое исследование). В MS SQL Server 2008 функции FIRST_VALUE нет, но будет в MS SQL Server 2012. В итоге, аналог упомянутого запроса создал с использованием CTE (Common Table Expressions) With, OVER, PARTITION BY и функции RANK.
— Oracle NVL = MS SQL ISNULL
— В Oracle есть возможность использования оператора IN c несколькими полями (multi-column IN statement):
— В MS SQL можно использовать IN только с одним полем. Эмуляцию оператора IN c несколькими полями можно сделать с помощью оператора INNER JOIN:
— Для извлечения даты из типа datetime в MS SQL можно использовать CAST(datetime_value as DATE) или CONVERT(DATE, datetime_value), которые являются аналогами Oracle TRUNC(datetime_value).
— Для извлечения года, месяца и дня из даты в Oracle можно использовать следующее:
В MS SQL с этим проще, так как существуют встроенные функции YEAR(), MONTH() и DAY(). Однако, если потребуется номер месяца извлечь в формате MM (строго два символа), то потребуется, например, следующее:
В заголовке статьи я использовал термин «программист SQL», под которым имею в виду занимающегося написанием различных запросов на языке SQL человека.
Также в заголовке статьи использован термин «одностороннее сравнение», которым подчеркивается, что запросы переводились только в одну сторону, с Oracle 10g (далее Oracle) на MS SQL Server 2008 (далее MS SQL), и расхождения/разности выявлялись в одном направлении.
Итак…
— В Oracle есть функция DECODE, по-моему достаточно удобная. Её в MS SQL не оказалось. Пришлось воспользоваться оператором CASE-WHEN-THEN-ELSE-END.
— В Oracle есть функция CEIL — разновидность округления. В MS SQL та же функция называется CEILING.
— В Oracle есть Materialized View, в MS SQL этого счастья — нет. Придется создавать таблицы и продумывать DROP, INSERT, UPDATE.
— В Oracle есть функция wm_concat, которая используется для сцепления группы строковых значений. В MS SQL именно этой функции нет, но есть функция STUFF. Если вам придется искать замену wm_concat, то советую копать в сторону следующего запроса:
SELECT STUFF((SELECT ',' + Column_Name FROM Table_Name
FOR XML PATH ('')), 1, 1, '')
— В Oracle был запрос, который использовал функцию FIRST_VALUE в связке с OVER и PARTITION BY. Перевод этого запроса в MS SQL превратился в настоящее приключение (и даже небольшое исследование). В MS SQL Server 2008 функции FIRST_VALUE нет, но будет в MS SQL Server 2012. В итоге, аналог упомянутого запроса создал с использованием CTE (Common Table Expressions) With, OVER, PARTITION BY и функции RANK.
— Oracle NVL = MS SQL ISNULL
— В Oracle есть возможность использования оператора IN c несколькими полями (multi-column IN statement):
SELECT *
FROM <table_name>
WHERE (column1, column2, ..., columnN) IN
(
SELECT DISTINCT column1, column2, ..., columnN
FROM <other_table_name>
WHERE <where_clause>
)
[AND <other_filter_criteria>...]
— В MS SQL можно использовать IN только с одним полем. Эмуляцию оператора IN c несколькими полями можно сделать с помощью оператора INNER JOIN:
SELECT *
FROM <table_name> T1
INNER JOIN
(
SELECT DISTINCT column1, column2, ..., columnN
FROM <other_table_name>
WHERE <where_clause>
) T2 ON T1.column1 = T2.column1 AND T1.column2 = T2.column2
... AND T1.columnN = T2.columnN
WHERE <other_filter_criteria>
— Для извлечения даты из типа datetime в MS SQL можно использовать CAST(datetime_value as DATE) или CONVERT(DATE, datetime_value), которые являются аналогами Oracle TRUNC(datetime_value).
— Для извлечения года, месяца и дня из даты в Oracle можно использовать следующее:
TO_CHAR (a.date_time, 'yyyy') YEAR
TO_CHAR (a.date_time, 'mm') MONTH
TO_CHAR (a.date_time, 'dd') DAY
В MS SQL с этим проще, так как существуют встроенные функции YEAR(), MONTH() и DAY(). Однако, если потребуется номер месяца извлечь в формате MM (строго два символа), то потребуется, например, следующее:
LEFT(CONVERT ( char(20) , a.date_time , 101 ),2)