17241

ТАБЛИЧНЫЕ ПЕРЕМЕННЫЕ

Лекция

Информатика, кибернетика и программирование

Лекция №5_1 ТАБЛИЧНЫЕ ПЕРЕМЕННЫЕ В Microsoft SQL Server 2000 появился новый тип table используемый в языке TransactSQL. Тип table подобен временным таблицам. Тип table можно использовать только для определения локальных переменных и возврата значения из функции пользователя. Основно...

Русский

2013-06-30

53 KB

6 чел.

Лекция №5_1

ТАБЛИЧНЫЕ ПЕРЕМЕННЫЕ

В Microsoft SQL Server 2000 появился новый тип table, используемый в языке Transact-SQL. Тип table подобен временным таблицам.

Тип table можно использовать только для определения локальных переменных и возврата значения из функции пользователя.

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

Объявление

При объявлении переменной переменная table инициализируется как пустая таблица. Если выполняется оператор SELECT сразу после объявления табличной переменной, то результатом будет пустой набор строк.

Не допускается присвоение одной табличной переменной другой.

Не допускается создание индексов на табличных переменных за исключением индексов, которые создаются ограничениями PRIMARY KEY и UNIQUE.

Declare @Mytable TABLE (№зк int Primary Key, ФИО char (30))

Insert into @Mytable
Select №
зк, ФИО from Список where ФИО like 'B*'
Select * from @Mytable

ФУНКЦИИ ПОЛЬЗОВАТЕЛЯ

Функция пользователя (UDF) представляет собой модуль Transact-SQL, который принимает один или более входных аргументов и рассчитывает возвращаемое значение.

UDF может содержать как операторы управления ходом выполнения программы, так и операторы DML.

Определение функции пользователя

Прежде чем создать UDF инструкцией CREATE FUNCTION, необходимо определить следующие характеристики:

  •  имя UDF,
  •  схема (квалификатор),
  •  число и типы данных входных параметров,
  •  типы возвращаемых значений.

Для изменения функции используется инструкция ALTER FUNCTION, для удаления – DROP FUNCTION.

Нельзя использовать оператор ALTER FUNCTION для преобразования типов функций.

Типы операторов, которые могут использоваться в функции:

  •  Оператор DECLARE.
  •  Присвоение значений объектам, локальным относительно функции, с помощью опции SET или SELECT.
  •  Операторы управления (например, IF..ELSE, WHILE, RETURN, GOTO, BREAK и CONTINUE).
  •  Оператор SELECT, содержащий список выбора с выражениями, которые присваивают значения локальным переменным функции.
  •  Операторы UPDATE, INSERT и DELETE.
  •  Операторы EXECUTE, для вызова хранимых процедур.

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

Детерминизм функций

Функции могут быть детерминированными и недетерминированными. Функция является детерминированной, если она всегда возвращает одно и то же значение для одних и тех же значений входных аргументов.

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

Функция GETDATE не является детерминированной, поскольку она всегда вызывается с одним и тем же аргументом, но возвращает все время разные значения при каждом выполнении.

Типы функций

SQL Server 2000 поддерживает три типа UDF:

  •  Скалярнозначные функции
  •  Однострочные (inline) табличнозначные функции
  •  Многооператорные табличнозначные фунции

Скалярнозначные функции

Пользовательская скалярная функция возвращает в качестве ответа единственное значение при каждом вызове функции. Функции являются скалярнозначными, если предложение возвращающее значение – RETURNS определяет один из скалярных типов данных. Скалярнозначные функции могут быть определены с помощью нескольких операторов Transact-SQL. Например:

CREATE FUNCTION CubicVolume
(@CubeLength decimal (4,1), @CubeWidth decimal (4,1), @CubeHeight decimal (4,1))
RETURNS decimal (12,3) --
тип данных возвращаемого значения.
AS
BEGIN
     RETURN (@CubeLength * @CubeWidth * @CubeHeight)
END

В данном примере предложение RETURNS определяет, что функция будет возвращать скалярное значение типа decimal. Предложение RETURNS может использовать любой из скалярных типов данных, которые поддерживает SQL Server, за исключением timestamp, text, ntext или image.

