mysql创建存储过程

环境

OS: Red Hat Linux As 5

Mysql:5.0.22

 

1.指定数据库

mysql> use test;
Database changed

 

2.创建表

mysql> create table tb_test(id int primary key auto_increment, name varchar(10));
Query OK, 0 rows affected (0.01 sec)

 

3.写入数据

mysql> insert into tb_test(name) values('zhang');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_test(name) values('zeng');
Query OK, 1 row affected (0.00 sec)

mysql> insert into tb_test(name) values('ming');
Query OK, 1 row affected (0.00 sec)

 

mysql> select * from tb_test;
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | zeng  |
|  3 | ming  |
+----+-------+
3 rows in set (0.00 sec)

 

4. 创建存储过程

mysql> delimiter $ -- delimiter $是设置 $为命令终止符号,代替默认的分号,因为分号有其他用处.
mysql> create procedure sp_test(IN pi_id int, OUT po_name varchar(10))
    -> begin
    -> select * from test.tb_test;
    -> select tb_test.name into po_name from test.tb_test where tb_test.id = pi_id;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ; -- 恢复分号作为分隔终止符号

 

5.调用存储过程

mysql> set    @po_name='';
Query OK, 0 rows affected (0.00 sec)

mysql> call   sp_test(1,@po_name);
+----+-------+
| id | name  |
+----+-------+
|  1 | zhang |
|  2 | zeng  |
|  3 | ming  |
+----+-------+
3 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

 

mysql> select @po_name;
+----------+
| @po_name |
+----------+
| zhang    |
+----------+
1 row in set (0.00 sec)

 

--The End--