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 INTBEGIN  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)BEGINDECLARE result VARCHAR(20);IF(param=0)THENSET result='a';ELSEIF (param=1) THENSET result='b';ELSESET result='c';END IF;RETURN result;END$$DELIMITER;SELECT testParam(06);-- caseWhileDROP FUNCTION IF EXISTS caseWhile;DELIMITER $$CREATE FUNCTION caseWhile(param INT) RETURNS INTBEGINDECLARE res INT DEFAULT 0;SELECTCASE protocol_group_idWHEN 0 THEN -1WHEN 1 THEN -2END 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