mysql存储过程实例

最新推荐文章于 2024-10-12 13:59:20 发布

playboyb

最新推荐文章于 2024-10-12 13:59:20 发布

阅读量90

点赞数

文章标签： MySQL JDBC 数据结构 iBATIS Oracle

先放入mysql存储过程，便于大家了解一下存储过程的整体代码结构：

CREATE PROCEDURE `get_branch_path`(IN company CHAR(32))
BEGIN
	DECLARE _branchid CHAR(32) ;
	DECLARE _branchpath LONGTEXT;
	DECLARE _currentid CHAR(32);
	DECLARE fetchSeqOK boolean;
	
	DECLARE cur1 CURSOR FOR SELECT branchid FROM t_branch WHERE companyid=company;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOK=TRUE;
	SET fetchSeqOK=FALSE;
	DROP TEMPORARY TABLE IF EXISTS temp_table;
	CREATE TEMPORARY TABLE IF NOT EXISTS temp_table (
		branchid CHAR(32),
		branchpath LONGTEXT
	);
	OPEN cur1;
	FETCH cur1 INTO _currentid;
	fetchSeqLoop:LOOP
	IF fetchSeqOK THEN
		LEAVE fetchSeqLoop;
	ELSE
		SET _branchid = _currentid;
		SET _branchpath = NULL;
		CALL generate_branch_path(_currentid, _branchpath);
		
		INSERT INTO temp_table VALUES(_branchid, _branchpath);
		FETCH cur1 INTO _currentid;
	END IF;
	END LOOP;
	CLOSE cur1;
	SELECT branchid,branchpath FROM temp_table;
	DROP TABLE temp_table;
END

CREATE PROCEDURE `generate_branch_path`(INOUT `_subid` varchar(255),INOUT `_subpath` longtext)
BEGIN
	DECLARE _path LONGTEXT;
	DECLARE _parentid CHAR(32);
	DECLARE fetchSeqOK boolean;
	DECLARE cur2 CURSOR FOR SELECT `name`,parentid FROM t_branch WHERE branchid=_subid;
	SET @@max_sp_recursion_depth = 20;
	OPEN cur2;
	FETCH cur2 INTO _path,_parentid;
	IF( LEFT(_parentid,1) <> 0 ) THEN
		SET _subid = _parentid;
		IF (_subpath IS NULL) THEN
			SET _subpath = _path;
		ELSE
			SET _subpath = CONCAT(_path,'/',_subpath);
		END IF;
		CALL generate_branch_path(_subid,_subpath);
	ELSE
		IF (_subpath IS NULL) THEN
			SET _subpath = _path;
		ELSE
			SET _subpath = CONCAT(_path,'/',_subpath);
		END IF;
	END IF;
END

上述两个存储过程所需要的数据表及测试数据：

