Практическая работа. Взаимодействие MySQL и PHP.. Методические указания для выполнения практических работ

• Название: Методические указания для выполнения практических работ
• Анкор: Практическая работа. Взаимодействие MySQL и PHP
• Дата: 04.03.2022
• Размер: 1.47 Mb.
• Имя файла: REUB_prakt_SQL.doc
• Тип: Практическая работа; #382805
• страница: 5 из 7

Пример триггера
create trigger DelUspevaemost

on uspevaemost

for delete

as

declare @oz int

declare @k int

begin

select @oz=ozenka from deleted

select @k=kol from svodnaj where ozenka=@oz
UPDATE svodnaj set kol=kol-1 where ozenka=@oz

if @k=1

delete from svodnaj where ozenka=@oz
end
3 Обновление записи
При обновлении записи в «Успеваемости» ( Триггер на обновление для «Успеваемость»), в таблице “svodnaj” для удаляемой оценки изменяется количество
До обновления После обновления

ozenka	kol		ozenka	kol
3	4		3	3
			5	1

Старое значение «Успеваемости»

ng	ns	kp	ozenka
101	01	02	3

Новое значение «Успеваемости»

ng	ns	kp	ozenka
101	01	02	5

Пример триггера

create trigger [UpdUspevaemost]

on [uspevaemost]

for update

as

declare @prev_oz int

declare @new_oz int

declare @k int

begin

select @prev_oz=ozenka from deleted

select @new_oz=ozenka from inserted

if @prev_oz<>@new_oz

begin

// изменение старой оценки: как при удалении

select @k=kol from svodnaj where ozenka=@prev_oz

UPDATE svodnaj set kol=kol-1 where ozenka=@prev_oz

if @k=1

delete from svodnaj where ozenka=@prev_oz
// изменение новой оценки: как при добавлении

if @new_oz>1 AND @new_oz<=5

begin

IF exists (select s.ozenka from svodnaj s where s.ozenka=@new_oz )

UPDATE svodnaj set kol=kol+1 where svodnaj.ozenka=@new_oz

else

insert into svodnaj ( ozenka,kol) values (@new_oz, 1)

end

else

BEGIN

RAISERROR ('ENTER OZENKA ' ,16,1)

ROLLBACK TRAN

END
end
end
Контрольные задания

1. 
   Создать триггер на удаление в индивидуальной базе данных для всех таблиц со стороны «одного» в мощности отношений.
   
2. 
   Создать триггер на обновление в индивидуальной базе данных для всех таблиц со стороны «одного» в мощности отношений.
   
3. 
   Создать триггер на добавление записи в индивидуальной базе данных.
   

Практическая работа №13
«Хранимые процедуры в SQL Server»

Цель: «познакомить студентов с понятием хранимая процедура, рассмотреть основные методы их создания»
Ход Работы
Хранимая процедура (stored procedure) – это именованный набор команд Transact-SQL, хранящийся непосредственно на сервере и представляющий собой самостоятельный объект базу данных. Она существует независимо от таблиц или каких-либо других объектов баз данных. Хранимая процедура может быть вызвана клиентской программой, другой хранимой процедурой или триггером. Возможно управлять правами доступа пользователей к хранимы процедурам. Прежде чем выполнить хранимую процедуру, сервер генерирует для неё так называемый план исполнения (execution plan), выполняет её оптимизацию и компиляцию. Выполняется кэширование плана исполнения процедуры, а также оптимизированного компилирования кода. Использование хранимых процедур реализует принцип модульного проектирования.

1. Типы хранимых процедур

Системные хранимые процедуры (system stored procedures) – это хранимые процедуры, поставляемые в составе SQL Server 2000. Предназначены для выполнения различных административных действий. Такие процедуры имеют префикс sp_ . Они хранятся в база данных master.

Пользовательские хранимые процедуры (user-defined stored procedures) – это процедуры созданные пользователями, реализующие те или иные действия (полноценный объект баз данных). Следствием этого является то, что каждая хранимая процедура хранится в конкретной базе данных.

Временные хранимые процедуры (temporary stored procedures) – эти процедуры существуют лишь некоторое время, после чего автоматически уничтожаются сервером. Бывают локальные и глобальные.

Локальные временные хранимые процедуры(local temporary stored procedure) – могут быть вызваны только из того соединения, в котором они были созданы. При создании такой процедуры необходимо дать ей имя, начинающееся символом #. Они хранятся в база данных tempdb и автоматически удаляются при отключении пользователя.
2. Синтаксис процедуры
Для создания хранимой процедуры на языке Transact-SQL используется SQL-оператор
CREATE PROCEDURE

Синтаксис данного оператора для MS SQL Server 2000

