mysql存储过程

mysql查看所有存储过程

查询数据库中的存储过程

 

方法一：

       

select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'

 

方法二：

        

 show procedure status;

 

查看存储过程或函数的创建代码

show create procedure proc_name;
show create function func_name;

新建一个存储过程

DELIMITER $$
DROP PROCEDURE IF EXISTS `pro_find`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_find`()
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE a,b INT DEFAULT 0;

    DECLARE cur1 CURSOR FOR SELECT mark FROM stugra;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    OPEN cur1;
    REPEAT
    FETCH cur1 INTO a;
    IF NOT done THEN
       IF  a<60  THEN
        SET b=b+1;
       END IF;
    ELSE
    SELECT b;
    END IF;
    UNTIL done END REPEAT;
    CLOSE cur1;
    END$$

DELIMITER ;

DELIMITER $$
DROP PROCEDURE IF EXISTS `Pro_hr_total_record`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Pro_hr_total_record`
(
    TabName varchar(20),
    SQLFilter varchar(1000),
    CountIndex int
)
BEGIN
    declare i int;
    declare SQLStr varchar(1000);
    drop table if exists hr_total_record;
    set i=0;
    set SQLStr='create table hr_total_record (select staff_name,sum(t_month) from ';
    set SQLStr=CONCAT(SQLStr,TabName,' where ');
    while i<CountIndex do
        set SQLStr = CONCAT(SQLStr,substring_index(SQLFilter,';',i));
        set i = i+1;
    end while;
    set SQLStr=CONCAT(SQLStr,' )');
    SET @sql = SQLStr;
    PREPARE sl FROM @sql;
    EXECUTE sl;
    DEALLOCATE PREPARE sl;
END$$

DELIMITER ;