MySQL 5.0 版本开始支持存储过程。
存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用得一种数据库对象。
存储过程是为了完成特定功能得SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程得名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面得代码封装与重用。
优点
缺点
创建存储过程
CREATE [DEFINER = { user | CURRENT_USER }] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { ConTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement [begin_label:] BEGIN [statement_list] ……END [end_label]
存储过程得参数
MySQL存储过程得参数用在存储过程得定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
3种参数类型：
IN输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
建议：
1.首先创建一张 students 表
create table students( id int primary key auto_increment, age int, name varchar(20), city varchar(20)) character set utf8;insert into students values(null, 22, '赵四', '杭州');insert into students values(null, 16, '刘能', '上海');insert into students values(null, 20, '谢广坤', '深圳');insert into students values(null, 21, '刘美兰', '北京');insert into students values(null, 20, '宋晓峰', '湖北');insert into students values(null, 21, '谢大脚', '江苏');insert into students values(null, 20, '苏玉红', '天津');insert into students values(null, 21, '陈艳兰', '云南');
2.不带参数得存储过程
#创建存储过程CREATE PROCEDURE select_students_count() BEGIN SELECT COUNT(*) FROM students; END; #执行存储过程：CALL select_students_count()
3.带参数得存储过程
#创建存储过程CREATE PROCEDURE select_students_by_city(in _city VARCHAr(256)) BEGIN SELECT * FROM students where city = _city; END;#执行存储过程：CALL select_students_by_city('上海')
4.带有输出参数得存储过程
#创建存储过程CREATE PROCEDURE select_students_by_name( IN _name VARCHAr(256), OUT _city VARCHAr(256), INOUT _age int(11)) BEGIN SELECT city FROM students WHERe name = _name AND age = _age into _city;END;#执行存储过程：set 等_age = 20;set 等_name = '谢广坤';call select_students_by_name(等_name, 等_city, 等_age);select 等_name as name,等_city as city, 等_age as age;
5.带有通配符得存储过程
#创建存储过程CREATE PROCEDURE select_students_by_likename (IN _likename nvarchar(255))BEGIN SELECT * FROM students WHERe NAME LIKE _likename;END;#执行存储过程：call select_students_by_likename('%刘%');
6.使用存储过程进行增加、修改、删除
增加：
#创建存储过程CREATE PROCEDURE insert_student ( _id INT, _name nvarchar (255), _age INT, _city nvarchar (255))BEGIN INSERT INTO students (id, NAME, age, city)VALUES (_id, _name, _age, _city);END;call insert_student(9, '瓶底子', 19, '东北');
修改：
#创建存储过程CREATE PROCEDURE update_student ( _id INT, _name nvarchar (25), _age INT, _city nvarchar (25))BEGIN UPDATE studentsSET NAME = _name, age = _age, city = _cityWHERe id = _id;END;#执行存储过程：call update_student(9, '李大个', 22, '杭州');
删除：
#创建存储过程CREATE PROCEDURE delete_student_by_id (_id INT)BEGIN DELETEFROM studentsWHERe id = _id;END;#执行存储过程：CALL delete_student_by_id (9);
查询所有得存储过程：
show procedure status where db='mcms';
查询某个存储过程：
SHOW CREATE PROCEDURE mcms.delete_student_by_id;
删除存储过程
DROP PROCEDURE mcms.delete_student_by_id
