数据库游标以及在java中的应用

一、游标的定义和使用

1．声明游标

  DECLARE  游标名 CURSOR  FOR  SELECT语句;

2．打开游标

OPEN   游标名;

3．提取数据

 FETCH  游标名  INTO   变量名1[，变量名2……];

4．关闭游标

         CLOSE  游标名;

【例3-43】创建存储过程emp_p，用游标提取emp表中1006雇员的姓名和职务。

DELIMITER @@
DROP PROCEDURE IF EXISTS emp_p@@
CREATE PROCEDURE emp_p()#创建无参的存储过程
 BEGIN
  DECLARE v_ename VARCHAR(255); #长度最好与表中定义的保持一致
  DECLARE v_job   VARCHAR(255); #声明变量  
  DECLARE emp_cursor CURSOR     
    FOR  SELECT  ename,Job  FROM emp   WHERE  empno=1006;
  OPEN emp_cursor;#打开游标          
  FETCH emp_cursor INTO v_ename,v_job;#从游标获取信息保存到变量 
  CLOSE emp_cursor; #关闭游标              
  SELECT v_ename,v_job;#查看从游标读取的数据
 END@@ 
DELIMITER ;
CALL emp_p();#调用游标

【例3-44】创建存储过程emp_p1，用游标显示工资最高的前3名雇员的姓名和工资。

DELIMITER @@
CREATE PROCEDURE emp_p1()#创建无参数存储过程
 BEGIN
  DECLARE v_ename VARCHAR(14);#声明变量
  DECLARE v_sal   DECIMAL(7,2);
  DECLARE i INT;
  DECLARE mycursor CURSOR 
   FOR  SELECT  ename,sal  FROM emp  ORDER BY sal DESC
		 	LIMIT  3; 
  SET i=1;#声明变量
  CREATE TABLE result(#创建一张表，用来存储员工姓名和工资
   ename VARCHAR(14),
   sal   DECIMAL(7,2)
  ); 
OPEN mycursor;#打开游标
  WHILE i<=3 DO
   FETCH mycursor INTO v_ename,v_sal;#从游标提取出一条数据，存储在相应变量内
   INSERT INTO result VALUES(v_ename,v_sal);#把从游标内取出的数据插入创建的表result，该语句执行一次插入一条数据
   SET i=i+1;
  END WHILE;
  CLOSE mycursor;#关闭游标
  SELECT * FROM result;#查询新表
 END@@
 
DELIMITER ;
CALL emp_p1();	

使用示例： 返回用户的最近一次充值记录（返回最后一条）

DELIMITER @@
DROP PROCEDURE IF EXISTS findcharge_log@@
CREATE PROCEDURE findcharge_log(
IN cusid INT
)#创建有参的存储过程
 BEGIN
  DECLARE v_who int(10); #长度最好与表中定义的保持一致
  DECLARE v_money double(20,2); #声明变量  
	DECLARE v_date datetime; #声明变量
	-- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;
	-- 游标
  DECLARE charge_cursor CURSOR FOR SELECT who,money,oper_date FROM charge_log where who=cusid AND IFNULL(money,0);
	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  OPEN charge_cursor;#打开游标
	-- 遍历
  read_loop: LOOP 
	-- 删除消费记录或者更改其他信息的记录
	DELETE FROM charge_log WHERE money=0 or money<0; 
  FETCH NEXT FROM charge_cursor INTO v_who,v_money,v_date;#从游标获取信息保存到变量 
             IF done THEN
                LEAVE read_loop;
             END IF; 
	END LOOP;
	CLOSE charge_cursor; #关闭游标
	SELECT v_who,v_money,v_date;#查看从游标读取的数据
 END@@ 
DELIMITER ;

在java中调用：

public Charge findChargeByid(int id) {
		Connection conn = null; // 数据库连接对象
		CallableStatement clbStmt = null; // CallableStatement对象
		ResultSet rs = null; // 结果集对象
		Charge charge=new Charge();
		try {
			// 获取数据库连接
			conn = JdbcUtils.getConnection();
			// 创建CallableStatement对象,调用存储函数
			clbStmt = conn.prepareCall("{CALL findcharge_log(?)}");
			// 获取顾客id参数
			clbStmt.setInt(1, id); 
			// 执行调用存储过程，并获取结果集
			rs = clbStmt.executeQuery();
	       if(rs.next()) {
	    	   //读取游标返回的结果
	        	charge.setId(rs.getInt("v_who"));
	        	charge.setMoney(rs.getDouble("v_money"));
	        	charge.setCdate(rs.getString("v_date"));
	        }
		} catch (SQLException sqle) {
			sqle.printStackTrace();
		} finally {
			// 关闭数据库操作对象
			try {
				rs.close();
				JdbcUtils.closeDB(conn, clbStmt);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return charge;
	}