Управление потоком внутри ХП

Внутри ХП могут использоваться операторы управления потоком

3.1 Оператор IF

IF условие THEN Действие(запрос); ELSE действие Действие(запрос); END IF;

 

Пример 7.2: Определить наличие студента Иванова среди заданного количества студентов

DELIMITER // CREATE PROCEDURE PR6 (IN fio nvarchar(20), IN num INT) BEGIN        SET @mess =''; IF (EXISTS (SELECT * FROM stud WHERE id<num and last_name =fio)) THEN Select 'студент найден' into @mess; ELSE Select 'студент не найден' into @ mess; END IF; END // DELIMITER; Call PR6('Иванов',5); SELECT @mess;

 

3.2 Оператор CASE

CASE переменная WHEN значение1 THEN действие(запрос)1; WHEN значение2 THEN действие(запрос)2; ELSE действие(запрос); END CASE;

  

Пример 7.3: Определить количество студентов Ивановых (0- нет таких, 1 - есть один; 2 - есть пара; 3- много)

DELIMITER // CREATE PROCEDURE PR7(IN fio nvarchar(20)) BEGIN        DECLARE count int default 0; SET @mess=''; SELECT count(*) into count FROM stud WHERE last_name = fio; CASE count WHEN 0 THEN SELECT 'нет таких' into @mess; WHEN 1 THEN SELECT 'есть один' into @mess; WHEN 2 THEN SELECT 'есть пара' into @mess; ELSE SELECT 'много' into @mess; END CASE; END// DELIMITER; Call PR7('Иванов'); S ELECT @mess;

 

3. 3 Оператор WHILE

WHILE условие DO Действие(запрос); END WHILE;

 

Пример 7.4: Определить количество пар однофамильцев среди заданного количества студентов

DELIMITER // CREATE PROCEDURE PR9(IN c INT) BEGIN #DECLARE c int default y; DECLARE i int default 1; DECLARE temp_count int default 0;        SET @count=0;  WHILE (i<c) DO SELECT count(*) into temp_count FROM stud WHERE id<>i and id<=c and last_name = (SELECT last_name FROM stud WHERE id=i); SELECT @count+temp_count into @count; SELECT i+1 into i; SELECT 0 into temp_count; END WHILE; END //  DELIMITER; Call PR9(21); SELECT @count;

 

Права доступа на выполнение ХП, просмотр сведений о ХП и их изменение

Передача прав на вызов процедуры необходима, если хранимая процедура, создана пользователем USER1, а необходимо дать к ней доступ пользователю USER2 то:

GRANT EXECUTE ON PROCEDURE <procedure name> TO <USER2>;

 

Просмотр информации о созданных хранимых процедурах

SHOW PROCEDURE STATUS – возвращает список хранимых процедур, с некоторыми данными о них.

SHOW CREATE PROCEDURE – возвращает код конкретной процедуры.

SELECT * FROM mysql. proc

 

Оператор ALTER PROCEDURE не изменяет тело процедуры. а позволяет изменить лишь ее свойства

Для изменения тела процедуры необходимо использовать  DROP(или DROP PROCEDURE IF EXISTS <имя процедуры>) и потом CREATE.

Задание к лабораторной работе №7

1) Создать хранимую процедуру для подсчета количествa студентов на факультете и форме обучения (входные данные название факультета и наименование формы обучения)

2) Создать хранимую процедуру: добавления студента на указанный факультет и форму обучения (входные данные название факультета и наименование формы обучения, дата рождения, дата поступления, ФИО).

3*) Создайте таблицы представленные на рисунке, заполните их данными

Providers

Id	Name
1	Продинвест
2	КрендельПродакшн
3	Родничек

 

Sets

Id_prov	Date_set	name	price
1	01.02.18	Овсянка	1
1	01.02.18	Гречка	1
2	01.02.18	Батон	1
3	01.02.18	Шампанское	5
3	01.02.18	Коньяк	19
2	02.02.18	Батон	1
2	02.02.18	Ватрушка	1,5
1	03.02.18	Шампанское	5
1	03.02.18	Коньяк	20

 

А также таблицу Orders, содержащую информацию о поступивших партиях, такую как идентификатор поставщика (Id_prov) дата комплектации партии (Date_set), наименование товара в партии (name), его цена (price), дата заказа (Date_order)

Создать хранимую процедуру по заполнению таблицы Orders. Входными параметрами для нее должны быть:

- дата партии (Date_set),

- наименование поставщика

- размер партии.

Если наименование поставщика не введено сообщение об ошибке. (используйте оператор if)

 Если дата не указана - добавить в таблицу набор из таблицы Sets с последней датой комплектации (используйте оператор if).

В таблицу Orders внести набор столько раз, сколько указано в размере партии (используйте оператор while). Eсли размер партии не указан, то набор добавить один раз, если размер партии больше 10 - выдать сообщение о превышении максимально допустимого объема партии. (используйте оператор case)

 

 

