偶也玩玩存储过程

 

概念: 存储过程是啥？ 有啥好处？

 

存储过程是由一些SQL语句和控制语句组成的被封装起来的过程，它驻留在数据库中，可以被客户应用程序调用，也可以从另一个过程或触发器调用。它的参数可以被传递和返回。与应用程序中的函数过程类似，存储过程可以通过名字来调用，而且它们同样有输入参数和输出参数。

　　根据返回值类型的不同，我们可以将存储过程分为三类：返回记录集的存储过程， 返回数值的存储过程（也可以称为标量存储过程），以及行为存储过程。顾名思义，返回记录集的存储过程的执行结果是一个记录集，典型的例子是从数据库中检索出符合某一个或几个条件的记录；返回数值的存储过程执行完以后返回一个值，例如在数据库中执行一个有返回值的函数或命令；最后，行为存储过程仅仅是用来实现数据库的某个功能，而没有返回值，例如在数据库中的更新和删除操作。

　　使用存储过程的好处

　　相对于直接使用SQL语句，在应用程序中直接调用存储过程有以下好处：

　　(1)减少网络通信量。调用一个行数不多的存储过程与直接调用SQL语句的网络通信量可能不会有很大的差别，可是如果存储过程包含上百行SQL语句，那么其性能绝对比一条一条的调用SQL语句要高得多。

　　(2)执行速度更快。有两个原因：首先，在存储过程创建的时候，数据库已经对其进行了一次解析和优化。其次，存储过程一旦执行，在内存中就会保留一份这个存储过程，这样下次再执行同样的存储过程时，可以从内存中直接调用。

　　(3)更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。

　　(4) 布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。

 

不同数据库存储过程的写法不一，但是大同小异，下面我就oracle和mysql的简单总结一下

 

存储过程语法：Oracle

 

1.基本结构 
　　create OR REPLACE PROCEDURE存储过程名字 
　　( 
　　参数1 IN NUMBER, 
　　参数2 IN NUMBER 
　　) IS 
　　变量1 INTEGER :=0; 
　　变量2 DATE; 
　　BEGIN 
　　END 存储过程名字 
　　2.select INTO STATEMENT 
　　将select查询的结果存入到变量中，可以同时将多个列存储多个变量中，必须有一条记录，否则抛出异常(如果没有记录抛出NO_DATA_FOUND) 
　　例子： 
　 BEGIN 
　　select col1,col2 into 变量1,变量2 FROM typestruct where xxx; 
　　EXCEPTION 
　　WHEN NO_DATA_FOUND THEN 
　 　　 xxxx; 
　　END; 
　　... 
　　3.IF 判断 
　　IF V_TEST=1 THEN 
　 　 BEGIN 
　　　　 do something 
　 　 END; 
　　END IF; 
　　4.while 循环 
　　WHILE V_TEST=1 LOOP 
　　BEGIN 
　XXXX 
　　END; 
　 END LOOP; 
　　5.变量赋值 
　　V_TEST := 123; 
　　6.用for in 使用cursor 
　 ... 
　 IS 
　　CURSOR cur IS select * FROM xxx; 
　 BEGIN 
　FOR cur_result in cur LOOP 
　 BEGIN 
　　 V_SUM :=cur_result.列名1+cur_result.列名2 
　 END; 
　END LOOP; 
　　END; 
　　7.带参数的cursor 
　 CURSOR C_USER(C_ID NUMBER) IS select NAME FROM USER where TYPEID=C_ID; 
　 OPEN C_USER(变量值); 
　　LOOP 
　　FETCH C_USER INTO V_NAME; 
　　EXIT FETCH C_USER%NOTFOUND; 
　　　do something 
　 END LOOP; 
　　CLOSE C_USER; 
　　8.用pl/sql developer debug 
　　连接数据库后建立一个Test WINDOW 
　　在窗口输入调用SP的代码,F9开始debug,CTRL+N单步调试 

 

存储过程语法：MySql (比较全)

 

 

一.创建存储过程

1.基本语法：

 