CREATE TABLE `t_branch` (
  `branchid` char(32) NOT NULL,
  `companyid` char(32) NOT NULL,
  `name` varchar(50) NOT NULL,
  `parentid` char(32) NOT NULL,
  `sortid` int(11) NOT NULL,
  `remark` varchar(200) DEFAULT NULL,
  `isshare` int(11) DEFAULT NULL,
  `lastupdatetime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `createtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`branchid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t_branch` VALUES ('402880ff2d6f871a012d6f871cf30000','402880ff2d5f53cd012d5f53d43d0000','销售部门','0','2','就是湖北销售部门！','0','2011-01-11 18:13:35','2011-01-10 18:46:32'), 
('402880ff2d6f871a012d6f8788210001','402880ff2d5f53cd012d5f53d43d0000','hbdev','402880ff2d744a06012d74531f8e0002','1','就是湖北研发','1','2011-01-11 17:16:42','2011-01-10 18:47:00'), 
('402880ff2d6f871a012d6f8816f30002','402880ff2d5f53cd012d5f53d43d0000','abc team','402880ff2d6f871a012d6f8788210001','2','就是 abc team！','1','2011-01-11 18:21:34','2011-01-10 18:47:36'), 
('402880ff2d744a06012d745231660000','402880ff2d5f53cd012d5f53d43d0000','人力资源部','0','3','就是湖北人力资源部！','0','2011-01-11 18:18:11','2011-01-11 17:06:47'),
 ('402880ff2d744a06012d7452930e0001','402880ff2d5f53cd012d5f53d43d0000','市场部','0','4','就是湖北市场部！','0','2011-01-11 18:18:11','2011-01-11 17:07:12'),
 ('402880ff2d744a06012d74531f8e0002','402880ff2d5f53cd012d5f53d43d0000','PTC','0','1','就是EFG！','0','2011-01-11 17:17:23','2011-01-11 17:07:48'), 
('402880ff2d749155012d7492e8e70000','402880ff2d5f53cd012d5f53d43d0000','HDK team','402880ff2d6f871a012d6f8788210001','1','就是HDK team！','0','2011-01-11 18:17:28','2011-01-11 18:17:28'), 
('ff8080812e3792bb012e50492fad0018','402880ff2d5f53cd012d5f53d43d0000','新部门','402880ff2d6f871a012d6f871cf30000','1','','0','2011-02-23 10:11:37','2011-02-23 10:11:37');

1.1存储过程代码结构：

CREATE PROCEDURE 存储过程名(参数列表，有三种类型：IN,OUT,INOUT) 
BEGIN   
   1.变量和条件声明  
   2. Cursor声明  
   3. Handler声明  
   4. 程序代码 
END

注意：

由括号包围的参数列必须总数存在的。如果没有参数，也要使用一个空参数列()。每个参数默认都是一个IN参数，可在参数名之间加入OUT,INOUT等关键字指定为其他类型。

变量和条件声明不需放在Cursor声明等代码块之前，否则该存储过程编译不能通过。

1.2变量声明

DECLARE _branchid CHAR(32) ;
DECLARE 关键字 
_branchid 变量名
CHAR(32) 变量类型

1.3指针和Handler声明

定义游标
cur1 CURSOR FOR SELECT branchid FROM t_branch WHERE companyid=company;
使用游标
open cur1；
fetch数据
FETCH cur1 INTO _currentid;
关闭游标
close cur1; 

用到游标cur1，都会离不开循环语句。
一般使用Loop和while来进行循环语句的编写。
这里使用Loop为例
fetchSeqLoop:LOOP
fetch cur1 into _currentid;
end Loop;
现在是死循环，还没有退出的条件，那么在这里和oracle有区别，OraclePL/SQL的指针有个隐性变量%notfound，
Mysql是通过一个Error handler的声明来进行判断的，
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetchSeqOK=TRUE;
在Mysql里当游标遍历溢出时，会出现一个预定义的NOT FOUND的Error，我们处理这个Error并定义一个continue的handler就可以了，
关于Mysql Error handler可以查询Mysql手册。定义一个flag，在NOT FOUND，标示Flag,在Loop里以这个flag为结束循环的判断就可以了。
具体代码可以参考上面的存储过程：get_branch_path();
注意：在循环语句中，记得使用FETCH cur1 into _currentid来进行指针的数据移动，否则指针会一直指向你初次赋值的地方，而不指向下一个值。

1.4程序代码

根据上述定义的变量，临时表，指针等运用循环语句，IF/ELSE语句，函数等内容完成一定的业务逻辑。

IF语句的结构：
   IF 逻辑表达式 THEN
         程序语句;
   ELSE
         程序语句;
   END IF;
WHILE语句的结构：
   WHILE 逻辑表达式 DO
         程序语句；
   END WHILE;
LOOP语句的结构：
  LOOP
       程序语句；
  END LOOP;


可以给代码块加lebel，这样END匹配比较直观，还可以用LEAVE语句来终结代码块:
fetchSeqLoop:LOOP
	IF fetchSeqOK THEN
		LEAVE fetchSeqLoop;
	ELSE
           程序语句；
        END IF;
END LOOP;

2.1存储过程调用

CALL generate_branch_path(_currentid, _branchpath);

存储过程中的参数有三种IN,OUT,INOUT.具体用法可以看看下面的这篇文章，就能够明白：

http://www.blogjava.net/nonels/archive/2009/04/22/233324.html

2.2存储过程的结果集返回

上述存储过程中，创建了一个临时表temp_table，用于存储调用方(持久层中间件：hibenate,ibatis,JDBC)所需要的结果集。如果不考虑临时表的回收问题，在调用方可以在调用存储过程之后，再直接用sql语句查询该临时表获取结果集。但考虑到临时表的回收问题，需要在存储过程结束时，删除该临时表，并返回该结果集。使用如下代码即可达到目的。

SELECT branchid,branchpath FROM temp_table;
	DROP TABLE temp_table;
在有了上面的SELECT语句之后，在DAO层调用存储过程之后，通过代码就可以获取结果集了。
使用JDBC等接口代码完成结果集的返回。
String url = "jdbc:mysql://127.0.0.1:3306/test";   
try {   
      DriverManager.registerDriver(new com.mysql.jdbc.Driver());      
      Connection conn = DriverManager.getConnection(url, "root","");   
      CallableStatement stmt = conn.prepareCall("{call xx()}");   
      stmt.execute();   
      ResultSet rs = (ResultSet)stmt.getResultSet();   
      while(rs.next()){   
              System.out.print (rs.getString(1));   
              System.out.print (" , ");   
              System.out.println (rs.getString(2));   
      }   
}  catch (SQLException e) {   
    e.printStackTrace();   
}

行了，关于mysql总结的经验主要就是这些了，上述代码很多借鉴了其他的一些文章，有雷同之处还望大家见谅。关于上述贴出的存储过程，数据表等是可以编译通过，并运行的。大家想学习的话，可以拷贝下来测试一下。