mysql sql

MySQL存储过程和函数

存储过程和函数

Posted by hongfei on March 28, 2020

存储过程和函数

存储过程和函数的生成要素

创建存储过程和函数

  • Create procedure # 创建存储过程

  • Create function # 创建函数

    • 创建语法
    mysql> help CREATE PROCEDURE;
Name: 'CREATE PROCEDURE'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
  
CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body
  
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
  
func_parameter:
    param_name type
  
type:
    Any valid MySQL data type
  
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  
routine_body:
    Valid SQL routine statement

    创建存储过程

    drop  PROCEDURE if exists simpleproc ;
  
delimiter //
CREATE (IN param1 int, OUT param2 int)
BEGIN
SELECT count(*) INTO PARAM2 FROM cmdb_userextra where user_id>param1;
END//
delimiter ;
# delimiter // 定界符，更改语句的结束符，本session生效
  
call simpleproc(5,@a); # 调用存储过程，输入参数5，@a接收函数输出
SELECT @a;

    创建函数

    mysql> delimiter //
mysql> CREATE function simplefunc(param1 int)
    -> returns int
    -> deterministic
    -> BEGIN
    -> SELECT count(*) into @a FROM cmdb_userextra where user_id>param1;
    -> return @a;
    -> END//
Query OK, 0 rows affected (0.01 sec)
  
mysql> delimiter ;
mysql>
mysql> select simplefunc(20);
+----------------+
| simplefunc(20) |
+----------------+
|             29 |
+----------------+
1 row in set (0.00 sec)

  • 函数与存储过程最大的区别就是函数调用有返回值，调用存储过程用call语句，而调用函数就是引用函数名+参数即可

  • Definer 和 sql security子句指定安全环境
    • Definer是MySQL的特殊的访问控制手段，当数据库当前没有这个用户权限时，执行存储过程很可能会报错
    • sql security的值决定了调用存储过程的方式，取值：definer(默认)或者invoker
      • definer: 在执行存储过程前验证definer对应的用户如dba@127.0.0.1是否存在，以及是否具有执行存储过程的全选，若没有则报错
      • invoker: 在执行存储过程时判断invoker即调用该存储过程的用户是否具有相应权限，若没有则报错。
  • IN，OUT，INOUT三个参数前的关键词只适用于存储过程，对函数而言所有的参数默认都是输入参数
  • IN输入参数用于把数值传入到存储过程中；OUT输出参数将数值传递到调用者，初始值是NULL;INOUT输入输出参数把数据传入到存储过程，在存储过程中修改之后再传递到调用者
show create PROCEDURE simpleproc ;
drop user  'oldwang'@'127.0.0.1';
# 当定义存储过程的用户'oldwang'被删除后，调用存储过程报错。可以用下面的方式解决。修改存储过程的安全环境
alert PROCEDURE simpleproc sql security invoker; 
show create PROCEDURE simpleproc ;

mysql> select routine_schema,routine_name,routine_type from routines where routine_schema='medivh';
+----------------+--------------+--------------+
| routine_schema | routine_name | routine_type |
+----------------+--------------+--------------+
| medivh         | simplefunc   | FUNCTION     |
| medivh         | simpleproc   | PROCEDURE    |
+----------------+--------------+--------------+
2 rows in set (0.01 sec)

