如何在一个过程中处理多个游标?

piok6c0g  于 2021-06-20  发布在  Mysql
关注(0)|答案(1)|浏览(417)

我想在程序的开头使用两个游标
这些是table
人。

________________
| code | name    |
|   1  | Carlos  |
|   2  | Charles |
|   3  | Mary    |
|   4  | Jhon    |
|   5  | Jen     |
-----------------

班级。

________________
    | code | desc       |
    |   1  | Java       |
    |   2  | PHP        |
    |   3  | Perl       |
    |   4  | Python     |
    |   5  | Javascript |
    -------------------
DECLARE R CURSOR FOR SELECT C.CODE FROM people;  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
DECLARE classes CURSOR FOR    SELECT DISTINCT a.CODE FROM classes;  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;

给两个循环,一个在另一个里面。。。。。。。。
但我要找回这个信息:
处理程序声明之后的游标声明。。。。。。。。。。。。。

DECLARE v_cdclass INT;
DELCARE v_codperson INT;
DROP TABLE IF EXISTS temp1;
CREATE TEMPORARY TABLE temp1 
(
  id  INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,  
  cdfunc         INT(11),
  class           INT
); 
OPEN R; 
    loop0: LOOP
         FETCH R INTO v_codperson; 
            IF done2 THEN
               LEAVE loop0;
            END IF;

            BLOCO2: BEGIN

              OPEN classes;      
                 loop1: LOOP    
                    FETCH classes INTO v_cdclass ;  
                    IF done1 THEN
                        LEAVE loop1;
                    END IF;
                        INSERT INTO temp1 
                        ( cdfunc,  class )
                        VALUES 
                        ( v_codperson , v_cdclass);  
                  END LOOP loop1;   
                CLOSE classes;        

            END BLOCO2;
    END LOOP ;      
CLOSE R; 
SELECT * FROM temp1;
5us2dqdw

5us2dqdw1#

尝试:

...
DECLARE R CURSOR FOR SELECT C.CODE FROM TABLE1;  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2 = TRUE;
...
   BLOCO2: BEGIN
      DECLARE classes CURSOR FOR SELECT DISTINCT a.CODE FROM TABLE2;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
...

相关问题