5.mysql> SELECT @p_out;
16.+-------+
17.| p_out |
18.+-------+
19.| 2 |
20.+-------+
Ⅲ. INOUT参数例子
创建:
1. mysql > DELIMITER //
2. mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)
3. -> BEGIN
4. -> SELECT p_inout;
5. -> SET p_inout=2;
6. -> SELECT p_inout;
7. -> END;
8. -> //
9. mysql > DELIMITER ;
执行结果:
1. mysql > SET @p_inout=1;
2. mysql > CALL demo_inout_parameter(@p_inout) ;
3. +---------+
4. | p_inout |
5. +---------+
6. | 1 |
7. +---------+
8.
9. +---------+
10.| p_inout |
11.+---------+
12.| 2 |
13.+---------+
14.
15.mysql > SELECT @p_inout;
16.+----------+
17.| @p_inout |
18.+----------+
19.| 2 |
20.+----------+
(4). 变量
Ⅰ. 变量定义
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中，datatype为MySQL的数据类型，如:int,float, date, varchar(length)
例如:
1. DECLARE l_int int unsigned default 4000000;
2. DECLARE l_numeric number(8,2) DEFAULT 9.95;
3. DECLARE l_date date DEFAULT '1999-12-31';
4. DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
5. DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
Ⅱ. 变量赋值
SET 变量名= 表达式值[,variable_name = expression ...]
Ⅲ. 用户变量
. 在MySQL客户端使用用户变量
1. mysql > SELECT 'Hello World' into @x;
2. mysql > SELECT @x;
3. +-------------+
4. | @x |
5. +-------------+
6. | Hello World |
7. +-------------+
8. mysql > SET @y='Goodbye Cruel World';
9. mysql > SELECT @y;
10.+---------------------+
11.| @y |
12.+---------------------+
13.| Goodbye Cruel World |
14.+---------------------+
15.
16.mysql > SET @z=1+2+3;
17.mysql > SELECT @z;
18.+------+
19.| @z |
20.+------+
21.| 6 |
22.+------+
. 在存储过程中使用用户变量
1. mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
2. mysql > SET @greeting='Hello';
3. mysql > CALL GreetWorld( );
4. +----------------------------+
5. | CONCAT(@greeting,' World') |
6. +----------------------------+
7. | Hello World |
8. +----------------------------+
. 在存储过程间传递全局范围的用户变量
1. mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
2. mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
3. mysql> CALL p1( );
4. mysql> CALL p2( );
5. +-----------------------------------------------+
6. | CONCAT('Last procedure was ',@last_proc |
7. +-----------------------------------------------+
8. | Last procedure was p1 |
9. +-----------------------------------------------+
注意:
①用户变量名一般以@开头
②滥用用户变量会导致程序难以理解及管理
(5). 注释
MySQL存储过程可使用两种风格的注释
双模杠：--
该风格一般用于单行注释
c风格：/* 注释内容*/ 一般用于多行注释
例如：
1. mysql > DELIMITER //
2. mysql > CREATE PROCEDURE proc1 --name存储过程名
3. -> (IN parameter1 INTEGER) /* parameters参数*/
4. -> BEGIN /* start of block语句块头*/
5. -> DECLARE variable1 CHAR(10); /* variables变量声明*/
6. -> IF parameter1 = 17 THEN /* start of IF IF条件开始*/
7. -> SET variable1 = 'birds'; /* assignment赋值*/
8. -> ELSE
9. -> SET variable1 = 'beasts'; /* assignment赋值*/
10. -> END IF; /* end of IF IF结束*/
11. -> INSERT INTO table1 VALUES (variable1);/* statement SQL语句*/
12. -> END /* end of block语句块结束*/
13.