Лабораторная работа №6. Создание хранимых процедур

Создание хранимых процедур

На практике часто требуется повторять последователь­ность одинаковых запросов. Хранимые процедуры позволяют объединить последова­тельность таких запросов и сохранить их на сервере. После этого клиентам достаточно послать один за­прос на выполнение хранимой процедуры.

Хранимые процедуры обладают следующими преимуществами.

Повторное использование кода – после создания хранимой процедуры ее можно вызывать из любых приложений и SQL-запросов.

Сокращение сетевого трафика – вместо нескольких запросов экономнее послать серверу запрос на выполнение хранимой процедуры и сразу получить ответ.

Безопасность – действия не приведут к нарушению целостности данных, т.к. для выполнения хранимой про­цедуры пользователь должен иметь привилегию.

Простота доступа – хранимые процедуры позволяют инкапсулировать сложный код и оформить его в виде простого вызова.

Выполнение бизнес-логики – хранимые процедуры позволяют перенести код сохранения целостности БД из прикладной программы на сервер БД. Бизнес-логика в виде храни­мых процедур не зависит от языка разработки приложения.

MS

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

USE [Notarius]

GO

/*Сумма услуги без комиссионных*/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[Sdelka1]

@N_klienta int

AS

DECLARE @mytable TABLE(sdelka1 int, sdelka2 int)

INSERT @mytable

SELECT SUM([SummaSdelki]-[Komissionie])

AS Sdelka, Sdelka.[N sdelki]

FROM [dbo].[Sdelka]

WHERE Sdelka.[N sdelki]=@N_klienta

GROUP BY [N sdelki]

SELECT SUM(sdelka1), SUM(sdelka2)FROM @mytable

GO

Вторая процедура:

USE [Notarius]

GO

--ФИО Клиента

CREATE PROCEDURE FIOkl

@Name nvarchar(max)

AS

SELECT * FROM [dbo].Klient WHERE FIO=@Name

GO

ORACLE

Простая процедура, которая выводит ФИО клиента и его номер телефона. Входным параметром является ФИО клиента:

CREATE procedure FIOkl(nam IN VARCHAR2)

IS

BEGIN

SELECT * FROM klient WHERE fio=nam

END FIOkl/

Вызвать процедуру, указав фио клиента:

SET SERVEROUTPUT ON

DECLARE

BEGIN FIOkl('Иванов Пётр Михайлович');

END/

Следующий скрипт представляет собой курсор, после выполнения которого на экране появляется список всех клиентов:

SET SERVEROUTPUT ON

DECLARE

CURSOR get_kl IS

SELECT * FROM klient;

klient get_kl%ROWTYPE;

BEGIN

OPEN get_kl;

LOOP

EXIT WHEN get_kl%NOTFOUND;

DBMS_OUTPUT.enable;

FETCH get_kl INTO klient;

DBMS_OUTPUT.put_line('N_klienta:'||TO_CHAR(klient.n_klienta)||''||klient.fio||''||klient.telefon||''||TO_CHAR(klient.adres));

END LOOP;

CLOSE get_kl;

AND;