|
最近由于工作需要再看《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;
（编辑：广州站长网）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