CREATE PROC [ EDURE ] procedure_name [ ; number ]

[ { @parameter data_type }

[ VARYING ] [ = default ] [ OUTPUT ]

] [ ,...n ]

[ WITH

{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]

[ FOR REPLICATION ]

AS
Аргументы:

procedure_name – имя создаваемой процедуры. Используются префиксы sp_, #, ## (значение указано выше). Как видно из синтаксиса, не допускается указывать имя владельца, которому будет принадлежать создаваемая процедура, а также имя базы данных, в которой должна быть размещена процедура. Чтобы разместить хранимую процедуру в конкретной базе данных нужно создать её в контексте этой базы данных.

;number – идентификационный номер хранимой процедуры, однозначно определяющий её в группе процедур. На пример: orderproc;1, orderproc;2. Процедура orderproc объединяет в себе две процедуры. При вызове DROP PROCEDURE orderproc, будут удалены все процедуры этой группы.

@parameter – имя параметра, который будет использоваться создаваемой хранимой процедурой для передачи входных или выходных данных. Имена параметров должны начинаться с символа @. В одной хранимой процедуре может использоваться до 1024 параметров.

data_type – тип данных, который будет иметь соответствующий параметр хранимой процедуры. Можно использовать все типы данных, включая text, ntext и image и пользовательские типа данных. Однако, тип данных cursor может использоваться только как выходной параметр (с указанием ключевого слова OUTPUT).

VARYING – ключевое слово, которое используется совместно с параметром OUTPUT, имеющим тип данных cursor. В качестве выходного параметра будет представлено результирующее множество.

default – значение, которое будет принимать соответствующий параметр по умолчанию. При вызове процедуры, явно можно будет не указывать значение соответствующего параметра. Будет использовано значение, созданное с помощью этого параметра.

OUTPUT – его наличие указывает, что параметр предназначается для возвращения данных из хранимой процедуры. Но этот параметр также может использоваться и для передачи значений в процедуру. Значение соответствующего параметра при вызове процедуры может быть задано только с помощью локальной переменной. Нельзя использовать выражения и константы, допустимые для обычных параметров.

n – количество определённых параметров.

{RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION}

RECOMPILE - Указывает, что SQL Server должен создавать план выполнения хранимой процедуры при каждом её вызове.

ENCRYPTION - Указывает, что SQL Server должен выполнить кодирование хранимой процедуры. Обратите внимание, сохранённые процедуры, созданные с выбором ENCRYPTION не могут рассматриваться с sp_helptext.

FOR REPLICATION – используется при репликации данных и включение создаваемой хранимой процедуры в качестве статьи в публикацию. Этот параметр не может быть использован совместно с параметром RECOMPILE.

AS – ключевое слово, свидетельствующее о начале тела процедуры
3. Вызов хранимой процедуры

Для вызова хранимой процедуры используется оператор EXECUTE.

Синтаксис оператора:
EXEC procedure_name

[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } ]
Указывая ключевое слово OUTPUT, вы предписываете присвоить соответствующей локальной переменной (внешней по отношению к процедуре) при завершении хранимой процедуры значение соответствующего параметра. При этом значения параметров могут изменяться в ходе процедуры.
DROP PROCEDURE – удаляет процедуру с сервера.
Задание 1.

Создать процедуру, которая выводит среднюю оценку по каждому предмету. Функция не принимает и не возвращает параметров

Выберите базу uch_proc , откройте папку Programmbility, выберите Stored Procedures.

Из контекстного меню для Stored Procedures выберите New Stored Procedure.

CREATE PROCEDURE sred_ozenka

AS

BEGIN

SELECT kp, avg(ozenka)

from uspevaemost

group by kp

END

Для компиляции нажмите !Execute. Процедура появится в списке.

Для выполнения процедуры в редакторе запросов введите команду Exec sred_ozenka.
Задание 2.

Создать процедуру, которая выводит среднюю оценку по предмету, переданному через параметр
CREATE PROCEDURE sred_ozenka_pred

@pred varchar(20)

AS

BEGIN

SELECT avg(uspevaemost.ozenka)

from uspevaemost, isuchenie, predmet

where uspevaemost.ng=isuchenie.ng and uspevaemost.kp=isuchenie.kp and uspevaemost.tabn=isuchenie.tabn

and uspevaemost.vidz=isuchenie.vidz and isuchenie.kp= predmet.kp and predmet.np=@pred

END

Для выполнения процедуры в редакторе запросов введите команду

Exec sred_ozenka_pred 'информатика'
Задание 3.

