Проектирование многопользовательской информационной системы

As

Select   /*Указываем, какие поля будут выведены*/

услуги.Номер_услуги,услуги.Услуга,услуги.Стоимость_услуги

From услуги    /*Из какой таблицы*/     

where услуги.Стоимость_услуги <=(select avg(Стоимость_услуги) From услуги)      /*Выбор услуги, где стоимость меньше либо равна средней стоимости услуг*/

  

/*12. Вывод информации о заказах, которые зданы*/

CREATE VIEW Заказы_которые_зданы   /*Указываем имя представления*/

AS

SELECT                      /*Указываем, какие поля будут выведены*/

 заказы.Номер_заказа, заказы.Адрес_объекта, заказы.Дата_здачи

FROM заказы                /*Из какой таблицы*/                        

WHERE заказы.Дата_здачи< Getdate()/*Выбор заказа у которого срок здачи меньше текущей даты*/

 

/*13. Вывод информации о заказах, которые будут зданы через 30 дней*/

CREATE VIEW Объекты_которые_будут_зданы_через_30_дней   /*Указываем имя представления*/

AS

SELECT                   /*Указываем, какие поля будут выведены*/  

заказы.Номер_заказа, заказы.Адрес_объекта, заказы.Дата_здачи

FROM заказы           /*Из какой таблицы*/                                           

WHERE заказы.Дата_здачи<DateAdd(day,30,заказы.Дата_здачи) and заказы.Дата_здачи>Getdate()   /*Выбор заказа у которого срок здачи истечет через 30 дней*/     

 

/*14. Вывод информации о клиентах,у  которых заказ более 60 дней*/

CREATE VIEW Клиенты_с_заказом_больше_60_дней  /*Указываем имя представления*/

AS

SELECT          /*Указываем, какие поля будут выведены*/              

клиенты.Номер_клиента,клиенты.Дата_заказа,клиенты.Ответственное_лицо

FROM  клиенты          /*Из какой таблицы*/                                                                    

WHERE  DateDiff(day,клиенты.Дата_заказа,Getdate())>60    /*Выбор клиента с заказом более 60 дней*/         

 

/*15. Вывод информации о клиентах*/

CREATE VIEW Информация_о_клиентах   /*Указываем имя представления*/

AS

SELECT               /*Указываем, какие поля будут выведены*/                      

клиенты.Номер_клиента,клиенты.Ответственное_лицо,

День=Day( клиенты.Дата_заказа),Месяц=Month( клиенты.Дата_заказа),Год=Year( клиенты.Дата_заказа)

FROM клиенты      /*Из какой таблицы*/                                                                                                                     

 

 

 

3.2 T-SQL-определения триггеров

/*2. Триггер, который  записывает при добавлении записей  в таблицу «услуги» в отдельную  таблицу информацию о дате  добавления, пользователе.*/

CREATE TRIGGER proverka_Uslugi /*Обьявляем имя триггера*/

ON услуги /*Указываем имя таблицы, с которой будет связан триггер*/

FOR INSERT/*Указываем операцию, на которую будет срабатывать триггер

(на  вставку)*/

AS

INSERT INTO

/* вставка записи в  таблицу TrUskugi */

TrUskugi(Номер_услуги,Услуга,Стоимость_услуги,Имя_пользователя,Дата_

добавления)

/* выбираем, какие записи  вставить в таблицу */

SELECT Номер_услуги,Услуга,Стоимость_услуги,

SYSTEM_USER,

getdate()

FROM inserted

 

/*Перед созданием такого  триггера необходимо создать  таблицу TrUskugi,

куда будет производится запись:*/

CREATE TABLE TrUskugi /*Обьявляем имя таблицы*/ (

[Номер_услуги][int] NULL , /*Обьявляем полей таблицы*/

[Услуга] [varchar](20) NOT NULL , /*Обьявляем полей таблицы*/

[Стоимость_услуги][int] NULL , /*Обьявляем полей таблицы*/

[Имя_пользователя] [varchar] (50) NULL , /*Обьявляем полей таблицы*/

[Дата_добавления] [datetime] NULL /*Обьявляем полей таблицы*/

) ON [PRIMARY]

 

Аналагичные хранимые процедуры:

