MySQL Tutorial/Procedure Function/Set

Assign value to a variable with set command

   <source lang="sql">

mysql> mysql> mysql> delimiter $$ mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->     DECLARE my_integer     INT;           /* 32-bit integer */
   ->
   ->     set my_integer = 12;
   ->
   ->     select "my_integer = " + 12;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> mysql> call myProc(); +----------------------+ | "my_integer = " + 12 | +----------------------+ | 12 | +----------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>

Concatenate string with integer

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->
   ->   DECLARE a INT;
   ->   DECLARE b VARCHAR(20);
   ->   DECLARE c INT;
   ->
   ->   SET a=99;
   ->   SET b="AAA";
   ->   SET c=CONCAT(a," ",b);
   ->   SELECT c;
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> call myProc(); +------+ | c | +------+ | 99 | +------+ 1 row in set (0.00 sec) Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>

Math operators

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc()

   -> BEGIN
   ->         DECLARE a INT DEFAULT 2;
   ->         DECLARE b INT DEFAULT 3;
   ->         DECLARE c FLOAT;
   ->
   ->         SET c=a+b; SELECT "a+b=",c;
   ->         SET c=a/b; SELECT "a/b=",c;
   ->         SET c=a*b; SELECT "a*b=",c;
   ->
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> mysql> call myProc(); +------+------+ | a+b= | c | +------+------+ | a+b= | 5 | +------+------+ 1 row in set (0.00 sec) +------+----------+ | a/b= | c | +------+----------+ | a/b= | 0.666667 | +------+----------+ 1 row in set (0.02 sec) +------+------+ | a*b= | c | +------+------+ | a*b= | 6 | +------+------+ 1 row in set (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> mysql> drop procedure myProc; Query OK, 0 rows affected (0.00 sec) mysql> mysql></source>

Using Set to declare variable in a procedure

   <source lang="sql">

mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE execute_immediate(in_sql varchar(4000))

   -> BEGIN
   ->
   ->   SET @tmp_sql=in_sql;
   ->   PREPARE s1 FROM @tmp_sql;
   ->   EXECUTE s1;
   ->   DEALLOCATE PREPARE s1;
   ->
   -> END$$

Query OK, 0 rows affected (0.00 sec) mysql> mysql> delimiter ; mysql> call execute_immediate("select 1+1"); +-----+ | 1+1 | +-----+ | 2 | +-----+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec) mysql> drop procedure execute_immediate; Query OK, 0 rows affected (0.00 sec)</source>