create procedure sp_name()
begin
………
end

2.参数传递

二.调用存储过程

1.基本语法：call sp_name()
注意：存储过程名称后面必须加括号，哪怕该存储过程没有参数传递

三.删除存储过程

1.基本语法：
drop procedure sp_name//
2.注意事项
(1)不能在一个存储过程中删除另一个存储过程，只能调用另一个存储过程

四.区块，条件，循环

1.区块定义，常用
begin
……
end;
也可以给区块起别名，如：
lable:begin
………..
end lable;
可以用leave lable;跳出区块，执行区块以后的代码
2.条件语句

 

if 条件 then
statement
else
statement
end if;

3.循环语句
(1).while循环

[label:] WHILE expression DO

statements

END WHILE [label] ;

 

(2).loop循环

 

[label:] LOOP

statements

END LOOP [label];

 

(3).repeat until循环

 

[label:] REPEAT

statements

UNTIL expression

END REPEAT [label] ;

 

五.其他常用命令

1.show procedure status
显示数据库中所有存储的存储过程基本信息，包括所属数据库，存储过程名称，创建时间等
2.show create procedure sp_name
显示某一个存储过程的详细信息

 

 

mysql存储过程中要用到的运算符

mysql存储过程学习总结－操作符

算术运算符

+     加   SET var1=2+2;       4
-     减   SET var2=3-2;       1
*     乘   SET var3=3*2;       6
/     除   SET var4=10/3;      3.3333
DIV   整除 SET var5=10 DIV 3;  3
%     取模 SET var6=10%3 ;     1

比较运算符

>            大于 1>2 False
<            小于 2<1 False
<=           小于等于 2<=2 True
>=           大于等于 3>=2 True
BETWEEN      在两值之间 5 BETWEEN 1 AND 10 True
NOT BETWEEN  不在两值之间 5 NOT BETWEEN 1 AND 10 False
IN           在集合中 5 IN (1,2,3,4) False
NOT IN       不在集合中 5 NOT IN (1,2,3,4) True
=            等于 2=3 False
<>, !=       不等于 2<>3 False
<=>          严格比较两个NULL值是否相等 NULL<=>NULL True
LIKE         简单模式匹配 "Guy Harrison" LIKE "Guy%" True
REGEXP       正则式匹配 "Guy Harrison" REGEXP "[Gg]reg" False
IS NULL      为空 0 IS NULL False
IS NOT NULL  不为空 0 IS NOT NULL True

逻辑运算符

与(AND)

 

 

 

AND

TRUE

FALSE

NULL

TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	NULL
NULL	NULL	NULL	NULL

 

或(OR)

 

 

 

OR

TRUE

FALSE

NULL

TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

 

异或(XOR)

 

 

 

XOR

TRUE

FALSE

NULL

TRUE	FALSE	TRUE	NULL
FALSE	TRUE	FALSE	NULL
NULL	NULL	NULL	NULL

 

位运算符

|   位或
&   位与
<<  左移位
>>  右移位
~   位非(单目运算，按位取反)

 

mysq存储过程中常用的函数，字符串类型操作，数学类，日期时间类。

mysql存储过程基本函数

一.字符串类 

CHARSET(str) //返回字串字符集
CONCAT (string2  [,... ]) //连接字串
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0
LCASE (string2 ) //转换成小写
LEFT (string2 ,length ) //从string2中的左边起取length个字符
LENGTH (string ) //string长度
LOAD_FILE (file_name ) //从文件读取内容
LOCATE (substring , string  [,start_position ] ) 同INSTR,但可指定开始位置
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length
LTRIM (string2 ) //去除前端空格
REPEAT (string2 ,count ) //重复count次
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length
RTRIM (string2 ) //去除后端空格
STRCMP (string1 ,string2 ) //逐字符比较两字串大小,
SUBSTRING (str , position  [,length ]) //从str的position开始,取length个字符,
注：mysql中处理字符串时，默认第一个字符下标为1，即参数position必须大于等于1

 

