mysql存储过程

MySQL存储过程管理

最新推荐文章于 2025-09-11 20:53:34 发布

weixin_34318326

最新推荐文章于 2025-09-11 20:53:34 发布

阅读量57

点赞数

文章标签：数据库

本文介绍两种查询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 ;