Выражения (transact-sql)expressions (transact-sql)

Создание и выполнение определяемых пользователем функций

Определяемые пользователем функции создаются посредством инструкции CREATE FUNCTION, которая имеет следующий синтаксис:

Соглашения по синтаксису

Параметр schema_name определяет имя схемы, которая назначается владельцем создаваемой UDF, а параметр function_name определяет имя этой функции. Параметр @param является входным параметром функции (формальным аргументом), чей тип данных определяется параметром type. Параметры функции — это значения, которые передаются вызывающим объектом определяемой пользователем функции для использования в ней. Параметр default определяет значение по умолчанию для соответствующего параметра функции. (Значением по умолчанию также может быть NULL.)

Предложение RETURNS определяет тип данных значения, возвращаемого UDF. Это может быть почти любой стандартный тип данных, поддерживаемый системой баз данных, включая тип данных TABLE. Единственным типом данных, который нельзя указывать, является тип данных timestamp.

Определяемые пользователем функции могут быть либо скалярными, либо табличными. Скалярные функции возвращают атомарное (скалярное) значение. Это означает, что в предложении RETURNS скалярной функции указывается один из стандартных типов данных. Функция является табличной, если предложение RETURNS возвращает набор строк.

Параметр WITH ENCRYPTION в системном каталоге кодирует информацию, содержащую текст инструкции CREATE FUNCTION. Таким образом, предотвращается несанкционированный просмотр текста, который был использован для создания функции. Данная опция позволяет повысить безопасность системы баз данных.

Альтернативное предложение WITH SCHEMABINDING привязывает UDF к объектам базы данных, к которым эта функция обращается. После этого любая попытка модифицировать объект базы данных, к которому обращается функция, претерпевает неудачу. (Привязка функции к объектам базы данных, к которым она обращается, удаляется только при изменении функции, после чего параметр SCHEMABINDING больше не задан.)

Для того чтобы во время создания функции использовать предложение SCHEMABINDING, объекты базы данных, к которым обращается функция, должны удовлетворять следующим условиям:

• все представления и другие UDF, к которым обращается определяемая функция, должны быть привязаны к схеме;

• все объекты базы данных (таблицы, представления и UDF) должны быть в той же самой базе данных, что и определяемая функция.

Параметр block определяет блок BEGIN/END, содержащий реализацию функции. Последней инструкцией блока должна быть инструкция RETURN с аргументом. (Значением аргумента является возвращаемое функцией значение.) Внутри блока BEGIN/END разрешаются только следующие инструкции:

• инструкции присвоения, такие как SET;

• инструкции для управления ходом выполнения, такие как WHILE и IF;

• инструкции DECLARE, объявляющие локальные переменные;

• инструкции SELECT, содержащие списки столбцов выборки с выражениями, значения которых присваиваются переменным, являющимися локальными для данной функции;

• инструкции INSERT, UPDATE и DELETE, которые изменяют переменные с типом данных TABLE, являющиеся локальными для данной функции.

По умолчанию инструкцию CREATE FUNCTION могут использовать только члены предопределенной роли сервера sysadmin и предопределенной роли базы данных db_owner или db_ddladmin. Но члены этих ролей могут присвоить это право другим пользователям с помощью инструкции GRANT CREATE FUNCTION.

В примере ниже показано создание функции ComputeCosts:

Функция ComputeCosts вычисляет дополнительные расходы, возникающие при увеличении бюджетов проектов. Единственный входной параметр, @percent, определяет процентное значение увеличения бюджетов. В блоке BEGIN/END сначала объявляются две локальные переменные: @addCosts и @sumBudget, а затем с помощью инструкции SELECT переменной @sumBudget присваивается общая сумма всех бюджетов. После этого функция вычисляет общие дополнительные расходы и посредством инструкции RETURN возвращает это значение.

3.3.4. Функция, возвращающая таблицу

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

CREATE FUNCTION GetPrice()
RETURNS TABLE
AS
 RETURN 
 (
  SELECT Дата, , Цена, 
    Количество, Цена*Количество AS Сумма
  FROM Товары
 )

Начало функции такое же, как у скалярной – указываем оператор CREATE FUNCTION и имя функции. Я специально создал эту функцию без параметров, чтобы вы увидели, как это делается. Не смотря на то, что параметров нет, после имени должны идти круглые скобки, в которых не надо ничего писать. Если не указать скобок, то сервер вернет ошибку и функция не будет создана.

