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;

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

 

posted @ 2019-06-11 13:37  霞光里  阅读(2794)  评论(0编辑  收藏  举报