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

Love The Way You Lie 2021-09-21 06:38 571阅读 0赞

### **一、游标的定义和使用** ###

**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;
    	}