一、游标的定义和使用
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;
}