(5.6)
drop function fun1;
delimiter //
create function fun1(p1 integer)
returns integer
no sql
begin
declare ret int;
set ret = p1;
if ret = 0 then
return ret;
else
signal sqlstate value '08S01'
set message_text = 'an error occurred';
end if;
end//
delimiter ;
select fun1(0);
select fun1(1);
(12cR1)
create or replace function fun1(p1 in integer) return integer
as
ret integer;
reigai exception;
pragma exception_init(reigai, -12154);
begin
ret := p1;
if ret = 0 then
return ret;
else
raise reigai;
end if;
end;
/
select fun1(0) from dual;
select fun1(1) from dual;
create or replace function fun2(p1 in integer) return integer
as
ret integer;
begin
ret := p1;
if ret = 0 then
return ret;
else
raise_application_error(-20000, 'an error occurred');
end if;
end;
/
select fun2(0) from dual;
select fun2(1) from dual;
(11)
create or replace function fun1(in p1 integer) returns integer
as
$$
declare
ret integer;
begin
ret := p1;
if ret = 0 then
return ret;
else
raise exception sqlstate '08000' using message = 'an error occurred';
end if;
end;
$$ language 'plpgsql';
select fun1(0);
select fun1(1);
(2014)
drop procedure proc1;
go
create procedure proc1(@p1 integer)
as
begin
declare @ret integer;
set @ret = @p1;
if @ret = 0
begin
set @ret = 0;
end
else
begin
throw 50000, 'an error occurred', 255;
end
end
go
exec proc1 0;
exec proc1 1;
※SQL Serverの場合、function内にraise文は定義できない模様
