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

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

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

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

declare @tranName DObjectName
declare @retCode integer
begin

	execute tran_Begin @tranName output

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

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;