proverka_Sakazi 3 - Триггер, который записывает при добавлении записей в таблицу «заказы» в отдельную таблицу информацию о дате добавления, пользователе.

proverka_Postav 4 - Триггер, который записывает при добавлении записей в таблицу «поставщики» в отдельную таблицу информацию о дате добавления, пользователе.

proverka_Mater 5 - Триггер, который записывает при добавлении записей в таблицу «услуги» в отдельную таблицу информацию о дате добавления, пользователе.

 

/*1. Триггер, запрещающий  ввод значения в поле «дата_заказа», таблицы

Заказы если оно превышает номер текущего года.*/

create TRIGGER proverka_Clientov/*Обьявляем имя триггера*/

ON клиенты/*Указываем имя таблицы, с которой будет связан триггер*/

FOR INSERT

AS

DECLARE @@t  int/*Объявляем переменную*/

Set @@t=5/*Инициализируем переменную*/

IF NOT EXISTS (SELECT * FROM клиенты, inserted

WHERE клиенты.Дата_заказа = inserted.Дата_заказа) /*Проверка разницы

между текущей датой  и датой рождения заказа*/

Set @@t=0

IF EXISTS (SELECT * FROM клиенты, inserted

WHERE  inserted.Дата_заказа>GETDATE())

Set @@t=0 /*Если условие выполняется, то обнуляем переменную*/

If @@t=0/*Если ошибка*/

BEGIN

PRINT 'Неверно введена  дата заказа'

ROLLBACK TRANSACTION

END

 

/*6. Триггер, который записывает при удаление записей из таблицы «услуги» в отдельную таблицу информацию об удаленных записях.*/

CREATE TRIGGER T_uslugi_1

ON услуги /*Связываем с таблицей*/

FOR DELETE

AS

INSERT INTO uslugi_ud  /*Указываем  таблицу, куда будет производиться 

запись*/

(Номер_услуги,Услуга,Стоимость_услуги) /*Перечисляем поля таблицы*/

SELECT /*Перечисляем вносимые  значения*/

Номер_услуги,Услуга,Стоимость_услуги

FROM deleted /*deleted – временная  таблица, куда заносятся удаляемые 

данные*/

 

/*Перед созданием такого  триггера необходимо создать таблицу

sotrudnik_ud, куда будет производится запись:*/

CREATE TABLE sotrudnik_ud (

[Номер_услуги][int] NULL ,

[Услуга] [varchar](20) NOT NULL ,

[Стоимость_услуги][int] NULL

) ON [PRIMARY]

 

Аналагичные хранимые процедуры:

T_zakazy_1 7 - Триггер, который записывает при удаление записей из таблицы «заказы» в отдельную таблицу информацию об удаленных записях.

T_postavchik_1 8 - Триггер, который записывает при удаление записей из таблицы «поставщики» в отдельную таблицу информацию об удаленных записях.

T_material_1 9 - Триггер, который записывает при удаление записей из таблицы «материалы» в отдельную таблицу информацию об удаленных записях.

T_sotrudnik_1 10- Триггер, который записывает при удаление записей из таблицы «сотрудники» в отдельную таблицу информацию об удаленных записях.

 

3.3. T-SQL-определения хранимых процедур

 

/*1. Вставка в таблицу  Клиенты.*/

Create PROCEDURE VstavkaKlienti /*Обьявляем имя хранимой

процедуры*/

(

@nom int, /*Обьявляем полей таблицы*/

@datzakaz datetime, /*Обьявляем полей таблицы*/

@fio varchar(150) /*Обьявляем полей таблицы*/

)

AS INSERT INTO клиенты VALUES (@nom, @datzakaz , @fio);

/*Производим вставку клиента в таблицу*/

 

Аналагичные хранимые процедуры:

VstavkaMaterial 3- Вставка в таблицу Материалы

VstavkaPostavchik 4- Вставка в таблицу Поставщики

VstavkaSotr 5- Вставка в таблицу Сотруники

VstavkaUslugi 2- Вставка в таблицу Услуги

 

/*6. Удаление Клиентов  из таблицы Клиенты по номеру .*/

create PROCEDURE DeleteKlient/*Обьявляем имя хранимой процедуры*/