mysql> select substring(’abcd’,0,2);
+———————–+
| substring(’abcd’,0,2) |
+———————–+
|                       |
+———————–+
1 row in set (0.00 sec)

 

mysql> select substring(’abcd’,1,2);
+———————–+
| substring(’abcd’,1,2) |
+———————–+
| ab                    |
+———————–+
1 row in set (0.02 sec)

TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符
UCASE (string2 ) //转换成大写
RIGHT(string2,length) //取string2最后length个字符
SPACE(count) //生成count个空格 

二.数学类

ABS (number2 ) //绝对值
BIN (decimal_number ) //十进制转二进制
CEILING (number2 ) //向上取整
CONV(number2,from_base,to_base) //进制转换
FLOOR (number2 ) //向下取整
FORMAT (number,decimal_places ) //保留小数位数
HEX (DecimalNumber ) //转十六进制
注：HEX()中可传入字符串，则返回其ASC-11码，如HEX(’DEF’)返回4142143
也可以传入十进制整数，返回其十六进制编码，如HEX(25)返回19
LEAST (number , number2  [,..]) //求最小值
MOD (numerator ,denominator ) //求余
POWER (number ,power ) //求指数
RAND([seed]) //随机数
ROUND (number  [,decimals ]) //四舍五入,decimals为小数位数]

 

注：返回类型并非均为整数，如：
(1)默认变为整形值
mysql> select round(1.23);
+————-+
| round(1.23) |
+————-+
|           1 |
+————-+
1 row in set (0.00 sec)

 

mysql> select round(1.56);
+————-+
| round(1.56) |
+————-+
|           2 |
+————-+
1 row in set (0.00 sec)

(2)可以设定小数位数，返回浮点型数据
mysql> select round(1.567,2);
+—————-+
| round(1.567,2) |
+—————-+
|           1.57 |
+—————-+
1 row in set (0.00 sec)

SIGN (number2 ) //返回符号,正负或0
SQRT(number2) //开平方

 
三.日期时间类
 

ADDTIME (date2 ,time_interval ) //将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区
CURRENT_DATE (  ) //当前日期
CURRENT_TIME (  ) //当前时间
CURRENT_TIMESTAMP (  ) //当前时间戳
DATE (datetime ) //返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间
DATEDIFF (date1 ,date2 ) //两个日期差
DAY (date ) //返回日期的天
DAYNAME (date ) //英文星期
DAYOFWEEK (date ) //星期(1-7) ,1为星期天
DAYOFYEAR (date ) //一年中的第几天
EXTRACT (interval_name  FROM date ) //从date中提取日期的指定部分
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second ) //生成时间串
MONTHNAME (date ) //英文月份名
NOW (  ) //当前时间
SEC_TO_TIME (seconds ) //秒数转成时间
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差
TIME_TO_SEC (time ) //时间转秒数]
WEEK (date_time [,start_of_week ]) //第几周
YEAR (datetime ) //年份
DAYOFMONTH(datetime) //月的第几天
HOUR(datetime) //小时
LAST_DAY(date) //date的月的最后日期
MICROSECOND(datetime) //微秒
MONTH(datetime) //月
MINUTE(datetime) //分

 

附:可用在INTERVAL中的类型
DAY ,DAY_HOUR ,DAY_MINUTE ,DAY_SECOND ,HOUR ,HOUR_MINUTE ,HOUR

 

Java调用 存储过程的例子

 

例子：在mysql5.0 的数据库上建立一个 person表，然后建立存储过程完成a.数据的插入b.数据的查找.c结果集查找

+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(10)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(20) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.17 sec)

 

步骤：首先在数据库里建立3个存储，完成a，b，c 响应的功能。

1.cmd ->l连接到mysql数据库

2.将控制台命令的结束符号 改成 // ，本来是" ;" ,由于存储过程中可能要使用到'';",因此需要修改，需要在控制台 执行delimiter //

3.存储过程 ：3个存储过程完成a,b,c三个功能

 

 

    
    create procedure sp2(out p int)
    begin
      select max(id) into p from person;
    end
    //
    
    CREATE PROCEDURE sp1(in p1 int(10),in p2 varchar(20))