Разница есть и в секции RETURNS, после которой указывается тип TABLE, что говорит о необходимости вернуть таблицу. После этого идет ключевое слово AS и RETURN, после которого должно идти возвращаемое значение. Для функции данного типа в секции RETURN нужно в скобках указать запрос, результат которого и будет возвращаться функцией.

Когда пишете запрос, то все его поля должны содержать имена. Если одно из полей не имеет имени, то результатом выполнения оператора CREATE FUNCTION будет ошибка. В нашем примере последнее поле является результатом перемножения полей «Цена» и «Количество», а такие поля не имеют имени, поэтому мы его задаем с помощью ключевого слова AS.

Посмотрим, как можно использовать такую функцию с помощью оператора SELECT:

SELECT * 
FROM GetPrice()

Так как мы используем простой оператор SELECT, то мы можем и ограничивать вывод определенными строками, с помощью ограничений в секции WHERE. Например, в следующем примере выбираем из результата функции только те строки, в которых поле «Количество» содержит значение 1:

SELECT * FROM GetPrice()
WHERE Количество=1

Функция возвращает в качестве результата таблице, которую вы можете использовать как любую другую таблицу базы данных. Давайте создадим пример в котором можно будет увидеть использование функции в связи с таблицами. Для начала создадим функцию, которая будет возвращать идентификатор работников таблицы tbPeoples и объединенные в одно поле ФИО:

CREATE FUNCTION GetPeoples()
RETURNS TABLE
AS
 RETURN 
 (
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO
  FROM tbPeoples
 )

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

SELECT * 
FROM GetPeoples() p, tbPhoneNumbers pn
WHERE p.idPeoples=pn.idPeoples

Как видите, функции, возвращающие таблицы очень удобны. Они больше, чем процедуры похожи на объекты просмотра, но при этом позволяют принимать параметры. Таким образом, можно сделать так, чтобы сама функция возвращала нам только то, что нужно. Вьюшки такого не могут делать по определению. Чтобы получить нужные данные, вьюшка должна выполнить свой SELECT запрос, а потом уже во внешнем запросе мы пишем еще один оператор SELECT, с помощью которого ограничивается вывод до необходимого. Таким образом, выполняется два запроса SELECT, что для большой таблицы достаточно накладно. Функция же может сразу вернуть только то, что нужно.

Рассмотрим пример, функция GetPeoples у нас возвращает все строки таблицы. Чтобы получить только нужную фамилию, нужно писать запрос типа:

SELECT * 
FROM GetPeoples()
WHERE FIO LIKE 'ПОЧЕЧКИН%'

В этом случае будут выполняться два запроса: этот и еще один внутри функции. Но если передавать фамилию в качестве параметра в функцию и там сделать секцию WHERE, то можно обойтись и одним запросом SELECT:

CREATE FUNCTION GetPeoples1(@Famil varchar(50))
RETURNS TABLE
AS
 RETURN 
 (
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName AS FIO
  FROM tbPeoples
  WHERE vcFamil=@Famil
 )

Числовые и календарные функции SQL

В состав Oracle входит несколько числовых функций (number functions), которые принимают числовые входные параметры и возвращают числовые значения. Календарные функции (date functions) помогают форматировать значения даты и времени несколькими способами. Ниже перечислены некоторые наиболее важные числовые и календарные функции.

• . Возвращает число, округленное на указанное количество позиций справа от десятичной запятой.
• . Возвращает результат усечения даты в указанном формате.
• . Применяется довольно часто и возвращает текущее значение даты и времени:

      SQL> SELECT sysdate FROM dual;
      SYSDATE
      --------------------
      07/AUG/2008
      SQL> 

