MySQL自定义函数和存储过程

一、自定义函数

自定义函数(user-defined function简称UDF)是一种对MySQL扩展的途径，其用法和内置函数相同。

自定义函数的两个必要条件：

参数
返回值(必须有)。函数可以返回任意类型的值。

1.1 创建

DELIMITER $$            --将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE FUNCTION 函数名([<参数1><类型1>[,<参数2><类型2>...]])   --定义函数
returns 数据类型         --定义返回值类型
-> BEGIN               --以关键字BEGIN开始
-> select * from city;  --执行语句
-> RETURN 值;           --返回值
-> END $$               --以关键字END结束
DELIMITER;              --将语句的结束符号恢复为分号

还可以有一些特别的选项，特别的选项写在return之后，begin之前，如：
- comment: 一个关于函数的描述
- 还有一些比如sql security等选项

1.2 调用

-- 无参调用
select myselect3();
-- 传参调用
select myselect5("python");
select * from class where id = myselect5("python");

1.3 查看

查看函数创建语句：

show create function 函数名;

查看所有函数：

show function status [like 'pattern'];

1.4 修改

函数的修改只能修改一些如comment的选项，不能修改内部的sql语句和参数列表。

alter function 函数名 选项;

1.5 删除

drop function 函数名;

1.6 案例

以下是拆分大整数为2幂的和算法

bigint目前支持最大值为

\[2^{62} = 4611686018427387904 \]

/*判断是否存在，如果存在就删除*/
DROP FUNCTION IF EXISTS binaryPow;
/*创建函数*/
create function binaryPow(num bigint(20))
/*返回值数据类型*/
RETURNS varchar(512)
begin
  /*参数二进制的变量*/
  DECLARE binaryNum VARCHAR(128) DEFAULT 0;
  /*自增变量*/
  DECLARE chooseNum int DEFAULT 0;
  /*返回值变量*/
  DECLARE returnNum varchar(255) DEFAULT 0;
  /*参数的长度*/
  DECLARE numLength varchar(255) DEFAULT 0;
  /*变量值*/
  DECLARE cur tinyint(1) DEFAULT 0;
  /*将参数转换成二进制并赋给binaryNum变量*/
  set binaryNum = bin(num);
  /*获得binaryNum长度赋给numLength*/
  set numLength = LENGTH(binaryNum);
  /*开启循环*/
  repeat
    /*分割binaryNum变量，每次获得1位*/
    set cur = substr(binarynum, numLength - chooseNum, 1);
    /*cur为0时，*/
    if cur > 0 then 
      if returnNum != 0 then
        set returnNum = CONCAT(returnNum, ',', cast(POW(2, chooseNum) as signed int));
      end if;
      if returnNum = 0 then
        set returnNum = cast(POW(2, chooseNum) as signed int);
      end if;
    end if;
  /*递增*/
  set chooseNum = chooseNum + 1;
  UNTIL chooseNum = numLength end repeat;
  return returnNum;
end;

/*调用*/
SELECT binaryPow(4611686018427387904);

注意：declare定义的变量名不能和select查询的列名相同，否则赋值不成功

如果开启bin_log时，会报以下错误：

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)

在函数创建前，需要设置参数

set global log_bin_trust_function_creators = true;

二、存储过程

存储过程是一组为了完成特定功能的SQL语句集合。

存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。

2.1 创建

DELIMITER $$                     --将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE Proc()          --创建存储过程，过程名为Proc，不带参数
-> BEGIN	                     --过程体以关键字BEGIN开始
-> select * from city;	         --过程体语句
-> END $$                        --过程体以关键字END结束
DELIMITER ;                      --将语句的结束符号恢复为分号

2.2 调用

CALL Proc;

2.3 查看

SHOW CREATE PROCEDURE [数据库.]存储过程名;		--查看某个存储过程的具体信息
SHOW CREATE PROCEDURE Proc;                 --查看已存在的存储过程
SHOW PROCEDURE STATUS LIKE '%Proc%' \G;     --查看存储过程和函数的状态

2.4 修改