15.

ПРИЛОЖЕНИЕ 1
Таблица для выполнения задания к лабораторным работам 2 и 3

create database UNIVER;

 

use UNIVER;

 

Create table stud

(id int not null auto_increment,

last_name nvarchar(25) not null,

f_name nvarchar(25) not null,

s_name nvarchar(25),

form nvarchar(10) not null default 'очно',

faculty nvarchar(10) not null default 'ФПМ',

year int not null default 1,

all_h int default NULL,

inclass_h int default NULL,

br_date date,

in_date date,  

exm float default NULL,

CONSTRAINT PK_stud Primary key (id)

);

insert into stud

(last_name,f_name,s_name,form,faculty,year,all_h,inclass_h,br_date,in_date,exm) values

(N'Стрынгель',N'К',null,N'заочная',N'ФПК',1,300,100,'19831212','20160901',8),

(N'Козлова',N'Д',N'Е',N'заочная',N'ФПК',2,300,100,'19831012','20150901',8.4),

(N'Федоров',N'Н',N'Н',N'заочная',N'ФПК',3,300,100,'19811207','20140901',7),

(N'Рингель',N'П',N'О',N'заочная',N'ФПК',3,300,100,'19730215','20160901',8),

(N'Бежик',N'Н',N'Н',N'вечерняя',N'ФПК',1,500,400,'19931211','2016-09-01',4.5),

(N'Осипчик',N'Н',N'Н',N'вечерняя',N'ФПК',1,500,400,'19831216','20150901',7.7),

(N'Белый',N'С',N'С',N'вечерняя',N'ФПК',2,450,370,'19870627','20150901',6.7),

(N'Ботяновский',N'А',N'С',N'вечерняя',N'ФПК',2,450,370,'19870723','20150901',7.6),

(N'Слободницкий',N'С',N'А',N'вечерняя',N'ФПК',2,450,370,'19870803','20150901',6.7),

(N'Рогатка',N'П',N'Р',N'очная',N'ФПМ',1,500,450,'19861027','20160901',7.4),

(N'Федоренко',N'П',N'Р',N'очная',N'ФПМ',1,500,450,'19950426','20160901',5.6),

(N'Зингель',N'П',N'В',N'очная',N'ФПМ',2,500,450,'19900425','20150901',3.4),

(N'Михеенок',N'Л',N'Н',N'очная',N'ФПМ',2,500,450,'19890313','20150901',5.3),

(N'Савицкая',N'Л',N'Н',N'очная',N'ФПМ',3,450,400, '19950705','20140901',7.7),

(N'Ковальчук',N'О',N'Е',N'заочная',N'ФПМ',1,350,100,'19640523','20160901',7.6),

(N'Заболотная',N'Л',N'И',N'заочная',N'ФПМ',1,350,100,'19860914','20160901',4.7),

(N'Ковриго',N'И',null,N'заочная',N'ФПМ',2,360,120,'19920301', '20150901',7.7),

(N'Шарапо',N'М',null,N'заочная',N'ФПМ',2,360,120,'19970325', '20150901',8.7),

(N'Сафроненко',N'Н',N'Л',N'заочная',N'ФПМ',3,370,130, '19920525','20140901',7.7),

(N'Зайцева',N'Т',N'Я',N'заочная',N'ФПМ',3,370,130,'19940725','20140901',5.6);

 

 

 

id       last_name        f_name s_name form                 faculty year  all_h inclass_h          br_date in_date            exm

1         Стрынгель      К        NULL заочная            ФПК  1         300    100    1983-12-12      2016-09-01           8

2         Козлова                           Д        Е        заочная            ФПК  2         300    100    1983-10-12      2015-09-01 8,4

3         Федоров          Н        Н        заочная            ФПК  3         300    100    1981-12-07      2014-09-01           7

4         Рингель            П        О        заочная            ФПК  3         300    100    1973-02-15      2016-09-01           8

5         Бежик               Н        Н        вечерняя          ФПК  1         500    400    1993-12-11      2016-09-01           4,5

6         Осипчик           Н        Н        вечерняя          ФПК  1         500    400    1983-12-16      2015-09-01           7,7

7         Белый               С        С        вечерняя          ФПК  2         450    370    1987-06-27      2015-09-01           6,7

8         Ботяновский   А        С        вечерняя          ФПК  2         450    370    1987-07-23      2015-09-01           7,6

9         Слободницкий С        А        вечерняя          ФПК  2         450    370    1987-08-03      2015-09-01           6,7

10      Рогатка            П        Р         очная                ФПМ 1         500    450    1986-10-27      2016-09-01           7,4

11      Федоренко      П        Р         очная                ФПМ 1         500    450    1995-04-26      2016-09-01           5,6

12      Зингель            П        В        очная                ФПМ 2         500    450    1990-04-25      2015-09-01           3,4