Для скалярнозначных функций необходимо использовать оператор RETURN с аргументом. Значение аргумента является возвращаемым значением функции. Тип данных аргумента будет неявно преобразован к типу возвращаемого значения функции.

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

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

Select dbo.CubicVolume(12.2,10.6,10.0) или

Print dbo.CubicVolume(12.2,10.6,10.0)

Однострочные табличнозначные функции

Для однострочных табличнозначных функций предложение RETURNS задает TABLE (таблицу) без указания списка столбцов. Функции этого типа определяются единственным оператором SELECT, представляющим тело функции. Столбцы, включающие типы данных таблицы, возвращаемой функцией, определяются списком SELECT, который определяет функцию. Например:

CREATE FUNCTION fn_CustomerNamesInRegion
      (@RegionParameter
 char (30))
RETURNS TABLE
AS
RETURN 
     (
     SELECT CustomerID, CompanyName 
         FROM Northwind.dbo.Customers 
         WHERE Region = @RegionParameter 
     )

Ниже приведен пример вызова рассмотренной выше однострочной табличнозначной функции:

SELECT * FROM fn_CustomerNamesInRegion ('WA')

Многооператорные табличнозначные функции

Если предложение RETURNS задает тип TABLE с определением столбцов и их типов данных, функция является многооператорной табличнозначной функцией. Например:

CREATE FUNCTION LargeOrderShippers 
    (@FreightParm money)
RETURNS @OrderShipperTab TABLE 
    ( 
    ShipperID int, 
    ShipperName char (80), 
    OrderID int, 
    ShippedDate datetime, 
    Freight money 
    )
AS
BEGIN 
    INSERT @OrderShipperTab 
        SELECT S.ShipperID, S.CompanyName, 
            O.OrderID, O.ShippedDate, O.Freight 
        FROM Shippers AS S INNER JOIN Orders AS O 
            ON S.ShipperID = O.ShipVia 
        WHERE O.Freight > @FreightParm 
    RETURN
END

Предложение RETURNS в данном примере определяет локальную возвращаемую переменную типа table с именем @OrderShipperTab, а также определяет структуру таблицы с определениями столбцов.

Операторы в теле функции вставляют строки в переменную @OrderShipperTab, создавая табличный набор строк, который возвращается функцией. При этом оператор возврата RETURN не имеет аргумента, значение возвращаемой переменной функции возвращается как значение функции.

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

SELECT *
FROM LargeOrderShippers ($500)

Вычисляемые столбцы

Можно вызывать скалярнозначную функцию в вычисляемых столбцах. Однако аргументы при этом могут содержать только константы и другие столбцы таблицы.

Можно использовать вычисляемый столбец в предложении WHERE.

Предположим, что имеется пользовательская скалярная функция, которая вычисляет премию на основе зарплаты и комиссионных, которые получает сотрудник. Зарплата и комиссионные находятся в таблице Employees.

Можно использовать запрос для получения списка сотрудников, которые получили премию больше $1000.00 в следующем виде:

Select EmpId, Fname, Lname from Employees where dbo.BONUS(Salary,Comm) > 1000

Если вычисляемый столбец создается с помощью пользовательской функции, которая рассчитывает премию (BONUS)

CREATE TABLE [Employees] 
       (
       [EmpId] [Char] (9) NOT NULL, 
       [Salary] [Decimal](10,2),
       [Comm] [Decimal](10,2), 
       BONUS as dbo.BONUS(Salary,Comm)
       )

то предыдущий запрос примет вид

Select EmpId, Fname, Lname from Employees where BONUS > 1000


 

А также другие работы, которые могут Вас заинтересовать

21399. ИНДИВИДУАЛИЗАЦИЯ ЮЛ 19.46 KB
  на практике он просто запутывает ГО когда регистрировали кооперативы то надо было его место нахождения тогда и появился юридический адрес они абонировали почтовый ящик Третий способ: не пользуются граждане Товарный знак знак обслуживания: в соответствии со статьёй 1477 товарный знак это обозначение служащее для индивидуализации товаров ЮЛ а знак обслуживания это обозначение служащее для индивидуализации выполнения работ и оказываемых услуг ЮЛ т. сочетание слов Раковая шейка Изобразительные шестеренка с буквами КЗ Объемный знак...
