mysql 中的存储过程和函数

发布时间：2022-11-08 12:38:40 所属栏目：MySql教程来源：

导读：　　最近由于工作需要再看《mysql8 从入门到精通》这本书，存储过程和函数这部分觉得还蛮有意思的，和大家分享一下，如有误解，欢迎指正。

　　第1节存储过程

　　存储过程的本质

　　存储的本质是多条

　　最近由于工作需要再看《mysql8 从入门到精通》这本书，存储过程和函数这部分觉得还蛮有意思的，和大家分享一下，如有误解，欢迎指正。

　　第1节存储过程

　　存储过程的本质

　　存储的本质是多条符合SQL语法规范的语句的结合。

　　存储过程的基本形式如下：

　　CREATE PROCEDURE sp_name ([paramater])
　　[characteristics ...] routine_body
　　具体有哪些参数不详细说明，仅以示例说明如何创建存储过程。

　　以下是最简单的存储过程：

　　# 修改结束符
　　DELIMITER //
　　# 创建存储过程
　　CREATE PROCEDURE CntBooks()
　　BEGIN
　　    SELECT COUNT(*) AS cnt FROM books;
　　END//
　　# 还原存储过程所使用的的结束符
　　DELIMITER ;
　　# 调用存储过程
　　CALL CntBooks();
　　创建含有输入输出的存储过程如下：

　　# 修改结束符
　　DELIMITER //
　　# 创建存储过程
　　CREATE PROCEDURE T1(
　　    IN param0 INT,
　　    OUT param1 INT)
　　BEGIN
　　    SELECT param0* COUNT(*) INTO param1 FROM books;
　　END//
　　# 还原结束符
　　DELIMITER ;
　　# 调用存储过程, 若有输入项，输出项需要用@引用，结果为6
　　CALL T1(2, @param1);
　　删除存储过程如下：

　　DROP PROCEDURE T1 IF EXISTS;
　　修改存储过程如下：

　　DROP PROCEDURE T1 IF EXISTS;
　　第2节函数

　　mysql中的函数其实和存储过程差不多，但语法稍有不同。

　　CREATE FUNCTION func_name([param])
　　    RETURNS type
　　    [characteristic] routine_body
　　简单示例如下：

　　CREATE FUNCTION selct()
　　    RETURNS CHAR(20)
　　    RETURN (SELECT name FROM books WHERE price=35);
　　# 函数的调用方法和内置函数方法相同
　　如果直接按照一些书上的方法去写的话是会报错的，报错如下：

　　RROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)。

　　查资料后得知MySQL 函数，是因为默认情况下mysql创建函数是需要指定：

　　1 DETERMINISTIC 不确定的 2 NO SQL 没有SQl语句，当然也不会修改数据 3 READS SQL DATA 只是读取数据，当然也不会修改数据 4 MODIFIES SQL DATA 要修改数据 5 CONTAINS SQL 包含了SQL语句

　　需要对mysql的参数进行修改，再创建函数就不会报错了。

　　# 显示参数状态
　　show variables like "%log_bin_trust%";
　　# 修改参数
　　set global log_bin_trust_function_creators = 1;
　　第3节 declare

　　# 声明变量
　　declare var_name, var_name2 ... data_type [default value];
　　# 为变量赋值
　　set var_name = expr [, var_name = expr]...;
　　select col_name[,...] into var_name[,...] from table_expr;
　　类似变量的创建方式还可以创建条件，类似于python中的error处理。

　　# 定义条件
　　declare condition_name condition for [condition_type]
　　[condition_type]:
　　sqlstate [value] sqlstate_value | mysql_error_code;
　　具体演示如下，两种方式是等价的：

　　# 两种创建条件的方式，一种是五个字符，一种是四个数字
　　declare comd_not_found condition for sqlstate '42000';
　　declare comd_not_found condition for 1148;
　　定义执行条件后，可以定义处理程序。

　　# 格式
　　declare handler_type handler for condition_value[,...] sp_statement
　　handler_type: continue | exit| undo
　　condition_value:
　　 sqlstate value|condition_name|mysql_error_code ...
　　# 简单的条件处理程序
　　declare continue handler for sqlstate '42S02' set @info='no_such_table';
　　光标是对查询的大数据集进行浏览的一种便捷方式，只能在函数或者存储过程中使用。创建的顺序需要遵循：

　　变量 or 条件 > 光标 > 处理程序。

　　光标的格式：

　　# 创建
　　declare cursor_name cursor for select_statement;
　　# 打开
　　open cursor_name;
　　# 关闭
　　close cursor_name;
　　# 使用光标, 将查询结果放入var_name中，需要在光标声明前定义好
　　fetch cursor_name into var_name [,...];
　　第4节流控制

　　条件控制IF

　　# 格式
　　if expr_condition then statement
　　[elseif expr_condition then statement]
　　[else statement]
　　end if;
　　# 示例
　　if val is NULL
　　    then select 'val is null';
　　    else select 'val is not null';
　　end if;
　　条件控制case when，这里描述的是存储过程中的case when，与sql语法中的case when稍微有点区别。

　　# 两种方式
　　# 1
　　case case_expr
　　    when when_value then statement;
　　    [when when_value then statement];
　　    else statement;
　　end case;
　　# 2
　　case
　　    when expr_condition then statement;
　　    [when expr_condition then statement];
　　    else statement;
　　end case;
　　循环loop和退出leave，其中leave可退出任何被标注的流程控制构造。

　　# 格式
　　[loop_label]: loop
　　    statement;
　　end loop [loop_label]
　　# exp，连续加1到10，
　　declare id int default 0;
　　add_loop: loop
　　    set id = id + 1;
　　    if id >= 10 then leave add_loop;
　　    else if;
　　end loop add_loop;
　　iterate 只可以出现在loop,repeat,while语句中，跳转到指定语句处。

　　# 示例
　　create procedure doiterate()
　　    begin
　　        declare p1 int default 0;
　　        my_loop: loop
　　            set p1 = p1+1
　　            if p1 < 10 then iterate my_loop;
　　            elseif p1 > 20 then leave my_loop;
　　            end if;
　　         select 'p1 btw 10 and 20';
　　      end loop my_loop;
　　  end;
　　repeat和while循环。

　　# 示例
　　[repeat_label :] repeat
　　    statement_list
　　until expr_condition
　　end repeat [repeat_label]
　　[while_label :] while expr_condition do
　　    statement_list
　　end while [while_label]
　　第5节查看&修改&删除存储过程和函数

　　查看已有过程或者函数，有三种方法。

　　show procedure| function status like "**";
　　show create procedure | function sp_name;
　　select * from information_schema.routines where routine_name='sp_name' and routine_type = "FUNCTION";
　　修改存储过程和函数与创建语句基本一致，具体格式如下：

　　# 修改不是修改存储过程或者函数的功能，而是修改类似权限或者属性
　　alter procedure | function sp_name [charateristic...]
　　删除存储过程。

　　drop procedure | function if exists sp_name;

（编辑：开发网_商丘站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

MariaDB10.3 系统版本	Amoeba达成数据库读写
orchestrator 高可用y	该如何使用MySQL事务