MySQL5新语句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 

+-------+

（出处：清风学院）