blog迁移至:
http://www.micmiu.com
mysql自从5.0开始支持存储过程,上周末没事小试下,在此记录下自己的初步学习的过程。
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,是可以执行存储过程。
如果不确定自己的版本,可以通过下面的命令确认下:
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].创建存储过程
create procedure proc_name()
begin
………
end
[2].调用存储过程
call proc_name()
[3].删除存储过程
drop procedure proc_name
[5].show procedure status 显示数据库中所有存储的存储过程基本信息
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)
[6].show create procedure proc_name 显示某一个存储过程的详细信息
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] 创建一个简单的查询当前日期和时间的存储过程,如下:
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 | 定义结束符合为“|”
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用来测试存储过程:
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)
验证表是否创建成功,如下:
mysql> show tables|
+----------------+
| Tables_in_test |
+----------------+
| log4j_info |
| tb_name_info |
+----------------+
2 rows in set (0.00 sec)
[3] 自己初始化一批数据以便测试,然后创建一个存储过程:用于查询表的记录总数
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)
[5] 创建一个存储过程:用于查询表的记录总数并返回查询结果
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)
------------------------------分------------------------------隔------------------------------线------------------------------
分享到:
相关推荐
Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三种循环等等) Mysql存储过程常用语句模板(含变量,if,三...
c++实现调mysql存储过程,实现存储过程的出参入参,可以支持查询多数据返回,还有存储过程的复杂数据的增删改等
mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页 mysql存储过程实现分页
本文实例讲述了mysql存储过程之返回多个值的方法。分享给大家供大家参考,具体如下: mysql存储函数只返回一个值。要开发返回多个值的存储过程,需要使用带有INOUT或OUT参数的存储过程。咱们先来看一个orders表它的...
mysql经典教程+mysql存储过程讲解 重点讲解Mysql的存储过程,触发器,游标的使用 对mysql不太熟的朋友可以好好学习。。。
MySQL存储过程学习 MySQL存储过程 MySQL存储过程
本文实例讲述了MySQL存储过程的异常处理方法。分享给大家供大家参考。具体如下: mysql> mysql> delimiter $$ mysql> mysql> CREATE PROCEDURE myProc -> (p_first_name VARCHAR(30), -> p_last_name VARCHAR(30)...
MySQL存储过程 MySQL存储过程 MySQL存储过程 MySQL存储过程 MySQL存储过程
mysql存储过程 mysql存储过程 mysql存储过程 mysql存储过程
MySql 分页 存储过程 MySql 分页 存储过程 MySql 分页 存储过程
MySQL存储过程经典教程MySQL存储过程经典教程MySQL存储过程经典教程MySQL存储过程经典教程MySQL存储过程经典教程MySQL存储过程经典教程
mysql 存储过程 实战
mysql存储过程,存储函数练习,里面有详细代码,供大家参考
mysql存储过程ppt
NULL 博文链接:https://669341085.iteye.com/blog/800576
mysql存储过程实例详细介绍了mysql存储过程的开发步骤
php中调用MySQL存储过程示例
MySQL存储过程实例教程,MySQL存储过程实例教程