13      Михеенок        Л        Н        очная                ФПМ 2         500    450    1989-03-13      2015-09-01           5,3

14      Савицкая         Л        Н        очная                ФПМ 3         450    400    1995-07-05      2014-09-01           7,7

15      Ковальчук      О        Е        заочная            ФПМ 1         350    100    1964-05-23      2016-09-01           7,6

16      Заболотная    Л        И        заочная            ФПМ 1         350    100    1986-09-14      2016-09-01           4,7

17      Ковриго                           И        NULL заочная            ФПМ 2         360    120    1992-03-01      2015-09-01 7,7

18      Шарапо                           М       NULL заочная            ФПМ 2         360    120    1997-03-25      2015-09-01 8,7

19      Сафроненко   Н        Л        заочная            ФПМ 3         370    130    1992-05-25      2014-09-01           7,7

20      Зайцева           Т        Я        заочная            ФПМ 3         370    130    1994-07-25      2014-09-01           5,6

ПРИЛОЖЕНИЕ 1
Таблица для выполнения задания к лабораторным работам 4-7

 

create database UNIVER2;

 

use UNIVER2;

 

Create table stud

(id int not null auto_increment Primary key,

last_name nvarchar(25) not null,

f_name nvarchar(25) not null,

s_name nvarchar(25) default null,

br_date date,

in_date date,  

exm float default NULL)

 ;

insert into stud

(last_name,f_name,s_name,br_date,in_date,exm) values

(N'Стрынгель',N'К',null,'19831212','20160901',8),

(N'Козлова',N'Д',N'Е','19831012','20150901',8.4),

(N'Федоров',N'Н',N'Н','19811207','20140901',7),

(N'Рингель',N'П',N'О','19730215','20160901',8),

(N'Бежик',N'Н',N'Н','19931211','2016-09-01',4.5),

(N'Осипчик',N'Н',N'Н','19831216','20150901',7.7),

(N'Белый',N'С',N'С','19870627','20150901',6.7),

(N'Ботяновский',N'А',N'С','19870723','20150901',7.6),

(N'Слободницкий',N'С',N'А','19870803','20150901',6.7),

(N'Рогатка',N'П',N'Р','19861027','20160901',7.4),

(N'Федоренко',N'П',N'Р','19950426','20160901',5.6),

(N'Зингель',N'П',N'В','19900425','20150901',3.4),

(N'Михеенок',N'Л',N'Н','19890313','20150901',5.3),

(N'Савицкая',N'Л',N'Н','19950705','20140901',7.7),

(N'Ковальчук',N'О',N'Е','19640523','20160901',7.6),

(N'Заболотная',N'Л',N'И','19860914','20160901',4.7),

(N'Ковриго',N'И',null,'19920301', '20150901',7.7),

(N'Шарапо',N'М',null,'19970325', '20150901',8.7),

(N'Сафроненко',N'Н',N'Л','19920525','20140901',7.7),

(N'Зайцева',N'Т',N'Я','19940725','20140901',5.6);

 

Create table form

(id int not null Primary key auto_increment,

form_name nvarchar(25) not null);

 

insert into form (form_name) values

(N'очно'),

(N'заочно'),

(N'вечернее');

 

Create table faculty

(id int not null Primary key auto_increment,

faculty_name nvarchar(25) not null);

 

insert into faculty (faculty_name)values

(N'ФПМ'),

(N'ФПК');

 

Create table hours

(id int not null auto_increment Primary key,

course int,

faculty_id int,

form_id int,

all_h int default NULL,

inclass_h int default NULL,

CONSTRAINT FK_to_faculty foreign key (faculty_id) references faculty(id),

CONSTRAINT FK_to_form foreign key (form_id) references form(id)

);

 

insert into hours (course,faculty_id,form_id,all_h,inclass_h) values

(1,2,2,300,100),

(2,2,2,350,110),

(3,2,2,300,100),

(1,2,3,500,400),

(2,2,3,450,370),

(1,1,1,500,450),

(2,1,1,500,450),

(3,1,1,450,400),

(1,1,2,350,100),

(2,1,2,360,120),

(3,1,2,370,130),

(4,1,2,370,130);

 

Create table process

(stud_id int not null,

hours_id int not null,

CONSTRAINT PK_process Primary key (stud_id,hours_id),

CONSTRAINT FK_to_hours foreign key (hours_id) references hours(id),

CONSTRAINT FK_to_stud foreign key (stud_id) references stud(id)

);

 

insert into process (stud_id,hours_id) values

(1,1),

(2,2),

(3,3),

(4,3),

(5,4),

(6,4),

(7,5),

(8,5),

(9,5),

(10,6),

(11,6),

(12,7),

(13,8),

(14,8),

(15,9),

(16,9),

(17,10),

(18,10),

(19,11),

(20,11);