DECLARE aaa,bbb,ccc,dddd varchar(30);
定第一个 cur1 select A,B from tableA OPEN cur1; REPEAT FETCH cur1 INTO aaa,bbb; IF aaa ... THEN -- 这时再定别一个,select 语句的条件是不固定的,即条件是 cur1里的变量
DECLARE cur2 select C,D from tableB where C =
再根据内容再内部一个循环
-- END IF; UNTIL done END REPEAT; close cur1;
定第一个 cur1 select A,B from tableA OPEN cur1; REPEAT FETCH cur1 INTO aaa,bbb; IF aaa ... THEN -- 这时再定别一个,select 语句的条件是不固定的,即条件是 cur1里的变量
DECLARE cur2 select C,D from tableB where C =
再根据内容再内部一个循环
-- END IF; UNTIL done END REPEAT; close cur1;
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
select va;
OPEN cur2;
REPEAT
FETCH cur2 INTO vc,vd;
set doneB=done;
select va,vc,vd;
UNTIL doneB END REPEAT;
CLOSE cur2;
END IF; UNTIL doneA END REPEAT;
CLOSE cur1;
END
OPEN cur2;这两句就能达到不同VA的查询???
select va;OPEN cur2;
select va; 不需要,只是为了调试看得清楚而已。 你好象不太熟悉编程。
自已加个计数器, 每次循环的时候set i=i+1;
改下成面那样?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,reccount INT;
DECLARE cur1 CURSOR FOR SELECT a,b FROM tableA;
DECLARE cur2 CURSOR FOR SELECT c,d,count(1) 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; OPEN cur2;
FETCH cur2 INTO vc,vd,reccount; WHILE reccount > 0 DO
set doneB=done;
select va,vc,vd;
set reccount = reccount - 1;
UNTIL doneB END REPEAT;
END WHILE;
CLOSE cur2;
UNTIL doneA END REPEAT;
CLOSE cur1;
END