Скалярні функції

Скалярними називають ті функції, які повертають одне значення Ці функції можуть приймати безліч параметрів, виконувати обчислення, але в результаті видають одне значення Ці функції можуть використовуватися в будь-яких виразах, навіть беруть участь в обмеженнях перевірки Значення повертається функцією за допомогою оператора return – ця команда повинна завершувати скалярную функцію

повинні бути детермінованими Це означає, що для одних і тих же вхідних параметрів вони повинні генерувати одне і те ж вихідне значення З цієї причини деякі вбудовані функції (Наприклад, getdate (), rasd () і newid ()) неприпустимі в скалярних користувальницьких функціях

У скалярних користувальницьких функціях не допускаються операції оновлення бази даних, але в той же час вони можуть працювати з локальними тимчасовими таблицями Вони не можуть повертати дані BLOB (двійкові великі обєкти) таких типів, як text, image і ntext, так само як табличні змінні і курсори

Додаткова Якщо в скалярної функції обчислюється деяка формула, розбирається на інформація складові текст або виконуються деякі інші завдання, що не викорис-

• _ – ** Зующие доступ до бази даних, то функція буде більш продуктивною, якщо

написати її на якомусь мові CLR, а не на Т-SQL Більш докладно про програмування на мовах CLR ми поговоримо в розділі 27

Створення скалярних функцій

створюються, змінюються і видаляються за допомогою тих же інструкцій DDL, що й інші обєкти, хоча синтаксис трохи відрізняється, щоб визначити повертається значення:

CREATE FUNCTION імя_функциі (входние_параметри)

RETURNS тип_даних AS

BEGIN

текст_функціі RETURN вираз END

У списку вхідних параметрів повинні бути зазначені типи даних і, в разі потреби, значення за замовчуванням, аналогічно збереженим процедурам (параметр = замовчування) Параметри функцій відрізняються від параметрів збережених процедур тим, що навіть якщо визначені значення за замовчуванням, параметри все одно повинні бути присутніми у виклику функції (тобто параметри з певними за замовчуванням значеннями все одно обовязкові) Щоб запитати значення за замовчуванням при виклику функції, їй передається ключове слово default

Вважається доброю практикою створювати для параметрів функції за замовчуванням, оскільки вони оберігають функцію від генерування помилки, якщо вона використовується у вираженні, а необхідний параметр їй не надано або якщо інструкція SELECT НЕ відібрала у функції жодного рядка

Наступна скалярная функція виконує просту арифметичну операцію її другий параметр має значення за замовчуванням:

CREATE FUNCTION dboMultiply (@A INT, @B INT = 3)

RETURNS INT AS

BEGIN

RETURN @A * @B End go

SELECT dboMultiply (3,4)

SELECT dboMultiply (7, DEFAULT)

Результат виконання пакету буде наступним:

12

21

Системні функції, розроблені компанією Microsoft, зберігаються в базі дан-На замітку них master і повинні викликатися з префіксом у вигляді двох двокрапок

(:: Імя_функциі)

У наступному прикладі представлена ​​більш складна скалярная функція fGetPrice, функціонально аналогічна представленої в попередньому розділі збереженій процедурі pGetPrice, яка повертала єдиний результат у своєму вихідному параметрі Ця збережена процедура повертала коректну ціну для заданої дати і детермінованою знижки клієнта Так як повертається значення – єдине, ця операція є кандидатом на реалізацію у вигляді скалярної функції

Ця функція має таке ж командне наповнення, як і наведена вище процедура, що зберігається, за винятком того, що результат повертається не за допомогою вихідного параметра @ CurrPrice, а в операторі return Параметр коду клієнта має значення за замовчуванням null Ще однією відмінністю є те, що збережена процедура використовувала значення за замовчуванням null для параметра дати, підставляючи замість нього поточну дату за допомогою функції get date () Користувальницькі функції є детермінованими і не можуть використовувати функції типу getdate (), тому параметр дати в даному випадку є обовязковим Нижче наведено текст скалярної функції fGetPrice ()

CREATE FUNCTION fGetPrice (

@Code CHAR(10),

@PriceDate DATETIME,

@ContactCode CHAR(15) = NULL)

RETURNS MONEY As

BEGIN

DECLARE @CurrPrice MONEY

DECLARE ©DiscountPercent NUMERIC (4,2)

– Встановлюємо відсоток знижки

– Якщо код замовника не переданий, знижка буде нульовою SELECT @ DiscountPercent = CustomerTypeDiscountPercent FROM dboContact

JOIN dboCustomerType

ON contactCustomerTypelD =

CustomerTypeCustomerTypelD WHERE ContactCode = @ContactCode IF ©DiscountPercent IS NULL SET @DiscountPercent = 0 SELECT @CurrPrice = Price * (l-@DiscountPercent)

FROM dboPrice JOIN dboProduct

ON PriceProductID = ProductProductID WHERE Code = @Code AND EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dboPrice JOIN dboProduct

ON PriceProductID = ProductProductID WHERE Code = @Code

AND EffectiveDate &lt= @PriceDate)

RETURN @CurrPrice END

Виклик скалярних функцій

можуть використовуватися в будь-якому місці виразів, де допустимо одне значення Користувальницькі скалярні функції повинні завжди викликатися за допомогою двокомпонентного імені (власник, імя) У наступному прикладі продемонстровано виклик функції fGetPriceO з бази даних OBXKites:

USE OBXKites

SELECT dbofGetPrice(1006,GetDate(),DEFAULT)

SELECT dbofGetPrice(1001,5/1/2001,NULL)

Результат виконання пакету:

1259500

149500

Додаткова Функція dbo GenColUpdate є користувальницької скалярною функцією, інформація використовуваної при аудиті, і буде розглянута в розділі 24 Призначена для користувача – функція dbo TitleCase була створена в розділі 8

Створення функцій зі повязаної схемою

Всі три типи користувача функцій можуть бути створені з використанням переваг звязування зі схемою Уявлення можуть звязуватися зі схемою, але ця функція недоступна для збережених процедур Звязування зі схемою робить неможливим зміну і видалення всіх обєктів, від яких залежить функція Якщо звязана зі схемою функція звертається до таблиці ТаИе, то до останньої можуть бути додані стовпці в Водночас не може бути змінений або видалений жоден з існуючих її стовпців, так само як і вся таблиця

Для створення функції, повязаної зі схемою, вставте параметр між ключовими словами returns і as, як показано нижче

CREATE FUNCTION FunctionName (Input Parameters)

RETURNS DataType WITH SCHEMA BINDING AS

BEGIN

інструкції

RETURNS вираз

END

Підключення до схеми не тільки попереджає користувача про те що деяка операція вплине на обєкт, – воно відкине ці зміни Для видалення звязку зі схемою, щоб дозволити зміни, використовується інструкція ALTER, яка відключає це звязування

Джерело: Нільсен, Пол Microsoft SQL Server 2005 Біблія користувача : Пер з англ – М: ООО ІД Вільямс , 2008 – 1232 с : Ил – Парал тит англ

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*