drop procedure if exists da003;delimiter //CREATE PROCEDURE da003()
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
declare done1,done2 int default 0;
DECLARE va,vb char(5) DEFAULT '99999';
DECLARE bdt datetime;
declare id1,id2 int; declare cur1 cursor for select empno from employee;
declare continue handler for not found set done1 = 1; open cur1; repeat
fetch cur1 into va;
if not done1 then
begin
declare cur2 cursor for select empno,startdatetime from buy_record where empno = va order by empno,startdatetime;
declare continue handler for not found set done2 = 1;
open cur2;
repeat
fetch cur2 into vb,bdt;
if vb <> '' then
select vb,bdt;
end if;
until done2 end repeat;
close cur2;
set vb = '';
set done2=0;
end;
end if;
until done1 end repeat;
close cur1; commit;
END;
//delimiter ;
如工号为10001的员工,本有两条
10001 时间1
10001 时间2
变成
10001 时间1
10001 时间2
10001 时间2如工号为10002的员工,本有三条
10002 时间1
10002 时间2
10002 时间3
变成
10002 时间1
10002 时间2
10002 时间3
10002 时间3就是说尾数多了一条,跟最后一条是一样的,要怎样才正常
mysql> drop procedure if exists sp_da003//
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> CREATE PROCEDURE sp_da003()
-> BEGIN
-> DECLARE done INT DEFAULT 0;
-> DECLARE doneA INT DEFAULT 0;
-> DECLARE doneB INT DEFAULT 0;
-> DECLARE va,vb,vc,vd INT;
->
-> DECLARE cur1 CURSOR FOR SELECT a,b FROM tableA;
-> DECLARE cur2 CURSOR FOR SELECT c,d FROM tableB where c=va;
-> DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
->
-> OPEN cur1;
->
-> REPEAT
-> FETCH cur1 INTO va, vb;
-> set doneA=done;
->
-> IF va>1 THEN
-> OPEN cur2;
-> FETCH cur2 INTO vc,vd;
-> WHILE done=0 DO
-> select va,vc,vd;
-> FETCH cur2 INTO vc,vd;
-> END WHILE;
-> CLOSE cur2;
-> END IF;
->
-> UNTIL doneA END REPEAT;
-> CLOSE cur1;
-> END//
Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;
mysql> select * from tableA;
+------+------+
| a | b |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+------+------+
3 rows in set (0.00 sec)mysql> select * from tableb;
+------+------+
| c | d |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 2 | 3 |
+------+------+
3 rows in set (0.00 sec)mysql> call sp_da003();
+------+------+------+
| va | vc | vd |
+------+------+------+
| 2 | 2 | 2 |
+------+------+------+
1 row in set (0.00 sec)+------+------+------+
| va | vc | vd |
+------+------+------+
| 2 | 2 | 3 |
+------+------+------+
1 row in set (0.00 sec)Query OK, 0 rows affected (0.00 sec)mysql>
tableA里有多个员工,而这个存储过程只显示了一个员工在tableB里的记录,其它员工没有显示
10001 时间1
10001 时间2
10002 时间1
10002 时间2
10002 时间3主题里的存储过程却变成
10001 时间1
10001 时间2
10001 时间2
10002 时间1
10002 时间2
10002 时间3
10002 时间3而ACMAIN_CHM兄的只显示了
10001 时间1
10001 时间2
没有10002的
+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 07:38:00 |
+-------+---------------------+
1 row in set (0.01 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 08:18:00 |
+-------+---------------------+
1 row in set (0.02 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 12:18:00 |
+-------+---------------------+
1 row in set (0.02 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 12:45:00 |
+-------+---------------------+
1 row in set (0.02 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 17:23:00 |
+-------+---------------------+
1 row in set (0.02 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 17:48:00 |
+-------+---------------------+
1 row in set (0.03 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 18:34:00 |
+-------+---------------------+
1 row in set (0.04 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20102 | 2009-08-03 20:32:00 |
+-------+---------------------+
1 row in set (0.04 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-01 00:00:00 |
+-------+---------------------+
1 row in set (0.04 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-01 07:45:00 |
+-------+---------------------+
1 row in set (0.05 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-01 23:45:00 |
+-------+---------------------+
1 row in set (0.05 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-02 08:15:00 |
+-------+---------------------+
1 row in set (0.05 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-02 09:29:00 |
+-------+---------------------+
1 row in set (0.05 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-03 08:15:00 |
+-------+---------------------+
1 row in set (0.06 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-03 15:55:00 |
+-------+---------------------+
1 row in set (0.06 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-04 07:56:00 |
+-------+---------------------+
1 row in set (0.06 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-04 16:16:00 |
+-------+---------------------+
1 row in set (0.06 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-05 15:56:00 |
+-------+---------------------+
1 row in set (0.07 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-06 00:16:00 |
+-------+---------------------+
1 row in set (0.07 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-06 23:46:00 |
+-------+---------------------+
1 row in set (0.07 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20281 | 2009-08-07 08:12:00 |
+-------+---------------------+
1 row in set (0.07 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-03 13:00:00 |
+-------+---------------------+
1 row in set (0.07 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-03 14:00:00 |
+-------+---------------------+
1 row in set (0.08 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-04 08:20:00 |
+-------+---------------------+
1 row in set (0.08 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-04 11:54:00 |
+-------+---------------------+
1 row in set (0.08 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-04 12:52:00 |
+-------+---------------------+
1 row in set (0.08 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-04 16:52:00 |
+-------+---------------------+
1 row in set (0.09 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-04 18:52:00 |
+-------+---------------------+
1 row in set (0.09 sec)+-------+---------------------+
| vb | bdt |
+-------+---------------------+
| 20393 | 2009-08-04 21:52:00 |
+-------+---------------------+
1 row in set (0.09 sec)Query OK, 0 rows affected (0.09 sec)
这是执行的,同一个员工最后一条没有重复一次了