Методические указания к выполнению лабораторных и курсовой работ ЙошкарОла 2007
 Скачать 0.8 Mb.
|
|
Создание пользовательских функций Как и другие объекты базы данных, пользовательские функции создаются с помощью соответствующей разновидности команды CREATE. Синтаксис ее может варьироваться в зависимости от вида создаваемой пользовательской функции. На операторы Transact-SQL внутри тела пользовательской функции накладываются два ограничения. Во-первых, функции не должны иметь побочных эффектов, т.е. они не могут вносить какие-либо постоянные изменения в объекты в области видимости функции. Например, если в пределах функции создается временная таблица, операторы внутри функции могут добавлять, модифицировать и удалять строки во временной таблице. Однако пользовательская функция не может изменять строки из постоянной таблицы. Второе ограничение состоит в том, что операторы в пользовательской функции не могут вызывать какие-либо недетерминированные функции (встроенные или пользовательские) или обращаться к недетерминированным глобальным переменным, таким как @@TOTAL_ERRORS, которая возвращает количество ошибок, обнаруженных SQL Server с момента запуска сервера. Создание скалярной функции Оператор CREATE для скалярных пользовательских функций имеет достаточно простой синтаксис для функций всех типов; CREATE FUNCTION имя_функции ([список_параметров]} RETURNS тип_данных AS BEGIN [onepaторы_tsql] RETURN (возвращаемое_значение) END Имя_функции должно удовлетворять правилам, действующим для идентификаторов. Хотя список_парометров в описании оператора CREATE занимает иное место, чем список параметров для хранимой процедуры, они имеют аналогичный синтаксис @имя_ параметра тип_данных [= значение_по_умолчанию] Имя__параметра должно соответствовать правилам, принятым для идентификаторов, и начинаться с символа @. Пользовательские функции могут иметь до 1024 входных параметров. Выходные параметры пользовательские функции не поддерживают - единственным значением, возвращаемым функцией, является результат ее выполнения. Заметим, что список_параметров является необязательным, но наличие скобок обязательно. Фраза RETURNS определяет тип возвращаемых функцией значений. Скалярные функции могут возвращать любые скалярные системные типы данных, за исключением timestamp, text, ntextили image. Операторы BEGIN...END, которыми ограничиваются операторы_tsql, составляющие тело функции, являются обязательными, даже если тело функции состоит из одного оператора RETURN. Создание табличных функций Оператор CREATE FUNCTION поддерживает создание двух различных типов табличных функций: подставляемых и многооператорных. Тело подставляемой табличной функции состоит из единственного оператора SELECT, в то время как многооператорная табличная функция может состоять из любого числа операторов Transact-SQL. Синтаксис для подставляемой табличной функции является усеченной разновидностью оператора CREATE FUNCTION. Блок BEGIN...END отсутствует, и нет никаких других операторов, кроме RETURN: CREATE FUNCTION имя_функции (список_параметров) RETURNS таблица AS RETURN (оператор_выборки) Синтаксис оператора CREATE FUNCTION для многооператорной табличной функции сочетает элементы синтаксиса для скалярной и для подставляемой функций: CREATE FUNCTION имя_фунции (список_параметров) RETURNS @локальная_ табличная_переменная TABLE (определение_ таблицы) AS BEGIN оnepaторы_tsql RETURN END Подобно скалярным функциям, в многооператорной табличной функции команды Transact-SQL располагаются внутри блока BEGIN ...END. Поскольку блок может содержать несколько операторов SELECT, во фразе RETURNS вы должны явно определить таблицу, которая будет возвращаться. Поскольку оператор RETURN в многооператорной табличной функции всегда возвращает таблицу, заданную во фразе RETURNS, он должен выполняться без аргументов - например, RETURN, а не RETURN @myTable. Применение пользовательских функций Синтаксис вызова скалярных функций схож с синтаксисом, используемым для встроенных функций Transact-SQL: имя_владельца. имя_функции( [список_параметров]) Имя_владелъца для скалярной функции является обязательным. Вы не можете использовать синтаксис с именованными параметрами (например, @имя_параметра = значение), а также не можете не указывать (опускать) параметры, но вы можете применять ключевое слово DEFAULT для указания значения по умолчанию, как вы это делаете для хранимых процедур. SQL Server предоставляет несколько встроенных пользовательских функций, не относящихся к обычным встроенным функциям. Они начинаются с fn_, а при их вызове используется специальный синтаксис ::имя_функции([список_параметров]). Символы :: заменяют имя_владелъца и указывают на то, что функция является встроенной пользовательской функцией. Для скалярной функции вы также можете использовать оператор EXECUTE: EXECUTE @возвращаемое_значение = имя_функции (список_параметров) Если вы используете оператор EXECUTE для пользовательской функции, вам не нужно указывать имя_владельца. В этом синтаксисе вы можете использовать именованные параметры: EXECUTE @возвращаемое_значение = имя_фунции @параметр = значение [, @параметр = значение [,...]] Если вы используете именованные параметры, они не обязательно должны следовать в том порядке, в котором указаны в объявлении функции, но вам необходимо указать все параметры; нельзя опускать ссылку на параметр для использования значения по умолчанию. Для табличных пользовательских функций как подставляемых, так и многооператорных должен всегда использоваться тот же синтаксис, что и для встроенных функций: имя_ фунции( [ список_ параметров]) Имя_владелъца здесь указывать не требуется, но необходимо включить все определенные параметры, как и при вызове любой пользовательской функции. Применение пользовательских функций в операторах Transact-SQL Скалярные пользовательские функции могут использоваться везде, где допустимо использовать тип данных, который они возвращают. Табличные пользовательские функции могут быть использованы только во фразе FROM оператора SELECT. Если фраза SELECT находится внутри оператора DECLARE CURSOR, курсор должен иметь тип STATIC и READ_ONLY. Применение пользовательских функций в определениях таблиц Пользовательские функции могут быть использованы в определениях таблиц, предоставляемых владельцем таблицы, но на параметры, используемые в этих функциях, накладываются некоторые ограничения. При использовании в качестве типа данных для вычисляемого столбца параметры пользовательской функции должны быть либо другими столбцами в таблице, либо константами. Это справедливо и в том случае, если пользовательская функция используется в качестве проверочного ограничения типа CHECK. Если пользовательская функция используется как значение по умолчанию для столбца, параметры должны быть константами. Пример создания функции табличного типа CREATE FUNCTION itog() RETURNS TABLE AS RETURN (SELECT * FROM table1 where param>17) Пример использования функции SELECT * FROM itog() Варианты заданий Схема таблицы СТУДЕНТ: • идентификатор зачетки • фамилия и инициалы студента • специальность • группа • дата рождения • наличие стипендии (имеется/не имеется) • адрес проживания • средний балл зачетки Ограничение уникальности: идентификатор зачетки. Проверочные ограничения: а) Код группы должен иметь следующую структуру: <цифра><цифра><буква><буква><цифра>; б)средний балл зачетки должен быть в интервале [2,5]. Спецификация представления: представляемая таблица содержит идентификатор зачетки, фамилию и инициалы студента, а также средний балл зачетки для студентов, получающих стипендию. Спецификация курсора: результирующая таблица включает фамилию и инициалы студента, группу и адрес проживания для студентов, имеющих средний балл зачетки более 4.5. Хранимая процедура: входной параметр – идентификатор зачетки; выходные параметры - фамилия и инициалы, специальность и группа студента. Схема таблицы ЭКЗАМЕН: • название предмета • фамилия и инициалы студента • фамилия и инициалы преподавателя • должность преподавателя • дата сдачи экзамена • номер аудитории • оценка • сложность предмета Ограничение уникальности: название предмета, фамилия и инициалы студента. Проверочные ограничения: а) должность преподавателя должна быть одной из следующего списка: ассистент, старший преподаватель, доцент, профессор; б) сложность предмета должна быть в интервале [0,1]. Спецификация представления: представляемая таблица содержит название предмета, фамилию и инициалы студента, а также экзаменационную оценку для тех экзаменов, которые принимают профессора. Спецификация курсора: результирующая таблица включает все сведения об экзаменах, сданных на оценку “отлично”. Хранимая процедура: входной параметр – фамилия и инициалы студента; возвращаемое значение – количество экзаменационных предметов, которые должен сдать этот студент. Схема таблицы ВОЕННОСЛУЖАЩИЕ: • номер военного билета • фамилия и инициалы • дата рождения • род войск • воинское звание • оклад • рост • вес • номер противогаза • наличие водительских прав (имеются/не имеются) Ограничение уникальности: номер военного билета. Проверочные ограничения: а) номер военного билета должен состоять из шести цифр; б) номер противогаза должен быть цифрой 1,2 или 3. Спецификация представления: представляемая таблица содержит номер военного билета, фамилию и инициалы, род войск военнослужащих ростом более 180 см. Спецификация курсора: результирующая содержит фамилию и инициалы, воинское звание, номер противогаза военнослужащих, имеющих водительские права. Хранимая процедура: входной параметр – номер военного билета; выходные параметры - фамилия и инициалы, дата рождения и воинское звание военнослужащего. Схема таблицы КОМПЬЮТЕР: • марка компьютера • страна сборки • процессор • объем оперативной памяти • объем внешней памяти • быстродействие • наличие мыши (имеется/не имеется) • марка монитора • цена • дата выпуска Ограничение уникальности: марка компьютера, страна сборки. Проверочные ограничения: а) объем оперативной памяти должен быть в интервале [2,128] Мбайт; б) Дата выпуска должна быть не больше текущей даты. Спецификация представления: представляемая таблица содержит марку компьютера, страну сборки и цену для компьютеров, имеющих объем оперативной памяти более 8 Мбайт. Спецификация курсора: результирующая таблица совпадает с базовой. Хранимая процедура: входные параметры – минимально и максимально допустимая цена компьютера; возвращаемое значение – количество компьютеров, которые можно купить в пределах минимальной и максимальной цены. Схема таблицы УЧЕБНЫЙ ПЛАН: • код специальности • название дисциплины • семестр • дата начала семестра • общее количество часов • наличие курсового проекта (имеется/не имеется) • формы отчетности (экзамены, зачеты и т.д.) Ограничение уникальности: код специальности, название дисциплины, семестр. Проверочные ограничения: а) код специальности должен иметь следующую структуру: <цифра><цифра>.<цифра><цифра>; б) семестр должен быть или осенний или весенний. Спецификация представления: представляемая таблица содержит код специальности, семестр и название дисциплин, для которых предусмотрен курсовой проект. Спецификация курсора: результирующая таблица содержит все сведения о дисциплинах весеннего семестра. Хранимая процедура: входные параметры – код специальности, название дисциплины, семестр; выходной параметр - форма отчетности. Схема таблицы ПОСТАВКИ ТОВАРОВ: • название фирмы-поставщика • название фирмы-потребителя • товарный кредит (да/нет) • название товара • количество единиц товара • вес единицы товара • цена единицы товара • платежные реквизиты (адрес и номер расчетного счета) • дата отгрузки Ограничение уникальности: название фирмы-поставщика, название фирмы-потребителя. Проверочные ограничения: а) поставляемыми товарами являются холодильники, пылесосы и утюги; б) количество поставляемых единиц товара не должно превышать 100 штук. Спецификация представления: представляемая таблица содержит сведения о товарах. Спецификация курсора: результирующая таблица содержит название фирмы-поставщика, название фирмы-потребителя и название товара для поставок, в которых используется товарный кредит. Хранимая процедура: входные параметры – название фирмы-поставщика, название товара; возвращаемое значение – общее количество единиц товара, которое поставляется введенной фирмой-поставщиком в другие фирмы. Схема таблицы АВТОТРАНСПОРТ: • государственный номер • тип (автобус, самосвал, тягач, джип) • марка • год изготовления • грузоподъемность или вместимость • расход горючего на 100 км • пробег к текущему техосмотру • дата проведения последнего техосмотра • успешность техосмотра (положительная/отрицательная) Ограничение уникальности: государственный номер. Проверочные ограничения: а) государственный номер имеет следующую структуру: <буква><цифра><цифра><цифра><буква><буква>; б) автотранспорт может быть следующих типов: автобус, самосвал, тягач, джип. Спецификация представления: представляемая таблица содержит государственный номер, тип и марку автотранспорта, успешно прошедшего техосмотр. Спецификация курсора: результирующая таблица содержит тип автотранспорта, грузоподъемность (вместимость) и дату проведения последнего техосмотра для автотранспорта, выпущенного за последние пять лет. Хранимая процедура: входной параметр – государственный номер; выходные параметры – тип, марка и грузоподъемность автотранспорта. Схема таблицы ФАКУЛЬТЕТ: • название факультета • фамилия и инициалы декана • телефон деканата • дата основания факультета • число выпускающих кафедр • число обучаемых студентов • наличие иностранных студентов (имеются/не имеются) • удельный вес преподавателей с учеными степенями Ограничение уникальности: название факультета. Проверочные ограничения: а) телефон деканата должен иметь следующую структуру: 63-<цифра><цифра>-<цифра><цифра>; б) удельный вес преподавателей с учеными степенями должен быть в интервале [0,1]. Спецификация представления: представляемая таблица содержит название факультета, фамилию и инициалы деканата, а также число выпускающих кафедр для факультетов, на которых учатся иностранные студенты. Спецификация курсора: результирующая таблица содержит фамилию и инициалы декана, телефон и дату основания деканата. Хранимая процедура: входной параметр – число обучаемых студентов; возвращаемое значение – количество факультетов, в которых общее число обучаемых студентов больше введенного входного параметра. Схема таблицы УСПЕВАЕМОСТЬ: • факультет • специальность • дисциплина • общее количество студентов • количество сдававших студентов • удельные веса отличных, хороших, удовлетворительных и неудовлетворительных оценок • дата начала экзаменационной сессии Ограничение уникальности: факультет, специальность, дисциплина. Проверочные ограничения: а) количество сдававших студентов не должно быть больше количества студентов; б) сумма удельных весов отличных, хороших, удовлетворительных и неудовлетворительных оценок должна быть равной 1. Спецификация представления: представляемая таблица содержит названия специальности и дисциплины, а также количество сдававших студентов для факультета вычислительной техники. Спецификация курсора: результирующая таблица содержит все сведения об успеваемости на специальности “Вычислительные машины, комплексы, системы и сети”. Хранимая процедура: входные параметры – факультет, специальность, дисциплина; выходные параметры – общее количество студентов и количество сдававших студентов. 10) Схема таблицы ЖИВОТНОЕ: • название животного • класс животного • максимальный вес животного • минимальный вес животного • средний вес животного • окраска • место обитания • дата занесения в Красну книгу • продолжительность жизни • летательные способности (летает/не летает) Ограничение уникальности: название животного. Проверочные ограничения: а) средний вес животного должен быть больше минимального веса и меньше максимального веса; б) дата занесения в Красную Книгу не должна быть больше текущей даты. Спецификация представления: представляемая таблица содержит сведения о весе животных. Спецификация курсора: результирующая таблица содержит название, класс и средний вес животных, способных летать. Хранимая процедура: входной параметр – продолжительность жизни; возвращаемое значение – количество животных, продолжительность жизни которых больше входного параметра. 11) Схема таблицы ФИРМА: • название фирмы • организационно-правовая форма (ООО, ОАО, ТОО и т.д.) • юридический адрес • дата учреждения • количество работников • число работников, имеющих водительские права • средний оклад работников • средний возраст работников • направления деятельности • задолженность по налогам (имеется/не имеется) Ограничение уникальности: название фирмы. Проверочные ограничения: а) число работников, имеющих водительские права не должно превышать общее количество работников; б) средний возраст работников не должен превышать 35 лет. Спецификация представления: представляемая таблица включает название фирмы и соответствующие обобщенные сведения о работниках этой фирмы. Спецификация курсора: результирующая таблица содержит название фирмы, юридический адрес и количество работников, причем удельный вес работников фирмы, имеющих водительские права, превышает 60%. Хранимая процедура: входной параметр – название фирмы; выходные параметры – организационно-правовая форма, юридический адрес, дата учреждения фирмы. 12) Схема таблицы ТЕЛЕВИЗОР: • марка телевизора • страна сборки • тип кинескопа • размер по диагонали • системы цветности • число каналов • наличие телетекста (имеется/не имеется) • звук (моно/.стерео) • выходная мощность • вес • потребляемая мощность • дата продажи Ограничение уникальности: марка телевизора, страна сборки. Проверочные ограничения: а) система цветности может быть следующих типов: PAL/SECAM, B/G, D/K, H, I, M, NTSC, VIT); б) число каналов должно быть не меньше 40. Спецификация представления: представляемая таблица содержит марку телевизора, страну сборки и потребляемую мощность для телевизоров, имеющих телетекст. Спецификация курсора: результирующая таблица совпадает с базовой. Хранимая процедура: входной параметр – тип кинескопа; возвращаемое значение – количество телевизоров, имеющих кинескоп данного типа. Лабораторная работа N 3 |