mysql 存储过程

一、存储过程介绍

我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。

二、使用存储过程的优点

(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

三、mysql 存储实例

1、创建存储过程语句：

drop  procedure  if exists procedureName;create procedure  procedureName([过程参数[,……])过程体；来一个简单的实例：DELIMITER //create  procedure   pro1 ( in num int)begin SELECT  * FROM musicmodel  limit  num;end;//DELIMITER ;调用存户过程：call   pro1(10);

从这个简单的例子我们能看到还有很多参数要传递处理，以及begin   end  等块体，下面我们就来详细的学习；

2、分隔符的定义，使用关键词DELIMITER 来定义分隔符：delimiter 其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。默认情况        下，delimiter是分号;。在命令行客户端中，如果有一行命令以分号结束，那么回车后，mysql将会执行该命令。但有时候，不希望MySQL这么做。在为可能输入较多的语句，且语句中包含有分号，这种情况下，就需要事先把delimiter换成其它符号，如//或$$。这样只有当//出现之后，mysql解释器才会执行这段语句。

3、存储过程中的参数 in ，out，inout三种类型的参数：

（1）、in 输入,在默认情况下是in,实例如下：

drop  procedure  if exists  pro2;DELIMITER //create procedure  pro2 (in num int)begin select * from musicmodel  limit num;end;//DELIMITER ;

其中的in 表名这个存储过程有一个输入参数，而int 就是参数的类型。

(2)、out 输出参数 实例如下：

drop  procedure if exists pro3;DELIMITERcreate  procedure  procedure3(out titles VARCHAR(50))BEGINDECLARE ids INT;select max(id) into ids from musicmodel ;select title  into titles from musicmodel  where id =ids;select titles;END;call PROCEDURE3 (@s);

out 表示输出类型，而在调用的时候和in类型的不同要有@符号，这里我们看到了一个DECLARE  这个关键词是用来声明变量的，格式是DECLARE Parameter int/varchar(20) default 0/'0';定义变量并且指定一个默认值。

（3）、inout 输入输出参数，实例如下：

drop  procedure  if exists   pro4DELIMITER  //create procedure  pro4(inout num int)begin select max(id) into  num  from musicmodel  limit num;select num;end ;//DELIMITER ;set @num=10;call pro4(@num);

输入输出参数，三种参数方式都结束了，其实这是比较简单的例子，复杂的大家可以多些多练深入学习；我们这里相比之前的多了一个 那就是 set关键词，这个关键词是用来给便令赋值，如果变量之前就定义过则直接使用 set a=10;如果之前没有指定则使用 set @a=10;

（4）、我们看到存储过程中有 begin 和end 其实就是一个块和 c中的函数以及 java中的方法有点类似，就是定义了一个块，里面定义的变量在外面不能用，而外面定义的可以在里面使用，如果里面定义的变量和外面的一样的话，里面的会把外面的给覆盖了。一个过程中可以有多个 begin……end 块，并且可以嵌套。

简单总结： declare  和 set的联系与区别：

declare为对变量进行声明，声明必须制定变量的数据类型，只能写在过程的前面set是对变量赋值，可以放在过程的任何地方对没有declare声明过的变量赋值，该变量必须以加上@号，否则会报错即 set a=10(a在前面就declare 了)  set @a=10(a之前没有定义过)

4、存储过程中的if else ，在写存储过程的时候我们可能有一些判断语句，而mysql 存储过程中提供了存储过具体的看看。

（1）、简单使用if else 的实例：

drop procedure  if exists  pro5DELIMITER //create procedure pro5(in ids int,out mytitle varchar(50))begin declare  titles varchar(100) default 'titles';IF ids=100 then select title into titles from musicmodel where id = ids;end if;if ids=1146315 then select title into  titles  from musicmodel  where id = ids;end if;set mytitle =titles;select mytitle;END//DELIMITER ;call pro5(1146315,@mytitls);

从中我们看到了if else 的格式，在存储过程中等于 是使用 “=”和sql 中的是一样的。

(2)、if……else 的实例：

drop   procedure  if exists pro6;DELIMITER //create procedure pro6(out rtn int)      begin          declare LoginId INT default 0;          set rtn=1;            IF LoginId = 3 THEN              set rtn=2;          ELSEIF LoginId = 0 THEN              set rtn=3;          ELSE              set rtn=4;          END IF;       SELECT trn；    end;DELIMITER ;call pro6(@value)

5、存储过程中的循环： LOOP 和 while

(1)、LOOP 循环

drop procedure  if exists pro7；DELIMITER //create  procedure pro7()begin declare  count int default 0;myloop:LOOPSET count=count+1;if count=10 then LEAVE  myloop;end if;end LOOP myloop;select count;end;//call pro7();

LOOP 循环定义了一个循环跳出标签： myloop: LOOP ,LEAVE myloop 如果执行这句就是LOOP循环跳出myloop 这个标签块的LOOP循环代码块。而整个循环结束标签 end LOOP myloop，而且LOOP也可以嵌套。

(2)、while 循环：

drop procedure  if exists  pro8;DELIMITER //create  procedure  pro11()begin declare   i int default 0;mylab:while i <10 DOselect * from  musicmodel  limit   0,i;set i=i+1;end while mylab;end;//call pro11();

while 循环和LOOP 循环很相似；

四、jdbc 调用：

1、jdbc调用存储过程，获取查询数据：

定义存储过程：

drop  procedure  if exists  musicinfo;DELIMITER  //create  procedure  musicinfo (in num int)     begin                                                                     select * from  musicmodel limit 0   ,num;      END;                                                                  call musicinfo(10);                                            

JDBC 调用存储过程获取数据：

public void process() throws SQLException{Connection con= null;Utils  utils= new Utils();con=utils.getCon();String sql="{call musicinfo(?)}";         // 注意这里的格式，这里添加了一个{}括号        CallableStatement  callStatement=con.prepareCall(sql);callStatement.setInt(1, 10);   //设置参数，这个和preparestatement 是一样的。ResultSet  res= callStatement.executeQuery(); // 调用存数过程while(res.next()){System.out.println(res.getInt(1)+" "+res.getString(3));}

JDBC 删除存储过程：

public void process() throws SQLException{Connection con= null;Utils  utils= new Utils();con=utils.getCon();String sql="drop procedure if exists musicinfo";CallableStatement  callStatement=con.prepareCall(sql);        int end= callStatement.executeUpdate(); // 删除存储过程System.out.println(end);}

多个查询结果的存储过程：

drop  procedure  morenDELIMITER //create procedure  moren()BEGINselect  id ,title from musicmodel  order by  id  asc  limit  3;select  id,title from musicmodel order by id desc limit  3;end;//DELIMITER ;

jdbc获取多个查询结果：

public void process1() throws SQLException{Connection con= null;Utils  utils= new Utils();con=utils.getCon();String sql="{call moren()}";CallableStatement  callStatement=con.prepareCall(sql);callStatement.execute();ResultSet res= callStatement.getResultSet();while (res.next()){int id= res.getInt("id");String title= res.getString("title");System.out.println("id="+id +" title:"+title);}//释放资源res.close();if(callStatement.getMoreResults()){ResultSet  res2=callStatement.getResultSet();while (res2.next()){int id= res2.getInt("id");String title=res2.getString("title");System.out.println("id:"+id +" title:"+title);}                       res2.close();  //释放资源                    }              // 释放资源              con.close();   }

通过这样我们可以获取同一个存储过程中有多个sql的语句，callStatement.execute();发送请求执行了存储过程，而ResultSet  res= callStatement.getResultSet() 来获取ResultSet 对象，之后通过callStatement.getMoreResults() 方法指针向下移动，并返回结果，如果有则返回true 否则返回false 在通过callStatement.getResultSet() 方法来获取下一个查询结果集。

总结：

1、三种参数类型：in，out ，inout 三种类型，其中的在调用out 或inout类型的参数的存储过程的时候call 中的参数要加@ ，call  proName(@out) ,如果是inout 则要使用set @a =10, call  proName(@a);

2、其中的begin……end 可以嵌套

3、set 是设置变量的值，如果这个值没有使用declare 来定义则要使用 set  @a =10;的格式，如果已经定义了在 直接 set a=10;

4、使用declare 来定义变量，这个定义的时候要指定类型，同时也可以指定一个默认值： declare  name  varchar(50)  default   'www';   declare  age  int  default 0;

5、LOOP 循环，可以嵌套循环，可以在某种条件下跳出到指定的循环地址。跳出使用LEAVE;