5.5. Пример оформления главы 4. «Создание процедур и функций»

Задание1. Создать функцию для подсчета затрат на комплектующие детали для определенного вида изделия.

Текст функции:

CREATE FUNCTION zcount(@p_naim VARCHAR(20))

RETURNS real AS

BEGIN

declare @v_sum real

set @v_sum=(SELECT SUM(id.kol*d.cena)

FROM izd_det id INNER JOIN detal d

ON id.kod_d=d.kod_d WHERE id.kod_i=

(SELECT kod_i FROM izdel WHERE name_i=@p_naim)

GROUP BY kod_i)

RETURN (@v_sum)

end

Проверим работу функции, создав следующую программу (рис. 5.21)

declare @ss real

set @ss=dbo.zcount('диван')

select @ss as Сумма

Р ис. 5.21. Проверка работы функции

Проверим правильность работы функции на примере изделия «диван». Найдем его код по таблице detal (он равен 120). В таблице izd_det найдём коды входящих в него деталей и их количество: деталь 129 – 1, деталь 133 – 1, деталь 135 – 2, деталь 138 – 2. Из таблицы «Деталь» выберем цены этих деталей и вычислим общую стоимость (см. рис. 5.22):

1*500+1*200+2*100+2*200=500+200+200+400+60=1300 р.

Рис. 5.22. Данные из таблиц izd_det и detal для расчета контрольного примера

Задание 2. Создать процедуру для выдачи сведений о выпуске определенного изделия в определенном цехе (использовать номер цеха и код изделия в качестве параметров процедуры). Процедура должна выдавать номер цеха, дату выпуска, наименование изделия, наименование детали, количество деталей на одно изделие, стоимость комплектующих на одну деталь, стоимость комплектующих на весь выпуск, а затем найти и вывести общую стоимость деталей на изделие и общую стоимость деталей на весь выпуск.

Текст процедуры:

CREATE PROC sum_v @pc INt,@pi INT AS

BEGIN

SELECT c.kod_c,v.dat,v.kod_i,i.name_i,v.kol_v,id.kod_d,d.name_d,

id.kol,d.cena,id.kol*d.cena AS стоим_на_изд,

id.kol*d.cena*v.kol_v AS стоим_на_вып_дату

FROM cex c INNER JOIN vypusk v ON c.kod_c=v.kod_c

INNER JOIN izdel i ON i.kod_i=v.kod_i

INNER JOIN izd_det id ON i.kod_i=id.kod_i

INNER JOIN detal d ON d.kod_d=id.kod_d

WHERE c.kod_c=@pc AND i.kod_i=@pi

ORDER BY v.dat

SELECT c.kod_c,v.kod_i,i.name_i,

SUM(id.kol*d.cena) AS общ_ стоим_на_изд,

SUM(id.kol*d.cena*v.kol_v) AS общ_ стоим_на_выпуск

FROM cex c INNER JOIN vypusk v ON c.kod_c=v.kod_c

INNER JOIN izdel i ON i.kod_i=v.kod_i

INNER JOIN izd_det id ON i.kod_i=id.kod_i

INNER JOIN detal d ON d.kod_d=id.kod_d

WHERE c.kod_c=@pc AND i.kod_i=@pi

GROUP BY c.kod_c,v.kod_i,i.name_i

END

Первый запрос в процедуре в каждой строке выдает сведения о детали, входящей в изделие при каждом его выпуске. В запросе стоит условие для отбора записей по заданном цеху (параметр @pc) и заданному изделию (параметр @pi). Второй запрос - сгуппированный, в нем определяется общая стоимость деталей на изделие и на общий выпуск изделий данного типа в данном цехе.

Для проверки работы процедуры была выполнена следующая команда:

EXEC sum_v 1, 20 ,

где 1 – номер цеха, 20 – код изделия.

Результат представлен на рис. 5.23.

Р ис. 5.23. Результаты выполнения процедуры Проверим правильность работы процедуры: из первой таблицы видно, что изделие с кодом 20 (стол письменный) выпускался два раза: 26.01.2008 и 28.01.2008. Стоимость всех деталей на каждое изделие равна сумме 270+280+40=590 р. и при определении общей стоимости на изделие в сгруппированном запросе эта сумма удваивается. Аналогично можно просчитать и общую стоимость всех комплектующих на выпуск всех изделий данного типа в данном цехе. Задание 3. Создать процедуру для выдачи списка изделий определенного изделия (код изделия задать в качестве параметра процедуры). В процедуре организовать проверку на правильность задания параметра, и если данный код отсутствует в таблице izd_det, выдать сообщение об ошибке. Текст процедуры: create proc detali @kod_i int as begin if not exists(select * from izd_det where kod_i=@kod_i) print ('Нет изделия с таким кодом, проверьте правильность указания параметра') else select i.kod_i, i.name_i, id.kod_d, d.name_d,d.cena, id.kol from izdel i inner join izd_det id on i.kod_i=id.kod_i inner join detal d on d.kod_d=id.kod_d where i.kod_i=@kod_i end

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

• exec detali 120

• exec detali 1200

В первом случае результат представлен на рис. 5.24, во втором – на рис. 5.25

Р ис. 5.24. Результат выполнения процедуры при правильном указании параметра Р ис. 5.25. Результат работы процедуры при вводе неправильного значения параметра