21400. СПОСОБЫ ОБРАЗОВАНИЯ ЮЛ 20 KB
  если все требования закона соблюдены отказать нельзя Если он случится можно обжаловать в суде УЧРЕДИТЕЛЬНЫЕ ДОКУМЕНТЫ ЮЛ Статья 52 виды: Устав: наиболее распространенный только на основе его действуют кооперативы и АО Учредительный договор: полное товарищество Учредительный договор и устав: ООО но теперь изменение что только на основе устава Общее положение о данном виде ЮЛ: закон о среднем специальном образовании эти учреждения действуют только на основании его Единственным документом д. на одной стороне активы а на другой пассивы; если...
21401. ОРГАНИЗАЦИОННО ПРАВОВЫЕ ФОРМЫ КО 17.16 KB
  Три формы: ООО Общество с дополнительной ответственностью АО Объединение в первую очередь имущества а вовторую лиц Не требует личного участия участника в деятельности общества Личность не имеет особого значения Участниками м. любые субъекты ГП кроме гос органов Участники общества не несут ответственности по долгам общества За рубежом общества тоже получили широкое распространение Система хоз тов и обв построена таким образом что чем...
21402. ПОЛНОЕ ТОВАРИЩЕСТВО 20.06 KB
  Полное товарищество это переходная форма от индивидуальной к коллективной предпринимательской деятельности т. участником только одного полного товарищества Фирменное наименование должно содержать либо имена наименования всех товарищей и слова полное товарищество либо имена наименования нескольких участников и слова компания и слова полное товарищество В качестве учредительного документа...
21403. ОБЩЕСТВО С ОГРАНИЧЕННОЙ ОТВЕТСТВЕННОСТЬЮ 22.83 KB
  Риск предпринимательской деятельности уменьшается до величины вклада который вносится пр создании ЮЛ; другое имущество риску не подвергается Возможность реально влиять на деятельность этого ЮЛ и его результаты Круг участников ограничен лицами которые как правило знают и доверяют друг другу До последнего времени: любой участник мог в любое время выйти без согласия и потребовать выдел своей доли Недостаток: нестабильная имущественная база т. весьма не удобно для кредиторов и остальных участников ГО что может в любое время участники...
21404. АКЦИОНЕРНОЕ ОБЩЕСТВО 23.22 KB
  Понятие: это КО созданная в результате объединения имущества нескольких лиц которые не несут ответственности по обязательствам этой организации и имеют в собственности акции удостоверяющие их обязательственное право требования по отношению к этой КО
21405. ПРОИЗВОДСТВЕННЫЙ КООПЕРАТИВ 18.52 KB
  Число членов не меньше 5 не допускается ситуация когда 1 член т. выделяется имущество из которого нельзя выделять паи Прибыль ПК распределяется в соответствии с трудовым участием членов Все основывается на трудовом участии Законодатель учитывает также и в размере паев: уставом м. учитывается и размер пая Организационное единство: обеспечивается уставом ППК; органы управления: общее собрание членов ПК исключительная компетенция: те вопросы которые...
21406. НЕКОММЕРЧЕСКИЕ ОРГАНИЗАЦИИ 23.16 KB
  имуществом принадлежащим ПК; если земельные участки то могут вернуть соответствующее недвижимое имущество; переход пая по наследству: в случае смерти его наследники м. одно лицо Ревизионная комиссия: следит за финансовой отчетностью Имущество ПК: собственник; имущество формируется за счет взносов и деятельности самого ПК если она разрешена уставом; формируется паевой фонд из паевых взносов Ликвидация: общие положения и специальные основания: если убытки не покрыты за счет доп. изменены Уклонение от целей предусмотренных уставом Имущество...
21407. ГОСУДАРСТВЕННЫЕ И МУНИЦИПАЛЬНЫЕ УНИТАРНЫЕ ПРЕДПРИЯТИЯ 19.96 KB
  Понятие: признается КО не наделенная правом собственности на имущество закрепленное за ней собственником статья 2 ФЗ собственником остается учредитель этого УП За УП имущество закрепляется на праве хоз ведения или на праве оперативного управления это ограниченные вещные права Такое существует только в нашей стране Имущество УП является не делимым и не может делиться на доли паи акции и распределяться между работниками предприятий только один собственник Может основываться только на гос или муниципальной собственности т. данная форма...