mysql自从5.0开始支持存储过程，上周末没事小试下，在此记录下自己的初步学习的过程。
|
1 2 3 4 5 6 7 8 9 10 |
C:\Users\michael>mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.1.51-community MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement |
从上面的信息可以看出我的版本是5.1.51，是可以执行存储过程。
如果不确定自己的版本，可以通过下面的命令确认下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
mysql> select VERSION(); +------------------+ | VERSION() | +------------------+ | 5.1.51-community | +------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'version'; +---------------+------------------+ | Variable_name | Value | +---------------+------------------+ | version | 5.1.51-community | +---------------+------------------+ 1 row in set (0.00 sec) |
【一】基本命令及语法
[1].创建存储过程
|
1 2 3 4 |
create procedure proc_name() begin ……… end |
[2].调用存储过程:
|
1 |
call proc_name() |
[3].删除存储过程:
|
1 |
drop procedure proc_name |
[4].show procedure status 显示数据库中所有存储的存储过程基本信息
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> show procedure status; +------+-----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+-- --------------------+--------------------+ | Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | c ollation_connection | Database Collation | +------+-----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+-- --------------------+--------------------+ | test | helloproc | PROCEDURE | root@localhost | 2011-01-08 22:56:16 | 2011-01-08 22:56:16 | DEFINER | | utf8 | u tf8_general_ci | utf8_general_ci | | test | proc_sysdate | PROCEDURE | root@localhost | 2011-01-09 12:53:15 | 2011-01-09 12:53:15 | DEFINER | | utf8 | u tf8_general_ci | utf8_general_ci | | test | proc_test_count | PROCEDURE | root@localhost | 2011-01-09 18:12:36 | 2011-01-09 18:12:36 | DEFINER | | utf8 | u tf8_general_ci | utf8_general_ci | +------+-----------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+-- --------------------+--------------------+ 3 rows in set (0.00 sec) |
[5].show create procedure proc_name 显示某一个存储过程的详细信息:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
mysql> show create procedure proc_sysdate; +--------------+----------+------------------------------------------------------------------------------------------+----------------------+--------- -------------+--------------------+ | Procedure | sql_mode | Create Procedure | character_set_client | collatio n_connection | Database Collation | +--------------+----------+------------------------------------------------------------------------------------------+----------------------+--------- -------------+--------------------+ | proc_sysdate | | CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_sysdate`() begin select SYSDATE(); end | utf8 | utf8_general_ci | utf8_general_ci | +--------------+----------+------------------------------------------------------------------------------------------+----------------------+--------- -------------+--------------------+ 1 row in set (0.00 sec) |
【二】简单范例
[1] 创建一个简单的查询当前日期和时间的存储过程，如下：
|
1 2 3 4 5 6 7 8 9 |
mysql> use test Database changed mysql> delimiter | mysql> create procedure proc_sysdate() -> begin -> select SYSDATE(); -> end -> | Query OK, 0 rows affected (0.00 sec) |
ps：delimiter | 定义结束符合为“|”
|
1 2 3 4 5 6 7 8 9 |
mysql> call proc_sysdate()| +---------------------+ | SYSDATE() | +---------------------+ | 2011-01-09 12:53:30 | +---------------------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.02 sec) |
[2] 创建一个表:TB_NAME_INFO用来测试存储过程：
|
1 2 3 4 5 6 7 8 9 10 |
mysql> DROP TABLE IF EXISTS `TB_NAME_INFO`; -> CREATE TABLE `TB_NAME_INFO`( -> `ID` smallint(5) NOT NULL AUTO_INCREMENT, -> `NAME` varchar(50) NOT NULL, -> PRIMARY KEY(`ID`)) -> ENGINE=InnoDB DEFAULT CHARSET=utf8; -> | Query OK, 0 rows affected (0.08 sec) Query OK, 0 rows affected (0.08 sec) |
验证表是否创建成功，如下：
|
1 2 3 4 5 6 7 8 |
mysql> show tables| +----------------+ | Tables_in_test | +----------------+ | log4j_info | | tb_name_info | +----------------+ 2 rows in set (0.00 sec) |
[3] 自己初始化一批数据以便测试，然后创建一个存储过程：用于查询表的记录总数
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
mysql> delimiter | mysql> create procedure proc_test_count() -> begin -> select count(*) from tb_name_info; -> end -> | Query OK, 0 rows affected (0.06 sec) mysql> call proc_test_count()| +----------+ | count(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) Query OK, 0 rows affected (0.00 sec) |
[4] 创建一个存储过程：用于查询表的记录总数并返回查询结果:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
mysql> create procedure proc_test_return_count(out ct int) -> begin -> select count(*) into ct from tb_name_info; -> end -> | Query OK, 0 rows affected (0.00 sec) mysql> call proc_test_return_count(@ct)| Query OK, 0 rows affected (0.01 sec) mysql> select @ct| +------+ | @ct | +------+ | 4 | +------+ 1 row in set (0.00 sec) |
原创文章，转载请注明： 转载自micmiu – 软件开发+生活点滴[ http://www.micmiu.com/ ]
本文链接地址: http://www.micmiu.com/db/mysql/mysql-procedure-demo/
0 条评论。