Создать процедуру, которая выводит через выходной параметр фамилию студента, который получал наибольшее количество двоек в группе, переданной через входной параметр. Итак, процедура будет иметь один входной и один выходной параметр. Около выходного параметра указывается ключевое слово output
CREATE PROCEDURE neud

@ngr varchar(3) , входной параметр – номер группы

@fio_s varchar(20) output выходной параметр

AS

BEGIN

SELECT @fio_s=fio

from student

where ng=@ngr and ns in(

SELECT ns

from uspevaemost

group by ns, ozenka

having ozenka=2 and count(kp)>= all (

SELECT count(kp)

from uspevaemost

group by ns, ozenka, ng

having ozenka=2 and ng=@ngr) )

END
Для выполнения процедуры в редакторе запросов введите команду

use uch_proc

declare @f varchar (20)

exec neud '101', @fio_s=@f output

print @f
Задание 4.

Создать процедуру, которая выводит коды предметов и вид занятий, по которым получал двойки самый неуспевающий студент указанной группы. Самый неуспевающий студент определяется процедурой neud . Т. е. создаваемая процедура будет вызывать neud

CREATE PROCEDURE neud_pred

@ngg varchar(3)
AS

declare @f varchar (20)

BEGIN

exec neud @ngg, @fio_s=@f output // вызов процедуры neud

select @f // вывод фамилии неуспевающего
// вывод кода предмета и вида занятия,по которым он получил 2
select uspevaemost.kp, uspevaemost.vidz

from uspevaemost, student

where uspevaemost.ng= student.ng and uspevaemost.ns= student.ns and student.fio=@f
END

Для выполнения процедуры в редакторе запросов введите команду

use uch_proc

exec neud_pred '101'

Контрольные задания

1. 
   Создать процедуру, которая в базе данных ‘sklad’определяет количество товара в таблице ‘tovar’.
   
2. 
   Создать процедуру, которая по входному параметру наименование поставщика выводит наименование, код, цену товара.
   
3. 
   Создать процедуру, у которой два параметра: входной параметр - наименование товара, выходной – цена товара. По входному параметру определить цену товара.
   
4. 
   Создать процедуру_2, которая выводит товар и его цену большую средней цены. Среднюю цену возвращает процедура_1 ( как в задании 4).
   
5. 
   База данных Учебный процесс. Создать процедуру, которая по входному параметру номер группы определяет количество положительных оценок ( выходной параметр)
   
6. 
   База данных Учебный процесс. Создать процедуру, которая через выходной параметр возвращает наименование предмета с максимальным количеством часов. Входного параметра нет.
   
7. 
   База данных Учебный процесс. Создать процедуру, которая по входному параметру фамилия студента, возвращает номер группы.
   
8. 
   Сформулируйте и выполните процедуры в индивидуальной базе данных ( без параметров, с входным и выходным параметром)
   

Практическая работа №14
«Создание запросов с использованием внешнего соединения»

Цель: «выявить отличия внешнего и внутреннего соединений, создать запросы для внешних соединений»
Ход Работы
При внутреннем соединении, рассмотренном ранее, таблицы связывались в условном операторе предложением:
<имя столбца таблицы 1> = < имя столбца таблицы 2>

Например: отобразить фамилии преподавателей с кафедры ‘информатика’

Select prepodavatel.fio

From kafedra, prepodavatel

Where kafedra.kkaf = prepodavatel.kkaf and kafedra.nkaf=‘информатика’
В этом случае осуществляется декартово произведение таблиц kafedra, prepodavatel и из полученного набора данных отбираются записи, удовлетворяющие условию поиска (kafedra.kkaf = prepodavatel.kkaf)
Существует и другой вид соединения таблиц – внешнее соединение. Оно определяется предложением From согласно спецификации

Select * from <таблица 1> <вид соединения> JOIN < таблица 2>

ON <условие поиска>
Внешнее соединение похоже на внутреннее, но в результирующий набор включаются записи ведущей таблицы соединения, которые объединяются с пустым множеством записей другой таблицы. Какая из таблиц будет ведущей определяет вид соединения:

• 
  Left - ( левое внешнее соединение), когда ведущей является таблица1 (расположенная слева от вида соединения);
  
• 
  Right – (правое внешнее соединение), когда ведущей является таблица2 (расположенная справа от вида соединения);
  

Задание 1: создайте две таблицы

Create table tab1( P1 varchar(1), P2 varchar(1), P3 int )

Create table tab2( P2 varchar(1), P4 varchar(1), )

Заполните данными эти таблицы.

tab1 Р1 Р2 Р3 a x 400 b x 200 c y 500 d

tab2 Р2 Р4 x 1 y 2 z 2

Тогда выполнение оператора Select, реализующего внешнее левое соединение