begin
      declare v1 int;
      declare v2 varchar(20);
      set v1 = p1;
      set v2 = p2;
      insert into person(id,name) values(v1,v2);
    end //
    
     create procedure sp6()
    begin
       select * from test;
    end//

 

 

 4.在java中调用 主要使用 CallableStatement  这个对象 完成java调用存储过程，它封装了一些基本的调用存储过称过的方法

 

package cn.com.xinli.ibatis.dao.impl;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

public class TestProcedure {

	/**
	 * @param args
	 */
	public static void main(String[] args)
	{
		TestProcedure tp=new TestProcedure();
		//tp.callIn(35,"胡晓亮");
		//tp.callOut();
		tp.callResult();
		
	}

	public  Connection getConnection()
	{
		  Connection conn = null;
		  //PreparedStatement preparedstatement = null;
		  try 
		  {
		   Class.forName("org.gjt.mm.mysql.Driver").newInstance(); 
		   String dbname = "test";
		   String url ="jdbc:mysql://localhost/"+dbname+"?user=root&password=12345678&useUnicode=true&characterEncoding=UTF-8" ;
		   conn= DriverManager.getConnection(url);
		  } 
		  catch (Exception e) 
		  {
		   e.printStackTrace();
		  } 
		  return conn;
		 }
	
	/*
	 * 插入一条记录
	 * */
	
	 public  void callIn(int id,String name){
		  //获取连接
		  Connection conn = getConnection();
		  CallableStatement cs = null;
		  try {
		   //可以直接传入参数
		   //cs = conn.prepareCall("{call sp1(1)}");
		   
		   //也可以用问号代替
		   cs = conn.prepareCall("{call sp1(?,?)}");
		   //设置第一个输入参数的值为110
		   cs.setInt(1, id);
		 
		   cs.setString(2, name);
		   cs.execute();
		  } catch (Exception e) {
		   e.printStackTrace();
		  } finally {
		   try {
		    if(cs != null){
		     cs.close();
		    }
		    if(conn != null){
		     conn.close();
		    }
		   } catch (Exception ex) {
		    ex.printStackTrace();
		   }
		  }
		  
		 }
	 /**
	  * 找到最大的记录
	  */
	 public  void callOut() {
		  Connection conn =getConnection();
		  CallableStatement cs = null;
		  try {
		   cs = conn.prepareCall("{call sp2(?)}");
		   //第一个参数的类型为Int
		   cs.registerOutParameter(1, Types.INTEGER);
		   cs.execute();
		   
		   //得到第一个值
		   int i = cs.getInt(1);
		   System.out.println(i);
		  } catch (Exception e) {
		   e.printStackTrace();
		  } finally {
		   try {
		    if(cs != null){
		     cs.close();
		    }
		    if(conn != null){
		     conn.close();
		    }
		   } catch (Exception ex) {
		    ex.printStackTrace();
		   }
		  }
		 }
	 
	/**
	 * 返回结果集
	 */ 
	 public  void callResult(){
		  //获取连接
		  Connection conn = getConnection();
		  CallableStatement cs = null;
		  try {
		   //可以直接传入参数
		   //cs = conn.prepareCall("{call sp1(1)}");
		   
		   //也可以用问号代替
		   cs = conn.prepareCall("{call sp6()}");
		   //设置第一个输入参数的值为110
		 
		 java.sql.ResultSet  rs= cs.executeQuery();
		 
		 while(rs.next())
		 {
			 System.out.println("ID："+rs.getInt(1));
			 System.out.println("姓名："+rs.getString(2));
		 }
		  } catch (Exception e) {
		   e.printStackTrace();
		  } finally {
		   try {
		    if(cs != null){
		     cs.close();
		    }
		    if(conn != null){
		     conn.close();
		    }
		   } catch (Exception ex) {
		    ex.printStackTrace();
		   }
		  }
		  
		 }
	
}

 

5.测试结果：

在mysql5.0下执行通过