Mysql自定义函数和存储过程

摩森特沃 2021年03月29日 789次浏览

Mysql自定义函数和存储过程

自定义函数和存储过程的区别

  • 自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数
  • 自定义函数中必须包含一条return语句，而这条特殊的sql语句不允许在存储过程中出现
  • 可以直接对自定义函数进行调用而不需要使用call语句，而对存储过程的调用需要使用call语句

变量作用域

  • 内部的变量再其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，此时已经再其作用域外，变量不再可见，但可通过out参数或者将其指派给会话变量来保存其值

DELIMITER

  • 声明语句结束符,如：
    • DELIMITER $$
    • DELIMITER //

存储过程的参数

in 输入参数

  • 表示调用者向过程传入值（传入值可以是字面量或变量）

示例：

delimiter $$
create procedure in_param(in p_in int)
begin
    select p_in;
    set p_in=2;
    select P_in;
end$$
delimiter ;

## 调用
set @p_in=1;
call in_param(@p_in);
## 输出结果
1
2
## 调用
select @p_in;
## 输出结果
1
## 说明：p_in 在存储过程中被修改，但并不影响 @p_id 的值，因为前者为局部变量、后者为全局变量

out 输出参数

  • 表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）

示例：

delimiter //
create procedure out_param(out p_out int)
begin
    select p_out;
    set p_out=2;
    select p_out;
end//
delimiter ;
## 调用
set @p_out=1;
call out_param(@p_out);
## 输出
null
2
## 说明：因为out是向调用者输出参数，不接收输入的参数，所以存储过程里的p_out为null
## 调用
select @p_out;
## 输出结果
2
## 说明：调用了out_param存储过程，输出参数，改变了p_out变量的值

inout输入输出参数

  • 既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量），改类型的参数应尽量少用

示例：

delimiter $$
create procedure inout_param(inout p_inout int)
begin
    select p_inout;
    set p_inout=2;
    select p_inout;
end$$
delimiter ;

## 调用
set @p_inout=1;
call inout_param(@p_inout);
## 输出结果
1
2
## 调用
select @p_inout;
## 输出结果
2
## 说明：调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量
  • 注意
    • 如果过程没有参数，也必须在过程名后面写上小括号例
    • 确保参数的名字不等于列的名字，否则在过程体中，参数名被当做列名来处理

MySql控制语句

存储过程体控制语句

begin-end

条件语句

if-then-else语句

语句格式

if 条件 then
    动作
end if;

if 条件 then
    动作
else
    动作
end if;

示例代码

DELIMITER //
CREATE PROCEDURE proc2(IN parameter int) 
begin
    declare var int; 
    set var=parameter+1; 
    if var=0 then 
        insert into t values(17); 
    end if; 
    if parameter=0 then
        update t set s1=s1+1; 
    else 
        update t set s1=s1+2; 
    end if; 
end;
// 
DELIMITER ;

case语句(类似Java中的switch-case)

语句格式

case 变量
    when 条件 then
        动作
    when 条件 then
        动作
    else
        动作
end case;
  • 特别注意
    • when中的条件需为值而不能为表达式
    • else下如果没有语句，则else须不保留
      示例代码
DELIMITER //
CREATE PROCEDURE proc3 (in parameter int) 
begin 
    declare var int;
    set var=parameter+1;
    case var
        when 0 then
            insert into t values(17);
        when 1 then
            insert into t values(18); 
        else 
            insert into t values(19); 
    end case;
end; 
// 
DELIMITER ;

循环语句

while-end while(类似Java中的while语法)

语句格式

while 条件 do
    动作
end while

示例代码

DELIMITER // 
CREATE PROCEDURE proc4()
begin
    declare var int;
    set var=0;
    while var<6 do
        insert into t values(var);
        set var=var+1; 
    end while; 
end; 
// 
DELIMITER ;

repeat-end repeat(类似Java中的do-while语法)

语句格式

repeat
    动作
until 条件
end repeat;

示例代码

DELIMITER // 
CREATE PROCEDURE proc5 () 
begin
    declare v int;
    set v=0;
    repeat
        insert into t values(v); 
        set v=v+1; 
    until v>=5 end repeat; 
end; 
// 
DELIMITER ;

