Подробное объяснение однострочных и групповых функций PL/SQL. Функция — это программа, которая имеет ноль или более параметров и возвращаемое значение. Oracle имеет ряд встроенных функций SQL.
Эти функции можно назвать операторами SQL или PL/SQL. Функции в основном делятся на две категории:
Функции группы однострочных функций. В этой статье обсуждается, как использовать однострочные функции и правила их использования.
Функция одной строки в SQL
SQL и PL/SQL содержат множество типов функций, включая символьные, числовые, функции даты, преобразования и смешанные функции для обработки отдельных строк данных.
Поэтому их можно вместе назвать однострочными функциями. Эти функции можно использовать в SELECT, WHERE, ORDER BY и других предложениях.
Например, следующий пример содержит однострочные функции, такие как TO_CHAR, UPPER и SOUNDEX.
SELECT ename,TO_CHAR(дата найма,'день,ДД-Пн-ГГГГ')
ИЗ импер.
Где UPPER(ename) Как 'AL%'ORDER BY SOUNDEX(ename)
Однострочные функции также можно использовать в других операторах, таких как предложение SET обновления, предложение VALUES в INSERT и предложение WHERE в DELET.
На сертификационном экзамене особое внимание уделяется использованию этих функций в операторе SELECT, поэтому наше внимание также сосредоточено на операторе SELECT.
NULL и однострочные функции Поначалу понимание NULL может быть трудным, и даже очень опытный человек может сбить его с толку.
Значение NULL представляет собой неизвестные данные или значение NULL. Любой операнд арифметического оператора имеет значение NULL, а результат — значение NULL.
Это правило также применимо ко многим функциям. Только CONCAT, DECODE, DUMP, NVL и REPLACE могут возвращать значения, отличные от NULL, при вызове с параметрами NULL.
Среди них функция NVL является наиболее важной, поскольку она может напрямую обрабатывать значения NULL.
NVL имеет два параметра: NVL(x1,x2), и x1, и x2 являются выражениями. Когда x1 имеет значение null, возвращается X2, в противном случае возвращается x1.
Давайте посмотрим на таблицу данных emp. Она содержит зарплату и бонус. Нам нужно рассчитать общую компенсацию.
имя столбца
emp_id тип ключа бонуса к зарплате
pk nulls/unique nn,u nnfk таблица тип данных номер номер длина номера 11.2 11.2
Вместо простого сложения зарплаты и бонуса, если определенная строка имеет нулевое значение, результат будет нулевым, как в следующем примере:
обновить emset оклад=(оклад+бонус)*1,1
В этом заявлении зарплата и бонус сотрудника будут обновлены до нового значения, но если бонуса нет,
То есть зарплата + ноль, тогда будет сделан неправильный вывод. В это время необходимо использовать функцию nvl, чтобы исключить влияние нулевых значений.
Итак, правильное утверждение:
обновить empset зарплату=(зарплата+nvl(бонус,0)*1.1
Однострочные строковые функции. Однострочные строковые функции используются для управления строковыми данными. Большинство из них имеют один или несколько параметров и большинство из них возвращают строки.
ASCII()
c1 — строка, возвращает код ASCII первой буквы c1, а ее обратная функция — CHR().
ВЫБЕРИТЕ ASCII('A') BIG_A,ASCII('z') BIG_z
ОТ empBIG_A BIG_z65 122
CHR(<i>)[NCHAR_CS]
я — число, и функция возвращает десятичное представление символа.
выберите CHR(65),CHR(122),CHR(223)
ОТ empCHR65 CHR122 CHR223A z B
КОНКАТ(,)
И c1, и c2 являются строками. Функция соединяет c2 с задней частью c1. Если c1 равно нулю, будет возвращено c2.
Если оба значения c1 и c2 равны нулю, верните значение null. Он возвращает тот же результат, что и оператор ||
выберите имя пользователя concat('slobo','Svoboda')
от двойного имени пользователя Слобо Сёбода
ИНИТКАП()
c1 — строка. Функция возвращает первую букву каждого слова в верхнем регистре, а остальные буквы в нижнем регистре. Слова состоят из пробелов, управляющих символов,
Ограничения по пунктуации.
выберите INITCAP('veni,vedi,vici') Цезарь
от двойногоЦезарьВени,Веди,Вичи
ИНСТР(,[,＜i＞[,]])
c1 и c2 — строки, i и j — целые числа. Функция возвращает позицию j-го вхождения слова c2 в c1, и поиск начинается с i-го символа c1.
Если нужный символ не найден, возвращается 0. Если i — отрицательное число, поиск будет выполняться справа налево, но позиция по-прежнему вычисляется слева направо.
Значение по умолчанию для i и j равно 1.
SELECT INSTR('Миссисипи','i',3,3)
ИЗ двойного INSTR('МИССИСИПИ','I',3,3)
11
выберите INSTR('Миссисипи','i',-2,3)
из двойного INSTR('МИССИСИПИ','I',3,3)
2
ИНСТРБ(,[,i[,j])
То же, что и функция INSTR(), за исключением того, что она возвращает байты. Для однобайтовой функции INSTRB() равна INSTR().
ДЛИНА()
c1 — это строка, и возвращается длина c1. Если c1 имеет значение null, будет возвращено нулевое значение.
выберите ДЛИНУ('Ipso Facto'), следовательно
от дуалэрго10
ДЛИНАb()
Как и LENGTH(), возвращает байты.
ниже()
Возвращает символ c в нижнем регистре, который часто встречается в подстрокеwhere.
выберите НИЖНИЙ (название цвета)
из детали товара
ГДЕ НИЖНИЙ (имя цвета), КАК '%white%'COLORNAMEWinterwhite
ЛПАД(,＜i＞[,])
c1 и c2 — строки, а i — целое число. Используйте строку c2, чтобы дополнить длину i в левой части c1, которая может повторяться несколько раз. Если i меньше длины c1,
Тогда будут возвращены только символы c1, пока я, а остальные будут усечены. Значение c2 по умолчанию — один пробел, см. RPAD.
выберите LPAD(ответ,7,'') дополненный,ответ без дополнений
из вопроса;
ПРОКЛАДКА БЕЗ ПОДКЛАДКИ Да ДаНЕТ НЕТМожет быть, возможно
ЛТРИМ(,)
Удалите самый левый символ в c1, чтобы первого символа не было в c2. Если c2 нет, то c1 не изменится.
выберите LTRIM('Миссисипи','Mis') из DualLTRppi
РПАД(,＜i＞[,])
Используйте строку c2 для дополнения длины i в правой части c1, которая может повторяться несколько раз. Если i меньше длины c1, то будут возвращены только символы c1 до тех пор, пока я.
Остальные будут сокращены. Значением по умолчанию c2 является один пробел, остальные аналогичны LPAD.
РТРИМ(,)
Удалите самый правый символ в с1, чтобы последнего символа не было в с2. Если с2 нет, то с1 не изменится.
ЗАМЕНЯТЬ(,[,])
c1, c2 и c3 — это строки. Функция заменяет c2, который появляется в c1, на c3 и возвращает значение.
выберите REPLACE('uptown','up','down')
из двойногоREPLACEцентр города
СТБСТР(,＜i＞[,])
c1 — строка, i и j — целые числа, а подстрока длины j возвращается, начиная с i-й позиции c1 или до конца строки, если j пусто.
выберите SUBSTR('Сообщение',1,4)
из дуалСУБСМесс
SUBSTRB(,<i>[,])
Это примерно то же самое, что и SUBSTR, за исключением того, что I и J вычисляются в байтах.
ЗВУКОВАЯ ДЕЯТЕЛЬНОСТЬ()
Возвращает слова, которые звучат похоже на c1
select SOUNDEX('dawes') Dawes SOUNDEX('dawes') Daws, SOUNDEX('dawson')
от дуалаDawes Daws DawsonD200 D200 D250
ПЕРЕВОДИТЬ(,,)
Замените те же символы в c1, что и в c2, на c3.
выберите тест TRANSLATE('fumble','uf','ar') из DualTEXTramble
ОБРЕЗАТЬ([[]] из c3)
Удалите первый, последний или оба значения в строке c3.
выберите TRIM('пробел') обрезать из двойного TRIMspace дополненного
ВЕРХНИЙ()
Возвращает версию c1 в верхнем регистре, которая часто встречается в подстрокеwhere.
выберите имя из двойного числа, где UPPER (имя) LIKE 'KI%'NAMEKING
Однострочные числовые функции Однострочные числовые функции оперируют числовыми данными, выполняя математические и арифметические операции. Все функции принимают числовые параметры и возвращают числовые значения.
Операндами и значениями всех тригонометрических функций являются радианы, а не углы. Oracle не предоставляет встроенной функции преобразования радианов и углов.
АБС()
Возвращает абсолютное значение n
АКОС()
Функция обратного кофактора возвращает число от -1 до 1. n представляет радианы
выберите ACOS(-1) pi,ACOS(1) НОЛЬ
ИЗ DualPI НОЛЬ3.14159265 0
АСИН()
В любом случае, загадочная функция возвращает от -1 до 1, n представляет собой радианы.
АТАН()
Функция Arctangent возвращает значение арктангенса n, где n представляет радианы.
ЦЕИЛ()
Возвращает наименьшее целое число, большее или равное n.
COS()
Возвращает совместное значение n, где n — радианы.
КОСХ()
Возвращает гиперболический сомножитель n, где n — число.
выберите COSH(<1.4>)
ОТ DualCOSH(1.4)2.15089847
ЭКСП()
Возвращает n-ную степень числа e, e=2,71828183.
ПОЛ()
Возвращает наибольшее целое число, меньшее или равное N.
ЛН()
Возвращает натуральный логарифм числа N, который должен быть больше 0.
БРЕВНО(,)
Возвращает логарифм числа n1 по основанию n2.
МОД()
Возвращает остаток от деления n1 на n2,
ВЛАСТЬ(,)
Возвращает n1, возведенный в степень n2.
КРУГЛЫЙ(,)
Возвращает значение n1, округленное до n2 знаков справа от десятичной точки. Значение n2 по умолчанию равно 0. На этот раз округляется ближайшее целое число до десятичной точки.
Если n2 — отрицательное число, оно округляется до соответствующей цифры слева от десятичной точки. n2 должно быть целым числом.
выберите КРУГЛЫЙ(12345,-2),КРУГЛЫЙ(12345.54321,2)
FROM DualROUND(12345,-2) ROUND(12345.54321,2)12300 12345.54
ЗНАК()
Если n — отрицательное число, возвращается -1, если n — положительное число, возвращается 1, а если n=0, возвращается 0.
ГРЕХ()
Возвращает положительное значение n, где n — радианы.
СИНХ()
Возвращает гиперболическое положительное значение n, где n — радианы.
КОРЕНЬ()
Возвращает квадратный корень из n, где n — радианы.
ТАН()
Возвращает тангенс n, где n — радианы.
ТАНХ()
Возвращает гиперболический тангенс n, где n — радианы.
ТРАНК(,)
Возвращает значение n1, усеченное до n2 десятичных знаков. Значение по умолчанию для n2 — 0. Если значением по умолчанию является n2, n1 будет усечено до целого числа.
Если n2 — отрицательное значение, оно усекается в соответствующей позиции слева от десятичной точки.
Функция даты одной строки
Однострочная функция даты работает с типом данных DATA, и большинство из них имеют параметры типа данных DATA.
Большинство возвращаемых значений также являются значениями типа данных DATA.
ADD_MONTHS(,＜i＞)
Возвращает результат даты d плюс i месяцев. я могу быть любым целым числом. Если я десятичное число,
Затем база данных неявно преобразует его в целое число и отсекает часть после десятичной точки.
ПОСЛЕДНИЙ_ДЕНЬ()
Функция возвращает последний день месяца, содержащего дату d.
МЕСЯЦ_МЕЖДУ(,)
Возвращает количество месяцев между d1 и d2, если даты d1 и d2 совпадают или обе являются последним днем месяца.
Тогда будет возвращено целое число, иначе возвращаемый результат будет содержать дробь.
НОВОЕ_ВРЕМЯ(,,)
d1 — тип данных даты. Если дата и время в часовом поясе tz1 равны d, возвращаются дата и время в часовом поясе tz2.
tz1 и tz2 — строки.
СЛЕДУЮЩИЙ_ДЕНЬ(,)
Возвращает первый день после даты d, заданной параметром dow, который определяет день недели на языке, заданном в текущем сеансе.
Возвращаемый компонент времени такой же, как компонент времени d.
выберите NEXT_DAY('01-январь-2000','Понедельник') "1-й понедельник",
NEXT_DAY('01 ноября 2004','вторник')+7 "2-й вторник")
из двойного;
1-й понедельник 2-й вторник 3 января 2000 г. 9 ноября 2004 г.
КРУГЛЫЙ([,])
Округление даты d в соответствии с форматом, заданным fmt, который представляет собой строку.
СЬЯДАТЕ
Функция не принимает параметров и возвращает текущую дату и время.
ТРАНК([,])
Возвращает дату d в единицах, указанных в fmt.
Функция преобразования одной строки Функция преобразования одной строки используется для работы с несколькими типами данных и преобразования между типами данных.
ЧАРТОРВИД()
c создает строку, а функция преобразует c в тип данных RWID.
ВЫБЕРИТЕ test_id
из test_case
где rowid=CHARTORWID('AAAA0SAACAAAALiAAA')
ПРЕОБРАЗОВАТЬ(,[,])
c хвостовая строка, dset и sset — это два набора символов. Функция преобразует строку c из набора символов sset в набор символов dset.
По умолчанию для sset используется набор символов базы данных.
ГЕКСТОРАВ()
x — шестнадцатеричная строка, и функция преобразует шестнадцатеричный x в тип данных RAW.
СЫРТОШЕСТИГРЯД()
x — это строка типа данных RAW, и функция преобразует тип данных RAW в шестнадцатеричный тип данных.
РОВИДТОЧАР()
Функция преобразует тип данных ROWID в тип данных CHAR.
TO_CHAR([[,)
x — это тип данных данных или число. Функция преобразует x в тип данных char в формате, указанном fmt.
Если x — дата, nlsparm= NLS_DATE_LANGUAGE управляет языком, используемым для возвращаемых месяца и дня.
Если x — число, nlsparm=NLS_NUMERIC_CHARACTERS используется для указания десятичных и тысячных разделителей, а также символов валюты.
NLS_NUMERIC_CHARACTERS="dg", NLS_CURRENCY="строка"
НА СЕГОДНЯШНИЙ ДЕНЬ([,[,)
c представляет строку, а fmt представляет строку в специальном формате. Возвращает c, отображаемый в формате fmt, а nlsparm указывает используемый язык.
Функция преобразует строку c в тип данных даты.
ТО_MULTI_BYTE()
c представляет строку, и функция преобразует символ усечения c в многобайтовый символ.
ТО_НОМЕР([,[,)
c представляет строку, fmt представляет строку в специальном формате, а возвращаемое значение функции отображается в формате, указанном fmt.
nlsparm представляет язык, а функция вернет число, представленное c.
ТО_SINGLE_BYTE()
Преобразуйте многобайтовые символы в строке c в эквивалентные однобайтовые символы.
Эта функция используется только в том случае, если набор символов базы данных содержит как однобайтовые, так и многобайтовые символы.
Другие однострочные функции
БФАЙЛИМЯ(,)
dir — это объект типа каталога, а file — имя файла. Функция возвращает пустой индикатор значения позиции BFILE,
Функция используется для инициализации переменных BFILE или столбцов BFILE.
ДЕКОДИРОВАНИЕ(,,[,,,[])
x — выражение, m1 — выражение соответствия, x сравнивается с m1, если m1 равно x, то возвращается r1, в противном случае x сравнивается с m2,
И так м3, м4, м5.... пока не будет возвращен результат.
СВАЛКА(,[,[,[,]]])
x — это выражение или символ, а fmt представляет восьмеричный, десятичный, шестнадцатеричный или одиночный символ.
Функция возвращает значение типа VARCHAR2, содержащее информацию о внутреннем представлении x.
Если указаны n1,n2, то будут возвращены байты длины n2, начиная с n1.
ПУСТОЙ_БЛОБ()
Эта функция не имеет параметров и возвращает пустой индикатор позиции BLOB. Функция, используемая для инициализации переменной BLOB или столбца BLOB.
ПУСТОЙ_CLOB()
Эта функция не имеет параметров и возвращает пустой индикатор позиции CLOB. Функция используется для инициализации переменной CLOB или столбца CLOB.
ВЕЛИЧАЙШИЙ()
exp_list — это список выражений, возвращающий наибольшее выражение. Каждое выражение неявно преобразуется в тип данных первого выражения.
Если первое выражение относится к любому из строковых типов данных, то возвращаемый результат имеет тип данных varchar2,
Используемое при этом сравнение представляет собой тип сравнения без заполнения пробелов.
НАИМЕНЕЕ()
exp_list — это список выражений, возвращающий наименьшее из них. Каждое выражение неявно преобразуется в тип данных первого выражения.
Если первое выражение относится к любому из строковых типов данных, возвращаемый результат имеет тип данных varchar2,
Используемое при этом сравнение представляет собой тип сравнения без заполнения пробелов.
UID
Эта функция не имеет параметров и возвращает целое число, которое однозначно идентифицирует текущего пользователя базы данных.
ПОЛЬЗОВАТЕЛЬ
Возвращает имя текущего пользователя
УСЕРЕНВ()
На основе opt return содержит информацию о текущем сеансе. Необязательные значения для opt:
Роль SYSDBA отвечает в сеансе ISDBA и возвращает TRUE.
SESSIONID возвращает идентификатор сеанса аудита.
ENTRYID возвращает доступные идентификаторы записей аудита.
INSTANCE Возвращает идентификатор экземпляра после подключения сеанса.
Это значение используется только в том случае, если вы используете параллельный сервер и имеете несколько экземпляров.
LANGUAGE возвращает набор символов языка, региона и настроек базы данных.
LANG возвращает ISO-аббревиатуру названия языка.
TERMINAL Возвращает идентификатор операционной системы для терминала или компьютера, используемого текущим сеансом.
РАЗМЕР()
х — это выражение. Возвращает количество байтов, внутренне представленных x.
Групповые функции в SQL также называются агрегатными функциями. Они возвращают один результат на основе нескольких строк. Точное количество строк невозможно определить.
Если запрос не выполнен и все результаты не включены. В отличие от однострочных функций, все строки известны во время анализа.
Из-за этого различия требования и поведение групповых функций немного отличаются от однострочных функций.
Групповые (многострочные) функции По сравнению с однострочными функциями Oracle предоставляет богатый набор групповых многострочных функций.
Эти функции могут использоваться в операторе select или в предложении has для select и часто используются с GROUP BY при выборе подстрок.
AVG([{DISYINCT|ВСЕ}])
Возвращает среднее числовых значений. Настройка по умолчанию — ВСЕ.
ВЫБРАТЬ AVG(sal),AVG(ВСЕ sal),AVG(ОТДЕЛЬНЫЕ Sal)
ИЗ scott.empAVG(SAL) AVG(ALL SAL) AVG(DISTINCT SAL)1877.94118 1877.94118 1916.071413
COUNT({*|DISTINCT|ВСЕ} )
Возвращает количество строк в запросе. Значение по умолчанию — ALL, * означает возврат всех строк.
МАКС([{DISTINCT|ВСЕ}])
Возвращает максимальное значение элемента списка выбора. Если x является строковым типом данных, он возвращает тип данных VARCHAR2.
Если X — тип данных DATA, возвращает дату, если X — числовой тип данных, возвращает число.
Обратите внимание, что различные и все не имеют никакого эффекта, максимальное значение должно быть одинаковым для обеих настроек.
МИН([{DISTINCT|ВСЕ}])
Возвращает минимальное значение элемента списка выбора.
СТАНДОТКЛОН([{DISTINCT|ВСЕ}])
Возвращает стандартное отклонение элементов списка селектора, где стандартное отклонение — это квадратный корень из дисперсии.
СУММ([{DISTINCT|ВСЕ}])
Возвращает сумму числовых значений выбранных элементов списка.
дисперсия([{DISTINCT|ВСЕ}])
Возвращает статистическую дисперсию выбранного элемента списка.
Используйте GROUP BY для группировки данных. Как следует из названия, функция группировки работает с сгруппированными данными.
Мы сообщаем базе данных, как группировать или классифицировать данные, используя GROUP BY. Когда мы используем функцию группировки в предложении SELECT инструкции SELECT,
Мы должны поместить сгруппированные или непостоянные столбцы в предложение GROUP BY. Если группа по не используется для специальной обработки,
Тогда классификация по умолчанию заключается в помещении всего результата в одну категорию.
выберите статистику, счетчик (*) zip_count
из состояния zip_codes GROUP BY;
ST ZIP_COUNT----------AK 360AL 1212AR 1309AZ 768CA 3982
В этом примере мы используем поле состояния для классификации; если мы хотим отсортировать результаты по почтовым индексам, мы можем использовать оператор ORDER BY:
Предложение ORDER BY может использовать функции столбца или группы.
выберите статистику, счетчик (*) zip_count
из почтовых_кодов
ГРУППИРОВАТЬ ПО состоянию ПОРЯДОК ПО СЧЕТУ(*) DESC;
СЧЕТ ST(*)----------NY 4312PA 4297TX 4123CA 3982
Ограничьте сгруппированные данные с помощью предложения HAVING
Теперь, когда вы знаете, как использовать функцию main в инструкции SELECT и предложении ORDER BY запроса, функцию group можно использовать только для двух подстрок.
Групповые функции нельзя использовать в подстроках WHERE. Например, следующий запрос неверен:
ошибка
ВЫБЕРИТЕ продавца_клерка,SUN(sale_amount)
ОТ валовых_продаж
ГДЕ sales_dept='ВНЕШНИЙ' И SUM(sale_amount)>10000
ГРУППИРОВАТЬ ПО продавцу
В этом операторе база данных не знает, что такое SUM(). Когда нам нужно указать базе данных сгруппировать строки, а затем ограничить вывод сгруппированных строк,
Правильный способ — использовать оператор HAVING:
ВЫБЕРИТЕ продавца_клерка,SUN(sale_amount)
ОТ валовых_продаж
ГДЕ sales_dept = 'ВНЕШНИЙ'
ГРУППИРОВАТЬ ПО продавцу
ЕСТЬ СУММА(продажа_сумма)>10000;
Вложенные функции Функции могут быть вложенными. Выход одной функции может быть входом другой функции. Операнды имеют наследуемый процесс выполнения.
Но приоритет функций основан только на положении, а функции следуют принципу изнутри наружу и слева направо.
Технология вложения обычно используется для таких функций, как DECODE, которые можно использовать в логических утверждениях IF...THEN...ELSE.