Oracle получить дробную часть числа
SQL functions are built into Oracle Database and are available for use in various appropriate SQL statements. Do not confuse SQL functions with user-defined functions written in PL/SQL.
If you call a SQL function with an argument of a datatype other than the datatype expected by the SQL function, then Oracle attempts to convert the argument to the expected datatype before performing the SQL function. If you call a SQL function with a null argument, then the SQL function automatically returns null. The only SQL functions that do not necessarily follow this behavior are CONCAT , NVL , REPLACE , and REGEXP_REPLACE .
The combined values of the NLS_COMP and NLS_SORT settings determine the rules by which characters are sorted and compared. If NLS_COMP is set to LINGUISTIC for your database, then all entities in this chapter will be interpreted according to the rules specified by the NLS_SORT parameter. If NLS_COMP is not set to LINGUISTIC , then the functions are interpreted without regard to the NLS_SORT setting. NLS_SORT can be explicitly set. If it is not set explicitly, it is derived from NLS_LANGUAGE . Please refer to Oracle Database Globalization Support Guide for more information on these settings.
In the syntax diagrams for SQL functions, arguments are indicated by their datatypes. When the parameter function appears in SQL syntax, replace it with one of the functions described in this section. Functions are grouped by the datatypes of their arguments and their return values.
When you apply SQL functions to LOB columns, Oracle Database creates temporary LOBs during SQL and PL/SQL processing. You should ensure that temporary tablespace quota is sufficient for storing these temporary LOBs for your application.
"User-Defined Functions" for information on user functions and "Data Conversion" for implicit conversion of datatypes
Oracle Text Reference for information on functions used with Oracle Text
Oracle Data Mining Application Developer's Guide for information on frequent itemset functions used with Oracle Data Mining
The syntax showing the categories of functions follows:
The sections that follow list the built-in SQL functions in each of the groups illustrated in the preceding diagrams except user-defined functions. All of the built-in SQL functions are then described in alphabetical order.
Числовые операторы
Оператор | Операция | Приоритет |
** | Возведение в степень | 1 |
+ | Тождество | 2 |
- | Отрицание | 2 |
* | Умножение | 3 |
/ | Деление | 3 |
+ | Сложение | 4 |
– | Вычитание | 4 |
= | Равно | 5 |
Меньше чем | 5 | |
> | Больше чем | 5 |
Меньше либо равно | 5 | |
>= | Больше либо равно | 5 |
<>, !=, ~=, ^= | Не равно | 5 |
IS NULL | Проверка неопределенности | 5 |
BETWEEN | Принадлежность диапазону | 5 |
NOT | Логическое отрицание | 6 |
AND | Конъюнкция | 7 |
OR | Дизъюнкция | 8 |
General Comparison Functions
Datetime Functions
Datetime functions operate on date ( DATE ), timestamp ( TIMESTAMP , TIMESTAMP WITH TIME ZONE , and TIMESTAMP WITH LOCAL TIME ZONE ), and interval ( INTERVAL DAY TO SECOND , INTERVAL YEAR TO MONTH ) values.
Some of the datetime functions were designed for the Oracle DATE datatype ( ADD_MONTHS , CURRENT_DATE , LAST_DAY , NEW_TIME , and NEXT_DAY ). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.
The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.
Передача параметров NLS функции TO_CHAR
По аналогии с функцией TO_NUMBER , функция TO_CHAR может получать в третьем параметре строку настроек NLS . Пример:
Таким способом можно задавать три параметра NLS : NLS_NUMERIC_CHARACTERS , NLS_CURRENCY и NLS_ISO_CURRENCY . Пример одновременного использования всех трех параметров приводился ранее, в разделе «Передача функции TO_NUMBER параметров NLS ».
Object Reference Functions
Oracle Database Concepts for more information about REF datatypes
NULL-Related Functions
Сводка числовых функций PL/SQL
В этом разделе представлены краткие описания всех встроенных функций PL/SQL. Там, где это возможно, функции перегружаются для разных числовых типов. Например:
- ABS
Функция перегружена для типов BINARY_DOUBLE, BINARY_FLOAT, NUMBER, SIMPLE_INTEGER, SIMPLE_FLOAT, SIMPLE_DOUBLE и PLS_INTEGER , так как операция определения абсолютного значения применима как к вещественным, так и к целочисленным значениям. - BITAND
Функция перегружена для типов PLS_INTEGER и INTEGER (подтип NUMBER ), так как операция AND может применяться только к целочисленным значениям. - CEIL
Функция перегружена для типов BINARY_DOUBLE, BINARY_FLOAT и NUMBER , поскольку функция CEIL не актуальна для целых чисел.
Чтобы узнать, для каких типов перегружена та или иная функция, запросите описание встроенного пакета SYS.STANDARD :
Почти все функции в следующем списке определяются во встроенном пакете SYS . STANDARD . Единственным исключением является функция BIN_TO_NUM . За полной документацией по отдельным функциям обращайтесь к справочнику Oracle SQL Reference.
Возвращает абсолютное значение числа.
Возвращает арккосинус угла n из диапазона [–1; 1]. Возвращаемое функцией значение находится в пределах от 0 до π.
Возвращает арксинус угла n из диапазона [–1; 1]. Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
Возвращает арктангенс угла n из диапазона (– ∞ ; + ∞ ). Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
Возвращает арктангенс n/m для чисел n и m из диапазона (– ∞ ; + ∞ ). Возвращаемое функцией значение находится в пределах от −π/2 до −π/2.
BIN_TO_NUM(b1, b2. bn)
Преобразует битовый вектор, представленный значениями от b1 до bn, в число. Каждое из значений вектора должно быть равно либо 0, либо 1. Например, результат вызова BIN_TO_NUM(1,1,0,0) равен 12.
Выполняет поразрядную операцию AND над битами двух положительных целых чисел. Например, вызов BITAND(12,4) дает результат 4, то есть в значении 12 (двоичное 1100) установлен 4-й бит.
Вам будет проще работать с BITAND , если вы ограничитесь положительными целыми числами. Значения типа PLS_INTEGER , особенно удобного в сочетании с BITAND , позволяют хранить значения до 230; таким образом, в вашем распоряжении 30 битов для выполнения поразрядных операций.
Возвращает наименьшее целое число, которое больше либо равно заданному значению. В табл. 1 и рис. 1 функция CEIL сравнивается с другими числовыми функциями округления и усечения.
Возвращает косинус угла n , заданного в радианах. Если угол задается в градусах, то значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический косинус n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями COS и COSH выражается следующей формулой: COS (i * n) = COSH (n)
Возвращает число e в степени n , где n — аргумент функции. Число e (приблизительно равное 2,71828) является основанием натурального логарифма.
Возвращает наибольшее целое число, которое меньше или равно заданному значению. В табл. 1 и рис. 1 функция FLOOR сравнивается с другими числовыми функциями округления и усечения.
GREATEST(n1, n2. n3)
Возвращает наибольшее число во входном списке; например, результат вызова GREATEST (1,0, -1, 20) равен 20.
Возвращает наименьшее число во входном списке; например, результат вызова LEAST (1, 0, –1, 20) равен −1.
Возвращает остаток от деления n на m . Остаток вычисляется по формуле, эквивалентной n-(m*FLOOR(n/m)) при совпадении знаков n и m или n-(m*CEIL(n/m)) при различающихся знаках. Например, результат вызова MOD(10, 2.8) равен 1.6. Если аргумент m равен 0, возвращается значение n . С помощью функции MOD можно быстро проверить число на четность или нечетность:
Возвращает m , если n является NaN («не числом»); в противном случае возвращается n . Возвращаемое значение относится к числовому типу аргумента, обладающему наибольшим приоритетом в следующем порядке: BINARY_DOUBLE, BINARY_FLOAT или NUMBER .
Возводит n в степень m . Если значение n отрицательно, то аргумент m должен быть целым числом. В следующем примере функция POWER используется для вычисления диапазона допустимых значений переменной типа PLS_INTEGER (от −231 −1 до 231 −1) :
Возвращает «псевдоостаток» от деления n на m. Значение вычисляется по следующей формуле:
Например, результат вызова REMAINDER(10, 2.8) равен −1.2. Сравните с функцией MOD .
Возвращает значение n , округленное до ближайшего целого. Пример:
Возвращает значение n , округленное до m разрядов. Значение m может быть отрицательным: в этом случае функция ROUND отсчитывает позиции округления влево, а не вправо от десятичной запятой. Примеры:
В табл. 1 и рис. 1 функция ROUND сравнивается с другими числовыми функциями округления и усечения.
Возвращает −1, 0 или +1, если значение n меньше нуля, равно нулю или больше нуля соответственно.
Возвращает синус угла n , заданного в радианах. Если угол задается в градусах, значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический синус n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями SIN и SINH выражается следующей формулой:
Возвращает тангенс угла n , заданного в радианах. Если угол задается в градусах, значение следует преобразовать в радианы (см. раздел «Тригонометрические функции»).
Возвращает гиперболический тангенс n . Если n — вещественное число, а i — мнимая единица, тогда связь между функциями TAN и TANH выражается следующей формулой:
Усекает значение n до целого числа. Например, результат вызова TRUNC(10.51) равен 10.
Усекает значение n до m разрядов. Например, результат вызова TRUNC(10.789, 2) равен 10.78. Значение m может быть отрицательным: в этом случае функция TRUNC отсчитывает позиции усечения влево, а не вправо от десятичной запятой. Так, вызов TRUNC(1264, -2) дает значение 1200.
В табл. 1 и рис. 1 функция CEIL сравнивается с другими числовыми функциями округления и усечения.
В том случае, когда при преобразовании символьной строки в число слева или справа от десятичной запятой получается больше цифр, чем допускает маска форматирования, в PL/SQL происходит ошибка. Но при преобразовании числа в символьную строку ошибка возникает только при наличии лишних цифр слева от запятой. Если маска форматирования содержит меньше цифр после десятичной запятой, чем требуется для представления числа, число округляется до указанного количества цифр.
Если же дробная часть числа не помещается в маску, происходит округление:
Цифры 5 и больше округляются в большую сторону, так что число 123,4567 округляется до 123,46, а цифры меньше 5 — в меньшую, поэтому 123,4xxx округляется до 123.
Неявные преобразования
Преобразования между числами и строками можно выполнить еще одним способом: просто поручите эту работу PL/SQL. Такое преобразование называется неявным, так как оно не определяется явно в программном коде. Пример неявных преобразований:
Как упоминалось в главе 7, неявные преобразования имеют ряд недостатков. Прежде всего, я считаю, что разработчик должен в полной мере контролировать свой код, а при использовании этого метода этот контроль отчасти утрачивается. Всегда лучше знать, когда и какое именно выполняется преобразование, поэтому желательно выполнять его явно. Если полагаться на неявное преобразование, то вы не сможете отследить, где и когда оно выполняется, и код станет менее эффективным. Кроме того, наличие явных преобразований делает код более понятным другим программистам.
Другой недостаток неявных преобразований заключается в том, что они могут нормально работать (по крайней мере на первый взгляд) в простых случаях, но их результат не всегда очевиден. Рассмотрим пример:
Single-Row Functions
Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.
Conversion Functions
XML Functions
Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause . For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE , GROUP BY , and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.
The semantics of this syntax are discussed in the sections that follow.
Specify the name of an analytic function (see the listing of analytic functions following this discussion of semantics).
Analytic functions take 0 to 3 arguments. The arguments can be any numeric datatype or any nonnumeric datatype that can be implicitly converted to a numeric datatype. Oracle determines the argument with the highest numeric precedence and implicitly converts the remaining arguments to that datatype. The return type is also that datatype, unless otherwise noted for an individual function.
"Numeric Precedence" for information on numeric precedence and Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion
Use OVER analytic_clause to indicate that the function operates on a query result set. That is, it is computed after the FROM , WHERE , GROUP BY , and HAVING clauses. You can specify analytic functions with this clause in the select list or ORDER BY clause. To filter the results of a query based on an analytic function, nest these functions within the parent query, and then filter the results of the nested subquery.
Notes on the analytic_clause : The following notes apply to the analytic_clause :
You cannot specify any analytic function in any part of the analytic_clause . That is, you cannot nest analytic functions. However, you can specify an analytic function in a subquery and compute another analytic function over it.
You can specify OVER analytic_clause with user-defined analytic functions as well as built-in analytic functions. See CREATE FUNCTION.
Use the PARTITION BY clause to partition the query result set into groups based on one or more value_expr . If you omit this clause, then the function treats all rows of the query result set as a single group.
To use the query_partition_clause in an analytic function, use the upper branch of the syntax (without parentheses). To use this clause in a model query (in the model_column_clauses ) or a partitioned outer join (in the outer_join_clause ), use the lower branch of the syntax (with parentheses).
You can specify multiple analytic functions in the same query, each with the same or different PARTITION BY keys.
If the objects being queried have the parallel attribute, and if you specify an analytic function with the query_partition_clause , then the function computations are parallelized as well.
Valid values of value_expr are constants, columns, nonanalytic functions, function expressions, or expressions involving any of these.
Use the order_by_clause to specify how data is ordered within a partition. For all analytic functions except PERCENTILE_CONT and PERCENTILE_DISC (which take only a single key), you can order the values in a partition on multiple keys, each defined by a value_expr and each qualified by an ordering sequence.
Within each function, you can specify multiple ordering expressions. Doing so is especially useful when using functions that rank values, because the second expression can resolve ties between identical values for the first expression.
Whenever the order_by_clause results in identical values for multiple rows, the function returns the same result for each of those rows. Please refer to the analytic example for SUM for an illustration of this behavior.
Restrictions on the ORDER BY Clause The following restrictions apply to the ORDER BY clause:
When used in an analytic function, the order_by_clause must take an expression ( expr ). The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position ( position ) and column aliases ( c_alias ) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.
An analytic function that uses the RANGE keyword can use multiple sort keys in its ORDER BY clause if it specifies either of these two windows:
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW . The short form of this is RANGE UNBOUNDED PRECEDING .
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING . The short form of this is RANGE UNBOUNDED FOLLOWING .
Window boundaries other than these two can have only one sort key in the ORDER BY clause of the analytic function. This restriction does not apply to window boundaries specified by the ROW keyword.
ASC | DESC Specify the ordering sequence (ascending or descending). ASC is the default.
NULLS FIRST | NULLS LAST Specify whether returned rows containing nulls should appear first or last in the ordering sequence.
NULLS LAST is the default for ascending order, and NULLS FIRST is the default for descending order.
Analytic functions always operate on rows in the order specified in the order_by_clause of the function. However, the order_by_clause of the function does not guarantee the order of the result. Use the order_by_clause of the query to guarantee the final result ordering.
order_by_clause of SELECT for more information on this clause
Some analytic functions allow the windowing_clause . In the listing of analytic functions at the end of this section, the functions that allow the windowing_clause are followed by an asterisk (*).
ROWS | RANGE These keywords define for each row a window (a physical or logical set of rows) used for calculating the function result. The function is then applied to all the rows in the window. The window moves through the query result set or partition from top to bottom.
ROWS specifies the window in physical units (rows).
RANGE specifies the window as a logical offset.
You cannot specify this clause unless you have specified the order_by_clause . Some window boundaries defined by the RANGE clause let you specify only one expression in the order_by_clause . Please refer to "Restrictions on the ORDER BY Clause".
The value returned by an analytic function with a logical offset is always deterministic. However, the value returned by an analytic function with a physical offset may produce nondeterministic results unless the ordering expression results in a unique ordering. You may have to specify multiple columns in the order_by_clause to achieve this unique ordering.
BETWEEN . AND Use the BETWEEN . AND clause to specify a start point and end point for the window. The first expression (before AND ) defines the start point and the second expression (after AND ) defines the end point.
If you omit BETWEEN and specify only one end point, then Oracle considers it the start point, and the end point defaults to the current row.
UNBOUNDED PRECEDING Specify UNBOUNDED PRECEDING to indicate that the window starts at the first row of the partition. This is the start point specification and cannot be used as an end point specification.
UNBOUNDED FOLLOWING Specify UNBOUNDED FOLLOWING to indicate that the window ends at the last row of the partition. This is the end point specification and cannot be used as a start point specification.
CURRENT ROW As a start point, CURRENT ROW specifies that the window begins at the current row or value (depending on whether you have specified ROW or RANGE , respectively). In this case the end point cannot be value_expr PRECEDING .
As an end point, CURRENT ROW specifies that the window ends at the current row or value (depending on whether you have specified ROW or RANGE , respectively). In this case the start point cannot be value_expr FOLLOWING .
value_expr PRECEDING or value_expr FOLLOWING For RANGE or ROW :
If value_expr FOLLOWING is the start point, then the end point must be value_expr FOLLOWING .
If value_expr PRECEDING is the end point, then the start point must be value_expr PRECEDING .
If you are defining a logical window defined by an interval of time in numeric format, then you may need to use conversion functions.
NUMTOYMINTERVAL and NUMTODSINTERVAL for information on converting numeric times into intervals
If you specified ROWS :
value_expr is a physical offset. It must be a constant or expression and must evaluate to a positive numeric value.
If value_expr is part of the start point, then it must evaluate to a row before the end point.
If you specified RANGE :
value_expr is a logical offset. It must be a constant or expression that evaluates to a positive numeric value or an interval literal. Please refer to "Literals" for information on interval literals.
You can specify only one expression in the order_by_clause
If value_expr evaluates to a numeric value, then the ORDER BY expr must be a numeric or DATE datatype.
If value_expr evaluates to an interval value, then the ORDER BY expr must be a DATE datatype.
If you omit the windowing_clause entirely, then the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW .
Analytic functions are commonly used in data warehousing environments. In the list of analytic functions that follows, functions followed by an asterisk (*) allow the full syntax, including the windowing_clause .
Oracle Data Warehousing Guide for more information on these functions and for scenarios illustrating their use
Остерегайтесь неявных преобразований!
В разделе «Типы BINARY_FLOAT и BINARY_DOUBLE » этой главы я привел код ( binary_performance . sql ), использовавшийся для сравнения производительности BINARY_DOUBLE и NUMBER . В первой версии этого теста циклы для вычисления площади были запрограммированы следующим образом:
Я был потрясен, когда результаты вдруг показали, что вычисления с NUMBER выполняются намного быстрее вычислений с BINARY_DOUBLE . Это было совершенно непостижимо, потому что я «знал», что операции с BINARY_DOUBLE выполняются на аппаратном уровне, а следовательно, просто обязаны работать быстрее операций с NUMBER .
Потом кто-то из сотрудников Oracle Corporation указал мне на мою ошибку: цикл FOR (в приведенном виде) неявно объявляет переменную цикла PLS_INTEGER с именем bd . Область действия нового объявления bd перекрывает блок цикла и замещает мое объявление bd в формате BINARY_DOUBLE . Кроме того, я записал константу в формате 2 (вместо 2d), из-за чего она интерпретировалась как NUMBER . Таким образом, значение bd сначала неявно преобразовывалось в NUMBER , затем возводилось в квадрат, и полученное значение NUMBER неявно снова преобразовывалось в BINARY_DOUBLE для умножения на pi_bd . Неудивительно, что результаты были настолько плохи! Подобные ловушки характерны для неявных преобразований.
Какое значение будет содержать переменная a после выполнения этого кода? Это зависит от того, как PL/SQL вычисляет выражение в правой части оператора присваивания. Если он сначала преобразует строку в число, мы получим следующий результат:
С другой стороны, если PL/SQL сначала преобразует число в строку, то результат будет таким:
И какой из двух результатов будет получен? Вы знаете? Даже если знаете, вряд ли другие программисты сразу догадаются об этом, читая ваш код. В данном случае лучше записать преобразование в явном виде:
Кстати говоря, это выражение соответствует порядку обработки исходного выражения базой данных. Согласитесь, с явно выраженными преобразованиями его намного проще понять с первого взгляда.
Строчные функции конвертации типа данных разработаны для изменения типа данных столбца, выражения или литерала. Наиболее часто используемые функции конвертации это TO_CHAR, TO_NUMBER и TO_DATE. TO_CHAR преобразует числа и даты в символьные данные, когда TO_NUMBER и TO_DATE преобразует символьные данные соответственно в число и дату.
Функции конвертации
Oracle позволяет определять столбцы с типами данных ANSI, DB2 и SQL/DS. Эти типы преобразуется к типам данных Oracle. У каждого столбца определяется тип данных который ограничивает природу данных которые могут храниться в этом столбце. Столбец NUMBER не может хранить символьную информацию. Столбец DATE не может хранить случайные символы или числа. VARCHAR2 может хранить символьные эквиваленты чисел и дат.
Если функция у которой входной параметр является строкой получает на вход число, Oracle автоматически конвертирует его в символьный эквивалент. Если функция с входными параметрами типа данных число или дата получает на вход строку, то должны соблюдаться определённые условия для автоматической конвертации. Несмотря на то что автоматическая неявная конвертация доступна, гораздо более понятно и надёжно явно сконвертировать один тип данных в другой перед вызовом строчной функции.
Неявная конвертация типов
Значения, которые не соответствуют типам данных параметров функции неявно конвертируется перед выполнением если это возможно. Оба типа данных VARCHAR2 и CHAR используются как символьные типы данных. Символьные типы данных достаточно гибкие для хранения практически любой информации. Таким образом, ДАТА и ЧИСЛО можно легко преобразовать в их символьный эквивалент. Такая конвертация известна как преобразования число в строку и дата в строку. Рассмотрим следующие запросы
Query 1: select length(1234567890) from dual
Query 2: select length(SYSDATE) from dual
Оба запроса используют функцию LENGTH у которой входной параметр определён как строка. Число 1234567890 в запросе один неявно конвертируется в строку ‘1234567890’ перед вычисление функции LENGTH и результат функции будет 10. Запрос номер два вычисляет функцию SYSDATE предположим 7 апреля 2008 года. Результат преобразуется в строку ’07-APR-08’ и результат выполнения функции LENGTH будет число 9.
Обычно не принято допускать неявную конвертацию строк в числа, так как единственная ситуация, когда это возможно, это если строка представляет собой валидное число. Строка ‘11’ будет неявно преобразована в число 11, но строка ’11.123.345’ не будет, как показано в следующих примерах
Query 3: select mod(’11’, 2) from dual
Query 4: select mod(‘11.123’, 2) from dual
Query 5: select mod(‘11.123.456’, 2) from dual
Query 6: select mod(‘$11’, 2) from dual
Запросы 3 и 4 неявно преобразовали строки ‘11’ и ’11.123’ в числа 11 и 11.123 соответственно, перед вызовом функции MOD которая в свою очередь вернула результат 1 и 1.123. Запрос 5 вернул ошибку ‘ORA-1722: invalid number’, когда Oracle попытался неявно преобразовать строку в число, так как ‘11.123.456’ не является корректным числом. Запрос 6 также вернул ошибку так как символ доллара не может бять неявно преобразован в число.
Неявная конвертация строки в дату возможна, когда строка удовлетворяет следующим шаблонам: [D|DD] separator1 [MON|MONTH] separator2][R|RR|YY|YYYY], где D и DD это день MON первые три буквы месяца, MONTH – полное название месяца. R и RR YY и YYYY отображают одну, две и четыре цифры года соответственно. Параметром separator1 и separator2 может быть практически любой спец символ, включая сюда пробел, табуляцию, знаки пунктуации и т.д. Таблица 10-2 показывает неявную конвертацию строки в дату, включая вызов функций работы с датами и результаты. Эти результаты предполагают, что система использует американскую локаль.
TIP Несмотря на то что неявная конвертация типов доступна, лучше использовать явный вызов функции конвертации перед использование данным в других функциях. Конвертация строки в число и дату использует маску форматирования.
Явная конвертация типов данных
Функции, которые конвертирует значение из одного типа данных в другой известны как явное преобразование типов. Они возвращают значение, которое будет гарантировано нужного типа данных и являются надёжным методом конвертации данных.
Число и дату можно явно конвертировать в строку используя функцию TO_CHAR. Строку можно явно конвертировать в число используя функцию TO_NUMBER. Функция TO_DATE используется для конвертации строку в DATE. Маски форматирования Oracle позволяют гибко контролировать процесс конвертации строки в число или дату.
Понимание широко используемых масок форматирования проверяется достаточно просто вопросами вида: какой результат вернёт вызов функций TO_CHAR(TO_DATE(’01-JAN-00′,’DD-MON-RR’),’Day’).
Использование функций TO_CHAR, TO_NUMBER и TO_DATE
Использование функций конвертации
Во многих ситуациях возникает потребность в использовании функций конвертации, начиная от форматирования даты в отчётах и до проверки численных значений в символьных столбцах перед стартом арифметических вычислений.
Таблица 10-3 показывает синтаксис строчных функций конвертации данных
Необязательный параметр поддержки национального формата (nls_parameters) полезен для указания языка и форматирования, в котором названия дней, месяцев и разделители разрядов, целой и дробной части заранее предопределены. На рисунке 10-2 отображено представление NLS_SESSION_PARAMETERS которое содержит значения параментов NLS для текущей сессии. По умолчанию значение NLS_CURRENCY – знак доллара, но это можно изменить на уровне сессии. Например, для изменения символа валюты на строку ‘GBP’ можно выполнить запрос
ALTER SESSION SET NLS_CURRENCY=’GBP’
Рисунок 10-2 – Представление NLS_SESSION_PARAMETERS
Конвертация числа в строку используя функцию TO_CHAR
Функция TO_CHAR возвращает значение типа VARCHAR2. Когда входных параметром является число то доступны некоторые параметры форматирования. Синтаксис команды TO_CHAR(num, [format], [nls_parameter]). Параметр num обязательный и должен быть числом. Необязательный параметр format можно использовать для указания информации о форматировании, такой как длина, символ валюты, позиция разделителя дробной и целой части и разделитель разрядов (три разряда) и должен быть заключен в одинарные кавычки. Доступны различные опции форматирования и часть из них представлена в таблице 10-4. Рассмотрим два запроса
Query 1: select to_char(00001)||’ is a special number’ from dual;
Query 2: select to_char(00001, ‘0999999’)||’ is a special number’ from dual;
В запросе номер один у числа 00001 убираются ведущие нули, значение преобразуется в строку ‘1’ и возвращается результат ‘1 is a special number’. Зпрос номер два использует маску форматирования ‘0999999’ для числа 00001 преобразуя число в строку ‘0000001’ и возвращая результат ‘0000001 is a special number’. 0 и 6 девяток в маске форматирования указывают функции TO_CHAR что необходимо использовать ведущие нули, и длина строки должна быть 7. Таким образом строка, возвращаемая функцией TO_CHAR содержит семь символов.
Tip Конвертация чисел в строки надёжный способ убедиться что функция и SQL запрос в целом, который ожидает символьного значения, не вернёт ошибку когда встретится число. Конвертация чисел в строки часто используется для форматирования значений для отчетов. Маска форматирования поддерживает символ валюты, разделитель порядков и разделитель целой и дробной части, что часто используется при отображении финансовой информации.
Конвертация даты в строку используя функцию TO_CHAR
Вы можете использовать преимущества модели масок форматирования при конвертации ДАТЫ в практически любой вариант отображения даты как символьного значения используя функцию TO_CHAR. Синтаксис функции TO_CHAR(date1, [format], [nls_parameter]).
Только параметр date1 обязательный; тогда он должен быть значением, которое может неявно преобразоваться в строку. Необязательный параметр format регистрозависимый и должен быть обрамлён одинарными кавычками. Маска форматирования указывает какие лементы даты должы быть выбраны и как отображать названия элементов даты: полные названия или аббревиатуры. Названия дней и месяцев автоматически разделяются пробелом. Такое поведение можно изменить, используя параметр маски fill mode (fm). Указав в начале маски параметр fm, вы укажете Oracle о необходимости убрать все пробелы. Доступно много опций для маски форматирования, часть из которых отображена в таблице 10-5. Рассмотрим три запроса
Query 1: select to_char(sysdate)||’ is today»s date’ from dual;
Query 2: select to_char(sysdate,’Month’)||’is a special time’ from dual;
Query 3: select to_char(sysdate,’fmMonth’)||’is a special time’ from dual;
Если текущая системная дата 3 января 2009 года и по умолчанию формат отображения DD/MON/RR тогда запрос один вернёт строку ‘03/JAN/09 is todays date’. Во втором запросе обратите внимание на две детали: во-первых, только месяц выбирается из даты, и во-вторых так как маска форматирования регистрозавсимая и в запросе используется ‘Month’, то запрос вернёт ‘January is a special time’. Нет нужды добавлять пробел в начале литерала, так как функция TO_CHAR автоматически добавит пробел к названию месяца. Если бы маска во втором запросе была ‘MONTH’ то запрос вернул бы ‘JANUARY is a special time’. Параметр fm в третьем запросе препятствует добавлению пробелов и результатом будет ‘Januaryis a special time’. В таблице 10-5 предполагается что обрабатывается дата 2 июня 1975 года и текущий год 2009.
Параметры форматирования, связанные с неделей, кварталом, веком и другими более редко использующимися элементами даты показаны на рисунке 10-7. Столбец результата предполагает, что функция использовалась для работы с датой 24 сентября 1000 года, с маской форматирования указанной в столбец format element.
Компонент время в типе данных дата выбирается, используя модели форматирования в таблице 10-7. Результат рассчитывается функцией TO_CHAR используя дату 27 июня 2010 года время 21:35:13 с маской форматирования указанной в столбце format element.
Некоторые различные элементы, которые можно использовать в форматировании даты и времени перечислены в таблице 10-8. Знаки пунктуации используются для разделения элементов форматирования. Три типа суффиксов существуют для форматирования элементов. Более того, символьные литералы могут быть включены в модель форматирования если они заключены в двойные кавычки. Результаты в таблице 10-8 получены используя функцию TO_CHAR для даты 12 сентября 2008 года 14:31 с маской форматирования указанной в соответствующем столбце.
Таблица JOB_HISTORY хранит информацию о должностях сотрудника, которые он занимал в компании. Запрос на рисунке 10-3 выбирает информацию из этой таблицы о конце срока действия должности сотрудника для каждого сотрудинка на основании столбцов END_DATE, EMPLOYEE_ID и JOB_ID и форматирует результат в красивое предложение. Символьное выражение объединяется с функцией TO_CHAR в которой используется маска ‘fmDay «the «ddth «of» Month YYYY’. Параметр fm используется чтобы убрать пробелы в имени дня недели и месяца. Два литерата ограниченые двойными кавычками используются для добавления читабельности результату. Литерал “th” применяется к элементу даты чтобы сделать литературно правильным отображение даты как 17 th или 31th. ‘Month’ в маске означает использовать полное имя месяца и наконец YYYY форматирует год как все четыре символа года.
Рисунок 10-3 – Запрос в таблицу JOB_HISTORY
select ‘Employee ‘||EMPLOYEE_ID||’ quit as ‘||JOB_ID||’ on ‘||TO_CHAR(END_DATE,’fmDay «the «ddth «of» Month YYYY’) «Quitting Date»
ORDER BY END_DATE;
Несмотря на то что компонент «век» не отображается по умолчанию, он хранится в базе данных и доступен для запроса. Маска форматирования DD-MON-RR используется по умолчанию для ввода значений и отображения. Когда значение добавляется или изменяется если явно неуказан век, то используется век из функции SYSDATE. Формат RR отличается от формата YY и так как RR также использует значение столетия. Влияние значения столетия на формат RR легче понять если рассмотреть следующие принципы
- Если две последние цифры текущего года между 0 и 49, а в указанном значении даты две последние цифры года между 50 и 99 то используется предыдущий век. Предположим, что текущая дата 2 июня 2007 года. Значение века для даты 24-JUNE-94 года будет 20
- Если две цифры текущего года между 50-99 и указанной даты также между 55 и 99, то возвращается текущий век. Преположим что текущая дата 2 июня 1975 года. Тогда значение века для 24-JUL-94 будет 20.
- Если две цифры текущей даты между 50 и 99, а в укащанной дате год между 0 и 49 – то считается следующий век. Предположим, что текущая дата 2 июня 1975 года, тогда для значения 24-JUL-07 значение века будет 21.
Конвертация строки в дату используя функцию TO_DATE
Функция TO_DATE возвращает значение типа данных DATE. Строка, конвертируемая в дату может содержать все или часть компонентов, составляющих тип DATE. Когда строка содержащая только часть компонентов даты преобразуется в дату, Oracle использует значение по умолчанию для составления валидного значения типа DATE. Части строки сопоставляются с элементами даты используя маску (или модель) форматирования. Синтаксис функции TO_DATE(string1, [format], [nls_parameter]).
Только параметра string1 обязателен, и eсли маска форматирования не указана, string1 должна быть в формат неявно конвертируемом в дату. Необязательный параметр format используется практически всегда и должен быть заключён в одинарные кавычки. Маска форматирования идентича перечисленным в таблицах 10-5, 10-6, и 10-7. У функции TO_DATE есть модификатор fx, которые используется подобно параметру fm функции TO_CHAR. Параметр fx требует обязательного совпадения строки и маски форматирования. Если строка не совпадает с маской – возвращается ошибка. Рассмотрим несколько примеров
Query 1: select to_date(’25-DEC-2010′) from dual;
Query 2: select to_date(’25-DEC’) from dual;
Query 3: select to_date(’25-DEC’, ‘DD-MON’) from dual;
Query 4: select to_date(’25-DEC-2010 18:03:45′, ‘DD-MON-YYYY HH24:MI:SS’) from dual;
Query 5: select to_date(’25-DEC-10′, ‘fxDD-MON-YYYY’) from dual;
Запрос 1 преобразует строку ’25-DEC-2010’ и строка может быть преобразована в дату используя маску DD-MON-YYYY. Дефис можно заменить на другой разделитель. Так как не было указано никакой информации о времени то используется значение по-умолчанию 00:00:00. Запрос 2 не может неявно преобразовать строку в дату, так как недостаточно информации и возвращается ошибка ORA-01840: input value is not long enough for date format. Указав маску DD-MON для строки ’25-DEC’ в запросе номер три, Oracle может преобразовать значение в дату, но так как год и время не указаны, в значении года будет использоваться значение года функции SYSDATE, а время установлено в полночь. Если текущий год будет 2009, то запрос 3 вернёт дату 25/DEC/09 00:00:00. Запрос 4 преобразует строку с явно указанными всеми компнонентами даты и времени. Запрос 5 использует параметра fx. Так как год в строке указан двумя символами, а требуемый формат предполагает четыре цифры года, то запрос возвращает ошибку ORA-01862: the numeric value does not match the length of the format item.
Конвертация строки в число используя функцию TO_NUMBER
Функция TO_NUMBER возвращает значение типа данных NUMBER. Исходная строка должна быть составлена таким образом, чтобы все несовместимые символы отсутствовали или были указаны в соответствующей маске форматирования. Синтаксис функции TO_NUMBER(string1, [format], [nls_parameter]). Только string1 является обязательным параметром, и если не указан параметр format то значение должно быть таким, чтобы была возможность неявно сконвертировать его в число. Маски форматирования идентичны перечисленным в таблице 10-4. Рассмотрим запросы
Query 1: select to_number(‘$1,000.55’) from dual;
Query 2: select to_number(‘$1,000.55′,’$999,999.99’) from dual;
Запрос один не может неявно преобразовать строку, так как она содержит знак валюты и разделители, которые явно неуказаны в маске, поэтому возвращается ошибка ORA-1722: invalid number. Запрос два находит символ валюты, запятой и точки в маске форматирования и несмотря на то что длина маски больше чем чем исходное значение в строке, возвращается число 1000.55
Функция TO_NUMBER конвертирует значение строки в число. Если вы используете число длиннее чем маска, возвращается ошибка. Если вы конвертируете число используя более длинную маску – возвращается значение исходной длины. Не путайте TO_NUMBER с TO_CHAR. Например TO_NUMBER(‘123.45’,’999.9’) вернёт ошибку, когда TO_CHAR(123.45,’999.9’) вернёт 123.6
Character Functions Returning Number Values
Character functions that return number values can take as their argument any character datatype.
Тригонометрические функции
В PL/SQL поддерживаются все основные тригонометрические функции. При их использовании следует помнить, что углы задаются в радианах, а не в градусах. Преобразование радианов в градусы и наоборот выполняется по следующим формулам:
В PL/SQL нет отдельной функции для получения числа π, однако его можно получить косвенным методом:
Арккосинус числа –1 равен значению π. Конечно, поскольку это число представляет собой бесконечную десятичную дробь, вы всегда будете работать с его приближенным значением. Для получения нужной точности можно округлить результат вызова ACOS (-1) до нужного количества позиций функцией ROUND .
Функция CAST
Функция CAST применяется для преобразования чисел в строки, и наоборот. Синтаксис функции выглядит так:
В следующем примере функция CAST сначала используется для преобразования числа типа NUMBER в строку VARCHAR2 , а затем символы этой строки преобразуются в соответствующее числовое значение:
У функции CAST есть один недостаток: она не поддерживает маски форматирования чисел. С другой стороны, эта функция является частью стандарта ISO SQL — в отличие от функций TO_CHAR и TO_NUMBER . Если разработчику важно, чтобы программный код был полностью совместим со стандартом ANSI , используйте для преобразования чисел в строковые значения функцию CAST . В других случаях мы рекомендуем применять функции TO_CHAR и TO_NUMBER .
Поскольку PL/SQL не соответствует стандарту ISO , написать на этом языке полностью совместимый с указанным стандартом код невозможно. Таким образом, функция CAST в программах на PL/SQL становится лишней. Она востребована только в SQL-командах ( SELECT, INSERT и т. д.), если они должны быть совместимы со стандартом ANSI .
Character Functions Returning Character Values
Character functions that return character values return values of the following datatypes unless otherwise documented:
If the input argument is CHAR or VARCHAR2 , then the value returned is VARCHAR2 .
If the input argument is NCHAR or NVARCHAR2 , then the value returned is NVARCHAR2 .
The length of the value returned by the function is limited by the maximum length of the datatype returned.
For functions that return CHAR or VARCHAR2 , if the length of the return value exceeds the limit, then Oracle Database truncates it and returns the result without an error message.
For functions that return CLOB values, if the length of the return values exceeds the limit, then Oracle raises an error and returns no data.
Числовые функции
В PL/SQL реализовано множество функций для работы с числами. Мы уже рассматривали функции преобразования TO_CHAR , TO_NUMBER, TO_BINARY_FLOAT и TO_BINARY_DOUBLE . В нескольких ближайших разделах приведены краткие описания важнейших функций. За подробными описаниями конкретных функций обращайтесь к справочнику Oracle SQL Reference.
Environment and Identifier Functions
Model Functions
В посте рассматриваются функции ROUND, TRUNC и MOD относящиеся к функциям по работе с числовыми данными.
Существенным отличием между числовыми и другими функциями является то, что они принимают и возвращают только числовые данные. Oracle предоставляет числовые функции для решения тригонометрических, степенных и логарифмических задач. Ниже рассматриваются три однострочные числовые функции – ROUND, TRUNC и MOD:
Функция ROUND выполняет операцию округления числового значения на основе указанной десятичной точности. Возвращаемое значение округляется в большую или меньшую сторону в зависимости от числового значения значащей цифры в позиции, задаваемой десятичной точностью. Если заданная десятичная точность равна n, то значимая для округления позиция является: (n + 1)-я относительно десятичной запятой (точки) вправо для n>=0 и (0-n)-я относительно десятичной запятой (точки) влево для n
ROUND (числовое значение, (десятичная точность)),
Описания десятичной точности
Десятичная точность | Десятичная позиция |
-4 | десятки тысяч (n*10000) |
-3 | тысячи (n * 1000) |
-2 | сотни (n * 100) |
-1 | десятки (n * 10) |
0 | единицы (n * 1) |
1 | десятых (n ÷ 10) |
2 | сотых (n ÷ 100) |
3 | тысячных (n ÷ 1000) |
4 | десятитысячных (n ÷ 10000) |
Примеры использования функции ROUND:
Если для примера выше опустить значение десятичной точности 0, то получается идентичный результат. При отсутствии десятичной точности, функция ROUND обрабатывает целую часть числа, как это было бы с десятичной точностью равной 0:
Функция TRUNC выполняет операцию усечения числового значения на основе указанной десятичной точности. Числовое усечение отличается от округления тем, что результирующее значение отбрасывает числовые знаки с позиции (n+1)-й вправо от десятичной запятой (точки) указанной десятичной точности n, если десятичная точность положительна или равна нулю. Если заданная десятичная точность n отрицательна, в исходном числе проставляются значащие нули до (0-n)-й десятичной позиции включительно влево от десятичной запятой (точки). Синтаксис функции:
TRUNC (числовое значение, (десятичная точность)).
Примеры использования функции TRUNC:
При отсутствии десятичной точности, функция TRUNC обрабатывает целую часть числа, как это было бы с десятичной точностью равной нулю:
Функция MOD возвращает числовой остаток от операции деления. Функция MOD выполняет операцию модульного деления. Ее синтаксис:
PL/SQL реализует несколько операторов, используемых при работе с числами. Эти операторы перечислены в табл. 1 в порядке возрастания приоритетов. Операторы с более низким приоритетом выполняются до операторов с более высоким приоритетом.
Пробелы при преобразовании чисел в символьные строки
В ходе преобразования числа в символьную строку функция TO_CHAR всегда оставляет место для знака «-», даже если число положительное.
Обратите внимание: каждая строка имеет длину семь символов, хотя для положительного числа достаточно и шести. Начальный пробел применяется для выравнивания чисел в столбцах. Но если по какой-то причине вам требуется вывести компактные числа без пробелов, это создаст проблемы.
Если для представления отрицательных чисел используются угловые скобки (в маске задан элемент PR ), положительные числа дополняются одним начальным и одним завершающим пробелом.
Если числа, преобразованные в символьные данные, не должны содержать ни начальных, ни завершающих пробелов, существует несколько решений. Одно из них основано на использовании элемента форматирования TM , определяющего «минимальное» представление числа:
Этот метод удобен, но не позволяет задавать другие элементы форматирования. Так, нельзя задать формат TM999.99, чтобы число выводилось с двумя цифрами в дробной части. Если вам нужны другие элементы форматирования или если элемент TM не поддерживается в вашей версии PL/SQL, можно воспользоваться усечением результата:
Функция LTRIM была использована для удаления начальных пробелов и сохранения двух фиксированных цифр справа от десятичной запятой. Если же знак выводится справа от числа (например, с использованием элемента форматирования MI ), можно воспользоваться функцией RTRIM . Если же при выводе используются элементы, влияющие на вывод с обеих сторон числа (например, PR ), используется функция TRIM .
Hierarchical Function
The hierarchical function applies hierarchical path information to a result set.
Encoding and Decoding Functions
The encoding and decoding functions let you inspect and decode data in the database.
Collection Functions
Numeric Functions
Aggregate Functions
Aggregate functions return a single result row based on groups of rows, rather than on single rows. Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list can be aggregate functions, GROUP BY expressions, constants, or expressions involving one of these. Oracle applies the aggregate functions to each group of rows and returns a single result row for each group.
If you omit the GROUP BY clause, then Oracle applies aggregate functions in the select list to all the rows in the queried table or view. You use aggregate functions in the HAVING clause to eliminate groups from the output based on the results of the aggregate functions, rather than on the values of the individual rows of the queried table or view.
"Using the GROUP BY Clause: Examples" and the "HAVING Clause" for more information on the GROUP BY clause and HAVING clauses in queries and subqueries
Many (but not all) aggregate functions that take a single argument accept these clauses:
DISTINCT causes an aggregate function to consider only distinct values of the argument expression.
ALL causes an aggregate function to consider all values, including all duplicates.
For example, the DISTINCT average of 1, 1, 1, and 3 is 2. The ALL average is 1.5. If you specify neither, then the default is ALL .
All aggregate functions except COUNT (*) and GROUPING ignore nulls. You can use the NVL function in the argument to an aggregate function to substitute a value for a null. COUNT never returns null, but returns either a number or zero. For all the remaining aggregate functions, if the data set contains no rows, or contains only rows with nulls as arguments to the aggregate function, then the function returns null.
The aggregate functions MIN , MAX , SUM , AVG , COUNT , VARIANCE , and STDDEV , when followed by the KEEP keyword, can be used in conjunction with the FIRST or LAST function to operate on a set of values from a set of rows that rank as the FIRST or LAST with respect to a given sorting specification. Please refer to FIRST for more information.
You can nest aggregate functions. For example, the following example calculates the average of the maximum salaries of all the departments in the sample schema hr :
This calculation evaluates the inner aggregate ( MAX ( salary )) for each group defined by the GROUP BY clause ( department_id ), and aggregates the results again.
Data Mining Functions
NLS Character Functions
Функции округления и усечения
Существуют четыре числовые функции, выполняющие округление и усечение числовых значений: CEIL, FLOOR, ROUND и TRUNC . Выбор нужной функции для конкретной ситуации может вызвать затруднения, поэтому в табл. 1 приводятся их сравнительные описания, а на рис. 5 представлены результаты вызова всех четырех функций с разными значениями.
Функция | Описание |
CEIL | Возвращает наименьшее целое число, большее либо равное заданному значению |
FLOOR | Возвращает наибольшее целое число, меньшее либо равное заданному значению |
ROUND | Выполняет округление числа. Положительное значение параметра определяет способ округления цифр, находящихся справа от запятой, а отрицательное — находящихся слева |
TRUNC | Усекает число до заданного количества десятичных знаков, отбрасывая все цифры, находящиеся справа |
Рис. 1. Функции округления и усечения
Large Object Functions
Читайте также: