[大数据]mysql函数

-- 删除函数
DROP FUNCTION IF EXISTS function_name;
-- 定义函数
/*
语法规则
DELIMITER $$
create function function_name([param_name][type],..)
returns [type] //返回类型
begin
	[函数体];
	return value;//必须要有返回值
end$$;
DELIMITER;
*/
-- 示例
DELIMITER $$
CREATE FUNCTION fgetApp () RETURNS INT
BEGIN
  DECLARE result INT DEFAULT 0; 
  SELECT 
    COUNT(*)  INTO result 
  FROM
    t_app_asset;
  RETURN result ;
END $$
DELIMITER;
SELECT fgetApp() AS COUNT;

-- 测试有参函数
DROP FUNCTION IF EXISTS testParam;

DELIMITER $$
CREATE FUNCTION testParam(param INT)
RETURNS VARCHAR(20)
BEGIN
DECLARE result VARCHAR(20);
	IF(param=0)THEN
	SET result='a';
	ELSEIF (param=1) THEN
	SET result='b';
	ELSE
	SET result='c';
	END IF;
	RETURN result;
END$$
DELIMITER;

SELECT testParam(06);

-- caseWhile
DROP FUNCTION IF EXISTS caseWhile;

DELIMITER $$

CREATE FUNCTION caseWhile(param INT) RETURNS INT
BEGIN
DECLARE res INT DEFAULT 0;
SELECT
CASE protocol_group_id
WHEN 0 THEN -1
WHEN 1 THEN -2
END INTO res
 FROM t_app_asset WHERE protocol_id=param;
 RETURN res;
END$$

SELECT caseWhile(2582);


-- loop循环
DELIMITER $$
DROP FUNCTION IF EXISTS floop;
CREATE FUNCTION floop() RETURNS INT