新SQL语句,在复合语句中声明变量的指令是DECLARE。 (1) Example with two DECLARE statements两个DECLARE语句的例子CREATE PROCEDURE p8 ()BEGINDECLARE a INT;DECLARE b INT;SET a = 5;SET b = 5;INSERT INTO t VALUES (a);SELECT s1 * a FROM t WHERE s1 >=
新SQL语句,在复合语句中声明变量的指令是DECLARE。(1) Example with two DECLARE statements 两个DECLARE语句的例子 CREATE PROCEDURE p8 () BEGIN DECLARE a INT; DECLARE b INT; SET a = 5; SET b = 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */
(2)Example with no DEFAULT clause and SET statement 没有默认子句和设定语句的例子 CREATE PROCEDURE p9 () BEGIN DECLARE a INT /* there is no DEFAULT clause */; DECLARE b INT /* there is no DEFAULT clause */; SET a = 5; /* there is a SET statement */ SET b = 5; /* there is a SET statement */ INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; // /* I won't CALL this */
(3)Example with DEFAULT clause 含有DEFAULT子句的例子 CREATE PROCEDURE p10 () BEGIN DECLARE a, b INT DEFAULT 5; INSERT INTO t VALUES (a); SELECT s1 * a FROM t WHERE s1 >= b; END; //
(4)Example of CALL 调用的例子 mysql> CALL p10() // +--------+ | s1 * a | +--------+ | 25 | | 25 | +--------+ 2 rows in set (0.00 sec) Query OK, 0 rows affected (0.00 sec)
结果显示了过程能正常工作 (5) Scope 作用域 CREATE PROCEDURE p11 () BEGIN DECLARE x1 CHAR(5) DEFAULT 'outer'; BEGIN DECLARE x1 CHAR(5) DEFAULT 'inner'; SELECT x1; END; SELECT x1; END; //
调用作用域例子的过程: mysql> CALL p11()// +-------+ | x1 | +-------+ | inner | +-------+ +-------+ | x1 | +-------+ | outer | +-------+