loop-end loop(类似Java中的for循环语句，其中的leave类似Java中的break语句)

语句格式

loop
    动作
leave --注意，此处的leave通常跟标签一起结合使用，如：leave Loop_lable
end loop;

示例代码

DELIMITER // 
CREATE PROCEDURE proc6 () 
begin
    declare v int;
    set v=0;
    LOOP_LABLE:loop
        insert into t values(v); 
        set v=v+1;
        if v >=5 then
            leave LOOP_LABLE;
        end if;
    end loop;
end;
// 
DELIMITER ;

iterate迭代(类似Java中的continue语句)

  • iterate通过引用复合语句的标号来重新开始复合语句

示例代码

DELIMITER // 
CREATE PROCEDURE proc10 () 
begin 
    declare v int; 
    set v=0; 
    LOOP_LABLE:loop 
        if v=3 then 
            set v=v+1; 
            ITERATE LOOP_LABLE; 
        end if; 
        insert into t values(v); 
        set v=v+1; 
        if v>=5 then 
            leave LOOP_LABLE; 
        end if; 
    end loop; 
end; 
// 
DELIMITER ;

Mysql函数

定义函数的格式一

CREATE FUNCTION 函数名(参数 类型,[参数 类型,...])
RETURNS 返回类型 RETURN 表达式值
-- 注意
-- 这种方式不能使用任何SQL语句

示例如下

-- 1. 创建函数
create function fun_add(a int,b int)
returns int return a + b;
-- 2. 执行函数
select fun_add(1,1);

定义函数的格式二

CREATE FUNCTION 函数(参数 类型,[参数 类型,...])
RETURNS 返回类型
BEGIN
END;
-- 如果要在函数体中可以使用更为复杂的语法，比如复合结构/流程控制/任何SQL语句/定义变量等。带复合结构的函数体的自定义函数的

示例如下

CREATE FUNCTION uuid_32() RETURNS CHAR(32)
BEGIN
 RETURN replace(uuid(), '-', '');
END;

Mysql游标

游标定义

  • 游标是一个存储在MySQL服务器上的数据库查询，它不是一条select语句，而是被该语句所检索出来的结果集。

使用游标的步骤

  • 声明游标：这个过程实际上是没有遍历数据的，它只是定义要使用的select语句来获取数据
  • 打开游标：游标定义好后，那么这里就需要打开游标。这个过程用前面定义的select语句把数据实际检索出来。即这个步骤之后，我们就可以遍历游标中的数据了
  • 遍历数据：对于有数据的游标，根据需要取出各行的数据来进行一定的操作
  • 关闭游标：使用完游标后，一定要关闭游标

游标使用的语法

  • 声明游标
DECLARE cursor_name CURSOR FOR select_statement;
  • 打开游标
OPEN cursor_name;
  • 遍历数据
FETCH cursor_name INTO var_name ;
  • 关闭游标
CLOSE cursor_name ;

应用举例

delimiter $ # 申明结束标志 
drop procedure if exists StatisticStore$ # 如果存储过程已经存在则删除 
CREATE PROCEDURE StatisticStore() 
BEGIN 
    # 创建接收游标数据的变量 
    declare c int; # 获取单条纪录的数量 
    declare n varchar(20); #获取名称 
    # 创建总数变量 
    declare total int default 0; 
    # 创建结束标志变量 
    declare done int default false; 
    # 创建游标 获取name和count的集合 
    declare cur cursor for select name,count from store where name = 'iphone'; 
    # 指定游标循环结束时的返回值 
    declare continue HANDLER for not found set done = true; 
    # 设置初始值 
    set total = 0; 
    # 打开游标 
    open cur; 
    # 开始循环游标里的数据 
    read_loop:loop 
        # 根据游标当前指向的一条数据 插入到上面申明的局部变量中 
        fetch cur into n,c; 
        # 判断游标的循环是否结束 
        if done then 
            leave read_loop; # 跳出游标循环 
        end if; 
        # 获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作， 
        set total = total + c; 
    # 有loop 就一定要有end loop 
    end loop; 
    # 关闭游标 
    close cur; 
    # 输出结果 
    select total; 
END 
$ 
delimiter ;
# 调用存储过程 
call StatisticStore();
上一篇： Maven