存储过程

一、什么是存储过程和函数

存储过程是一组预先编译好的SQL语句的集合，可以理解成批处理语句，特点：

提高了代码的重用性简化了操作
减少了编译次数并且减少了和数据库服务器的连接次数，
提高了效率

二、创建存储过程

2.1.创建语法

CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    存储过程体（一组合法的SQL语句）
END

注意：

参数列表包含三部分：参数模式、参数名、参数类型，举例：

in stuname varchar(20)

参数模式：

in：该参数可以作为输入，也就是该参数需要调用方传入值
out：该参数可以作为输出，也就是该参数可以作为返回值
inout：该参数既可以作为输入又可以作为输出，也就是该参数既需要传入值，又可以返回值

2.2.定义结束标记

默认的 MySQL 语句分隔符为 ; （正如你已经在迄今为止所使用的MySQL 语句中所看到的那样）。 mysql 命令行实用程序也使用; 作为语句分隔符。如果命令行实用程序要解释存储过程自身内的; 字符，则它们最终不会成为存储过程的成分，这会使存储过程中的SQL 出现句法错误。解决办法是临时更改命令行实用程序的语句分隔符，如下所示：

其中， DELIMITER // 告诉命令行实用程序使用 // 作为新的语句结束分隔符，可以看到标志存储过程结束的END 定义为 END//而不是 END; 。这样，存储过程体内的 ; 仍然保持不动，并且正确地传递给数据库引擎。最后，为恢复为原来的语句分隔符，可使用DELIMITER; 。

除 \ 符号外，任何字符都可以用作语句分隔符。如果你使用的是mysql 命令行实用程序，

所以只需要在开头加上 DELIMITER // 和结尾加上 // DELIMITER;即可。

语法：

delimiter 结束标记

案例：

delimiter //

2.3.调用存储过程

通过call语句调用存储过程

CALL 存储过程名(实参列表);

2.4.创建和调用存储过程案例

1）.空参列表

#案例：插入到admin表中五条记录

SELECT * FROM admin;

# 结束标志
DELIMITER //
# 创建存储过程
CREATE PROCEDURE myProced1()
BEGIN
    INSERT INTO admin(username,`password`) 
    VALUES('刘备','1234'),('关羽','6789'),('张飞','8976');
END //
DELIMITER;

# 调用存储过程
CALL myProce1()

2）.创建带in模式参数的存储过程

案例1：创建存储过程实现根据女神名，查询对应的男神信息

DELIMITER //

CREATE PROCEDURE myProced2(IN bName VARCHAR(25))
BEGIN
    SELECT boyName FROM beauty 
    INNER JOIN boys ON beauty.boyfriend_id = boys.id
    WHERE beauty.`name` = bName;
END //

DELIMITER;

# 调用
CALL myProced2('热巴')

案例2 ：创建存储过程实现，用户是否登录成功

DELIMITER //
CREATE PROCEDURE myProced3(IN username VARCHAR(25),IN pwd VARCHAR(25))
BEGIN
    # 声明并初始化
    DECLARE result INT DEFAULT 0;
    
    SELECT COUNT(*) INTO result # 赋值
    FROM admin
    WHERE admin.username = username AND admin.`password` = pwd;
    
    # 判断
    SELECT IF(result>0,'成功','失败'); # 使用
END //
DELIMITER;

# 调用
CALL myProced3('john','8888')

3）.创建out模式参数的存储过程

案例1：根据输入的女神名字，返回对应的男神名字

DELIMITER //
CREATE PROCEDURE myProced4(IN bName VARCHAR(25), OUT boyName VARCHAR(20))
BEGIN
    SELECT bo.boyName INTO boyName       # INTO boyName是将bo.boyName赋值给返回的参数boyName
    FROM beauty be
    INNER JOIN boys bo ON be.boyfriend_id = bo.id
    WHERE be.`name` = bName;
END //

DELIMITER;

# 调用,@boyName定义了一个用户变量，接收存储过程中OUT中返回的信息
CALL myProced3('热巴',@boyName)

# 查看变量中的值
SELECT @boyName

案例2：根据输入的女神名，返回对应的男神名和魅力值

DELIMITER //
CREATE PROCEDURE myProced5(IN bName VARCHAR(25), OUT boyName VARCHAR(20),OUT userCp VARCHAR(20))
BEGIN
    SELECT bo.boyName,bo.userCP INTO boyName,userCp       # INTO boyName,userCp是将bo.boyName,bo.userCP赋值给返回的参数boyName、userCp
    FROM beauty be
    INNER JOIN boys bo ON be.boyfriend_id = bo.id
    WHERE be.`name` = bName;
END //

DELIMITER;

# 调用,@boyName定义了一个用户变量，接收存储过程中OUT中返回的信息
CALL myProced5('热巴',@boyName,@userCp)

# 查看变量中的值
SELECT @boyName,@userCp

4）.创建带inout模式参数的存储过程

案例1：传入a和b两个值，最终a和b都翻倍并返回

DELIMITER //
CREATE PROCEDURE (INOUT a INT,INOUT b INT)
BEGIN
    SET a=a*2;
    SET b=b*2;
END //

DELIMITER;

# 创建两个变量
SET @m=10;
SET @n=5;
# 调用
CALL myProced6(@m,@n)
SELECT @m,@n

三、修改存储过程

使用 ALTER PROCEDURE 语句来修改存储过程。具体语法如下：

ALTER PROCEDURE procedure_name(parameters)
BEGIN
    -- 存储过程代码
END;

其中，procedure_name 是要修改的存储过程的名称，parameters 是存储过程的参数列表。

ALTER PROCEDURE 语句不支持在MySQL中直接修改存储过程的参数列表。如果需要修改存储过程的参数，通常的做法是先删除原有的存储过程，然后重新创建一个新的存储过程。以下是修改后的SQL语句：

# 修改存储过程,根据女神名查对应的男神名
# 1.先删除存储过程
DROP PROCEDURE IF EXISTS myProced4

# 2.在创建存储过程
DELIMITER //
CREATE PROCEDURE myProced4(IN bName VARCHAR(25))
BEGIN
    SELECT bo.boyName FROM beauty be
    INNER JOIN boys bo ON be.boyfriend_id = bo.id
    WHERE be.`name` = bName;
END //

DELIMITER;

# 调用,@boyName定义了一个用户变量，接收存储过程中OUT中返回的信息
CALL myProced4('热巴')

四、删除存储过程

存储过程被创建后，保存在数据库服务器上，直至被删除。可以使用 DROP PROCEDURE 语句删除数据库中已创建的存储过程。语法格式如下：

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>

语法说明如下：

1) 过程名

指定要删除的存储过程的名称。

2) IF EXISTS

指定这个关键字，用于防止因删除不存在的存储过程而引发的错误。注意：存储过程名称后面没有参数列表，也没有括号，在删除之前，必须确认该存储过程没有任何依赖关系，否则会导致其他与之关联的存储过程无法运行。

# 删除存储过程
DROP PROCEDURE IF EXISTS myProced3;

五、查看存储过程

查看存储过程的状态

SHOW PROCEDURE STATUS LIKE 'myProced5%';

查看存储过程的定义

SHOW CREATE PROCEDURE ;

posted @ 2024-05-17 18:32 酒剑仙* 阅读(69) 评论(0) 收藏举报

刷新页面返回顶部

Augus