@number int/*Индификация поля по которому будет производится

удаления*/

AS

IF not EXISTS  (SELECT * FROM услуги_клиенты WHERE 

Номер_клиента=@number)

DELETE

FROM клиенты

WHERE

Номер_клиента=@number /*Поля для в вода клиента*/

 

Аналагичные хранимые процедуры:

DeleteMaterial 8- Удаление Материалов из таблицы Материалы по номеру

DeletePost 9- Удаление Поставщика из таблицы Поставщики по его номеру

DeleteSotr 10- Удаление Сотрудника из таблицы сотрудники по его номеру

табельному номеру

DeleteUslugi 7- Удаление услуги по ее номеру

 

/*11. Обновление таблицы  Сотрудники по ид_номеру меняем  оклад.*/

create  PROCEDURE UpdateSotr/*Обьявляем имя хранимой процедуры*/

@nom int, /*Индификация поля по которому будет производится

удаления*/

@st int/*Индификация поля по которому будет производится удаления*/

AS

IF EXISTS (SELECT * FROM Сотрудники WHERE

Табельный_номер=@nom)

UPDATE Сотрудники

SET Оклад=@st /*Имя поля которое будем менять*/

WHERE Табельный_номер=@nom/*Имя поля по которому будет

производится выборка*/

 

Аналагичные хранимые процедуры:

UpdateBrig 12- Обновление таблицы Бригады по номеру бригады меняем

количество человек

UpdateKlient 13- Обновление таблицы Клиенты по номеру клиента меняем

отсветственное лицо

UpdateSakaz 14- Обновление таблицы Заказаы по номеру заказа меняем

дату заказа

UpdateUslug 15- Обновление таблицы Услуги по табельный номер

сотрудника меняем оклад

 

/*16.Вывод информации о сотрудниках.*/

create PROCEDURE VivodSotr/*Обьявляем имя хранимой процедуры*/

@nom int/*Индификация поля по которому будет производится удаления*/

AS

SELECT

Сотрудники.Табельный_номер,Сотрудники.ФИО,Сотрудники.Должность,

Сотрудники.Номер_бригады,Бригады.ФИО_бригадира/*Имя полей

которые будут показаны в отчете*/

FROM Бригады,Сотрудники

WHERE (Табельный_номер=@nom) AND (Сотрудники.Номер_бригады 

=Бригады.Номер_бригады)  /*Связывание двух таблиц по полям и выборка

по номеру*/

 

Аналагичные хранимые процедуры:

VivodKlient 18 - Выводит по номеру клиента информацию о клиенте из

таблицы Клиенты

VivodPost 19- Выводит по номеру поставщика информацию о поставщике

из таблицы Поставщики

VivodMater 20- Выводит по номеру материала информацию о материале из

таблицы Материалы

 

/*17.Вывод информации  о заказах по дате заказа.*/

create PROCEDURE VivodSakaz/*Обьявляем имя хранимой процедуры*/

@dat datetime/*Индификация поля по которому будет производится

удаления*/

AS

SELECT

заказы.Номер_заказа,заказы.Дата_здачи,услуги_заказы.Номер_услуги,услу

ги.Услуга,услуги.Стоимость_услуги/*Имя полей которые будут показаны

в отчете*/

FROM заказы,услуги_заказы,услуги

WHERE (Дата_здачи=@dat) AND (заказы.Номер_заказа

=услуги_заказы.Номер_заказа)  AND (услуги_заказы.Номер_услуги                                  

=услуги.Номер_услуги)  /*Связывание двух таблиц по полям и выборка по

дате здачи объекта*/

3.4. T-SQL-определения  курсоров

1./*Курсор для просмотра  списка клиентов и подсчёт  их количества*/

DECLARE infklient CURSOR

GLOBAL /*Создается глобальный  курсор, который будет существовать  до 

закрытия данного соединения*/

SCROLL /*Создает прокручиваемый  курсор*/

KEYSET  /*Будет создан  ключевой курсор*/

TYPE_WARNING

FOR

SELECT/*Какие поля  будут  показаны в курсоре*/

клиенты.Номер_клиента,клиенты.Дата_заказа,клиенты.

Ответственное_лицо

