mysql 存储过程

时间:2016-06-12 21:58 来源:linux.it.net.cn 作者:IT

一、存储过程介绍

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

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

二、使用存储过程的优点

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

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

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

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

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

三、MySQL 存储实例

1、创建存储过程语句：

[sql] view plain copy
				 
 print?

			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,实例如下：

[sql] view plain copy
				 
 print?

			drop  procedure  if exists  pro2;  

			DELIMITER //  

			create procedure  pro2 (in num int)  

			begin   

			select * from musicmodel  limit num;  

			end;  

			//  

			DELIMITER ;

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

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

[sql] view plain copy
				 
 print?

			drop  procedure if exists pro3;  

			DELIMITER  

			create  procedure  procedure3(out titles VARCHAR(50))  

			BEGIN  

			DECLARE 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 输入输出参数，实例如下：

[sql] view plain copy
				 
 print?

			drop  procedure  if exists   pro4  

			DELIMITER  //  

			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 的实例：

[sql] view plain copy
				 
 print?

			drop procedure  if exists  pro5  

			DELIMITER //  

			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 的实例：

[sql] view plain copy
				 
 print?

			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 循环

[sql] view plain copy
				 
 print?

			drop procedure  if exists pro7；  

			DELIMITER //  

			create  procedure pro7()  

			begin   

			declare  count int default 0;  

			myloop:LOOP  

			SET 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 循环：

[sql] view plain copy
				 
 print?

			drop procedure  if exists  pro8;  

			DELIMITER //  

			create  procedure  pro11()  

			begin   

			declare   i int default 0;  

			mylab:while i <10 DO  

			select * from  musicmodel  limit   0,i;  

			set i=i+1;  

			end while mylab;  

			end;  

			//  

			call pro11();

while 循环和LOOP 循环很相似；

四、jdbc 调用：

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

定义存储过程：

[sql] view plain copy
				 
 print?

			drop  procedure  if exists  musicinfo;  

			DELIMITER  //  

			create  procedure  musicinfo (in num int)       

			begin                                                                       

			select * from  musicmodel limit 0   ,num;        

			END;                                                                    

			call musicinfo(10);

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

[sql] view plain copy
				 
 print?

			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 删除存储过程：

[sql] view plain copy
				 
 print?

			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);  

			    }

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

[sql] view plain copy
				 
 print?

			drop  procedure  moren  

			DELIMITER //  

			create procedure  moren()  

			BEGIN  

			select  id ,title from musicmodel  order by  id  asc  limit  3;  

			select  id,title from musicmodel order by id desc limit  3;  

			end;  

			//  

			DELIMITER ;

jdbc获取多个查询结果：

[sql] view plain copy
				 
 print?

			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;

(责任编辑：IT)