For more information read:
Remember these important tips. Lets take the variable VAR1. It can be confusing to remember when to use "@VAR1" or "VAR1" at times.
Simple function.
create database if not exists test_sp;
use test_sp;
drop procedure if exists n;
drop procedure if exists sp_i;
drop procedure if exists sp_iio;
drop procedure if exists sp_ioi;
drop procedure if exists sp_io;
drop procedure if exists sp_errorcheck;
DELIMITER //
CREATE PROCEDURE n()
BEGIN
SELECT now();
END //
DELIMITER ;
-- Simple procedure. It takes a number and adds 10.
DELIMITER //
CREATE PROCEDURE sp_i(IN no1 int4)
BEGIN
select 'start', no1;
select no1 + 10 into no1;
select 'end', no1;
END //
DELIMITER ;
-- Simple function, adds one day to time.
drop procedure if exists sp_o;
DELIMITER //
CREATE PROCEDURE sp_o(out VAR1 int, inout VAR2 int)
BEGIN
select 'start', VAR1, VAR2, @GLOBAL1;
set VAR1 = @GLOBAL1;
select 4 into VAR2;
select 'end', VAR1, VAR2, @GLOBAL1;
END //
DELIMITER ;
-- Simple procdure, adds two numbers together.
drop procedure if exists sp_iio;
DELIMITER //
CREATE PROCEDURE sp_iio(in no1 int, In no2 int, OUT no3 int, OUT no4 int)
BEGIN
DECLARE s int DEFAULT 0;
set s = no1 + no2;
select no3 + 1 into no3;
set no4 = 5;
select 'done', s, no1, no2, no3, no4;
END //
DELIMITER ;
-- another way
DELIMITER //
CREATE PROCEDURE sp_ioi(inout no1 int, In no2 int)
BEGIN
set no1 = no1 + no2;
select 'done', no1, no2;
END //
DELIMITER ;
-- simple one inout variables
DELIMITER //
CREATE procedure sp_io(inout no1 int)
BEGIN
declare temp int default 100;
select no1 + temp into no1;
select 'done', no1, temp;
END //
DELIMITER ;
-- simple one inout variables, with error
DELIMITER //
CREATE PROCEDURE sp_errorcheck(inout no1 int)
BEGIN
IF no1 is NULL then set no1 = -1; END IF;
set no1 = 1000;
select 'done', no1;
END //
DELIMITER ;
call n();
call sp_i(1);
set @temp1 = 2000;
set @temp2 = 2000;
call sp_iio(1,1, @temp2, @temp2);
select 'iio GOOD', @temp1, @temp2;
set @no3 = 2000;
set @no4 = 2000;
call sp_iio(1,1, @no3, @no4);
select 'iio BAD ', @no3, @no3;
set @io = 3000;
call sp_ioi(@io,1);
select 'ioi', @io;
call sp_io(@io);
select 'io', @io;
call sp_errorcheck(@io);
select 'ec', @io;
call sp_errorcheck(@io);
select 'ec', @io;
set @GLOBAL1 = 3;
set @i1 = 1;
set @i2 = 2;
select "env", @i1, @i2;
call sp_o(@i1, @i2);
select "env", @i1, @i2;
set @i1 = 1;
select "env", @i1;
call sp_i(@i1);
select "env", @i1;
MariaDB [test_sp]> set @i1 = 1; -------------- set @i1 = 1 -------------- Query OK, 0 rows affected (0.000 sec) MariaDB [test_sp]> select "env", @i1; -------------- select "env", @i1 -------------- +-----+------+ | env | @i1 | +-----+------+ | env | 1 | +-----+------+ 1 row in set (0.000 sec) MariaDB [test_sp]> call sp_i(@i1); -------------- call sp_i(@i1) -------------- +-------+------+ | start | no1 | +-------+------+ | start | 1 | +-------+------+ 1 row in set (0.000 sec) +-----+------+ | end | no1 | +-----+------+ | end | 11 | +-----+------+ 1 row in set (0.000 sec) Query OK, 1 row affected (0.000 sec) MariaDB [test_sp]> select "env", @i1; -------------- select "env", @i1 -------------- +-----+------+ | env | @i1 | +-----+------+ | env | 1 | +-----+------+ 1 row in set (0.000 sec) MariaDB [test_sp]>
For OUT, We see the @i1 variable is submitted as "1" to the stored procedure. We see the sp immediately makes it NULL. At the end it changes it to "3" and the environment variables @i1 becomes "3".
For INOUT, @i2 is submitted as "2". It does not change upon entering. It s changed to "4" and then the environment variable is changed.
MariaDB [test_sp]> set @GLOBAL1 = 3; -------------- set @GLOBAL1 = 3 -------------- Query OK, 0 rows affected (0.000 sec) MariaDB [test_sp]> set @i1 = 1; -------------- set @i1 = 1 -------------- Query OK, 0 rows affected (0.000 sec) MariaDB [test_sp]> set @i2 = 2; -------------- set @i2 = 2 -------------- Query OK, 0 rows affected (0.000 sec) MariaDB [test_sp]> select "env", @i1, @i2; -------------- select "env", @i1, @i2 -------------- +-----+------+------+ | env | @i1 | @i2 | +-----+------+------+ | env | 1 | 2 | +-----+------+------+ 1 row in set (0.000 sec) MariaDB [test_sp]> call sp_o(@i1, @i2); -------------- call sp_o(@i1, @i2) -------------- +-------+------+------+-------+----------+ | start | VAR1 | VAR2 | @VAR2 | @GLOBAL1 | +-------+------+------+-------+----------+ | start | NULL | 2 | NULL | 3 | +-------+------+------+-------+----------+ 1 row in set (0.000 sec) +-----+------+------+-------+----------+ | end | VAR1 | VAR2 | @VAR2 | @GLOBAL1 | +-----+------+------+-------+----------+ | end | 3 | 4 | NULL | 3 | +-----+------+------+-------+----------+ 1 row in set (0.000 sec) Query OK, 1 row affected (0.000 sec) MariaDB [test_sp]> select "env", @i1, @i2; -------------- select "env", @i1, @i2 -------------- +-----+------+------+ | env | @i1 | @i2 | +-----+------+------+ | env | 3 | 4 | +-----+------+------+ 1 row in set (0.000 sec) MariaDB [test_sp]>
drop procedure if exists sp_flow;
DELIMITER //
CREATE PROCEDURE sp_flow(IN n int4)
BEGIN
DECLARE s VARCHAr(20) default 'unknown';
IF n > 10 THEN SET s = 'High'; end if;
CASE
WHEN n = 10 THEN set s = 'nothing';
WHEN n < 10 THEN set s = 'Low';
ELSE begin end;
end case;
select s;
end;
//
delimiter ;
call sp_flow(11);
call sp_flow(10);
call sp_flow(9);
call sp_flow("a");
MariaDB [test_sp]> call sp_flow(11);
+------+
| s |
+------+
| High |
+------+
1 row in set (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
MariaDB [test_sp]> call sp_flow(10);
+---------+
| s |
+---------+
| nothing |
+---------+
1 row in set (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
MariaDB [test_sp]> call sp_flow(9);
call sp_flow("a");
+------+
| s |
+------+
| Low |
+------+
1 row in set (0.000 sec)
Query OK, 0 rows affected (0.000 sec)
MariaDB [test_sp]> call sp_flow("a");
ERROR 1366 (22007): Incorrect integer value: 'a' for column ``.``.`n` at row 1
drop procedure if exists sp_error;
DELIMITER //
CREATE PROCEDURE sp_error(IN n int4)
BEGIN
declare error1 int default 0;
declare error2 int default 0;
declare s1 text;
declare s2 text;
declare n2 int default 0;
set n2=n;
select "intitial", n, n2;
begin
declare c1 cursor for select 1;
DECLARE continue HANDLER FOR SQLEXCEPTION set error1 = 1;
open c1;
set n = n + 1;
close c1;
end;
select "add 1", n, n2;
begin
declare c2 cursor for select 1;
DECLARE continue HANDLER FOR SQLEXCEPTION set error2 = 1;
open c2;
set n = @n + "a";
close c2;
end;
select "add 'a'", n, n2;
CASE
WHEN error1 = 0 THEN set s1 = 'first exception not activated. Good';
WHEN error1 = 1 THEN set s1 = 'first execption activated. Bad';
ELSE begin end;
end case;
CASE
WHEN error2 = 0 THEN set s2 = '2nd exception not activated. Bad';
WHEN error2 = 1 THEN set s2 = '2nd exception activated. Good';
ELSE begin end;
end case;
select n, n2, error1, error2, s1, s2;
end ;
//
delimiter ;
call sp_error(1);
Notice how "n" becomes NULL when an error occurs. This is very very bad.
The error trapping still works. But I would recommend:
-------------- call sp_error(1) -------------- +----------+------+------+ | intitial | n | n2 | +----------+------+------+ | intitial | 1 | 1 | +----------+------+------+ 1 row in set (0.000 sec) +-------+------+------+ | add 1 | n | n2 | +-------+------+------+ | add 1 | 2 | 1 | +-------+------+------+ 1 row in set (0.000 sec) +---------+------+------+ | add 'a' | n | n2 | +---------+------+------+ | add 'a' | NULL | 1 | +---------+------+------+ 1 row in set (0.000 sec) +------+------+--------+--------+-------------------------------------+-------------------------------+ | n | n2 | error1 | error2 | s1 | s2 | +------+------+--------+--------+-------------------------------------+-------------------------------+ | NULL | 1 | 0 | 1 | first exception not activated. Good | 2nd exception activated. Good | +------+------+--------+--------+-------------------------------------+------OA-------------------------+ 1 row in set (0.001 sec) Query OK, 0 rows affected (0.001 sec) MariaDB [test_sp]>