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>