mysql存储过程简单例子

1.之前经常在oracle数据库中使用存储过程，换到mysql后用的不多，但是有时候也用，大致记录一下，基本和oracle的一样。

CREATE DEFINER = `root`@`%` PROCEDURE `NewProc`()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE n_id decimal;
  DECLARE cur1 CURSOR FOR SELECT b.ID FROM book_fs AS b where b.BOOKID NOT IN (SELECT ID FROM media) ORDER BY b.ID limit 500000;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  -- 打开游标
    OPEN cur1;
  REPEAT
    FETCH cur1 INTO n_id;
    IF NOT done THEN
            DELETE from book_fs where id = n_id;
    END IF;
  UNTIL done END REPEAT;
  CLOSE cur1;
END;

2.统计所有表数据量

use information_schema;
select table_name,table_rows from tables where TABLE_SCHEMA = 'your_DB_name' order by table_rows desc;

  -- 说明：以上语句得出的行统计数量，并不精确，是一个大约估计的数量