 |
Заполнение таблиц базы данных записями.
|
|
|
|
В простейшем случае вставка данных с помощью команды INSERT предлагает использование конструкции INSERT...VALUES:
INSERT [INTO] table_or_view
[(column_list..n)]
VALUES (data_values [...n])
С помощью этой команды можно добавить одну-единственную строку.
Аргумент table_or_view указывает имя таблицы или представления, в которые необходимо вставить строку данных.
Скрипт заполнения базы данных DB_Books пятнадцатью записями:
INSERT INTO Authors(Code_author, name_author, Birthday)
VALUES(1, 'Толстиков', '1971-05-08');
INSERT INTO Authors(Code_author, name_author, Birthday)
VALUES(2, 'Лукашин', '1960-01-03');
INSERT INTO Authors(Code_author, name_author, Birthday)
VALUES(3, 'Веткин', '1965-02-09')
INSERT INTO Authors(Code_author, name_author, Birthday)
VALUES(4, 'Досин', '1974-05-08')
INSERT INTO Authors(Code_author, name_author, Birthday)
VALUES(5, 'Чаплыгин', '1982-03-07')
INSERT INTO dbo.Publishing_house(Code_publish, Publish, City)
VALUES(1, 'Наш дом', 'Минск')
INSERT INTO dbo.Publishing_house(Code_publish, Publish, City)
VALUES(2, 'Наш дом', 'Минск')
INSERT INTO dbo.Publishing_house(Code_publish, Publish, City)
VALUES(3, 'Наш дом', 'Минск')
INSERT INTO dbo.Publishing_house(Code_publish, Publish, City)
VALUES(4, 'Урожай', 'Москва')
INSERT INTO dbo.Publishing_house(Code_publish, Publish, City)
VALUES(5, 'Урожай', 'Москва')
INSERT INTO dbo.Books(Code_book, Title_book, Code_author, Pages, Code_publish)
VALUES(1, 'Урожай на грядках', 1, 150, 1)
INSERT INTO dbo.Books(Code_book, Title_book, Code_author, Pages, Code_publish)
VALUES(2, 'Виноград', 2, 260, 2)
INSERT INTO dbo.Books(Code_book, Title_book, Code_author, Pages, Code_publish)
VALUES(3, 'Садоводство', 3, 320, 3)
INSERT INTO dbo.Books(Code_book, Title_book, Code_author, Pages, Code_publish)
VALUES(4, 'Овощеводство', 4, 160, 4)
INSERT INTO dbo.Books(Code_book, Title_book, Code_author, Pages, Code_publish)
VALUES(5, 'Овощи', 5, 360, 5)
Создание хранимых процедур
Далее необходимо создать 5 хранимых процедур и продемонстрировать их с экрана.
1 Создать хранимую процедуру со входным параметром. Вывести названия книг из таблицы Books, в которых число страниц превышает величину, заданную входным параметром хранимой процедуры.
|
|
|
2 Создать хранимую процедуру со входным параметром. Вывести список названий книг по первой букве названия, задаваемой входным параметром хранимой процедуры.
3 Создать хранимую процедуру со входным параметром. Вывести фамилии авторов из таблицы Authors, чей год рождения превышает заданный входным параметром.
4 Создать хранимую процедуру с двумя входными параметрами. Вывести количество книг, в которых количество страниц превышает заданное входным параметром количество, и название книги задано еще одним входным параметром хранимой процедуры
5 Создать хранимую процедуру со входным и выходным параметрами. Процедура должна вывести количество книг (определяемое выходным параметром), в которых количество страниц превышает заданное входным параметром количество.
Примеры хранимых процедур
1. Список выдач книг за текущий день.
CREATE PROCEDURE CpicokVidach
AS
SELECT /*Перечисляем поля, которые будут выведены в результате запроса */
Пользование_библиотекой2.Табельный_номер, COUNT(Пользование_библиотекой2.Дата_выдачи)
FROM /*указываем имя таблицы из которых выбираются записи*/
Пользование_библиотекой2
WHERE /*задаем условие отбора*/
Пользование_библиотекой2.Дата_выдачи=(SELECT GETDATE())
GROUP BY /*производится группировка по указанному полю*/
Табельный_номер
/*SELECT GETDATE() позволяет получить текущую дату (год, месяц, число)
COUNT (<поле>) возвращает количество записей какого-либо поля*/
2. Количество экземпляров какой-либо книги.
CREATE PROCEDURE KolExzemplarov
/*Объявляем необходимые переменные*/
@ISBN varchar(20)
AS
/* Следующая конструкция проверяет, существуют ли записи в таблице «Книги» с заданным ISBN*/
IF not EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)
RETURN 0 /*Вызывает конец процедуры KolExzemplarov */
SELECT Экземпляр.ISBN
INTO TEMP1 /*Сохраняет выбранные поля во временной таблице Temp1*/
FROM Экземпляр
WHERE ISBN = @ISBN
SELECT COUNT(ISBN) /* Count подсчитывает количество неповторяющихся записей поля ISBN*/
|
|
|
FROM TEMP1
3. Список книг, которыми пользовался какой-либо студент
CREATE PROCEDURE CpicokKnigCtudenta
@Chit_nom int /*Объявляем необходимые переменные*/
AS SELECT Студенты.Имя, Студенты.Фамилия, Пользование_библиотекой2.Шифр, Книга.Автор, Книга.Название
FROM /*указываем имена таблиц, из которых выбираются записи*/
Книга, Экземпляр, Студенты, Пользование_библиотекой2
WHERE (Студенты.Читательский_номер = Пользование_библиотекой2.Читательский_номер) AND (Экземпляр.Шифр = Пользование_библиотекой2.Шифр) AND (Экземпляр.ISBN =Книга.ISBN)
/* AND позволяет задать в операторе WHERE несколько условий, которые должны выполняться одновременно*/
4. Удаление из таблицы «Студенты». Допустимо, если в таблице «Пользование библиотекой2» нет ссылающихся записей.
CREATE PROCEDURE DeleteStudent
@Chit_nom int /*Объявляем необходимые переменные*/
AS /*Проверяем, если ссылающиеся записи, если записей нет, разрешается удаление.*/
IF not EXISTS (SELECT * FROM Пользование_библиотекой2 WHERE Читательский_номер=@Chit_nom)
DELETE /*Оператор удаления*/
FROM Студенты /*Имя таблицы, откуда нужно удалить*/
WHERE /*Условие удаления – удаляем строку, для которой значение поля Читательский_номер совпадает с нужным*/
Читательский_номер=@Chit_nom
5. Вставка в таблицу «Заказы». Разрешена, если в таблицах «Книга» и «Преподаватели» есть записи, на которые будет ссылаться новая запись.
PROCEDURE NewZakaz
@Kolvo int, /*Объявляем необходимые переменные*/
@data_zakaza datetime,
@Chit_nomer int,
@ISBN varchar(20)
AS /*Проверяем, есть ли запись в таблице «Заказы» с такими же значениями ключевых полей, как у новой записи*/
IF EXISTS (SELECT * FROM Заказы WHERE ISBN = @ISBN AND Читательский_номер=@Chit_nomer)
RETURN 0 /*Если есть, завершаем выполнение процедуры*/
IF EXISTS (SELECT * FROM Преподаватели WHERE Читательский_номер = @Chit_nomer)
/*Проверили, есть ли в «Преподаватели» соответствующая запись*/
IF EXISTS (SELECT * FROM Книга WHERE ISBN = @ISBN)
/*Проверили, есть ли в «Книга» соответствующая запись*/
INSERT INTO Заказы /*Указываем таблицу, куда вставляем запись*/
VALUES (@Kolvo,@data_zakaza,@Chit_nomer, @ISBN) /* Указываем какие значения*/
6. Обновление таблицы «Студенты». Изменение фамилии студента.
CREATE PROCEDURE UpdateStudent
@Chit_nom int, /* Объявляем необходимые переменные */
|
|
|
@Fam varchar(20)
AS
IF EXISTS (SELECT * FROM Студенты /* Проверяем, существуют ли студенты,*/
WHERE Читательский_номер = @Chit_nom) /*читательский номер которых равен искомому*/
UPDATE Студенты /*Если такие есть обновляем «Студенты»
SET Фамилия=@Fam /*полю фамилия присваиваем новое значение*/
WHERE Читательский_номер = @Chit_nom /*если читательский номер записи равен искомому*/
7. Вставка в таблицу «Пользование библиотекой2». Разрешается, если есть в таблицах «Студенты», «Сотрудники_библиотеки» и «Экземпляр» соответствующие записи.
ALTER PROCEDURE NewPolzovanieStydentov
@Chit_nomer int, /*Объявляем необходимые переменные*/
@data_vidachi datetime,
@data_priema datetime,
@tab_nomer int,
@Shifr varchar(20)
AS
IF EXISTS (SELECT * FROM Пользование_библиотекой2 /*Проверяем, нет ли уже в таблице */
WHERE Шифр = @Shifr AND /*записи с таким же значением первичного ключа*/
Читательский_номер=@Chit_nomer)
RETURN 0 /* если есть, завершается процедура*/
IF EXISTS (SELECT * FROM Студенты /*проверяем наличие соответствующей записи в*/
WHERE Читательский_номер = @Chit_nomer) /*таблице «Студенты»*/
IF EXISTS (SELECT * FROM Экземпляр /*проверяем наличие соответствующей записи в*/
WHERE Шифр = @Shifr) /*таблице «Экземпляр»*/
IF EXISTS (SELECT * FROM Сотрудники_библиотеки /* проверяем наличие соответствующей */
WHERE табельный_номер = @tab_nomer) /* записи в таблице «Сотрудники_библиотеки»*/
INSERT /* если условия выполняются, добавляем*/
INTO Пользование_библиотекой2 /*в таблицу новые значения*/
VALUES (@Chit_nomer,@data_vidachi,@data_priema,@tab_nomer,@Shifr)
8. Вставка в таблицу «Сотрудники_библиотеки». Проверяется, наличие соответствующей записи в поле номер-отдела в таблице «Отделы».
CREATE PROCEDURE NewSotrudnik
@Tab_nom int, /*Объявляем необходимые переменные*/
|
|
|
@Fam varchar(20),
@Name varchar(20),
@Sec_name varchar(20),
@data_rogd datetime,
@Dolgn varchar(20),
@Nom_otd int
AS
IF EXISTS (SELECT * FROM Сотрудники_библиотеки /*Проверяем, нет ли уже в таблице */
WHERE Табельный_номер = @Tab_nom) /*записи с таким же значением первичного ключа*/
RETURN 0 /* если есть, завершается процедура*/
IF EXISTS (SELECT * FROM Отделы /*Проверяем, есть ли уже в таблице «Отделы» */
WHERE Номер_отдела = @Nom_otd) /*записи с таким же значением поля номер_отдела*/
INSERT /* если условие выполняется, добавляем*/
INTO Сотрудники_библиотеки /*в таблицу новые значения*/
VALUES (@Tab_nom,@Fam, @Name, @Sec_name, @data_rogd, @Dolgn, @Nom_otd)
9. Сколько существует должностей в библиотеке.
CREATE PROCEDURE KolDolgn
AS SELECT COUNT (DISTINCT Сотрудники_библиотеки.должность)
FROM Сотрудники_библиотеки /* COUNT (DISTINCT <поле>) подсчитывает количество разноименных значений какого-либо поля в таблице*/
10. Обновление таблицы «Отделы». Изменился начальник отдела.
ALTER PROCEDURE UpdateOtdel
@Nom_otdela int,
@Fam_New_Nach_otd varchar(20),
@Tab_Nom_New_Nach_otd int
AS
IF not EXISTS (SELECT * FROM Сотрудники_библиотеки /*Условие проверяет, есть ли в */
WHERE табельный_номер = @Tab_Nom_New_Nach_otd /* библиотеке сотрудник с искомыми*/
AND фамилия=@Fam_New_Nach_otd) /*фамилией и табельным номером*/
RETURN 0 /* если нет, завершается процедура*/
IF EXISTS (SELECT * FROM Отделы /*Условие проверяет, есть ли в библиотеке*/
WHERE Номер_отдела = @Nom_otdela) /*искомый номер отдела*/
UPDATE Отделы
SET фамилия_начальника_отдела=@Fam_New_Nach_otd /*меняем значение поля «фамилия начальника отдела» на новое*/
UPDATE Отделы
SET табельный_номер_начальника=@Tab_Nom_New_Nach_otd /*меняем значение поля «табельный номер начальника отдела» на новое*/
WHERE Номер_отдела = @Nom_otdela /*меняем только для записи, у которой значение «номер_отдела» равно искомому*/
|
|
|
