mysql存储过程教程（mysql存储过程使用）

MySQL 5.0 版本开始支持存储过程。

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

• 将重复性很高的一些操作，封装到一个存储过程中，简化了对这些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输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

建议：

• 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 students SET NAME = _name, age = _age, city = _city WHERE id = _id; END; #执行存储过程： call update_student(9, '李大个', 22, '杭州');

删除：

#创建存储过程 CREATE PROCEDURE delete_student_by_id (_id INT) BEGIN DELETE FROM students WHERE 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

,