本文出自明月工作室:https://www.freebytes.net/it/database/mysql-cunchu.html
一. 定义
存储过程 Procedure 是一组为了完成特定功能的 SQL 语句集合,经编译后存储在数据库中,用户通过指定存储过程的名称并给出参数来执行。存储过程中可以包含逻辑控制语句和数据操纵语句,它可以接受参数、输出参数、返回单个或多个结果集以及返回值。
以上是官方的定义。简单来说,如果把sql当成是代码,其实存储过程就相当于函数。把一组具备特定功能的sql语句封装成起来而已。所以他的本质是为了更好的执行对数据库的操作。那么好在哪里呢?
二. 存储过程的优势
1. 性能。存储过程在创建时就已经在数据库服务器中编译好并存储起来,调用时只需提供过程名和参数,就可以直接使用。而sql语句,没执行一句就要编译一次,这在sql语句发送并不频繁的情况下还好,但是如果短时间大量发送sql语句的情况下,不仅会降低网络性能也会增加数据库负担。
2. 可完成更复杂的数据库控制。由于存储过程中可以包含逻辑控制语句和数据操纵语句,类似遍历、if这种逻辑可以直接卸载过程中。
3.我在程序中写了一段代码,发送一千条sql插入语句到本地数据库,大概花了6672ms,而在存储过程中只花了4689ms。以下分别是代码和存储过程sql:
@Autowired private JdbcTemplate jdbcTemplate; public static final String INSERT_SQL="insert into user (id, name, age) values(?,?,?)"; public void insert(){ long begin = System.currentTimeMillis(); for (int i = 600000; i < 601000; i++) { jdbcTemplate.update(INSERT_SQL,i,"林"+i,10+Math.random()*10); } long end = System.currentTimeMillis(); System.out.println(end-begin); }
CREATE PROCEDURE test_insert2 () BEGIN DECLARE i INT DEFAULT 601000; WHILE i<602000 DO insert into `user` (id,NAME,age) values (i,CONCAT('林',i),18); SET i=i+1; END WHILE; COMMIT; END; CALL test_insert2()
三. 存储过程基本语法和变量讲解
1. delimiter // 该语句指定MySQL把“//”当作分隔符。也就是说,如果你在MySQL client中输入这句,则“;”符号不再被当作结束符。
2. create procedure getAllUser() 表示创建存储过程,其名为getAllUser()。
3. call getAllUser ; 调用存储过程getAllUser。
4. drop procedure getAllUser; 删除存储过程。
5. begin...end 之间编写过程体,即写sql语句和逻辑代码。
6. 存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:
- IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
- OUT:该值可在存储过程内部被改变,并可返回
- INOUT:调用时指定,并且可被改变和返回
四. 分别在MySQL client中和Navicat中创建存储过程
- 在client创建一个查询所有user表记录的过程。


最后输入 还原分隔符为 “;”

调用过程

删除过程
- 在Navicat中创建一个插入user表数据的存储过程 ,并且传递两个参数

右键新建函数,选择过程



在这里写逻辑语句吧,完整如下:
BEGIN DECLARE i INT DEFAULT initValue; WHILE i<=endValue DO insert into `user` (id,NAME,age) values (i,CONCAT('林',i),18); SET i=i+1; END WHILE; COMMIT; END
这里的意思是 把第一个参数赋给变量i,当i小于第二个参数值时,一直进行while循环,每次循环i+1。
Navicat里面点击sql预览可以看到完整的代码:

按下ctrl+s保存:

然后运行,输入两个参数,逗号隔开:

就可以插入数据了。
五. 在java程序中调用存储过程
@Autowired private DataSource dataSource; public ListgetUser() { List users = new ArrayList (10000); User user = new User(); try { CallableStatement callableStatement = dataSource.getConnection().prepareCall("{call getUser}"); ResultSet rs = null; //执行查询操作,并获取结果集 rs = callableStatement.executeQuery(); while (rs.next()){ user.setName(rs.getString("name")); user.setAge(rs.getInt("age")); user.setId(rs.getInt("id")); users.add(user); } } catch (SQLException e) { e.printStackTrace(); } return users; }
调用一个getUser()的存储过程,并将结果封装成实体。主要是 CallableStatement callableStatement = dataSource.getConnection().prepareCall("{call getUser}");这句代码。这里我认为应该有更成熟的调用方式才对,但是我也懒得找资料了。反正原理都一样。
六. 自定义函数
mysql的自定义函数其实就是我们自己去定义类似sum(...)、max(...)这种函数,很简单,创建起来跟存储过程差不多,就不多做讲解了。
create function sum1(x int,y int) returns int(20)
begin
//编写函数体
return xxx ;
end ;
使用 select sum1(215,555)
删除 drop sum1