mysql怎么调用存储过程 mysql存储过程详细教程(3)

创建:

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
2
3
4
5
6
7
8
9

执行结果:

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.---------- 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

局部变量声明一定要放在存储过程体的开始

DECLAREvariable_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';   
1
2
3
4
5

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=123;  
17.mysql > SELECT @z;  
18.------  
19.| @z   |  
20.------  
21.|  6   |  
22.------  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

本文来自电脑杂谈，转载请注明本文网址：
http://www.pc-fly.com/a/tongxinshuyu/article-59713-3.html