You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
197 lines
3.9 KiB
197 lines
3.9 KiB
drop database if exists demo;
|
|
create database demo;
|
|
use demo;
|
|
create procedure proc_top_a(p1 integer)
|
|
begin
|
|
## DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND
|
|
begin
|
|
end;
|
|
select "Starting ...";
|
|
call proc_middle_a(p1);
|
|
select "The end";
|
|
end
|
|
$$
|
|
create procedure proc_middle_a(p1 integer)
|
|
begin
|
|
DECLARE l integer;
|
|
# without RESIGNAL:
|
|
# Should be: DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND
|
|
DECLARE EXIT HANDLER for 1 /* not sure how to handle exceptions */
|
|
begin
|
|
select "Oops ... now what ?";
|
|
end;
|
|
select "In prod_middle()";
|
|
create temporary table t1(a integer, b integer);
|
|
select GET_LOCK("user_mutex", 10) into l;
|
|
insert into t1 set a = p1, b = p1;
|
|
call proc_bottom_a(p1);
|
|
select RELEASE_LOCK("user_mutex") into l;
|
|
drop temporary table t1;
|
|
end
|
|
$$
|
|
create procedure proc_bottom_a(p1 integer)
|
|
begin
|
|
select "In proc_bottom()";
|
|
if (p1 = 1) then
|
|
begin
|
|
select "Doing something that works ...";
|
|
select * from t1;
|
|
end;
|
|
end if;
|
|
if (p1 = 2) then
|
|
begin
|
|
select "Doing something that fail (simulate an error) ...";
|
|
drop table no_such_table;
|
|
end;
|
|
end if;
|
|
if (p1 = 3) then
|
|
begin
|
|
select "Doing something that *SHOULD* works ...";
|
|
select * from t1;
|
|
end;
|
|
end if;
|
|
end
|
|
$$
|
|
call proc_top_a(1);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
In proc_bottom()
|
|
In proc_bottom()
|
|
Doing something that works ...
|
|
Doing something that works ...
|
|
a b
|
|
1 1
|
|
The end
|
|
The end
|
|
call proc_top_a(2);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
In proc_bottom()
|
|
In proc_bottom()
|
|
Doing something that fail (simulate an error) ...
|
|
Doing something that fail (simulate an error) ...
|
|
ERROR 42S02: Unknown table 'demo.no_such_table'
|
|
call proc_top_a(3);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
ERROR 42S01: Table 't1' already exists
|
|
call proc_top_a(1);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
ERROR 42S01: Table 't1' already exists
|
|
drop temporary table if exists t1;
|
|
create procedure proc_top_b(p1 integer)
|
|
begin
|
|
select "Starting ...";
|
|
call proc_middle_b(p1);
|
|
select "The end";
|
|
end
|
|
$$
|
|
create procedure proc_middle_b(p1 integer)
|
|
begin
|
|
DECLARE l integer;
|
|
DECLARE EXIT HANDLER for SQLEXCEPTION, NOT FOUND
|
|
begin
|
|
begin
|
|
DECLARE CONTINUE HANDLER for SQLEXCEPTION, NOT FOUND
|
|
begin
|
|
/* Ignore errors from the cleanup code */
|
|
end;
|
|
select "Doing cleanup !";
|
|
select RELEASE_LOCK("user_mutex") into l;
|
|
drop temporary table t1;
|
|
end;
|
|
RESIGNAL;
|
|
end;
|
|
select "In prod_middle()";
|
|
create temporary table t1(a integer, b integer);
|
|
select GET_LOCK("user_mutex", 10) into l;
|
|
insert into t1 set a = p1, b = p1;
|
|
call proc_bottom_b(p1);
|
|
select RELEASE_LOCK("user_mutex") into l;
|
|
drop temporary table t1;
|
|
end
|
|
$$
|
|
create procedure proc_bottom_b(p1 integer)
|
|
begin
|
|
select "In proc_bottom()";
|
|
if (p1 = 1) then
|
|
begin
|
|
select "Doing something that works ...";
|
|
select * from t1;
|
|
end;
|
|
end if;
|
|
if (p1 = 2) then
|
|
begin
|
|
select "Doing something that fail (simulate an error) ...";
|
|
drop table no_such_table;
|
|
end;
|
|
end if;
|
|
if (p1 = 3) then
|
|
begin
|
|
select "Doing something that *SHOULD* works ...";
|
|
select * from t1;
|
|
end;
|
|
end if;
|
|
end
|
|
$$
|
|
call proc_top_b(1);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
In proc_bottom()
|
|
In proc_bottom()
|
|
Doing something that works ...
|
|
Doing something that works ...
|
|
a b
|
|
1 1
|
|
The end
|
|
The end
|
|
call proc_top_b(2);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
In proc_bottom()
|
|
In proc_bottom()
|
|
Doing something that fail (simulate an error) ...
|
|
Doing something that fail (simulate an error) ...
|
|
Doing cleanup !
|
|
Doing cleanup !
|
|
ERROR 42S02: Unknown table 'demo.no_such_table'
|
|
call proc_top_b(3);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
In proc_bottom()
|
|
In proc_bottom()
|
|
Doing something that *SHOULD* works ...
|
|
Doing something that *SHOULD* works ...
|
|
a b
|
|
3 3
|
|
The end
|
|
The end
|
|
call proc_top_b(1);
|
|
Starting ...
|
|
Starting ...
|
|
In prod_middle()
|
|
In prod_middle()
|
|
In proc_bottom()
|
|
In proc_bottom()
|
|
Doing something that works ...
|
|
Doing something that works ...
|
|
a b
|
|
1 1
|
|
The end
|
|
The end
|
|
drop database demo;
|
|
|