mysql数据库存储过程（细）-------游标的应用

数据局在应用中不仅可以在java中输入语句调用，它自身也带有类似以java方法(无返回值)的东西----------存储过程

DELIMITER $$USE `pubs10`$$DROP PROCEDURE IF EXISTS `p1`$$   -- 如果存在，删掉已经存在（同名）的存储过程CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`(n INT)BEGIN     DECLARE i INT DEFAULT 1;     DECLARE sum1 INT DEFAULT 0;WHILE i<=n DO  IF i % 2!=0  THEN     SET sum1=sum1+i;   END IF ;  SET  i=i+1; END WHILE; SELECT sum1;    END$$DELIMITER ;

上面是一个mysql的存储过程，输入n，显示1到n之间偶数的和。

注意：

1.存储过程第一次调用后，要再次调用就需要第三行代码，存储过程，触发器，方法都有类似的代码，略有差异。是因为，运行一次后存储过程已经存在了，在再次运行时需要将之前的存储过程删掉。

2.数据库中存储过程的执行特点假如，在执行一个存储过程中报错，那么它会将报错前的语句全部执行。假设你在存储过程中创建了表那么报错在该条语句之后，但是运行时弹出错误，但此时表已经创建成功了。

3.select 语句类似于java中的return但又不太一样。无论一个存储过程中有几个select，哪怕是在循环中的select，运行完存储过程只会得到第一次select查询到的值，后面的select语句都失效。但是，后面的其他语句还是会照常执行。
存储过程的写法有点类似于汇编语言，但又比汇编语言灵活的多。那么存储过程有没有想java一样实现将一堆查找的对象输入到数组里逐个使用的功能呢？是如何实现数组总排序自增的功能呢。

DELIMITER $$USE `pubs10`$$DROP PROCEDURE IF EXISTS `pp1`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `pp1`(aid VARCHAR(20),ver INT)BEGIN       DECLARE cnt INT DEFAULT 0;       DECLARE dan INT DEFAULT 0;       DECLARE he INT DEFAULT 0;       DECLARE i INT DEFAULT 1;       DECLARE tcnt INT DEFAULT 0;       DECLARE tid VARCHAR(20);              DROP TABLE linshi;       IF ver=0 THEN       DELETE FROM information;       END IF;                     SET @a = 0;  -- 设置@排序，@必不可少       CREATE TABLE linshi SELECT @a:=@a+1 AS xh,title_id FROM titleauthor WHERE au_id=aid; -- 创建一张只有序号和内容的表实现数组排序功能       SELECT COUNT(1) INTO cnt FROM linshi;              WHILE i<=cnt DO   -- while遍历序号来取值       SELECT title_id INTO tid FROM linshi WHERE xh=i;  -- 让i等于序号实现取值       SELECT COUNT(1) INTO tcnt FROM titleauthor WHERE title_id=tid;       IF tcnt=1 THEN       SET dan=dan+1;       ELSE       SET he=he+1;       END IF;       SET i=i+1;       END WHILE;       INSERT INTO information VALUES (aid,cnt,dan,he);       IF ver=0 THEN       SELECT * FROM information;       END IF;    END$$DELIMITER ;

上面是输入作者id，找到作者出的书的数量，其中单著和合著的数量。

在存储过程中有专门的语法实现和上面一样的功能------------游标

DELIMITER $$USE `pubs10`$$DROP PROCEDURE IF EXISTS `pp11`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `pp11`(aid VARCHAR(20),ver INT)BEGIN       DECLARE cnt INT DEFAULT 0;       DECLARE dan INT DEFAULT 0;       DECLARE he INT DEFAULT 0;              DECLARE tcnt INT DEFAULT 0;       DECLARE tid VARCHAR(20);       DECLARE n INT DEFAULT 1; -- 声明int型标量n，默认值为1       DECLARE cur CURSOR FOR SELECT DISTINCT title_id FROM titleauthor WHERE au_id=aid; -- 创建游标，将所有查询的对象放入游标中       DECLARE CONTINUE HANDLER FOR NOT FOUND SET n =0; -- 如果找不到则n=0                     IF ver=0 THEN       DELETE FROM information;  -- 清空结果展示的表       END IF;       OPEN cur;  -- 打开游标              FETCH cur INTO tid; -- 将游标里的值放入tid中，如果没有那么n就为0了，下面的while就不执行了       SELECT COUNT(1) INTO cnt FROM (SELECT DISTINCT title_id FROM titleauthor WHERE au_id=aid) a;       WHILE n=1 DO              SELECT COUNT(1) INTO tcnt FROM titleauthor WHERE title_id=tid;  -- 在书-作者表里，找作者的个数       IF tcnt=1 THEN  -- 一个肯定是单著       SET dan=dan+1;       ELSE       SET he=he+1;  -- 大于1是合著       END IF;       FETCH cur INTO tid;  -- 结束时再刷新一下n的值，界定下次是否进入循环       END WHILE;              INSERT INTO information VALUES (aid,cnt,dan,he);  -- 提前创建一张满足条件的表，把结果存进去       IF ver=0 THEN       SELECT * FROM information;  -- 展示这张表       END IF;    END$$DELIMITER ;

上面要拿到结果要事先船检一张满足业务的临时表，其实有更好的方法，在存储过程里：建表--输出--删表

DELIMITER $$USE `pubs`$$DROP PROCEDURE IF EXISTS `test07263`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `test07263`(xxx VARCHAR(20))BEGIN       DECLARE n INT DEFAULT 1;       DECLARE tid VARCHAR(20);       DECLARE sas INT DEFAULT 0;       DECLARE aname VARCHAR(20); -- 通过类型找图书，作者名，销量内连接了三张表       DECLARE cur CURSOR FOR        SELECT t.title_id,sa.qty ,au.au_fname FROM titles t INNER JOIN  sales sa ON t.title_id=sa.title_id INNER  JOIN titleauthor ti ON t.title_id=ti.title_id INNER JOIN AUTHORS au ON au.au_id=ti.au_id WHERE TYPE = xxx;        DECLARE CONTINUE HANDLER FOR NOT FOUND SET n=0;                     CREATE TABLE linshi (shuming VARCHAR(20),xiaoliang INT,zuozhe VARCHAR(20));  -- 建表       OPEN cur;       FETCH cur INTO tid,sas,aname;              WHILE n=1 DO         -- insert into test VALUES (tid);         INSERT INTO linshi VALUES (tid,sas,aname);       FETCH cur INTO tid,sas,aname;       END WHILE;       CLOSE cur;             -- SELECT * FROM test;       SELECT * FROM linshi;  -- 输出        DROP TABLE linshi; -- 删表       -- select tid,sas,aname;    END$$DELIMITER ;

那么，双重循环俩个游标咋整？注意：俩个游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET n=0;

对俩个游标都生效。

处理办法：在外循环游标里开一个新的begin - end，里面再放一个游标

DELIMITER $$USE `pubs10`$$DROP PROCEDURE IF EXISTS `pp3`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `pp3`()BEGIN       DECLARE aid VARCHAR(20);       DECLARE tid VARCHAR(20);       DECLARE n INT DEFAULT 1;       DECLARE cur1 CURSOR FOR SELECT DISTINCT au_id FROM titleauthor;       DECLARE CONTINUE HANDLER FOR NOT FOUND SET n = 0;       DELETE FROM information;       OPEN cur1;       FETCH cur1 INTO aid;              WHILE n=1 DO       INSERT INTO tttt VALUES (aid);        BEGIN -- 第二个begin中，声明第二个游标          DECLARE dan INT DEFAULT 0;          DECLARE he INT DEFAULT 0;          DECLARE tcnt INT DEFAULT 0;           DECLARE cur2 CURSOR FOR SELECT DISTINCT title_id FROM titleauthor WHERE au_id =aid;          DECLARE CONTINUE HANDLER FOR NOT FOUND SET n=0;                      OPEN cur2;          FETCH cur2 INTO tid;          WHILE n=1 DO          SELECT COUNT(1) INTO tcnt FROM titleauthor WHERE title_id = tid;          IF tcnt =1 THEN          SET dan=dan+1;          ELSE          SET he=he+1;          END IF;                              FETCH cur2 INTO tid;                    END WHILE;          INSERT INTO information VALUES (aid,dan+he,dan,he);          CLOSE cur2;              END;       SET n=1;  -- 为避免第二个游标找不到结果影响第一个，这里将n的值为1       FETCH cur1 INTO aid;       END WHILE;       CLOSE cur1;       SELECT * FROM information;     END$$DELIMITER ;