【数据库】存储过程的创建与调用

1.存储过程原理

将sql语句，如增删改查建封装起来，再通过【CALL】调用

2.存储过程的创建

以navicat为例，需要进入到A服务器的A数据库，打开后找到函数项，右键新建函数，选择过程，点击完成，不需要填写名称

3.编辑存储过程

// navicat会默认生成基础语句

CREATE DEFINER = CURRENT_USER PROCEDURE ``()
BEGIN
    #Routine body goes here...
​
END;

3.1.用户权限

// 即修改【DEFINER】之后的内容

CREATE DEFINER = `root` PROCEDURE ``()
BEGIN
    #Routine body goes here...
​
END;

// 如果想要指定a用户就把a用户名替换掉CURRENT_USER

CREATE DEFINER = `root`@`%` PROCEDURE ``()
BEGIN
    #Routine body goes here...
​
END;

// 如果想要允许该用户从任何主机连接到MySQL服务器，可以在后面增加@%，其中@是分隔符，%是通配符

3.2.定义储存过程名称

// 即修改【PROCEDURE】后 ``中的内容

CREATE DEFINER = `root`@`%` PROCEDURE `my_name`()
BEGIN
    #Routine body goes here...
​
END;

// 可以在其中人已填写，符合规范就好

3.3.编写存储过程

// 即在【BEGIN】和【end】中添加SQL语句

CREATE DEFINER = `root`@`%` PROCEDURE `my_sql`()
BEGIN
    SELECT * FROM sheet_1 WHERE id = '1313';
​
END;

// SQL语句写在BEGIN和END之间，即将编辑好的sql替换掉提示语#Routine body goes here...

3.4.自定义条件参数

CREATE DEFINER = `root`@`%` PROCEDURE `my_name`()
BEGIN
    SELECT * FROM sheet_1 WHERE id = '1313';
​
END;

# 如果想要在调用时手动输入查询的参数，需要添加一个设定值
# 即写在my_name()的括号中，并在SQL语句中引用这个设定值,如下【set_id 】

CREATE DEFINER = `root`@`%` PROCEDURE `my_name`(IN set_id VARCHAR (100))
BEGIN
    SELECT * FROM sheet_1 WHERE id = set_id;
​
END;

--- 
其中

IN：关键字用于定义存储过程的输入参数

set_id：用户自定义设定值的名称，符合规范就可以

VARCHAR：是数据类型

(100)：是数据长度

设定好之后，点击保存，即可在左侧函数项中添加一个名为my_sql的存储过程
--- 

4.调用存储过程

// 在左侧栏查询出新建一个查询，并通过CALL调用存储过程

CALL my_name('1313') # 查询id为1313的数据

// 其中my_name就是存储过程名称，参数'1313'就是set_id的实际值

5.直接执行SQL语句和调用存储过程比较

	直接执行SQL语句	存储过程调用
灵活性	高，即时编写和执行	低，需要预先定义存储过程
性能	对于一次性或偶尔执行的复杂查询可能更高效	对于频繁执行的复杂查询更高效，因为减少了编译时间
网络开销	大，每次执行都需要发送SQL语句	小，只需调用存储过程名称和传递参数
安全性	较低，除非采取严格的参数化查询等措施	较高，通过限制访问权限和存储过程内部的逻辑控制
调试难度	低，错误和异常通常能立即反馈	高，调试过程复杂且难以直接观察执行状态和过程
可重用性和模块化	低，每次执行都需要重新编写SQL语句（除非保存为脚本）	高，存储过程可以被多个应用程序或用户重用
可移植性	高，与数据库系统无关	低，与特定数据库系统紧密耦合
维护成本	低，通常只需修改SQL语句	高，可能需要修改存储过程代码并在数据库服务器上进行重新编译和部署