FROM Увольнения, Сотрудники
WHERE Увольнения.Личный_номер = @id AND Увольнения.Личный_номер = Сотрудники.Личный_номер // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id
7. Поиск всех нормативных документов отдела
CREATE PROC Documents
@dept varchar(100)
SELECT Нормативные_документы.Номер_нормативного_документа, Нормативные_документы.Тип,
Нормативные_документы.Дата_документа
FROM Нормативные_документы, Нормативные_документы_отделов
WHERE Нормативные_документы.Номер_нормативного_документа = Нормативные_документы_отделов.Номер_нормативного_документа
AND Название_отдела = @dept // Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @dept
8. Просмотр личного дела сотрудника по его личному номеру
CREATE PROC GetWorkerInfo
@id int * FROM Сотрудники WHERE Личный_номер = @id
// Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id
9. Просмотр штатной расстановки отдела
CREATE PROC Lists
@dept varchar(100)
SELECT Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность, Сотрудники.Звание
FROM Сотрудники
WHERE Сотрудники.Название_отдела = @dept // Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @deptBY Фамилия // Сортирует данные, возвращаемые запросом, по фамилии
. Добавление нового отдела в таблицу «Отделы»
CREATE PROC NewDepartment
@id int,
@dept varchar(100),
@name varchar(100),
@oName varchar(100),
@lastName varchar(100),
@kol int,
@tarif bigint,
@prim varchar(200),
@tel bigint,
@fax bigint
INSERT INTO Отделы (Название_отдела, Номер_штатного_расписания, Фамилия_начальника, Имя_начальника,
Отчество_начальника, Количество_штатных_единиц, Тарифная_ставка, Примечание, Телефон, Факс) // Добавление новой строки в таблицу «Отделы»
VALUES (@id, @dept, @name, @oName, @lastName, @kol, @tarif, @prim, @tel, @fax) // Задает набор выражений значений строки
. Добавление нового сотрудника
CREATE PROC NewWorker
@id int,
@name varchar(100),
@oName varchar(100),
@lastName varchar(100),
@education varchar(100),
@position varchar(100),
@rank varchar(100),
@town varchar(100),
@street varchar(100),
@d int,
@kv int,
@dept varchar(100) INTO Сотрудники (Личный_номер, Фамилия, Имя, Отчество, Образование, Должность, Звание, Адрес_город, Адрес_улица, Адрес_дом, Адрес_квартира, Название_отдела) // Добавление новой строки в таблицу «Сотрудники»
VALUES (@id, @name, @oName, @lastName, @education, @position, @rank, @town, @street, @d, @kv,@dept) ) // Задает набор выражений значений строки
. Поиск приказа по его номеру
CREATE PROC Orders
@id int Приказы.Номер_приказа, Приказы_сотрудников.Название_отдела, Приказы.Ответственный, Приказы.Дата_приказа
FROM Приказы, Приказы_сотрудников
WHERE Приказы.Номер_приказа = Приказы_сотрудников.Номер_приказа AND Приказы.Номер_приказа = @id
// Выборка строк таблицы, где значение колонки «Номер_приказа» соответствует введенному значению @id
13. Поиск сотрудников, занимающий определенную должность
CREATE PROC Position
@position varchar(100)
SELECT Сотрудники.Название_отдела, Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность,Сотрудники.Звание
FROM Сотрудники
WHERE Должность = @position // Выборка строк таблицы, где значение колонки «Должность» соответствует введенному значению @position
14. Поиск отдела по номеру его штатного расписания
CREATE PROC Schedule
@id int Отделы.Номер_штатного_расписания, Отделы.Название_отдела, Отделы.Фамилия_начальника, Отделы.Имя_начальника, Отделы.Отчество_начальника, Отделы.Количество_штатных_единиц, Отделы.Тарифная_ставка, Отделы.Примечание
FROM Отделы
WHERE Номер_штатного_расписания = @id // Выборка строк таблицы, где значение колонки «Номер_штатного_расписания» соответствует введенному значению @id
15. Поиск сотрудников по возрасту
CREATE PROC Search
@i int
SELECT Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность,
День_рождения.Дата_рождения, День_рождения.Количество_полных_лет
FROM Сотрудники, День_рождения
WHERE Сотрудники.Личный_номер = День_рождения.Личный_номер
AND ISNUMERIC(День_рождения.Количество_полных_лет)<>0
AND День_рождения.Количество_полных_лет >= @i; // Выборка строк таблицы, где значение колонки «Количество_полных_лет» больше или равно введенному значению @i; ISNUMERIC проверяет, чтобы значение «Количество_полных_лет» не было равно нулю
. Поиск сотрудника по фамилии или началу фамилии
CREATE PROC SearchWorker
@name varchar(100)
SELECT * FROM Сотрудники
WHERE Фамилия LIKE @name // Выборка строк таблицы, где значение колонки «Фамилия» соответствует введенному значению @name
17. Просмотра сотрудников, которые служили в ВС
CREATE PROC SpecZvanie
@vs varchar (20)
AS@vs = 'да' // Установить значение переменной, равной значению «да»Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.ДолжностьСпецзвания, СотрудникиСпецзвания.Личный_номер=Сотрудники.Личный_номер AND Служба_в_ВС = @vs // Выборка строк таблицы, где значение колонки «Служба_в_ВС» соответствует значению переменной @vs
. Обновление данных о сотруднике
CREATE PROC UpdateWorker
@id int,
@position varchar (100),
@rank varchar (100)
IF EXISTS (SELECT * FROM Сотрудники WHERE Личный_номер = @id ) // Проверка на наличие нужной строки в таблице «Сотрудники»Сотрудники // Обновление таблицы «Сотрудники»Должность = @position // Установить значение в колонке «Должность», равное введенному значению переменной @positionЛичный_номер = @id // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @idСотрудникиЗвание = @rank // Установить значение в колонке «Звание», равное введенному значению переменной @ rankЛичный_номер = @id
19. Просмотр отпусков сотрудника
CREATE PROC Vacation
@id int
ASСотрудники.Личный_номер, Сотрудники.Название_отдела, Сотрудники.Фамилия, Сотрудники.Имя,
Сотрудники.Отчество, Сотрудники.Должность, Табель_отпусков.Номер_табеля, Табель_отпусков.Тип_отпуска,
Табель_отпусков.Дата_с, Табель_отпусков.Количество_днейТабель_отпусков, СотрудникиТабель_отпусков.Личный_номер = @id AND Табель_отпусков.Личный_номер = Сотрудники.Личный_номер // Выборка строк таблицы, где значение колонки «Личный_номер» соответствует введенному значению @id
20. Поиск сотрудника по трудовому стажу, выше указанного
CREATE PROC WorkerExperience
@experience int
SELECT distinct Сотрудники.Личный_номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Должность, Сотрудники.Название_отдела, DATEDIFF(year,dbo.Трудовой_стаж.Дата_приёма_на_работу,GETDATE()) AS 'Стаж' // DATEDIFF - функция, которая возвращает интервал времени day, прошедшего от указанной даты Дата_с до текущей даты, установленной на данном сервере, GETDATE - возвращает текущую дату, установленную на данном сервереСотрудники, Трудовой_стажСотрудники.Личный_номер=Трудовой_стаж.Личный_номер AND Трудовой_стаж.Стаж >= @experience // Выборка строк таблицы, где значение колонки «Стаж» больше или равно введенному значению @experience
1. Курсор для просмотра сотрудников в выбранном отделе
CREATE PROCEDURE curs1
@otdel varchar(100)curs1 CURSORSCROLL KEYSET
// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения
TYPE_WARNING
// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT
FOR
SELECT*FROM Сотрудники
WHERE Название_отдела LIKE @otdel
// Выборка строк таблицы, где значение колонки «Название_отдела» соответствует введенному значению @otdel
FOR READ ONLY // Только для чтения
open global curs1 // открытие глобального курсора
DECLARE
@@Counter int@@Counter =@@CURSOR_ROWS
// присвоение переменной @@Counter значения, равного числу рядов курсора =@@CURSOR_ROWS
Select @@Counter 'Количество сотрудников в этом отделе'
CLOSE curs1 // закрытие курсора
DEALLOCATE curs1 // освобождение курсора
. Курсор для просмотра количествa командировок в этом месяце
CREATE PROCEDURE curs2curs2 CURSOR SCROLL KEYSET
// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения
TYPE_WARNING
//Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT
FOR
SELECT
Командировки.Номер_командировки, Командировки.Дата_с, Командировки.Срок, Командировки.Место,
Командировки.Личный_номер, Командировки.Название_отдела
FROM Командировки
FOR UPDATE // Курсор на обновление в таблице
open global curs2 // открытие глобального курсора
DECLARE
@@nomer int,
@@date datetime,
@@srok int,
@@mesto varchar(50),
@@l_nomer int,
@@otdel varchar(100),
@@var int,
@@Counter int@@Counter = 1@@var = 0@@Counter<= @@CURSOR_ROWS
// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора
BEGINcurs2 INTO @@nomer, @@date, @@srok, @@mesto , @@l_nomer,@@otdel
// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@nomer, @@date, @@srok, @@mesto , @@l_nomer,@@otdel
IF (DATEDIFF(M,@@date,GETDATE())= 0) // Проверка, чтобы месяц текущей даты, установленной на данном сервере, был равен значению переменной @@date
BEGIN
SET @@var=@@var+1 // Установить значение переменной @@var большим на единицу
print @@date
END
SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу
END
Select @@var as 'В этом месяце командировок:'
CLOSE curs2 //закрытие курсора
DEALLOCATE curs2 //освобождение курсора
. Поиск сотрудника по фамилии
CREATE PROCEDURE curs3 // открытие глобального курсора
@fio varchar (100)curs3 CURSOR SCROLL KEYSET // глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения
TYPE_WARNING // Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT
FOR
SELECT Сотрудники.Личный_Номер, Сотрудники.Фамилия, Сотрудники.Имя, Сотрудники.Отчество, Сотрудники.Должность,
Сотрудники.Звание, Сотрудники.Название_отдела
FROM Сотрудники
FOR UPDATE // Курсор на обновление в таблице
open global curs3
@@id int,
@@name varchar(100),
@@oName varchar(100),
@@lastName varchar(100),
@@position varchar(100),
@@rank varchar(100),
@@dept varchar(100),
@@Counter int,
@@var int@@Counter = 1 @@var = 0
WHILE @@Counter<= @@CURSOR_ROWS // Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора
BEGINcurs3 INTO @@id, @@name, @@oName,@@lastName,@@position,@@rank,@@dept
// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@id, @@name, @@oName,@@lastName,@@position,@@rank и @@dept
IF @fio = @@name // Проверка, чтобы вводимая фамилия @fio была равна значению @@name
BEGIN
Select @@id as 'Личный номер', @@name+' '+SUBSTRING(@@oName, 1, 1)+'.'+ SUBSTRING(@@lastName, 1, 1)+'.'
// SUBSTRING - функция, которая возвращает часть значения @@oName и @@lastName, чтобы «склеить» ФИО сотрудника
as 'ФИО',@@position as 'Должность', @@rank 'Звание', @@dept 'Отдел'
END
SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу
END
CLOSE curs3 //закрытие курсора
DEALLOCATE curs3 //освобождение курсора
. Курсор для просмотра отпусков по выбранному типу
CREATE PROCEDURE curs4
@otpusk varchar(100)curs4 CURSORSCROLL KEYSET
// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения
TYPE_WARNING
// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT
FOR
SELECT * FROM Табель_отпусков
FOR UPDATE // Курсор на обновление в таблице
open global curs4 // открытие глобального курсора
DECLARE
@@nomer int,
@@type varchar(100),
@@date datetime,
@@day int,
@@id int,
@@dept varchar(100),
@@Counter int@@Counter = 1 @@COUNTER<= @@CURSOR_ROWS
// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора
BEGINcurs4 INTO @@nomer, @@type,@@date,@@day, @@id, @@dept
// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@nomer, @@type,@@date,@@day, @@id и @@dept
IF(CHARINDEX(@otpusk,@@type)<>0) // Проверка, чтобы значений вводимой переменной @otpusk не было в колонке со значениями @@type
BEGIN@@nomer as 'Номер табеля', @@type as 'Тип отпуска', CAST(@@date AS nvarchar(12)) as 'Дата с', @@day as 'Срок',
// CAST - функция преобразования типа данных в nvarchar(12) значений колонки «Дата_с»
@@id 'Личный номер сотрудника', @@dept 'Отдел'
END
SET @@Counter =@@Counter +1 // Установить значение переменной @@Counter большим на единицу
END
DEALLOCATE curs4 //освобождение курсора
. Курсор для просмотра сотрудников, у кого в текущем месяце День рождения
CREATE PROCEDURE curs5curs5 CURSOR SCROLL KEYSET
// глобальный прокручиваемый ключевой курсор, который будет существовать до закрытия текущего соединения
TYPE_WARNING
// Сервер будет информировать пользователя о неявном изменении типа курсора, если он несовместим с запросом SELECT
FOR
SELECT * FROM День_рождения
FOR UPDATE // Курсор на обновление в таблице
open global curs5 // открытие глобального курсора
DECLARE
@@age int,
@@date datetime,
@@id int,
@@otdel varchar(100),
@@var int,
@@Counter int@@Counter = 1@@var = 0 @@Counter<= @@CURSOR_ROWS
// Выполняется до тех пор, пока число строк в таблице меньше или равно числу рядов курсора
BEGINcurs5 INTO @@age, @@date, @@id,@@otdel
// FETCH - получает определенную строку из курсора и помещает данные из столбцов выборки в переменные @@age, @@date, @@id и @@otdel
IF (MONTH(@@date) = MONTH(DATEADD(MONTH, 0, GETDATE())))
// Проверка, чтобы значение MONTH введенной переменной (@@date совпадало с текущим месяцем, в соответствии с текущей датой, установленной на данном сервере; GETDATE - функция, возвращающая текущую системную отметку времени базы данных
BEGIN
SET @@var=@@var+1
// Установить значение переменной @@var большим на единицу
PRINT @@date // Вывод на экран значения переменной @@date
Select @@age as 'Полных лет', CAST(@@date AS nvarchar(12)) as 'Дата рождения',
@@id 'Личный номер сотрудника', @@otdel 'Отдел'
END
SET @@Counter =@@Counter +1
// Установить значение переменной @@Counter большим на единицу
END
Select @@var as 'В этом месяце день рождение у:'
CLOSE curs5 //закрытие курсора
DEALLOCATE //освобождение курсора
Информационная система предусматривает возможность гибкого разграничения прав доступа пользователей к хранимой информации. Такой подход обеспечивает защиту хранящейся и обрабатываемой информации, а именно:
− ограничение прав на чтение, изменение или уничтожение;
− обеспечение целостности информации, а также доступности информации для органов управления и уполномоченных пользователей;
− исключение утечки информации при обработке и передаче между объектами вычислительной техники.
Созданы группы пользователей, исполняющие различные роли:
) Админ (login − admin/password − 0). Имеет доступ ко всей информации, может добавлять/изменять данные в таблицах, добавлять новых пользователей и удалять существующих.
CREATE LOGIN admin // Создание логина adminPASSWORD = '0' // Присвоение пароляUSER admin // Создание пользователя adminLOGIN adminALL PRIVILEGES // Назначение пользователю всех правadmin GRANT OPTION // C возможностью назначения прав другим пользователям
GO
2) Сотрудник отдела кадров (kadr/1). Так же, как и админ, имеет доступ ко всем данным, есть возможность изменять их, но не может производить операции с пользователями.