9. Stored procedures

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

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

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

Хранимые процедуры сообщают свой «статус возврата», который указывает, была ли выполнена процедура полностью, или нет, а также причины неудачи. Это значение может храниться в переменной, которая передается процедуре при ее вызове, и использоваться в последующих операторах 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;

Передача параметров. Атрибут NOCOPY

create or replace procedure my_p2(s1 out varchar2, s2 in out nocopy varchar2)
as
begin
    s1 := 'aaaaaaaaaaaaaaaaaaaaaaaaaa';
    s2 := 'bbbbbbbbbbbbbbbbbbbbbbbbbb';
end;

Модули PL/SQL

create or replace package PackageTest
as
    type point is record
        (
            x number,
            y number
         );

    tpoint point;
    n number;
         
    procedure p(ii in number, ss out varchar2);
    function f(n in point) return number;
    function f(x in number, y in number) return number;
end PackageTest;

create or replace package body PackageTest
as
    type t1 is record
        (
            i number,
            j number
         );

    cnt number;
    trow1 test%rowtype;
     
    procedure p(ii in number, ss out varchar2)
    is
    begin
        select s into ss from test where i = ii;
    exception
        when no_data_found then
            ss := 'empty string';
    end;

    function f(n in point) return number
    as
    begin
        return n.x+n.y;
    end;

    function f(x in number, y in number) return number
    as
    begin
        return x+y;
    end;
begin
    trow1.i := 1;
    trow1.s := 'null string';
    n := 100;
    cnt := -10;
end PackageTest;

create or replace procedure PackageTest_test
as
-- error
--    d1 PackageTest.tl;
    s varchar2(100);
    n PackageTest.point;
begin
    n.x := 100;
    n.y := 200;
    PackageTest.p(1, s);
    dbms_output.put_line('s = ' || s);
    dbms_output.put_line('f((100, 100)) = ' || PackageTest.f(n));
    dbms_output.put_line('f(100, 100) = ' || PackageTest.f(n));
    dbms_output.put_line('n = ' || PackageTest.n);
-- error
--    dbms_output.put_line('cnt = ' || PackageTest.cnt);
end;

Просмотр объектов и компиляция

desc user_objects

select object_name, object_type, status from user_objects;

Перекомпиляция процедур и функций

create or replace function f(i in number)
return varchar2
as
begin
    return to_char(i+1);
end;

create or replace procedure p(ii in number, ss out varchar2)
as
begin
    select s into ss from test where i = ii;
exception
    when no_data_found then
        ss := 'no data';
end;

select substr(object_name, 1, 30), object_type, status from user_objects;

select substr(object_name, 1, 30), object_type, status from user_objects where object_type in 
             ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') and (object_name like 'P%' or object_name like 'F%');

declare
    i number;
    s varchar2(100);
begin
    i := 10;
    s := f(i);
    dbms_output.put_line(s);

    i := 1;
    p(i, s);
    dbms_output.put_line(s);
end;

alter table test add h date;
alter table test drop column h;

alter procedure p compile;

select * from user_errors;

alter function f compile;

alter package packagetest compile specification;
alter package packagetest compile body;
alter package packagetest compile package;

select substr(object_name, 1, 30), object_type, status from user_objects where object_type in 
             ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY') and (object_name like 'P%' or object_name like 'F%');

select * from user_errors;

Скрипт для перекомпиляции процедур и функций

Необходимо поместить в файл, запуск @<file>.sql

----------------------------------------------------
set echo off
set linesize 1000
set pagesize 500
set heading off
set feedback off
spool c:\tmp\a.sql
select 'ALTER ' || object_type || ' ' || object_name || ' COMPILE;' from user_objects where object_type in 
        ('PROCEDURE', 'FUNCTION', 'PACKAGE');
select 'SELECT * FROM USER_ERRORS;' from dual;
spool off
@c:\tmp\a.sql
host del c:\tmp\a.sql
-----------------------------------------------------

Вложенные страницы