[Level 2] The Samples of MySQL Stored Function and Stored Procedure.

FYI.

[SQL]

delimiter //

drop function if exists stanley.getSQL//
create function stanley.getSQL(name varchar(32))
returns text
begin
  return concat('Hello World, ',name,'!');
end//
delimiter ;

[mysql client]

mysql> select getSQL('stanley');

+-----------------------+
| getSQL('stanley')     |
+-----------------------+
| Hello World, stanley! |
+-----------------------+
1 row in set (0.00 sec)
 

mysql> select length(getSQL('stanley'));

+---------------------------+
| length(getSQL('stanley')) |
+---------------------------+
|                        21 |
+---------------------------+
1 row in set (0.00 sec)

[SQL]

delimiter //
drop procedure if exists stanley.setParam//
create procedure stanley.setParam(in id int, inout word text)
begin
  if id > 0 then
    set word=concat(word, '> 0');
  elseif id = 0 then
    set word=concat(word, '= 0');
  else
    set word=concat(word, '< 0');
  end if;
end//

drop procedure if exists stanley.run//
create procedure stanley.run(in id int)
begin
  declare output text;
  set output = 'The answer is ';
  call stanley.setParam(id, output);
  select output;
end//
delimiter ;

[mysql client]

mysql> call stanley.run(1);+-------------------+
| output            |
+-------------------+
| The answer is > 0 |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
 

mysql> call stanley.run(0);+-------------------+
| output            |
+-------------------+
| The answer is = 0 |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
 

mysql> call stanley.run(-1);

+-------------------+
| output            |
+-------------------+
| The answer is < 0 |
+-------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
 

Wish this helps.

regards,
Stanley Huang