• . Преобразует тип данных или в тип данных .
• . Служит для изменения текущего формата даты. Стандартный формат даты в Oracle выглядит так: ДД-МММ-ГГГГ ().
• Функция принимает символьную строку с действительными данными внутри и преобразует ее в принятый по умолчанию формат даты в Oracle. Она может также изменять формат даты, как показано ниже:

      SQL> SELECT TO_DATE('August 20,2008', 'MonthDD,YYYY') FROM dual;
      TO_DATE('AUGUST20,2008'
      -----------------------
      08/20/2008
      SQL>
 

TO_CHAR. Преобразует дату в символьную строку, как показано в следующем примере:

      SQL> SELECT SYSDATE FROM dual;
      SYSDATE
      -----------
      04-AUG-2008
      SQL>
      SQL> SELECT TO_CHAR(SYSDATE, 'DAY, DDTH MONTH YYYY') FROM DUAL;
      TO_CHAR(SYSDATE,'DAY,DDTHMON
      --------------------------------
      THURSDAY , 04TH AUGUST 2008
      SQL> 

TO_NUMBER. Преобразует символьную строку в числовой формат:

      SQL> UPDATE employees SET salary = salary +
      TO_NUMBER('100.00', '9G999D99')
      WHERE last_name = 'Alapati'; 

Как можно сочетать LEFT, RIGHT и LEN

Допустим, что Вам необходимо получить первые несколько символов в строке или проверить эти первые символы в строке на наличие чего-либо, например, какой-то номер, а его длина естественно разная (пример естественно тестовый).

• Left (str, kol) – функции вырезает указанное количество символов с лева, имеет два параметра первой это строка а второй соответственно количество символов;
• Right (str, kol) — функции вырезает указанное количество символов с право, параметры те же самые.

Теперь будем использовать простые запросы к таблице

Для начала давайте создадим таблицу test_table:

CREATE TABLE (
          IDENTITY(1,1) NOT NULL,
         (18, 0) NULL,
         (50) NULL,
 CONSTRAINT  PRIMARY KEY CLUSTERED 
(
         ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON 
) ON 
GO

Заполним ее тестовыми данными и напишем вот такие запросы:

Как Вы понимаете первый запрос это просто выборка всех строк (Основы SQL — оператор select), а второй это уже непосредственно сочетание наших функций, вот код:

select * from test_table 
select number, left(text,LEN(number)) as str from  test_table

А если бы эти номера были справа, то мы бы использовали функцию RIGHT.

Определение подпрограмм PL/SQL в командах SQL (12.1 и выше)

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

Функция может использоваться в запросах следующим образом:

Эта возможность позволяет «расширить» язык функциональностью, присущей конкретному приложению, и повторно использовать алгоритмы (вместо копирования). К недостаткам выполнения пользовательских функций в SQL следует отнести необ­ходимость переключения контекста между исполнительными ядрами и P. Начиная с Oracle Database 12c вы можете определять функции и процедуры в секции подзапроса, чтобы затем использовать их как любую встроенную или пользовательскую функцию. Эта возможность позволяет консолидировать функцию и запрос в одной команде:

Главное преимущество такого решения — повышение производительности, так как при таком определении функций затраты на переключение контекста с ядра а ядро существенно снижаются. С другой стороны, за него приходится расплачиваться возможностью повторного использования логики в других частях приложения.

Впрочем, для определения функций в секции есть и другие причины. В можно вызвать пакетную функцию, но нельзя сослаться на константу, объявленную в пакете (если только команда не выполняется внутри блока), как показано в сле­дующем примере:

Классическое обходное решение основано на определении функции в пакете и ее по­следующем вызове:

Для простого обращения к значению константы в команде SQL потребуется слишком много кода и усилий. Начиная с версии 12.1 это стало излишним — достаточно создать функцию в секции :

Функции , определяемые в SQL, также пригодятся при работе с автономными базами данных, доступными только для чтения. Хотя в таких базах данных невозмож­но создавать «вспомогательные» функции , вы можете определять их прямо в запросах.

Механизм стал чрезвычайно полезным усовершенствованием языка SQL. Тем не менее каждый раз, когда вы планируете его использование, стоит задать себе один вопрос: «Потребуется ли эта функциональность в нескольких местах приложения?»

Если вы ответите на него положительно, следует решить, компенсирует ли выигрыш по производительности от применения потенциальные потери от копи­рования и вставки этой логики в нескольких командах SQL.

Учтите, что в версии 12.1 в блоках PL/SQL невозможно выполнить статическую коман­ду с секцией . Безусловно, это выглядит очень странно, и я уверен, что в 12.2 такая возможность появится, но пока при попытке выполнения следующего кода будет выдана ошибка:

Вас заинтересует / Intresting for you:

Назначение языка SQL и необход… 548 просмотров Ирина Светлова Mon, 28 Oct 2019, 05:40:06

Управление приложениями PL/SQL… 2182 просмотров Rasen Fasenger Thu, 16 Jul 2020, 06:20:48

Встроенные методы коллекций PL… 4432 просмотров sepia Tue, 29 Oct 2019, 09:54:01

Тип данных RAW в PL/SQL 3606 просмотров Doctor Thu, 12 Jul 2018, 08:41:33

Author: Максим Николенко

Другие статьи автора:

3.3.3. Использование функций

Как выполнить такую функцию? Да также, как и многие другие системные функции (например, GETDATE()). Например, следующий пример использует функцию в операторе SELECT:

SELECT dbo.GetSumm('Картофель', '03.03.2005')

В этом примере, оператор SELECT возвращает результат выполнения функции GetSumm. Функция принадлежит пользователю dbo, поэтому перед именем я указал владельца. После имени в скобках должны быть перечислены параметры в том же порядке, что и при объявлении функции. В данном примере я запрашиваю затраты на картофель, купленный 3.3.2005.

Выполните следующий запрос и убедитесь, что он вернул тот же результат, что и созданная нами функция:

SELECT Цена*Количество
FROM Товары
WHERE ='Картофель'
  AND Дата='03.03.2005'

Функции можно использовать не только в операторе SELECT, но и напрямую, присваивая значение переменной. Например:

DECLARE @Summ numeric(10,2)
SET @Summ=dbo.GetSumm('Картофель', '03.03.2005')
PRINT @Summ

В этом примере мы объявили переменную @Summ типа numeric(10,2). Именно такой тип возвращает функция. В следующей строке переменной присваивается результат выполнения Summ, с помощью SET.

Давайте посмотрим, что произойдет, если передать функции такие параметры, при которых запрос функции вернет более одной строки. В нашей таблице товаров сочетание даты и название не дает уникальности, потому что мы ее нарушили. Первичного ключа в таблице также нет, и среди товаров у меня есть четыре строки, которые имеют свои точные копии. Это нарушает правило уникальности строк в реляционных базах, но очень наглядно показывает, что в реальной жизни нарушать его нельзя.

Итак, в моей таблице есть две покупки хлеба 1.1.2005-го числа. Попробую запросить у функцию сумму:

SELECT dbo.GetSumm('Хлеб', '01.01.2005')

Результатом будет только одно число, хотя строки две. А какую строку из двух вернул сервер? Никто точно сказать не может, потому что они обе одинаковые и без единого различия. Поэтому сервер скорей всего вернул первую из строк.

Строковые функции

Аналитические функции SQL

Предлагаемые в Oracle аналитические функции SQL являются мощными инструментами для приложений бизнес-аналитики. В Oracle заявляют, что в случае применения этих функций скорость выполнения запросов может увеличиваться на 200–500%. Они предназначены для выполнения сложных суммарных вычислений без написания слишком большого объема кода. Ниже перечислены наиболее важные аналитические функции SQL, которые могут применяться в базе данных Oracle.

Функции ранжирования (ranking functions). Такие функции позволяют ранжировать элементы набора данных в соответствии с определенными критериями. В Oracle доступно несколько функций ранжирования: и . В листинге 1 приведен простой пример,показывающий, как функция ранжирования помогает расположить в нужном порядке данные по продажам.

SQL> SELECT sales_type,
TO_CHAR(SUM(amount_sold), '9,999,999,999') SALES,
RANK() OVER (ORDER BY SUM(amount_sold) ) AS original_rank,
RANK() OVER (ORDER BY SUM(amount_sold)
DESC NULLS LAST) AS derived_rank
FROM sales, products, customers, time_frame, sales_types
WHERE sales.prod_id=products.prod_id AND
sales.cust_id=customers.cust_id AND
sales.time_id=time_frame.time_id AND
sales.sales_type_id=sales_types.sales_type_id AND
timeframe.calendar_month_desc IN ('2008-07', '2008-08')
AND country_id='INDIA'
GROUP BY sales_type;
SALES_TYPE      SALES       ORIGINAL_RANK  DERIVED_RANK
------------- ---------    --------------  ------------
Direct Sales    5,744,263         5        1
Internet        3,625,993         4        2
Catalog         1,858,386         3        3
Partners        1,500,213         2        4
Tele Sales        604,656         1        5
SQL>

• Функции агрегирования со скользящим окном (moving-window aggregates). Эти функции позволяют получать кумулятивные суммарные и скользящие средние значения.
• Функции сравнения периодов (period-over-period comparison). Эти функции позволяют сравнивать два периода (например, “насколько, в процентном отношении, показатели первого квартала 2008 г. увеличились по сравнению с показателями первого квартала 2006 г.?”).
• Функции сравнения показателей соотношения для составления отчетов (ratio-toreport comparisons). Эти функции позволяют сравнивать показатели соотношения (например, “сколько, в процентном отношении, людей зарегистрировали партийную принадлежность в августе по сравнению со всем годом?”).
• Статистические функции (statistical functions). Эти функции вычисляют корреляционные и регрессивные показатели и тем самым позволяют анализировать причинно-следственные связи между данными.
• Инверсные процентные функции (inverse percentiles). Эти функции помогают находить данные, соответствующие значению процентиля (например, получать имена тех агентов по продаже, объем продаж у которых соответствует срединному значению продаж).
• Функция условного ранжирования и распространения (hypothetical ranks and distributions). Эти функции помогают вычислять, как новое значение для столбца впишется в существующие данные с точки зрения ранжирования и распространения.
• Гистограммные функции (histograms). Эти функции возвращают подходящее для каждой строки в таблице количество гистограммных данных.
• Агрегатные функции определения значений первой и последней записи (first/last aggregate). Такие функции удобно применять в случае использования конструкции для сортировки данных по группам. Они позволяют задавать желаемый порядок сортировки для групп.

Вас заинтересует / Intresting for you:

Oracle и непроцедурный доступ … 6797 просмотров Antoni Tue, 21 Nov 2017, 13:32:50

Создание базы данных Oracle 13323 просмотров Александров Попков Wed, 14 Nov 2018, 12:44:39

Видеокурс по администрированию… 7147 просмотров Илья Дергунов Mon, 14 May 2018, 05:08:47

СУБД Oracle: обзор характерист… 4151 просмотров Antoni Fri, 24 Nov 2017, 07:35:05

Author: Боба

Другие статьи автора:

3.3.5. Много операторная функция возвращающая таблицу

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

Следующий пример показывает, как создать функцию, которая может вернуть в качестве результата таблицу, и при этом, в теле функции могут выполняться несколько операторов:

CREATE FUNCTION имя (параметры)
RETURNS  имя_переменной TABLE 
  (описание вида таблицы, 
   в которой будет представлен результат)
AS
 BEGIN
  Выполнение любого количества операций
  RETURN
 END

Это упрощенный вид создания процедуры. Более полный вид мы рассматривали в начале главы, а сейчас я упростил объявление, чтобы проще было его разбирать.

Объявление больше похоже на создание скалярных функций. Первая строка без изменений. В секции RETURNS объявляется переменная, которая имеет тип TABLE. После этого, в скобках нужно описать поля результирующей таблицы. После ключевого слова AS идtт пара операторов BEGIN и END, между которыми может выполняться какое угодно количество операций. Выполнение операций заканчивается ключевым словом RETURN.

Вот тут есть одно отличие от скалярных функций – после RETURN мы указывали имя переменной, значение которой должно стать результатом. В данном случае ничего указывать не надо. Мы уже объявили переменную в секции RETURNS и описали формат этой переменной. В теле функции мы можем и должны наполнить эту переменную значениями и именно это попадет в результат.

Теперь посмотрим на пример создания функции:

CREATE FUNCTION getFIO ()
RETURNS  @ret TABLE 
  (idPeoples int primary key,
   vcFIO varchar(100))
AS
 BEGIN
  INSERT @ret
  SELECT idPeoples, vcFamil+' '+vcName+' '+vcSurName
  FROM tbPeoples;

  RETURN
 END

В данном примере в качестве результата объявлена переменная @ret, которая является таблицей из двух полей «idPeoples» типа int и «vcFIO» типа varchar длинной в 50 символов. В теле функции в эту таблицу записываются значения из таблицы tbPeoples и выполняется оператор RETURN, завершающий выполнение функции.

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

SELECT * 
FROM GetFIO()