Опубликован: 11.03.2009 | Доступ: свободный | Студентов: 2469 / 426 | Оценка: 4.22 / 4.22 | Длительность: 11:41:00
Лекция 10:

Хранимые процедуры и функции. Типы параметров, синтаксис описания формальных параметров

< Лекция 9 || Лекция 10: 12 || Лекция 11 >
Аннотация: Лекция посвящена теме хранимых процедур и функций. В теоретической части рассказывается о типах параметров и синтаксисе их описания. А также, рассматриваются практические задания по теме.

SQL Server

Хранимая процедура - это подпрограмма, состоящая из SQL операторов и команд T-SQL. Хранимая процедура сохраняется на сервере и выполняется по команде пользователя или вызывается из блока T-SQL. План выполнения процедуры подготавливается во время запуска процедуры, поэтому собственно выполнение процедуры происходит очень быстро. Хранимая процедура может:

  • Содержать параметры (аргументы);
  • Вызывать другие процедуры;
  • Возвращать свой статус вызывающей процедуре или модулю T_SQL, указывающий на успешное окончание или ошибку, и в случае ошибки на ее причину;
  • Возвращать значения параметров вызывающей процедуре или модулю T-SQL ;

Синтаксис для создания хранимой процедуры:

create procedure [владелец.]название_процедуры[;номер] [
[(] @название_параметра тип_данных [= default] [output]
[,  @название_параметра тип_данных [= default] [output]]...[)]] [with
recompile]
as sql_операторы

Оператор execute:

[execute] [@return_status =]
	[[[сервер.]база_данных.]владелец.]название_процедуры[;номер]
		[[@название_параметра = ] значение | 
		 [@название_параметра = ] @переменная [output]
[,[@название_параметра = ] значение | 
	 [@название_параметра = ] @переменная [output]...]]
[with recompile]

Параметры

Параметр - это аргумент хранимой процедуры. Один или несколько параметров могут быть объявлены в операторе создания процедуры. Значение каждого параметра, объявленного в операторе create procedure, должно указываться пользователем в момент вызова процедуры.

Названиям параметров должен предшествовать символ "@", а сами эти названия должны соответствовать правилам, установленным для идентификаторов. Для всех параметров должен быть указан системный или пользовательский тип данных, и если необходимо длина этого типа данных. Названия параметров являются локальными по отношению к содержащей их процедуре; такие же названия можно использовать для параметров в другой процедуре.

В операторе create procedure для параметра можно указать значение, принимаемое по умолчанию. Это значение, которое может быть любой константой, используется в качестве аргумента процедуры, если для этого параметра не было указано никакого значения.

CREATE   proc  dbo.tran_Begin /*
 * Инициализация транзакции
 * Если процедура выполняется внутри транзакции, то создается savepoint
 * Иначе инициализируется транзакция
 * На выходе передается null для транзакции и имя точки сохранения для savepoint
 */
(@trName DObjectName output, @explicitSavepointNameFlag DLogical = 0)
as
	declare @d Datetime
begin
--	return 0

	if @@trancount = 0
	begin
		select @trName = null
		begin transaction
	end
	else
	begin
-- если не задано имя и не установлен флаг использования переданного имени, то генерим имя
		if @explicitSavepointNameFlag = 0 or @trName is null
		begin
			select @d = getdate()
			select @trName = 'tr_'+ISNULL(convert(varchar, @@nestlevel), '')+'_'+ISNULL(convert(varchar(50),
				rand(datepart(mm, @d)*100000+datepart(ss, @d)*1000+datepart(ms, @d))), '')
		end
		save tran @trName
	end

	if @@error <> 0
	begin
-- ошибка создания транзакции
		return 5
	end

	return 0
end

CREATE  proc  dbo.tran_Commit /*
 * Подтверждение транзакции
 * Работает в паре с tran_Begin
 * на вход передается имя точки сохранения (транзакции), возвращенное tran_Begin
 * Если вызов идет из транзакции и @trName равно null, то выполняется commit,
 * иначе ничего не делается
 */
(@trName DObjectName)
as
begin
	if @@trancount > 0 and @trName is null
	begin
		commit

		if @@error <> 0
		begin
-- ошибка подтверждения транзакции
			return 6
		end
	end

	return 0
end