IN(输入参数)：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT(输出参数)：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT(输入输出参数)：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

DELIMITER $$				
CREATE PROCEDURE Proc1(IN inname CHAR(16))		
-> BEGIN					
-> SELECT * FROM city WHERE Store_Name = inname;
-> END $$					
DELIMITER ;					

CALL Proc1('Boston');

2.5 删除

存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。

DROP PROCEDURE IF EXISTS Proc;    --仅当存在时删除，如果指定的过程不存在，则产生一个错误

2.6 控制语句

2.6.1 if-then-else

DELIMITER $$
CREATE PROCEDURE Proc2(IN inid int)
-> begin
-> declare var int;
-> set var = inid * 2;
-> if var >= 10 then
-> update t set id = id + 1;
-> else
-> update t set id = id - 1;
-> end if;
-> end $$

DELIMITER;

2.6.2 循环while

DELIMITER $$
CREATE PROCEDURE Proc3()
-> begin 
-> declare var int(10);
-> set var = 0;
-> while var < 6 do
-> insert into t values(var);
-> set var = var + 1;
-> end while;
-> end $$

DELIMITER;

2.7 案例

-- subject_id 递增 start
/*判断是否存在，如果存在就删除*/
/*取一个系统中绝对不会用到的存储过程名称*/
drop procedure if exists tempProcedure_Test;
/*创建临时存储过程*/
create procedure tempProcedure_Test()
BEGIN
  /*declare定义的变量名不能和select查询的列名相同*/
  /*判断是否还有记录*/
  declare isDone int default 0;
  /*用于自增的变量*/
  declare intNumber BIGINT(20) default 190122100000177;
  /*每条记录循环时的临时ID*/
  declare tempId BIGINT(20);

  /*取出来所有需要循环的数据*/
  declare folderIds cursor for

  /*sql语句 开始*/
  SELECT
	a.id 
  FROM
	t_account_subject a,(
	SELECT
		account_id,
		subject_code,
		max( `year` ) `year`
	FROM
		`t_account_subject` d 
	GROUP BY
		d.account_id,
		d.subject_code 
	ORDER BY
		d.YEAR DESC 
	) b 
  WHERE
	a.account_id = b.account_id 
	AND a.YEAR = b.YEAR 
	AND a.subject_code = b.subject_code 
	AND a.subject_id = 0;
  /*sql语句 结束*/

  /*如果不存在就设置为1，即为true*/
  declare continue handler for not FOUND set isDone = 1;

  /*OPEN*/
  open folderIds;
  REPEAT
  /*开始循环数据*/
  fetch folderIds into tempId;
    if not isDone THEN
      update t_account_subject set subject_id = intNumber where id = tempId;
      set intNumber = intNumber + 1;
    end if;
    until isDone end repeat;
  close folderIds; /*CLOSE，对应上面的open folderIds;*/
END;
/*调用下这个临时的存储过程*/
call tempProcedure_Test();
drop procedure tempProcedure_Test; /*使用完毕后要删除垃圾*/
-- subject_id 递增 end

总结

存储过程和函数主要区别

存储过程实现的功能要复杂一点，函数实现的功能针对性比较强;
存储过程，功能强大，可以执行包括修改表等一系列数据库操作；
用户定义函数不能用于执行一组修改全局数据库状态的操作。
对于存储过程来说可以返回参数，如记录集，函数只能返回值或者表对象;
函数只能返回一个变量；而存储过程可以返回多个；
存储过程的参数可以有IN，OUT，INOUT三种类型，而函数必须指定返回值，且参数默认为IN类型;
存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。
存储过程，可以使用非确定函数，不允许在用户定义函数主体中内置非确定函数。
存储过程一般是作为一个独立的部分来执行（EXECUTE语句执行），而函数可以作为查询语句的一个部分来调用（SELECT调用）;
由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。SQL语句中不可用存储过程，而可以使用函数。
调用方式:
函数select my_fun();
过程call my_pro();

参考文章

posted @ 2022-04-25 18:37 夏尔_717 阅读(478) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

ciel_717