Впроваджені табличні функції

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

Створення впровадженої табличній функції

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

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

RETURNS Table AS

RETURN (інструкція SELECT)

Наступна впроваджена таблична функція є функціональним еквівалентом подання vE vent View, створеного нами в розділі 14

USE СНА2 go

CREATE FUNCTION fEventList ()

RETURNS Table AS

RETURN(

SELECT dboCustomerTypeName AS Customer,

dboCustomerLastName, dboCustomerFirstName, dboCustomerNickname,

dboEvent_mm_CustomerConfirmDate, dboEventCode, dboEventDateBegin, dboTourName AS Tour, dboBaseCampName, dboEventComment FROM dboTour

INNER JOIN dboEvent

ON dboTourTourlD = dboEventTourlD INNER JOIN dboEvent_mm_Customer

ON dboEventEventID = dboEvent_mm_CustomerEventID INNER JOIN dboCustomer

ON dboEvent_mm_CustomerCustomerlD = dboCustomerCustomerlD LEFT OUTER JOIN dboCustomerType ON dboCustomerCustomerTypeID

= dboCustomerTypeCustomerTypeID INNER JOIN dboBaseCamp

ON dboTourBaseCampID = dboBaseCampBaseCampID)

Виклик впровадженої табличній функції

Для вилучення даних за допомогою функції fEventList викличте її в реченні FROM інструкції SELECT:

SELECT LastName, Code, DateBegin FROM dbofEventList()

Результат виконання інструкції (скорочений):

LastName                        Code           DateBegin

Anderson                        01-003        2001-03-16 00:00:00000

Brown  01-003               2001-03-16 00:00:00000

Frank    01-003               2001-03-16 00:00:00000

Як і в збережених процедурах, основна затримка в продуктивності виникає при першому виклику функції, коли вона компілюється і зберігається в памяті Всі подальші виклики спрацьовують швидше

Порівнюючи впроваджені табличні функції з іншими обєктами SQL Server, можна сказати, що по продуктивності вони стоять на одній сходинці з збереженими процедурами і на 5-10% швидше уявлень

Використання параметрів

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

У наступних прикладах порівнюються додавання обмеження в подання з використанням параметра функції Описане нижче уявлення повертає поточний прайс-лист для всіх товарів

USE OBXKites go

CREATE VIEW vPricelist AS

SELECT, PricePrice FROM dboPrice

JOIN dboProduct P

ON PriceProductID = PProductID WHERE EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dboPrice

WHERE ProductID = PProductID

AND Eff ectiveDate &lt= GetDateO)

Для обмеження цього списку ціною одного товару в зухвалу інструкцію додається пропозиція WHERE:

SELECT *

FROM vPriceList WHERE = *1001

Буде отримано наступний результат:

Code    Price

1001    149500

З подання vPriceList SQL Server створює нову інструкцію SELECT, потім викликає обмеження WHERE зовнішньої інструкції і на їх підставі генерує план виконання запиту

На противагу цьому функція дозволяє передавати обмеження як параметр у вже скомпільовану інструкцію SELECT:

CREATE FUNCTION dbofPriceList (

@Code CHAR(10) = Null, ©PriceDate DateTime)

RETURNS Table AS

RETURN(

SELECT Code, PricePrice FROM dboPrice

JOIN dboProduct P

ON PriceProductID = PProductID WHERE EffectiveDate =

(SELECT MAX(EffectiveDate)

FROM dboPrice

WHERE ProductID = PProductID

AND EffectiveDate &lt= @PriceDate)

AND (Code = @Code OR @Code IS NULL)

)

Якщо функція викликається з першим параметром за замовчуванням, то повертається прайс-лист для всіх товарів на задану дату:

SELECT * FROM dbofPriceList(DEFAULT, 2/20/2002)

Буде отримано наступний результат:

Code    Price

1047    69500

1049    129500

Якщо ж як першого параметра передається код товару, то скомпільована інструкція SELECT у функції поверне тільки один рядок, відповідну заданому товару: SELECT * FROM dbofPriceList (1001 , 2/20/2002)

Буде отримано наступний результат:

Code    Price

1001    149500

Корельовані користувача функції

Під впроваджених табличних функціях може використовуватися введена в SQL Server 2005 команда APPLY при цьому використовуються різні значення параметрів для різних рядків, що обробляються головним запитом Одного разу, працюючи з версією SQL Server 2000, мені довелося витратити чимало часу, щоб обійти це обмеження, так що мені не залишається нічого іншого, окрім як подякувати розробників SQL Server 2005

Існують дві форми команди APPLY найбільш поширеною з них є CROSS APPLY Це імя може кілька ввести в оману, оскільки вона працює швидше як внутрішнє (INNER JOIN), а не перехресне обєднання (CROSS JOIN) Команда CROSS APPLY обєднує дані з основного запиту з табличним набором даних з користувача функції Якщо функція не вертає даних, то рядок з основного запиту Також не повертається, як у наступному прикладі:

USE СНА2 go

CREATE FUNCTION fEventList2 (@CustomerID INT)

RETURNS Table AS

RETURN (

SELECT dboCustomerTypeName AS Customer,

dboCustomerLastName, dboCustomerFirstName, dboCustomerNickname,

dboEvent_mm_CustomerConfirmDate, dboEventCode, dboEventDateBegin, dboTourName AS Tour, dboBaseCampName, dboEventComment FROM dboTour

INNER JOIN dboEvent

ON dboTourTourlD = dboEventTourlD INNER JOIN dboEvent mm Customer

ON dboEventEventID = dboEvent_mm_CustomerEventID INNER JOIN dboCustomer

ON dboEvent_mm_CustomerCustomerlD = dboCustomerCustomerlD LEFT OUTER JOIN dboCustomerType ON dboCustomerCustomerTypeID

= dboCustomerTypeCustomerTypeID INNER JOIN dboBaseCamp

ON dboTourBaseCampID = dboBaseCampBaseCampID WHERE CustomerCustomerlD = @CustomerID

)

SELECT CLastName, Code, DateBegin, Tour FROM Customer З

CROSS APPLY fEventList2(CCustomerlD)

ORDER BY CLastName

Буде отримано наступний результат:

LastName Code DateBegin                                           Tour

Anderson     01-003 2001-03-16 00:00:00000 Amazon Trek

Anderson     01-006 2001-07-03 00:00:00000 Bahamas Dive

Anderson    01-016 2001-11-16 00:00:00000 Outer Banks Lighthouses

Andrews     01-015 2001-11-05 00:00:00000 Amazon Trek

Andrews     01-012 2001-09-14 00:00:00000 Gauley River Rafting

Andrews     01-014 2001-10-03 00:00:00000 Outer Banks Lighthouses

Bettys          01-013 2001-09-15 00:00:00000 Gauley River Rafting

Bettys          01-015 2001-11-05 00:00:00000 Amazon Trek

Команда OUTER APPLY працює майже так само, як ліве зовнішнє обєднання При такому використанні рядка головного запиту включаються до результуючий набір даних, незалежно від того, повернула Чи впроваджена таблична функція небудь дані

Джерело: Нільсен, Пол 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>

*

*