CREATE  proc  dbo.tran_Rollback /*
 * Откат транзакции
 * Работает в паре с tran_Begin
 * на вход передается имя точки сохранения (транзакции), возвращенное tran_Begin
 * Если вызов идет из транзакции и @trName равно null, то выполняется откат всей транзакции,
 * иначе выполняется откат до точки сохранения @trName
 */
(@trName DObjectName)
as
begin
	if @@trancount > 0
	begin
		if @trName is null
			rollback tran
		else
			rollback tran @trName

		if @@error <> 0
		begin
-- ошибка отката транзакции
			return 7
		end
	end

	return 0
end
Листинг 10.1.

Возврат результатов

Хранимые процедуры сообщают свой "статус возврата", который указывает, была ли выполнена процедура полностью, или нет, а также причины неудачи. Это значение может храниться в переменной, которая передается процедуре при ее вызове, и использоваться в последующих операторах Transact-SQL. Другой способ возврата информации из хранимых процедур состоит в возврате значений через выходные параметры. Параметры, определенные как выходные, в операторе create procedure (создать процедуру) или execute (выполнить) используются для возврата значений в место вызова процедуры. Затем с помощью условных операторов можно проверить возвращаемое значение.

Код возврата и выходные параметры позволяют разделить хранимые процедуры на модули. Группа SQL операторов, которые используются несколькими хранимыми процедурами, могут быть объединены в одну процедуру, которая сообщает свой статус выполнения или значения своих параметров вызывающей процедуре. Например, многие системные процедуры, поставляемые с SQL Сервером, обращаются к процедуре, которая проверяет являются ли указанные параметры правильными идентификаторами.

Если в операторах create procedure и execute указывается опция output в названии параметра, то процедура возвращает значение этого параметра вызывающему объекту. Этим объектом может быть SQL пакет или другая хранимая процедура, которые используют возвращаемые значения в своей дальнейшей работе. Если возвращаемые параметры используются в операторе execute, который является частью пакета, то значения возвращаемых параметров вместе с заголовком выводятся на экран перед выполнением последующих операторов пакета.

declare @tranName DObjectName
declare @retCode integer
begin

	execute tran_Begin @tranName output

select @tranName
.........................................

Oracle

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

create or replace procedure my_proc(i in number := 123,
   j out number, k in out number /* error := 123 */)
as
begin
    dbms_output.put_line('i = ' || i);
    dbms_output.put_line('j = ' || j);
    dbms_output.put_line('k = ' || k);

    if i is null then
        return;
    end if;

-- error
--    i := 10;
    j := 20;
    k := 30;
end;

create or replace procedure my_proc_error
  (i number /* error (1) */, s varchar2 /* error (10) */)
as
begin
    null;
end;

declare
    a number;
    b number;
    c number;
begin
    a := 1;
    b := 2;
    c := 3;
    my_proc(a, b, c);

    dbms_output.put_line('a = ' || a);
    dbms_output.put_line('b = ' || b);
    dbms_output.put_line('c = ' || c);
end;

Создание функций

create or replace function my_fun(i in number) return number
as
    j number;
begin
    j := i+10;
    return j;    
end;
    
declare
    a number;
begin
    a := 12+my_fun(10);
    dbms_output.put_line('a = ' || a);
end;

create or replace function my_fun(i in number) return number
as
    j number;

    procedure p(n in out number)
    as
    begin
        n := n+i+j;        
    end;
begin
    j := 3;
    p(j);
    j := j+i+10;
    return j;    
end;
    
declare
    a number;
begin
    a := 12+my_fun(10);
    dbms_output.put_line('a = ' || a);
end;

Вызов процедур и функций

create or replace procedure my_p
 (s varchar2, i number := 10, j number := 20)
as
begin
    null;
end;

declare
    a number;
    b number;
    s varchar2(100);
begin
    my_p(s);
    my_p(s, a);
    my_p(s, a, b);
    my_p(s=>s, j=>b);
    my_p(s, j=>b);
end;
< Лекция 9 || Лекция 10: 12 || Лекция 11 >
Александра Каева
Александра Каева
Светлана Токаревская
Светлана Токаревская

Добрый день! Скажите пожалуйста, так и задумано, что в каждой лекции приложен один и тот же приктикум?

Алексей Мендрин
Алексей Мендрин
Украина
Уалихан Джумадилов
Уалихан Джумадилов
Россия