MySQL数据分析进阶（八）存储过程

※食用指南：文章内容为‘CodeWithMosh’SQL进阶教程系列学习笔记，笔记整理比较粗糙，主要目的自存为主，记录完整的学习过程。（图片超级多，慎看！）

【中字】SQL进阶教程 | 史上最易懂SQL教程！10小时零基础成长SQL大师！！https://www.bilibili.com/video/BV1UE41147KC/?spm_id_from=333.1007.0.0&vd_source=b287f1f4a1fa54cc438e31a0f87ef4e2

第八章：存储过程

1、WHAT ARE STORED PROCEDURES——什么是存储过程

不建议在应用代码里写语句：会让应用代码很混乱且难以维护

①在使用C#、Java或者Python开发应用

如果把Java代码和SQL混在一起，那么SQL代码介入会让应用代码很混乱和难以维护

②一些C#、Java这样的编程语言需要编译工作

在这类应用代码里写SQL查询，一旦发现需要修改其中的查询，就必须重新编译应用代码才能生效。所以每次修改SQL代码都需要重新编译或者可能重新部署应用代码

解决方法：将SQL代码储存在它应属的数据库里——存储过程或函数中

存储过程Stored Procedure：一个包含一堆SQL代码的数据库对象

存储过程Stored Procedure的优势：

①Store and organize SQL（存储和管理SQL）

在应用代码里，调用这些过程来获取或保存数据，使用存储过程来存储和管理SQL代码

②Faster execution（更快速执行）

大多数DBMS可以对存储过程里的代码做一些优化，因此存储过程里的SQL代码有时执行起来更快

③Data security（数据安全性）

取消对所有表的直接访问权限，让很多操作如插入、更新和删除数据由存储过程来完成，然后可以指定能够执行特定存储过程的某一人，从而限制用户对我们数据的操作范围（防止一些用户删除数据）

2、CREATING A STORED PROCEDURE——创建一个存储过程

把这个查询存在存储过程里

①get_clients，使用小写字母并用下划线分隔，是MySQL开发者通用的

②BEING和END关键字之间的内容成为存储过程的主体（body）

③通常创建的存储过程有好几条语句（SELECT部分），每条（包含一条）语句都需要分号终结

其他的DBMS，如SQL Server可能就不需要

DELIMITER重新定义分割符：让存储过程不会到clients时报错

 

$$视为新的分隔号，把所有的语句当成一个整体；

最后再用DELIMITER ；把符号还回分号

🔺DELIMITER后面要加空格

其他的DBMS，如SQL Server不需要改动默认分隔符

执行后刷新

一般来说，我们会用C#、Java、Python等应用代码调用存储过程，但有时想用SQL代码调用存储过程，使用CALL即可

练习：

3、CREATING PROCEDURES USING MAYSQLWORKBENCH——使用MYSQL工作台创建存储过程

更为简单的存储工程创建方式：

导航面板→右击存储过程文件夹→创建一个存储过程

在这个窗口就不用担心改默认分隔符，重点放在SQL语句

MySQL会默认添加反引号，防止SQL关键字的名称冲突

编写完Apply，MySQL自动生成SQL语句，再次Apply

4、DROPPING STORED PROCEDURES——删除存储过程

出了错的或者想要重新创建存储过程的方法：

执行后即刻消失，再次执行就会报错（删除一个并不存在的过程）

可以使用IF EXISTS关键字来防止错误出现（参考view）

AGAIN：最好把删除和创建每一个存储过程的代码存储在不用的SQL文件中，并把文件放在GIT源代码控制下，可以和其他组员共享GIT存储库，任何人都可以在自己的电脑上使用所有视图和存储过程再建数据库

安全重建：删除检查是否有同名的存储过程存在，有的话就删掉，再重新创建

（并存到了新stored_procedures文件夹，名为client_id）

5、PARAMETERS——参数

在存储过程中添加参数

一般使用参数为存储过程传递值，也可以为调用程序赋值

state CHAR (2)：有两个字符的字符串

VARCHAR：可改变长度的字符串（存储姓名、电话号码、信息），一般能确定字符串有固定长度才使用

WHERE state = state：这里不会起任何作用因为在用它本身列的值对比（无论如何都是对的）

🔺需要把这两个state（state这一列的值、定义的state这个系数）区分开：

①加前缀p（parameter）

 

②加后缀_ param

③不变动，给表格起别名

调用情况：

如果不提供任何值，将会出现错误，因为MySQL中所有系数都是必填的

练习：

Invoices表中client_id系数为INT（整数）

完成后调用，输入你的系数（即哪位客户）

 6、PARAMETERS WITH DEFAULT VALUES——带默认值的参数

为系数配置默认值

在get_clients_by_state制定一个规则：如果存储过程调用这无法明确具体在那个state，就默认返回CA的客户

①使用IF函数

IF函数结尾需要用END IF：告诉MySQL语句该到哪里结束

②创建完毕使用空值调用这个过程——验证

③再改变一下规则：不只是返回位于CA的客户，而是返回所有客户

当输入NULL

当输入CA

④将两段查询合并为一段

IFNULL(state,C.state)：如果第一个值是空值，就返回第二个值

练习：

INT、TINYINT都是整数

INT：占用4个字节，可以存储更大的数字-

TINYINT：占用1字节的内存，可以存储0-255的数字

client_id、payment_method_id 这两个系数都不是必填的，

^ 如果传递NULL，过程应该能够返回数据库里的所有付款记录

^ 如果提供client_id，则返回这个客户的payment_method_id

^ 如果这两个系数都赋值，就返回指定客户使用付款方式支付的所有付款

AND：两个条件都需要满足