mysql> show create function simplefunc;
---
| simplefunc | NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost` FUNCTION `simplefunc`(param1 int) RETURNS int(11)
    DETERMINISTIC
BEGIN
SELECT count(*) into @a FROM cmdb_userextra where user_id>param1;
return @a;
END | utf8                 | utf8_general_ci      | utf8mb4_general_ci |
---
1 row in set (0.00 sec)

Declare语句

  • declare语句通常用来声明本地变量、游标、条件或者handler
  • declare语句只允许出现在bdgin。。。end语句中而且必须出现在第一行
  • declare的顺序也有要求，通常是先声明本地变量，再是游标，然后是条件和handler
delimiter //
CREATE function simplefunc2 (parm1 int)
returns INTO
deterministic
BEGIN
declare v_count int default 0；
SELECT count(*) into v_count FROM students WHERE sid>parm1;
return v_count;
END //
delimiter ;

流程控制语句

case语句

delimiter //
CREATE PROCEDURE exp_case(v_sid int)
BEGIN
declare v int default 1;
select gender INTO v from students where sid=v_sid;
CASE v
WHEN 1 THEN UPDATE STUDENTS SET gender=2 WHERE sid=v_sid;
WHEN 2 THEN UPDATE STUDENTS SET gender=1 WHERE sid=v_sid;
ELSE
UPDATE students SET gender=-1 WHERE sid=v_sid;
END CASE;
END;
//
delimiter ;

delimiter //
CREATE PROCEDURE exp_case(v_sid int)
BEGIN
declare v int default 1;
select gender INTO v from students where sid=v_sid;
CASE
WHEN v<2 THEN UPDATE STUDENTS SET gender=2 WHERE sid=v_sid;
WHEN v>1 THEN UPDATE STUDENTS SET gender=1 WHERE sid=v_sid;
ELSE
UPDATE students SET gender=-1 WHERE sid=v_sid;
END CASE;
END;
//
delimiter ;

IF语句

DELIMITER //
CREATE FUNCTION SimpleCompare(n INT, m INT)
RETURNS VARCHAR(20)
deterministic
BEGIN 
DECLARE S VARCHAR(20);
IF n > m  THEN s = ’=‘；
ELSEIF n = m THEN SET s = '=';
ELSE SET s = "<";
END IF
SET s = concat(n , ' ', s ' ',m)
return s

REPEAT语句

  • repeat语句是存储过程或函数中表达循环执行的一种方式
    • repeat语句中statement_list一直重复执行直到search_condition条件满足
    • statement_list可以包含一个或多个SQL语句

流程控制while语句

  • while语句是存储过程或函数中表达循环执行的一种方式
    • 当search_condition返回为true时，则循环执行statement_list中的语句，直到search_condition的结果返回为false
    • Statement_list中可以包含一个或多个SQL语句

流程控制 return语句

  • return语句在函数汇总，用来终结函数的执行并将指定值返回给调用
    • 在函数中必须要有至少一个return语句，当有多个return语句时则表明函数有多种退出方式

游标的使用

定义游标

  • cursor游标用来声明一个数据集（select的结果集）

  • 游标的声明必须在变量和条件声明之后，在handler声明之前

    delimiter //
crete procedure proc_cursor()
BEGIN
declare done int default true;
declare v_sid int;
declare v_sname VARCHAR(100);
declare cur1 cursor for select sid,sname  from students;
# 定义游标(名称cur1)为select的结果集合
declare continue handler for not found set done = false;
# 定义一个handler 当循环不能继续的时候，设置done=false
open cur1; 
# 打开游标
while done do
  # select done v_sid,v_sname;
	fetch cur1 into v_sid,v_sname;
	# fetch 一个游标，就是循环去游标中取值，赋值给变量v_sid,v_sname;
	select v_sid,v_sname;
end while
# 结束循环
end //
delimiter ;

    • curosor语句用来关闭之前打开的游标

      close cursor_name

    • 如果关闭一个未打开的游标，则mysql会报错

    • 如果在存储过程和函数中未使用此语句关闭已经打开的游标，则游标会在声明的begin…end语句执行完之后自动关闭。

    • cursor declare语句用来声明一个游标和指定游标对应的数据集合，通常数据集合是一个select语句

      delcare cursor_name cursor for select_statement

      • Selecgt_statement代表一个select语句

    • corsor fetch语句用来获取游标指定数据集的下一行数据并将各个字段值赋予后面的变量

      fetch [[NEXT] FROM] cursor_name INTO var_name [m var_name] ...

      • 数据集中的自调研需要和INTO语句中定义的变量一一对应
      • 数据集中的数据都fetch完之后，则返回NOT FOUND

Declare Handler语句

  • Handler_action子句生命当执行完sttement语句之后应该怎么办
    • continue代表继续执行该存储过程或函数
    • Exit代表退出声明此handler的begin…end语句块
    • Undo参数之后会不支持
  • Condition_value的值有以下几种：
    • mysql_err_code表示mysql err code的整数
    • SQLSTATE sqlstate_value表示MySQL中用5位字符串表达的语句状态
    • Condition_name表示之前在declare_condition语句中声明的名字
    • SQLWARNING表示所有的警告信息，即SQLSTATE中01开头的所有错误
    • NOT FOUND表示查完或者查不到数据，即SQLSTATE中02开头的所有错误
    • SQLEXCEPTION表示所有的错误信息
  • 当condition发生但没有声明handler时，则存储过程和函数依照如下规则处理
    • 发生SQLEXCEPTION错误，则执行exit退出
    • 发生SQLWARNING警告，则执行continue继续执行
    • 发生NOT FOUND情况，则执行continue继续执行
create table t1(s1 INT, primary key(s1));

delimiter //
create procedure proc_handler_demo()
begin 
declare continue handler for SQLSTATE '23000' SET @X2=1;
declare continue handler for 1062 set @X2=1；
set @x=1;
insert into values(1);
set @x=2;
insert into values(1);
set @x=3;
END；
//
delimiter

call proc_handler_demo()
select @x # @x会等于几？？

1062 或者 SQLSTATE ‘23000’ 表示主键冲突

触发器

创建触发器

  • Create trigger语句

    • create trigger语句用来创建一个触发器，触发器的作用是当表上有对应SQL语句发生时，则触发执行。
    • 触发器创建时需要指定对应的表名tabl_name
    for each row  # 每行都要修改
  
# 创建一个触发器
delimiter //
create trigger simple_trigger
after UPDATE
on students for each row
BEGIN
insert into students_history values(new.sid, old.sname, new.sname, old.dept_id,new.dept_id,now());
end;
//
delimiter ;

    delimiter //
create trigger trig3
after insert
on students for each row
BEGIN
insert into students_history values(new.sid, null, new.sname, null,new.dept_id,now());
end;
//
delimiter ;

触发器内容的插入的列 别加主键，会造成触发器修改的表的主键冲突。报错不够明显，难以排查。

show triggers 查看触发器。

练习

在score表上创建一个触发器，当有新的数据插入试，在score_avg表里记录对应学生的所有课程的平均成绩（注意，如果在score_avg表里已经有了该学生的记录，需要update）

delimiter //
create trigger trig2 
after insert on each row
begin 
declare n INT
select count(*) into n from scroe_avg where sid=new.sid;
if n=1 THEN
	update score_avg set avg_score=(select avg(score) from score where sid=new.sid) where sid=new.sid;
ELSE
	insert into scroe_avg select sid, avg(score) from score where sid=new.sid group by sid;
END;
//
delimiter ;

如何查看已经存在的存储过程和函数

查看存储过程、函数和创建方式

select routine_schema, routine_name, routine_type from information_schema.routines where routine_schema='db_name';
show create procedure handlerdemo
show create function simplefunc

查看创建触发器

select trigger_schema, trigger_name from infornation_schema.trigger_schema;
select trigger_schema, trigger_name from infornation_schema.trigger_schema where trigger_schema='db_name' ;
show create trigger 'trig_name';
CATALOG
    FEATURED TAGS
    Kubernetes K8S 容器 mysql sql优化 sql
    FRIENDS