mysql自定义函数

目录

一、概念

二、自定义函数语法

三、结构控制语法

四、虚拟表的创建

五、常用函数 

六、自定义函数案例

一、概念

1. 概念——自定义函数(user-defined function UDF)是对sql的扩展，其语法和函数相同
2. 必备条件——参数：不一定要有；返回值：一定要有
3. 包含——sql语句、声明、循环、结构控制等。

二、自定义函数语法

     1、创建函数

            Create function function_name(参数列表) returns 返回值类型

     2、删除函数

            Drop function function_name(参数列表);

            Drop function if exists function_name(参数列表);  ——推荐，删除时判断是否存在

     3、调用函数

           Select function_name(参数列表);

     4、变量的使用

           局部变量的声明：DECLARE 变量1[,变量2,... ] 变量类型  [DEFAULT 默认值]

           为变量赋值的两种方式：

                    1）、SET parameter_name = value；

                    2）、SELECT INTO parameter_name；——可以跟sql的select复用。

                              例如：将a表的值查出来给变量param——SELECT a.value INTO param FROM table a WHERE a.xx  = xx。

           用户变量定义（可理解为全局变量,作用域为当前用户的客户端）:SET @param_name = value 

三、结构控制语法

      1、IF 条件语句

IF cj > 90 THEN 
    SET pf = '优秀' 
ELSEIF cj > 60 THEN 
    SET pf = '良好' THEN
ELSE SET pf = '不及格'
END IF

     2、CASE 条件语句

CASE 
WHEN cj >= 60 THEN SET pf = '及格'; 
ELSE SET pf = '不及格'; 
END CASE ; 

---

     3、LEAVE 终止语句：LEAVE label  ——跳出循环 类似java中的break

           ITERATE 终止语句：ITERATE label ——结束此次循环，开始下次循环 类似java中的continue

     4、WHILE 循环语句：

WHILE sum<100 DO
SET sum = sum + 1;
END WHILE

      5、REPEAT 循环语句

REPEAT 
SET sum = sum + 1; 
UNTIL sum = 100 
END REPEAT ; 

四、虚拟表的创建

       建立虚拟表的优点：

               省空间——客户端退出时，虚拟表（临时表）自动drop

               私密性——客户端建立临时表只为特定的事务服务，这个表具有专用和私有性，不需要共享给其他人

               高效性——客户端建立的临时表具有单独操作和读写性能，因此速度上和处理效率上更高

       例：

DROP TEMPORARY TABLE IF EXISTS temp_virtualTable;
CREATE TEMPORARY TABLE temp_virtualTable (
	id int 
);

五、常用函数

六、自定义函数案例

案例说明：输入汉字和一个flag标识，返回汉字编码。

注意：要在库中放入汉字编码表，汉字编码链接 密码: mn8r

flag = 1返回除空格外的所有字符；

flag = 2返回汉字和字母的全拼；

flag = 3返回所有类型的字符；

例如

    输入： "张三丰 A2",  1  输出 ： zsfA2 

    输入： "张三丰 A2",  2  输出 ： zhangshanfengA2

    输入： "张三丰 A2",  3  输出 ： zsf A2 

BEGIN
declare aChar varchar(4);
	declare aResult nvarchar(1024) default '';
	declare i int default 1;
	declare j int default 1;
	declare outChar nvarchar(64);
	declare textLen ,aCode int;
	
	drop temporary table if exists temp_virtualTable;
	CREATE TEMPORARY TABLE temp_virtualTable (
		id int ,
		bm int,
		pym varchar(32)
	);

	set textLen = char_length(atext);
	while i <= textLen do
		set aChar = substring(aText, i, 1);
		set aCode = ORD(CONVERT(aChar USING ucs2));
		
		if aCode>=19968 and aCode<=40869 then						
			insert into temp_virtualTable(id,bm) values (i,aCode);
		else 	
			insert into temp_virtualTable(id,bm,pym) values (i,aCode,aChar);
		end if;
		set i = i + 1;	
	end while;

	update temp_virtualTable,HZBMGL set temp_virtualTable.pym = HZBMGL.py where HZBMGL.bm = temp_virtualTable.bm;

	while j< i DO
		
	  select pym into outChar from temp_virtualTable where id = j;
		if flag&1 = 1 then						/*flag为1时，返回除空格外的所有字符*/
			if outChar = ' ' THEN
				set outChar = '';
			else set outChar = substring(outChar, 1, 1);
			end if;
		elseif flag&2 = 2 THEN				/*flag为2时，返回汉字和字母的全拼*/
			if (outChar >= 'a' and outChar <= 'z') or (outChar >= 'A' and outChar <= 'Z') or CHAR_LENGTH(outChar) > 1  THEN
				set outChar = outChar;
			else set outChar = '';
			end if;
		elseif flag&4 = 4 THEN				/* 返回所有字符*/
			set outChar = substring(outChar, 1, 1);
		else 	
			set outChar = '';
		end if;
		set aResult = concat(aResult, outChar);
		set j = j + 1;	
	end while;
	return aResult;
END