payment_mothod_id：是参数名称，可以与列名不同

Arguments实参：提供给形参的值

Parameters形参：占位符，过程或函数中的小小坑位

如上定义了两个形参：client_id、payment_method_id

7、PARAMTETER VALIDATION——参数验证

使用过程插入、更新和删除数据

创建一个过程更新发票过程中学习参数验证，确保过程不会意外地往数据库存储错误数据

DECIMAL(9,2)：小数数据类型

第一个参数代表位数，第二个参数是小数点后的位数：最多9位数，小数点后两位

只想更新i.payment_total 、i.payment_date

不想意外更新invoice表中的number，invoice_date等

之前数据：

调用执行

如果数值为负数，则会出现不合理的数据

❗如何验证传递给这个存储过程的参数：

SIGNAL语句：标志或者引发错误（像其他编程语言中抛出异常）

查找错误的完整列表在此处查询：sqlstate errors

前两个字母界定了错误类别

22：数据异常

22003：在存储过程中，处理超位值

SIGNAL SQLSTATE '22003'：这里的22003是字符串，而不是数字

确认后再次执行，弹出错误提醒

综上：在做任何数据变动前线检查存储过程系数很有帮助

但如果写太多参数验证，也会使存储过程变得很复杂并难以维护

这里的payment_amount本身就是不允许有空值，即便传递了空值，MySQL自动标注这一错误，不需要再编写

要尽量利用最少的验证逻辑，只保留最最关键的

应该从用户端接受输入信息，再在应用中使用更多验证（前后端中进行验证）

相比访问数据库，在应用在检测和报告错误会更快捷

把参数验证作为终极备选方案，以防止有人没有通过应用直接调用了存储过程

8、OUTPUT PARAMETERS——输出参数

用参数来给调用程序返回值

创建后验证：

也可通过参数获取这些值

使用TINYINT或者INT，具体根据未支付发票数量决定

默认情况下，存储过程的所有这些系数都是输入参数，也就是只能在给过程传递值的时候才可以使用它们，所以需要给两个参数附上OUT关键字前缀，就会把参数标记为输出参数

    SELECT COUNT(*),SUM(invoice_total)：取读数据

    INTO invoices_count,invoices_total：复制到这些输出参数

代码解释

①用户定义变量：set @invoices_count = 0;、set @invoices_total = 0;

变量就是可以用来存储单一值的对象

@作为前缀来定义变量，调用过程中传递这些变量

②(3, @invoices_count, @invoices_total);

第一个参数3，即为客户3，后面连个参数就是先前定义的变量

③select @invoices_count, @invoices_total;

调用过程后，需要用选择语句来取读这些值，并在此显示

使用输出参数需要在读取数据上花精力，除非有足够的动机，尽量避免使用

9、VARIABLES——变量

User or session variables ：用户或者会话变量

SET语句定义，@符号作为前缀

@invoices_count = 0;

通常在调用有输出参数的存储过程时使用这些变量（传递变量，获取输出参数值）

变量在整个客户会话过程中被保存，客户从MySQL断线时，变量又被清空

Local variables：本地变量（局部变量）

不会在整个客户端会话过程中被保存，一旦存储过程完成执行任务，这些变量就会被清光

一般在存储过程中执行计算任务

①设定一个商业规则

此处就可使用本地变量，在过程中定义这些本地变量，能轻易地计算这个公式

②在BEGIN语句后声明变量

DECLARE：是在复合语句中声明变量地指令，在过程中定义的变量并不是真正的定义。

                     只是在BEGIN/END块内定义了而已（也就是形参）

③第二、三个变量不设置默认值，用选择语句来设定

④计算风险因素

SET设计变量的值

以上为如何在存储过程中声明并使用本地变量

这些本地变量只有在存储过程中才有意义，只要声明它们，就可以被使用了

然后一旦我们执行完毕存储过程，就被抹去了

（结果错了，但暂时找不到原因）

10、FUNCTIONS——函数

创建自己的函数

函数只能返回单一值，比起存储过程，无法返回拥有多行和多列的结果集

如上节，创建函数计算每位客户的风险因素

RETURNS：明确了这个函数返回的值的类型，可以是INT、INTEGER或者任何其他MySQL中的数据类型

在RETURNS设置函数属性（每个MySQL至少要有一个属性）

DETERMINISTIC（确定性）：如果给予这个函数同样的一组值，它永远会返回一样的值

在不想根据数据库中的数据返回值的时候很有用

READS SQL DATA（读取SQL数据）：函数中会配置选择语句，以读取一些数据

MODIFIES SQL DATA（修改SQL数据）：函数中有插入、更新或者删除函数

可以同时有多种属性，既取读又修改数据

创建函数计算每位客户的风险因素，函数没有DETERMINISTIC（确定性）

输入同个client_id可能会返回不同值，这个客户后续可能会支付其他发票，所以风险因素随时可以改变

①选择READS SQL DATA

从获取风险因素过程复制变量、选择语句、计算风险因素的公式

②修改选择语句

执行后使用：

（结果为NULL,，暂时无解）

③解决出现空值：

把函数放在SQL文件，并放在源代码里

使用DROP FUNCTION IF EXISTS

11、OTHER CONVENTIONS——其他约定

函数：加fn前缀

存储过程：

①加proc、proc_前缀

②用驼峰式proGetRiskFactor（每个词首字母都大小写，除了第一个单词）

③直接getGetRiskFactor、get_risk_factor

DELIMITER使用$$或者//

以上约定根据已经在用的那种使用即可，无需强制使用那一种

（不要和老同事吵用哪种）

但如果从头推进一个项目，就可以根据自己的喜好约定

关键在于遵循已经存在的约定

————TBC