FROM клиенты /*Из какой  таблицы выбираются данные*/

FOR READ ONLY /*Только для  чтения*/

open global infklient /*открываем  глобальный курсор*/

 FETCH NEXT FROM infklient

DECLARE /*объявляем переменную*/

 @@Count int

SET @@Count =@@CURSOR_ROWS /*присваиваем  ей число рядов 

курсора*/

Select @@Count  /*выводим  результат на экран*/

CLOSE infklient /*закрываем курсор*/

DEALLOCATE infklient/*освобождаем  курсор*/

 

2./*Курсор для просмотра  списка сотрудников и их табельного номера*/

DECLARE udalSotrudnik CURSOR

GLOBAL/*Создается глобальный  курсор, который будет существовать  до 

закрытия данного соединения*/

SCROLL /*Создает прокручиваемый  курсор*/

KEYSET /*Будет создан  ключевой курсор*/

TYPE_WARNING    /* сервер будет информировать пользователя о

неявном изменении типа курсора, если он несовместим с запросом

SELECT */

FOR

SELECT/*Какие поля  будут  показаны в курсоре*/

sotrudnik_ud.Табельный_номер,

sotrudnik_ud.Номер_бригады,

sotrudnik_ud.ФИО,

sotrudnik_ud.Номер_паспорта,

sotrudnik_ud.Должность,

sotrudnik_ud.Оклад,

sotrudnik_ud.Адрес,

sotrudnik_ud.Стаж,

sotrudnik_ud.Номер_соц_страховки

FROM sotrudnik_ud/*Из какой  таблицы выбираются данные*/

ORDER BY sotrudnik_ud.Табельный_номер DESC

FOR READ ONLY /*Только для  чтения*/       

open global udalSotrudnik/*открываем  глобальный курсор*/

FETCH udalSotrudnik

CLOSE udalSotrudnik /*закрываем  курсор*/     

DEALLOCATE udalSotrudnik /*освобождаем  курсор*/

 

3./*Курсор для удаления  записи из “Медицинская карта  пациента”*/

DECLARE udalmaterial CURSOR

LOCAL SCROLL KEYSET

FOR SELECT        /* что будет показано в курсоре  */

материалы.Код_материала,материалы.Стоимость_материалов,

материалы.Наименование_материала, материалы.Количество_материала

FROM материалы

FOR UPDATE 

open udalmaterial

DECLARE

@@Код_материала  varchar(50),  /*Объявляем необходимые переменные*/

@@Стоимость_материалов int,

@@Наименование_материала varchar(50),

@@Количество_материала int,

@@Count int

SET @@Count = 0

WHILE @@Count < @@CURSOR_ROWS

BEGIN

SET @@Код_материала = ' '

SET @@Стоимость_материалов = ' '

SET @@Наименование_материала = ' '

SET @@Количество_материала= ' '

SET @@Count = @@Count + 1

END

CLOSE udalmaterial         /*закрываем курсор*/

DEALLOCATE udalmaterial        /* освобождаем курсор */

 

4.DECLARE infMaterial CURSOR

GLOBAL SCROLL KEYSET TYPE_WARNING

FOR SELECT  

материалы.Код_материала,материалы.Стоимость_материалов,

материалы.Наименование_материала, материалы.Количество_материала

FROM материалы

FOR READ ONLY     

open global infMaterial

FETCH NEXT FROM infMaterial /*просматриваем*/

DECLARE     

@@Count int

SET @@Count =@@CURSOR_ROWS

Select @@Count

CLOSE infMaterial   

DEALLOCATE infMaterial

 

5.DECLARE  @ИД_поставщика_1    VARCHAR(50),

@Название_организации_1    VARCHAR(50),

@message VARCHAR(80)

DECLARE postavchik_cursor CURSOR LOCAL FOR

SELECT ИД_поставщика, Название_организации

FROM поставщики

WHERE ИД_поставщика ='101'

ORDER BY ИД_поставщика, Название_организации

OPEN postavchik_cursor

FETCH NEXT FROM postavchik_cursor INTO

@ИД_поставщика_1,@Название_организации_1

WHILE @@FETCH_STATUS=0

BEGIN

SELECT @message=@ИД_поставщика_1+ @Название_организации_1